fbpx
SQL 語法筆記
SQL

SQL 語法筆記

By May 7, 2020 September 25th, 2020 No Comments

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

1. 基本語法

1.1 創建資料表

CREATE TABLE

  1. CREATE TABLE table_name (
  2. column_1 data_type,
  3. column_2 data_type,
  4. column_3 data_type
  5. );
CREATE TABLE table_name (
   column_1 data_type, 
   column_2 data_type, 
   column_3 data_type
);
  1. CREATE TABLE celebs (
  2. id INTEGER,
  3. name TEXT,
  4. age INTEGER
  5. );
CREATE TABLE celebs (
    id INTEGER,
    name TEXT, 
    age INTEGER
);
  1. CREATE TABLE celebs (
  2. id INTEGER PRIMARY KEY,
  3. name TEXT UNIQUE,
  4. date_of_birth TEXT NOT NULL,
  5. date_of_death TEXT DEFAULT 'Not Applicable',
  6. );
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

  1. DROP DATABASE table_name;
DROP DATABASE table_name;

1.3 新增資料

INSERT INTO

  1. INSERT INTO table_name (id, name, age)
  2. VALUES (1, 'Justin Bieber', 21);
  4. SELECT * FROM table_name;
INSERT INTO table_name (id, name, age)
VALUES (1, 'Justin Bieber', 21);

SELECT * FROM table_name;

1.4資料表查詢

SELECT

  1. SELECT column_name FROM table_name;
SELECT column_name FROM table_name;
  1. SELECT * FROM table_name;
SELECT * FROM table_name;

1.5 更新資料

UPDATE

  1. UPDATE celebs
  2. SET age = 22
  3. WHERE id = 1;
UPDATE celebs
SET age = 22
WHERE id = 1;

1.6 改變資料表欄位

ALTER TABLE

  • 新增欄位
  1. ALTER TABLE celebs
  2. ADD COLUMN twitter_handle TEXT;
ALTER TABLE celebs 
ADD COLUMN twitter_handle TEXT;
  • 刪除欄位
  1. ALTER TABLE celebs
  2. DROP COLUMN twitter_handle;
ALTER TABLE celebs
DROP COLUMN twitter_handle;
  • 修改欄位
    • SQL Server / MS Access
      1. ALTER TABLE celebs
      2. ALTER COLUMN twitter_handle INTEGER;
      ALTER TABLE celebs
ALTER COLUMN twitter_handle INTEGER;
    • My SQL / Oracle (prior version 10G)
      1. ALTER TABLE celebs
      2. MODIFY COLUMN twitter_handle INTEGER;
      ALTER TABLE celebs
MODIFY COLUMN twitter_handle INTEGER;
    • Oracle 10G and later
      1. ALTER TABLE celebs
      2. MODIFY twitter_handle INTEGER;
      ALTER TABLE celebs
MODIFY twitter_handle INTEGER;

1.7 刪除資料

DELETE FROM

  1. DELETE FROM celebs
  2. WHERE twitter_handle IS NULL;
DELETE FROM celebs
WHERE twitter_handle IS NULL;

2. 進階查詢語法

2.1 SELECT

查詢結果顯示 name, genre, year

  1. SELECT name, genre, year
  2. FROM movies;
SELECT name, genre, year
FROM movies;

2.2 AS

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

  1. SELECT imdb_rating AS 'IMDb'
  2. FROM movies;
SELECT imdb_rating AS 'IMDb' 
FROM movies;

2.3 SELECT DISTINCT

去掉的重複的查詢結果

  • 去掉 year 重複的結果
    1. SELECT DISTINCT year
    2. FROM movies;
    SELECT DISTINCT year
FROM movies;

     

  • 去掉 year 和 name 都重複的結果
    1. SELECT DISTINCT year, name
    2. FROM movies;
    SELECT DISTINCT year, name
FROM movies;

2.4 WHERE

篩選資料用的

  1. SELECT *
  2. FROM movies
  3. WHERE year > 2014;
SELECT *
FROM movies
WHERE year > 2014;

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

2.5 LIKE

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

  1. SELECT *
  2. FROM movies
  3. WHERE name LIKE 'Se_en';
