[Notes] Database SQL 基本語法

19 min readJun 22, 2021

SQL = Structure Query Language，他是一個用來查詢、操作資料庫的語言。

為什麼要使用資料庫？

「CRUD = Create創建, Read讀取, Update更新, Delete刪除」這四項東西無法規模化、自動化。

Create: 每個新註冊會員、每筆新成立訂單
Read: 發送行銷 EDM 的對象、特定節慶的折扣
Update: 註冊會員資料的修改
Delete: 會員的離開、訂單的取消

資料庫的分類
（Ａ）SQL — e.g. Microsoft SQL Server, Oracle, IBM DB2, MySQL, PostgreSQL
（Ｂ）NoSQL — e.g. MangoDB, Firebase
簡單來說，SQL 資料庫以表格的形式儲存資料，表格較直觀，但不夠靈活。NoSQL 資料庫以 JSON 格式儲存資料，JSON 較抽象，但非常靈活，可以無限巢狀。

SQL 的重要性？

SQL 是在職的資料科學家推薦新手學習的前三語言之一。

初探SQL語法與程式撰寫習慣

SELECT 欄位名稱（變數名稱）,
       衍生運算 AS 衍生變數名稱
    FROM 資料庫名稱.表格名稱
    WHERE 條件篩選
    GROUP BY 分組條件
    HAVING 分組條件篩選
    ORDER BY 查詢結果客製化排序

#1 查詢語法：函數全大寫

#2 變數名稱、資料庫名稱與表格名稱：遵從其命名之大小寫

#3 換行與縮排

選擇多個變數請換行，一行一個變數
換行後查詢語法使用 Tab 鍵縮排

#4 使用分號標註一個查詢的結束

#5 以 /**/ 加入註解

[CLICK ME] SQL 樣式指南

/*從 world.country 中選擇 Name 與 Continent 兩個變數*/
SELECT Name,
       Continent
    FROM world.country;

E.G. 課程範例：從資料庫表格 nba.player_profile 中找出Kawhi Leonard 生日

SELECT dateOfBirthUTC, 
       firstName, 
       lastName
    FROM nba.player_profile
    WHERE firstName = 'Kawhi';

使用函數來表達我們想要的資訊

ROUND(_, 保留的小數點位數)

POWER(_, exponent)

E.g. 請計算球員的BMI

SELECT personName,
       heightMeters,
       weightKilograms,
       ROUND(weightKilograms/POWER(heightMeters, 2), 2) AS BMI
    FROM nba.nba_players;

WHERE 敘述

對查詢結果下條件篩選，概念上像是從superset取subset出來。

E.g.1 請找出日本的人口數

SELECT Name,
       Population
    FROM world.country
    WHERE Name = 'Japan';

E.g.2 查詢 nba.player_career_summary 中場均得分 ppg 超過 25 分或低於 10 分的球員 ID personId、場均得分 ppg

SELECT personId,
       ppg
    FROM nba.player_career_summary
    WHERE ppg > 20;

以OR / IN描述聯集

SELECT *
    FROM world.country
    WHERE Name = 'Japan' OR
          Name = 'France' OR
          Name = 'Taiwan';SELECT *
    FROM world.country
    WHERE Name IN ('Japan', 'France', 'Taiwan');

以AND / BETWEEN描述交集

SELECT Name,
       SurfaceArea
    FROM world.country
    WHERE SurfaceArea > 250000 AND SurfaceArea < 300000;SELECT Name,
       SurfaceArea
    FROM world.country
    WHERE SurfaceArea BETWEEN 250000 AND 300000;

使用 != 描述不等於
使用 % 與 LIKE 作模糊比對

E.g. 選出國名中有 land 的國家名

SELECT Name
    FROM world.country
    WHERE Name LIKE '%land%';

ORDER BY 做查詢結果客製化排序

預設遞增排序：ASC
若要遞減排序：DESC

E.g. 找出人口數最多的前 10 個國家（LIMIT 敘述）

SELECT Name,
       Population
    FROM world.country
    ORDER BY Population DESC
    LIMIT 10;

子查詢 Sub-query

