首页 > 代码库 > 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多表查询与数据更新