Oracle二xvm.docx

《Oracle二xvm.docx》由会员分享，可在线阅读，更多相关《Oracle二xvm.docx（11页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、Oracle（二）作者:IT电子教育门户来源:最后修改于：2008-11-4 9:21:00点击开始打印页面地址是: 1、课程名称：OracleORACLE的经典的查询案例。多表查询、分组统计、子查询。数据库更新操作2、知识点2.1、上次课程的主要知识点1、SQL的基础语法SELECT 要显示的列、内容或全部column|expression|* FROM 表名称 别名WHERE 多个查询条件ORDER BY 排序的列 DESC|ASC2、重点函数：NVL、DECODE、字符串、to_char、to_number、to_date2.2、本次预计讲解的知识点1、多表查询，SQL:1999语法对多

2、表查询的支持2、分组统计及统计函数3、子查询，并结合多表查询，分组统计做复杂查询3、具体内容3.1、多表查询一张以上的表一起查询，就称为多表查询。例如：要一起查询雇员表和部门表的全部信息。那么此时就可以按照以下的语法进行编写：SELECT * FROM emp,dept ;但是如果直接运行以上的程序会发现问题：发现显示的记录有56条？但是实际上雇员表一共才有14条。例如：SELECT COUNT(*) FROM emp ;SELECT COUNT(*) FROM dept;分别求出雇员表和部门表，发现雇员表中只有14条记录，而部门表只有4条记录，但是如果两个放在了一起，则会出现56条记录。即：

3、14 4 = 56 ？那么对于以上的情况，在数据库中有一个名称 笛卡尔积。但是此结果并不是用户所需要的，用户所需要看到的就是全部的雇员信息或部门信息。只有14个雇员就应该只显示14条记录。那么此时就可以通过对关联列的条件限制来去除笛卡尔积。在雇员表中有一个deptno的字段。在部门表也有一个deptno的字段。即：可以通过以下的方式去掉所有的重复数据：SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno ;以上的程序已经符合查询的标准，但是存在问题，如果现在要查询的表名称过长，则在编写查询列的时候肯定要重复写上表名称。所以一般在多表查询的时候都

4、习惯于为表起一个别名。例如：现在要求查询出雇员的编号、雇员姓名、部门的编号、部门名称、部门位置：SELECT e.empno,e.ename,d.deptno,d.dname,d.locFROM emp e,dept dWHERE e.deptno=d.deptno ;例如：要求查询出每个雇员的姓名、雇员的工作、雇员的直接上级领导的姓名。SELECT e.ename,e.job,m.enameFROM emp e,emp mWHERE e.mgr=m.empno ;例如：要求查询出每个雇员的姓名、工作、领导姓名、部门名称、雇员工资。SELECT e.ename,e.job,m.ename,d.

5、dname,e.salFROM emp e,emp m,dept dWHERE e.deptno=d.deptno AND e.mgr=m.empno ;思考：现在要求查询出每个雇员的姓名、工资、部门名称、工资在公司的等级（salgrade）、领导的姓名，领导的工资在公司的等级。SELECT e.ename,e.sal,d.dname,s1.grade,m.ename,s2.gradeFROM emp e,emp m,dept d,salgrade s1,salgrade s2WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.sal BETWEEN

6、 s1.losal AND s1.hisal AND m.sal BETWEEN s2.losal AND s2.hisal ;进一步思考：现在要求按照以下的样式显示工资等级： 1：第5等工资 2：第4等工资 3：第3等工资 4：第2等工资 5：第1等工资SELECT e.ename,e.sal,d.dname,decode(s1.grade,1,第五等工资,2,第四等工资,3,第三等工资,4,第二等工资,5,第一等工资),m.ename,decode(s2.grade,1,第五等工资,2,第四等工资,3,第三等工资,4,第二等工资,5,第一等工资)FROM emp e,emp m,dept

7、d,salgrade s1,salgrade s2WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.sal BETWEEN s1.losal AND s1.hisal AND m.sal BETWEEN s2.losal AND s2.hisal ;观察以下SQL的查询结果：SELECT * FROM dept ;发现部门表中一共有四个部门，但是在执行以下SQL语句之后再观察：SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno ;发现在以上的查询结果中，并没有显示出40部门的信息，那么如果此时必须显

