Oracle二xvm.docx

Oracle（二）作者:IT电子教育门户来源:最后修改于：2008-11-4 9:21:00点击开始打印页面地址是: 1、课程名称：Oracle ORACLE的经典的查询案例。 多表查询、分组统计、子查询。 数据库更新操作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、 分组统计及统计函数 3、 子查询，并结合多表查询，分组统计做复杂查询3、具体内容3.1、多表查询 一张以上的表一起查询，就称为多表查询。例如：要一起查询雇员表和部门表的全部信息。那么此时就可以按照以下的语法进行编写：SELECT * FROM emp,dept ; 但是如果直接运行以上的程序会发现问题：发现显示的记录有56条？但是实际上雇员表一共才有14条。例如：SELECT COUNT(*) FROM emp ;SELECT COUNT(*) FROM dept; 分别求出雇员表和部门表，发现雇员表中只有14条记录，而部门表只有4条记录，但是如果两个放在了一起，则会出现56条记录。即：14 × 4 = 56 ？那么对于以上的情况，在数据库中有一个名称 笛卡尔积。 但是此结果并不是用户所需要的，用户所需要看到的就是全部的雇员信息或部门信息。只有14个雇员就应该只显示14条记录。那么此时就可以通过对关联列的条件限制来去除笛卡尔积。 在雇员表中有一个deptno的字段。 在部门表也有一个deptno的字段。 即：可以通过以下的方式去掉所有的重复数据：SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno ; 以上的程序已经符合查询的标准，但是存在问题，如果现在要查询的表名称过长，则在编写查询列的时候肯定要重复写上表名称。所以一般在多表查询的时候都习惯于为表起一个别名。 例如：现在要求查询出雇员的编号、雇员姓名、部门的编号、部门名称、部门位置：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.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 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 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部门的信息，那么如果此时必须显示出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”列出来，则必须进行连接处理。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 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.deptno,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 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,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 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语句之中，所以此时，如果要对分组的条件进行过滤，则必须单独编写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 例如：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$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;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、子查询 子查询：在一个查询语句之后包含了另外一个查询语句，称为子查询。 例如：要求查询出比7654工资高的全部雇员的信息。 此时，就必须先查询出7654的工资，之后把这个工资作为条件继续取出其他的内容：SELECT * FROM empWHERE sal>(SELECT sal FROM emp WHERE empno=7654) ;子查询在操作中又分为三类：  单列子查询：返回的结果是一列一个内容  单行子查询：返回一条完整的记录  多行子查询：返回多条记录例如：要求查询出工资比7654高，同时与7788从事相同工作的全部雇员信息。  7654的工资：SELECT sal FROM emp WHERE empno=7654  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) ;例如：查询出雇员工资大于公司平均工资的雇员信息SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp) ;思考： 要求查询出：部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名，和最高收入雇员的姓名。 程序需要两张表关联：emp、dept 1、 如果要想求出员工数量，则肯定分组，分组肯定使用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 ; 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 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) edWHERE 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 <= 10) WHERE RN > 5;例如：查询平均工资最低的工种的名称及平均工资：SELECT job,avg(sal)FROM emp eGROUP 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 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 <ANY (SELECT MIN(sal) FROM emp GROUP BY deptno) ;ALL操作：  >ALL：比里面最大的值要大SELECT ename,sal,deptnoFROM empWHERE sal >ALL (SELECT MIN(sal) FROM emp GROUP BY deptno) ;  <ALL：比里面最小的值要小SELECT ename,sal,deptnoFROM empWHERE sal <ALL (SELECT MIN(sal) FROM emp GROUP BY deptno) ;多列子查询： 同时返回多个列SELECT ename,deptno,sal,commFROM empWHERE (sal,NVL(comm,-1) IN (SELECT sal,NVL(comm,-1) FROM emp WHERE deptno=20)  ;3.4、数据库更新操作 数据库的主要操作分为两种：   数据库的查询操作：SELECT   数据库的更新操作：INSERT、UPDATE、DELETE下面为了保存表的完整性，现在先将emp表复制一份 myemp：CREATE TABLE myemp AS SELECT * FROM emp ; 此时myemp表中就有了与emp表同样的结构和数据。3.4.1、添加数据 添加数据的语法：INSERT INTO 表名称 (字段名称,) VALUES (值,) ;为myemp表中添加一条新的记录：  按标准做法完成（推荐）：INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7899,'张三','清洁工',7369,'13-11月-1995',9000,0,40) ; 已经很明确的写出来到底需要那几列。  可以在插入的时候不写列的名称，而直接插入具体的值：INSERT INTO myemp VALUES (7845,'李四','工人',7369,'13-11月-1998',100,1,40) ;例如：现在希望增加一个雇员，但是此雇员暂时没有领导，没有奖金：  第一种做法：INSERT INTO myemp(empno,ename,job,hiredate,sal,deptno) VALUES (7679,'王五','清洁工','13-11月-1995',9000,40) ;  第二种做法：INSERT INTO myemp VALUES (7845,'李四','工人',null,'13-11月-1998',100,null,40) ;注意： 日期，在插入的时候必须与oracle本身的日期格式一样，否则无法插入，可是现在给出的是：“1995-06-25”这样的日期格式，那该如何插入？INSERT INTO myemp(empno,ename,job,hiredate,sal,deptno) VALUES (7673,'王五','清洁工',to_date('1995-06-25','yyyy-mm-dd'),9000,40) ; 插入数据的时候，也可以基于另外一张表进行数据的插入。例如：现在把emp表复制一份，但是不要里面的数据。写一个永远不可能成立的条件CREATE TABLE emp1 AS SELECT * FROM emp WHERE 1=2 ; 现在把emp表中的全部经理的雇员信息插入到emp1表中。INSERT INTO emp1 SELECT * FROM emp WHERE job='MANAGER' ;3.4.2、修改数据 UPDATE可以修改数据，语法如下：   UPDATE 表名称 SET 修改全部à要修改的字段=新值,.      à   UPDATE 表名称 SET 要修改的字段=新值,. WHERE 修改条件  修改局部 成批修改à例如：现在将myemp表中的所有雇员的佣金修改为1000。UPDATE myemp SET comm=1000 ;例如：将JONES的工资修改为5000UPDATE emp1 SET sal=5000 WHERE empno=(SELECT empno FROM emp1 WHERE ename='JONES') ; 在更新的时候如果不直接写入具体的条件，也可以编写子查询进行搜索。但是一般情况下按ID更新是最普遍的做法：UPDATE emp1 SET sal=5000 WHERE empno=7566 ;3.4.3、删除数据 从数据库中把一条数据永远的消失掉。语法：  DELETE 如果不加条件，则表示全部删除。àFROM 表名称 WHERE 条件 例如：现在要求清空emp1表中的全部记录。DELETE FROM emp1 ;例如：删除myemp表中7673雇员：DELETE FROM myemp WHERE empno=7673 ;例如：删除雇员编号为：7845、7679的雇员DELETE FROM myemp WHERE empno IN(7845,7679) ;3.4.4、事务处理 现在在此用户下有myemp表。Myemp表中有数据： 现在在第一个session中把一张表的数据全部删除之后，发现第二个session里依然可以找到全部的内容，也就是说第一个session中的内容没有被真正删除。 因为在oracle中一个session中的所有操作并不会立刻反映到数据库的真实记录之中，而会把所有的操作放到缓存之中，如果用户操作失误，则可以进行恢复。在oracle中提供了两个最主要的事务命令：  rollback：回滚。撤消之前的全部操作  commit：提交。提交全部的更新操作事务的含义：所有的操作要么全部成功，要么全部失败。4、查询练习1、列出至少有一个员工的所有部门。SELECT COUNT(empno) FROM emp GROUP BY deptno HAVING COUNT(empno)>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、 列出所有员工的姓名及其直接上级的姓名。  自身关联SELECT 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.hiredate<m.hiredate ;5、 列出部门名称和这些部门的员工信息（总数），同时列出那些没有员工的部门。SELECT d.deptno,d.dname,dd.cFROM dept d,(SELECT deptno dno,COUNT(empno) c FROM emp GROUP BY deptno HAVING COUNT(empno)>1) dd WHERE d.deptno=dd.dno(+);6、 列出所有“CLERK”（办事员）的姓名及其部门名称。SELECT e.ename,d.dnameFROM dept d,emp eWHERE d.deptno=e.deptno AND e.job='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”从事相同工作的所有员工。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、列出在每个部门工作的员工数量、平均工资和平均服务期限。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 FROM 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、查出某个员工的直接上级主管，并要求出这些主管中的薪水超过3000  (用一条sql语句解决) SELECT e.ename,m.enameFROM emp e,emp mWHERE e.mgr=m.empno AND m.sal>3000 ;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)/12>10 ;5、总结1、 巩固子查询、多表查询、分组统计的使用2、 SQL语句的标准结构3、 可以使用SQL进行数据的增加、修改、删除操作6、预习任务1、 创建和管理表（重要）2、 约束（重要）3、 数据的集合操作（重要）4、 视图（重要）5、 序列（重要）6、 用户管理（了解）