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);
网友评论