Oracle数据库sql语句部分.docx
《Oracle数据库sql语句部分.docx》由会员分享，可在线阅读，更多相关《Oracle数据库sql语句部分.docx（19页珍藏版）》请在冰豆网上搜索。
Oracle数据库sql语句部分
oracle笔记
第一课：
客户端
1.SqlPlus（客户端），命令行直接输入：
sqlplus，然后按提示输入用户名，密码。
2.从开始程序运行:
sqlplus，是图形版的sqlplus.
3.http:
//localhost:
5560/isqlplus
Toad：
管理，PlSqlDeveloper:
第二课：
更改用户
1.sqlplussys/密码assysdba
2.alteruserscottaccountunlock;（解锁账号）
第三课：
tablestructure
（系统自带的表有emp、salgrade、dept、bonus、dual）
1.描述某一张表：
desc表名
2.select*from表名
第四课：
select语句：
1.计算数据可以用空表：
比如：
.select2*3fromdual
2.selectename,sal*12annual_salfromemp;与selectename,sal*12"annualsal"fromemp;
（annual_sal为sal*12的别名）
区别：
加双引号保持原大小写（保持原格式），不加全变大写。
任何含有空值的数学表达式结果都为空值。
3.字符串连接符
selectename||‘abcd’
||用来连接两个字符串
如果连接字符串中含有单引号，用两个单引号代替一个单引号。
第五课：
distinct去重复
selectdeptnofromemp;
selectdistinctdeptnofromemp;
selectdistinctdeptno,jobfromemp
去掉deptno,job两者组合的重复。
更多的项，就是这么多项的组合的不重复组合。
第六课：
Where过滤条件
select*fromempwheredeptno=10;
select*fromempwheredeptno<>10;不等于10
select*fromempwhereename='bike';
selectename,salfromempwheresalbetween800and1500（>=800and<=1500）
即selectename,salfromempwheresalbetween800and1500；与selectename,salfromempwheresal>=800andsal<=1500;等价。
空值处理:
selectename,sal,commfromempwherecommis（not）null;
即selectename,sal,commfromempwherecommisnull;（为空）和
selectename,sal,commfromempwherecommisnotnull;（不为空）
in语句：
selectename,sal,commfromempwhereename（not）in（'smith','king','abc'）;
即selectename,sal,commfromempwhereenamein（'smith','king','abc'）;和
selectename,sal,commfromempwhereenamenotin（'smith','king','abc'）;
语句selectename,sal,commfromempwheresalin（800,1500,2000）;表示“谁的薪水等于800，或者1500，或者2000”。
连接条件：
and并且
or或
not取反
模糊查询like：
%代表任意数量的任意字符
_代表一个任意字符
selectenamefromempwhereenamelike'_A%';
如果要查询含有%的，要用转义字符\（默认的）
转义字符可以自定义：
escape'自定义的转义字符'比如:
selectenamefromempwhereenamelike'%$a%'escape'$';
即
如果查询%
可用转义字符\%.
还可以用escape'$'比如:
selectenamefromempwhereenamelike'%$%%'escape'$';
第七课:
orderby
select*fromdept;默认按升序（asc）排列,
要按降序（desc）用如下语句：
select*fromdeptorderbydeptdesc;
selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;首先按照deptno升序排列，在deptno相同的情况下再按照ename的降序排列。
第八课:
sqlfunction1:
回顾：
selectename,sal*12annual_salfromemp
whereenamenotlike'_A%'andsal>800
orderbysaldesc;
lower函数：
selectlower（ename）fromemp;lower函数用于将字段转化为小写；
selectenamefromempwherelower（ename）like'_a%';等同于
selectenamefromempwhereenamelike'_a%'orenamelike'_A%';
substr函数：
selectsubstr（ename,1,3）fromemp;从第一个字符开始截,一共截三个字符.
selectsubstr（ename,2,3）fromemp;从第二个字符开始截,一共截三个字符.
char和ascii函数：
selectchr（65）fromdual结果为:
A将ASCII码转换为字符
selectascii（'a'）fromdual结果为:
65将字符转换为ASCII码
round函数：
selectround（23.652,1）fromdual;结果为:
23.7（第二个参数为指定四舍五入到哪位数）
selectround（23.652,-1）fromdual;20
to_char函数：
selectto_char（sal,'$99，999，999'）fromemp;（用9就可以在没有数字的地方不显示，如果用0的话一定会用0填充满）
selectto_char（sal,'L99_999_999'）fromemp;L:
代表本地符号；
这个需要掌握牢:
（务必掌握对日期的转换）
selecthiredatefromemp;
显示为:
HIRTHDATE
----------------
17-12月-80
----------------
改为:
selectto_char（hiredate,'YYYY-MM-DDHH:
MI:
SS'）fromemp;
显示:
BIRTHDATE
-------------------
1980-12-1712:
00:
00
-------------------
selectto_char（sysdate,'YYYY-MM-DDHH24:
MI:
SS'）fromdual;//也可以改为:
HH12
显示
TO_CHAR（SYSDATE,'YY
-------------------
2007-02-2514:
46:
14
to_date函数:
selectename,hiredatefromempwherehiredate>to_date（'1981-2-2012:
34:
56','YYYY-MM-DDHH24:
MI:
SS'）;
如果直接写birthdate>'1981-2-2012:
34:
56'会出现格式不匹配,因为表中的格式为:
DD-MM月-YY.
to_number函数：
selectsalfromempwheresal>888.88无错.但
selectsalfromempwheresal>$1,250,00;
会出现无效字符错误.
改为:
selectsalfromempwheresal>to_number（'$1,250.00','$9,999,99'）;
nvl函数：
把空值改为0
selectename,sal*12+nvl（comm,0）fromemp;
作用：
把comm为空的地方用0代替，这样可以防止comm为空时,sal*12相加也为空的情况.
第九课:
Groupfunction组函数（即从多行中得到一个输出）
牢记组函数：
max（）,min（）,avg（）,sum（）,count（）
selectto_char（avg（sal）,'99999999,99'）fromemp;
selectround（avg（sal）,2）fromemp;
结果:
2073.21
selectcount（*）fromempwheredeptno=10;
selectcount（ename）fromempwheredeptno=10;
selectcount（comm）fromemp;count某个字段,如果这个字段不为空就算一个.
selectcount（distinctdeptno）fromemp;
selectsum（sal）fromemp;
第十课:
Groupby语句
注意：
count（）是计数不是空值的数量
需求:
现在想求每个部门的平均薪水.
selectavg（sal）fromempgroupbydeptno;
selectdeptno,avg（sal）fromempgroupbydeptno;
按两个字段分组
selectdeptno,job,max（sal）fromempgroupbydeptno,job;
求薪水值最高的人的名字.
selectename,max（sal）fromemp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
应如下求:
selectenamefromempwheresal=（selectmax（sal）fromemp）;
Groupby语句应注意,
出现在select中的字段,如果没出现在组函数中,必须出现在Groupby语句中.
第十一课:
Having对分组结果筛选
Where是对单条纪录进行筛选,Having是对分组结果进行筛选.
selectavg（sal）,deptnofromemp
groupbydeptno
havingavg（sal）>2000;
查询工资大于1200,按部门编号进行分组,分组后平均薪水大于1500,按平均工薪倒充排列.（执行顺序要牢记。
）
selectavg（sal）fromemp
wheresal>1200
groupbydeptno
havingavg（sal）>1500
orderbyavg（sal）desc;
第十二课:
子查询:
select语句中嵌套select语句,可以在where,from后.
谁挣的钱最多（谁:
这个人的名字, 钱最多）
selectename,salfromempwheresal=（selectmax（sal）fromemp）;
问那些人工资,在平均工资之上.
selectename,salfromempwheresal>（selectavg（sal）fromemp）;
查找每个部门挣钱最多的那个人的名字.
selectename,deptnofromempwheresalin（selectmax（sal）fromenamegroupbydeptno）查询会多值.（不正确）
应该如下:
把select max（sal）,deptnofromempgroupbydeptno;当成一个表.语句如下:
selectename,salfromemp
join（select max（sal）max_sal,deptnofromempgroupbydeptno）t
on（emp.sal=t.max_salandemp.deptno=t.deptno）;
每个部门的平均薪水的等级.
分析:
首先求平均薪水（当成表）,把平均薪水和另外一张表连接.
仔细体会、理解）
selectdeptno,gradefrom（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）dept_avg_sal
joinsalgradesal_grade
on（dept_avg_sal.avg_salbetweensal_grade.losalandsal_grade.hisal）;
dept_avg_sal表sal_grade表
第十四课:
self_table_connection自连接
把某个人的名字以及他的经理人的名字求出来（经理人及这个人在表中同处一行）
分析:
首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.
selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno.
注意：
empno编号和MGR都是编号。
第十15课:
SQL1999_table_connections
selectename,dname,gradefromempe,deptd,salgrades
wheree.deptno=d.deptnoande.salbetweens.losalands.hisaland连接条件
job<>'CLERK'过滤条件
有没有办法把过滤条件和连接条件分开来?
出于这样考虑,Sql1999标准推出来了.有许多人用的还是旧的语法,所以得看懂这种语句.
selectename,dnamefromemp,dept;（旧标准）.笛卡尔乘积
selectename,dnamefromempcrossjoindept;（1999标准）
selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno（旧）
selectename,dnamefromempjoindepton（emp.deptno=dept.deptno）;1999标准.没有Where语句.
selectename,dnamefromempjoindeptusing（deptno）;等同上句,但不推荐使用.
selectename,gradefromempejoinsalgradeson（e.salbetweens.losalands.hisal）;
join连接语句,on过滤条件。
连接，条件一眼分开。
如果用Where语句较长时，连接语句和过滤语句混在一起。
三张表连接：
slectename,dname,gradefrom
empejoindeptdon（e.deptno=d.deptno）
joinsalgradeson（e.salbetweens.losalands.hisal）
whereenamenotlike'_A%';
把每张表连接条件不混在一起，然后数据过滤条件全部区分开来。
读起来更清晰，更容易懂一点。
selecte1.ename,e2.enamefromempe1joinempe2on（e1.mgr=e2.empno）;
左外连接：
会把左边这张表多余数据显示出来。
selecte1.ename,e2.enamefromempe1leftjoinempe2on（e1.mgr=e2.empno）;left后可加outer
右外连接：
把右边这张表多余数据显示出来。
selectename,dnamefromemperightouterjoindeptdon（e.deptno=d.deptno）;outer可以取掉。
全外连接：
即把左边多余数据，也把右边多余数据拿出来，
selectename,dnamefromempefulljoindeptdon（e.deptno=d.deptno）;
PS：
所谓的“外”连接，即把多余的数据显示出来。
Outer关键字可以省略
16-23
求部门中哪些人的薪水最高：
selectename,salfromemp
join（selectmax（sal）max_sal,deptnofromempgroupbydeptno）t
on（emp.sal=t.max_salandemp.deptno=t.deptno）
A.求部门平均薪水的等级。
selectdeptno,avg_sal,gradefrom
（selectdeptno,avg（sal）avg_salfromempgroupbydeptno）t
joinsalgradeson（t.avg_salbetweens.losalands.hisal）
B.求部门平均薪水的等级
selectdeptno,avg（grade）from
（selectdeptno,ename,gradefromempjoinsalgradeson
（emp.salbetweens.losalands.hisal））t
groupbydeptno
C.那些人是经理
selectenamefromempwhereempnoin（selectmgrfromemp）;
更高效率的写法：
selectenamefromempwhereempnoin（selectdistinctmgrfromemp）;
D.不准用组函数，求薪水的最高值（面试题）
selectdistinctsalfromempwheresalnotin
（selectdistincte1.salfromempe1joinempe2on（e1.sal
E.平均薪水最高的部门编号
selectdeptno,avg_salfrom
（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）
whereavg_sal=
（selectmax（avg_sal）from
（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）
）
F.平均薪水最高的部门名称
selectdnamefromdeptwheredeptno=
（
selectdeptnofrom
（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）
whereavg_sal=
（selectmax（avg_sal）from
（selectavg（sal）avg_sal,deptnofromempgroupbydeptno）
）
升级会员 