[SQL] 語法與基本操作

資訊

py4e - Using Databases

SQL 練習與教學網站

五大鍵

Super Key：一個或多個欄位的組合，可以唯一識別一筆資料。
Candidate Key：最小的 Super Key，也就是最少欄位的組合，可以唯一識別一筆資料。
Primary Key：Candidate Key 中選擇一個當作主鍵，並且不可重複。
Alternate Key：除了 Primary Key 以外的 Candidate Key。
Foreign Key：另一個資料表的 Primary Key，用來建立資料表之間的關聯。

建立資料庫

建立資料庫，關鍵字是 CREATE。
設定字元集。
定序，即決定了字元該如何被排序。

CREATE DATABASE myDatabase
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

資訊

刪除資料庫：

DROP DATABASE myDatabase;

建立資料表

建立一個資料表，必須要規範：

欄位名稱。
資料型態。
必填與否。
預設值。
哪個欄位要做為主鍵。

Sample
CREATE TABLE [table_name](
  [column1_name] [data_type] [not_null] [default],
  [column2_name] [data_type] [not_null] [default],
  [column3_name] [data_type] [not_null] [default],
  PRIMARY KEY ([column_name])
);

提示

可以用 Online SQL Generator 來產生 code

先簡單創一個飲料表單：

drinks
CREATE TABLE `drinks` (
  `id`    int(11)      unsigned NOT NULL AUTO_INCREMENT,
  `name`  varchar(20)           NOT NULL,
  `price` int(11)      unsigned NOT NULL,
  `cost`  int(11)      unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

資訊

資料型態	說明
int	整數
float	浮點數
char	固定長度字串
varchar	可變長度字串
text	長字串
date	日期
time	時間
datetime	日期時間
timestamp	時間戳記

其他如 unsigned、not null、auto_increment 等是資料型態的修飾詞，分別代表非負整數、不可為空、自動增加。

資料表的調整

若要在建立好的表格上做調整，可以使用 ALTER TABLE。
舉例來說，我們要新增一個欄位 category 來記錄飲料的類別：

ALTER TABLE drinks ADD (category varchar(20) NOT NULL);

反之若要調整刪除欄位，則使用 DROP：

ALTER TABLE drinks DROP category;

SQL 的 CRUD

新增資料

新增資料會用到 INSERT INTO 指定要新增資料的資料表及欄位，並透過 VALUES 賦值。

INSERT INTO `drinks` (`name`, `price`, `cost`)
VALUES ('阿華田', 65, 20),
('百香紅茶', 45, 10),
('四季春茶', 25, 5),
('愛玉冰茶', 50, 7),
('冰咖啡', 70, 30);

查詢資料

查詢資料是用 SELECT 撈資料。

SELECT * FROM `drinks`;

修改資料

使用 UPDATE 來更新資料，並透過 WHERE 來限制指令的作用對象。

UPDATE `drinks`
SET `price` = 90
WHERE `name` = '冰咖啡';

刪除資料

資訊

刪除表格中所有內容，使用 TRUNCATE：

TRUNCATE TABLE `drinks`;

DELETE FROM `drinks`
WHERE `name` = '阿華田';

SQL 運算子

前置準備，先準備一組 SQL 資料

customers
CREATE TABLE `customers` (
	`id` INT unsigned NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	`phone` VARCHAR(20) NOT NULL,
	`birth` DATE NOT NULL,
	PRIMARY KEY (`id`)
);

INSERT INTO `customers`(`name`, `phone`, `birth`)
VALUES ('Johny', '0980123123', '1997-04-22'),
('Wendy', '0919456456', '1999-07-07');

SELECT * FROM `customers`;

orders
CREATE TABLE `orders` (
    `id` INT unsigned NOT NULL AUTO_INCREMENT,
    `drinks_id` INT(11) unsigned NOT NULL,
    `customers_id` INT(11) unsigned NOT NULL,
    `amounts` INT(11) unsigned NOT NULL,
    `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

INSERT INTO `orders`(`drinks_id`, `customers_id`, `amounts`)
VALUES (1, 2, 4),
(2, 2, 3),
(3, 2, 9),
(3, 1, 2);

SELECT * FROM `orders`;

運算子有哪些？

計算運算子：+、-、*、/、%
比較運算子：=、<> (不等於)、>、<、>=、<=
邏輯運算子： AND、OR、BETWEEN、LIKE

運算子實戰

假設現在飲料店要因應活動，全體品項要打 8 折，第一個直覺應該是用 UPDATE 把價格全部改掉：

ver.update
UPDATE `drinks`
SET `price` = `price` * 0.8;

但這樣事直接對資料做更改，並不是最佳解。最好的選擇是多創一個欄位來記錄折扣價格：

best solution
SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`;

運算子實戰 - 條件限制

只有單價 30 以上的才可以打8折，使用 WHERE 配合運算子：

SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`
WHERE `price` >= 30;

雙條件：50 以上不打折。

// 寫法1
SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`
WHERE `price` >= 30 AND `price` <= 50;

// 寫法2
SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`
WHERE `price` BETWEEN 30 AND 50;

運算子實戰 - 關鍵字搜尋

名字帶茶的飲料才打8折，使用 WHERE 配合運算子 LIKE：

SELECT `name`, `price`, (`price` * 0.8) AS `discount_price`
FROM `drinks`
WHERE `name` LIKE '%茶%';

提示

%茶% 表示要找前面有任意字元的茶和後面有任意字元的茶。同理，我們要找以茶開頭的飲料可以寫 茶%。

SQL 內建的函式

SUM

今天一共賣了幾杯飲料？

SELECT SUM(`amounts`) AS `total_sold`
FROM `orders`;

AVG

一單平均賣幾杯？

SELECT AVG(`amounts`) AS `average_sold`
FROM `orders`;

GROUP BY

每個飲料品項總共賣多少杯？

SELECT `drinks_id`, SUM(`amounts`) as total_sold
FROM `orders`
GROUP BY `drinks_id`;

COUNT

今天各種飲料項目的熱門程度 (出現次數)？

SELECT `drinks_id`, COUNT(`amounts`) as num_of_orders
FROM `orders`
GROUP BY `drinks_id`;

HAVING

銷售量超過10的的飲料項目？

危險

直覺會使用 WHERE，但儲存總量的 total_sold 欄位是用函式建的， WHERE 無法篩選

SELECT `drinks_id`, SUM(`amounts`) as total_sold
FROM `orders`
GROUP BY `drinks_id`
HAVING `total_sold` > 10;

JOIN

已知在 orders 資料表裡記錄了每個飲料品項的販賣情況，但這個資料表中只記錄了飲料的 drinks_id，並沒有飲料的詳細名稱，詳細名稱是記錄在 drinks 資料表裡面，所以會需要用 JOIN 來幫忙兩個資料表做連結。

INNER JOIN

查尋兩邊資料表都有的資料，比方說，現在要把 orders 的 drinks_id 對回 drinks 的 id 來找出飲料名稱：

SELECT orders.`drinks_id`, orders.`amounts`, drinks.`name`
FROM drinks
JOIN orders
ON drinks.`id` = orders.`drinks_id`;

JOIN 的重點在於：

SELECT，反正查詢一定要有 SELECT。
FROM 是原本的資料表。
JOIN 是要加入查詢的資料表。
ON 則是參考點。

LEFT JOIN 和 RIGHT JOIN

INNER JOIN 只列出了兩邊資料相符的部分，但如果今天想把參考點對應不到的資料也一併呈現呢？比方說還是想列出所有飲料名稱怎麼辦，那就是使用 LEFT JOIN 和 RIGHT JOIN。

提示

LEFT 指的是 FROM 後面的資料表，RIGHT 指的是 JOIN 後面的資料表

-- LEFT JOIN
SELECT orders.`drinks_id`, orders.`amounts`, drinks.`name`
FROM drinks
LEFT JOIN orders
ON drinks.`id` = orders.`drinks_id`;

-- RIGHT JOIN
SELECT orders.`drinks_id`, orders.`amounts`, drinks.`name`
FROM orders
RIGHT JOIN drinks
ON drinks.`id` = orders.`drinks_id`;

SELF JOIN

通常用在同一張資料表做比較的狀況。比方說，在 drinks 中找比冰咖啡便宜的飲料：

SELECT d1.`name`, d2.`name` AS `cheaper_name`, d2.price
FROM drinks AS d1
JOIN drinks AS d2
ON d1.price > d2.price
WHERE d1.`name` = '冰咖啡';

綜合練習

哪項飲料銷售量最高？

SELECT drinks.`name`, SUM(orders.`amounts`) AS `total`
FROM drinks
JOIN orders
ON drinks.`id` = orders.`drinks_id`
GROUP BY orders.`drinks_id`
ORDER BY `total` DESC
LIMIT 1;

誰是購買額最高的顧客？

SELECT customers.`name`, SUM(orders.`amounts` * drinks.`price`) AS `revenue`
FROM orders
JOIN customers
ON customers.`id` = orders.`customers_id` 
JOIN drinks
ON drinks.`id` = orders.`drinks_id`
GROUP BY customers.`id`
ORDER BY `revenue` DESC
LIMIT 1;

SQL Transaction (交易)

SQL 作為操縱資料的語言，有時難免會遇到複數資料操縱必須全部執行完成，否則乾脆不要執行的時候，比如說：轉帳時 A、B 帳戶的資料。

若是今天 A 轉了錢給 B，那他們的帳戶金額資料勢必得更新。但當 A 更新完後，程式突然崩潰了，B 沒更新到帳戶資料，這樣算有入帳還是沒入帳？所以會需要交易來幫助管理需要全部執行完畢的步驟來避免這種情形。

交易的基本原則就是：

SQL 指令執行皆正確，則執行 COMMIT 完成異動，相關資料表更動。
SQL 指令執行出任何一個錯，則執行 'ROLLBACK` 取消異動。

轉帳範例
BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

-- 提交事務
COMMIT;

Reference

Alpha Camp
Meta Back-End Developer Professional Certificate
Database Transaction & ACID

SQL 練習與教學網站​

五大鍵​

建立資料庫​

建立資料表​

資料表的調整​

SQL 的 CRUD​

新增資料​

查詢資料​

修改資料​

刪除資料​

SQL 運算子​

前置準備，先準備一組 SQL 資料​

運算子有哪些？​

運算子實戰​

運算子實戰 - 條件限制​

運算子實戰 - 關鍵字搜尋​

SQL 內建的函式​

SUM​

AVG​

GROUP BY​

COUNT​

HAVING​

JOIN​

INNER JOIN​

LEFT JOIN 和 RIGHT JOIN​

SELF JOIN​

綜合練習​

SQL Transaction (交易)​

Reference​