一、多表联合查询

二、子查询

三、动态条件查询

 

 

LESSON 4 Displaying Data from Multiple Tables

--------------------------------------------------------

查询s_emp表中最大的工资数,并且显示出这个最大工资数的员工名字

select last_name,max(salary)

from s_emp;

多表查询

查询多张表的时候会产生笛卡尔积

为了防止笛卡尔积的产生，我们需要使用某些条件把两张表或多张表连接起来（一般会使用两个表间主外键相等的关系）

1）等值连接

内连接

自连接，自己连接自己

2）不等值连接

三种不等值连接：

表名1 left outer join 表名2 on 连接条件

表名1 right outer join 表名2 on 连接条件

表名1 full outer join 表名2 on 连接条件

其中的outer可以省去不写

查询s_emp表中每一个员工的名字和部门编号

select last_name,dept_id

from s_emp

查询s_emp表中每一个员工的名字和部门编号,并且显示出部门的名字

select se.last_name,se.dept_id,sd.id,sd.name

from s_emp se,s_dept sd

order by se.last_name;

select se.last_name,se.dept_id,sd.name

from s_emp se,s_dept sd

where se.dept_id = sd.id

order by se.last_name;

查询s_emp表中每一个员工的名字和部门编号,并且显示出部门的名字以及部门所在地区的名字

select se.last_name,se.dept_id,sd.name,sd.region_id,sr.name

from s_emp se, s_dept sd, s_region sr

where se.dept_id = sd.id and sd.region_id = sr.id

order by last_name asc;

查询s_emp表中每一个员工的id、名字、领导人的id、领导人的名字，没有领导人的员工除外。

select s1.id,s1.last_name,s1.manager_id,s2.last_name

from s_emp s1,s_emp s2

where s1.manager_id = s2.id and s1.manager_id is not null

order by s2.id asc;

//向s_emp中插入一条测试数据

//这是一条DML语句，会产生事务

insert into s_emp(id,last_name)

values(26,'tom');

//所以插入之后的数据要提交

commit;

下面都是不等值连接的例子:

---------------------------------

查询员工的名字、部门号、部门名字

(即使某个员工还没有部门编号也要显示出来)

//如果还用这样的等值连接是查询不出来tom这个人的

select se.last_name,se.dept_id,sd.name

from s_emp se,s_dept sd

where se.dept_id = sd.id;

//哪边数据少在那边加上这个加号

select se.last_name,se.dept_id,sd.name

from s_emp se,s_dept sd

where se.dept_id = sd.id(+);

左连接（将左表作为主表）

select se.last_name,se.dept_id,sd.name

from s_emp se left join s_dept sd

on se.dept_id = sd.id;

右连接

select se.last_name,se.dept_id,sd.name

from s_emp se right join s_dept sd

on se.dept_id = sd.id;

insert into s_dept(id,name)values(51,'KunShan');

commit;

--------------------------------

查询员工的名字、部门号、部门名字

(没有员工存在的部门也要显示出来)

select se.last_name,se.dept_id,sd.name

from s_emp se,s_dept sd

where se.dept_id(+) = sd.id;

查询员工的名字、部门号、部门名字

(即使某个员工还没有部门编号也要显示出来、没有员工存在的部门也要显示出来)

//全连接

select se.last_name,se.dept_id,sd.name

from s_emp se full outer join s_dept sd

on se.dept_id = sd.id;

select se.last_name,se.dept_id,sd.name

from s_emp se full join s_dept sd

on se.dept_id = sd.id;

操作俩个结果集的关键字

---------------------------------------------

(这里说的结果集指的是一条sql语句查询后得到的结果)

 

union

union all

minus

intersect

第一条sql:

select id,last_name

from s_emp

where id < 7;

结果集一:

ID LAST_NAME

-- ---------------

1 Velasquez

2 Ngao

3 Nagayama

4 Quick-To-See

5 Ropeburn

6 Urguhart

第二条sql语句:

select id,last_name

from s_emp

where id < 6;

结果集二:

