《管理信息化二》由会员分享,可在线阅读,更多相关《管理信息化二(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