ORACLE百例试炼二

ameimeng

　　Oracle系列《二》：多表复杂查询和事务处理
　　多表查询应该注意去除笛卡尔积，一般多个表时会为表起个别名
　　【1】要求查询雇员的编号、姓名、部门编号、部门名称及部门位置
　　SQL> SELECT   e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d
　　WHERE e.deptno = d.deptno;
　　【2】要求查询每个雇员的姓名、工作、雇员的直接上级领导的姓名(表自关联)
　　SQL> SELECT e.ename,e.job,m.ename FROM emp e,emp m
　　WHERE e.mgr = m.empno;
　　【3】对【2】进行扩充，将雇员所在部门名称同时列出
　　SQL> SELECT e.ename,e.job,m.ename,d.dname FROM emp e,emp m,dept d
　　WHERE e.mgr = m.empno AND e.deptno=d.deptno;
　　【4】查询每个雇员的姓名、工资、部门名称，工资在公司的等级(salgrade)，及其领导的姓名所在公司的等级
　　先确定工资等级表的内容
　　SQL> SELECT * FROM salgrade;
　　查询每个雇员的姓名、工资、部门名称和工资在公司的等级
　　SQL> SELECT e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s
　　WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;
　　查询其领导姓名及工资所在公司的等级
　　SQL> SELECT
　　e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade  FROM emp e,dept d,salgrade s,emp m,salgrade ms
　　WHERE e.deptno = d.deptno
　　AND e.sal BETWEEN s.losal AND s.hisal
　　AND e.mgr = m.empno
　　AND m.sal BETWEEN ms.losal AND ms.hisal;
　　【5】左连接与右连接的概念，"+"在等号左边表示右连接，反之，左连接
　　查询雇员的编号、姓名及其领导的编号、姓名
　　SQL> SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m
　　WHERE e.mgr = m.empno(+); 就发现将KING的那条记录也连过来了
　　SQL1999语法中有如下几种连接(了解)
　　1、交叉连接CROSS JOIN，产生笛卡尔积
　　SQL> SELECT * FROM emp CROSS JOIN dept;
　　2、自然连接NATURAL JOIN,自动进行关联字段的匹配
　　SQL> SELECT * FROM emp NATURAL JOIN dept;
　　3、使用USING子句，直接关联操作列
　　SQL> SELECT * FROM emp JOIN dept USING(deptno) WHERE deptno=30;
　　4、使用ON子句，用户自己编写的条件
　　SQL> SELECT * FORM emp JOIN dept ON(emp.deptno = dept.deptno) WHERE deptno=30;
　　5、左连接(左外连接、LEFT (OUTER) JOIN)、右连接(右外连接、RIGHT (OUTER) JOIN)
　　组函数及分组统计
　　1、COUNT():求出全部记录数
　　2、MAX()：求出一组中最大值
　　3、MIN():求出最小值
　　4、AVG()：求出平均值
　　5、SUM()：求和
　　【1】求出每个部门的雇员数量
　　SQL> SELECT deptno,count(empno) FROM emp
　　GROUP BY deptno;
　　【2】按部门分组，并显示部门的名称，及每个部门的员工数
　　SQL> SELECT d.dname,COUNT(e.empno) FROM emp e,dept d
　　WHERE e.deptno=d.deptno GROUP BY d.dname;
　　【3】要求显示平均工资大于2000的部门编号和平均工资
　　SQL> SELECT deptno,AVG(sal) FROM emp
　　WHERE AVG(sal)>2000 GROUP BY deptno;
　　出错，WHERE子句中不能出现分组函数的条件，要使用HAVING子句 上述语句应该改为如下
　　SQL> SELECT deptno,AVG(sal) FROM emp
　　GROUP BY deptno
　　HAVING AVG(sal)>2000
　　【4】显示非销售人员工作名称以及从事同一工作雇员的月工资总和，
　　并且要求从事同一工作的雇员月工资合计大于$5000, 输出结果按月工资的合计升序排序
　　按工作分组，求出非销售人员的月工资总和
　　SQL> SELECT job,SUM(sal) FROM emp
　　WHERE job'SALESMAN' GROUP BY job;
　　对分组条件进行限制，然后进行排序,HAVING子句不能使用别名
　　SQL> SELECT job,SUM(sal)  totalSal  FROM emp
　　WHERE job'SALESMAN' GROUP BY job
　　HAVING SUM(sal) > 5000 ORDER BY totalSal;
　　【5】分组函数可以嵌套使用，但是在SELECT列中就不能再出现该分组条件的列名了
　　SQL> SELECT deptno,MAX(AVG(sal)) FROM emp
　　GROUP BY deptno;
　　出错！修改如下
　　SQL> SELECT MAX(AVG(sal)) FROM emp
　　GROUP BY deptno;
　　【6】查询出比7654工资要高的全部雇员的信息
　　首先要查询雇员编号7654的工资
　　SQL> SELECT sal FROM emp WHERE empno=7654;
　　以上述条件的结果最后后续查询的依据
　　SQL> SELECT * FROM emp
　　WHERE sal>(SELECT sal FROM emp WHERE empno=7654);
　　子查询在操作中分为以下三类：
　　1、单列子查询：返回的结果是一列的内容
　　2、单行子查询：返回多个列，也可能是一条记录
　　3、多行子查询：返回多个记录
　　【1】要求查询工资比7654高，同时与7788从事相同工作的全部雇员
　　SQL> SELECT * FROM emp
　　WHERE sal>(SELECT sal FROM emp WHERE empno=7654)
　　AND job=(SELECT job FROM emp WHERE empno=7788);
　　【2】要求查询 部门名称、部门员工数、部门平均工资，部门的最低收入雇员的姓名
　　查询部门员工数、部门平均工资
　　SQL> SELECT deptno,COUNT(empno),AVG(sal) FROM emp
　　GROUP BY deptno;
　　查询部门的名称，及最低收入雇员姓名，要进行表关联(子查询)
　　SQL> SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,(
　　SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min  FROM emp
　　GROUP BY deptno) ed, emp e
　　WHERE d.deptno=ed.deptno AND e.sal = ed.min;
　　若上述存在两个最低工资的情况，则会出错，在子查询中存在以下3种查询的操作符号
　　IN：指定一个查询范围，例如查询每个部门的最低工资(返回值有多个)
　　SQL> SELECT * FROM emp
　　WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);
　　ANY：=ANY(与IN操作一样)、>ANY(比最小大)、 SELECT * FROM emp
　　WHERE sal ALL(比最大要大)、select *  from emp where job in (select  job  from emp  where  deptno=10);
　　数据库更新操作INSERT、UPDATE、DELETE
　　【1】复制一张表，例如复制EMP表为MYEMP
　　SQL> CREATE TABLE MYTEMP AS SELECT * FROM emp;
　　【2】将编号为7899的雇员的领导取消
　　SQL> UPDATE myemp SET mgr=null WHERE empno=7899;
　　【3】更新时，一定要注意不能批量更新(加上WHERE子句)，多列更新例子如下
　　SQL> UPDATE myemp SET mgr=null,comm=null WHERE empno IN(7369,8899);
　　【4】删除掉全部领取奖金的雇员
　　SQL> DELECT FROM emp WHERE comm is NOT NULL;
　　事务处理 ACID
　　A：Atomicity   原子性：事务中的操作或者都完成，或者都取消
　　C：Consistency 一致性：事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况
　　I：Isolation   隔离性：当前的事务与其他未完成的事务是隔离的
　　D：Durability  持久性：在COMMIT之后，数据永久保存在数据库中，在此之前，事务的操作都可以回滚
　　验证事务过程：
　　创建一张临时表，只包含部门10
　　SQL> CREATE TABLE emp10 AS SELECT * FROM emp WHERE empno=10;
　　删除emp10中的7782雇员
　　SQL> DELETE FROM emp10 WHERE empno=7782;
　　再打开另一个窗口，发现数据还存在，此时如果可以使用以下的两种命令进行事务处理
　　COMMIT 和 ROLLBACK 提交事务和回滚事务
　　SQL查询练习
　　【1】列出至少一个员工的所有部门
　　SQL> SELECT d.*,ed.cou FROM dept d,(
　　SELECT deptno,COUNT(empno) cou FROM emp  GROUP BY deptno
　　HAVING COUNT(empno) > 1
　　) ed
　　WHERE d.deptno=ed.deptno;
　　【2】列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
　　SQL> SELECT d.deptno,d.dname,e.empno,e.ename
　　FROM dept d,emp e  WHERE d.deptno = e.deptno(+);
　　【3】列出所有"CLERK"(办事员)的姓名及其部门名称，部门的人数
　　关联dept表
　　SQL> SELECT e.ename,d.dname FROM emp e,dept d
　　WHERE e.deptno=d.deptno and e.job='CLERK';
　　使用GROUP BY 完成部门分组人数
　　SQL> SELECT e.ename,d.dname,ed.cou FROM emp e,dept d,
　　(  SELECT deptno,COUNT(empno) cou FROM emp  GROUP BY deptno) ed
　　WHERE job='CLERK' AND e.deptno=d.deptno AND ed.deptno=e.deptno;