ID LAST_NAME

-- --------------

1 Velasquez

2 Ngao

3 Nagayama

4 Quick-To-See

5 Ropeburn

1 union 取得俩个结果集的并集

select id,last_name

from s_emp

where id < 7

union

select id,last_name

from s_emp

where id < 6;

2 union all 之间把俩个结果集放在一起,不管重复的数据

select id,last_name

from s_emp

where id < 7

union all

select id,last_name

from s_emp

where id < 6;

3 minus 去掉俩个结果集相同的部分

select id,last_name

from s_emp

where id < 7

minus

select id,last_name

from s_emp

where id < 6;

 

4 intersect 只保留俩个结果集相同部分(交集)

select id,last_name

from s_emp

where id < 7

intersect

select id,last_name

from s_emp

where id < 6;

子查询

------------------------------------------------------

查询Ngao所在部门的编号

select dept_id from s_emp

where last_name='Ngao';

查询41号部门的平均工资

select avg(salary)

from s_emp

where dept_id = 41

group by dept_id;

select avg(salary)

from s_emp

where dept_id = 41;

AVG(SALARY)

-----------

1247.5

 

sql语句的基本格式:

-------------

select ...

from ..

where ...

group by ...

having ...

order by ..

**执行顺序： ---> where ---> group by --->组函数 ---> having ---> order by

select/having 后面出现了组函数，那么么有被组函数修饰的列就一定要写在group by 后面

-----------------------------------------------------------------------------

查询Ngao所在部门的平均工资

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

);

练习1：

---------------------------------------------------------------------------

查询比41号部门平均工资高的部门里面的员工信息(显示员工的 名字 部门号 工资)

 

select last_name,dept_id,salary

from s_emp

where dept_id=any(

select dept_id from s_emp

group by dept_id

having avg(salary) >

(

select avg(salary) from s_emp

where dept_id = 41

)

);

分析：

1.查询的是员工信息

2.员工所在部门的平均工资比41号部门高

41部门的平均工资

select avg(salary)

from s_emp

where dept_id = 41;

AVG(SALARY)

-----------

1247.5

比41部门平均工资高的部门

select dept_id

from s_emp

group by dept_id

having avg(salary)>1247.5

order by dept_id;

select dept_id

from s_emp

group by dept_id

having avg(salary)>

(

select avg(salary)

from s_emp

where dept_id = 41

)

order by dept_id;

DEPT_ID

----------

10

31

32

33

35

50

 

select last_name,dept_id,salary

from s_emp

where dept_id in(10,31,32,33,35,50);

select last_name,dept_id,salary

from s_emp

where dept_id in

(

select dept_id

from s_emp

group by dept_id

having avg(salary)>

(

select avg(salary)

from s_emp

where dept_id = 41

)

);

 

练习2：

-------------------------------------------------------------------------------------------

查询比Ngao所在部门的平均工资高的员工信息,并且这些员工所在部门平均工资也要比Ngao所在部门的平均工资要高.

select last_name,dept_id,salary

from s_emp

where dept_id in

(

select dept_id

from s_emp

group by dept_id

having avg(salary)>

(

select avg(salary)

from s_emp

where dept_id = 41

)

)

and salary >

(

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

)

);

分析：

1.查询的是员工信息

2.这些员工的工资比Ngao所在部门平均工资高

3.这些员工所在部门的平均工资也要比Ngao所在部门平均工资高

Ngao所在的部门编号

select dept_id

from s_emp

where last_name = 'Ngao';

DEPT_ID

--------

41

 

41号部门的平均工资

select avg(salary)

from s_emp

where dept_id = 41;

AVG(SALARY)

-----------

1247.5

比41部门平均工资高的部门

select dept_id

from s_emp

group by dept_id

having avg(salary)>1247.5

order by dept_id;

DEPT_ID

----------

10

31

32

33

35

50

 

这个例子最终要执行的sql其实为:

select last_name,dept_id,salary

from s_emp

where salary > 1247.5

and dept_id in(10,31,32,33,35,50);