8、示出40部门的话，则必须对表的连接进行设置 左连接、右连接。SELECT * FROM emp e,dept d WHERE e.deptno(+)=d.deptno ;以上代码在查询条件处的等号左边加入了一个“(+)”，那么此时表示的是右连接，以DEPT表为准。如果现在把“(+)”放到右边，则表示左连接，以emp表为准。SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno(+) ;默认的是以左边为标准进行连接的，属于左连接。例如：之前讲解的列出每一个员工的员工姓名和领导名称的时候，并没有列出“king”。如果现在要把“king”列出来，则必须进

9、行连接处理。SELECT e.ename,e.job,m.enameFROM emp e,emp mWHERE e.mgr=m.empno(+) ;以上为表连接在实际中的标准操作，但是对于SQL语法来说，对表的连接又有另外一套语法。交叉连接（CROSS JOIN）：产生笛卡尔积SELECT * FROM emp CROSS JOIN dept ;自然连接（NATURAL JOIN）：自动进行关联字段的匹配SELECT * FROM emp NATURAL JOIN dept ;USING子句：直接指定关联列即可SELECT e.ename,e.sal,deptno,d.locFROM emp

10、e JOIN dept d USING (deptno)WHERE deptno=20 ;ON子句：用户自己指定关联的条件SELECT e.empno,e.ename,e.deptno,d.deptno,d.locFROM emp eJOIN dept dON (e.deptno=d.deptno) ;左连接（左外连接）、右连接（右外连接）：LEFT JOIN，RIGHT JOINSELECT e.ename,d.deptno,d.locFROM emp eRIGHT OUTER JOIN dept dON (e.deptno=d.deptno) ;SELECT e.ename,d.deptn

11、o,d.locFROM emp eLEFT OUTER JOIN dept dON (e.deptno=d.deptno) ;3.2、组函数和分组统计分组：例如：把所有男生分为一组，所有女生分为一组。之后对每组的数据进行统计。这样的函数就称为分组函数。3.2.1、组函数常用的分组函数： COUNT：求出全部的记录数 MAX：求出一个组中的最大值 MIN：求出最小值 AVG：求平均值 SUM：求和1、COUNT函数：SELECT COUNT(empno) FROM emp ;2、MAX、MIN：求最大和最小值，针对于数字的应用上。 求出所有员工的最低工资：SELECT MIN(sal) FROM

12、 emp ; 求出所有员工的最高工资：SELECT MAX(sal) FROM emp ;3、求和及平均值 求出所有员工的总工资：SELECT SUM(sal) FROM emp ; 求出所有员工的平均工资：SELECT AVG(sal) FROM emp ;3.2.2、分组统计分组统计使用GROUP BY 进行分组，后面要跟上分组的条件，即：GROUP BY 分组条件（按那个字段）例如：求出每个部门的雇员数量。只能按deptno分组。SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno ;例如：求出每个部门的平均工资SELECT deptno

13、,AVG(sal) FROM emp GROUP BY deptno ;注意点：观察以下代码：SELECT deptno,COUNT(empno) FROM emp ;以上代码不能够正确执行，因为：1、程序中如果使用分组函数，则有两种情况：必须有group by，这样才能跟上分组的条件。直接使用分组函数查询：SELECT COUNT(emp) FROM emp ;2、在使用分组函数的时候，不能出现分组函数和分组条件之外的字段。例如：按部门分组，要求显示出部门的名称，及每个部门的员工数。SELECT e.deptno,d.dname,COUNT(e.empno) FROM emp e,dept

14、d GROUP BY e.deptno ;在以上的SQL语句之中“d.dname”并不属于分组的条件或是分组的函数，所以不能使用。例如：要求显示出平均工资大于2000的部门编号和平均工资。SELECT deptno,AVG(sal) FROM emp WHERE AVG(sal)2000 GROUP BY deptno ;出现了以下错误：SELECT deptno,AVG(sal) FROM emp WHERE AVG(sal)2000 GROUP BY deptno *第 1 行出现错误:ORA-00934: 此处不允许使用分组函数因为分组函数不能出现在where语句之中，所以此时，如果要对

15、分组的条件进行过滤，则必须单独编写HAVING子句，HAVING的功能与WHERE一样，只是条件是作为分组的条件出现。所以以上代码可以修改为：SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)2000 ;完整的SQL语句格式：SELECT DISTINCT* | COLUMN | EXPRESIONFROM 表1 别名1,表2 别名2,. 在此处不能够出现各种分组函数WHERE 多个查询条件 GROUP BY 分组条件HAVING 可以出现各分组函数分组查询条件 ORDER BY 排序 ASC | DESC 例如：显示

