【MySQL - 6】多表关系是什么？如何进行多表查询？内连接、外连接、子查询（含超多练习）

最新推荐文章于 2024-06-04 23:49:31 发布

Beeemo

最新推荐文章于 2024-06-04 23:49:31 发布

阅读量514

点赞数

分类专栏： MySQL入门文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_45453739/article/details/107875972

版权

MySQL入门专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1. 多表关系

多表关系，是指表与表之间的关系，在上一篇文章中，我们说到了表与表之间的关系是靠外键来建立的，下面来详细说说具体有哪些多表关系

1.1 一对多关系

一对多关系，一个很简单的例子，一个部门可以对应多个员工，其实现方式也很简单，在“多”的一方建立外键指向“一”的主键即可，下面就来演示一下这个例子

# 一对多关系 一个部门多个员工
CREATE TABLE onemore1(
	id INT,
	NAME VARCHAR(8), 
	dept INT, 
	CONSTRAINT 1om2 FOREIGN KEY (dept) REFERENCES onemore2(id)
);
CREATE TABLE onemore2(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(8)
);

来看看他的架构图
在这里插入图片描述

1.2 多对多关系

多对多关系，可能大家一下子反应不过来有什么是多对多的，其实也有一个很简单的例子，一个学生可以选择多门课程，一门课程也可以被多个学生所选择，因此学生与课程之间就是多对多的关系，那怎么来实现多对多关系呢？这里就注意了，多对多关系需要建立一张新表作为中间表来建立多对多关系，新表上的两列分别建立外键指向学生表与课程表，有点抽象？没关系，我们来演示一下

# 多对多关系 学生与课程
CREATE TABLE moremore1(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(8),
	lect INT
);
CREATE TABLE moremore2(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(8)
);
-- 中间表
CREATE TABLE moremore3(
	stu INT, 
	luct INT, 
	CONSTRAINT 1mm3 FOREIGN KEY (stu) REFERENCES moremore1(id), 
	CONSTRAINT 2mm3 FOREIGN KEY (luct) REFERENCES moremore2(id)
);

不太清楚中间关系？来看看架构图
在这里插入图片描述

1.3 一对一关系

最后是一对一关系，一对一是啥？很简单，学号和姓名对吧，那怎么实现一对一关系呢？别被我绕进去了，一对一关系直接写在同一张表上不就好了嘛

1.4 小案例

关系介绍完了，但是似乎上面的例子不够复杂对吧，印象一定不够深刻，这里来做一个四个表之间的多表关系

以下是情境引入：例如某某旅游网站，有很多的旅游线路分类，点进某个分类后你可以看见许多旅游线路，若你对这个旅游线路感兴趣你可以加入你自己的购物车，同样道理，这个旅游线路也可以被加进你朋友的购物车

接下来分析一下刚刚的情境：一个分类下有多个线路，一对多关系；一个人可以收藏多个旅游线路，一条旅游线路也可以被多个人收藏，多对多关系；所涉及的表有分类表、线路表、个人表、中间表

那么我们来演示一下这个栗子

# 多表关系 旅游网
CREATE TABLE catalog(
	cid INT PRIMARY KEY, 
	NAME VARCHAR(16)
);
CREATE TABLE route(
	rid INT PRIMARY KEY,
	NAME VARCHAR(16), 
	cata INT, 
	CONSTRAINT rc FOREIGN KEY(cata) REFERENCES catalog(cid)
);
CREATE TABLE customer(
	id INT PRIMARY KEY, 
	NAME VARCHAR(8)
);
-- 中间表
CREATE TABLE r_and_c(
	cuid INT, 
	rid INT, 
	CONSTRAINT cc FOREIGN KEY(cuid) REFERENCES customer(id), 
	CONSTRAINT rr FOREIGN KEY(rid) REFERENCES route(rid)
);

给你们直观看看这个架构图
在这里插入图片描述

2. 多表查询

多表关系就介绍完了，其实多表关系理解都很简单，不难；而多表查询就没那么简单了，下面来举个例子说明一下什么是多表查询，这里有一个员工表和部门表

-- 部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(20)
);
INSERT INTO dept(NAME) VALUES("研发部"),("市场部"),("财务部");
-- 员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(8), 
	gender VARCHAR(8), 
	salary INT, 
	joindate DATE, 
	dept INT, 
	FOREIGN KEY(dept) REFERENCES dept(id)
);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("孙悟空","男",7200,"2013-02-24",1);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("猪八戒","男",3600,"2010-12-02",2);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("唐僧","男",9000,"2008-08-08",2);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("白骨精","女",5000,"2015-10-07",3);
INSERT INTO emp(NAME, gender, salary, joindate, dept) VALUES("蜘蛛精","女",4500,"2011-03-14",1);

