mysql经典四表查询_mysql四-2：多表查询

最新推荐文章于 2022-10-21 16:36:39 发布

张兴艺

最新推荐文章于 2022-10-21 16:36:39 发布

阅读量205

点赞数

文章标签： mysql经典四表查询

本文链接：https://blog.csdn.net/weixin_36364044/article/details/113955684

版权

本文详细介绍了MySQL中的多表连接查询，包括交叉连接、内连接、左连接和右连接。通过实例展示了如何使用这些连接类型来获取员工与部门之间的匹配信息，以及如何处理不存在对应关系的数据。此外，还提到了全外连接在MySQL中的实现方式，以及union操作符在组合查询中的应用。

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

一、介绍

本节主题：

多表连接查询

复合条件连接查询

子查询

准备表：

#建表create tabledepartment(

idint,

namevarchar(20)

);create tableemployee(

idint primary keyauto_increment,

namevarchar(20),

sex enum(‘male‘,‘female‘) not null default ‘male‘,

ageint,

dep_idint);

#插入数据insert into department values(200,‘技术‘),

(201,‘人力资源‘),

(202,‘销售‘),

(203,‘运营‘);insert into employee(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)

;

#查看表结构和数据

mysql> descdepartment;+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

mysql> descemployee;+--------+-----------------------+------+-----+---------+----------------+

+--------+-----------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| sex | enum(‘male‘,‘female‘) | NO | | male | |

| age | int(11) | YES | | NULL | |

| dep_id | int(11) | YES | | NULL | |

+--------+-----------------------+------+-----+---------+----------------+

mysql> select * fromdepartment;+------+--------------+

| id | name |

+------+--------------+

| 200 | 技术 |

| 201 | 人力资源 |

| 202 | 销售 |

| 203 | 运营 |

+------+--------------+

mysql> select * fromemployee;+----+------------+--------+------+--------+

| 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 |

+----+------------+--------+------+--------+

创建department和employee表，并写入数据

需要注意的是在employee表有一个dep_id=204是department表没有的。同时，department表有一个id=203在employee表没有对应的记录。

二、多表连接查询

#重点：外链接语法SELECT字段列表FROM 表1 INNER|LEFT|RIGHT JOIN表2ON 表1.字段 = 表2.字段;

1、交叉连接：不适用任何匹配条件。生成笛卡尔积

mysql> select * fromemployee, department;+----+------------+--------+------+--------+------+--------------+

| 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 | 运营 |

+----+------------+--------+------+--------+------+--------------+

24 rows in set (0.01 sec)

每个员工和四个部门做一次对应关系。但这四次匹配仅有一次是需要的：即dep_id和id相同的那个。

mysql> select * from employee, department where employee.dep_id =department.id;+----+-----------+--------+------+--------+------+--------------+

| id | name | sex | age | dep_id | id | name |

+----+-----------+--------+------+--------+------+--------------+

| 1 | egon | male | 18 | 200 | 200 | 技术 |

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

+----+-----------+--------+------+--------+------+--------------+

5 rows in set (0.00 sec)

通过对笛卡尔积的再处理，得出了两张表有对应关系的部分。但是两张表上，对方没有对上的记录并没有显示出来。因此需要使用mysql给的专用处理工具来处理。

2、内连接：只连接匹配的行

即取两张表共同部分，相当于上面利用条件从笛卡尔积结果中筛选出正确结果。

#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来

mysql> select * from employee inner join department on employee.dep_id =department.id;+----+-----------+--------+------+--------+------+--------------+

| id | name | sex | age | dep_id | id | name |

+----+-----------+--------+------+--------+------+--------------+

| 1 | egon | male | 18 | 200 | 200 | 技术 |

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

+----+-----------+--------+------+--------+------+--------------+

5 rows in set (0.00sec)

# 与上面笛卡尔where筛选的执行结果完全一样

3、外链接之左连接：在内连接的基础上保留左表的记录

在内连接的基础上保留左表有但右边没有的记录——以左表为准，找出所有员工信息，包括没有部门的员工。

mysql> select * from employee left join department on employee.dep_id =department.id;+----+------------+--------+------+--------+------+--------------+

| 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 |

+----+------------+--------+------+--------+------+--------------+

6 rows in set (0.00 sec)

没有对应的记录也显示出来了，并用NULL显示。

4、外链接之右连接：在内连接的基础上保留右表的记录

在内连接的基础上增加右边有左边没有的结果——以右表为准，找出所有部门信息，包括没有员工的部门。

mysql> select * from employee right join department on employee.dep_id =department.id;+------+-----------+--------+------+--------+------+--------------+

| id | name | sex | age | dep_id | id | name |

+------+-----------+--------+------+--------+------+--------------+

| 1 | egon | male | 18 | 200 | 200 | 技术 |

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

+------+-----------+--------+------+--------+------+--------------+

6 rows in set (0.00 sec)

由于employee表没有运营部门员工信息，以NULL显示。

5、全外连接：在内连接的基础上保留，左右两个表没有对应关系的记录

在内连接的基础上增加左边有右边没有和右边有左边没有的结果——显示全部记录

注意：mysql不支持full join

mysql> select * from employee full join department on employee.dep_id =department.id;

ERROR1054 (42S22): Unknown column ‘employee.dep_id‘ in ‘on clause‘

mysql使用union来间接实现全外连接

mysql> select * from employee left join department on employee.dep_id =department.id-> union

-> select * from employee right join department on employee.dep_id =department.id;+------+------------+--------+------+--------+------+--------------+

| 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 |

+------+------------+--------+------+--------+------+--------------+

7 rows in set (0.01 sec)

注意union与union all的区别：union会去掉相同的记录。

原文：https://www.cnblogs.com/xiugeng/p/9030720.html

张兴艺

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql经典四表查询_mysql四-2：多表查询

一、介绍本节主题：多表连接查询复合条件连接查询子查询准备表：#建表create tabledepartment(idint,namevarchar(20));create tableemployee(idint primary keyauto_increment,namevarchar(20),sex enum(‘male‘,‘female‘) not null default ‘male‘,ag...
复制链接

扫一扫