网站建设资讯

NEWS

网站建设资讯

ORACLE百例试炼二

   Oracle系列《二》:多表复杂查询和事务处理

创新互联公司专注于企业营销型网站、网站重做改版、富民网站定制设计、自适应品牌网站建设、H5网站设计电子商务商城网站建设、集团公司官网建设、外贸网站制作、高端网站制作、响应式网页设计等建站业务,价格优惠性价比高,为富民等各大城市提供网站开发制作服务。

多表查询应该注意去除笛卡尔积,一般多个表时会为表起个别名

【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),及其领导的姓名所在公司的等级 

<1>先确定工资等级表的内容 

SQL> SELECT * FROM salgrade;


<2>查询每个雇员的姓名、工资、部门名称和工资在公司的等级 

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;


<3>查询其领导姓名及工资所在公司的等级 

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, 输出结果按月工资的合计升序排序

<1>按工作分组,求出非销售人员的月工资总和 

SQL> SELECT job,SUM(sal) FROM emp 

WHERE job<>'SALESMAN' GROUP BY job; 


<2>对分组条件进行限制,然后进行排序,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工资要高的全部雇员的信息 

<1>首先要查询雇员编号7654的工资 

SQL> SELECT sal FROM emp WHERE empno=7654;


<2>以上述条件的结果最后后续查询的依据 

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】要求查询 部门名称、部门员工数、部门平均工资,部门的最低收入雇员的姓名 

<1>查询部门员工数、部门平均工资 

SQL> SELECT deptno,COUNT(empno),AVG(sal) FROM emp 

GROUP BY deptno; 


<2>查询部门的名称,及最低收入雇员姓名,要进行表关联(子查询)

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(比最小大)、

SQL> SELECT * FROM emp 

WHERE sal 


ALL: >ALL(比最大要大)、


多行子查询

显示和10号部门从事相同岗位的雇员信息

SQL>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之后,数据永久保存在数据库中,在此之前,事务的操作都可以回滚


验证事务过程: 

<1>创建一张临时表,只包含部门10 

SQL> CREATE TABLE emp10 AS SELECT * FROM emp WHERE empno=10; 


<2>删除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"(办事员)的姓名及其部门名称,部门的人数 

<1>关联dept表 

SQL> SELECT e.ename,d.dname FROM emp e,dept d 

WHERE e.deptno=d.deptno and e.job='CLERK'; 


<2>使用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;


网页题目:ORACLE百例试炼二
当前链接:http://njwzjz.com/article/jdsepj.html