Oracle二

Oracle（二）作者:IT电子教育门户来源:http://www.mldn.cn最后修改于：2008-11-49:21:00点击开始打印页面地址是:http://www.mldn.cn/articleview/2008-11-4/article_view_2869.htm1、课程名称：OracleORACLE的经典的查询案例。多表查询、分组统计、子查询。数据库更新操作2、知识点2.1、上次课程的主要知识点1、SQL的基础语法SELECT要显示的列、内容或全部column|expression|*FROM表名称别名WHERE多个查询条件ORDERBY排序的列DESC|ASC2、重点函数：NVL、DECODE、字符串、to_char、to_number、to_date2.2、本次预计讲解的知识点1、多表查询，SQL:1999语法对多表查询的支持2、分组统计及统计函数3、子查询，并结合多表查询，分组统计做复杂查询3、具体内容3.1、多表查询一张以上的表一起查询，就称为多表查询。例如：要一起查询雇员表和部门表的全部信息。那么此时就可以按照以下的语法进行编写：SELECT*FROMemp,dept;但是如果直接运行以上的程序会发现问题：发现显示的记录有56条？但是实际上雇员表一共才有14条。例如：SELECTCOUNT(*)FROMemp;SELECTCOUNT(*)FROMdept;分别求出雇员表和部门表，发现雇员表中只有14条记录，而部门表只有4条记录，但是如果两个放在了一起，则会出现56条记录。即：14×4=56？那么对于以上的情况，在数据库中有一个名称——笛卡尔积。但是此结果并不是用户所需要的，用户所需要看到的就是全部的雇员信息或部门信息。只有14个雇员就应该只显示14条记录。那么此时就可以通过对关联列的条件限制来去除笛卡尔积。在雇员表中有一个deptno的字段。在部门表也有一个deptno的字段。即：可以通过以下的方式去掉所有的重复数据：SELECT*FROMemp,deptWHEREemp.deptno=dept.deptno;以上的程序已经符合查询的标准，但是存在问题，如果现在要查询的表名称过长，则在编写查询列的时候肯定要重复写上表名称。所以一般在多表查询的时候都习惯于为表起一个别名。例如：现在要求查询出雇员的编号、雇员姓名、部门的编号、部门名称、部门位置：SELECTe.empno,e.ename,d.deptno,d.dname,d.locFROMempe,deptdWHEREe.deptno=d.deptno;例如：要求查询出每个雇员的姓名、雇员的工作、雇员的直接上级领导的姓名。SELECTe.ename,e.job,m.enameFROMempe,empmWHEREe.mgr=m.empno;例如：要求查询出每个雇员的姓名、工作、领导姓名、部门名称、雇员工资。SELECTe.ename,e.job,m.ename,d.dname,e.salFROMempe,empm,deptdWHEREe.deptno=d.deptnoANDe.mgr=m.empno;思考：现在要求查询出每个雇员的姓名、工资、部门名称、工资在公司的等级（salgrade）、领导的姓名，领导的工资在公司的等级。SELECTe.ename,e.sal,d.dname,s1.grade,m.ename,s2.gradeFROMempe,empm,deptd,salgrades1,salgrades2WHEREe.mgr=m.empnoANDe.deptno=d.deptnoANDe.salBETWEENs1.losalANDs1.hisalANDm.salBETWEENs2.losalANDs2.hisal;进一步思考：现在要求按照以下的样式显示工资等级：•1：第5等工资•2：第4等工资•3：第3等工资•4：第2等工资•5：第1等工资SELECTe.ename,e.sal,d.dname,decode(s1.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资'),m.ename,decode(s2.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资')FROMempe,empm,deptd,salgrades1,salgrades2WHEREe.mgr=m.empnoANDe.deptno=d.deptnoANDe.salBETWEENs1.losalANDs1.hisalANDm.salBETWEENs2.losalANDs2.hisal;观察以下SQL的查询结果：SELECT*FROMdept;发现部门表中一共有四个部门，但是在执行以下SQL语句之后再观察：SELECT*FROMempe,deptdWHEREe.deptno=d.deptno;发现在以上的查询结果中，并没有显示出40部门的信息，那么如果此时必须显示出40部门的话，则必须对表的连接进行设置——左连接、右连接。SELECT*FROMempe,deptdWHEREe.deptno(+)=d.deptno;以上代码在查询条件处的等号左边加入了一个“(+)”，那么此时表示的是右连接，以DEPT表为准。如果现在把“(+)”放到右边，则表示左连接，以emp表为准。SELECT*FROMempe,deptdWHEREe.deptno=d.deptno(+);默认的是以左边为标准进行连接的，属于左连接。例如：之前讲解的列出每一个员工的员工姓名和领导名称的时候，并没有列出“king”。如果现在要把“king”列出来，则必须进行连接处理。SELECTe.ename,e.job,m.enameFROMempe,empmWHEREe.mgr=m.empno(+);以上为表连接在实际中的标准操作，但是对于SQL语法来说，对表的连接又有另外一套语法。交叉连接（CROSSJOIN）：产生笛卡尔积SELECT*FROMempCROSSJOINdept;自然连接（NATURALJOIN）：自动进行关联字段的匹配SELECT*FROMempNATURALJOINdept;USING子句：直接指定关联列即可SELECTe.ename,e.sal,deptno,d.locFROMempeJOINdeptdUSING(deptno)WHEREdeptno=20;ON子句：用户自己指定关联的条件SELECTe.empno,e.ename,e.deptno,d.deptno,d.locFROMempeJOINdeptdON(e.deptno=d.deptno);左连接（左外连接）、右连接（右外连接）：LEFTJOIN，RIGHTJOINSELECTe.ename,d.deptno,d.locFROMempeRIGHTOUTERJOINdeptdON(e.deptno=d.deptno);SELECTe.ename,d.deptno,d.locFROMempeLEFTOUTERJOINdeptdON(e.deptno=d.deptno);3.2、组函数和分组统计分组：例如：把所有男生分为一组，所有女生分为一组。之后对每组的数据进行统计。这样的函数就称为分组函数。3.2.1、组函数常用的分组函数：•COUNT：求出全部的记录数•MAX：求出一个组中的最大值•MIN：求出最小值•AVG：求平均值•SUM：求和1、COUNT函数：SELECTCOUNT(empno)FROMemp;2、MAX、MIN：求最大和最小值，针对于数字的应用上。•求出所有员工的最低工资：SELECTMIN(sal)FROMemp;•求出所有员工的最高工资：SELECTMAX(sal)FROMemp;3、求和及平均值•求出所有员工的总工资：SELECTSUM(sal)FROMemp;•求出所有员工的平均工资：SELECTAVG(sal)FROMemp;3.2.2、分组统计分组统计使用GROUPBY进行分组，后面要跟上分组的条件，即：GROUPBY分组条件（按那个字段）例如：求出每个部门的雇员数量。只能按deptno分组。SELECTdeptno,COUNT(empno)FROMempGROUPBYdeptno;例如：求出每个部门的平均工资SELECTdeptno,AVG(sal)FROMempGROUPBYdeptno;注意点：观察以下代码：SELECTdeptno,COUNT(empno)FROMemp;以上代码不能够正确执行，因为：1、程序中如果使用分组函数，则有两种情况：必须有groupby，这样才能跟上分组的条件。直接使用分组函数查询：SELECTCOUNT(emp)FROMemp;2、在使用分组函数的时候，不能出现分组函数和分组条件之外的字段。例如：按部门分组，要求显示出部门的名称，及每个部门的员工数。SELECTe.deptno,d.dname,COUNT(e.empno)FROMempe,deptdGROUPBYe.deptno;在以上的SQL语句之中“d.dname”并不属于分组的条件或是分组的函数，所以不能使用。例如：要求显示出平均工资大于2000的部门编号和平均工资。SELECTdeptno,AVG(sal)FROMempWHEREAVG(sal)2000GROUPBYdeptno;出现了以下错误：SELECTdeptno,AVG(sal)FROMempWHEREAVG(sal)2000GROUPBYdeptno*第1行出现错误:ORA-00934:此处不允许使用分组函数因为分组函数不能出现在where语句之中，所以此时，如果要对分组的条件进行过滤，则必须单独编写HAVING子句，HAVING的功能与WHERE一样，只是条件是作为分组的条件出现。所以以上代码可以修改为：SELECTdeptno,AVG(sal)FROMempGROUPBYdeptnoHAVINGAVG(sal)2000;完整的SQL语句格式：SELECT[DISTINCT]*|COLUMN|EXPRESIONFROM表1别名1,表2别名2,….在此处不能够出现各种分组函数WHERE多个查询条件GROUPBY分组条件HAVING可以出现各分组函数分组查询条件ORDERBY排序[ASC|DESC]例如：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列：1、显示全部的非销售人员信息：SELECT*FROMempWHEREjob'SALESMAN';2、按工作分组，同时求出工资的总和SELECTjob,SUM(sal)FROMempWHEREjob'SALESMAN'GROUPBYjob;3、对分组的条件进行限制，工资综合大于5000SELECTjob,SUM(sal)FROMempWHEREjob'SALESMAN'GROUPBYjobHAVINGSUM(sal)5000;4、按工资的升序排列SELECTjob,SUM(sal)suFROMempWHEREjob'SALESMAN'GROUPBYjobHAVINGSUM(sal)5000ORDERBYsu;简单的原则：列上有重复内容的时候才可以进行分组。注意：组函数可以嵌套使用，但在组函数嵌套使用的时候不能再出现分组条件的查询语句：求出平均工资最高的部门•错误的代码：SELECTdeptno,MAX(AVG(sal))FROMempGROUPBYdeptno;•正确的代码：SELECTMAX(AVG(sal))FROMempGROUPBYdeptno;3.3、子查询子查询：在一个查询语句之后包含了另外一个查询语句，称为子查询。例如：要求查询出比7654工资高的全部雇员的信息。此时，就必须先查询出7654的工资，之后把这个工资作为条件继续取出其他的内容：SELECT*FROMe