一篇文章带你弄懂多表查询是个什么操作

最新推荐文章于 2025-03-17 18:34:25 发布

Alfredorw

最新推荐文章于 2025-03-17 18:34:25 发布

阅读量986

点赞数 18

分类专栏：数据库详细基础教程文章标签：数据库 sql mysql 后端

本文链接：https://blog.youkuaiyun.com/2301_80263861/article/details/140011169

版权

数据库详细基础教程专栏收录该内容

10 篇文章

订阅专栏

多表查询

多表查询

阅读指南：
本文章讲述了如何在数据库中进行多表查询，讲述了多表查询是如何进行操作的，配有实例，方便大家学习，如果读者感兴趣，后续我们会更新高级的操作在我们的对于数据库教程的合集中，大家可以来很我们一起学习讨论
合集链接：
数据库详细基础教程

多表查询

原理：
将多张表合并成一张表，这样就可以使用我们的单表查询的语法来进行查询信息。

连接方式：

利用多表查询语法合并成单张虚拟表，按照多表合并的方法，分为水平合并和垂直合并语法。

垂直合并：

将多个表汇总，这时候对多张表的主外键没有要求。

水平合并：

将多个表水平排列，这时候对多张表的主外键是有要求的，要相互对应。

垂直合并

语法：

UNION	# 合并记录的同时去掉完全重复的行数据
UNION ALL	# 纯粹的垂直合并记录，不会去掉重复数据

特点：

实现要求：只要求合并的结果集之间的列数和对应列的类型相同即可；

主外键：UNION只是结果垂直汇总，不要求主外键的一致行；

重复数据认定：一行中的所有列值都相同，认定为重复行；

示例：

CREATE TABLE a(
   aid INT,
   aname VARCHAR(10)
);

CREATE TABLE b(
   bid INT,
   bname VARCHAR(10)
);

INSERT INTO a VALUES(1,'aaaa'),(2,'bbbb'),(3,'cccc');
INSERT INTO b VALUES(4,'aaaa'),(2,'bbbb'),(3,'cccc');

# 去重复合并 [将a和b的数据去重复合并成一个结果集]
SELECT aid ,aname FROM a
UNION
SELECT bid ,bname FROM b
UNION 
SELECT bid ,bname FROM b;

# 不去重复合并
SELECT aid ,aname FROM a
UNION ALL
SELECT bid ,bname FROM b
UNION ALL
SELECT bid ,bname FROM b;

♦️水平合并

内连查询——内连接：

将两个表中的主（外）键的数据进行匹配，将相等的主（外）键进行拼接，并返回匹配成功的行。

语法：

SELECT * FROM 表1 [INNER] JOIN 表2 ON 表1.主键 = 表2.外键;	(标准)
SELECT * FROM 表1, 表2 WHERE 表1.主键 = 表2.外键;	(非标准)
# 或者 用对表起别名的方式
SELECT * FROM 表1的别名 [INNER] JOIN 表2的别名 ON 表1的别名.主键 = 表2的别名.外键;	(标准)
SELECT * FROM 表1的别名, 表2的别名 WHERE 表1的别名.主键 = 表2的别名.外键;	(非标准)

外连查询——外连接：

将两个或多个表的数据按照主外键是否相等匹配输出，同时还会返回未匹配的行

与内连查询不同的是：

内连查询返回的是两个表中主外键完全一致的数据，才会被返回；
外连查询则是返回的是所有数据，当确定了一个表为逻辑主表后，返回的内容将会把逻辑主表的数据全部输出，同时也会输出能够匹配上的数据；

语法：

SELECT * FROM 表1（逻辑主表） LEFT [OUTER] JOIN 表2 ON 表1.主键 = 表2.外键（左外）
SELECT * FROM 表1（逻辑主表） RIGHT [OUTER] JOIN 表2 ON 表1.主键 = 表2.外键（右外）

注：开发时常用到

自然连接

是内连接和外连接的升班，会自动找到两个表中相同的列名，但是查询比较繁琐，并且易出错（他不只是判断主外键键是否相等），所以不是很推荐。

语法：

SELECT * FROM emp NAYURAL JOIN dept;	# 自然内连接
SELECT * FROM emp NAYURAL LEFT JOIN dept;	# 自然左外连接
SELECT * FROM emp NAYURAL RIGHT JOIN dept;	# 自然右外连接

自连接

指的是一张表连接自实现特殊的多表查询的情况。例如：查询员工和其对应的领导

只是一种特殊场景的操作，没有独特的语法，可以认为是一种思想。

