多表查询
- 一、多表查询介绍与准备工作
- 1、主题:
- 2、准备示例用的表:
- 3、查看一下表dep与emp:
- 二、多表连接查询
- 1、交叉连接:
- 2、内连接:只连接匹配的行
- 3、外连接之左连接:优先显示左表全部记录
- 4、外连接之右链接:优先显示右表全部记录
- 5、全外连接:显示左右两个表全部记录
- 三、符合条件连接查询
- 四、子查询
- 1、带in关键字的子查询
- 2、带any关键字的子查询
- 3、带all关键字的子查询
- 4、带比较运算符的子查询
- 5、带exists关键字的子查询
- 5.1 in与exists
一、多表查询介绍与准备工作
1、主题:
-多表连接查询
-复合条件连接查询
-子查询
2、准备示例用的表:
# 建表
create table dep(
id int,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into emp(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
3、查看一下表dep与emp:
mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
二、多表连接查询
- 外连接语法:
select 字段列表
from 表1 inner/left/right join 表2
on 表1.字段 = 表2.字段;
1、交叉连接:
不适用于任何匹配条件(生成笛卡尔积)
select * from emp,dep;
结果显示:
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
2、内连接:只连接匹配的行
# 找两张表共有的部分,相当于利用条件从笛卡尔积结果汇总筛选出了正确的结果;
select emp.id,emp.name,emp.age,emp.sex,dep.name from emp
inner join dep on emp.dep_id=dep.id;
+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| 1 | egon | 18 | male | 技术 |
| 2 | alex | 48 | female | 人力资源 |
| 3 | wupeiqi | 38 | male | 人力资源 |
| 4 | yuanhao | 28 | female | 销售 |
| 5 | liwenzhou | 18 | male | 技术 |
+----+-----------+------+--------+--------------+
#上述sql等同于
select emp.id,emp.name,emp.age,emp.sex,dep.name from emp,dep
where emp.dep_id=dep.id;
# dep表中没有204编号的部门,因而emp表中关于204这条员工的信息没有匹配出来
3、外连接之左连接:优先显示左表全部记录
# 以左表为准,即找出所有员工信息,当然包括没有部门的员工
# 本质就是:在内连接的基础上增加左边有而右边没有的结果
select emp.id,emp.name,dep.name as depart_name from emp
left join dep on emp.dep_id=dep.id;
+----+------------+--------------+
| id | name | depart_name |
+----+------------+--------------+
| 1 | egon | 技术 |
| 5 | liwenzhou | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 6 | jingliyang | NULL |
+----+------------+--------------+
4、外连接之右链接:优先显示右表全部记录
#以右表为准,即找出所有部门信息,包括没有员工的部门
#本质就是:在内连接的基础上增加右边有左边没有的结果
select emp.id,emp.name,dep.name as depart_name from emp
right join dep on emp.dep_id=dep.id;
+------+-----------+--------------+
| id | name | depart_name |
+------+-----------+--------------+
| 1 | egon | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 5 | liwenzhou | 技术 |
| NULL | NULL | 运营 |
+------+-----------+--------------+
5、全外连接:显示左右两个表全部记录
# 全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
# PS:mysql不支持全外连接full join
# mysql可以使用union连接的方式间接完成全外连接
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
# union与union all的区别:union会去掉相同的记录
查看结果:
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------------+--------+------+--------+------+--------------+
三、符合条件连接查询
示例一:找出年龄大于25岁的员工及其所在部门
(以内连接的方式查询emp和dep表,并且emp表中的age、字段必须大于25)
select emp.name,dep.name from emp inner join dep
on emp.dep_id = dep.id
where age>25;
示例二:
以内连接的方式查询emp和dep表,并且以age字段的升序方式显示
select * from emp inner join dep
on emp.dep_id = dep.id
order by age;
四、子查询
1、子查询是将一个查询语句嵌套在另一个查询语句中;
2、内层查询语句的查询结果,可以为外层查询语句提供查询条件;
3、子查询中可以包含:in、not in、any、exists和notexists等关键字
4、还可以包含比较运算符:=、 !=、 <、 >等
1、带in关键字的子查询
# 查询平均年龄在25岁以上的部门名
select id,name from dep where id in
(select dep_id from emp group by dep_id having avg(age) > 25);
# 查看技术部员工的姓名
select name from emp where id in
(select id from dep where name = "技术");
# 查看人数不足一人的部门名(子查询得到的是有人的部门id)
select name from dep where id not in
(select distinct dep_id from emp);
# 注意点:not in 不支持null
#查询没有员工的部门:
select * from dep where id not in
(select distinct dep_id from emp); # 如果表中含有null,查询结果就会不准确
#正确查看方法:
select * from dep where id not in
(select distinct dep_id from emp where dep_id is not null);
2、带any关键字的子查询
# 在sql中的any和some是同义词,some的用法和功能与安逸一致
# any和in运算符不同之处:
- in用法一:in (子查询语句)
- in用法二:in(值1,值2,值3...)
- any只能跟子查询语句
- any必须与其他运算符配合使用,而且必须将比较运算符放在any关键字之前,
# 所比较的值需要匹配子查询中的任意一个值
# 例如:使用in和使用=any得到的结果是一致的
select * from emp where salary = any (
select max(salary) from emp group by depart_id);
# 等同于:
select * from emp where salary in (
select max(salary) from emp group by depart_id);
# 结论:=any等价于in, <>any 等价于 not in
3、带all关键字的子查询
# all用法与any类似,只不过all表示的是匹配所有,any表示任一
# 查看薪资在所有部门平均线上的员工
select * from emp where salary > all(
select avg(salary) from emp group by depart_id);
# 同样的,all后面也不能跟(值1,值2,值3...)这样的集合
4、带比较运算符的子查询
# 比较运算符:=、!=、>、>=、<、<=、<>
① 查询大于平均年龄的员工名与年龄
select name,age from emp where age > (select avg(age) from emp);
结果显示:
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)
② 查询大于部门内平均年龄的员工名、年龄
select emp.name,emp.age from emp inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) as t2
on emp.dep_id = t2.dep_id
where emp.age > t2.avg_age;
5、带exists关键字的子查询
exists关键字表示存在,在使用exists关键字时,内层查询语句不返回查询的记录,
而是返回一个真假值,True或者False
当返回的是True时,外层查询语句将进行查询;
当返回的是False时,外层查询语句将不进行查询;
示例一:内层查询返回True
select * from emp where exists (select id from dep where id = 203);
# 查询结果:
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
示例二:(内层查询返回False)
select * from emp where exists (select id from dep where id = 204);
#查询结果:
Empty set (0.00 sec)
5.1 in与exists
# in查询效率高于exists
# exists后面一般都是子查询(相关子查询,与逐语句相关),当子查询返回行数时,exists条件返回True,否则返回False,exists是不会返回列表的值的,他只会判断括号里的数据能不能查找出来
# 例如:查询班级里有员工的部门
select * from dep where exists (
select * from emp where emp.dep_id = dep.id);
# not exists 的效果高于not in
select * from dep where not exists (
select * from emp where emp.dep_id = 203);
# exists 执行原理为:
- 依次执行外部查询
- 然后为外部查询返回的每一行分别执行一次子查询
- 子查询如果返回行,则exists条件成立,条件成立则输出外部查询取出的那条记录
# in 的执行原理:
- in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
- 然后主查询再去结果集里面去找符合要求的字段列表去,符合要求的输出,反之则不输出