自學SQL網題目解答與筆記

SELECT 查詢 101

1. 【簡單查詢】找到所有電影的名稱title

   SELECT title FROM movies;
   

2. 【簡單查詢】找到所有電影的導演

   select director from movies;
   

3. 【簡單查詢】找到所有電影的名稱和導演

   select title, director from movies;
   

4. 【簡單查詢】找到所有電影的名稱和上映年份

   select title, year from movies;
   

5. 【簡單查詢】找到所有電影的所有信息

   select * from movies;
   

6. 【簡單查詢】找到所有電影的名稱,Id和播放時長

   select title, id, Length_minutes
   from movies;
   

條件查詢 (constraints) (Pt. 1)

1. 【簡單條件】找到id為6的電影

   select * from movies
   where id = 6;
   

2. 【簡單條件】找到在2000-2010年間year上映的電影

   select * 
   from movies
   where year >= 2000 and year <= 2010;
   

3. 【簡單條件】找到不是在2000-2010年間year上映的電影

   select * 
   from movies
   where year < 2000 or year > 2010;
   

4. 【簡單條件】找到頭5部電影

   select * 
   from movies
   where id <= 5;
   

5. 【簡單條件】找到2010（含）年之後的電影里片長小於兩個小時的片子

   select * 
   from movies
   where year >= 2010
   and Length_minutes < 120;
   

條件查詢(constraints) (Pt. 2)

1. 【複雜條件】找到所有Toy Story系列電影

   select *
   from movies
   where title like "Toy Story%";
   

2. 【複雜條件】找到所有John Lasseter導演的電影

   select *
   from movies
   where director = "John Lasseter";
   

3. 【複雜條件】找到所有不是John Lasseter導演的電影

   select *
   from movies
   where director <> "John Lasseter";
   

4. 【複雜條件】找到所有電影名為"WALL-"開頭的電影

   select *
   from movies
   where title like "WALL-%";
   

5. 【複雜條件】有一部98年電影中文名《蟲蟲危機》請給我找出來

   select *
   from movies
   where year = 1998;
   

查詢結果Filtering過濾 和 sorting排序

DISTINCT

DISTINCT 語法介紹，以Movies表為例，可能很多電影都是同一年Year發佈的，如果想要按年份排重，一年只能出現一部電影到結果中， 可以用 DISTINCT 關鍵字來指定某個或某些屬性列唯一返回。寫作：DISTINCT Year

選取出唯一的結果的語法:

SELECT DISTINCT column, another_column, … 
FROM mytable 
WHERE condition(s);

因為 DISTINCT 語法會直接刪除重覆的行, 我們還會學習 GROUP BY 語句， GROUP BY 也會返回唯一的行，不過可以對具有相同的 屬性值的行做一些統計計算，比如：求和.

結果排序 （Ordering results）

為瞭解決結果排序問題, 我們可以用 ORDER BY col_name 排序的語法來讓結果按一個或多個屬性列做排序.

結果排序（ordered results）

SELECT column, another_column, … 
FROM mytable WHERE condition(s) 
ORDER BY column ASC/DESC;

ORDER BY col_name 這句話的意思就是讓結果按照 col_name 列的具體值做 ASC升序 或 DESC 降序，對數字來說就是升序 1，2，3，... 或降序 ... 3,2,1 . 對於文本列，升序和降序指的是按文本的字母序。

通過Limit選取部分結果

LIMIT 和 OFFSET 子句通常和ORDER BY 語句一起使用，當我們對整個結果集排序之後，我們可以 LIMIT來指定只返回多少行結果 ,用 OFFSET來指定從哪一行開始返回。你可以想象一下從一條長繩子剪下一小段的過程，我們通過 OFFSET 指定從哪裡開始剪，用 LIMIT 指定剪下多少長度。

limited查詢

SELECT column, another_column, … 
FROM mytable 
WHERE condition(s) 
ORDER BY column ASC/DESC 
LIMIT num_limit OFFSET num_offset;

你可以想象一下一個新聞網站的新聞條目數據，他們在頁面上是按熱度和時間排序的，每一個頁面只顯示10條數據，在所有這些屬性都是不斷變化的情況下。我們可以想見通過SQL的ORDER LIMIT OFFSET 句法，我們可以根據要求從資料庫篩選出需要的新聞條目.

題目

