SQL 語法筆記

本文會介紹SQL 基本語法, 進階查詢, 計算函式, 彙總函式 和表格連接的語法。

1. 基本語法

1.1 創建資料表

CREATE TABLE

CREATE TABLE table_name (
   column_1 data_type, 
   column_2 data_type, 
   column_3 data_type
);

CREATE TABLE celebs (
    id INTEGER,
    name TEXT, 
    age INTEGER
);

CREATE TABLE celebs (
   id INTEGER PRIMARY KEY, 
   name TEXT UNIQUE,
   date_of_birth TEXT NOT NULL,
   date_of_death TEXT DEFAULT 'Not Applicable',
);

1. PRIMARY KEY：可用於資料的唯一識別，不能重複，一個資料只能有一個欄位是 PRIMARY KEY。
2. UNIQUE：與 PRIMARY KEY 很像，不過它可以設置在一個資料的多個欄位
3. NOT NULL：強制該欄位一定要給定值
4. DEFAULT：如果欄位沒有值，會自動定預設值

1.2 刪除資料表

DROP DATABASE

DROP DATABASE table_name;

1.3 新增資料

INSERT INTO

INSERT INTO table_name (id, name, age)
VALUES (1, 'Justin Bieber', 21);

SELECT * FROM table_name;

1.4資料表查詢

SELECT

SELECT column_name FROM table_name;

SELECT * FROM table_name;

1.5 更新資料

UPDATE

UPDATE celebs
SET age = 22
WHERE id = 1;

1.6 改變資料表欄位

ALTER TABLE

• 新增欄位

ALTER TABLE celebs 
ADD COLUMN twitter_handle TEXT;

• 刪除欄位

ALTER TABLE celebs
DROP COLUMN twitter_handle;

• 修改欄位
  • SQL Server / MS Access

    ALTER TABLE celebs
    ALTER COLUMN twitter_handle INTEGER;

  • My SQL / Oracle (prior version 10G)

    ALTER TABLE celebs
    MODIFY COLUMN twitter_handle INTEGER;

  • Oracle 10G and later

    ALTER TABLE celebs
    MODIFY twitter_handle INTEGER;

1.7 刪除資料

DELETE FROM

DELETE FROM celebs
WHERE twitter_handle IS NULL;

2. 進階查詢語法

2.1 SELECT

查詢結果顯示 name, genre, year

SELECT name, genre, year
FROM movies;

2.2 AS

可以改變查詢結果的欄位名稱，但不會改掉資料表裡的欄位

SELECT imdb_rating AS 'IMDb' 
FROM movies;

2.3 SELECT DISTINCT

去掉的重複的查詢結果

• 去掉 year 重複的結果

  SELECT DISTINCT year
  FROM movies;

   

• 去掉 year 和 name 都重複的結果

  SELECT DISTINCT year, name
  FROM movies;

2.4 WHERE

篩選資料用的

SELECT *
FROM movies
WHERE year > 2014;

:::danger
「等於」要特別注意，只有一個等號

2.5 LIKE

可以在 WHERE 子句中使用 LIKE 運算子來進行模糊搜尋

SELECT *
FROM movies
WHERE name LIKE 'Se_en';

:::info
_ 代表任意字元
:::

SELECT * 
FROM movies
WHERE name LIKE 'A%';

• A% 代表由 A 開頭
• %A 代表由 A 結尾
• %man% 代表所有含有 man 的字串

2.6 NULL

NULL 不能拿來用運算子進行比較，因此出現 IS NULL、IS NOT NULL 來判斷是否是 NULL。

• 顯示具有 imdb_rating 的資料

  SELECT name
  FROM movies 
  WHERE imdb_rating IS NOT NULL;

   

2.7 BETWEEN

可以在 WHERE 子句中使用 BETWEEN 運算子來篩選出特定範圍區間得值

篩選出 name 開頭為 ‘A’、‘B’、‘C’ 的結果

SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'D';

篩選出 year 在 1990 ~ 1999 區間的結果

SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

:::warning
BETWEEN 兩個字串，會不包含結尾
BETWEEN 兩個數字，會包含結尾的數字
:::

2.8 AND

SELECT * 
FROM movies
WHERE year BETWEEN 1990 AND 1999
   AND genre = 'romance';

2.9 OR

SELECT *
FROM movies
WHERE genre = 'romance'
  OR genre = 'comedy';

2.10 ORDER BY

用來排序結果

:::warning
如果 WHERE 存在，ORDER BY 永遠跟在 WHERE 後面
:::

• DESC：從大到小排序
• ASC：從小到大排序（預設）

2.11 LIMIT

用來限制查詢結果數量

SELECT *
FROM movies
ORDER BY imdb_rating DESC
LIMIT 3;

:::warning
永遠放在查詢語法最後面
:::

:::info
不是所有 RDBMS（關聯式資料庫管理系統） 都有
:::

2.12 CASE

SQL 中的判斷式，可以用在顯示結果加上新欄位

:::info
CASE 通常在 SELECT 語句中
:::

• WHEN 後面接著條件式，如果條件式為真，會返回 THEN 後面的字串
• ELSE 會在前面條件式都不符合的時候，返回一個字串
• CASE 結尾一定要有一個 END
• 可以在 END 後面接 AS 來簡稱該條件的欄位名稱

SELECT name,
    CASE
        WHEN genre = 'romance' THEN 'Chill'
        WHEN genre = 'comedy' THEN 'Chill'
        ELSE 'Intense'
    END AS 'Mood'
FROM movies;

3. 計算函式

3.1 Count

計算指定欄位非空資料的總數

SELECT COUNT(*) 
FROM fake_apps
WHERE price = 0;

3.2 SUM

加總指定欄位的值

SELECT SUM(downloads)
FROM fake_apps;

3.3 MAX / MIN

求指定欄位的最大或最小值

SELECT MIN(downloads)
FROM fake_apps;

3.4 AVERAGE

求指定欄位的平均值

SELECT AVG(price)
FROM fake_apps;

3.5 ROUND

將指定資料四捨五入

SELECT ROUND(AVG(price), 2)
FROM fake_apps;

4. 彙總函式

4.1 GROUP BY

用來搭配 SELECT 語句，將指定欄位中，相同資料的放在一組，以便計算函式以組為單位進行計算

:::info
GROUP BY 會出現在 WHERE 之後，ORDER BY 和 LIMIT 之前
:::

SELECT category, SUM(downloads) 
FROM fake_apps
GROUP BY category;

GROUP BY 後面的參數可以接數字，從 1 開始，分別代表 SELECT 後面的第幾個值

SELECT category, 
   price,
   AVG(downloads)
FROM fake_apps
GROUP BY 1, 2;

以上面的例子來說，GROUP BY 後面的 1、2 分別指向 category 和 price

4.2 HAVING

與 WHERE 類似，不過他是針對 Group

:::info
HAVING 會出現在 GROUP BY 之後，ORDER BY 和 LIMIT 之前
:::

SELECT price, 
   ROUND(AVG(downloads)),
   COUNT(*)
FROM fake_apps
GROUP BY price
HAVING COUNT(*) > 10;

5. 表格連接

5.1 JOIN

使用方法：JOIN 表格名稱 ON 連接欄位規則

連接後會自動補 NULL

SELECT *
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id;

5.2 INNER JOIN

使用方法：INNER JOIN 表格名稱 ON 連接欄位規則

會自動將不符合連接欄位規則的部分捨棄，如下圖

SELECT COUNT(*)
FROM newspaper
JOIN online
ON newspaper.id = online.id;

文章出處：BlackMaple’s Blog