從資料表選擇
讀者如果是資料科學的初學者,可以略過下述的程式碼;讀者如果不是資料科學的初學者,欲使用 JupyterLab 執行本章節內容,必須先執行下述程式碼載入所需模組與連接資料庫。
%LOAD sqlite3 db=../databases/imdb.db timeout=2 shared_cache=true
ATTACH "../databases/nba.db" AS nba;
ATTACH "../databases/twElection2020.db" AS twElection2020;
ATTACH "../databases/covid19.db" AS covid19;
在第二章「建立學習環境」我們寫作了 SQL 敘述完成哈囉世界、檢視學習資料庫中第一個資料表(依照英文字母順序排列)的「前五列、所有欄」,藉此確認學習環境能夠妥善運行。
SELECT 'Hello, World!';
'Hello, World!' |
---|
Hello, World! |
SELECT *
FROM actors
LIMIT 5;
id | name |
---|---|
1 | Aamir Khan |
2 | Aaron Eckhart |
3 | Aaron Lazar |
4 | Abbas-Ali Roomandi |
5 | Abbey Lee |
在第二章「建立學習環境」我們寫作了 SQL 敘述查詢四個學習資料庫每一個資料表的元資料(Metadata)來獲得每一欄的資訊。
SELECT *
FROM PRAGMA_TABLE_INFO('actors');
cid | name | type | notnull | dflt_value | pk |
---|---|---|---|---|---|
0 | id | INTEGER | 0 | NULL | 1 |
1 | name | TEXT | 0 | NULL | 0 |
藉由觀察這三個 SQL 敘述,我們可以將 SQL 敘述歸納為以下幾個部分的組成:
SELECT
、FROM
與 LIMIT
。*
與 ;
。'Hello, World!'
。PRAGMA_TABLE_INFO()
。其中 SELECT
是「選擇」欄的保留字,FROM
是指定「從」哪個資料表查詢,LIMIT m
是讓查詢結果顯示前 m
列,*
表示「所有欄」,;
表示一段 SQL 敘述的結束。我們習慣以 (m, n)
來描述一個具有 m
列、n
欄的資料表或者查詢結果,其中 m
不包含欄名那一列,舉例來說 SELECT 'Hello, World!';
的查詢結果是 (1, 1)
。
SELECT 'Hello, World!';
'Hello, World!' |
---|
Hello, World! |
SELECT * FROM actors LIMIT 5;
的查詢結果是 (5, 2)
SELECT *
FROM actors
LIMIT 5;
id | name |
---|---|
1 | Aamir Khan |
2 | Aaron Eckhart |
3 | Aaron Lazar |
4 | Abbas-Ali Roomandi |
5 | Abbey Lee |
SELECT constants
¶使用單獨存在的 SELECT
保留字指定希望在查詢結果中顯示的常數,常用的常數類別有四種,分別是整數、浮點數、文字與空值,我們可以使用 TYPEOF()
函數顯示常數或者資料表欄位的類別,當 SELECT
之後有不只一個資料的時候就用逗號 ,
分隔。
常數類別 | 範例 |
---|---|
整數 integer |
7 , 19 , 5566 , ...etc. |
浮點數 real |
2.718 , 3.14159 , ...etc. |
文字 text |
'Hello, World!' , 'SQL' , ...etc. |
空值 null |
NULL |
SELECT 5566,
TYPEOF(5566);
5566 | TYPEOF(5566) |
---|---|
5566 | integer |
SELECT 2.718,
TYPEOF(2.718);
2.718 | TYPEOF(2.718) |
---|---|
2.718 | real |
SELECT 'Hello, World!',
TYPEOF('Hello, World!');
'Hello, World!' | TYPEOF('Hello, World!') |
---|---|
Hello, World! | text |
SELECT NULL,
TYPEOF(NULL);
NULL | TYPEOF(NULL) |
---|---|
NULL | null |
寫作 SQL 敘述會有需要在其中添加註解(Comments)的時候,註解是不會被關聯式資料庫管理系統執行的說明文字,通常作為與工作上的同事、或者未來的自己(看不懂自己從前所寫的程式是相當常見的),說明 SQL 敘述中值得注意的事項或者邏輯,常用的註解形式有單行註解、行末註解與多行註解。
單行註解:用兩個減號 --
標註並且單獨存在一行。
-- single line comment
SELECT columns
FROM table
LIMIT m;
行末註解:用兩個減號 --
標註,但是置放於一行 SQL 敘述的句尾。
SELECT columns -- end of line comment
FROM table -- end of line comment
LIMIT m; -- end of line comment
多行註解:用 /*
開頭、*/
結尾來標註。
/*
multiple-line comments...
multiple-line comments...
multiple-line comments...
*/
SELECT columns
FROM table
LIMIT m;
LIMIT
¶SELECT columns
FROM table
LIMIT m;
因為資料表中的觀測值列數可能都有很多筆,為了在有限的版面顯示,我們可以透過 LIMIT
保留字讓查詢結果顯示前 m
列即可。
SELECT *
FROM movies
LIMIT 1;
id | title | release_year | rating | director | runtime |
---|---|---|---|---|---|
1 | The Shawshank Redemption | 1994 | 9.3 | Frank Darabont | 142 |
SELECT *
FROM movies
LIMIT 3;
id | title | release_year | rating | director | runtime |
---|---|---|---|---|---|
1 | The Shawshank Redemption | 1994 | 9.3 | Frank Darabont | 142 |
2 | The Godfather | 1972 | 9.2 | Francis Ford Coppola | 175 |
3 | The Dark Knight | 2008 | 9 | Christopher Nolan | 152 |
SELECT columns FROM table;
¶從資料表選擇欄位的時候使用 SELECT
與 FROM
保留字分別指定欄位名稱與資料表名稱,若希望從資料表選擇「所有」欄位,可以使用星號(*
)達成。
SELECT *
FROM movies
LIMIT 5;
id | title | release_year | rating | director | runtime |
---|---|---|---|---|---|
1 | The Shawshank Redemption | 1994 | 9.3 | Frank Darabont | 142 |
2 | The Godfather | 1972 | 9.2 | Francis Ford Coppola | 175 |
3 | The Dark Knight | 2008 | 9 | Christopher Nolan | 152 |
4 | The Godfather Part II | 1974 | 9 | Francis Ford Coppola | 202 |
5 | 12 Angry Men | 1957 | 9 | Sidney Lumet | 96 |
在 SELECT
後加入欄的名稱讓查詢結果只顯示資料表中指定的欄。
SELECT title
FROM movies
LIMIT 5;
title |
---|
The Shawshank Redemption |
The Godfather |
The Dark Knight |
The Godfather Part II |
12 Angry Men |
在 SELECT
後加入欄的名稱讓查詢結果只顯示資料表中指定的多個欄,在不同欄名稱之間用逗號分隔。
SELECT title,
release_year,
director
FROM movies
LIMIT 5;
title | release_year | director |
---|---|---|
The Shawshank Redemption | 1994 | Frank Darabont |
The Godfather | 1972 | Francis Ford Coppola |
The Dark Knight | 2008 | Christopher Nolan |
The Godfather Part II | 1974 | Francis Ford Coppola |
12 Angry Men | 1957 | Sidney Lumet |
AS alias
¶SELECT constants AS alias;
SELECT columns AS alias
FROM table;
我們可以透過 AS
保留字來為查詢的結果取別名,不論是常數或者是資料表的欄,都能在查詢結果中以指定的名稱顯示。
SELECT 'Hello, World!' AS hello_world,
TYPEOF('Hello, World!') AS typeof_hello_world;
hello_world | typeof_hello_world |
---|---|
Hello, World! | text |
SELECT title AS movie,
release_year AS released_in,
director AS directed_by
FROM movies
LIMIT 5;
movie | released_in | directed_by |
---|---|---|
The Shawshank Redemption | 1994 | Frank Darabont |
The Godfather | 1972 | Francis Ford Coppola |
The Dark Knight | 2008 | Christopher Nolan |
The Godfather Part II | 1974 | Francis Ford Coppola |
12 Angry Men | 1957 | Sidney Lumet |
DISTINCT
¶SELECT DISTINCT columns
FROM table;
我們可以透過 DISTINCT
保留字來為查詢的結果剔除重複值,舉例來說,在 imdb
資料庫的 movies
資料表中 director
欄的前 10 列可以看到重複出現的導演如 Francis Ford Coppola(執導教父三部曲)與 Peter Jackson(執導魔戒三部曲)。
SELECT director
FROM movies
LIMIT 10;
director |
---|
Frank Darabont |
Francis Ford Coppola |
Christopher Nolan |
Francis Ford Coppola |
Sidney Lumet |
Steven Spielberg |
Peter Jackson |
Quentin Tarantino |
Peter Jackson |
Sergio Leone |
在加入 DISTINCT
保留字之後同樣顯示前 10 列,可以清楚發現已經沒有重複出現的導演。
SELECT DISTINCT director
FROM movies
LIMIT 10;
director |
---|
Frank Darabont |
Francis Ford Coppola |
Christopher Nolan |
Sidney Lumet |
Steven Spielberg |
Peter Jackson |
Quentin Tarantino |
Sergio Leone |
Robert Zemeckis |
David Fincher |
閱讀到這裡,讀者對於寫作過的 SQL 敘述應該有一些疑惑,例如保留字大寫、換行或者句首的空白(縮排,Indentation),如果沒有遵照這樣的方式寫作會不會影響查詢結果呢?答案是「不會」,SQL 具有幾個語言特性:
;
做為結束的標註。'some texts'
標註,整數、浮點數與空值可以直接寫作。在 SQL 的敘述中保留字大小寫、是否換行或者是否有縮排,都不會對查詢結果有任何的影響。例如下列的這段 SQL 敘述,保留字大小寫、換行與縮排都相當隨興,但卻沒有影響到查詢的結果。
Select title, release_year,
rating
from movies liMiT 5;
title | release_year | rating |
---|---|---|
The Shawshank Redemption | 1994 | 9.3 |
The Godfather | 1972 | 9.2 |
The Dark Knight | 2008 | 9 |
The Godfather Part II | 1974 | 9 |
12 Angry Men | 1957 | 9 |
唯一不能夠隨興寫作的是 SQL 敘述中保留字彼此之間的「相對順序」,例如目前所學的幾個保留字 SELECT
、FROM
、LIMIT
等。
SELECT DISTINCT columns AS alias
FROM table
LIMIT m;
如果調動保留字的相對順序,就會得到語法錯誤的訊息(Syntax error),例如對調 LIMIT
與 FROM
的順序。
SELECT DISTINCT director AS distinct_director
LIMIT 10
FROM movies;
near "FROM": syntax error while preparing "SELECT DISTINCT director AS distinct_director
LIMIT 10
FROM movies;".
又或者對調 SELECT
與 FROM
的順序。
FROM movies
SELECT DISTINCT director AS distinct_director
LIMIT 10;
near "FROM": syntax error while preparing " FROM movies
SELECT DISTINCT director AS distinct_director
LIMIT 10;".
只需要遵守保留字的書寫順序讓 SQL 寫作時有很大的彈性,但是這不代表只要查詢結果是正確的,就可以隨心所欲地寫作。有時候我們必須顧慮到可讀性(Readibility),特別是在工作時,可讀性更為重要,因為工作上可能會遭遇到協作、程式碼審查(Code review)、維運、代理或者交接等情境,在這些時候查詢結果正確僅是最低門檻,可讀性必須要能達到前述情境中所要求的程度。推薦的作法是參考一份工作團隊、協作夥伴或者自己喜歡並且願意去遵從的風格指南(Style guide),風格指南指的是一套規範程式語言在寫作時必須遵從的編排、格式和設計的準則,風格指南能夠確保每一段程式碼都和其他不同人寫作的程式碼有高度一致性,被眾多使用者採用的 SQL 風格指南有:
本書採用 Simon Holywell 的風格指南來寫作 SQL,其中值得注意的幾個重點有:
alias_for_some_variables
。SELECT DISTINCT director AS distinct_director
FROM movies
LIMIT 10;
distinct_director |
---|
Frank Darabont |
Francis Ford Coppola |
Christopher Nolan |
Sidney Lumet |
Steven Spielberg |
Peter Jackson |
Quentin Tarantino |
Sergio Leone |
Robert Zemeckis |
David Fincher |
除了參閱、遵從 Simon Holywell 的風格指南,我們也可以善用 SQLiteStudio 的 Format SQL 功能,在編輯器範圍按右鍵,讓寫作的 SQL 敘述之編排、格式和設計具備更高的可讀性。
SELECT
FROM
LIMIT
AS
DISTINCT
SELECT DISTINCT columns AS alias
FROM table
LIMIT m;
id | county | town | village |
---|---|---|---|
1 | 南投縣 | 中寮鄉 | 中寮村 |
2 | 南投縣 | 中寮鄉 | 內城村 |
3 | 南投縣 | 中寮鄉 | 八仙村 |
4 | 南投縣 | 中寮鄉 | 和興村 |
5 | 南投縣 | 中寮鄉 | 崁頂村 |
nba
資料庫的球隊資料表 teams
中選擇 confName
、divName
、fullName
三個變數,並且使用 LIMIT 10
顯示前十列資料,參考下列預期的查詢結果。¶預期輸出:(10, 3) 的查詢結果。
confName | divName | fullName |
---|---|---|
East | Southeast | Atlanta Hawks |
East | Atlantic | Boston Celtics |
East | Central | Cleveland Cavaliers |
West | Southwest | New Orleans Pelicans |
East | Central | Chicago Bulls |
West | Southwest | Dallas Mavericks |
West | Northwest | Denver Nuggets |
West | Pacific | Golden State Warriors |
West | Southwest | Houston Rockets |
West | Pacific | LA Clippers |
nba
資料庫的球員資料表 players
中選擇 firstName
、lastName
兩個變數,並依序取別名為 first_name
、last_name
,使用 LIMIT 5
顯示前五列資料,參考下列預期的查詢結果。¶預期輸出:(5, 2) 的查詢結果。
first_name | last_name |
---|---|
LeBron | James |
Carmelo | Anthony |
Udonis | Haslem |
Dwight | Howard |
Andre | Iguodala |
twElection2020
資料庫的 admin_regions
資料表選擇「不重複」的縣市(county
),參考下列的預期查詢結果。¶預期輸出:(22, 1) 的查詢結果。
distinct_counties |
---|
南投縣 |
嘉義市 |
嘉義縣 |
基隆市 |
宜蘭縣 |
屏東縣 |
彰化縣 |
新北市 |
新竹市 |
新竹縣 |
桃園市 |
澎湖縣 |
臺中市 |
臺北市 |
臺南市 |
臺東縣 |
花蓮縣 |
苗栗縣 |
連江縣 |
金門縣 |
雲林縣 |
高雄市 |
nba
資料庫的 teams
資料表選擇「不重複」的分組(divName
),參考下列的預期查詢結果。¶預期輸出:(6, 1) 的查詢結果。
distinct_divisions |
---|
Southeast |
Atlantic |
Central |
Southwest |
Northwest |
Pacific |