【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“sqlselect语句大部”一文，供大家参考学习
--------------mytieba---niit/niit-----------
select*fromt_tieba_dir1;
selectCLUB_DIR1_NAME,COUNT_CLUBSfromt_club_dir1;
createtabletasselectCLUB_DIR1_NAME,COUNT_CLUBSfromt_club_dir1;
select*fromt_club_dir1wherecount_clubs=0;
select*fromt_club_dir1where1=1;
select*fromt_club_dir1where1=2;
-----------scott/tiger-------------
select*fromempwheredeptno=20;
select*fromempwheresal>2000;
select*fromempwherecommisnull;
select*fromempwherecommisnotnull;
selectemp.*,sal+commfromemp;
selectemp.*,sal+nvl(comm,0)fromemp;
selectemp.*,nvl2(comm,comm,0)fromemp;
selectemp.*,to_char(hiredate,'yyyy-mm-dd')fromemp;
selectemp.*,to_char(hiredate,'yyyy"年"mm"月"dd"日"')fromemp;
selectemp.*,to_char(hiredate,'yyyy"年"mm"月"dd"日"')aschinese日期fromemp;
insertintoempvalues(8888,'9527','clerk',7698,to_date('1999-9-9','yyyy-mm-dd'),888,88,20);
selectemp.*fromempwherehiredate>to_date('1981-1-1','yyyy-mm-dd')andhiredate
selectemp.*fromempwherehiredatebetweento_date('1981-1-1','yyyy-mm-dd')andto_date('1982-1-1','yyyy-mm-dd')
selectemp.*fromempwheretrunc(hiredate,'yyyy')=trunc(to_date('1981','yyyy'),'yyyy')
selecttrunc(sysdate,'yyyy')fromdual
selectsysdatefromdual;
-----聚合函数-------
selectcount(*)fromemp;
selectcount(job)fromemp;
selectdistinct(job)fromemp;
selectcount(distinct(job))fromemp;
selectmax(sal),min(sal),avg(sal)fromemp;
-----模糊查询----%--_------
select*fromempwhereempnolike'%8%'
select*fromempwhereempnolike'%8%'orempnolike'%9%'
select*fromempwhereenamelike'%A%'orenamelike'%O%'
select*fromempwhereenamelike'%A%'andenamelike'%O%'
select*fromempwhereenamenotlike'%A%'andenamenotlike'%O%'
select*fromempwheresal>99andsal<1000;
select*fromempwheresallike'___';
---------集合操作符-------------------
createtabletasselect*fromempwheredeptnoin(20,30)
createtablemasselect*fromempwheredeptnoin(10,30)
------
select*fromt
minus
select*fromm;
--
deptno=20
------
------
select*fromm
minus
select*fromt;
--
deptno=10
------
------
select*fromt
union
select*fromm;
--
deptno=10,20,30
------
------
select*fromt
unionall
select*fromm;
--
deptno=10,20,30,30
------
------
select*fromt
intersect
select*fromm;
--
deptno=30
------
-----------------排序--------------------
select*fromemporderbysal;
select*fromemporderbysalasc;
select*fromemporderbysaldesc;
select*fromemporderbycommdesc;
select*fromemporderbydeptnoasc,saldesc;
----------分组-(输出列必须包含在groupby字段中或被聚合函数所使用)---------
selectempno,ename,job,deptnofromempgroupbydeptno,job,empno,ename;
selectdeptno,count(*)as部门人数fromempgroupbydeptno;
------------------------------------------------------------------------------------------------
---------select*from表where条件groupby分组依据orderby排序-----having再次条件-------
------------------------------------------------------------------------------------------------
------------------伪列--------------------
selectemp.*,rowid,rownumfromemp;
selectemp.*,rowid,rownumfromempwhererowid='AAAL+ZAAEAAAAAdAAA';
selectemp.*,rowid,rownumfromempwhererowid<'AAAL+ZAAEAAAAAdAAF';
selectemp.*,rowid,rownumfromempwhererowid>'AAAL+ZAAEAAAAAdAAF';
selectemp.*,rowid,rownumfromempwhererowid>'AAAL+ZAAEAAAAAdAAA'androwid<'AAAL+ZAAEAAAAAdAAF';
selectemp.*,rowid,rownumfromempwhererowidbetween'AAAL+ZAAEAAAAAdAAA'and'AAAL+ZAAEAAAAAdAAF';
selectrowidfromempwhererownum<=1;
selectmin(rowid),max(rowid)fromemp;
selectemp.*,rowid,rownumfromempwhererownum<6;
selectemp.*,rowid,rownumfromempwhererownum<=6;
selectemp.*,rowid,rownumfromemporderbyrowiddesc
selectemp.*,rowid,rownumfromempwhererownum<5orderbyrowiddesc;
--内部视图
select*from
(
selectemp.*fromemporderbyrowiddesc--形成内部视图
)whererownum<5;
--
selectemp.*fromempwhererownum<16
minus
selectemp.*fromempwhererownum<11;
--
select*fromempwhererowidin(selectrowidfromempwhererownum<16)
androwidnotin(selectrowidfromempwhererownum<11);
--
select*fromempwhere
rowidin
(
selectrowidfromempwhererownum<
(
selectcount(*)fromemp
)
)
and
rowidnotin
(
selectrowidfromempwhererownum<
(
selectcount(*)-5fromemp
)
);
----------排列函数-----------
selectemp.*,rownumas顺序fromemporderbysaldesc;
selectempno,ename,mgr,job,sal,comm,deptno,rowid,rownumfrom(selectemp.*fromemporderbysaldesc);
selectemp.*,rank()over(orderbysaldesc)asordersfromemp;
selectemp.*,dense_rank()over(orderbysaldesc)asordersfromemp;
------------特殊表dual------connsystem/managerassysdba------------
select*fromdual;
insertintodualvalues(1);
updatedualsetd='y'whered='x';
deletefromdual;
-------------------------
select1+2fromdual;
select10/4fromdual;
selectsysdatefromdual;
selectfloor(10/4)fromdual;
selectceil(10/4)fromdual;
---------------------------------------------------------------------
dual:oracle保留关键字，永远1行1列,确保语法树的完整
---------------------------------------------------------------------
-----------子查询:将一个查询的结果作为另一查询的条件-------------------
结果:单值
-----------------------------------------------------------------
select*fromempwheresal>(selectavg(sal)fromemp);
select*fromempwheresal>(selectavg(sal)fromemp)-500andsal<(selectav
g(sal)+500fromemp);
-----------------------------------------------------------------
结果:多值
-----------------------------------------------------------------
select*fromempwherejobin('CLERK','ANALYST');
select*fromempwheredeptnoin(selectdeptnofromdeptwherelocin('NEWYORK','BOSTON'))
SELECT*fromempwheresal>any(1000,2000);
SELECT*fromempwheresal>all(1000,2000);
-----------连接查询-----------------
----无条件连接--交叉连接--笛卡尔积
createtabledasselect*fromdeptwheredeptnoin(10,30,40);----15records
createtableeasselect*fromempwheredeptnoin(10,20,30);----3records
selecte.*,d.*frome,d----15*3records
----有条件连接--等同于内连接
selecte.*,d.*frome,dwheree.deptno=d.deptno-----9records
----左外连接√
标准SQL语法:selectemp.*,dept.*fromempleftouterjoindeptonemp.deptno=dept.deptno;
oracle语法:selectemp.*,dept.*fromemp,deptwhereemp.deptno=dept.deptno(+);
表别名:selecte.*,d.*fromempe,deptdwheree.deptno=d.deptno(+);
----右外连接√
selectemp.*,dept.*fromemprightouterjoindeptonemp.deptno=dept.deptno;
selectemp.*,dept.*fromemp,deptwhereemp.deptno(+)=dept.deptno;
----内连接---取这张表中的共同项（对应项）√
selectemp.*,dept.*fromempinnerjoindeptonemp.deptno=dept.deptno;
selectemp.*,dept.*fromemp,deptwhereemp.deptno=dept.deptno;
----自连接√
selecte1.empno,e1.ename,e1.mgr,e2.enamefromempe1,empe2wheree1.mgr=e2.empno(+);
-----------对象-----------------
