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

1、多表查询概述

• 多表查询，即查询时候的数据来源不再只有一张表。

• 多表查询在关系型数据库的基础理论中是有一些理论基础的，在这里只简要地介绍一个概念“笛卡尔积”。
  笛卡尔积：假设有两个集合分别是和，集合是，集合是，笛卡尔积是针对集合的运算，集合与集合的笛卡尔积记作：
  
  

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

• 为了阐述多表查询，使用以下的 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);