《管理信息化二》由会员分享，可在线阅读，更多相关《管理信息化二（11页珍藏版）》请在金锄头文库上搜索。
1、Oracle（二）作者:IT电子教育门户来源:http:/最后修改于：2008-11-4 9:21:00点击开始打印页面地址是:http:/ 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、多表查询，
2、SQL:1999语法对多表查询的支持2、分组统计及统计函数3、子查询，并结合多表查询，分组统计做复杂查询3、具体内容3.1、多表查询一张以上的表一起查询，就称为多表查询。例如：要一起查询雇员表和部门表的全部信息。那么此时就可以按照以下的语法进行编写：SELECT * FROM emp,dept ;但是如果直接运行以上的程序会发现问题：发现显示的记录有56条？但是实际上雇员表一共才有14条。例如：SELECT COUNT(*) FROM emp ;SELECT COUNT(*) FROM dept;分别求出雇员表和部门表，发现雇员表中只有14条记录，而部门表只有4条记录，但是如果两个放在了一起，
3、则会出现56条记录。即：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.jo
5、b,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
6、.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
7、,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部门的
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”。如果现在要把“k
9、ing”列出来，则必须进行连接处理。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.
10、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.e
11、name,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 M
12、IN(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 ;例如：求出每个部门的平均工资S
13、ELECT 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
14、 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
