56、【数据库技术】【MySQL】多表查询

1、多表查询概述

多表查询，即查询时候的数据来源不再只有一张表。
多表查询在关系型数据库的基础理论中是有一些理论基础的，在这里只简要地介绍一个概念“笛卡尔积”。
笛卡尔积：假设有两个集合分别是 $X$ 和 $Y$ ， $X$ 集合是 $\{a,b,c \}$ ， $Y$ 集合是 $\{0,1 \}$ ，笛卡尔积是针对集合的运算，集合 $X$ 与集合 $Y$ 的笛卡尔积记作 $X×Y$ ：
$X×Y=\{(x,y)|x\in X\land y\in Y\}$
$=\{(a,0),(a,1),(b,0),(b,1),(c,0),(c,1) \}$

在关系型数据库的理论中，将两个集合 $X$ 和 $Y$ 分别看作两张表，将集合中的元素看作表中的一行数据，多表查询的基础就建立在多表的笛卡尔积之上。

为了阐述多表查询，使用以下的 SQL 语句创建两张表，一张是“商品分类表”，一张是“商品表”，并插入一些示例数据：

-- 商品分类表
CREATE TABLE category (
c_id VARCHAR(10),
c_name VARCHAR(30) NOT NULL,
CONSTRAINT PRIMARY KEY pk_category (c_id)
);

-- 商品表
CREATE TABLE product (
p_id VARCHAR(10),
p_name VARCHAR(30) NOT NULL,
p_price DECIMAL(7,2) DEFAULT 0.00 NOT NULL,
p_flag TINYINT DEFAULT 0 NOT NULL, -- 是否上架标记：1表示上架、0表示下架
p_c_id VARCHAR(10) DEFAULT NULL,
CONSTRAINT PRIMARY KEY pk_product(p_id),
CONSTRAINT FOREIGN KEY fk_product_category (p_c_id) REFERENCES category (c_id) ON UPDATE RESTRICT ON DELETE RESTRICT
);

-- 插入示例数据
INSERT INTO category(c_id,c_name)
VALUES('c001','家电');

INSERT INTO category(c_id,c_name)
VALUES('c002','鞋服');

INSERT INTO category(c_id,c_name)
VALUES('c003','化妆品');

INSERT INTO category(c_id,c_name)
VALUES('c004','汽车');

INSERT INTO product(p_id,p_name,p_price,p_flag,p_c_id)
VALUES('p001','小米电视机',5000.00,1,'c001');

INSERT INTO product(p_id,p_name,p_price,p_flag,p_c_id)
VALUES('p002','格力空调',3000.00,1,'c001');

INSERT INTO product(p_id,p_name,p_price,p_flag,p_c_id)
VALUES('p003','美的冰箱',4500.00,1,'c001');

INSERT INTO product(p_id,p_name,p_price,p_flag,p_c_id)
VALUES('p004','篮球鞋',800.00,1,'c002');

INSERT INTO product(p_id,p_name,p_price,p_flag,p_c_id)
VALUES('p005','运动裤',200.00,1,'c002');

INSERT INTO product(p_id,p_name,p_price,p_flag,p_c_id)
VALUES('p006','T恤',300.00,1,'c002');

INSERT INTO product(p_id,p_name,p_price,p_flag,p_c_id)
VALUES('p007','冲锋衣',2000.00,1,'c002');

INSERT INTO product(p_id,p_name,p_price,p_flag,p_c_id)
VALUES('p008','神仙水',800.00,1,'c003');

INSERT INTO product(p_id,p_name,p_price,p_flag,p_c_id)
VALUES('p009','大宝',200.00,1,'c003');

2、纯“笛卡尔积”式查询

这种查询是没有实际意义的，但是其他的多表查询是建立在此基础之上的。

SELECT ······
FROM table_1_name,table_2_name,······,table_n_name;

SELECT *
FROM category,product;

MySQL-多表查询-纯笛卡尔积式查询

3、内连接查询

通过指定的条件去匹配多表中的数据，匹配上就显示，匹配不上就不显示。
内连接查询是建立在纯“笛卡尔积”式查询的基础之上，但是内连接查询得到数据是具有现实意义的。有意义的原因就在于“指定条件”，而在指定的条件中，“从表的外键 = 主表的主键”是最常使用的。

3.1、隐式内连接

条件的指定通过WHERE完成；“连接”不需要关键字，直接在FROM子句后面直接写多个表名。

SELECT table_name_1.field_1,table_name_2.field_1,······
FROM table_name_1,table_name_2,······
WHERE ······

由于涉及多表，所以在使用SELECT语句选择字段和WHERE语句编写查询条件的时候，可能会以表名.字段名这样的形式，当然如果字段名在这多张表中属于独一无二的那种，当然可以直接使用字段名即可。
对于表名比较复杂的，可以采用AS来“取别名”。

SELECT t1.field_1,t2.field,······
FROM table_name_1 [AS] t1,table_name_2 [AS] t2
WHERE ······

举例：

-- 查询所有商品信息和对应的分类信息
SELECT *
FROM product,category
WHERE product.p_c_id=category.c_id;

