SQL语言多表查询、分组统计、子查询、数据表的更新操作、事务处理

SQL语言多表查询、分组统计、子查询、数据表的 更新操作、事务处理 3.1、多表查询 3.1.1、基本语法 但是在多表查询之前首先必须处理一个问题: 例如:现在求出雇员表中的总 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 数(14条记录) SELECT COUNT(*) FROM emp ; 例如:现在求出部门表的总记录数(4条记录) SELECT COUNT(*) FROM dept ; 所谓的多表查询就是直接在FROM语句之后加入若干张表,下面将emp和dept表进行多表查询 SELECT * FROM emp,dept ; 以上确实完成了两张表的联合查询,但是查询出来的结果是56条记录。 部门表的记录总数* 雇员表的记录总数= 56条记录。那么这样的结果在数据库中就称为笛卡尔积。 对于这样的结果明显不是最终查询者需要返回的结果,应该想办法去掉笛卡尔积。所以如果要使用多表查 询,则必须按照以下的语句形式进行编写: SELECT 字段 FROM 表1,表2 WHERE 将两张表的关联字段进行比较,去掉笛卡尔积 以emp和dept表为例 1、雇员表结构: No. 字段名称字段类型字段作用 1 EMPNO NUMBER(4) 表示的是雇员编号,长度为四位的整数 2 ENAME VARCHAR2(10) 雇员的姓名,使用字符串表示,字符串的长度最大为10 3 JOB VARCHAR2(9) 工作,字符串表示,最大长度为9 4 MGR NUMBER(4) 雇员的直接上级领导编号 5 HIREDATE DATE 雇佣日期 6 SAL NUMBER(7,2) 工资,工资长度一共是7位,其中整数占5位,小数占2位 7 COMM NUMBER(7,2) 奖金(佣金) 8 DEPTNO NUMBER(2) 部门编号 2、部门表结构: No. 字段名称字段类型字段作用 1 DEPTNO NUMBER(2) 雇员编号 2 DNAME VARCHAR2(14) 部门名称 3 LOC VARCHAR2(13) 部门位置 两张表中都存在deptno字段,一般在数据库建表的时候都会把关联字段的名称统一。 例如:使用关联字段消除掉之前多表查询的迪卡尔积。 SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno ; 但是如果一直按照以上的格式编写的话,对于表名称太长的情况时,如果在访问字段前还是使用表名称的 形式,会很麻烦。所以一般可以为一张表起一个别名。修改以上的范例: SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno ; 例如:要求查询出雇员的编号、雇员的姓名、工资、部门的名称及位置 SELECT e.empno,e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno ; 例如:要求查询出每个雇员的姓名、工作、雇员工资、领导姓名、领导工资 ? 如果要找到领导信息,则肯定需要emp表 ? 如果要找到雇员信息,则肯定需要emp表 ? 消除笛卡尔积的条件:雇员的管理者的编号= 雇员中的雇员编号 SELECT e.ename 雇员姓名,e.job 雇员工作,e.sal 雇员工资,m.ename 领导姓 名,m.sal 领导工资 FROM emp e,emp m WHERE e.mgr=m.empno ; 可以发现,本次查询是本表关联本表,那么这样的查询称为自身关联查询。 例如:要求查询出每个雇员的姓名、工作、雇员工资、部门名称、领导姓名、领导工资 ? 如果要找到领导信息,则肯定需要emp表 ? 如果要找到雇员信息,则肯定需要emp表 ? 如果要找到部门信息,则肯定需要dept表 SELECT e.ename 雇员姓名,e.job 雇员工作,e.sal 雇员工资,m.ename 领导姓 名,m.sal 领导工 资,d.dname 部门名称 FROM emp e,emp m,dept d WHERE e.mgr=m.empno AND e.deptno=d.deptno; 注意:在以上的查询中性能如何? 思考: 现在要求查询出每个雇员的姓名、工资、部门名称,雇员的工资及在公司的工资等级、领导的姓名、领导 的工资及工资在公司的工资等级。 salgrade:工资等级表 No. 字段名称字段类型字段作用 1 GRADE NUMBER 等级编号 2 LOSAL NUMBER 此等级的最低工资 3 HISAL NUMBER 此等级的最高工资 通过sal指定losal和hisal的范围来去除笛卡尔积 ? 部门表 ? 领导表:emp SELECT e.ename,e.sal,d.dname,e.sal,s.grade,m.ename,m.sal,sm.grade FROM emp e,dept d,emp m,salgrade s,salgrade sm WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno=d.deptno AND m.sal BETWEEN sm.losal AND sm.hisal AND e.mgr=m.empno ; 进一步扩展: 在查询完的工资等级进行显示的修改,修改要求如下: ? 1:“E等工资” ? 2:“D等工资” ? 3:“C等工资” ? 4:“B等工资” ? 5:“A等工资” 需要使用DECODE函数 SELECT e.ename,e.sal,d.dname,e.sal, DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资'), m.ename,m.sal,DECODE(sm.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资') FROM emp e,dept d,emp m,salgrade s,salgrade sm WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno=d.deptno AND m.sal BETWEEN sm.losal AND sm.hisal AND e.mgr=m.empno ; 3.1.2、SQL:1999语法(了解) 在SQL语句中提供了另外一套对与表关联查询的支持语法: SELECT table1.column,table2.column FROM table1 [CROSS JOIN table2]| [NATURAL JOIN table2]| [JOIN table2 USING(column_name)]| [JOIN table2 ON(table1.column_name=table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)]; 交叉连接(CROSS JOIN):专门产生笛卡尔积 SELECT * FROM emp CROSS JOIN dept ; 自然连接(NATUAL JOIN):自动进行关联字段的匹配 SELECT * FROM emp NATURAL JOIN dept ; ON子句;由用户自己指定关联的条件 SELECT * FROM emp JOIN dept ON(emp.deptno=dept.deptno) ; USING子句:直接指定关联的条件 SELECT * FROM emp JOIN dept USING(deptno) ; 3.1.3、左、右连接 现在先查询出全部雇员和部门的信息 SELECT * FROM emp e,dept d WHERE e.deptno = d.deptno ; 查询结果: 以上的结果中显示了三个部门,但是实际上部门有四个 select * from dept ; 那么为什么之前列出的所有部门中没有40部门,因为在雇员表中没有一个雇员属于40部门。因为匹配的结 果都是以雇员为主。以等号左边为准,实际上以上的多表查询,可以直接通过以下的形式表现: SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno(+) ; 也就是说,现在的代码以等号的左边为准,所以此连接称为左连接,如果现在以等号的右边为准呢?那么 就称为右连接: SELECT * FROM emp e,dept d WHERE e.deptno(+)=d.deptno ; 从程序运行结果可以发现,40部门出现了,那么意味着现在的匹配条件以等号右边为准,所以是右连接。 可以发现一个规律: ? (+)在等号的左边属于右连接 ? (+)在等号的右边属于左连接 同样SQL:1999语法也对左、右连接有所支持(了解) 例如:实现左连接 SELECT * FROM emp LEFT OUTER JOIN dept ON (emp.deptno=dept.deptno) ; 例如:实现右连接 SELECT * FROM emp RIGHT OUTER JOIN dept ON (emp.deptno=dept.deptno) ; 3.2.2、分组 在SQL语句中如果要想实现对数据的分组统计,则必须使用GROUP BY 子句,此时,完整的SQL语法如下: SELECT column | * FROM table1 als1,table2 als2 [WHERE conditions] [GROUP BY column] [ORDER BY column] 例如:求出每个部门的雇员数量 ? 应该按照部门编号进行分组 SELECT deptno,COUNT(*) FROM emp GROUP BY deptno ; 例如:求出每个部门的平均工资 SELECT deptno,AVG(sal) FROM emp GROUP BY deptno ; 以上已经完成了分组,但是对于分组统计中,是不能出现分组条件之外的字段。 例如:有以下一段SQL语句 SELECT deptno,AVG(sal),ename FROM emp GROUP BY deptno ; 一旦执行之后出现以下的错误提示: SELECT deptno,AVG(sal),ename 第1 行出现错误: ORA-00979: 不是GROUP BY 表达式 对于分组函数而言,可以单独使用,如果要连同其他字段一起查询,此字段必须是分组的字段,而且此语 句之中必须存在group by 子句: SELECT deptno,COUNT(*) FROM emp ; 出现以下错误: SELECT deptno,COUNT(*) 第1 行出现错误: ORA-00937: 不是单组分组函数 而如果只写一个分组函数,则不会有任何的问题。 例如:要求显示出平均工资大于2000的部门编号及平均工资 SELECT deptno,AVG(sal) FROM emp WHERE AVG(sal)>2000 GROUP BY deptno ; 语句执行后出现以下的错误: WHERE AVG(sal)>2000 第3 行出现错误: 在WHERE语句之中是不能出现分组函数的。只要是分组条件且此条件中要使用分组函数,就必须在HAVING 子句之中编写,与WHERE子句的功能一样,只是HAVING必须写在GROUP之后,没有GROUP BY绝对不能出现 HAVING。 SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000 ; 完整的语句格式: SELECT column | * FROM table1 als1,table2 als2 [WHERE conditions] [GROUP BY column [HAVING 分组条件]] [ORDER BY column] 例如:显示非销售人员的工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事相同工作雇员 的月工资合计大于5000,输出结果按月工资的合计升序排列。 1、显示全部非销售人员的信息(限定条件,需要在WHERE子句中编写) SELECT * FROM emp WHERE job<>'SALESMAN' ; 2、按雇员的工作分组,分组之后可以求出工资的总和 SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job; 3、对分组的条件进行过滤,求出月工资的总和大于5000 SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000; 4、按照工资的总和进行排序 SELECT job,SUM(sal) suF ROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000 ORDER BY su; 注意: 分组函数本身是允许嵌套的,但是,嵌套之后是不能出现分组条件的。 例如:求出平均工资最高的部门 A、错误的代码 SELECT deptno,MAX(AVG(sal)) FROM emp GROUP BY deptno ; 实际上以上的代码使用了分组函数的嵌套。 B、正确的代码 SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno ; 原则: 当列中存在重复的内容时,才可以进行分组。 3.3、子查询 例如:要求查询出工资比7566雇员的工资要高的全部雇员信息 1、确定出7566雇员的工资 SELECT sal FROM emp WHERE empno=7566 ; 2、求出工资大于7566的雇员 SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE empno=7566) ; 需要注意的是,子查询可以在任意的位置上编写:SELECT、FROM、WHERE、HAVING 子查询语法: SELECT 字段,[SELECT column_list FROM 表] 别名 FROM 表名称,[SELECT column_list FROM 表] 别名 WHERE 条件,[SELECT column_list FROM 表] 别名 GROUP BY 分组条件,[SELECT column_list FROM 表] 别名 HAVING 条件,[SELECT column_list FROM 表] 别名 对于自查询而言又分为三类: ? 单列子查询:返回的结果是一行一列 ? 单行子查询:一行多列,例如:一条完整的记录 ? 多行子查询:返回多条记录 例如:要求查询出工资比7654高,同时又与7788从事同一个工作的雇员信息 ? 查询出7654雇员的工资 SELECT sal FROM emp WHERE empno=7654 ; ? 查出7788的工作 SELECT job FROM emp WHERE empno=7788 ; 将以上的两条记录作为查询的条件 SELECT * FROM emp WHERE 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) ; 思考: 要求查询出:部门名称,部门的雇员数,部门的平均工资,部门中最低收入的雇员的姓名。部门中最低收入、雇员数、平均工资,按部门分组 SELECT deptno,MIN(sal) fROM emp GROUP BY deptno ; ? 多表关联及子查询 SELECT d.dname,e.ename,dm.avg,dm.min,dm.count FROM dept d,emp e,(SELECT deptno,AVG(sal) avg,MIN(sal) min,COUNT(empno) count FROM emp GROUP BY deptno) dm WHERE d.deptno=e.deptno AND d.deptno=dm.deptno AND e.sal=dm.min ; 由此可见,子查询可以在任意的位置上出现,如果在FROM语句之后,实际上此查询就是表示出了一张临时 表,临时表的访问要有别名。 例如:查询平均工资最底的工作名称及其平均工资 SELECT job,AVG(sal) FROM emp GROUP BY job HAVING AVG(sal)=(SELECT MIN(AVG(sal)) FROM emp GROUP BY job) ; 在子查询中还提供了以下的比较运算符: ? IN:指定一个范围的内容 ? ANY ? ALL 1、IN操作符 例如:要求查询出各个部门最低工资的雇员信息 ? 每个部门的最低工资是多个,之后根据工资查询的时候实际上就是应该指定出一个具体的范围。 SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno) ; 2、ANY操作符:=ANY、>ANY、ANY:比最小的值要大 SELECT * FROM emp WHERE sal>ANY(SELECT MIN(sal) FROM emp GROUP BY deptno) ; 实际上ANY是要与里面的每一个内容进行比较的。 3、ALL操作符 ? ALL:比最大的值要大 SELECT * FROM emp WHERE sal>ALL(SELECT MIN(sal) FROM emp GROUP BY deptno) ; 以上实际都是针对于多行子查询的应用。 多列子查询:一次性返回多个列,如果要想比较,则必须多个列一起比较 SELECT * FROM emp WHERE (sal,NVL(comm,0)) IN (SELECT sal,NVL(comm,0) FROM emp WHERE deptno=20) ; 4、查询练习 ? 求出那个部门的雇员数量 SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno ; ? 求出数量大于1的所有部门 SELECT d.*,dc.c FROM dept d,(SELECT deptno,COUNT(empno) c FROM emp GROUP BY deptno) dc WHERE d.deptno=dc.deptno; 2、列出薪金比“SMITH”多的所有员工。 SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH') ; 3、列出所有员工的姓名及其直接上级的姓名。 SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr=m.empno ; SELECT ename, (SELECT ename FROM emp WHERE empno=e.mgr) FROM emp e; 4、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称。 ? 列出受雇日期早于其直接上级的所有员工的编号,姓名 SELECT e.empno,e.ename FROM emp e,emp m WHERE e.mgr=m.empno AND e.hiredate1500 ; ? 雇员的人数 SELECT job,COUNT(*) FROM emp WHERE job IN (SELECT job FROM emp GROUP BY job HAVING MIN(sal)>1500) GROUP BY job ; 8、列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname LIKE '%SALES%') ; 9、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级。 ? 求出工资高于平均工资的雇员姓名,部门编号 SELECT e.ename,e.deptno FROM emp e WHERE sal>(SELECT AVG(sal) FROM emp) ; ? 求出部门的名称及上级领导信息 SELECT e.ename,e.deptno,d.dname,m.ename FROM emp e,dept d,emp m WHERE e.sal>(SELECT AVG(sal) FROM emp) AND d.deptno=e.deptno AND e.mgr=m.empno ; ? 工资的等级 SELECT e.ename,e.deptno,d.dname,m.ename,s.grade FROM emp e,dept d,emp m,salgrade s WHERE e.sal>(SELECT AVG(sal) FROM emp) AND d.deptno=e.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal ; 10、列出与“SCOTT”从事相同工作的所有员工及部门名称。 SELECT e.ename,d.dname FROM emp e,dept d WHERE e.job=(SELECT job FROM emp WHERE ename='SCOTT') AND e.deptno=d.deptno AND e.ename<>'SCOTT' ; 11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 ? 多列自查询,IN操作 SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) ; 12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。 ? 30部门的所有雇员薪金 SELECT sal FROM emp WHERE deptno=30 ; ? 列出大于:>ALL SELECT e.ename,e.sal,d.dname FROM emp e,dept d WHERE e.sal>ALL(SELECT sal FROM emp WHERE deptno=30) AND e.deptno=d.deptno ; 13、列出在每个部门工作的员工数量、平均工资和平均服务期限。 SELECT COUNT(empno),AVG(sal),ROUND(AVG(MONTHS_BETWEEN(sysdate,hi redate))/12) FROM emp GROUP BY deptno ; 14、列出所有员工的姓名、部门名称和工资。 SELECT e.ename,d.dname,e.sal FROM emp e.dept d WHERE e.deptno=d.deptno 15、列出所有部门的详细信息和部门人数。 SELECT d.deptno,d.dname,d.loc,dc.c FROM dept d,(SELECT deptno,COUNT(*) c FROM emp GROUP BY deptno) dc WHERE d.deptno=dc.deptno ; 16、列出各种工作的最低工资及从事此工作的雇员姓名。 SELECT e.ename,es.job,es.sal FROM emp e,(SELECT job,MIN(sal) sal FROM emp GROUP BY job) es WHERE e.job=es.job AND e.sal=es.sal ; 17、列出各个部门的MANAGER(经理)的最低薪金。 SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY job ; 18、列出所有员工的年工资,按年薪从低到高排序。 SELECT (sal+NVL(comm.,0))*12 income FROM emp ORDER BY income DESC 19、查出某个员工的上级主管,并要求出这些主管中的薪水超过3000 SELECT * FROM emp e,emp m WHERE e.mgr=m.empno AND m.sal>3000 ; 20、求出部门名称中,带…S?字符的部门员工的、工资合计、部门人数 SELECT d.deptno,NVL(SUM(sal),0),COUNT(empno) FROM emp e,dept d WHERE e.deptno(+)=d.deptno AND d.dname LIKE '%S%' GROUP BY d.deptno ; 21、给任职日期超过10年的人加薪10% UPDATE emp sal=sal*1.1 WHERE MONTHS_BETWEEN(sysdate,hiredate)/12>10 ;