超全SQL语句 含详细备注示例.docx

超全SQL语句 含详细备注示例.docx

《超全SQL语句 含详细备注示例.docx》由会员分享，可在线阅读，更多相关《超全SQL语句 含详细备注示例.docx（17页珍藏版）》请在冰豆网上搜索。

超全SQL语句含详细备注示例

从命令行连接到SQLPLUS：

Sqlplussys/密码assysdba

Alteruserscottaccountunlock;

用户已更改。

connscott/tiger

已连接。

SQL单条语句：

SQL>descemp（表名）;描述这张表

EMPNO：

雇员编号COMM：

津贴DNAME：

部门名称LOC：

部门所在地

descsalgrade：

描述薪水等级

select语句：

select*fromemp：

把这个表中的内容全部取出来

selectename,sal*12,fromemp;取出雇员的年薪

select2*3fromemp;

Descdual;（dual代表输出一个字段）

select*fromdual;

select2*3fromdual;只出一个结果

selectsysdatefromdual;取出系统时间

selectename,sal*12anunal_sal（或者”anunalsal”，加引号是保持原来的格式输出，它代表年薪名）fromemp;

selectename,commfromemp;输出每个人的津贴

selectename,sal*12+commfromemp;年薪+津贴

selectename||salfromemp;||代表字符连接符

selectename||‘abc’fromemp;’’是把字符串内容加上

selectename||‘abc‘‘def’fromemp;中间会打印出一个单引号

Where是过滤语句：

select*fromempwheredeptno=10;取出部门编号为10的成员

select*fromempwhereename=‘Clark’;=是等值判断

selectename,salfromempwheresal>1500;取出薪水大于1500的成员

selectename,sal,deptnofromwheredeptno<>10;取出部门编号不等于10的部门成员和薪水

selectename,salfromempwhereename>‘cba’;字符串比较

selectename,salfromempwheresalbetween800and1500（wheresal>=800andsl<=1500）;取出薪水在800到1500之间的成员

selectename,sal,commfromempwherecommisnull;取出津贴为空的成员

selectename,sal,commfromempwherecommisnotnull;取出津贴不为空的成员

selectename,sal,commfromempwheresalin<800,1500,2000>;取出薪水等于800,1500,2000的成员

selectename,sal,commfromempwhereenamein<’sMITH’,‘KING’,‘ABC’>;取出这三个人

selectename,sa,hiredatefromempwherehiredate>‘20-2月-81’（‘20-2-1981’）;取出入职时间为1981年2月20号的成员

selectename,salfromempwheredeptno=10andsal>1000;

selectename,salfromempwheredeptno=10orsal>1000;注意两者结果不一样

selectenamefromempwhereenamelike‘%ALL%’;取出其中带有ALL字符串的成员名

selectenamefromempwhereenamelike‘_A%’;取出第二个字符是A的成员

selectenamefromempwhereenamelike‘%\%%’（‘%$%%’）（whereenamelike‘%$%%’escape‘$’）;告诉它这是转义字符

数据排序：

desc是降序、asc是升序（不写的话默认是升序）

select*fromdeptorderbydeptnodesc;按照deptno降序排列

selectempno,enamefromemporderbyempnoasc;按照empno;升序排列

//selectempno,enamefromempwheredeptno<>10orderbysalasc;

selectename,sal*12annual_salfromempwhereenamenotlike‘_A%’andsal>800orderbysaldesc;取出名字中第二个字母不是A且薪水大于800的成员按降序排列

sQL中的函数：

selectlowerfromemp;取出的名字都是小写

selectenamefromempwherelowerlike‘_a%’;先小写名字再取出第二个字母是a的成员

selectenamefromempwherelike‘_a%’orenamelike‘_A%’;

selectsubstrfromemp;从第二个开始截取三个名字

selectchr<65>fromdual;把AsCII码转为字符

selectascii<’A’>fromdual;把A转为AsCII码