倘若我要查询每位员工的个人信息以及对应的部门信息怎么办呢？分别SELECT一下？
在这里插入图片描述

不行呀，查看不在同一个表，看起来不方便不直观，那这时候就需要用到多表查询了，先来说说格式
SELECT 列名1, 列名2, ... , 列名n FROM 表名1, 表名2, ... , 表名n;

那么我们来试一下利用多表查询查一下这两个表，看一下可不可以得出每位员工的个人信息与部门信息

SELECT * FROM dept,emp;

在这里插入图片描述
Emmm…综合表是查出来了，但是里面有些不符合的数据呀，为什么会这样子呢？其原因是在进行多表查询时是默认把所提供的表进行笛卡尔积运算得出结果（不晓得啥是笛卡尔积的同学自行百度）因此我们需要运用一些手段来得出我们所想要的结果，下面来介绍一下具体有哪些手段可以使用

2.1 内连接

内连接分为隐式内连接与显式内连接，内连接查询的逻辑是：从哪些表中查询数据、查询条件是什么、查询什么东西，下面来分别介绍一下

隐式内连接：

隐式内连接的思想是使用WHERE来消除无用条件，怎么消除呢？例如刚刚所查询出来的例子而言，认真观察一下你会发现每个人的部门都不完全对应的，因此在筛选时我们可以以部门为条件进行筛选

SELECT * FROM emp,dept WHERE emp.`dept`=dept.`id`;

在这里插入图片描述
显然这个结果已经是我们想要的了

显式内连接：

显式内连接的思想其实同样是消除无用字段，但他的方式并不是使用WHERE，而是使用... INNER JOIN ... ON ...，其详细的使用格式如下
SELECT 列名1, 列名2, ... , 列名n FROM 表1 INNER JOIN 表2 ON 条件;

另外，INNER关键字可以省略，同样刚才的例子，我们来演示一下

select * from emp join dept on emp.`dept`=dept.id;

在这里插入图片描述
同样也是可以得到我们所期望的结果

2.2 外连接

外连接分为左外连接与右外连接，其功能是查询某表的所有数据及其与其他表的交集部分，有点点抽象？别急，先继续看下去

左外连接：

语法：SELECT 列名1, 列名2, ... , 列名n FROM 表1 LEFT JOIN 表2 ON 条件;，其含义是查询出表1的所有数据以及表2中符合条件的数据

下面来演示一下前面的例子

SELECT * FROM emp LEFT JOIN dept ON emp.`dept`=dept.`id`;

在这里插入图片描述

右外连接：

右外连接其实是跟左外连接相对的，语法：SELECT 列名1, 列名2, ... , 列名n FROM 表1 RIGHT JOIN 表2 ON 条件;，其含义是查询出表2的所有数据以及表1中符合条件的数据

下面来演示一下前面的例子

SELECT * FROM emp RIGHT JOIN dept ON emp.`dept`=dept.`id`;

在这里插入图片描述
大家有没有发现外连接中的左右是什么意思呢？在左外连接的语法中，处于左边（前面）的表所有数据都会显示；在右外连接的语法中，处于右边（后面）的表所有数据都会显示，因此其实两种方式的都可以达到一模一样的效果，平常使用上我们比较习惯使用左外链接

2.3 子查询

子查询，其实通俗一点的意思是嵌套查询，同样是不懂啥意思对吧？来直接看例子，还是前面用前面所建立的员工表，现在我需要查询工资最高的员工信息，怎么做呢？我们可以拆成两步

第一步，找出最高工资是多少

SELECT MAX(salary) FROM emp;

在这里插入图片描述
第二步，根据该最高工资查询员工信息

SELECT * FROM emp WHERE emp.`salary`=9000;

在这里插入图片描述
显然，结果是拿到了，可是分两步做是不是太麻烦了？有没有方法可以一步完成的？那肯定是有的，我们可以利用子查询来干这个事情

SELECT * FROM emp WHERE emp.`salary`=(SELECT MAX(salary) FROM emp);

在这里插入图片描述
这样一条语句就可以得到我们想要的结果了，这种写法其实是子查询的第一种情况，子查询一共有三种不同的情况，下面来详细介绍一下：

情况1，子查询的结果是单行单列的：

这是什么意思呢？这里结合一个栗子来讲一下，现在我的需求是查询员工工资小于平均工资的人，看到这个需求后我们要解决的第一件事情是找出平均工资究竟是多少

SELECT AVG(salary) FROM emp;

在这里插入图片描述
第二件事情是根据这个平均工资来找出符合条件的人

SELECT * FROM emp WHERE emp.`salary`< 5860;

