mysql 多表查询

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/shuiCSDN/article/details/75268698

目录


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
展开阅读全文

没有更多推荐了,返回首页