SELECT *
FROM movies
WHERE name LIKE 'Se_en';

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

  1. SELECT *
  2. FROM movies
  3. WHERE name LIKE 'A%';
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 的資料
    1. SELECT name
    2. FROM movies
    3. WHERE imdb_rating IS NOT NULL;
    SELECT name
FROM movies 
WHERE imdb_rating IS NOT NULL;

     

2.7 BETWEEN

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

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

  1. SELECT *
  2. FROM movies
  3. WHERE name BETWEEN 'A' AND 'D';
SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'D';

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

  1. SELECT *
  2. FROM movies
  3. WHERE year BETWEEN 1990 AND 1999;
SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

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

2.8 AND

  1. SELECT *
  2. FROM movies
  3. WHERE year BETWEEN 1990 AND 1999
  4. AND genre = 'romance';
SELECT * 
FROM movies
WHERE year BETWEEN 1990 AND 1999
   AND genre = 'romance';

2.9 OR

  1. SELECT *
  2. FROM movies
  3. WHERE genre = 'romance'
  4. OR genre = 'comedy';
SELECT *
FROM movies
WHERE genre = 'romance'
  OR genre = 'comedy';

2.10 ORDER BY

用來排序結果

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

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

2.11 LIMIT

用來限制查詢結果數量

  1. SELECT *
  2. FROM movies
  3. ORDER BY imdb_rating DESC
  4. LIMIT 3;
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 來簡稱該條件的欄位名稱
  1. SELECT name,
  2. CASE
  3. WHEN genre = 'romance' THEN 'Chill'
  4. WHEN genre = 'comedy' THEN 'Chill'
  5. ELSE 'Intense'
  6. END AS 'Mood'
  7. FROM movies;
SELECT name,
    CASE
        WHEN genre = 'romance' THEN 'Chill'
        WHEN genre = 'comedy' THEN 'Chill'
        ELSE 'Intense'
    END AS 'Mood'
FROM movies;

3. 計算函式

3.1 Count

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

  1. SELECT COUNT(*)
  2. FROM fake_apps
  3. WHERE price = 0;
SELECT COUNT(*) 
FROM fake_apps
WHERE price = 0;

3.2 SUM

加總指定欄位的值

  1. SELECT SUM(downloads)
  2. FROM fake_apps;
SELECT SUM(downloads)
FROM fake_apps;

3.3 MAX / MIN

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

  1. SELECT MIN(downloads)
  2. FROM fake_apps;
SELECT MIN(downloads)
FROM fake_apps;

3.4 AVERAGE

求指定欄位的平均值

  1. SELECT AVG(price)
  2. FROM fake_apps;
SELECT AVG(price)
FROM fake_apps;

3.5 ROUND

將指定資料四捨五入

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

4. 彙總函式

4.1 GROUP BY

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

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

  1. SELECT category, SUM(downloads)
  2. FROM fake_apps
  3. GROUP BY category;
SELECT category, SUM(downloads) 
FROM fake_apps
GROUP BY category;

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

  1. SELECT category,
  2. price,
  3. AVG(downloads)
  4. FROM fake_apps
  5. GROUP BY 1, 2;
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 之前
:::

  1. SELECT price,
  2. ROUND(AVG(downloads)),
  3. COUNT(*)
  4. FROM fake_apps
  5. GROUP BY price
  6. HAVING COUNT(*) > 10;
SELECT price, 
   ROUND(AVG(downloads)),
   COUNT(*)
FROM fake_apps
GROUP BY price
HAVING COUNT(*) > 10;

5. 表格連接

5.1 JOIN

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

連接後會自動補 NULL

  1. SELECT *
  2. FROM orders
  3. JOIN customers
  4. ON orders.customer_id = customers.customer_id;
SELECT *
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id;

5.2 INNER JOIN

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

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

  1. SELECT COUNT(*)
  2. FROM newspaper
  3. JOIN online
  4. ON newspaper.id = online.id;
SELECT COUNT(*)
FROM newspaper
JOIN online
ON newspaper.id = online.id;

文章出處：BlackMaple’s Blog

 

Facebook Comments

About Post Author

Avatar

tina

See author's posts

Leave a Reply