[Mysql] 子查询

1.子查询定义

子查询:在一个SELECT语句中,嵌入了另外一个SELECT语句, 那么被嵌入的SELECT语句称之为子查询语句

主查询:主要查询的对象,第一条SELECT语句

主查询和子查询的关系

1.子查询是嵌入到主查询中

2.子查询是辅助主查询的,要么充当条件,要么充当数据源

3.子查询是可以独立存在的语句,是一条完整的SELECT语句

子查询语句可以多次使用SELECT，并且各个SELECT会有层级关系

在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询

---

数据导入

DROP TABLE IF EXISTS purchase_info; 
CREATE TABLE purchase_info( 
commodity_id VARCHAR(8), 
category VARCHAR(16), 
colour VARCHAR(16),
purchase_quantity INT, 
purchase_date DATE 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
purchase_info 
(commodity_id,category,colour,purchase_quantity,purchase_date) 
VALUE ('c001','clothing','black',150,'2022-04-25') 
,('c002','clothing','white',50,'2022-04-05') 
,('c003','shoes','white',500,'2022-03-23') 
,('c004','shoes','red',200,'2022-04-07') 
,('c005','clothing','blue',120,'2022-04-15');

purchase_info表(商品进货信息表) 

commodity_id:商品id  category:类别  colour:颜色  purchase_quantity:购买数量 

purchase_date:购买日期 

注意:purchase_info表中的数据作为问题1和问题2查询的数据源

---

问题1:找出比平均进货量多的商品信息 

SELECT * 
FROM purchase_info 
WHERE purchase_quantity > (SELECT AVG(purchase_quantity) 
                           FROM purchase_info);

结果展示:  

上述子查询的部分代码如下:

SELECT AVG(purchase_quantity) FROM purchase_info;

结果展示:

这类子查询叫作非关联子查询，它的特点是数据库嵌套查询中内层查询是完全独立于外层查询的

该子查询的执行顺序是先运行子查询部分，再使运行得到的结果参与子查询外部SELECT的运行

问题2:查询大于本商品类别平均进货量的商品信息

解题思路:将商品按照商品类别分组计算平均进货量，然后通过子查询进行比较

SELECT * 
FROM purchase_info 
WHERE purchase_quantity > (SELECT AVG(purchase_quantity) 
						   FROM purchase_info 
						   GROUP BY category);

运行时发现代码出现异常[Err] 1242 - Subquery returns more than 1 row 

仔细观察以上代码可以发现，此时子查询的结果并不是一个数据，而是多个数据，所以子查询外部每个商品价格比较的对象有多个，导致无法比较

-- 子查询部分
SELECT AVG(purchase_quantity) 
FROM purchase_info 
GROUP BY category;

正确的做法是将子查询外部每个商品价格和子查询结果中同类别商品的均价进行比较，即建立内外部的关联关系

SELECT * 
FROM purchase_info AS a 
WHERE purchase_quantity > (SELECT AVG(purchase_quantity) 
                           FROM purchase_info AS b 
                           WHERE a.category = b.category 
                           GROUP BY category);

结果展示:

上述代码中通过WHERE a.category = b.category建立内外部的关联关系，这种子查询叫作关联子查询 ，它的特点是数据库嵌套查询中内层查询和外层查询不相互独立，内层查询也依赖于外层查询

该子查询的执行顺序是先执行外层查询，再执行内层查询

例如：

SELECT * FROM A_table WHERE A_table.column < (SELECT column FROM B_table WHERE A_table.id = B_table.id);

1.先查询到到一条A_table的记录

2.进入内层查询，根据A_table.id在B_table中找到满足B_table.id与之相等的记录，如果找到符合A表那条记录的column值 < B表该条记录的column值，则A表那条记录满足条件，则查询输出一条记录

3. 找到外层查询的下一条记录，重复以上步骤，直到A表记录查询完毕

2.子查询分类

按内层查询与外层查询是否有关联分为:关联子查询、非关联子查询

---

数据导入

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `sid` int(11) NOT NULL,
  `cid` int(11) DEFAULT NULL,
  `sname` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` double DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `students` VALUES ('1', '1', 'Odin', '18', '180');
INSERT INTO `students` VALUES ('2', '1', 'Harry', '19', '175');
INSERT INTO `students` VALUES ('3', '2', 'Jack', '25', '171');
INSERT INTO `students` VALUES ('4', '3', 'Lee', '27', '170');
INSERT INTO `students` VALUES ('5', '3', 'Rita', '30', '172');
INSERT INTO `students` VALUES ('6', '3', 'Mark', '28', '173');
INSERT INTO `students` VALUES ('7', '3', 'Bob', '35', '181');

DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `classes` VALUES ('1', '语文');
INSERT INTO `classes` VALUES ('2', '数学');
INSERT INTO `classes` VALUES ('3', '英语');

students表

classes表

按结果的行列数不同分为：

标量子查询:子查询返回的结果是一个数据(一行一列),相当于一个常数

例子:在students表中查询大于平均年龄的学生信息

SELECT AVG(age) FROM students;

SELECT * 
FROM students 
WHERE age > (SELECT AVG(age) FROM students);

列级子查询:返回的结果是一列(一列多行)

例子:查询学生表中的学员报班学习了哪几类课程

SELECT cid FROM students;

SELECT name 
FROM classes 
WHERE id IN(SELECT cid FROM students);

行级子查询:返回的结果是一行(一行多列)

例子:在students表中查询班级中年龄和身高都是排第一的学生信息

SELECT MAX(height),MAX(age) FROM students;

SELECT * 
FROM students 
WHERE (height,age) = (SELECT MAX(height),MAX(age) FROM students);

表子查询:把子查询当作是一个普通的数据表，返回的结果是多行多列(多行多列) 

例子:查询每个班的最高身高

SELECT * FROM students ORDER BY height DESC;

SELECT s.cid, MAX(s.height)AS 'max_height'
FROM (SELECT * FROM students ORDER BY height DESC)AS s
GROUP BY s.cid;

3.子查询易错点 

数据导入

DROP TABLE IF EXISTS purchase_info; 
CREATE TABLE purchase_info( 
commodity_id VARCHAR(8), 
category VARCHAR(16), 
colour VARCHAR(16),
purchase_quantity INT, 
purchase_date DATE 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
purchase_info 
(commodity_id,category,colour,purchase_quantity,purchase_date) 
VALUE ('c001','clothing','black',150,'2022-04-25') 
,('c002','clothing','white',50,'2022-04-05') 
,('c003','shoes','white',500,'2022-03-23') 
,('c004','shoes','red',200,'2022-04-07') 
,('c005','clothing','blue',120,'2022-04-15');

purchase_info表(商品进货信息表) 

commodity_id:商品id  category:类别  colour:颜色  purchase_quantity:购买数量 

purchase_date:购买日期 

注意:purchase_info表中的数据作为易错点1和易错点2查询的数据源

易错点1

问题: 查询大于本商品类别平均进货量的商品信息

SELECT * 
FROM purchase_info AS a 
WHERE purchase_quantity > (SELECT AVG(purchase_quantity) 
                           FROM purchase_info AS b 
                           GROUP BY category) 
AND a.category = b.category;

运行上述代码出现异常[Err] 1054 - Unknown column 'b.category' in 'where clause'

将上述代码进行修正 

SELECT * 
FROM purchase_info AS a 
WHERE purchase_quantity > (SELECT AVG(purchase_quantity) 
                           FROM purchase_info AS b 
                           WHERE a.category = b.category 
                           GROUP BY category);

上述两段代码的区别在于，出现异常的那段代码将a.category = b.category的条件移到子查询外部书写 

[Err] 1054 - Unknown column 'b.category' in 'where clause'

异常很容易被理解，即b.category这个字段名在WHERE语句中不能被理解，这是因为子查询内部中表的别名b在子查询外部不能被识别，即其作用域仅限于子查询内部。在子查询内部运行完毕后，最原始的表b已经不存在了，对子查询外部展示的是子查询的结果表，而不是原始表。这样就能理解为什么将关联条件写在子查询外部会抛出异常了

易错点2

问题: 在子查询外部查询各商品类别平均进货量

SELECT * 
FROM(SELECT AVG(purchase_quantity) 
     FROM purchase_info 
     GROUP BY category);

运行上述代码抛出异常[Err] 1248 - Every derived table must have its own alias 

这是因为子查询需要有一个别名，所以将上述代码稍微修改一下，给子查询添加一个别名a

SELECT * 
FROM(SELECT AVG(purchase_quantity) 
     FROM purchase_info 
     GROUP BY category)AS a;

结果展示:

如何写出子查询

如果使用一层查询无法完成需要的操作时，则可以考虑使用子查询语句

在书写子查询语句之前，将需要查询的内容拆分成多步，并依次排列好，然后将能合并在一起的步骤放在一层查询中，同时将需要在上一步结果的基础上进行的步骤放在当前子查询层的外部操作中，这样就可以理顺思路，正确书写子查询语句

逐渐增加子查询来建立查询

用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立(和测试)查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据(可理解为最内层查询的直接结果)建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后(找出查询为什么不正常)的大量时间，并且极大地提高了查询一开始就正常工作的可能性

例子

milk_tea表