SELECT within SELECT
將前一次查詢的結果巢狀在本次查詢中，就是子查詢 Sub-query

E.g.1 請找出人口數比巴西多的國家

/*在不會子查詢前的我們，可能會用以下方法實現*/
/*先知道巴西人口有多少*/
SELECT Population
    FROM world.country
    WHERE Name = 'Brazil';
/*然後再寫一個查詢*/
SELECT Name
    FROM world.country
    WHERE Population > ___;/*如果改成用子查詢，其實就是把第一個Query的內容傳入到第二個Query的限制條件*/
SELECT Name
    FROM world.country
    WHERE Population > (SELECT Population
                            FROM world.country
                            WHERE Name = 'Brazil');

E.g.2 使用子查詢從 nba.nba_players 找出 ppg 比 Stephen Curry 高的球員

SELECT personName
    FROM nba.nba_players
    WHERE ppg > (SELECT ppg 
                     FROM nba_players 
                     WHERE personName = 'Stephen Curry');

E.g.3 使用子查詢從 nba.nba_teams 找出與 Boston Celtics、Dallas Mavericks 同一個 Division 的球隊

SELECT fullName,
       divName
    FROM nba.nba_teams
    WHERE divName IN (SELECT divName 
                          FROM nba_teams 
                          WHERE fullName IN ('Boston Celtics', 'Dallas Mavericks'));

一般函數

一般函數：輸入與輸出資料長度相同（輸入幾筆、輸出就有幾筆）。

LENGTH(str)得知文字長度
LEFT(str, n)取出由左邊數來的前面 n 個字元
CONCAT(str1, str2, str3)連結文字
…etc.

E.g.1 從 nba.nba_teams 找出城市名稱小於等於 6 個英文字母的球隊，像是 Miami、Boston…etc.

SELECT fullName,
       city
    FROM nba.nba_teams
    WHERE LENGTH(city) <= 6;

E.g.2 利用 CONCAT() 函數將 nba.player_profile 的 firstName 與 lastName 連結起來為 playerName

SELECT firstName,
       lastName,
       CONCAT(firstName, ' ', lastName) AS playerName
    FROM nba.player_profile;

聚合函數

聚合函數：輸入資料長度通常都大於輸出（函數具有統計、摘要的特性）。

COUNT()計算列數
SUM()回傳加總值
DISTINCT()回傳相異值
…etc.

E.g.1 查詢 nba.player_career_summary 中有幾個球員的場均得分 ppg 超過 20 分

SELECT COUNT(*) AS n_players
    FROM nba.player_career_summary
    WHERE ppg > 20;

E.g.2 查詢 nba.nba_teams 中央組（Central）與西南組（Southwest）共有幾支球隊

SELECT COUNT(*) AS n_teams
    FROM nba.nba_teams
    WHERE divName IN ('Central', 'Southwest');

E.g.3 查詢 tw_election_2020.president 中三組候選人的得票數

SELECT SUM(votes) AS ttl_votes
    FROM tw_election_2020.president
    WHERE candidates = '宋楚瑜/余湘';

SELECT SUM(votes) AS ttl_votes
    FROM tw_election_2020.president
    WHERE candidates = '韓國瑜/張善政';

SELECT SUM(votes) AS ttl_votes
    FROM tw_election_2020.president
    WHERE candidates = '蔡英文/賴清德';

E.g.4 查詢 tw_election_2020.president 臺北市共有幾個投票所 office？臺北市大安區有幾個投票所

SELECT COUNT(DISTINCT(office)) AS nb_office
    FROM tw_election_2020.president
    WHERE city_county = '臺北市';

SELECT COUNT(DISTINCT(office)) AS nb_office
    FROM tw_election_2020.president
    WHERE district = '大安區' AND
          city_county = '臺北市';

GROUP BY

依類別分別作聚合函數運算 / 分組來整理表格。

E.g.1 計算各個 Continent 的國家數

SELECT Continent,
       COUNT(*) AS n_countries
    FROM world.country
    GROUP BY Continent;

E.g.2 計算各個 Continent 的人口總數

SELECT Continent, 
       SUM(Population) AS n_ppl
    FROM world.country
    GROUP BY Continent;