示例：

基本数据的创建：

# 准备数据 【部门表，员工表，职位表】
CREATE TABLE `t_department` (
  `did` INT NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  `dname` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门名称',
  `description` VARCHAR(200) DEFAULT NULL COMMENT '部门简介',
  PRIMARY KEY (`did`),
  UNIQUE KEY `dname` (`dname`)
);

INSERT  INTO `t_department`(`did`,`dname`,`description`) 
VALUES (1,'研发部','负责研发工作'),
(2,'人事部','负责人事管理工作'),
(3,'市场部','负责市场推广工作'),
(4,'财务部','负责财务管理工作'),
(5,'后勤部','负责后勤保障工作'),
(6,'测试部','负责测试工作');

CREATE TABLE `t_job` (
  `jid` INT NOT NULL AUTO_INCREMENT COMMENT '职位编号',
  `jname` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '职位名称',
  `description` VARCHAR(200) DEFAULT NULL COMMENT '职位简介',
  PRIMARY KEY (`jid`),
  UNIQUE KEY `jname` (`jname`)
);

INSERT  INTO `t_job`(`jid`,`jname`,`description`) 
VALUES (1,'技术总监','负责技术指导工作'),
(2,'项目经理','负责项目管理工作'),
(3,'程序员','负责开发工作'),
(4,'测试员','负责测试工作'),
(5,'人事主管','负责人事管理管理'),
(6,'人事专员','负责人事招聘工作'),
(7,'运营主管','负责市场运营管理工作'),
(8,'市场员','负责市场推广工作'),
(9,'财务主管','负责财务工作'),
(10,'出纳','负责出纳工作'),
(11,'后勤主管','负责后勤管理工作'),
(12,'网络管理员','负责网络管理');

DROP TABLE IF EXISTS `t_employee`;