16、非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列：1、显示全部的非销售人员信息：SELECT *FROM empWHERE job SALESMAN ;2、按工作分组，同时求出工资的总和SELECT job,SUM(sal)FROM empWHERE job SALESMAN GROUP BY job;3、对分组的条件进行限制，工资综合大于5000SELECT job,SUM(sal)FROM empWHERE job SALESMAN GROUP BY job HAVING SUM(sal)5000;

17、4、按工资的升序排列SELECT job,SUM(sal) suFROM empWHERE job SALESMAN GROUP BY job HAVING SUM(sal)5000ORDER BY su;简单的原则：列上有重复内容的时候才可以进行分组。注意：组函数可以嵌套使用，但在组函数嵌套使用的时候不能再出现分组条件的查询语句：求出平均工资最高的部门 错误的代码：SELECT deptno,MAX(AVG(sal)FROM emp GROUP BY deptno ; 正确的代码：SELECT MAX(AVG(sal)FROM emp GROUP BY deptno ;3.3、子查询子查询：

18、在一个查询语句之后包含了另外一个查询语句，称为子查询。例如：要求查询出比7654工资高的全部雇员的信息。此时，就必须先查询出7654的工资，之后把这个工资作为条件继续取出其他的内容：SELECT * FROM empWHERE sal(SELECT sal FROM emp WHERE empno=7654) ;子查询在操作中又分为三类： 单列子查询：返回的结果是一列一个内容 单行子查询：返回一条完整的记录 多行子查询：返回多条记录例如：要求查询出工资比7654高，同时与7788从事相同工作的全部雇员信息。 7654的工资：SELECT sal FROM emp WHERE empno=765

19、4 7788的工作：SELECT job FROM emp WHERE empno=7788 综合SELECT * FROM empWHERE sal(SELECT sal FROM emp WHERE empno=7654) AND job=(SELECT job FROM emp WHERE empno=7788) ;例如：要求查询出工资最低的雇员姓名、工作、工资 最低工资：SELECT MIN(sal) FROM emp ; 肯定以最低工资作为条件SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp) ;例如：查询出雇员工资大于公司平

20、均工资的雇员信息SELECT * FROM emp WHERE sal(SELECT AVG(sal) FROM emp) ;思考：要求查询出：部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名，和最高收入雇员的姓名。程序需要两张表关联：emp、dept1、如果要想求出员工数量，则肯定分组，分组肯定使用deptno，而部门名称需要单独查询dept表：SELECT d.dname,ed.c FROM dept d,(SELECT deptno dno,COUNT(empno) c FROM emp GROUP BY deptno) edWHERE d.deptno=ed.dno ;

21、2、加上部门的平均工资：SELECT d.dname,ed.c,ed.aFROM dept d,(SELECT deptno dno,COUNT(empno) c,AVG(sal) a FROM emp GROUP BY deptno) edWHERE d.deptno=ed.dno ;3、部门的最低收入雇员的姓名SELECT d.dname,ed.c,ed.a,e.enameFROM dept d,(SELECT deptno dno,COUNT(empno) c,AVG(sal) a,MIN(sal) min FROM emp GROUP BY deptno) ed,emp eWHERE

22、d.deptno=ed.dno AND e.sal=ed.min ;4、求出最高工资SELECT d.dname,ed.c,ed.a,ed.min,ed.max,(select ename e1 FROM emp WHERE sal=min) loemp ,(select ename e2 FROM emp WHERE sal=max AND rownum=1) hiemp FROM dept d,(SELECT deptno dno,COUNT(empno) c,AVG(sal) a,MIN(sal) min,MAX(sal) max FROM emp GROUP BY deptno) ed

23、WHERE d.deptno=ed.dno ;附录：ROWNUM：也是一种数据类型，表示的是所在的行数：例如：以下的查询语句：select ename,rownum from emp ;例如：如果现在输入了以下的SQL语句：select * from emp ;是列出了全部的数据内容。但是此时，只需要显示前五条记录。SELECT * FROM(SELECT A.*, ROWNUM RN FROM (select empno,ename from emp ) A WHERE ROWNUM 5;例如：查询平均工资最低的工种的名称及平均工资：SELECT job,avg(sal)FROM emp e

24、GROUP BY job HAVING avg(sal)=(SELECT MIN(AVG(sal) FROM emp GROUP BY job) ;针对于多行子查询，那么还有以下三种符号： IN ANY ALLIN指定范围：SELECT ename,sal,deptnoFROM empWHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno) ;ANY操作： =ANY：与IN操作符的功能一样SELECT ename,sal,deptnoFROM empWHERE sal =ANY (SELECT MIN(sal) FROM emp GROUP

25、 BY deptno) ; ANY：比里面最小的值要大SELECT ename,sal,deptnoFROM empWHERE sal ANY (SELECT MIN(sal) FROM emp GROUP BY deptno) ; ANY：比最大值要小SELECT ename,sal,deptnoFROM empWHERE sal ALL：比里面最大的值要大SELECT ename,sal,deptnoFROM empWHERE sal ALL (SELECT MIN(sal) FROM emp GROUP BY deptno) ; ALL：比里面最小的值要小SELECT ename,sal

26、,deptnoFROM empWHERE sal 1;如果现在要求显示的是部门名称SELECT d.deptno,d.dnameFROM dept d,(SELECT deptno dno,COUNT(empno) c FROM emp GROUP BY deptno HAVING COUNT(empno)1) dd WHERE d.deptno=dd.dno;2、列出薪金比“SMITH”多的所有员工。SELECT * FROM emp WHERE sal(SELECT sal FROM emp WHERE ename=SMITH) ;3、列出所有员工的姓名及其直接上级的姓名。 自身关联SEL

27、ECT e.ename,m.enameFROM emp e,emp mWHERE e.mgr=m.empno ;4、列出受雇日期早于其直接上级的所有员工。SELECT e.ename,e.hiredate,m.ename,m.hiredateFROM emp e,emp mWHERE e.mgr=m.empno AND e.hiredate1) dd WHERE d.deptno=dd.dno(+);6、列出所有“CLERK”（办事员）的姓名及其部门名称。SELECT e.ename,d.dnameFROM dept d,emp eWHERE d.deptno=e.deptno AND e.j

28、ob=CLERK ;7、列出最低薪金大于1500的各种工作。SELECT job,MIN(sal)FROM empGROUP BY job HAVING MIN(sal)1500 ;8、列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。SELECT ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname=SALES) ;9、列出薪金高于公司平均薪金的所有员工。SELECT * FROM emp WHERE sal(SELECT AVG(sal) FROM emp) ;10、列出与“SCOTT

