mysql 多表查询

目录

---

1. 连接查询

数据库中存在多个表，表与表之间进行关联。对于数据库的操作必然不限于单表的查询，必然要用到多表之间的查询。比如：内连接查询、外连接查询以及复合条件连接查询。

首先创建一个商品表，和一个订单项表，两表之间用 pid 关联。通过这两个表来进行连接查询的实验。

product 商品表：

CREATE TABLE `product` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `pname` varchar(255) DEFAULT NULL,
  `market_price` double DEFAULT NULL,
  `shop_price` double DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `pdesc` varchar(255) DEFAULT NULL,
  `is_hot` int(11) DEFAULT NULL,
  `pdate` datetime DEFAULT NULL,
  `csid` int(11) DEFAULT NULL,
  PRIMARY KEY (`pid`),
  KEY `FKED8DCCEFB9B74E02` (`csid`),
  CONSTRAINT `FKED8DCCEFB9B74E02` FOREIGN KEY (`csid`) REFERENCES `categorysecond` (`csid`)
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8;

orderitem 订单项表：

CREATE TABLE `orderitem` (
  `itemid` int(11) NOT NULL AUTO_INCREMENT,
  `count` int(11) DEFAULT NULL,
  `subtotal` double DEFAULT NULL,
  `pid` int(11) DEFAULT NULL,
  `oid` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemid`),
  KEY `FKE8B2AB6166C01961` (`oid`),
  KEY `FKE8B2AB6171DB7AE4` (`pid`),
  CONSTRAINT `FKE8B2AB6166C01961` FOREIGN KEY (`oid`) REFERENCES `orders` (`oid`),
  CONSTRAINT `FKE8B2AB6171DB7AE4` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8;

1.1 内连接查询

内连接 INNER JOIN 使用比较运算符进行表间的数据比较，查询出与连接条件的数据行，组成新的数据。内连接中只有满足条件的记录才会出现在显示结果中。

SELECT p.pid,p.pname,o.count
WHERE p.pid = o.pid

1. 从两个表中同时查询数据。由于在两个表中进行查询，为了避免表中字段名称重复产生歧义，给两个表各自的别名 p 和 o : product p,orderitem o
2. WHERE 子句做过滤作用表明只有两表的 pid 相同时才符合查询条件
3. 最后返回的结果由两个表中的数据共同组成

SELECT p.pid,p.pname,o.count
FROM product p INNER JOIN orderitem o
ON p.pid = o.pid

1. 内连接使用 INNER ON进行查询，返回的结果与上一条结果一样
2. INNER JOIN 语法是 ANSI SQL标准规范，可以确保不会忘记连接条件

1.2 外连接查询

连接查询将查询过个表中相关联的行，内连接只会返回符合查询条件和连接条件的行。但是有时候需要包含没有关联的数据。外连接分为左外连接 LEFT JOIN 和 右外连接 RIGHT JOIN，还是用案例来说明比较清晰。

1.2.1 左连接

SELECT o.itemid,o.count,p.pid,p.pname
FROM orderitem o LEFT JOIN product p
ON p.pid = o.pid
WHERE o.oid = 31

+--------+-------+------+------------------------------------------+
| itemid | count | pid  | pname                                    |
+--------+-------+------+------------------------------------------+
|     79 |     2 |    1 | 韩版连帽加厚毛衣女外套                   |
|     80 |     1 |    7 | 新款优雅奢华毛领白鸭绒轻薄羽绒服         |
|     81 |     1 |   23 | 韩版毛呢外套女韩范秋冬装厚中长款冬季呢子 |
|     88 |   100 | NULL | NULL                                     |
+--------+-------+------+------------------------------------------+
4 rows in set

1. 左连接返回左表全部数据，以及右边连接字段相等的数据
2. 可以看到 pid 作为连接字段，即使右表的 pid 为 null 也将其查询显示
3. 查询所有的订单项，即使商品已经从订单项中移除，任然查询出该订单

1.2.2 右连接

SELECT o.itemid,o.count,p.pid,p.pname
FROM orderitem o RIGHT JOIN product p
ON p.pid = o.pid
WHERE o.oid = 31

+--------+-------+-----+------------------------------------------+
| itemid | count | pid | pname                                    |
+--------+-------+-----+------------------------------------------+
|     79 |     2 |   1 | 韩版连帽加厚毛衣女外套                   |
|     80 |     1 |   7 | 新款优雅奢华毛领白鸭绒轻薄羽绒服         |
|     81 |     1 |  23 | 韩版毛呢外套女韩范秋冬装厚中长款冬季呢子 |
+--------+-------+-----+------------------------------------------+
3 rows in set

1. 右连接返回右表的全部数据，以及右边连接字段相等的数据
2. 可以看到 pid 作为连接字段，在右表的 pid 为 null，无法查询右表数据，所以 pid 为空的记录不显示
3. 查询所有的订单项，商品已经从订单项中移除，不显示该订单

1.3 复合条件连接查询

复合条件查询即通过添加过滤条件，限制查询的结果，使查询结果更加精确

SELECT o.itemid,o.count,p.pid,p.pname
FROM orderitem o RIGHT JOIN product p
ON p.pid = o.pid AND p.pid = 1
WHERE o.oid = 31

+--------+-------+-----+------------------------+
| itemid | count | pid | pname                  |
+--------+-------+-----+------------------------+
|     79 |     2 |   1 | 韩版连帽加厚毛衣女外套 |
+--------+-------+-----+------------------------+
1 row in set

1. 在以上左连接的基础上添加过滤条件，AND p.pid = 1，则过滤了pid不为1的数据行

SELECT o.itemid,o.count,p.pid,p.pname
FROM orderitem o RIGHT JOIN product p
ON p.pid = o.pid 
WHERE o.oid = 31
ORDER BY o.count

1. 或者通过 ORDER BY 对数据进行排序等

2. 子查询

子查询即一个查询语句嵌套另一个查询语句，或者说一个查询语句处于另一个查询语句的内部。处于内部的查询语句作为外层查询语句的过滤条件。

子查询的功能也能用连接查询完成，但是使用子查询使得代码更易阅读。

2.1 ANY、SOME 关键字子查询

CREATE TABLE tab1(num1 INT NOT NULL);
CREATE TABLE tab2(num2 INT NOT NULL);
INSERT INTO tab1 VALUES(1),(3),(10),(20);
INSERT INTO tab2 VALUES(4),(6),(12),(18);

SELECT num1 
FROM tab1 
WHERE num1 >
ANY (SELECT num2 FROM tab2)

1. 先创建 tab1 和 tab2 两个表，如上所示
2. ANY 和 SOME 用法完全相同，表示任一的意思。
3. 即先查询出 tab2 表中的所有 num2 数值
4. tab1 表中的 num1 中的数字，只要比任一 num2 大即满足条件
5. 所以返回值为 10,20

2.2 ALL 关键字子查询

SELECT num1 
FROM tab1 
WHERE num1 >
ALL (SELECT num2 FROM tab2)

1. 语句其他部分不变，将前面的 ANY 换成 ALL
2. ALL 表示所有的意思，即 num1 的数值需要比 num2 所有数值都大
3. 所以返回值只有 20

2.3 EXISTS 关键字子查询

SSELECT pid,pname
FROM product
WHERE market_price > 200 
AND EXISTS
(SELECT * FROM orderitem WHERE pid = 5)

1. EXISTS后跟查询语句，语句返回结果为 true，则执行外层查询；返回结果为 false,则不执行外层查询
2. EXISTS可以与其他过滤条件结合使用
3. 也可以使用 NOT EXITS，用法与 EXISTS 相同，结果相反
4. EXISTS 和 NOT EXITS 的结果取决于是否能够返回数据，所以与查询的字段无关

2.4 IN 关键字子查询

SELECT product.pid,pname
FROM product
WHERE pid IN
(SELECT pid FROM orderitem WHERE subtotal > 200)

1. IN 后接的查询语句查询出一个结果作为一个结果集
2. 结果集作为外层语句的查询范围
3. 以上 sql 查询出总量大于200的所有订单项，这些订单项的 pid 就是一个集合。外层查询以查询出的 pid 作为条件，查出相对应的 pname
4. 同样也可以使用 NOT IN，作用与 IN 相反

2.5 比较运算符子查询

SELECT pid,pname
FROM product
WHERE pid =
(SELECT pid FROM orderitem o WHERE itemid = 2)

1. 将上面的 IN 换成比较运算符 =
2. 使用 2.4 中的子查询，子查询会返回多条结果，报错如下：[Err] 1242 - Subquery returns more than 1 row
3. 所以说使用比较远算符，子查询只能返回一个结果，作为外层查询语句的条件

3. 合并结果查询 UNION、UNION ALL

UNION 可以连接多条 SELECT 语句，将两个结果集组成一个结果集。两个结果集，对应的列数和数据类型必须相同。

SELECT COUNT(*) AS TOTAL FROM 
(SELECT pid,pname FROM product WHERE market_price < 1000
UNION ALL
SELECT pid,pname FROM product WHERE shop_price > 10) a

+-------+
| TOTAL |
+-------+
|   133 |
+-------+
1 row in set

SELECT COUNT(*) AS TOTAL FROM 
(SELECT pid,pname FROM product WHERE market_price < 1000
UNION
SELECT pid,pname FROM product WHERE shop_price > 10) a;

+-------+
| TOTAL |
+-------+
|    72 |
+-------+
1 row in set

1. 查询语句唯一区别在于有无 ALL 关键字，可以看到有 ALL 查询出来的结果更多
2. UNION ALL 不删除结果集之间的重复记录，UNION 删除重复记录
3. 加入 ALL 关键字查询时使用的资源少，尽量使用它
4. 当知道查询结果会有重复记录，且重复记录有用，或者不需要删除重复记录，使用UNION ALL
5. 当知道查询结果不会有重复记录，使用 UNION ALL