likes
comments
collection
share

MySQL多表查询

作者站长头像
站长
· 阅读数 44

写在前面👀

多表查询的过程：先计算多个表的笛卡尔积，在基于一些条件针对笛卡尔积中的记录进行筛选

注意: 多表查询时,要用两个表中相关联的字段作为条件使用

一、数据准备✨

-- 创建人物身份表
CREATE TABLE identity(
id INT PRIMARY KEY AUTO_INCREMENT,# 主键
 name varchar(20)                 # 身份名称
);
-- 添加部门表数据
INSERT INTO identity (name) VALUES('海贼'),('海军'),('革命军'),('天龙人');

-- 创建op人物表
CREATE TABLE op (
	id INT PRIMARY KEY AUTO_INCREMENT,# 主键
	name VARCHAR(20),                # 姓名
	gender enum('男','女','人妖'),# 性别
	fruit VARCHAR(30), # 果实能力
    reward BIGINT, # 悬赏金,单位:贝利
	ity_id INT,  # 外键
	FOREIGN KEY (ity_id) REFERENCES identity (id) # op表关联identity表
);

-- 添加人物数据
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('路飞',1,'人人果实-幻兽种-尼卡形态',1500000000,1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('索隆',1,null,320000000,1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('黄猿',1,'闪闪果实',null,2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('亚尔丽塔',2,'滑滑果实',5000000,1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('伊万科夫',3,'荷尔蒙果实',null,3);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('萨博',1,'烧烧果实',602000000,3);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('小冯',3,'模仿果实',32000000,1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('艾斯',1,'烧烧果实',550000000,1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('达斯琪',2,null,null,2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('小萨蒂',2,null,null,2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('战国',1,'人人果实-幻兽种-大佛形态',null,2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('熊',1,'肉球果实',296000000,3);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('红发',1,'面子果实',4028900000,1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('罗宾',2,'花花果实',130000000,1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('Joker',1,'线线果实',340000000,null);

二、笛卡尔积现象🎃

笛卡尔积的本质：排列组合

1️⃣笛卡尔乘积🎭

笛卡尔乘积公式 : A表中数据数 * B表中数据数 = 笛卡尔乘积

-- 需求：查询所有的人物和他们的身份
SELECT * FROM op,identity; # 这样查询会显示15*4=60种记录
  • 如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔积现象👇MySQL多表查询

2️⃣如何清除笛卡尔积现象的影响👨‍🔧

  • 我们发现不是所有的数据组合都是有用的，所以需要通过条件过滤掉没用的数据。
/* 设置过滤条件 */ 
-- op表的ity_id等于identity表的id的数据才是有用的
SELECT * FROM op,identity WHERE op.ity_id=identity.id;
-- 多表查询的时候我们习惯用别名,L代表左表，R代表右表
SELECT * FROM op AS L,identity AS R WHERE L.ity_id=R.id;
  • 演示结果如下👇MySQL多表查询

三、内连接查询🌹

内连接查询只显示符合条件的数据，有两种内连接方式：隐式内连接，显示外连接

  • 内连接查询的是左表与右表的交集部分:A∩B，如下图所示👇

MySQL多表查询

1️⃣隐式内连接🍄

  • 上面也提到过用WHERE设置关联条件可以过滤冗余数据
/* 语法 */
SELECT 字段名 FROM 左表, 右表 WHERE 条件
/* 示例 */
-- 查询所有的人物和他们的身份
SELECT * FROM op,identity WHERE op.ity_id=identity.id;

2️⃣显示内连接🥕

  • 使用 INNER JOIN ... ON 语句, 可以省略 INNER
/* 语法 */
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
/* 示例 */
-- 需求：查询小冯的果实能力及身份
# 1.确定查询哪些表
SELECT * FROM op AS L INNER JOIN identity AS R;
# 2.确定表连接条件
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id;
# 3.确定查询条件
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id WHERE L.name='小冯';
# 4.确定查询字段
SELECT L.name,L.fruit,R.name FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id WHERE L.name='小冯';
# 5.仅供参考的标准写法，分行写方便添加注释
SELECT 
	L.name AS 姓名 ,    -- 查询字段：人物姓名
	L.fruit AS 果实 ,   -- 查询字段：人物果实能力
	R.name AS 身份      -- 查询字段：人物身份
FROM 
	op AS L            -- 查询左表：op 
INNER JOIN 
	identity AS R      -- 查询右表：identity
ON 
	L.ity_id=R.id      -- 连接条件：op表的ity_id与identity表的id相同
WHERE 
	L.name='小冯';     -- 查询条件：姓名为小冯的人物

  • 演示结果如下👇MySQL多表查询

  • 当然你用WHERE AND查询，结果也是一样的

SELECT 
	L.name AS 姓名 ,    -- 查询字段：人物姓名
	L.fruit AS 果实 ,   -- 查询字段：人物果实能力
	R.name AS 身份      -- 查询字段：人物身份
FROM 
	op AS L,           -- 查询左表：op  
	identity AS R      -- 查询右表：identity
WHERE 
	L.ity_id=R.id      -- 连接条件：op表的ity_id与identity表的id相同
AND 
	L.name='小冯';     -- 查询条件：姓名为小冯的人物

3️⃣总结内连接查询步骤🧅

  1. 确定查询哪些表

  2. 确定表连接条件

  3. 确定查询条件

  4. 确定查询字段

四、外连接查询🍁

分为左外连接和右外连接查询

1️⃣左外连接🌻

  • 返回所有左表中的记录，右侧表中不存在匹配时在相应字段显示NULL。
  • 左外连接使用 LEFT OUTER JOIN ... ON，OUTER 可以省略

MySQL多表查询

/* 语法 */
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
/* 示例 */
-- 使用内连接查询
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id;
-- 使用左外连接查询
SELECT * FROM op AS L LEFT JOIN identity AS R ON L.ity_id=R.id;
  • 演示结果如下👇
    • 使用内连接查询MySQL多表查询
    • 使用左外连接查询MySQL多表查询

2️⃣右外连接🌼

  • 返回所有右表中的记录，左侧表中不存在匹配时在相应字段显示NULL。
  • 右外连接使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略

MySQL多表查询

/* 语法 */
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
/* 示例 */
-- 使用内连接查询
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id;
-- 使用右外连接查询
SELECT * FROM op AS L RIGHT JOIN identity AS R ON L.ity_id=R.id;
  • 演示结果如下👇
    • 使用内连接查询MySQL多表查询
    • 使用右外连接查询MySQL多表查询

3️⃣外连接总结🌸

  1. 左外连接和右外连接其实是一样的道理，一般使用左外连接较多
  2. 外连接可以理解为：在内连接的基础上保证左表(或右表)的数据全部显示，右表(或左表)不符合条件的则显示null

五、子查询🌳

又叫嵌套查询，一个查询的结果可以作为另一个查询的条件或者表名使用

  • 下面是子查询的三种情况👇

1️⃣单行单列🥄

  • 子查询的结果是单行单列，在 WHERE 后面作为条件，父查询使用比较运算符，如：> 、<、<>、= 等
/* 语法 */
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
/* 示例 */
# 需求：查询悬赏金最高的那个男人的全部信息
-- 1.在op表查询最高悬赏金是多少
SELECT MAX(reward) FROM op;
-- 2.根据最高悬赏金到op表查询到对应的人物信息
SELECT * FROM op WHERE reward = (SELECT MAX(reward) FROM op);
  • 演示结果如下👇MySQL多表查询

2️⃣多行单列🍴

  • 子查询结果是多行单列，结果是一个集合，父查询使用 IN 运算符
/* 语法 */
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
/* 示例 */
# 需求：查询有哪些人是海贼和海军
-- 先查询海贼与海军的 id
SELECT id FROM identity WHERE name in('海贼','海军');
-- 再查询在这 id 中有哪些人物
SELECT * FROM op WHERE ity_id in (SELECT id FROM identity WHERE name in('海贼','海军'));
  • 演示结果如下👇MySQL多表查询

3️⃣多行多列🎹

  • 子查询结果是多行多列，在 FROM 后面作为虚拟表
  • 注意：这张虚拟表需要取别名，否则这张表没有名称则无法访问表中的字段
/* 语法 */
SELECT 查询字段 FROM （子查询） AS 虚拟表别名 WHERE 条件;
/* 示例 */
# 需求：查询赏金小于平均值的人物所有信息，包括基本信息和所属所属身份
-- 1.在op表找到赏金小于平均值的人物基本信息
SELECT * FROM op WHERE reward<(SELECT AVG(reward) FROM op);
-- 2.查询idendity表信息，与虚拟表的信息组合，设置过滤条件
SELECT * FROM (SELECT * FROM op WHERE reward<(SELECT AVG(reward) FROM op)) AS L,identity AS R WHERE L.ity_id=R.id;
  • 演示结果如下👇MySQL多表查询

4️⃣子查询总结🧊

  1. 子查询结果只要是单列，则在 WHERE 后面作为条件
  2. 子查询结果只要是多列，则在 FROM 后面作为虚拟表进行二次查询

写在后面🍻

转载自:https://juejin.cn/post/7084900181262467079
评论
请登录