selectround<23.652>fromdual;四舍五入输出为24（默认四舍五入到个位）

selectround<23.652,2>fromdual;保留两位小数，输出23.65

selectround<23.652,-1>fromdual;四舍五入到十位，输出为20

selectto_charfromemp;把sal转为固定格式，小数点后四位，小数点前五位（小数点后的必写，小数点前的位数不够可以省略）

selectto_charfromemp;L为转为本地货币符

selectto_charfromemp;注意：

即使小数点前位数不够，也得把0补上

selectto_char

MI:

ss’>fromemp;把入职时间改为固定格式

selectto_char

MI:

ss’>fromemp;HH为12进制，HH24为24进制

selectto_char

MI:

ss’>fromdual;

selectename,hiredatefromempwherehiredate>to_char<’1981-2-2012:

34:

56’,‘YYYY-MM-DDHH24:

MI:

ss’>;

selectsalfromempwheresal>to_number<’$1,250.00’,’$9,999.00’>;取出薪水大于1250的

selectename,sal*12+nvlfromemp;如果comm不存在则以0计算，nvl就是为了避免空值

selectcountfromemp;

selectcountfromemp;

Groupby分组语句：

selectavgfromempgroupbydeptno;

selectdeptno,avgfromempgroupbydeptno;上述两项都是求部门的平均薪水

selectdeptno,jobmaxfromempgroupbydeptno,job;

selectenamefromempwheresal=fromemp>;取出薪水最高的那个人名

selectavg,deptnofromempgroupbydeptnohavingavg>2000;取出平均薪水大于2000的那些组（不能用where，因为它只控制单行输出，having是对分组进行限制）

总结语句：

selectavgfromempwheresal>1200groupbydeptnohavingavg>1500orderbyavgdesc;

子查询：

Selectename,salfromempwheresal=fromemp>;select嵌套

Selectename,salfromempwheresal>fromemp>;

//Selectename,sal,deptnofromempwheresalinfromempgrupbyemptno>;注意这句话有问题

表连接：

Selectename,salfromemp

join,deptnofromempgroupbydeptno>t

On;

Jion是连接的意思on里面是连接条件

自连接：

Selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno;老版写法

Selecte1.ename,e2.enamefromempe1joinempe2on;新版写法

Selecte1.ename,e2.enamefromempe1leftjoinempe2on;左外连接：

注意left的区别，把左边多余的拿出来

等值连接：

Selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno;老版写法

Selectensme,dnamefromempjoindepton;新版写法

Selectensme,dnamefromempjoindeptusing;和上面的写法一样，但不推荐使用using的用法

非等值连接：

Selectename,gradefromempe（e代表的是表的别名）joinsalgradeson;

三个表的连接写法：

Selectename,dname,gradefromempejoindeptdonjoinsalgradesonwhereenamenotlike‘_A%’;

求部门中哪些人的薪水最高：

Selectename,salfromempjoinmax_sal,deptnofromempgroupbydeptno>ton;

求部门平均薪水的等级：

Selectdeptno,avg,gradefromavg_salfromempgroupbydeptno>tjoinsalgradeson;

求出所有部门的每个人的薪水等级：

Selectdeptno,ename,gradefromempjoinsalgradeson;

求部门平均薪水的等级：

Selectdeptno,avgfrom>tgroupbydeptno;

雇员中哪些人是经理人：

Selectenamefromempwhereempnoin;

Selectenamefromempwhereempnoin;有效率的写法

不准用组函数，求薪水的最高值（面试题）：

提示：

用自连接

selectdistinctsalfromempwheresalnotin>;

求平均薪水最高的部门编号：

Selectdeptno,avg_salfrom

avg_sal,deptnofromempgroupbydeptno>

whereavg_sal=

from

avg_sal,deptnofromempgroupbydeptno>

>;

另一种写法：

Selectdeptno,avg_salfrom

avg_sal,deptnofromempgroupbydeptno>

