{DAY11} SQL查询语法3

前言

SQL的查询语法今天到最後一部分?

要练习关联式资料库最重要的精髓！

就是查询出在不同资料表之间有关联的资料

我上的课程一样是郭耀仁老师在台大资讯系统训练班所开设的『SQL资料分析』

这篇文章会使用老师上课教到的观念

今天练习分成两个部分

1.) 子查询

2.) 垂直与水平合并

在子查询里可以看到在一段SQL 中包含着另外一段的 SQL

可以处理三种情形

但实作过後会发现

当要处理的资料来自不同资料表

会变得很不直观，写起来也容易出错

就需要使用到垂直与水平合并 UNION跟 JOIN

来让语法简介快速又方便

接下来就开始练习进阶的查询语法吧～～

使用的资料库来源分别是：

IMDb.com 最高评等的电影 https://www.imdb.com/chart/top

子查询

如果一段 SQL 中包含着另外一段的 SQL，这样的 SQL 结构就被称为子查询（Subquery）

情境一：查询的筛选条件必须要先做一个查询才能得知

我们想知道 imdb 资料库的 movies 资料表中片长最短的电影是哪一部。

SELECT column_names
  FROM table_name
 WHERE conditions (Another SELECT statement);

原本会是分成两段的查询

SELECT MIN(runtime) AS minimum_runtime  -- 先查询「最短」的片长是几分钟。
  FROM movies;

SELECT *
  FROM movies
 WHERE runtime = 45;  -- 再依据前一个查询结果作为筛选条件。

将第一个查询完整的放到第二个查询内

SELECT *
  FROM movies
 WHERE runtime = (SELECT MIN(runtime) AS minimum_runtime 
                  FROM movies);

情境二：查询的计算内容必须要先做一个查询才能得知

我们想知道 imdb 资料库的 movies 资料表中，在2000年之後上映的电影占比为多少？

我们要分两次查询来完成

• 先查询在2000年之後上映的电影有几部。
• 再依据前一个查询结果作为计算内容。

SELECT COUNT(*) AS number_of_movies
  FROM movies
 WHERE release_year >= 2000;  

SELECT 101 / CAST(COUNT(*) AS REAL) AS millennium_percentage  
  FROM movies;

合并成一个式子

SELECT (Another SELECT statement) AS alias
  FROM table_name;

SELECT (SELECT COUNT(*) AS number_of_movies
        FROM movies
        WHERE release_year >= 2000)/ CAST(COUNT(*) AS REAL) AS millennium_percentage  
  FROM movies;

也可以应用子查询将一段 SQL 查询结果作为资料表

SELECT column_names
  FROM (SELECT column_names FROM table_name) AS alias;

记得取别名，之後可以使用到栏位的指定

SELECT *
  FROM (SELECT release_year,
               MAX(rating) AS max_rating
          FROM movies
         GROUP BY release_year) AS max_rating_each_year
 ORDER BY release_year DESC
 LIMIT 10;

垂直合并

以 UNION 垂直合并，结合观测值

A SELECT statement
UNION
Another SELECT statement

SELECT director AS my_favorites
  FROM movies
 WHERE director IN ('Christopher Nolan', 'Steven Spielberg')
 UNION
SELECT name
  FROM actors
 WHERE name IN ('Tom Hanks', 'Leonardo DiCaprio');

使用 UNION 的注意事项

• 垂直合并的栏位数要相同。
• 垂直合并的 SQL 若有使用到 ORDER BY 只能放在 UNION 之後。
• 垂直合并的重复观测值会被省略。

若希望保留重复观测值，改使用 UNION ALL

SELECT director AS my_favorites
  FROM movies
 WHERE director IN ('Christopher Nolan', 'Steven Spielberg')
 UNION ALL
SELECT name
  FROM actors
 WHERE name IN ('Tom Hanks', 'Leonardo DiCaprio')
 ORDER BY my_favorites;

水平合并

了解资料表之间透过结合键（Join Key）水平合并。
以 JOIN 水平合并，结合变数
因为是「水平」合并，在 FROM 後的资料表被称为「左表格」、JOIN 後的资料表被称为「右表格」。

SELECT left_table.column_names,
       right_table.column_names
  FROM left_table
  JOIN right_table
    ON left_table.join_key = right_table.join_key;

**结合两张资料表：**以 JOIN 水平合并 movies 与 casting

SELECT movies.title,
       movies.director,
       movies.release_year,
       casting.actor_id
  FROM movies  -- 左表格
  JOIN casting -- 右表格
    ON movies.id = casting.movie_id
WHERE movies.title = 'The Shawshank Redemption';

**结合三张资料表：**以 JOIN水平合并 movies、casting 与 actors

SELECT movies.title,
       movies.director,
       casting.ord,
       actors.name
  FROM movies  -- 左表格
  JOIN casting -- 右表格
    ON movies.id = casting.movie_id
    JOIN actors   -- 右二表格
    ON casting.actor_id = actors.id
WHERE movies.title = 'The Shawshank Redemption';

若希望保留以左表格为主的观测值，改使用LEFT JOIN

SELECT left_table.column_names,
       right_table.column_names
  FROM table_name AS left_table
  LEFT JOIN table_name AS right_table
    ON left_table.join_key = right_table.join_key;

SELECT two_movies.title,
       two_castings.actor_id
  -- 左表格是刺激1995与阿甘正传的电影资料
  FROM (SELECT *
          FROM movies
         WHERE title IN ('The Shawshank Redemption', 'Forrest Gump')) AS two_movies
  -- 右表格是刺激1995与黑暗骑士的名单资料
  LEFT JOIN (SELECT *
               FROM casting
              WHERE movie_id IN (1, 4)) AS two_castings
    ON two_movies.id = two_castings.movie_id
 ORDER BY two_movies.title;

小结

目前学过的SQL查询语法还是要注意使用的顺序

SELECT column_names     
  FROM left_table      
  JOIN right_table     
    ON left_table.join_key = right_table.join_key
 WHERE conditions       
 GROUP BY column_names  
HAVING conditions       
 UNION SELECT statement 
 ORDER BY column_names  
 LIMIT m;              

在学完这些查询的语法过後

就可以练习用最简短的语法

把想要筛选出来的资料选出来