8.MySQL多表查询

开局签到Python基础

已于 2022-02-28 10:49:38 修改

阅读量195

点赞数

分类专栏： 2.MySQL 文章标签： mysql sql 数据库

于 2021-07-30 21:39:21 首次发布

风

本文链接：https://blog.csdn.net/qq_46137324/article/details/119256517

版权

2.MySQL 专栏收录该内容

15 篇文章 2 订阅

订阅专栏

本文详细介绍了MySQL中的多表查询方法，包括子查询和联表查询。子查询将一个查询的结果作为另一个查询的条件，而联表查询则通过内连接、左连接、右连接和全连接来组合多表数据。文章通过实例展示了如何使用这些方法查询员工与部门的关系，以及如何筛选特定条件的数据。此外，还提供了练习题和解题步骤，帮助读者深入理解多表查询的运用。

摘要由CSDN通过智能技术生成

文章目录

1.模板

# 创建部门表
create table dep(
    id int primary key auto_increment, 	   dep_name varchar(16)
);

# 创建员工表
create table emp(
	id int primary key auto_increment,
	name char(16),
	gender enum('male', 'femal', 'others') default 'male',
    dep_id int
    );

 # 为部门表录值
insert into dep(id, dep_name) values
    (1,'A部门'),
    (2,'B部门'),
    (3,'C部门'),
    (9,'D部门');

# 为员工表录值
insert into emp(name, dep_id) values 
    ('kid', 1),
    ('qz',2),
    ('ze',2),
    ('db',2),
    ('pp',3),
    ('qq',4);

select * from dep;
select * from emp;

+------+----------+
| id   | dep_name |
+------+----------+
|    1 | A部门    |
|    2 | B部门    |
|    3 | C部门    |
|    9 | D部门    |
+------+----------+

+----+------+--------+--------+
| id | name | gender | dep_id |
+----+------+--------+--------+
|  1 | kid  | male   |      1 |
|  2 | qz   | male   |      2 |
|  3 | ze   | male   |      2 |
|  4 | db   | male   |      2 |
|  5 | pp   | male   |      3 |
|  6 | qq   | male   |      4 |
+----+------+--------+--------+

2. 多表查询

多表查询的方式:
1.子查询
2.联表查询

3. 子查询

将一个查询语句的结果当作另一个查询语句的条件去使用。
前一个sql语句作为查询的依据的时候,要写在括号内.

查询部门是A,B两个部门的员工信息。

# 1. 先获取AB部门的id号
select id from dep where dep_name in ('A部门', 'B部门');

+----+
| id |
+----+
|  1 |
|  2 |
+----+

# 2.再拿部门id去员工表中筛选对应的员工
select name from emp where dep_id in (前面的SQLsql);

# 3.子查询写法
select name from emp where dep_id in (
    select id from dep where dep_name in ('A部门', 'B部门')
);

表的查询结果可以作为其他表的查询条件，也可以通过起别名的方式把它作为一张虚拟表跟其他表关联。

4. 联表查询

查询两张表结果为笛卡尔积.
这种方式拼接表把所有的对应关系都呈现出来,消耗的资源太大.
两张表中没有关联会被显示出来.

select * from  emp, dep;

+----+------+--------+--------+----+----------+
| id | name | gender | dep_id | id | dep_name |
+----+------+--------+--------+----+----------+
|  1 | kid  | male   |      1 |  1 | A部门    |
|  1 | kid  | male   |      1 |  2 | B部门    |
|  1 | kid  | male   |      1 |  3 | C部门    |
|  1 | kid  | male   |      1 |  9 | D部门    |
|  2 | qz   | male   |      2 |  1 | A部门    |
|  2 | qz   | male   |      2 |  2 | B部门    |
|  2 | qz   | male   |      2 |  3 | C部门    |
|  2 | qz   | male   |      2 |  9 | D部门    |
|  3 | ze   | male   |      2 |  1 | A部门    |
|  3 | ze   | male   |      2 |  2 | B部门    |
|  3 | ze   | male   |      2 |  3 | C部门    |
|  3 | ze   | male   |      2 |  9 | D部门    |
|  4 | db   | male   |      2 |  1 | A部门    |
|  4 | db   | male   |      2 |  2 | B部门    |
|  4 | db   | male   |      2 |  3 | C部门    |
|  4 | db   | male   |      2 |  9 | D部门    |
|  5 | pp   | male   |      3 |  1 | A部门    |
|  5 | pp   | male   |      3 |  2 | B部门    |
|  5 | pp   | male   |      3 |  3 | C部门    |
|  5 | pp   | male   |      3 |  9 | D部门    |
|  6 | qq   | male   |      4 |  1 | A部门    |
|  6 | qq   | male   |      4 |  2 | B部门    |
|  6 | qq   | male   |      4 |  3 | C部门    |
|  6 | qq   | male   |      4 |  9 | D部门    |
+----+------+--------+--------+----+----------+
# 其中qq的数据 D部门的数据，被显示

