iT邦幫忙

第 11 屆 iT 邦幫忙鐵人賽

DAY 17
0
自我挑戰組

網頁服務開發之路系列 第 17

Day17. MySQL: 子查詢

學習來源: Codedata - MySQL 超新手入門(10)子查詢

一個敘述中的查詢敘述

  1. 好處是不用執行多次查詢就可以完成工作
  2. 範例:
    SELECT Code, Population
    FROM country
    WHERE Population > ( SELECT Population
                         FROM   country
                         WHERE  Code = 'USA' )
    

WHERE、HAVING子句與子查詢

子查詢大部份使用在提供判斷條件用的資料,在「WHERE」和「HAVING」子句中,都可能出現子查詢

  1. 比較運算子

    1. 使用在比較運算子的子查詢,在「SELECT」子句中不可以指定超過一個欄位的回傳資料
    2. 子查詢也不可以回傳超過一筆以上的紀錄
  2. 「IN」運算子

    1. 範例
      SELECT Name
      FROM country
      WHERE Code IN (SELECT CountryCode
                     FROM   city
                     WHERE  Population > 90000)
      
    2. 「IN」運算子可以視需要搭配「NOT」運算子
  3. 其它運算子

    1. ALL: 子查詢的結果用 AND
      SELECT *
      FROM outertable
      WHERE n 比較運算子 ALL (SELECT n
                             FROM   innertable)
      
    2. ANY, SOME: 子查詢的結果 OR,「=ANY」 的效果與 「IN 」相同
      SELECT *
      FROM outertable
      WHERE n 比較運算子 ANY (SELECT n
                             FROM   innertable )
      
  4. 多欄位子查詢

    1. 跟Iraq國家同一個地區,而且跟Iraq國家的政府型式一樣的國家,語法:
      SELECT Name
      FROM country
      WHERE (Region, GovernmentForm) = ( SELECT  Region, Government
                                         FROM    country
                                         WHERE   Name = 'Iraq')
      
    2. 子查詢傳回多比紀錄時就要使用「IN」運算子:
      SELECT Continent, Name, GNP
      FROM   country
      WHERE  (Continent, GNP)  IN (SELECT   Continent, MAX(GNP)
                                   FROM     country
                                   GROUP BY Continent)
      

SELECT子句與子查詢

  1. India國家佔全世界人口的比例,範例
    SELECT ( SELECT Population FROM country WHERE Name = 'India')/
           ( SELECT SUM(Population) FROM country)
    

FROM 子句與子查詢

子查詢回傳的結果會被當成一個「表格」

  1. 亞洲GNP前十名國家,範例
    SELECT Name, GNP
    FROM  ( SELECT *
            FROM   country
            WHERE  Contient = 'Asia' ) asiacountry
    ORDER BY GNP DESC
    LIMIT 10
    

資料維護與子查詢

在使用「INSERT」、「UPDATE」與「DELETE」敘述執行新增、修改與刪除資料時,也可以依照需要使用子查詢來簡化資料維護的敘述。

  1. 新增「INSERT」
    1. 如果你要新增的資料,可以執行一個查詢來取得的話,就可以搭配子查詢來簡化新增紀錄的工作
    2. 語法
      INSERT [INTO] 表格名稱
      ON DUPLICATE KEY UPDATE 欄位 = 運算式[]
      
    3. 使用子查詢提供「INSERT」敘述需要的資料,要特別注意子查詢回傳的欄位資料
    4. 想要新增亞洲國家的資料到「mycountry」表格中,你可以使用子查詢傳回新增紀錄需要的資料給「INSERT」敘述使用,範例
      INSERT INTO mycountry
      ( SELECT Code, Name, Continent, Region, Population, GNP
        FROM country
        WHERE Continent = 'Asia')
      
  2. 修改「UPDATE」
    1. 語法
      UPDATE [IGNORE] 表格名稱
      SET 欄位名稱 = 運算式|DEFAULT [,...]
      WHERE 條件
      
    2. 如果沒有使用「WHERE」子句指定修改的條件,「UPDATE」敘述會修改表格中所有的紀錄
    3. 修改和子查詢不可以同時出現相同的表格
    4. SALES部門的員工加薪百分之五,範例
      UPDATE cmdev.emp
      SET    salary = salary * 1.05
      WHERE  deptno = ( SELECT deptno
                        FROM cmdev.dept
                        WHERE dname = 'SALES')
      
  3. 刪除「DELETE」
    1. 語法
      DELETE [IGNORE] FROM 表格名稱
      WHERE 條件
      
    2. 如果沒有使用「WHERE」子句指定刪除的條件,「DELETE」敘述會刪除表格中所有的紀錄
    3. 刪除和子查詢不可以同時出現相同的表格
    4. 刪除SALES部門員工,範例
      DELETE FROM cmdev.emp
      WHERE deptno = ( SELECT deptno
                       FROM cmdev.dept
                       WHERE empno = 'SAKES') 
      

關聯子查詢

需要使用外層查詢的資料來執行判斷的工作,這樣的敘述稱為「關聯子查詢、correlated subqueries」

  1. 在「WHERE」或「HAVING」子句中用來設定條件的子查詢,可以依照需求使用像「IN」、「ANY」這些運算子來判斷條件是否符合
  2. 「EXISTS」
    1. 子查詢有任何紀錄資料回傳,條件就算成立
    2. 通常會在使用關聯子查詢中
    3. 範例
      SELECT Name
      FROM country c
      WHERE [NOT] EXISTS ( SELECT * 
                           FROM   city
                           WHERE  CountryCode = c.Code AND
                           Population > 80000 )
      

子查詢與結合查詢

一些子查詢完成的工作,也可以改用其它的作法來完成

  1. 結合查詢
  2. 「LEFT JOIN」

上一篇
Day16. MySQL: 表格與索引
下一篇
Day18. MySQL: View 的應用
系列文
網頁服務開發之路30

尚未有邦友留言

立即登入留言