-- 查询商品表的商品名称和价格，以及商品的分类信息
SELECT p.p_name AS name,p.p_price AS price,c.c_name AS category
FROM product AS p,category AS c
WHERE p.p_c_id=c.c_id;

-- 查询格力空调是属于哪一分类下的商品
SELECT p.p_name AS name,c.c_name AS category
FROM product AS p,category AS c
WHERE p.p_c_id=c.c_id
AND p.p_id='p002';

3.2、显式内连接

表之间的连接需要使用INNER JOIN，其中INNER可以省略；连接条件使用ON。注意，ON后跟的是连接条件，比如“从表的外键 = 主表的主键”，其他的条件还是使用WHERE。

SELECT t1.field1,t2.field1,······
FROM table_name_1 [AS] t1 [INNER] JOIN table_name_2 [AS] t2,······
ON ······
WHERE ······

举例：

-- 查询所有商品信息和对应的分类信息
SELECT *
FROM product INNER JOIN category
ON product.p_c_id=category.c_id;

-- 查询鞋服分类下，价格大于500的商品名称和价格
SELECT p.p_name AS name,p.p_price AS price
FROM product AS p INNER JOIN category AS c
ON p.p_c_id=c.c_id
WHERE p.p_price>500
AND c.c_id='c002';

4、外连接查询

分为左外连接查询和右外连接查询两种。

4.1、左外连接查询

以“左表”为基准，匹配“右表”中的数据。如果匹配的上，就展示匹配到的数据；如果匹配不到，仅“左表”中的数据正常展示，其余为null。

SELECT ······
FROM left_table_name LEFT [OUTER] JOIN right_table_name
ON ······
······;

举例：

SELECT *
FROM category LEFT [OUTER] JOIN product
ON category.c_id=product.p_c_id;

MySQL-左外连接查询示例

-- 查询每个分类下的商品个数

SELECT category.c_name,COUNT(product.p_id)
FROM category LEFT [OUTER] JOIN product
ON category.c_id=product.p_c_id
GROUP BY category.c_id;

4.2、右外连接查询

以“右表”为基准，匹配“左表”中的数据。如果匹配的上，就展示匹配到的数据；如果匹配不到，仅“右表”中的数据正常展示，其余为null。

SELECT ······
FROM left_table_name RIGHT [OUTER] JOIN right_table_name
ON ······
······;

举例：

SELECT *
FROM product RIGHT OUTER JOIN category
ON category.c_id=product.p_c_id;

MySQL-右外连接查询示例

5、子查询

一条查询语句的结果, 作为另一条查询语句的一部分。
子查询的语句必须放在小括号中。
子查询一般作为父查询的查询条件使用。
子查询常见分类：

1、WHERE型子查询：将子查询的结果，作为父查询的条件。用在父查询的WHERE子句中。
2、FROM型子查询：将子查询的结果，当作一张表，提供给父层查询使用。用在父查询的FROM子句中。
3、EXISTS型子查询：子查询的结果是“单列多行”，类似一个数组，一般作为父查询的IN、NOT IN的条件使用。
4、上述类型的各种组合。

5.1、子查询的结果作为查询条件（`WHERE`型）

SELECT ······
FROM ······
WHERE field_xx=(SELECT ······);

举例：

-- 查询价格最高的商品的信息

SELECT *
FROM product
WHERE p_price=(
SELECT MAX(p_price)
FROM product);

-- 查询小于平均价格的商品的信息
SELECT *
FROM product
WHERE p_price<(
SELECT AVG(p_price)
FROM product);

5.2、子查询的结果作为一张表（`FROM`型）

当子查询作为一张表的时候，需要起别名（使用AS），否则无法访问表中的字段。并且只能访问子查询中SELECT后所跟的字段（基于“子查询的结果作为一张表”这一观念）。

SELECT ······
FROM (SELECT ······) [AS] xx
WHERE ······
······;

举例：

-- 查询商品中，价格大于500的商品信息，包括商品名称、商品价格、商品所属分类名称

SELECT p.p_name,p.p_price,c.c_name
FROM product [AS] p INNER JOIN (SELECT c_id,c_name FROM category) [AS] c
ON p.p_c_id=c.c_id
WHERE p.p_price>500;

5.3、子查询结果是单列多行（`EXISTS`型）

强调一下，子查询的结果必须是“单列多行”（包括“单列单行”）才适用；否则的话（多行多列），适用“FROM型”。

SELECT ······
FROM ······
WHERE field_xx IN (SELECT ······)
······；

举例：

-- 查询价格小于2000的商品,来自于哪些分类(名称)

SELECT category.c_name
FROM category
WHERE category.c_id IN (
SELECT DISTINCT product.p_c_id
FROM product
WHERE product.p_price<2000);

最后编辑于：2021.09.19 20:49:04

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,098评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,213评论 2赞 380
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 149,960评论 0赞 336
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,519评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,512评论 5赞 364
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,533评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,914评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,574评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,804评论 1赞 296
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,563评论 2赞 319
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,644评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,350评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,933评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,908评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,146评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,847评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,361评论 2赞 342