擅用子查询，让复杂问题简单化
查询"01"课程比"02"课程成绩高的学生信息及课程分数
分析
第一步：根据需要查询的最终结果确认所需用到的表："学生信息及课程分数"，需要用到学生信息表与成绩表；
第二步：确认条件："01"课程比"02"课程成绩高的学生，需要先分别查出"01"课程的成绩与"02"课程，再根据条件"成绩更高"筛选出学生；
#查询01课程的成绩
SELECT * FROM sc WHERE c_id='01';
#查询02课程的成绩
SELECT * FROM sc WHERE c_id='02';
第三步：查询：根据第二步筛选出的学生，查询出对应学生的"学生信息及课程分数"。
语句
SELECT stu.*,sc.c_id,sc.score
FROM (SELECT * FROM sc WHERE c_id='01') t1
JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id
JOIN stu ON t1.s_id=stu.s_id
JOIN sc ON stu.s_id=sc.s_id
WHERE t1.score>t2.score;
结果
s_id | s_name | s_birth | s_sex | c_id | score |
|---|---|---|---|---|---|
02 | 钱电 | 1990-12-21 | 男 | 01 | 70 |
02 | 钱电 | 1990-12-21 | 男 | 02 | 60 |
02 | 钱电 | 1990-12-21 | 男 | 03 | 80 |
04 | 李云 | 1990-08-06 | 男 | 01 | 50 |
04 | 李云 | 1990-08-06 | 男 | 02 | 30 |
04 | 李云 | 1990-08-06 | 男 | 03 | 20 |
如果要求课程分数需要"课程名称"与"对应分数"，则还需要跟课程表连接以显示课程名及分数。
语句
SELECT stu.*,co.c_name,sc.score
FROM (SELECT * FROM sc WHERE c_id='01') t1
JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id
JOIN stu ON t1.s_id=stu.s_id
JOIN sc ON stu.s_id=sc.s_id
JOIN co ON sc.c_id=co.c_id
WHERE t1.score>t2.score;
结果
s_id | s_name | s_birth | s_sex | c_name | score |
|---|---|---|---|---|---|
02 | 钱电 | 1990-12-21 | 男 | 语文 | 70 |
04 | 李云 | 1990-08-06 | 男 | 语文 | 50 |
02 | 钱电 | 1990-12-21 | 男 | 数学 | 60 |
04 | 李云 | 1990-08-06 | 男 | 数学 | 30 |
02 | 钱电 | 1990-12-21 | 男 | 英语 | 80 |
04 | 李云 | 1990-08-06 | 男 | 英语 | 20 |
利用子查询进行连接表
以上例子中先分别查出"01"课程的成绩与"02"课程，再根据查询出的结果去查询对应学生信息及课程成绩，即用到本节将要介绍的子查询。
在SELECT语句中，子查询总是从内向外处理。在处理上面的SELECT语句时，MySQL实际上执行了两个操作。
首先，它执行下面的查询：
SELECT t1.*
FROM (SELECT * FROM sc WHERE c_id='01') t1
JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id
WHERE t1.score>t2.score;
此时得到两个学生编号s_id等于02和04表格：
s_id | c_id | score |
|---|---|---|
02 | 01 | 70 |
04 | 01 | 50 |
然后，将得到的表格t与另外两个表格连接后再查询。
SELECT stu.*,sc.c_id,sc.score
FROM t
JOIN stu ON t1.s_id=stu.s_id
JOIN sc ON stu.s_id=sc.s_id
其实这里也分了两步：
SELECT stu.*,sc.c_id,sc.score
FROM t
JOIN stu ON t1.s_id=stu.s_id
结果：
s_id | s_name | s_birth | s_sex | c_id | score |
|---|---|---|---|---|---|
02 | 钱电 | 1990-12-21 | 男 | 01 | 70 |
04 | 李云 | 1990-08-06 | 男 | 01 | 50 |
这里只得到了这两个学生的课程1的成绩，结果需要查询到这两个学生所有课程的成绩，因此需要将上述得到的表格tt再与成绩表连接。
SELECT stu.*,sc.c_id,sc.score
FROM tt
JOIN sc ON stu.s_id=sc.s_id
格式化SQL 包含子查询的
SELECT语句难以阅读和调试，特别是它们较为复杂时更是如此。如上所示把子查询分解为多行并且适当地进行缩进，能极大地简化子查询的使用。
利用子查询进行过滤
查询没学过"张三"老师授课的同学的信息
首先需要查询出"张三"老师授课信息：
SELECT stu.s_id
FROM te
LEFT JOIN co ON te.t_id=co.t_id
LEFT JOIN sc ON co.c_id=sc.c_id
LEFT JOIN stu ON sc.s_id=stu.s_id
WHERE t_name='张三';
得到"张三"老师授课信息s_id为01，02，03，04，05，07。然后，这两个值以 NOT IN 操作符要求的逗号分隔的格式传递给外部查询的 WHERE 子句。
外部查询变成：
SELECT *
FROM stu
WHERE s_id NOT IN (01,02,03,04,05,07);
结果：
s_id | s_name | s_birth | s_sex |
|---|---|---|---|
06 | 吴兰 | 1992-03-01 | 女 |
08 | 王菊 | 1990-01-20 | 女 |
可见，在 WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。
列必须匹配 在
WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。
作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。
查询每位学生选修的课程数
首先可使用 SELECT COUNT(*)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的学生，可仅对该学生的课程进行计数。
SELECT COUNT(c_id)
FROM sc
WHERE s_id = '01';
为了对每个学生执行COUNT(*)计算，应该将COUNT(*)作为一个子查询。
SELECT *, (SELECT COUNT(c_id)
FROM sc
WHERE sc.s_id = stu.s_id) AS cos
FROM stu;
结果：
s_id | s_name | s_birth | s_sex | cos |
|---|---|---|---|---|
01 | 赵雷 | 1990-01-01 | 男 | 3 |
02 | 钱电 | 1990-12-21 | 男 | 3 |
03 | 孙风 | 1990-05-20 | 男 | 3 |
04 | 李云 | 1990-08-06 | 男 | 3 |
05 | 周梅 | 1991-12-01 | 女 | 2 |
06 | 吴兰 | 1992-03-01 | 女 | 2 |
07 | 郑竹 | 1992-04-21 | 女 | 2 |
08 | 王菊 | 1990-01-20 | 女 | 0 |
这条SELECT语句对customers表中每个学生返回5列：s_id，s_name，s_birth，s_sex和cos。cos是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个学生执行一次。在此例子中，该子查询执行了8次，因为检索出了8个学生。
子查询中的
WHERE子句使用了完全限定列名，任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。如果不使用完全限定的列名会与本身匹配。
逐渐增加子查询来建立查询 用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性。
附录
数据库建立即数据导入准备
-- 创建数据库school
CREATE DATABASE school;
-- 选择进入school数据库
USE school;
-- ------------建表导数-------------
-- 创建stu
CREATE TABLE stu(
s_id VARCHAR(10) PRIMARY KEY,
s_name VARCHAR(10) NOT NULL,
s_birth DATE,
s_sex VARCHAR(10))
-- 导入数据
INSERT INTO stu VALUES
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1992-04-21' , '女'),
('08' , '王菊' , '1990-01-20' , '女');
SELECT * FROM stu; -- 检查数据
SELECT COUNT(*) FROM stu; -- 检查总行数
-- 创建co
CREATE TABLE co(
c_id VARCHAR(10) PRIMARY KEY,
c_name VARCHAR(10),
t_id VARCHAR(10));
-- 导入数据
INSERT INTO co VALUES
('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');
SELECT * FROM co; -- 检查数据
SELECT COUNT(*) FROM co; -- 检查总行数
-- 创建te
CREATE TABLE te(
t_id VARCHAR(10) PRIMARY KEY,
t_name VARCHAR(10));
-- 导入数据
INSERT INTO te VALUES
('01' , '张三'),
('02' , '李四'),
('03' , '王五');
SELECT * FROM te; -- 检查数据
SELECT COUNT(*) FROM te; -- 检查总行数
-- 创建sc
CREATE TABLE sc(
s_id VARCHAR(10),
c_id VARCHAR(10),
score int);
-- 导入数据
INSERT INTO sc VALUES
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);
SELECT * FROM sc; -- 检查数据
SELECT COUNT(*) FROM sc; -- 检查总行数腾讯云开发者