1. 【結果排序】按導演名排重列出所有電影(只顯示導演)，並按導演名正序排列

   select distinct director
   from movies;
   

2. 【結果排序】列出按上映年份最新上線的4部電影

   select *
   from movies
   order by year desc 
   limit 4;
   

3. 【結果排序】按電影名字母序升序排列，列出前5部電影

   select *
   from movies
   order by title asc
   limit 5;
   

4. 【結果排序】按電影名字母序升序排列，列出上一題之後的5部電影

   select *
   from movies
   order by title asc
   limit 5 offset 5;
   

5. 【結果排序】如果按片長排列，John Lasseter導演導過片長第3長的電影是哪部，列出名字即可

   select title
   from movies
   where director = "John Lasseter"
   order by Length_minutes desc
   limit 1 offset 2;
   

複習 SELECT 查詢

Table(表）: North_american_cities

在這個數據表中，你需要熟悉一下latitudes（緯度）和 longitudes（經度）的概念， latitudes在赤道以北是正數，以南是負數；longitudes在子午線東部是正數，以西是負數， 在查詢中需要註意 經緯度和東西南北方向的對應關係。

1. 【複習】列出所有加拿大人的信息(包括所有欄位)

   select *
   from north_american_cities
   where country = "Canada";
   

2. 【複習】列出所有在Chicago西部的城市，從西到東排序(包括所有欄位)

   select *
   from north_american_cities
   where Longitude < (select Longitude 
                      from north_american_cities 
                      where city = "Chicago")
   order by Longitude asc;
   

3. 【複習】用人口數population排序,列出墨西哥Mexico最大的2個城市(包括所有欄位)

   select *
   from north_american_cities
   where country = "Mexico"
   order by population desc
   limit 2;
   

4. 【複習】列出美國United States人口3-4位的兩個城市和他們的人口(包括所有欄位)

   select *
   from north_american_cities
   where country = "United States"
   order by population desc
   limit 2 offset 2;
   

5. 列出所有美國United States的城市按緯度從北到南排序(包括所有欄位)

   select * 
   from north_american_cities
   where country = "United States"
   order by Latitude desc;
   

6. 北美所有城市,請按國家名字母序從A-Z再按人口從多到少排列看下前10位的城市(包括所有欄位)

   select * 
   from north_american_cities
   order by Country asc, Population desc
   limit 10;
   

用JOINs進行多表聯合查詢

用JOINs進行多表聯合查詢

藉助主鍵(primary key)（當然其他唯一性的屬性也可以），我們可以把兩個表中具有相同 主鍵ID的數據連接起來（因為一個ID可以簡要的識別一條數據，所以連接之後還是表達的同一條數據）（你可以想象一個左右連線游戲）。具體我們用到 JOIN 關鍵字。我們先來學習 INNER JOIN.

用INNER JOIN 連接表的語法

SELECT column, another_table_column, … 
FROM mytable （主表） 
INNER JOIN another_table （要連接的表）    
	ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC 
LIMIT num_limit OFFSET num_offset;

通過ON條件描述的關聯關係;INNER JOIN 先將兩個表數據連接到一起. 兩個表中如果通過ID互相找不到的數據將會捨棄。此時，你可以將連表後的數據看作兩個表的合併，SQL中的其他語句會在這個合併基礎上 繼續執行（想一下和之前的單表操作就一樣了）.

練習

Table: Movies

Table: Boxoffice

1. 【聯表】找到所有電影的國內Domestic_sales和國際銷售額International_sales

   Movies表+Boxoffice表的兩個欄位

   select *
   from Movies
   inner join Boxoffice
   where Movies.id = Boxoffice.Movie_id;
   

   Id	Title	Director	Year	Length_minutes	Movie_id	Rating	Domestic_sales	International_sales
   1	Toy Story	John Lasseter	1995	81	1	8.3	191796233	170162503
   2	A Bug's Life	John Lasseter	1998	95	2	7.2	162798565	200600000

2. 【聯表】找到所有國際銷售額比國內銷售大的電影

   select *
   from Movies
   inner join Boxoffice
   where Movies.id = Boxoffice.Movie_id
   and Domestic_sales < International_sales;
   

3. 【聯表】找出所有電影按市場占有率rating倒序排列

   select *
   from Movies
   inner join Boxoffice
   where Movies.id = Boxoffice.Movie_id
   order by rating desc;
   

4. 【聯表】每部電影按國際銷售額比較,排名最靠前的導演是誰,國際銷量多少

   select director, International_sales
   from Movies
   inner join Boxoffice
   where Movies.id = Boxoffice.Movie_id
   order by International_sales desc
   limit 1;
   

5. John Lasseter導演的每部電影每分鐘值多少錢,告訴我最高的3個電影名和價值就可以

   select 
   	title, 
   	(Domestic_sales + International_sales) / Length_minutes as min_val
   from Movies
   inner join Boxoffice
   on Movies.id = Boxoffice.Movie_id
   where Director = "John Lasseter"
   order by min_val desc
   limit 3;
   

外連接（OUTER JOINs）

INNER JOIN 只會保留兩個表都存在的數據（還記得之前的交集嗎），這看起來意味著一些數據的丟失，在某些場景下會有問題.

真實世界中兩個表存在差異很正常，所以我們需要更多的連表方式，也就是本節要介紹的左連接LEFT JOIN,右連接RIGHT JOIN 和 全連接FULL JOIN. 這幾個 連接方式都會保留不能匹配的行。

用LEFT/RIGHT/FULL JOINs 做多表查詢

SELECT column, another_column, … 
FROM mytable 
INNER/LEFT/RIGHT/FULL JOIN another_table    
ON mytable.id = another_table.matching_id
WHERE condition(s) 
ORDER BY column, … ASC/DESC 
LIMIT num_limit OFFSET num_offset;

和INNER JOIN 語法幾乎是一樣的. 我們看看這三個連接方法的工作原理：
在表A 連接 B, LEFT JOIN保留A的所有行，不管有沒有能匹配上B 反過來 RIGHT JOIN則保留所有B里的行。最後FULL JOIN 不管有沒有匹配上，同時保留A和B里的所有行

練習

Table: Employees

Table: Buildings

1. 【複習】找到所有有雇員的辦公室(buildings)名字

   select distinct building
   from Employees 
   where Building is not null;
   

2. 【複習】找到所有辦公室里的所有角色（包含沒有雇員的）,並做唯一輸出(DISTINCT)

   select distinct 
   	Buildings.building_name, 
       Employees.Role
   from Buildings
   left join Employees
   on Employees.Building = Buildings.Building_name; 
   

3. 【難題】找到所有有雇員的辦公室(buildings)和對應的容量

   select distinct 
   	Building, 
       Capacity
   from Buildings
   left join Employees
   on Employees.Building = Buildings.Building_name
   where Building is not null;
   

on & where 的區別

在使用left jion時，on和where條件的區別如下：

1. on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。
2. where條件是在臨時表生成 好後，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。

關於特殊關鍵字 NULLs

Table: Employees

Table: Buildings

1. 【複習】找到雇員里還沒有分配辦公室的(列出名字和角色就可以)

   select distinct Name, Role
   from Employees
   left join Buildings 
   where Building is null;
   

2. 【難題】找到還沒有雇員的辦公室

   select Building_name
   from Buildings
   left join Employees
   on Buildings.Building_name = Employees.Building	
   where name is null;
   

   思路：

   先進行聯表查詢

   Building_name	Capacity	Role	Name	Building	Years_employed
   1e	24	Engineer	Becky A.	1e	4
   1e	24	Engineer	Dan B.	1e	2
   1e	24	Engineer	Dan M.	1e	4
   1e	24	Engineer	Malcom S.	1e	1
   1e	24	Engineer	Sharon F.	1e	6
   1e	24	Manager	Scott K.	1e	9
   1e	24	Manager	Shirlee M.	1e	3
   1w	32	null	null	null	null
   2e	16	null	null	null	null
   2w	20	Artist	Brandon J.	2w	7
   2w	20	Artist	Jakob J.	2w	6
   2w	20	Artist	Lillia A.	2w	7
   2w	20	Artist	Sherman D.	2w	8
   2w	20	Artist	Tylar S.	2w	2
   2w	20	Manager	Daria O.	2w	6

   可看到一些行Building存在而name為空，這就是沒有雇員的房間

   Building_name	Capacity	Role	Name	Building	Years_employed
   1w	32	null	null	null	null
   2e	16	null	null	null	null

在查詢中使用表達式

Table: Movies (Read-Only)

Table: Boxoffice (Read-Only)

1. 【計算】列出所有的電影ID,名字和銷售總額(以百萬美元為單位計算)

   select 
   	id, title, 
       (Domestic_sales + International_sales) / 1000000 as total
   from movies
   inner join Boxoffice 
   on movies.Id = Boxoffice.Movie_id;
   

2. 【計算】列出所有的電影ID,名字和市場指數(Rating的10倍為市場指數)

   select 
   	id, title, 
       Rating * 10 MarketIndex
   from movies
   inner join Boxoffice
   on movies.Id = Boxoffice.Movie_id;
   

3. 【計算】列出所有偶數年份的電影，需要電影ID,名字和年份

   select 
   	id, title, year
   from movies
   inner join Boxoffice
   on movies.Id = Boxoffice.Movie_id
   where year % 2 = 0;
   

4. 【難題】John Lasseter導演的每部電影每分鐘值多少錢,告訴我最高的3個電影名和價值就可以

   select 
   	title, 
   	(Domestic_sales + International_sales) / Length_minutes as min_val
   from Movies
   inner join Boxoffice
   on Movies.id = Boxoffice.Movie_id
   where Director = "John Lasseter"
   order by min_val desc
   limit 3;
   

在查詢中進行統計I (Pt. 1)

對全部結果數據做統計

SELECT 
AGG_FUNC(column_or_expression) AS aggregate_description, … FROM mytable 
WHERE constraint_expression;

如果不指明如何分組，那統計函數將對查詢結果全部數據進行統計，當然每一個統計也可以像之前用AS來取一個別名，以增加可讀性。

常見統計函數

下麵介紹幾個常用統計函數:

分組統計

GROUP BY 數據分組語法可以按某個col_name對數據進行分組，如：GROUP BY Year指對數據按年份分組， 相同年份的分到一個組裡。如果把統計函數和GROUP BY結合，那統計結果就是對分組內的數據統計了.
GROUP BY 分組結果的數據條數，就是分組數量，比如：GROUP BY Year，全部數據里有幾年，就返回幾條數據， 不管是否應用了統計函數.

用分組的方式統計

SELECT 
AGG_FUNC(column_or_expression) AS aggregate_description, … FROM mytable 
WHERE constraint_expression
GROUP BY column;

練習

Table(表）: Employees 全表查看

1. 【統計】找出就職年份最高的雇員(列出雇員名字+年份）

   select Name, max(Years_employed)
   from Employees;
   

2. 【分組】按角色(Role)統計一下每個角色的平均就職年份

   select Role, avg(Years_employed)
   from Employees
   group by Role;
   

3. 【分組】按辦公室名字總計一下就職年份總和

   select Building, sum(Years_employed)
   from Employees
   group by Building;
   

4. 【難題】每棟辦公室按人數排名,不要統計無辦公室的雇員

   select Building, count(*) as count
   from Employees
   where Building is not null
   group by Building;
   

在查詢中進行統計II (Pt. 2)

在 GROUP BY 分組語法中，我們知道資料庫是先對數據做WHERE，然後對結果做分組，如果我們要對分組完的數據再篩選出幾條如何辦？ （想一下按年份統計電影票房，要篩選出>100萬的年份？）

一個不常用的語法 HAVING 語法將用來解決這個問題，他可以對分組之後的數據再做SELECT篩選.

用HAVING進行篩選

SELECT group_by_column, 
AGG_FUNC(column_expression) AS aggregate_result_alias, … FROM mytable 
WHERE condition
GROUP BY column 
HAVING group_condition;

HAVING 和 WHERE 語法一樣，只不過作用的結果集不一樣. 在我們例子數據表數據量小的情況下可能感覺 HAVING沒有什麼用，但當你的數據量成千上萬屬性又很多時也許能幫上大忙 .

Table(表）: Employees 全表查看

1. 【統計】統計一下Artist角色的雇員數量

   select count(*)
   from Employees
   where Role = "Artist";
   

2. 【分組】按角色統計一下每個角色的雇員數量

   select Role, count(*)
   from Employees
   group by Role;
   

3. 【分組】算出Engineer角色的就職年份總計

   select sum(Years_employed)
   from Employees 
   where Role = "Engineer";
   

4. 【難題】按角色分組算出每個角色按有辦公室和沒辦公室的統計人數(列出角色，數量，有無辦公室,註意一個角色如果部分有辦公室，部分沒有需分開統計）

   select 
   	Role, 
       count(Role) as count,
       case
       	when Building is null then 0
           else 1
       end 
       as bn
   from Employees 
   group by Role, bn;
   

查詢執行順序

介紹完了所有查詢相關的語法，我們來把之前的所有語法集中到一個句子中.

這才是完整的SELECT查詢

SELECT DISTINCT 
	column, 
	AGG_FUNC(column_or_expression), … 
FROM mytable    
JOIN another_table 
ON mytable.column = another_table.column    
WHERE constraint_expression   
GROUP BY column    
HAVING constraint_expression    
ORDER BY column ASC/DESC    
LIMIT count OFFSET COUNT;

一個查詢SQL的執行總是先從數據里按條件選出數據，然後對這些數據再次做一些整理處理，按要求返回成結果，讓結果儘可能是簡單直接的。因為一個 查詢SQL由很多部分組成，所以搞清楚這些部分的執行順序還挺重要的，這有助於我們更深刻的理解SQL執行過程.

查詢執行順序

1. FROM 和 JOIN

   FROM 或 JOIN會第一個執行，確定一個整體的數據範圍. 如果要JOIN不同表，可能會生成一個臨時Table來用於 下麵的過程。總之第一步可以簡單理解為確定一個數據源表（含臨時表)

2. WHERE

   我們確定了數據來源 WHERE 語句就將在這個數據源中按要求進行數據篩選，並丟棄不符合要求的數據行，所有的篩選col屬性 只能來自FROM圈定的表. AS別名還不能在這個階段使用，因為可能別名是一個還沒執行的表達式

3. GROUP BY

   如果你用了 GROUP BY 分組，那GROUP BY 將對之前的數據進行分組，統計等，並將是結果集縮小為分組數.這意味著 其他的數據在分組後丟棄.

4. HAVING

   如果你用了 GROUP BY 分組, HAVING 會在分組完成後對結果集再次篩選。AS別名也不能在這個階段使用.

5. SELECT

   確定結果之後，SELECT用來對結果col簡單篩選或計算，決定輸出什麼數據.

6. DISTINCT

   如果數據行有重覆DISTINCT 將負責排重.

7. ORDER BY

   在結果集確定的情況下，ORDER BY 對結果做排序。因為SELECT中的表達式已經執行完了。此時可以用AS別名.

8. LIMIT / OFFSET

   最後 LIMIT 和 OFFSET 從排序的結果中截取部分數據.

練習

Table: Movies (Read-Only)

Table: Boxoffice (Read-Only)

1. 【複習】統計出每一個導演的電影數量（列出導演名字和數量）

   select Director, count(*) as count
   from Movies 
   group by Director;
   

2. 【複習】統計一下每個導演的銷售總額(列出導演名字和銷售總額)

   select 
   	Director, 
       sum(Domestic_sales + International_sales) as total
   from Movies 
   inner join Boxoffice 
   on Movies.id = Boxoffice.Movie_id
   group by Director;
   

3. 【難題】按導演分組計算銷售總額,求出平均銷售額冠軍（統計結果過濾掉只有單部電影的導演，列出導演名，總銷量，電影數量，平均銷量)

   select 
   	Director, 
       sumSales,
       countMovies,
       sumSales / countMovies as avgSales
   from (
   	select 
       	Director, 
           sum(Domestic_sales + International_sales) as sumSales,
           count(title) as countMovies
       from movies
       inner join Boxoffice 
       on movies.id = Boxoffice.Movie_id
       group by director
       having count(title) <> 1
       )
   order by avgSales desc
   limit 1;
   

   Director	Sum_a	Count_a	Sum_a/Count_a
   Pete Docter	1294159000	2	647079500

4. 【變態難】找出每部電影和單部電影銷售冠軍之間的銷售差，列出電影名，銷售額差額

   select (
     	select
     		(Domestic_sales + International_sales) as sumSales
     	from movies
    	inner join Boxoffice 
     	on Movies.id = boxoffice.movie_id
     	order by sumSales desc 
     	limit 1
   ) -
   (Domestic_sales+International_sales) as saleDiff,
   title
   from movies
   inner join Boxoffice 
   on Movies.id = boxoffice.movie_id
   order by saleDiff desc;