所以最终把sql语句中的条件替换为子查询为:

select last_name,dept_id,salary

from s_emp

where salary >

(

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

)

)

and dept_id in

(

select dept_id

from s_emp

group by dept_id

having avg(salary)>

(

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

)

)

);

 

练习3：

----------------------------------------------------------------

查询比Ngao所在部门的平均工资高员工信息,并且这些员工所在部门平均工资也要比Ngao所在部门的平均工资要高.

(显示 员工的名字、部门号、工资)

增加显示这个员工所在部门名字

 

select se.last_name,se.dept_id,se.salary,sd.name,sr.name,se2.last_name

from s_emp se,s_dept sd,s_region sr,s_emp se2

where se.salary >

(

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

)

)

and se.dept_id in

(

select dept_id

from s_emp

group by dept_id

having avg(salary)>

(

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

)

)

)

and se.dept_id = sd.id

and sd.region_id = sr.id

and se.manager_id = se2.id;

 

1.查询的是员工信息

2.这些员工的工资比Ngao所在部门平均工资高

3.这些员工所在部门的平均工资也要比Ngao所在部门平均工资高

select se.last_name,se.dept_id,se.salary,sd.dept_name

from s_emp se join s_dept sd

on se.dept_id = sd.id;

 

select last_name,dept_id,salary

from s_emp

where salary >

(

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

)

)

and dept_id in

(

select dept_id

from s_emp

group by dept_id

having avg(salary)>

(

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

)

)

);

 

 

select se.last_name,se.dept_id,se.salary,sd.name

from s_emp se join s_dept sd

on ((se.dept_id=sd.id) and

(se.salary >

(

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

)

)

and se.dept_id in

(

select dept_id

from s_emp

group by dept_id

having avg(salary)>

(

select avg(salary)

from s_emp

where dept_id =

(

select dept_id

from s_emp

where last_name = 'Ngao'

)

)

)

)

);

 

练习4：

------------------------------------------

查询每一个部门中比本部门平均工资高的员工信息

显示出员工的名字、工资、部门号、这个部门的平均工资

//这个sql语句的查询结果将来可以当做要查询的一张表

select dept_id,avg(salary) avgSal

from s_emp

group by dept_id;

DEPT_ID avgSal

------- -----------

42 1081.66667

43 900

34 1160

44 1050

31 1400

32 1490

35 1450

50 2025

41 1247.5

45 1089

33 1515

10 1450

 

select se.last_name,se.salary,se.dept_id,temp.avgSal

from s_emp se,(

select dept_id,avg(salary) avgSal

from s_emp

group by dept_id

) temp

where se.dept_id = temp.dept_id

and se.salary > temp.avgSal;

1.查询的是员工信息

2.例如要把41号部门中比41号部门(本部门)平均工资高的员工信息查询出来

select avg(salary),dept_id

from s_emp

group by dept_id;

select last_name,salary,dept_id

from s_emp;

select s1.last_name,s1.salary,s1.dept_id,s2.avg_sal

from s_emp s1

join (select avg(salary) avg_sal,dept_id from s_emp group by dept_id) s2

on ((s1.dept_id = s2.dept_id) and (s1.salary > s2.avg_sal))

order by s1.dept_id asc;

 

sql语句执行的时候输入参数(运行时参数)

------------------------------------------------

select last_name,salary,dept_id

from s_emp

where last_name=&name;

select last_name,salary,dept_id

from s_emp

where id = 2;

select last_name,salary,dept_id

from s_emp

where id = &id;

//运行sql语句的时候给字符串参数赋值

select last_name,salary,dept_id

from s_emp

where last_name = &last_name;

select last_name,salary,dept_id

from s_emp

where last_name = '&last_name';

select last_name,salary,dept_id

from s_emp

where id > &id or last_name = '&last_name';

//运行时再给出具体的筛选条件

select last_name,salary,dept_id

from s_emp

where &condition;

select last_name,salary,dept_id

from s_emp

&condition;

insert into student(id,name,age)

values(&id,'&name',&age);