E.g.3 查詢 tw_election_2020.president 台北市的行政區各自有幾個投票所，並遞減排序顯示

SELECT city_county,
       district,
       COUNT(DISTINCT(office)) AS n_office
    FROM tw_election_2020.president
    WHERE city_county = '臺北市'
    GROUP BY district
    ORDER BY n_office DESC;

E.g.4 查詢 tw_election_2020.president 六都各組候選人的得票數分別為何

SELECT city_county,
       number,
       candidates,
       SUM(votes) AS ttl_votes
    FROM tw_election_2020.president
    WHERE city_county IN ('臺北市', '新北市', '桃園市', '臺中市', '臺南市', '高雄市')
    GROUP BY city_county, candidates
    ORDER BY city_county, number;

HAVING

HAVING只能用在aggregate (合計) 情況下，也就是有GROUP BY的時候，並且HAVING要過濾的條件，要跟GROUP BY的合計函數有關。（針對聚合函數的運算結果作資料篩選）

E.g. 找出總人口數超過 1 億的洲

SELECT Continent
    FROM world.country
    GROUP BY Continent
    HAVING SUM(Population) > 100000000;

JOIN

是關聯式資料庫最重要的技巧！
聯結的種類：
#1. Inner Join：只會幫你把表格「交集」的觀測值保留
#2. Left Join：會幫你把左邊表格的觀測值全部保留
#3. Right Join：會幫你把右邊表格的觀測值全部保留
#4. Full Join（MySQL 不支援）

相關重要名詞

PK(Primary Key)：一個表格用來標註獨立觀測值的欄位
FK(Foreign Key)：用來與其他表格聯結的欄位

JOIN 語法結構

SELECT left_tbl.VARIABLE,
       right_tbl.VARIABLE
    FROM left_tbl
    JOIN right_tbl /*預設為 Inner Join: 將所有表格「交集」的觀測值傳回*/
    ON left_tbl.FK = right_tbl.FK;

JOIN 後面接欲聯結的表格名稱
ON 後面指定欲聯結表格中的參照欄位
指派表格名稱是進行聯結時的好習慣
檢查聯結前後表格觀測值個數也是好習慣

E.g.1 [內部連結 Inner Join] 將 country 表格與 city 表格的名稱水平合併。

SELECT Code, Name
    FROM world.country
    WHERE Code = 'TWN';
    
SELECT CountryCode, Name
    FROM world.city
    WHERE CountryCode = 'TWN';SELECT country.Name as country_name,
       city.Name as city_name
    FROM world.country
    JOIN world.city 
    ON country.Code = city.CountryCode
    WHERE country.Name = 'Taiwan';

E.g.2 [內部連結 Inner Join] 查詢 nba 資料庫中多倫多暴龍隊(raptors)的球員陣容顯示 personId、firstName 與 lastName

SELECT personID, urlName
    FROM nba.team_roster
    WHERE urlName = 'raptors';SELECT firstName, lastName, personID
    FROM nba.player_profile;SELECT team_roster.personID, firstName, lastName
    FROM nba.team_roster
    JOIN nba.player_profile
    ON team_roster.personID = player_profile.personID
    WHERE urlName = 'raptors';SELECT left_tbl.personId,
       right_tbl.firstName,
       right_tbl.lastName
    FROM (SELECT * 
              FROM nba.team_roster 
              WHERE urlName = 'raptors'
         ) left_tbl
    JOIN (SELECT * 
              FROM nba.player_profile
         ) right_tbl
    ON left_tbl.personId = right_tbl.personId;

E.g.3 [內部連結 Inner Join] 從nba.player_salary、nba.player_profile與 nba.player_career_summary 顯示 playerName、ppg、rpg、apg

SELECT salary.playerName,
       career_summary.ppg,
       career_summary.rpg,
       career_summary.apg
    FROM (SELECT * FROM nba.player_salary) salary
    JOIN (SELECT CONCAT(firstName, ' ', lastName) AS playerName,
                 personID          
              FROM nba.player_profile) profile
    ON salary.playerName = profile.playerName
    JOIN (SELECT * FROM nba.player_career_summary) career_summary
    ON profile.personId = career_summary.personId;