where筛选只显示外键关联的数据.
两张表中没有关联会被显示出来.

# 涉及多张表查询的时候字段名称容易冲突需要使用表名.字段的方式
select * from emp, dep where emp.dep_id = dep.id;

+----+------+--------+--------+------+----------+
| id | name | gender | dep_id | id   | dep_name |
+----+------+--------+--------+------+----------+
|  1 | kid  | male   |      1 |    1 | A部门    |
|  2 | qz   | male   |      2 |    2 | B部门    |
|  3 | ze   | male   |      2 |    2 | B部门    |
|  4 | db   | male   |      2 |    2 | B部门    |
|  5 | pp   | male   |      3 |    3 | C部门    |
+----+------+--------+--------+------+----------+
# 其中qq的数据 D部门的数据，没有显示

4.1 内置方法

MySQL开设4个对应方法：
1. inner join 内连接：只拼接两张表中有关联的数据。
2. left  join 左连接: 左边所有的数据都显示出来，字段没有关联的数据以null填充。
3. right join 右连接：右边所有的数据都显示出来，字段没有关联的数据以null填充。
4. union  全连接：左右两表都展示出来。

4.2 内连接

select * from emp inner join dep on emp.dep_id = dep.id;
# 效果与 select * from emp, dep where emp.dep_id = dep.id; 一样. 但是占用的资源小.

+----+------+--------+--------+----+----------+
| id | name | gender | dep_id | id | dep_name |
+----+------+--------+--------+----+----------+
|  1 | kid  | male   |      1 |  1 | A部门    |
|  2 | qz   | male   |      2 |  2 | B部门    |
|  3 | ze   | male   |      2 |  2 | B部门    |
|  4 | db   | male   |      2 |  2 | B部门    |
|  5 | pp   | male   |      3 |  3 | C部门    |
+----+------+--------+--------+----+----------+
# 其中qq的数据,没有显示。应为qq在部门表中没有对应的数据.

2.3左连接

select * from emp left join dep on emp.dep_id = dep.id;

+----+------+--------+--------+------+----------+
| id | name | gender | dep_id | id   | dep_name |
+----+------+--------+--------+------+----------+
|  1 | kid  | male   |      1 |    1 | A部门    |
|  2 | qz   | male   |      2 |    2 | B部门    |
|  3 | ze   | male   |      2 |    2 | B部门    |
|  4 | db   | male   |      2 |    2 | B部门    |
|  5 | pp   | male   |      3 |    3 | C部门    |
|  6 | qq   | male   |      4 | NULL | NULL     |
+----+------+--------+--------+------+----------+

2.4 右连接

select * from emp right join dep on emp.dep_id = dep.id;

+------+------+--------+--------+------+----------+
| id   | name | gender | dep_id | id   | dep_name |
+------+------+--------+--------+------+----------+
|    1 | kid  | male   |      1 |    1 | A部门    |
|    2 | qz   | male   |      2 |    2 | B部门    |
|    3 | ze   | male   |      2 |    2 | B部门    |
|    4 | db   | male   |      2 |    2 | B部门    |
|    5 | pp   | male   |      3 |    3 | C部门    |
| NULL | NULL | NULL   |   NULL |    9 | D部门    |
+------+------+--------+--------+------+----------+

