ORACLE笔记03++Word格式文档下载.docx
《ORACLE笔记03++Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《ORACLE笔记03++Word格式文档下载.docx（13页珍藏版）》请在冰点文库上搜索。
显示每个部门的每种岗位的平均工资和最低工资
selectavg（sal）平均工资,min（sal）最低工资,jobfromempgroupbyjob;
显示部门平均工资低于2000的部门号和它的平均工资
（1）、查询出各个部门的平均工资
selectavg（sal）,deptnofromempgroupbydeptno;
（2）、挑选出低于2000的
selectavg（sal）,deptnofromempgroupbydeptnohavingavg（sal）<
2000;
●多表查询
需求：
因为在实际开发中，我们不可避免的用到两张表或者以上的表进行联合查询，比如：
？
显示雇员名，雇员工资以及所在部门名字：
selectename,sal,dnamefromemp,deptwhereemp.deptno=dept.deptno;
（1）、我们先看看对表查询的原理：
请看图解：
特别说明：
笛卡尔积：
在多表查询的时候，如果不带任何条件，则会出现笛卡尔积。
怎么样避免笛卡尔积：
多表查询的条件至少不能少于表的个数减1。
（2）、显示雇员名，雇员工资以及所在部门名字：
selectename，sal，dnamefromemp,deptwhereemp.deptno=dept.deptno;
如何显示部门号为10的部门名、员工名、工资
selectdname,ename,salfromemp,deptwhere（emp.deptno=dept.deptnoandemp.deptno=10）;
或者：
selectdname,ename,salfromemp,deptwhere（emp.deptno=dept.deptnoanddept.deptno=10）;
显示各个员工的姓名，工资，以及工资的级别
selectemp.ename,emp.sal,salgrade.gradefromemp,salgradewhereemp.salbetweensalgrade.losalandsalgrade.hisal;
在多表查询过程中，要不要加表名，如果两个表的列同名，则需要加表名区分，否则就可以不加表名。
虽然可以不加，但是建议大家都加上表名，以增加可读性。
注意：
我们建议大家在进行多表查询时，使用别名，这样方便。
举例：
selecte.ename,e.sal,s.gradefromempe,salgradeswheree.salbetweens.losalands.hisal;
显示雇员名，雇员工资以及所在部门的名字，并按部门排序
selectemp.ename,emp.sal,dept.dnamefromemp,deptwhereemp.deptno=dept.deptnoorderbydname;
自连接：
比如显示‘ford’的上级：
（1）、查询出他的上级的编号
selectmgrfromempwhereename=’ford’;
（2）、显示上级的信息：
select*fromempwhereempno=（selectmgrfromempwhereename=’ford’）;
显示各个员工的姓名和他的上级领导的姓名
（1）、把emp表看成是两张表（worker，boss），
selectworker.ename,boss.enamefromempworker,empbosswhereworker.mgr=boss.empno;
这里我们看到king没有显示出来？
因为king没有上级，如果希望显示没有上级的员工也显示出来，则需要使用外连接。
子查询：
子查询是指嵌入到其他sql语句中的select语句，也叫嵌套查询。
（1）、单行子查询：
如何显示与SMITH同一个部门的所有员工
先查询出SMITH在哪个部门
selectdeptnofromempwhereename='
SMITH'
;
然后查询同一个部门的人员
select*fromempwheredeptno=（selectdeptnofromempwhereename='
）;
多行子查询：
如何查询出和部门10的工作相同的雇员的名字、岗位、工资、部门编号
首先：
查询出10号部门编号的有哪些？
selectdistinctjobfromempwheredeptno=10;
然后：
显示和它的岗位有一个相同的员工
selectename,job,sal,deptnofromempwherejobin（selectjobfromempwheredeptno=10）;
在子查询中使用all操作符
如何显示工资比30号部门的所有员工的工资高的员工的姓名，工资和部门号
all（selectsalfromempwheredeptno=30）;
select*fromempwheresal>
（selectmax（sal）fromempwheredeptno=30）;
在子查询中使用any
如何显示工资比30号部门的任意一个员工的工资高的员工的姓名，工资和部门号
any（selectsalfromempwheredeptno=30）;
（selectmin（sal）fromempwheredeptno=30）;
多列子查询：
如何查询与smith的部门和岗位完全相同的所有雇员
先查出smith所在的部门和岗位
selectdeptno,jobfromempwhereename='
select*fromempwhere（deptno,job）=（selectdeptno,jobfromempwhereename='
（deptno,job）中的顺序和子查询中的顺序要一致，不然会出错。
这种办法在oracle中支持，在别的数据库中不一定支持。
在from子句总使用子查询：
如何显示高于自己部门平均工资的员工信息
（这里要用到数据查询的一个小技巧，把一个子查询当做一个临时表使用）
思考：
各个部门平均工资是多少？
把上面查询的结果当做一个临时表对待：
selecte.ename,e.sal,t.myavg,e.deptnofromempe,（selectavg（sal）myavg,deptnofromempgroupbydeptno）twheree.deptno=t.deptnoande.sal>
t.myavg;
！
这个方法用得非常多，必须要掌握。
查找每个部门工资最高的人的详细信息：
查询出各个部门的最高工资
selectmax（sal）,deptnofromempgroupbydeptno;
上面的查询结果当做一个临时表对待：
selectt1.*fromempt1,（selectmax（sal）mymax,deptnofromempgroupbydeptno）t2wheret1.deptno=t2.deptnoandt1.sal=t2.mymax;
显示每个部门的信息（编号，名称）和人员数量
各个部门有多少人？
selectdeptno,count（*）fromempgroupbydeptno;
把上面的查询结果当做临时表：
selectt1.*,t2.numfromdeptt1,（selectdeptno,count（*）numfromempgroupbydeptno）t2wheret1.deptno=t2.deptno（+）;
（+）：
表示外连接符号。
小结：
需要说明的是：
当在from子句中使用子子查询时，该子查询会被当做一个临时表对待，当在from子句中使用子查询时，必须给子查询指定别名。
分页查询：
分页查询是我们学习任何一个数据库必须掌握的一个要点。
MYSQL：
select*from表名wherelimit从第几条取，取几条；
SQLSERVER:
selecttop5*from表名whereidnotin（selecttop4idfrom表名where条件）；
排除前5条，再取4条，这个案例实际上是取出了第6条到第9条。
ORACLE:
selectt2.*from（selectt1.*,rownumrnfrom（select*fromemp）t1whererownum<
=6）t2wherern>
=4;
说明：
上面的这个sql语句是oracle数据库效率比较高的查询方法，在百万级别都可以及时响应。
oracle使用三层过滤机制，
第一层：
初步过滤：
select*fromemp；
第二层：
把第一层当做临时表：
selectt1.*,rownumrnfrom（select*fromemp）t1whererownum<
=6；
第三层：
把前两层当做临时表：
selectt2.*from（selectt1.*,rownumrnfrom（select*fromemp）t1whererownum<
实际上我们可以把上面的SQL语句当做一个分页模板来对待：
其中把上面的数字进行修改即可。
其中：
6：
表示取到第几条4：
从哪条开始取。
如果我们需要对不同的情况进行分页，请在最内层进行修改即可，包括多表查询都可以。
请思考：
请按照入职时间的先后顺序查询出第7---10个人是谁？
selectt2.*from（selectt1.*,rownumrnfrom（select*fromemporderbyhiredate）t1whererownum<
=10）t2wherern>
=7;
看看分页查询的效率如何？
模拟10w数据的一个表：
->
测试下
createtablemytestasselectempno,ename,sal,comm,deptnofromemp;
自我复制：
insertintomytest（empno,ename,sal,comm,deptno）selectempno,ename,sal,comm,deptnofrommytest;
合并查询：
（详细请见幻灯片）
（1）、union：
（2）、unionall
（3）、intersect取交集
（4）、minus取差集
ORACLE的内连接和外连接：
内连接：
是我们用的最多的一种连接。
前面我们所说的都是内连接：
比如：
我们需要显示员工的信息和部门名称
selectemp.ename,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;
等价于：
selectemp.ename,dept.dnamefromempinnerjoindeptwhereemp.deptno=dept.deptno;
外连接：
左外连接，右外连接，完全外连接。
我们创建两张表做测试：
createtablestu（idnumberprimarykey,namevarchar2（32））;
insertintostuvalues（1,’Jack’）;
insertintostuvalues（2,’Tom’）;
insertintostuvalues（3,’Kity’）;
insertintostuvalues（4,’nono’）;
createtableexam（idnumberprimarykey,gradenumber）;
insertintoexamvalues（1,56）;
insertintoexamvalues（2,76）;
insertintoexamvalues（11,8）;
实际应用：
显示所有人的成绩，如果没有成绩，也要显示该人的姓名和ID号成绩显示为空
selectstu.name,stu.id,exam.gradefromstu,examwherestu.id=exam.id;
上面我们使用的是内连接，特点是只有两张表同时匹配上，才被选则。
使用左外连接来解决：
selectstu.name,stu.id,exam.gradefromstuleftjoinexamonstu.id=exam.id;
如果stu（左表）的记录没有和exam（右表）任意一条记录匹配上，也要被选中。
怎么判断一张表示左表还是右边呢？
如果在leftjoin左边，就是左表。
另外一种写法：
selectstu.name,stu.id,exam.gradefromstu,examwherestu.id=exam.id（+）;
右外连接：
显示所有成绩，如果没有名字匹配，显示空
selectstu.id,stu.name,exam.gradefromsturightjoinexamonstu.id=exam.id;
右外连接指的是右边的表如果没有和左边的任何一条记录匹配，也要被选中。
另外还有一种写法：
selectstu.id,stu.name,exam.gradefromstu,examwherestu.id（+）=exam.id;
把（+）写在左边就表示右外连接。
实际上，左外连和右外连接是可以相互转换的：
例如：
（1）、selectstu.id,stu.name,exam.gradefromsturightjoinexamonstu.id=exam.id;
（2）、selectstu.id,stu.name,exam.gradefromstu,examwherestu.id（+）=exam.id;
（3）、selectstu.name,stu.id,exam.gradefromexamleftjoinstuonstu.id=exam.id;
（4）、selectstu.name,stu.id,exam.gradefromstu,examwherestu.id（+）=exam.id;
完全外连接：
两个表查询不管有没有匹配上，都要显示出来
显示所有成绩和所有人的名字，如果没有相应的匹配值，则显示为空：
selectstu.id,stu.name,exam.gradefromstufulljoinexamonstu.id=exam.id;
小练习：
列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门：
selectdept.dname,emp.*fromdept,empwhereemp.deptno（+）=dept.deptno;
selectdept.dname,emp.*fromdeptleftjoinemponemp.deptno=dept.deptno;
练习：
6、
（1）、selectavg（sal）,sum（sal）,max（sal）,min（sal）fromemp;
（2）、selectcount（job）,avg（sal）fromempgroupbyjob;
（3）、select'
雇员总数'
count（*）fromempunionselect'
补助雇员'
count（comm）fromemp;
（4）、select'
管理者'
count（job）fromempwherejob='
MANAGER'
（5）、selectmax（sal）-min（sal）fromemp;
（6）、select‘岗位’,avg（sal）fromempgroupbyjobunionselect‘部门’,avg（sal）fromempgroupbydeptno;
7、
（1）、selectdept.dname,emp.ename,emp.sal,emp.jobfromemp,deptwheredept.deptno=emp.deptnoanddept.deptno=20;
（2）、selectemp.ename,m,dept.dnamefromemp,deptwheredept.deptno=emp.deptnoandmisnotnull;
（3）、selectemp.ename,emp.sal,dept.dnamefromemp,deptwheredept.deptno=emp.deptnoanddept.loc=’DALLAS’;
（4）、
升级会员 