SQLite 資料庫

SQLite 是一個輕量化的 RDBMS

以 C 語言實踐
輕巧、快速、自我包含且功能完整
跨平台作業系統
自我包含（self-contained）：SQLite 將整個資料庫包裝為一個單獨、可跨平台使用的檔案
大量程式語言提供 SQLite 連結：C/C++、Python、R、PHP、Ruby on Rails、Java…etc.

基礎 SQLite 命令列指令

.database 檢視資料庫
.open 連結資料庫
.tables 檢視資料庫中的表格
.header on 檢視表格時顯示變數名稱
.mode column 檢視表格時比較漂亮
.shell cls 清空 SQLite 命令列
.exit 離開 SQLite 命令列

如何創建表格？

[方法ㄧ] 使用 .import 指令匯入 .csv 檔案建立表格

.mode csv
.import nba_teams.csv nba_teams

[方法二] 使用 CREATE TABLE 指令建立表格

Step1. 首先在資料夾下建立 create_table.sql 的檔案

/* create_table.sql: 
告訴系統你現在要建立的table名稱是 avenger，且裡面有兩個欄位：
分別是 avenger 字串 以及 character_name 字串 */
CREATE TABLE avengers (
    avenger TEXT,
    character_name TEXT
);

Step2. 執行 .read create_table.sql 先建立avenger這個table

Step3. 使用 INSERT INTO...VALUES... 指令輸入觀測值

/*insert_data.sql*/
INSERT INTO avengers (avenger, character_name)
VALUES ('Iron Man', 'Tony Stark');INSERT INTO avengers (avenger, character_name)
VALUES ('Captain America', 'Steve Rogers');INSERT INTO avengers (avenger, character_name)
VALUES ('Thor', 'Thor');INSERT INTO avengers (avenger, character_name)
VALUES ('Hulk', 'Bruce Banner');INSERT INTO avengers (avenger, character_name)
VALUES ('Black Widow', 'Natasha Romanoff');INSERT INTO avengers (avenger, character_name)
VALUES ('Hawkeye', 'Clint Barton');

Step4. 執行 .read insert_data.sql 把東西創建存入table

如何更新表格？

一開始我們加入錯誤的資料

INSERT INTO avengers (avenger, character_name)
VALUES ('Spider-Man', 'Scott Lang');

想要更新把 Scott Lang 改成 Peter Parker

UPDATE avengers
SET character_name = 'Peter Parker'
WHERE character_name = 'Scott Lang';

如何刪除觀測值？

使用 DELETE 刪除觀測值

DELETE FROM avengers
WHERE avenger = 'Spider-Man';

如何刪除表格？

使用 DROP TABLE table_name 刪除表格

DROP TABLE avengers;

如何將資料庫與Python連結？

使用套件進行連線與查詢

pymysql
sqlalchemy
pandas
mysqlclient

[CLICK ME] Reference Colab Link

Step1. 安裝套件

!sudo apt-get install python-dev default-libmysqlclient-dev
!pip install pymysql sqlalchemy mysqlclient

Step2. Import Package

import pymysql
from sqlalchemy import create_engine
import pandas as pd

Step3. 建立連線（四大要素：host, port, user, password）

host = "rsqltrain.ced04jhfjfgi.ap-northeast-1.rds.amazonaws.com"
port = 3306
user = xxx
passwd = yyy
dbname = "nba"
connection = create_engine('mysql+mysqldb://{}:{}@{}:{}/{}'.format(user, passwd, host, port, dbname))

Step4. 使用Pandas查詢表格 pd.read_sql_query(query_string, connection)

query_str = """
    SELECT *
        FROM nba_teams
        LIMIT 10;
"""
pd.read_sql_query(query_str, connection)

可得到你想要查找的資料

Udemy Course：https://www.udemy.com/course/sql4da-essentials/

This article will be updated at any time! Thanks for your reading. If you like the content, please click the “clap” button. You can also press the follow button to track new articles. Feel free to connect with me via LinkedIn or email.

[Notes] Database SQL 基本語法

Written by Haren Lin

No responses yet