Oracle.docx
《Oracle.docx》由会员分享，可在线阅读，更多相关《Oracle.docx（31页珍藏版）》请在冰豆网上搜索。
Oracle
Oracle重点
重点：
与SELECT相关的所有
如果你不做DBA，其他知识点暂时理解就够了
Day01
1.s_emp、s_dept表的字段含义
first_name名
last_name姓
title职位
dept_id部门号
commission_pct提成（有空值）
2.列出所有人的年薪
selectfirst_name,salary*12froms_emp;
3.给列起别名
selectfirst_name,salary*12Ann_Salfroms_emp;
selectfirst_name,salary*12"AnnSal"froms_emp;
selectfirst_name,salary*12as"AnnSal"froms_emp;
4.处理空值的函数nvl（p1,p2）
nullOracle当做无穷大来处理
空值不等于0
空值不等于空格
算数表达式中为空值，返回空值
selectfirst_name,salary*12*（1+nvl（commission_pct,0）/100）froms_emp;
5.SQLPLUS命令：
a）L
列出上一次敲入的命令
b）clearscr或者！
clear
清屏
6.字段（列名）拼接
||字符串拼接
''Oracle中字符和字符串用单引号表示
""双引号用于表示别名
selectfirst_name||''||last_nameemployeefroms_emp;
selectfirst_name||'isintdepartment'||dept_id||'.'froms_emp;
7.去除重复值distinct
##该公司有哪些职位？
selectdistincttitlefroms_emp;
##各个部门有哪些不同的职位？
##distinct的功能：
部门号单独重复，职位单独重复，部门号和职位联合不重复
selectdistinctdept_id,titlefroms_emp;
##会报错，因为distinct只能出现在select后面，否则会造成逻辑不通
selectdept_id,distincttitlefroms_emp;（X）
8.列出表中所有字段
##注意：
写*会降低效率，公司中一般会禁止写*;
select*froms_emp;
9.Oracle中写SQL大小写区别在功能上无影响，性能上有影响
##注意：
写SQL，一般公司都有规范
10.where控制子句
##年薪大于1.2w的员工的年薪?
##如果salary字段上建了索引，第一种写法，索引用不上，所以慢
selectfirst_name,salary*12a_sal
froms_emp
wheresalary*12>12000;
##如果salary字段上建了索引，第二种写法，效率高些
selectfirst_name,salary*12a_sal
froms_emp
wheresalary>1000;
##会报错，where子句后面不可以跟“列别名”,where子句执行在select语句之前
selectfirst_name,salary*12a_sal
froms_emp
wherea_sal>12000;
##EX.不会报错，orderby子句可以使用“别名”
selectfirst_name,salary*12a_sal
froms_emp
orderbya_sal;
11.注意：
单引号中大小写敏感
##列出Carmen的年薪是多少？
Selectfirst_name,salary*12a_sal
Froms_emp
Wherefirst_name='Carmen';
12.大小写转换函数lower（）upper（）
##列出Carmen的年薪是多少？
selectfirst_name,salary*12a_sal
froms_emp
wherelower（first_name）='carmen';
13.AND和betweenand连接符
##找出员工工资在1000与1500之间
selectfirst_name,salary
froms_emp
wheresalary>=1000andsalary<=1500;
##betweenand就表示了如上含义
selectfirst_name,salary
froms_emp
wheresalarybetween1000and1500;
14.OR连接符IN（）表述形式=ANY（）
##找出31、41、43部门员工的姓名和部门号？
selectfirst_name,dept_id
froms_emp
wheredept_id=31ordept_id=41ordept_id=43;
##简单的表述形式in（）
selectfirst_name,dept_id
froms_emp
wheredept_idin（31,41,43）;
##另一种表述形式in（）相当于=any（）
selectfirst_name,dept_id
froms_emp
wheredept_id=any（31,41,43）;
##从连续区间中取值使用Between-And，从离散数值中取值用IN（）
15.LIKE运算符SUBSTR（）函数Length（）函数
（通配符：
%表示0或多个字符；_表示任意单个字符）
##效率高些
wherelast_namelike'M%'
##结果等同如上
wheresubstr（last_name,1,1）='M';
##列出名字的最后两个字母
selectfirst_name,substr（first_name,-2,2）froms_emp;
##列出名字的最后两个字母length（）函数
selectsubstr（first_name,length（first_name）-1,2）froms_emp;
16.escape关键字（表示\后边的符号不是通配符）
selecttalble_namefromuser_tables
wheretalbe_namelike'S\_%'escape'\';
17.ISNULL判断字段是否为空ISNOTNULL
selectfirst_name,commission_pctfroms_empwherecommission_pctisnull;
18.NOTBETWEENAND
NOTIN（）
NOTLIKE
ISNOTNULL
##除了31、41、43部门的部门员工的情况
selectfirst_name,dept_idfroms_emp
wheredept_idnotin（31,41,43）;
##等价写法
selectfirst_name,dept_idfroms_emp
wheredept_id!
=31anddept_id!
=41anddept_id!
=43;
##等价写法<>all（31,41,43）
selectfirst_name,dept_idfroms_emp
wheredept_id<>all（31,41,43）;
##任何数据与NULL比较，都返回false，
##使用notin（）时，如果集合中有null值，则查不出任何记录，对in（）没影响
selectfirst_name,dept_idfroms_emp
wheredept_idnotin（31,41,43,null）;
19.注意下两句SQL的区别，理解OR和AND
##找出部门号为44，工资大于1000的员工或者部门号为42的所有员工?
selectlast_name,salary,dept_id
froms_emp
wheresalary>=1000anddept_id=44ordept_id=42;
##找出部门号为44或者42的，并且工资大于1000的员工
selectlast_name,salary,dept_id
froms_emp
wheresalary>=1000and（dept_id=44ordept_id=42）;
20.隐式数据类型转换
##如下式相同的结果，系统做了隐式数据类型转换，均为：
字符转数值
selectfirst_name,salaryfroms_empwheresalary=1450;
selectfirst_name,salaryfroms_empwheresalary='1450';
##相当于
selectfirst_name,salaryfroms_empwhereto_number（salary）=1450;
##做严格的数据类型匹配相当重要
selectfirst_name,salaryfroms_empwheresalary=1450;
21.显式数据类型转换to_char（）函数
##输出所有员工的manager_id，如果没有manager_id，则用BOSS填充
selectfirst_name,nvl（to_char（manager_id）,'Boss'）froms_emp;
Day02
22.表和表之间的关系
s_emp员工表
s_dept部门表
s_region部门所在地区表
salgrade工资等级表
emp员工表
dept部门表
23.等值连接
##查询''Carmen'所在部门的地区？
（Canmen在哪个地区上班？
）
##中间表“部门表”
##用几张表就JOIN几次
##等值连接（内连接的一种）：
父表的主键==子表的外键
selecte.first_name,r.name
froms_empe
joins_deptd
one.dept_id=d.id
ande.first_name='Carmen'
joins_regionr
ond.region_id=r.id;
##亚洲地区有哪些员工？
selecte.first_name,r.name
froms_empe
joins_deptd
one.dept_id=d.id
joins_regionr
ond.region_id=r.id
andr.name='Asia';
24.非等值连接
##列出员工的工资以及对应的工资级别？
selecte.ename,e.sal,s.grade
fromempe
joinsalgrades
one.salbetweens.losalands.hisal;
##SMITH的工资级别？
selecte.ename,e.sal,s.grade
fromempe
joinsalgrades
one.salbetweens.losalands.hisal
ande.ename='SMITH';
##3,5级有哪些员工（哪些员工属于3,5级）？
selecte.ename,e.sal,s.grade
fromempe
joinsalgrades
one.salbetweens.losalands.hisal
ands.gradein（3,5）;
25.自连接
##列出员工名和领导名的对应关系
##结果为24个，少一个manager_id为空的人（BOSS丢了）
selecte.first_nameemplayee,m.first_namemanager
froms_empe
joins_empm
one.manager_id=m.id;
##列出哪些人是领导？
selectdistinctm.first_name
froms_empejoins_empm
onm.id=e.manager_id;
26.outerjoin外连接
##内连接fromt1joint2ont1.id=t2.id
##fromt1leftouterjoint2ont1.id=t2.id左边的表做驱动表
##fromt1rightouterjoint2ont1.id=t2.id右边的表做驱动表
##外连接解决的问题：
驱动表中的记录在结果集中“一个都不少”
##列出员工名和领导名的对应关系？
selecte.first_nameemployee,nvl（m.first_name,'Boss'）manager
froms_empe
leftouterjoins_empm
one.manager_id=m.id;
##如何写外连接：
##先写出内连接，再确定哪张表当驱动表就可以
##哪个部门没有员工？
14条记录，少1条
selecte.ename,e.deptno
fromempe
joindeptd
one.deptno=d.deptno;
##哪个部门没有员工？
15条记录
selecte.ename,e.deptno,d.deptno,d.dname
fromempe
rightjoindeptd
one.deptno=d.deptno;
##哪个部门没有员工？
15条记录
selectd.deptno,d.dname,e.ename,e.deptno
fromempe
rightjoindeptd
one.deptno=d.deptno
wheree.empnoisnull;
##使用外连接解决了两类问题：
1.把所有结果列出到结果集
2.解决否定问题（不是，没有，不包含）
##那些人是员工？
（即：
那些人不是领导？
）
##思路：
##先解决那些人是领导
##能匹配的是领导
##把匹配不上的挑出来
##
selecte.first_name,m.first_name
froms_empe
rightjoins_empm
one.manager_id=m.id;
##加条件
selecte.first_name,m.first_name
froms_empe
rightjoins_empm
one.manager_id=m.id
wheree.idisnull;
##最后列出m.first_name即可
selectm.first_name
froms_empe
rightjoins_empm
one.manager_id=m.id
wheree.idisnull;
27.And在外连接之前做过滤，where在外连接之后做过滤
##
selecte.ename,d.dname
fromempe
rightjoindeptd
one.deptno=d.deptno
ande.ename='SMITH';
##驱动表的过滤全部写在where之后
selecte.enameeename,d.dnamedename
fromempe
rightjoindeptd
one.deptno=d.deptno
ande.ename='SMITH';
wheree.empnoisnull;
##选择leftjon或者rightjoin不重要，重要的是选择哪张表做驱动表
28.fulloutjoin用的比较少
29.组函数
##组函数：
一堆数据返回的结果
##max（）
##avg（）
##min（）
##avg（）
##求所有人的平均工资？
##求所有人的平均提成？
selectavg（nvl（commission_pct,0））froms_emp;
##count（）处理的结果如果全为空值，结果返回0
selectcount（commission_pct）froms_empwherecommission_pctisnull;
##计算有多少条记录
selectcount（id）froms_emp;
##求按提成分组，计算人数？
selectcommission_pct,count（id）
froms_emp
groupbycommission_pct;
##count（）函数中可以加入关键字
selectcount（title）froms_emp;
##等同于
selectcount（alltitle）froms_emp;
##把重复值去掉，再做统计
selectcount（distincttitle）froms_emp;
##列出42号部门的平均工资
##若有groupby子句，select后面可跟groupby后面跟的表达式以及组函数，其他会报错。
selectdept_id,avg（salary）
froms_emp
wheredept_id=42
groupbydept_id;
##若没有groupby子句，select后面有一个组函数，其他都必须是组函数
selectmax（dept_id）,avg（salary）
froms_emp
wheredept_id=42;
作业：
insertintosalgradevalues（6,10000,15000）;
##列出每个工资级别有多少员工？
##列出3,5级有多少员工
##列出每个工资级别有多少员工（若该级别没有员工，也要列出）
Day03
30.子查询
##先执行子查询；子查询只执行一遍
##若子查询返回值为多个，Oracle会去掉重复值之后，将结果返回主查询
##谁是受老板剥削工资最低的人？
selectfirst_name,salary
froms_emp
wheresalary=（selectmin（salary）froms_emp）;
##谁跟SMITH的职位是一样的？
selectlast_name,title
froms_empwhere
title=（selecttitlefroms_empwherelast_name='Smith'）
andlast_name!
='Smith';
##如果表中有重复值，如两个'Smith'，会报错：
##single-rowsubqueryreturnsmorethanonerow单行子查询返回多行
##修改为：
selectlast_name,title
froms_empwhere
title=any（selecttitlefroms_empwherelast_name='Smith'）
andlast_name!
='Smith';
##哪些部门的平均工资比32部门的工资高？
selectdept_id,avg（salary）
froms_emp
groupbydept_id
havingavg（salary）>
（selectavg（salary）froms_empwheredept_id=32）;
##那些人是领导？
子查询
selectfirst_name
froms_emp
whereidin（selectmanager_idfroms_emp）;
##那些人是领导？
表连接
selectdistinctm.first_name
froms_empm
joins_empe
one.manager_id=m.id;
##Ben的领导是谁？
子查询
selectfirst_name
froms_emp
whereid=
（selectmanager_idfroms_empwherefirst_name='Ben'）;
##Ben领导谁？
子查询
selectfirst_name
froms_emp
wheremanager_id=
（selectidfroms_empwherefirst_name='Ben'）;
##Ben的领导是谁？
表连接
selectm.first_name
froms_empm
Joins_empe
one.first_name='Ben'ande.manager_id=m.id;
##Ben领导谁？
表连接
selecte.first_name
froms_empe
joins_empm
onm.first_name='Ben'ande.manager_id=m.id;
##
selectfirst_name
froms_emp
whereidin（selectmanager_idfroms_emp）;
##演示代码
##对notin来说，结果集中如果有null，则整个结果集为null
##结论：
对notin来说，子查询结果集中是不能有null的
selectfirst_name
froms_emp
whereidnotin（selectmanager_idfroms_emp）;
##查询那些人是员工？
selectfirst_name
froms_emp
whereidnotin（selectmanager_idfroms_empwheremanager_idisnotnull）;
##notin尽量不用
31.子查询与空值
##哪些部门的员工工资等于本部门员工平均工资？
##多列
selectfirst_name,dept_id,salary
froms_emp
where（dept_id,salary）in（selectdept_id,avg（salary）froms_empgroupbydept_id）;
32.关联子查询
##哪些员工的工资比本部门的平均工资高？
selectfirst_name,dept_id,salary
froms_empouter
wher
升级会员 