whereavg_sal=

>fromempgroupbydeptno>;

求平均薪水最高的部门称号：

Selectdnamefromdeptwheredeptno=

avg_sal,deptnofromempgroupbydeptno>

whereavg_sal=

from

avg_sal,deptnofromempgroupbydeptno>

>

>;

求平均薪水的等级最低的部门名称：

Selectdname,t1.deptno,grade,avg_salfrom

（

Selectdeptno,grade,avg_salfrom

（Selectdeptno,avgavg_salfromempgroupbydeptno）t

Joinsalgradeson（t.avg_salbetweens.losalands.hisal）

）t1

Joindepton（t1.deptno=dept.deptno）

Wheret1.grade=

（

Selectmin（grade）from

（

Selectdeptno,grade,avg_salfrom

（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t

Joinsalgradeson（t.avg_salbetweens.losalands.hisal）

）

）

求比普通员工最高薪水还要高的经理名称：

selectenamefromemp

Whereempnoinand

sal>selectmaxfromemp

whereempnonotin

求部门经理人中平均薪水最低的部门名称？

求薪水最高的前5名雇员？

//Selectename,salfrom（selectename,salfromempwhereempnoin（selectsal,//deptnofromemporderbysaldesc））whererownum<=5;

求薪水最高的第六到第十名雇员？

求最后入职的5名雇员？

1、查找选了‘黎明’老师课的的姓名

2、查有2门课以上不及格的学生姓名和平均成绩

selectsname,avgsal

 from（selectsno,avg（scgrade）avgsal

           from（selects.sno,sname,cno,scgrade

                       fromsc,s

                       wherescgrade<60ands.sno=sc.sno

                ）

              groupbysno

               havingcount（*）>=2）table1,s

                wheres.sno=table1.sno

3、查选了1和选了2的学生的姓名

1、selectsname

 froms

 wheresnonotin（selectsno

                fromc,sc

                        whereo=o

                        andc.cteacher='黎明'）

2、selectsname,avgsal

 from（selectsno,avg（scgrade）avgsal

           from（selects.sno,sname,cno,scgrade

                       fromsc,s

                       wherescgrade<60ands.sno=sc.sno

                ）

           groupbysno

                havingcount（*）>=2）table1,s

                wheres.sno=table1.sno

3、selectsname

    froms,sc

    wheres.sno=sc.sno

     andcno='1'

    intersect

     selectsname

    froms,sc

     wheres.sno=sc.sno

       andcno='2'

1）.求部门中哪些人的薪水最高

      selectename,emp.deptno,sal

  from（selectmax（sal）maxsalary,deptno

          fromemp

                 groupbydeptno）table1,emp

            whereemp.sal=table1.maxsalary

2）.求部门平均薪水的等级

  selectdeptno,grade

  from（selectavg（sal）avgsal,deptno

          fromemp

          groupbydeptno）table1,salgrade

  whereavgsalbetweenlosalandhisal

3）.雇员中有哪些人是经理人

    select*

    fromemp

    wherejob='MANAGER'

4）.不准用组函数，求薪水的最高值（面试题）

  selectsal

  fromemp

  wheresal>=all（selectsal

                       fromemp

                       ）

5）.求平均薪水最高的部门的部门的编号

  selectdeptno,avg（sal）maxsal

  fromemp

  groupbydeptno

  havingavg（sal）>=all（selectavg（sal）fromempgroupbydeptno）

6）.求平均薪水最高的部门的部门名称

  selectdname

  from（selectdeptno,avg（sal）maxsal

        fromemp

        groupbydeptno

        havingavg（sal）>=all（selectavg（sal）fromempgroupbydeptno））table1,dept

    wheretable1.deptno=dept.deptno

7）.求平均薪水等级最低的部门名称

selectdname

fromdept

wheredeptno=（selectdeptno

          from（selectdeptno,grade

               from（selectavg（sal）avgsal,dep