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
  SELECTstudent.* 
  FROMstudentINNER 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# 使用子查詢
  SELECTstudent.* 
  FROMstudent
  WHEREstudent.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 FROM teacher WHERE t_name = '張三')
  ；# 外層：查詢出學過“張三”老師教過的課的學生ID
  SELECTs_id 
  FROMscore 
  WHEREc_id IN ( SELECT c_id FROM course WHERE t_id IN ( SELECT t_id FROM teacher WHERE t_name = '張三' ) ) 
  ；# 最終：再使用關鍵詞 NOT IN 查詢出沒學過“張三”老師課的學生的學號、姓名
  SELECTst.s_id,st.s_name 
  FROMstudent st 
  WHEREst.s_id NOT IN (
  SELECTs_id 
  FROMscore 
  WHEREc_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 ……)

• 示例：

• -- 查詢學生平均成績并按照平均成績進行排名
  SELECTt.*
  FROM(
  SELECTstu.s_name,AVG( sc.s_score ) avgscore
  FROMstudent stuJOIN score sc ON stu.s_id = sc.s_id
  GROUP BYsc.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

• 示例：

• -- 查詢不同老師所教不同課程平均分從高到低顯示-- 普通寫法
  SELECTteacher.t_name,course.c_name,t.avgscore 
  FROMcourseJOIN ( SELECT c_id, AVG( s_score ) avgscore FROM score GROUP BY c_id ) t ON course.c_id = t.c_idJOIN teacher ON course.t_id = teacher.t_id 
  ORDER BYt.avgscore DESC-- CTE寫法
  WITH t1 AS (SELECT c_id, AVG( s_score ) avgscore FROM score GROUP BY c_id
  )
  SELECTteacher.t_name,course.c_name,t1.avgscore
  FROMcourseJOIN t1 ON course.c_id = t1.c_idJOIN teacher ON course.t_id = teacher.t_id
  ORDER BYt1.avgscore DESC;