MySQL记录操作之多表查询

一、多表查询介绍与准备工作

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()的子查询会先产生结果集，
- 然后主查询再去结果集里面去找符合要求的字段列表去，符合要求的输出，反之则不输出