29、”从事相同工作的所有员工。SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename=SCOTT) ;11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) ;12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。SELECT * FROM emp WHERE sal ALL (SELECT sal FROM emp WHERE deptno=30) ;13、列出在每个部门工作的

30、员工数量、平均工资和平均服务期限。SELECT deptno,COUNT(empno),AVG(sal),AVG(sysdate-hiredate)/365) FROM emp GROUP BY deptno ;14、列出所有员工的姓名、部门名称和工资。SELECT e.ename,d.dname,e.salFROM dept d,emp e WHERE d.deptno=e.deptno ;15、列出所有部门的详细信息和部门人数。SELECT d.deptno,d.dname,d.loc,dd.cFROM dept d,(SELECT deptno dno,COUNT(empno) c FR

31、OM emp GROUP BY deptno) ddWHERE d.deptno=dd.dno ;16、列出各种工作的最低工资。SELECT job,MIN(sal) FROM emp GROUP BY job ;17、列出各个部门的MANAGER（经理）的最低薪金。SELECT deptno,MIN(sal) FROM emp WHERE job=MANAGER GROUP BY deptno ;18、列出所有员工的年工资，按年薪从低到高排序。SELECT (sal+NVL(comm,0)*12 income FROM emp ORDER BY income ;19、查出某个员工的直接上级主

32、管，并要求出这些主管中的薪水超过3000 (用一条sql语句解决)SELECT e.ename,m.enameFROM emp e,emp mWHERE e.mgr=m.empno AND m.sal3000 ;20、求出部门名称中，带S字符的部门员工的、工资合计、部门人数要求：部门工资合计要 大于 5000，并且按照部门的人数排序。SELECT e.deptno,SUM(e.sal),COUNT(e.empno) cFROM emp e,(SELECT deptno FROM dept WHERE dname LIKE %S%) dWHERE e.deptno=d.deptnoGROUP BY e.deptno HAVING SUM(sal)5000ORDER BY c;21、给任职日期超过10年的人加薪10%UPDATE emp SET sal=sal*1.1 WHERE MONTHS_BETWEEN(sysdate,hiredate)/1210 ;5、总结1、巩固子查询、多表查询、分组统计的使用2、SQL语句的标准结构3、可以使用SQL进行数据的增加、修改、删除操作6、预习任务1、创建和管理表（重要）2、约束（重要）3、数据的集合操作（重要）4、视图（重要）5、序列（重要）6、用户管理（了解）