首页 > 代码库 > Oracle多表查询与数据更新
Oracle多表查询与数据更新
2.1 多表查询
2.1.1 多表查询的基本语法（重点）
多表查询语法如下：
|
SELECT {DISTINCT} *|查询列1 别名1，查询列2 别名2 FROM 表名称1 别名1，表名称2 别名2 {WHERE 条件（s）} {ORDER BY 排序字段 ASC|DESC，排序字段 ASC|DESC} |
例：同时查询emp和dept表
|
SQL> select * from emp,dept; |
发现返回了56条数据，emp一个才14条，dept表才4条。56=14*4。
查询emp表的记录数：
|
SQL> select count(*) from emp; |
说明使用多表查询的时候会产生笛卡尔积，要想去掉笛卡尔积，必须使用字段进行关联操作。
在emp和dept表中都有一个deptno字段，属于关联字段。
例：加入where语句消除笛卡尔积
|
Select * from emp,dept Where emp.deptno=dept.deptno; |
例：查询雇员的编号、姓名、部门编号、部门名称即部门位置。
|
select e.deptno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno; |
例：查询每个雇员的姓名、工作、雇员的直接上级领导的姓名
|
select e.ename name,e.job job,m.ename mgr from emp e,emp m where e.mgr=m.empno; |
例：要求继续扩展，将部门名称也列出来。
|
select e.ename name,e.job job,m.ename mgr,d.dname from emp e,emp m,dept d where e.mgr=m.empno and e.deptno=d.deptno; |
思考：要求查询出每个雇员的姓名、工资、部门名称，工资在公司的等级，及其领导的姓名及工资等级。
|
select e.ename name, e.sal sal,d.dname dname，s.grade grade,m.ename mgr,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); |
进一步扩展：要求按照以下格式显示工资等级
l 1：第五等工资
l 2：第四等工资
l 3：第三等工资
l 4：第二等工资
l 5：第一等工资
|
select e.ename name,e.sal ,d.dname dname， decode(s.grade,1,‘第五等工资‘,2,‘第四等工资‘,3,‘第三等工资‘,4,‘第二等工资‘ ,5,‘第一等工资‘ ) grade,m.ename mgr, decode(ms.grade,1,‘第五等工资‘,2,‘第四等工资‘,3,‘第三等工资‘,4,‘第二等工资‘ ,5,‘第一等工资‘ ) mgrade 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);
|
2.1.2、左、右连接（重点）
Dept表中有4条数据
将emp和dept关联查询，查询发现只写出3个部门，因为雇员表中并没有40部门的雇员。
|
select e.ename ,e.deptno,d.dname from emp e,dept d where e.deptno(+)=d.deptno; |
可以发现40部门出现了。这里用到了右连接，具有以下规律：
l （+）在=左边表示右连接
l （+）在=右边表示左连接
|
select e.ename ,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno(+); |
发现以上的（+）没起到作用，无法显示40部门信息。
左、右连接在一般在开发使用较多，实际上之前的查询雇员姓名及每个雇员领导的时候就应该用到左、右连接。
e.mgr = m.empno(+) e.mgr = m.empno
|
select e.empno,e.ename ,m.ename,m.empno from emp e,emp m where e.mgr = m.empno(+); |
2.1.3 SQL：1999语法对SQL的支持（了解）
SQL：1999语法格式：
|
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; |
例：自然连接（NATURAL JOIN）：自动进行关联字段的匹配。
|
select * from emp natural join dept; |
例：USING子句：直接关联的操作列
|
select * from emp e join dept d using(deptno) where deptno=30; |
例：ON子句，用户自己编写的连接条件。
|
SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno) WHERE e.deptno=30 ; |
例：左连接（左外连接）、右连接（右外连接）：LEFT JOIN，RIGHT JOIN
|
select e.ename,d.deptno,d.dname,d.loc from emp e right outer join dept d on (e.deptno=d.deptno); |
2.2 组函数及分组统计（重点）
2.2.1 组函数
在SQL中常用的组函数有以下几个：
l COUNT():求出全部的记录数
l MAX():求出一组中的最大值
l MIN():求出最小值
l AVG():求出平均值
l SUM():求和
例：
|
Select count（empno）from emp; |
|
Select min(sal) from emp; |
例：求20部门的总工资
|
Select sum(sal) from emp where deptno=20; |
2.2.2 分组统计
分组统计需要用GROUPBY进行分组，SQL语法格式：
|
SELECT {DISTINCT} * |列1 别名1,列2 别名2 FROM 表1 别名1,表2 别名2 {WHERE 条件(s)} {GROUP BY 分组条件} {ORDER BY 排序字段 ASC|DESC , 排序字段ACS | DESC ,…} |
例：求出每个部门雇员总数。
|
select deptno, count(empno) from emp group by (deptno); |
注意点：观察以下代码
|
SELECT deptno,COUNT(empno) FROM emp ; |
以上代码不能正确执行，是因为：
1 如果程序中使用了分组函数，则有两种可以使用的情况：
n 程序中存在了GROUP BY，并指定了分组条件，这样可以将分组条件一起查询出来；
n 如果不使用分组的话，则只能单独的使用分组函数。
2 在使用分组函数的时候，不能出现分组函数和分组条件以外的字段。
|
SELECT deptno,empno,COUNT(empno) FROM emp GROUP BY deptno ; |
例：按部门分组，并显示部门的名称，及每个部门的员工数。
|
select count(e.empno) ,d.dname from emp e,dept d where e.deptno=d.deptno group by d.dname; |
例：求出平均工资大于2000的部门编号和平均工资
|
select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno; |
分组函数只能在分组中使用，不允许在where语句中出现。如果现在研指定分组发条件。则只能通过第二种条件的指令：HAVING，语法格式：
|
SELECT {DISTINCT} * 列1 别名1,列2 别名2 FROM 表1 别名1,表2 别名2 {WHERE 条件(s)} {GROUP BY 分组条件{HAVING分组条件}} {ORDER BY 排序字段ASC|DESC , 排序字段 ACS | DESC ,…} |
例：使用HAVING完成以上操作
|
select deptno,avg(sal) from emp group by deptno HAVING avg(sal) >2000; |
例：显示非销售人员工作名称以及从事同一个工作雇员的工资的总和，并且满足从事同一工作的雇员的月工作合计大于5000，输出结果按工资的合计升序排列。
|
select job,sum(sal) from emp where job<> ‘SALESMAN‘ group by job having sum(sal)>5000 order by sum(sal); |
分组的简单原则：
只要一列上存在重复的内容才有可能考虑到分组
注意：
分组函数可以嵌套使用，但是在嵌套使用的时候不能再出现分组条件的查询语句。
例：求出平均工资最高的部门工资
错误代码：
|
select deptno ,max(sum(sal)) from emp group by deptno; |
正确代码：
|
select max(sum(sal)) from emp group by deptno; |
2.3 子查询
子查询：在一个查询内部还包含另外一个查询，格式：
|
SELECT {DISTINCT} * | 列1 别名1,列2 别名2 FROM 表1 别名1,表2 别名2 ( SELECT {DISTINCT} * |列1 别名1,列2 别名2 FROM表1 别名1,表2 别名2 {WHERE 条件(s)} {GROUP BY 分组条件 {HAVING分组条件}} {ORDER BY 查询字段ASC|DESC , 查询字段ACS | DESC , } ) 别名, … {WHERE条件(s) ( SELECT {DISTINCT} * |列1 别名1,列2 别名2 FROM表1 别名1,表2 别名2 {WHERE条件(s)} {GROUP BY分组条件{HAVING分组条件}} {ORDER BY 查询字段ASC|DESC , 查询字段ACS | DESC , } ) } {GROUP BY {HAVING }} {ORDER BY ASC|DESC , ACS | DESC , } |
例：要求查询出比7654工资高的全部雇员信息
l 首先要知道7654的工资是多少
|
select sal s1 from emp where empno=7654; |
l 之后要以以上的结果作为后续查询的依据，只有是其他的工作大于s1，则符合条件。
|
select * from emp where sal>(select sal s1 from emp where empno=7654); |
所有子查询必须在（）中编写代码。
子查询在操作中又分为以下三类：（面试题）
l 单列子查询：返回的结果是一列的一个内容，出现几率高；
l 单行子查询：返回多个列。有可能是一条完整的记录
l 多行子查询：返回多条记录
例：要求查询出工资比7654高，同时与7788从事相同工作的全部雇员信息。
|
select * from emp where sal>(select sal s1 from emp where empno=7654) and job=(select job from emp where empno=7788); |
思考：
要求查询出：部门名称、部门的员工数，部门的平均工资，部门的最低收入雇员的姓名。
1、求出每个部门的员工数、平均工资
|
select deptno,count(deptno),avg(sal) from emp group by deptno; |
2、查询部门的名称，需关联dept表
|
select d.dname,ed.deptno,ed.cnt,ed.arg from dept d,( select deptno,count(deptno) cnt,avg(sal) arg from emp group by deptno) ed where d.deptno=ed.deptno; |
3、求出最低收入的雇员姓名
|
select d.dname,ed.deptno,ed.cnt,ed.arg,ed.m,e.ename from dept d,emp e, (select deptno,count(deptno) cnt,avg(sal) arg,min(sal) m from emp group by deptno) ed where d.deptno=ed.deptno and e.sal= ed.m; |
如果此时在一个部门中同时存在两个工资最低的雇员，则程序就会出错。在子查询中存在以下三种查询的操作符号：
l IN：指定一个查询的范围
l ANY：
=ANY:与IN的操作符功能完全一样
>ANY:比里面最小的值要大
<ANY: 比最大的值要小
l ALL
>ALL:比最大值要大的
<ALL:比最小值要小的
例：求出每个部门的最低工资的雇员信息：IN
有多个部门，因此返回的值肯定的多个，此时可以用IN指定一个操作范围。
|
select * from emp where sal IN (select min(sal) from emp group by deptno); |
例：ANY操作
l =ANY: 与IN的操作符功能完全一样
|
select * from emp where sal =ANY (select min(sal) from emp group by deptno); |
l >ANY: 比里面最小的值要大（所有工资大于800的），共13条数据
|
select * from emp where sal >ANY (select min(sal) from emp group by deptno); |
l <ANY: 比最大的值要小（所有工资小于1300的）
|
select * from emp where sal <ANY (select min(sal) from emp group by deptno); |
ALL操作
l >ALL:比最大值要大的（所有工资大于1300的雇员的信息）
l <ALL:比最小值要小的（所有工资小于800的雇员的信息）
|
select * from emp where sal >ALL (select min(sal) from emp group by deptno); |
对于子查询来讲，还可以进行多列子查询，一个子查询中同时返回多个查询的列。
|
select * from emp where (sal,NVL(comm,-1)) IN( select sal,NVL(comm,-1) from emp where deptno=20); |
2.4 数据库更新操作
数据库的主要操作分为两种：
l 数据库的查询操作：SELECT
l 数据库的更新操作：INSERT(添加)、UPDATE(修改，更新)、DELETE(删除)
复制一份emp表的信息：
|
create table myemp as select * from emp; |
2.4.1 添加数据
添加数据的语法：
|
INSERT INTO 表名称[(字段名称1，字段名称2，…)]VALUES(值1，值2，…)； |
例：为myemp表添加一条数据
l 使用标准的做法完成（推荐）
|
insert into myemp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7899,‘张三‘,‘清洁工‘,7369,‘14-2月-1995‘,500,300,40); |
l 使用简略的写法（不推荐）。因为要添加所有字段的内容，所有可以不写上字段名称，只有值的数量及顺序与数据表中的一致即可。
|
insert into myemp values(8899,‘李四‘,‘清洁工‘,7369,‘14-2月-1995‘,510,300,40); |
例：要求插入一个新雇员，但是该雇员没有领导，也没有奖金。
l 第一种做法：明确写出要插入的字段
|
insert into myemp (empno,ename,job,hiredate,sal,deptno) values(3242,‘王五‘,‘清洁工‘,‘13-6月-2000‘,5400,40); |
l 第二种做法：如果插入时没有明确写出字段名称的话，使用null表示不插入的字段的具体内容。
|
insert into myemp values(3242,‘王五‘,‘清洁工‘,null,‘13-6月-2000‘,5400,null,40); |
之前插入数据的时候，日期的格式是按照表中固定的格式，如果现在的日期格式的“2014-01-14”的话，那么就要用TO_DATE（）函数，将字符串类型变为DATE类型数据。
|
Insert into myemp values(8888,‘赵六‘,‘清洁工‘,null,to_date(‘2014-01-14‘,‘yyyy-mm-dd‘),6000,null,40); |
2.4.2 修改数据
在SQL中使用UPDATE语句可以完成数据的修改功能，语法格式：
|
修改全部：UPDATE 表名称 SET 要修改的字段 = 新值，要修改的字段 = 新值，… 修改局部：UPDATE 表名称 SET 要修改的字段 = 新值，要修改的字段 = 新值，…WHERE 修改条件 |
从一般的开发角度上讲，修改操作一般都是加入修改条件。
例：将myemp表中所有雇员的佣金修改为1000——>修改全部
|
update myemp set comm=1000; |
例：将编号为7899的雇员的工作修改为7000——>指定修改条件
|
update myemp set sal=7000 where empno=7899; |
例：将7369、8899、7788的领导及奖金取消
|
Update myemp set mgr =null,comm=null where empno in(7369,8899,7788); |
2.4.3 删除数据
在SQL中使用DELETE命令删除记录，语法格式如下：
|
删除全部：DELETE FROM 表名称； 删除局部：DELETE FROM 表名称 WHERE 删除条件； |
例：删除编号为7899的雇员信息
|
Delete from myemp where empno=7899; |
例：删除表的全部内容
|
Delete from myemp; |
2.5 事务处理
例：建一个只含10部门雇员的临时表
|
Create table emp10 as select * from emp where deptno=10; |
删除emp10表中7782的雇员后，再查询发现已经将此雇员信息删除掉了，但是选择打开第二个窗口，再次查询emp10，发现7782还存在。实际上这就是Oracle中事务处理的概念。
事务处理：保证数据操作的完整性，所有的操作要么同时成功，要么同时失败。
在Oracle中对于每个连接到数据库的窗口（sqlplus、sqlplusw）连接之后实际上都会与数据库的连接建立一个session，即：每一个连接到数据库上的用户都表示创建了一个session。
一个是session对数据库所做的修改，不会立刻反应到数据库的真实数据上，是允许回滚的，当一个session提交所有的操作之后，数据库才真正的做出修改。
n 提交事务：commit
n 回滚事务：rollback；
|-如果数据已经被提交了，则肯定无法回滚。
在Oracle中关于事务的处理上也会存在一种死锁的概念。
n 一个session如果更新了数据库中的记录，其他session是无法立刻更新的，要等待对方提交之后才允许更新。
2.6 查询练习
1、列出所有至少有一个员工的部门。
|
select dn.deptno,d.dname from dept d,( select deptno from emp group by deptno having count(deptno) > 0) dn where d.deptno=dn.deptno; |
2、列出所有薪水比“SMITH”高的员工。
|
select * from emp where sal >( select sal from emp where ename=‘SMITH‘); |
3、列出所有员工的姓名及直接上级的姓名
|
select e.ename name, m.ename mgr from emp e,emp m where e.mgr=m.empno; |
4、列出所有受雇日期早于直接上级的员工的编号，姓名，部门名称
|
select e.empno,e.ename,e.hiredate,m.hiredate,d.dname from emp e,emp m,dept d where e.mgr=m.empno and e.hiredate -m.hiredate<0 and d.deptno=e.deptno; |
5、列出部门名称和这些部门的员工信息，同时列出没有员工的部门。
|
select d.dname,d.deptno,e.empno,e.ename from dept d,emp e where d.deptno=e.deptno(+); |
6、列出所有工作为’CLERK’的姓名及其部门名称，部门的人数。
|
select e.ename n,d.dname d,j.c from emp e,dept d,(select deptno,count(deptno) c from emp group by deptno) j where job=‘CLERK‘ and d.deptno=e.deptno and j.deptno=e.deptno ; |
7、求出最低工资大于1500的各种工作及其从事此工作的全部雇员人数。
|
select job ,min(sal), count(empno) from emp group by job having min(sal)>1500; |
|
select e.job,count(e.empno) from emp e where e.job IN ( select job from emp group by job having min(sal)>1500) group by e.job; |
8、列出在部门’SALES’工作的员工的姓名，假定不知道销售部门的编号。
|
select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname=‘SALES‘; |
|
select ename from emp where deptno=(select deptno from dept where dname=‘SALES‘); |
9、列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，工资等级。
|
select e.deptno,e.ename,e.sal,d.dname,d.loc,m.ename,g.grade from emp e,dept d,emp m,salgrade g where e.sal > (select avg(sal) from emp) and e.deptno=d.deptno and e.mgr=m.empno(+) and e.sal between g.losal and g.hisal; |
10、列出与’SCOTT’从事相同工作的所有员工及其部门名称。
|
select e.* ,d.dname from emp e,dept d where job=(select job from emp where ename=‘SCOTT‘) and e.deptno=d.deptno and ename<>‘SCOTT‘; |
11、列出薪资等于部门30中员工薪资的所有员工的姓名和薪资
|
select ename,sal,deptno from emp where sal IN ( select sal from emp where deptno=30) and deptno<>30; |
12、列出薪资高于部门30工作的所有员工的薪资的员工姓名和薪资、部门
|
select e.ename,e.sal,e.deptno,d.dname from emp e,dept d where sal >ALL ( select sal from emp where deptno=30) and e.deptno=d.deptno and e.deptno <>30; |
13、列出在每个部门工资的员工数量、平均工资和平均服务期限。
|
select d.dname,e.* from dept d,(select deptno,count(empno) con，avg(sal)， round(avg(months_between(sysdate,hiredate))/12,2) year from emp group by deptno) e where d.deptno=e.deptno; |
|
select d.dname,count(e.empno),avg(sal),avg(months_between(sysdate,hiredate)/12) year from emp e,dept d where d.deptno=e.deptno group by d.dname; |
14、列出所有部门的详细信息和部门人数
|
select d.*,ed.c from dept d,(select deptno,count(deptno) c from emp group by deptno) ed where d.deptno=ed.deptno; |
但是以上的查询中没有包含部门40，因为部门40没有任何雇员，所有部门40的员工人数应该使用0表示。
|
select d.*,NVL(ed.c,0) from dept d,(select deptno,count(deptno) c from emp group by deptno) ed where d.deptno=ed.deptno(+); |
15、列出各种工作的最低工资及从事此工作的雇员姓名。
|
select e* from emp e,(select job ,min(sal) m from emp group by job) de where e.sal=de.m and e.job=de.job; |
|
select * from emp where sal IN(select min(sal) from emp group by job) ; |
16、列出各个部门MANAGER的最低工资
|
select deptno,min(sal) from emp where job=‘MANAGER‘ group by deptno; |
17、列出所有员工的年薪，按年薪从低到高排序
|
select ename,job,(sal+NVL(comm,0)）*12 ysal from emp order by ysal asc; |
18、求出部门名称中带有’S’字符的部门员工的总工资、部门人数
|
select d.dname,sum(e.sal),count(e.deptno) from emp e,dept d where e.deptno = d.deptno and d.dname like‘%S%‘ group by d.dname; |
|
select deptno,sum(sal),count(empno) from emp where deptno IN(select deptno from dept where dname like‘%S%‘) group by deptno; |
19、改任职超过30年的员工加薪10%
|
update emp set sal=1.1*sal where months_between(sysdate,hiredate)/12 > 30; |
Oracle多表查询与数据更新