在这里插入图片描述
有没有注意到我们刚刚的第一件事情所查出来的表是单行单列的，这就是子查询的情况1，这个第一件事情的查询语句可以作为一个子查询放到第二件事情的查询语句的条件当中

SELECT * FROM emp WHERE emp.`salary`< (SELECT AVG(salary) FROM emp);

情况2，子查询的结果是多行单列的：

同样结合一个栗子来讲一下，现在我的需求是查询财务部和研发部所有的员工信息，看到这个需求后我们要解决的第一件事情是找出财务部和研发部对应的部门代号是啥

SELECT id FROM dept WHERE NAME IN("财务部","研发部");

在这里插入图片描述
第二件事情是根据这个部门代号来找出符合条件的人

SELECT * FROM emp WHERE dept IN(1,3);

在这里插入图片描述
有没有注意到我们刚刚的第一件事情所查出来的表是多行单列的，这就是子查询的情况2，这个第一件事情的查询语句同样也可以作为一个子查询放到第二件事情的查询语句的条件当中，使用运算符IN来判断

SELECT * FROM emp WHERE dept IN(SELECT id FROM dept WHERE NAME IN("财务部","研发部"));

情况3，子查询的结果是多行多列的：

同样结合一个栗子来讲一下，现在我的需求是查询入职日期是2011-11-11之后的员工信息和部门信息，看到这个需求后我们要解决的第一件事情是找出职日期是2011-11-11之后的员工信息

SELECT * FROM emp WHERE emp.`joindate`>"2011-11-11";

在这里插入图片描述
第二件事情是找出这些员工对应的部门信息一并显示

哎呀，是不是好像不知道怎么实现这个东西呢？好像需要两个表才可以做到呀，对，有注意到刚刚第一件事情查出来的表是多行多列的吗，这就是我们将要介绍的情况3，这个第一件事情的查询结果可以作为一张虚拟表放到第二件事情的查询语句的表列表当中

SELECT * FROM (SELECT * FROM emp WHERE emp.`joindate`>"2011-11-11") t1,dept t2 WHERE t1.`dept`=t2.`id`;

在这里插入图片描述
除此以外，其实该需求还可以使用前面所介绍的内连接来完成

SELECT * FROM emp,dept WHERE emp.`dept`=dept.`id` AND emp.`joindate`>"2011-11-11"; -- 内连接

2.4 综合练习

相关内容介绍完了，为了巩固一下相关语法的使用，这里来给一个综合练习，一共有六个小题，这里给出几个表创建的原始代码，以方便大家练习

部门表

-- 部门表
CREATE TABLE pdept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	city VARCHAR(8)
);
INSERT INTO 
	pdept(id,NAME,city) 
VALUES
	(10,"教研部","北京"),
	(20,"学工部","上海"),
	(30,"销售部","广州"),
	(40,"财务部","深圳");

在这里插入图片描述
职务表

-- 职务表
CREATE TABLE pjob(
	id INT PRIMARY KEY,
	NAME VARCHAR(20),
	description VARCHAR(50)
);
INSERT INTO
	pjob(id,NAME,description)
VALUES
	(1,"董事长","管理公司"),
	(2,"经理","管理部门"),
	(3,"销售","销售产品"),
	(4,"文员","处理事务");

在这里插入图片描述
员工表

-- 员工表
CREATE TABLE pemp(
	id INT PRIMARY KEY,
	NAME VARCHAR(8),
	job INT,
	manager INT,
	jobdate DATE,
	salary DOUBLE(7,2),
	bonus DOUBLE(7,2),
	dept INT,
	CONSTRAINT jobkey FOREIGN KEY(job) REFERENCES pjob(id),
	CONSTRAINT managerkey FOREIGN KEY(manager) REFERENCES pemp(id),
	CONSTRAINT deptkey FOREIGN KEY(dept) REFERENCES pdept(id)
);
INSERT INTO 
	pemp(id,NAME,job,manager,jobdate,salary,bonus,dept)
VALUES
	(1001,"孙悟空",4,1004,"2000-12-17",8000.00,NULL,20),
	(1002,"卢俊义",3,1006,"2001-02-20",16000.00,3000.00,30),
	(1003,"林冲",3,1006,"2001-02-22",12500.00,5000.00,30),
	(1004,"唐僧",2,1009,"2001-04-02",29750.00,NULL,20),
	(1005,"李逵",4,1006,"2001-09-28",12500.00,14000.00,30),
	(1006,"宋江",2,1009,"2001-05-01",28500.00,NULL,30),
	(1007,"刘备",2,1009,"2001-09-01",24500.00,NULL,10),
	(1008,"猪八戒",4,1004,"2007-04-19",30000.00,NULL,20),
	(1009,"罗贯中",1,NULL,"2001-11-17",50000.00,NULL,10),
	(1010,"吴用",3,1006,"2001-09-08",15000.00,0.00,30),
	(1011,"沙僧",4,1004,"2007-05-23",11000.00,NULL,20),
	(1012,"李逵",4,1006,"2001-12-03",9500.00,NULL,30),
	(1013,"小白龙",4,1004,"2001-12-03",30000.00,NULL,20),
	(1014,"关羽",4,1007,"2002-01-23",13000.00,NULL,10);

