SQL语法（DQL)：SELECT 多表查询之子查询

1、子查询

• 定义：如果某一个SQL语句A包含了一个查询Select语句B，称B叫做子查询，称A叫做主查询，A带有子查询语句
• 目的：提高代码复用性，间接提高代码开发效率
• 分类：
  • 条件子查询：将子查询结果作为主查询条件：发生在过滤中：where、having

  • -- 条件子查询
    -- 查询的条件依赖于另外一个查询语句的结果，可以用于select、delete、update中
    where  col in/= (select ……)
    # 注意：= 匹配 一行一列，in 匹配 多行一列

  • 数据源子查询：将子查询结果作为主查询数据来源：发生数据来源中：from

  • -- 数据源子查询
    -- 询的数据来自于另外一个查询语句的结果，主要用于select语句中
    from (select ……) t
    # 注意：数据源子查询必须给定别名

  • 字段子查询：将子查询结果作为主查询中一列的值：结果作为字段：select

  • -- 字段子查询
    -- 查询的字段来自于另外一个查询语句的结果，主要用于select语句中，结果必须一行一列
    with 别名1 as (
    	select1
    ), 别名2 as (
    	select2
    )
    ……
    select ……

2、条件子查询

• 功能：用于在一条SQL语句中，通过一个子查询来实现条件过滤

• 场景：一般用在where子句中， 支持select、update、delete，过滤条件依赖于一条select语句的结果

• 语法：

• -- 用于查询数据
  select …… from where …… (select ……)
  -- 用于更新数据
  update ……  where …… (select ……)
  -- 用于删除数据
  delete from …… where …… (select ……)

• 示例1：

• -- 查询所有成绩小于60分的学生信息
  
  # 使用join
  SELECT
  	student.* 
  FROM
  	student
  	INNER JOIN ( SELECT s_id, MAX( s_score ) max FROM score GROUP BY s_id HAVING max < 60 ) m ON student.s_id = m.s_id
  
  # 使用子查询
  SELECT
  	student.* 
  FROM
  	student
  WHERE
  	student.s_id IN
  	 ( SELECT s_id FROM score GROUP BY s_id HAVING MAX( s_score ) < 60 ) 
  
  

• 示例2：

• # 查询没学过“张三”老师课的学生的学号、姓名
  
  # 内层：先查询出“张三”老师的教师ID
  SELECT t_id 
  FROM teacher 
  WHERE t_name = '张三'
  ；
  
  # 中层：再查询出“张三”老师教过的课程ID
  SELECT c_id 
  FROM course 
  WHERE t_id IN ( SELECT t_id F
                  ROM teacher 
                  WHERE t_name = '张三'
                )
  ；
  
  # 外层：查询出学过“张三”老师教过的课的学生ID
  SELECT
  	s_id 
  FROM
  	score 
  WHERE
  	c_id IN ( SELECT c_id 
                FROM course 
                WHERE t_id IN ( SELECT t_id 
                                FROM teacher 
                                WHERE t_name = '张三' 
                              ) 
              ) 
  ；
  
  # 最终：再使用关键词 NOT IN 查询出没学过“张三”老师课的学生的学号、姓名
  SELECT
  	st.s_id,
  	st.s_name 
  FROM
  	student st 
  WHERE
  	st.s_id NOT IN (
  SELECT
  	s_id 
  FROM
  	score 
  WHERE
  	c_id IN ( SELECT c_id FROM course WHERE t_id IN ( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) 
  	)
  

3、数据源子查询

• 功能：用于在一条SQL语句中，通过一个子查询来构造查询的数据内容

• 场景：一般用在select语句中，常用于继续对上一步的结果继续进行处理

• 语法：

• select …… from (select ……)

• 示例：

• -- 查询学生平均成绩并按照平均成绩进行排名
  SELECT
  	t.*
  FROM
  	(
  SELECT
  	stu.s_name,
  	AVG( sc.s_score ) avgscore
  FROM
  	student stu
  	JOIN score sc ON stu.s_id = sc.s_id
  GROUP BY
  	sc.s_id
  order by avgscore desc
  	) t
  ;

• 注意：数据源子查询一定要给子查询取个别名

4、字段子查询

• 功能：用于select通过子查询生成一列的数据

• 场景：一般用在select后面，不常用

• 语法：

• select ……, (select …… ) as col from ……

• 示例：

• -- 查询所有学生的ID对应的平均成绩以及班级的平均成绩
  
  # 先分别查询 所有学生的ID对应的平均成绩 以及 班级的平均成绩
  select s_id,
         avg(s_score),
  from score
  group by s_id
  ;
  
  select avg(s_score) as a_score 
  from score
  ；
  
  
  # 合并
  select s_id,
         avg(s_score),
         (select avg(s_score) from score ) as a_score
  from score
  group by s_id
  ;

5、子查询的CTE表达式

• 问题：一旦子查询嵌套的结构多了，整体代码开发的逻辑容易混乱，代码可读性不强，怎么解决？

• 解决：CTE【Common Table Expresssion】表达式，通用/公共表表达式

• 功能：可以将每一步SQL的结果临时构建一个表名，再继续下一步对上一步的表名进行处理

• 场景：一般用于数据源子查询中

• 语法：

• -- 单层
  WITH tmp_tb_name AS (
  	SELECT ……
  )
  SELECT …… FROM tmp_tb_name
  
  -- 多层
  WITH tmp_tb_name1 AS (
  	SELECT ……
      
  ), tmp_tb_name2 AS (
  	SELECT …… FROM tmp_tb_name1
      
  ), tmp_tb_name3 AS (
  	SELECT …… FROM tmp_tb_name2
      
  )
  SELECT …… FROM tmp_tb_name3

• 示例：

• -- 查询不同老师所教不同课程平均分从高到低显示
  
  -- 普通写法
  SELECT
  	teacher.t_name,
  	course.c_name,
  	t.avgscore 
  FROM
  	course
  	JOIN ( SELECT c_id, AVG( s_score ) avgscore FROM score GROUP BY c_id ) t ON course.c_id = t.c_id
  	JOIN teacher ON course.t_id = teacher.t_id 
  ORDER BY
  	t.avgscore DESC
  
  -- CTE写法
  WITH t1 AS (
      SELECT c_id, AVG( s_score ) avgscore FROM score GROUP BY c_id
  )
  SELECT
  	teacher.t_name,
  	course.c_name,
  	t1.avgscore
  FROM
  	course
  	JOIN t1 ON course.c_id = t1.c_id
  	JOIN teacher ON course.t_id = teacher.t_id
  ORDER BY
  	t1.avgscore DESC;