2.5全连接

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;

+------+------+--------+--------+------+----------+
| id   | name | gender | dep_id | id   | dep_name |
+------+------+--------+--------+------+----------+
|    1 | kid  | male   |      1 |    1 | A部门    |
|    2 | qz   | male   |      2 |    2 | B部门    |
|    3 | ze   | male   |      2 |    2 | B部门    |
|    4 | db   | male   |      2 |    2 | B部门    |
|    5 | pp   | male   |      3 |    3 | C部门    |
|    6 | qq   | male   |      4 | NULL | NULL     |
| NULL | NULL | NULL   |   NULL |    9 | D部门    |
+------+------+--------+--------+------+----------+

5.多表查询练习

基于前面的emp表中,早gender后加入一个字段age,在录值。

# 添加字段
alter table emp add age int after gender;

# 修改数据
update emp set age = 18 where id =1;
update emp set age = 26 where id =2;
update emp set age = 27 where id =3;
update emp set age = 19 where id =4;
update emp set age = 21 where id =5;
update emp set age = 24 where id =6;

mysql> select * from emp;
+----+------+--------+------+--------+
| id | name | gender | age  | dep_id |
+----+------+--------+------+--------+
|  1 | kid  | male   |   18 |      1 |
|  2 | qz   | male   |   26 |      2 |
|  3 | ze   | male   |   27 |      2 |
|  4 | db   | male   |   19 |      2 |
|  5 | pp   | male   |   21 |      3 |
|  6 | qq   | male   |   24 |      4 |
+----+------+--------+------+--------+

查询平均年龄在20岁以上的部门名称.

5.1联表方式查询

联表操作涉及到多表操作的时候一定要加上表的前缀。

# 1.先拼接两张表
select * from emp inner join dep on emp.dep_id = dep.id;

+----+------+--------+------+--------+----+----------+
| id | name | gender | age  | dep_id | id | dep_name |
+----+------+--------+------+--------+----+----------+
|  1 | kid  | male   |   18 |      1 |  1 | A部门    |
|  2 | qz   | male   |   26 |      2 |  2 | B部门    |
|  3 | ze   | male   |   27 |      2 |  2 | B部门    |
|  4 | db   | male   |   19 |      2 |  2 | B部门    |
|  5 | pp   | male   |   21 |      3 |  3 | C部门    |
+----+------+--------+------+--------+----+----------+

# 2.对拼接的表 按部门名称进行分组.
select dep_name from emp inner join dep on emp.dep_id = dep.id group by dep_name;

+----------+
| dep_name |
+----------+
| A部门    |
| B部门    |
| C部门    |
+----------+

# 3.分组后使用聚合函数avg求平均值,在使用having过滤出平均年龄大于32的.

select dep_name from emp 
	inner join dep on emp.dep_id = dep.id 
	group by dep_name having avg(age) > 20;

+----------+
| dep_name |
+----------+
| B部门    |
| C部门    |
+----------+

5.2 子查询

# 1.在员工表中对部门id进行分组,
select dep_id from emp group by dep_id;

+--------+
| dep_id |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
+--------+

# 2.筛选出平均大于20岁的部门id
select dep_id from emp group by dep_id having avg(age) > 20;

+--------+
| dep_id |
+--------+
|      2 |
|      3 |
|      4 |← 这个部门id,在部门表中没有对应的部门
+--------+

# 3.拿着部门id去部门表中筛选出部门名称
select dep_name from dep where id in 
	(select dep_id from emp group by dep_id 
 having avg(age) > 20);

+----------+
| dep_name |
+----------+
| B部门    |
| C部门    |
+----------+

5. exists:存在

exists() 返回布尔值.

写在exists()内的sql语句的查询结果为
为True 外层语句执行，否则不执行。

select * from emp where exists (   # 返回值为True 执行外层的sql语句
select id from dep where id>3      
);

+------+----------+
| id   | dep_name |
+------+----------+
|    1 | A部门    |
|    2 | B部门    |
|    3 | C部门    |
|    9 | D部门    |   # id > 3
+------+----------+