超全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_charMI:
ss’>fromemp;把入职时间改为固定格式
selectto_charMI:
ss’>fromemp;HH为12进制,HH24为24进制
selectto_charMI:
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