在这里插入图片描述
工资等级表

-- 工资等级表
CREATE TABLE psalary(
	grade INT PRIMARY KEY,
	low INT,
	high INT
);
INSERT INTO 
	psalary(grade,low,high) 
VALUES
	(1,7000,12000),
	(2,12001,14000),
	(3,14001,20000),
	(4,20001,30000),
	(5,30001,99999);

所有原始表都已经给出了，下面就来看一下这六道小题目是啥
在这里插入图片描述
大家可以自己先做一下，做不出来再来看看我一下的参考代码以及结果

需求一

-- 需求一：查询所有员工信息，查询员工编号、员工姓名、工作、职务名称、职务描述
SELECT
	t1.`id`,
	t1.`name`,
	t2.*
FROM
	pemp t1,pjob t2
WHERE
	t1.`job`=t2.`id`;

在这里插入图片描述
需求二

-- 需求二：查询员工编号、员工姓名、工资、职务名称、职务描述、部门名称、部门位置
SELECT
	t1.`id`,
	t1.`name`,
	t1.`salary`,
	t2.`name`,
	t2.`description`,
	t3.`name`,
	t3.`city`
FROM
	pemp t1,pjob t2,pdept t3
WHERE
	t1.`job`=t2.`id`
	AND t1.`dept`=t3.`id`;

在这里插入图片描述
需求三

-- 需求三：查询员工姓名、工资、工资等级
SELECT
	t1.`name`,
	t1.`salary`,
	t2.`grade`
FROM
	pemp t1,psalary t2
WHERE
	t1.`salary` BETWEEN t2.`low` AND t2.`high`;

在这里插入图片描述
需求四

-- 需求四：查询员工姓名、工资、职务名称、职务描述、部门名称、部门位置、工资等级
SELECT
	t1.`name`,
	t1.`salary`,
	t2.`name`,
	t2.`description`,
	t3.`name`,
	t3.`city`,
	t4.`grade`
FROM
	pemp t1,pjob t2,pdept t3,psalary t4
WHERE
	t1.`job`=t2.`id`
	AND t1.`dept`=t3.`id`
	AND t1.`salary` BETWEEN t4.`low` AND t4.`high`;

在这里插入图片描述
需求五

-- 需求五：查询部门编号、部门名称、部门位置、部门人数
		-- 方法一
SELECT
	t1.*,
	IFNULL(t3.`num`,0) num
FROM
	pdept t1 LEFT JOIN (SELECT t2.`dept` id,COUNT(t2.`dept`) num FROM pemp t2 GROUP BY t2.`dept`) t3
ON 
	t1.`id`=t3.`id`;
		-- 方法二
SELECT
	t1.*,
	COUNT(t2.`dept`) num
FROM
	pdept t1 LEFT JOIN pemp t2
ON
	t1.`id`=t2.`dept`
GROUP BY
	t2.`dept`;

在这里插入图片描述
需求六

-- 需求六：查询所有员工的姓名及其直接上级的姓名，没有领导的员工也需要查询
SELECT
	t1.`name`,
	t1.`manager`,
	t2.`name` superior
FROM
	pemp t1 LEFT JOIN pemp t2
ON
	t1.`manager`=t2.`id`;

在这里插入图片描述
The end~该内容可能理解上会比较绕，需要好好理解多加练习

Beeemo

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
【MySQL - 6】多表关系是什么？如何进行多表查询？内连接、外连接、子查询（含超多练习）

目录1. 多表关系1.1 一对多关系1.2 多对多关系1.3 一对一关系1.4 小案例2. 多表查询2.1 内连接2.2 外连接2.3 子查询2.4 综合练习1. 多表关系多表关系，是指表与表之间的关系，在上一篇文章中，我们说到了表与表之间的关系是靠外键来建立的，下面来详细说说具体有哪些多表关系1.1 一对多关系一对多关系，一个很简单的例子，一个部门可以对应多个员工，其实现方式也很简单，在“多”的一方建立外键指向“一”的主键即可，下面就来演示一下这个例子# 一对多关系一个部门多个员工CREAT
复制链接

扫一扫