CREATE TABLE `t_employee` (
  `eid` INT PRIMARY KEY  AUTO_INCREMENT COMMENT '员工编号',
  `ename` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名',
  `salary` DOUBLE NOT NULL COMMENT '薪资',
  `commission_pct` DECIMAL(3,2) DEFAULT NULL COMMENT '奖金比例',
  `birthday` DATE NOT NULL COMMENT '出生日期',
  `gender` ENUM('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '男' COMMENT '性别',
  `tel` CHAR(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号码',
  `email` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱',
  `address` VARCHAR(150) DEFAULT NULL COMMENT '地址',
  `work_place` SET('北京','深圳','上海','武汉') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '北京' COMMENT '工作地点',
  `hiredate` DATE NOT NULL COMMENT '入职日期',
  `job_id` INT DEFAULT NULL COMMENT '职位编号',
  `mid` INT DEFAULT NULL COMMENT '领导编号',
  `did` INT DEFAULT NULL COMMENT '部门编号'
);

INSERT  INTO `t_employee`(`eid`,`ename`,`salary`,`commission_pct`,`birthday`,`gender`,`tel`,`email`,`address`,`work_place`,`hiredate`,`job_id`,`mid`,`did`) 
VALUES (1,'孙洪亮',28000,'0.65','1980-10-08','男','13789098765','shl@atguigu.com','白庙村西街','北京,深圳','2011-07-28',1,1,1),
(2,'何进',7001,'0.10','1984-08-03','男','13456732145','hj@atguigu.com','半截塔存','深圳,上海','2015-07-03',2,1,1),
(3,'邓超远',8000,NULL,'1985-04-09','男','18678973456','dcy666@atguigu.com','宏福苑','北京,深圳,上海,武汉','2014-07-01',3,7,1),
(4,'黄熙萌',9456,NULL,'1986-09-07','女','13609876789','hxm@atguigu.com','白庙村东街','深圳,上海,武汉','2015-08-08',8,22,3),
(5,'陈浩',8567,NULL,'1978-08-02','男','13409876545','ch888@atguigu.com','回龙观','北京,深圳,上海','2015-01-01',3,7,1),
(6,'韩庚年',12000,NULL,'1985-04-03','男','18945678986','hgn@atguigu.com','龙泽','深圳,上海','2015-02-02',3,2,1),
(7,'贾宝玉',15700,'0.24','1982-08-02','男','15490876789','jby@atguigu.com','霍营','北京,武汉','2015-03-03',2,1,1),
(8,'李晨熙',9000,'0.40','1983-03-02','女','13587689098','lc@atguigu.com','东三旗','深圳,上海,武汉','2015-01-06',4,1,1),
(9,'李易峰',7897,NULL,'1984-09-01','男','13467676789','lyf@atguigu.com','西山旗','武汉','2015-04-01',3,7,1),
(10,'陆风',8789,NULL,'1989-04-02','男','13689876789','lf@atguigu.com','天通苑一区','北京','2014-09-03',2,1,1),
(11,'黄冰茹',15678,NULL,'1983-05-07','女','13787876565','hbr@atguigu.com','立水桥','深圳','2014-04-04',4,1,1),
(12,'孙红梅',9000,NULL,'1986-04-02','女','13576234554','shm@atguigu.com','立城苑','上海','2014-02-08',3,7,1),
(13,'李冰冰',18760,NULL,'1987-04-09','女','13790909887','lbb@atguigu.com','王府温馨公寓','北京','2015-06-07',3,2,1),
(14,'谢吉娜',18978,'0.25','1990-01-01','女','13234543245','xjn@atguigu.com','园中园','上海,武汉','2015-09-05',5,14,2),
(15,'董吉祥',8978,NULL,'1987-05-05','男','13876544333','djx@atguigu.com','小辛庄','北京,上海','2015-08-04',6,14,2),
(16,'彭超越',9878,NULL,'1988-03-06','男','18264578930','pcy@atguigu.com','西二旗','深圳,武汉','2015-03-06',8,22,3),
(17,'李诗雨',9000,NULL,'1990-08-09','女','18567899098','lsy@atguigu.com','清河','北京,深圳,武汉','2013-06-09',8,22,3),
(18,'舒淇格',16788,'0.10','1978-09-04','女','18654565634','sqg@atguigu.com','名流花园','北京,深圳,武汉','2013-04-05',9,18,4),
(19,'周旭飞',7876,NULL,'1988-06-13','女','13589893434','sxf@atguigu.com','小汤山','北京,深圳','2014-04-07',10,18,4),
(20,'章嘉怡',15099,'0.10','1989-12-11','女','15634238979','zjy@atguigu.com','望都家园','北京','2015-08-04',11,20,5),
(21,'白露',9787,NULL,'1989-09-04','女','18909876789','bl@atguigu.com','西湖新村','上海','2014-06-05',12,20,5),
(22,'刘烨',13099,'0.32','1990-11-09','男','18890980989','ly@atguigu.com','多彩公寓','北京,上海','2016-08-09',7,22,3),
(23,'陈纲',13090,NULL,'1990-02-04','男','18712345632','cg@atguigu.com','天通苑二区','深圳','2016-05-09',3,2,1),
(24,'吉日格勒',10289,NULL,'1990-04-01','男','17290876543','jrgl@163.com','北苑','北京','2017-02-06',12,20,5),
(25,'额日古那',9087,NULL,'1989-08-01','女','18709675645','ergn@atguigu.com','望京','北京,上海','2017-09-01',3,2,1),
(26,'李红',5000,NULL,'1995-02-15','女','15985759663','lihong@atguigu.com','冠雅苑','北京','2021-09-01',12,23,1),
(27,'周洲',8000,NULL,'1990-01-01','男','13574528569','zhouzhou@atguigu.com','冠华苑','北京,深圳','2020-08-15',3,NULL,NULL);

内连查询：

# 情景1:查询[[员工编号、姓名以及所属部门的编号 ]]和[[部门名称]] -> 多表查询 -> 水平多表查询 -> 关系
SELECT e.eid , e.ename, e.did , d.did , d.dname FROM t_employee e INNER JOIN t_department d;
# 非标准语法  表1 别名 , 表2 别名 where 主 = 外
SELECT e.eid , e.ename, e.did , d.did , d.dname FROM t_employee e,t_department d;

# 情景2:查询员工编号、姓名以及所属部门的编号和部门名称
SELECT e.eid , e.ename, e.did , d.did , d.dname FROM t_employee e INNER JOIN t_department d  ON e.did = d.did;
SELECT e.eid , e.ename, e.did , d.did , d.dname FROM t_employee e,t_department d WHERE e.did = d.did;    

# 情景3:查询员工编号、姓名以及所属部门的编号和部门名称
SELECT e.eid , e.ename, e.did , d.did , d.dname FROM t_employee e JOIN t_department d  ON e.did = d.did;

# 情景4:查询员工编号大于10的[员工编号、姓名以及所属部门的编号和部门名称]
SELECT e.eid , e.ename, e.did , d.did , d.dname FROM t_employee e 
                                                  INNER JOIN t_department d  ON e.did = d.did
                                                  WHERE e.eid > 10;                                                                                                   
SELECT e.eid , e.ename, e.did , d.did , d.dname FROM 
                                                  t_employee e,t_department d 
                                                  WHERE e.did = d.did AND  e.eid > 10; 

# 如果有额外的条件(非主外键相等) 两种都正常添加where即可! 添加条件
# 理解: 多表查询 -> 表结果进行拼接 -> 依然正常使用查询语法


# 情景5: 多表(3+)查询并且添加额外的条件筛选
# 查询员工编号大于10的   员工编号、姓名以及所属部门的编号和部门名称,岗位名称
# 员工和部门 | 员工和岗位

SELECT e.eid , e.ename , e.did , d.did , d.dname , e.job_id , j.jname FROM t_employee e 
                                                INNER JOIN t_department d ON e.did = d.did
                                                INNER JOIN t_job j ON e.job_id = j.jid
                                                WHERE e.eid > 10;
SELECT e.eid , e.ename , e.did , d.did , d.dname , e.job_id , j.jname 
                                                FROM t_employee e , t_department d ,t_job j 
                                                WHERE e.did = d.did AND e.job_id = j.jid AND e.eid > 10;
/*
  多表查询的方法：
  1. 有三张表至少有两对主外键相等条件 and 
  2. 有N张表,至少有N-1对主外键相等条件 and
  3. 语法
     标准
     表1 INNER JOIN 表2 ON 主=外
         INNER JOIN 表3 ON 主=外
         INNER JOIN 表4 ON 主=外 ..
         where 其他的条件
     非标准
     from 表1 , 表2 ,表3 .... 表n where (n-1对的) 主外键相等and | 其他条件继续添加即可and 其他条件即可       
*/

外连查询：

# 情景1:查询[所有]员工编号、姓名以及所属部门的编号和部门名称
SELECT e.eid,e.ename, IFNULL(d.dname,'暂时未分配') FROM t_employee e  LEFT JOIN t_department d  ON e.did = d.did;
SELECT e.eid,e.ename, IFNULL(d.dname,'暂时未分配') FROM t_department d  RIGHT JOIN  t_employee e  ON e.did = d.did;

# 情景2:查询所有员工编号、姓名以及所属部门的编号和部门名称
SELECT e.eid,e.ename, IFNULL(d.dname,'暂时未分配') FROM t_department d  RIGHT JOIN  t_employee e  ON e.did = d.did;


# 情景3:查询员工编号大于10的员工编号、姓名以及所属部门的编号和部门名称
SELECT e.eid,e.ename, IFNULL(d.dname,'暂时未分配') FROM t_department d  RIGHT JOIN  t_employee e  ON e.did = d.did WHERE e.eid > 10;

# 情景4: 多表(3+)查询并且添加额外的条件筛选 
# 查询员工编号大于10的员工编号、姓名以及所属部门的编号和部门名称,岗位名称
# 先找到逻辑主表,把逻辑主表放在第一位,后续都是left join 
SELECT e.eid,e.ename, IFNULL(d.dname,'暂时未分配') , j.jname FROM t_employee e 
                                                        LEFT JOIN  t_department d   ON e.did = d.did
                                                        LEFT JOIN  t_job j ON e.job_id = j.jid
                                                   WHERE e.eid > 10;

自然连接查询：

# 情景1:查询所有有部门的员工编号、姓名以及所属部门的编号和部门名称
SELECT e.eid , e.ename , e.did , d.dname FROM t_employee e NATURAL JOIN t_department d; #省略了on
SELECT e.eid , e.ename , e.job_id , j.jname FROM t_employee e NATURAL JOIN t_job j; #省略了on 主外键命名不同

# 情景2:查询所有员工编号、姓名以及所属部门的编号和部门名称
SELECT e.eid , e.ename , e.did , d.dname FROM t_employee e NATURAL LEFT JOIN t_department d;

SELECT e.eid , e.ename , e.mid FROM t_employee e

自连接：

# 情景1:查询编号等于5号员工的编号,姓名,领导编号,领导姓名
SELECT e1.eid , e1.ename , e1.mid , e2.ename  FROM t_employee e1 LEFT JOIN t_employee e2 ON e1.mid = e2.eid WHERE e1.eid = 5;

# 情景2:查询编号等于5号员工的编号,姓名,领导编号,领导姓名 ,以及领导的领导编号和姓名
SELECT e1.eid , e1.ename , e1.mid , e2.ename , e2.mid , e3.ename  FROM t_employee e1 LEFT JOIN t_employee e2 ON e1.mid = e2.eid