2014 Winter Olympic Games

儲存資料

• 以 table 為單位
• 由 row 和 column 所組成
• column 設定好後就不會輕易更改
• row 可以無限增加

CREATE TABLE

建立表格

表格或欄位名稱

Table 和 Cloumn 的名稱允許以下格式

• 大小寫英文字母
• 底線 _ 和 $
• Unicode

  U+0080 ~ U+FFFF

• 建議只用 「小寫字母」 和 「底線」

CREATE TABLE employee (
    employee_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(30) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    notes MEDIUMTEXT,
    PRIMARY KEY (employee_id),
    INDEX (last_name),
    UNIQUE (email)
)
ENGINE=InnoDB;

CREATE TABLE address (
    employee_id INTEGER UNSIGNED NOT NULL,
    address VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state CHAR(2) NOT NULL,
    postcode CHAR(5) NOT NULL,
    FOREIGN KEY (employee_id)
    REFERENCES employee (employee_id)
)
ENGINE=InnoDB;

主鍵 (Primary Key)

• 每一筆資料必須是表格中的唯一值
• 用來獨一無二地確認表格中某行資料
• 可以普通欄位或是一個公式
• 可以包含一或多個欄位

• 不能為 NULL

CREATE TABLE employee (
    employee_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(30) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    notes MEDIUMTEXT,
    PRIMARY KEY (employee_id),
    INDEX (last_name),
    UNIQUE (email)
)
ENGINE=InnoDB;

CREATE TABLE address (
    employee_id INTEGER UNSIGNED NOT NULL,
    address VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state CHAR(2) NOT NULL,
    postcode CHAR(5) NOT NULL,
    FOREIGN KEY (employee_id)
    REFERENCES employee (employee_id)
)
ENGINE=InnoDB;

AUTO_INCREMENT

• 新增時沒有指定就自動遞增
• 只能和主鍵一起使用
• 只能用在正整數

CREATE TABLE employee (
    employee_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(30) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    notes MEDIUMTEXT,
    PRIMARY KEY (employee_id),
    INDEX (last_name),
    UNIQUE (email)
)
ENGINE=InnoDB;

CREATE TABLE address (
    employee_id INTEGER UNSIGNED NOT NULL,
    address VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state CHAR(2) NOT NULL,
    postcode CHAR(5) NOT NULL,
    FOREIGN KEY (employee_id)
    REFERENCES employee (employee_id)
)
ENGINE=InnoDB;

NULL / NOT NULL

• 設定欄位是否允許 NULL
• NULL 值是 MySQL 的一個特殊的值，代表什麼都沒有
• NULL 不好使用，所以通常建議把欄位設定為 NOT NULL

CREATE TABLE employee (
    employee_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(30) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    notes MEDIUMTEXT,
    PRIMARY KEY (employee_id),
    INDEX (last_name),
    UNIQUE (email)
)
ENGINE=InnoDB;

CREATE TABLE address (
    employee_id INTEGER UNSIGNED NOT NULL,
    address VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state CHAR(2) NOT NULL,
    postcode CHAR(5) NOT NULL,
    FOREIGN KEY (employee_id)
    REFERENCES employee (employee_id)
)
ENGINE=InnoDB;

INDEX

• 建立索引可加速搜尋
• 如果有需要對某個欄位搜尋務必記得加上索引
• 不要過於濫用
  建過多索引也會浪費效能

CREATE TABLE employee (
    employee_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(30) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    notes MEDIUMTEXT,
    PRIMARY KEY (employee_id),
    INDEX (last_name),
    UNIQUE (email)
)
ENGINE=InnoDB;

CREATE TABLE address (
    employee_id INTEGER UNSIGNED NOT NULL,
    address VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state CHAR(2) NOT NULL,
    postcode CHAR(5) NOT NULL,
    FOREIGN KEY (employee_id)
    REFERENCES employee (employee_id)
)
ENGINE=InnoDB;

UNIQUE

• 和 INDEX 差別在多了資料不能重複的限制 (NULL 不會算重複)
• 主鍵和 UNIQUE 差別在 UNIQUE 可以存 NULL 值

CREATE TABLE employee (
    employee_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(30) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    notes MEDIUMTEXT,
    PRIMARY KEY (employee_id),
    INDEX (last_name),
    UNIQUE (email)
)
ENGINE=InnoDB;

CREATE TABLE address (
    employee_id INTEGER UNSIGNED NOT NULL,
    address VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state CHAR(2) NOT NULL,
    postcode CHAR(5) NOT NULL,
    FOREIGN KEY (employee_id)
    REFERENCES employee (employee_id)
)
ENGINE=InnoDB;

FOREIGN KEY

• 代表和另外一個表格的欄位有關聯
• 兩個欄位的各項屬性必須一樣 (名字可以不同)
• 只能有在另外一個表格中存在的資料

CREATE TABLE employee (
    employee_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(30) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    notes MEDIUMTEXT,
    PRIMARY KEY (employee_id),
    INDEX (last_name),
    UNIQUE (email)
)
ENGINE=InnoDB;

CREATE TABLE address (
    employee_id INTEGER UNSIGNED NOT NULL,
    address VARCHAR(50) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state CHAR(2) NOT NULL,
    postcode CHAR(5) NOT NULL,
    FOREIGN KEY (employee_id)
    REFERENCES employee (employee_id)
)
ENGINE=InnoDB;

DATA TYPES

資料型態

整數

浮點數

CHAR

• 適合建立索引
• 適合儲存可見字元
• 可以設定字元集
• 建議設定字元集為 utf8mb4

BINARY

• 適合建立索引
• 適合儲存二進位資料
• 不能設定字元集

TEXT

• 不適合建立索引
• 適合儲存可見字元
• 可以設定字元集
• 建議設定字元集為 utf8mb4

BLOB

• 不適合建立索引
• 適合儲存二進位資料
• 不能設定字元集

其他字串型態

時間

STORAGE ENGINES

儲存引擎

InnoDB 和 MyISAM 比較

INSERT DATA

新增資料

INSERT

INSERT INTO employee
    (first_name, last_name, email, hire_date)
VALUES
    ('Nischal', 'Bhatia', 'nbhatia@example.com', '2014-12-15');

• 省略 employee_id (AUTO_INCREMENT)
• 省略 notes (允許 NULL)
• 字串型態的資料要用單引號或雙引號均可
  • 建議使用單引號
  • 使用反斜線 \ 作為跳脫字元

SELECT * FROM employee;

INSERT (省略欄位名稱)

INSERT INTO employee VALUES
    (NULL, 'Bhatia', 'Nischal', 'nbhatia@example.com', '2014-12-15', NULL);

• 不可省略欄位
• 欄位順序必須和 CREATE 時一樣

RETRIEVING DATA

取得資料

sakila database

• 這是 MySQL 提供的範例資料庫之一
• 下載網址＆安裝教學
• Unix-like 系統建議下載 tgz
• Windows 系統建議下載 zip
• 先匯入 sakila-schema.sql
• 再匯入 sakila-data.sql

USE sakila;

SOURCE C:/temp/sakila-db/sakila-schema.sql;
SOURCE C:/temp/sakila-db/sakila-data.sql;

SELECT 語法

SELECT last_name, first_name
FROM actor;

ORDER

SELECT last_name, first_name
FROM actor
ORDER BY last_name;

多個 ORDER

SELECT last_name, first_name
FROM actor
ORDER BY last_name ASC, first_name DESC;

LIMIT

SELECT last_name, first_name
FROM actor
ORDER BY last_name ASC, first_name DESC
LIMIT 5;

SELECT 語法順序

以上的 SELECT 語法順序是很重要的，如果不確定該怎麼下指令，可以參考官方文件。

WHERE

過濾資料

WHERE 語法

SELECT title, length, rating
FROM film
WHERE length < 60
ORDER BY title;

AND

SELECT title, length, rating
FROM film
WHERE length >= 60 AND length <= 120
ORDER BY title;

OR

SELECT title, length, rating
FROM film
WHERE length = 60 OR length = 120 OR length = 180
ORDER BY title;

LIKE

SELECT title, length, rating
FROM film
WHERE title LIKE '%GAME%'
ORDER BY title;

WHERE 可用的條件式

AGGREGATE FUNCTIONS AND GROUPING

彙總函式與集合

MAX

SELECT MAX(amount)
FROM payment;

COUNT

SELECT COUNT(payment_id)
FROM payment;

SUM AND GROUP

SELECT customer_id, SUM(amount) AS amt
FROM payment
GROUP BY customer_id
ORDER BY amt DESC
LIMIT 10;

UPDATING DATA

更新資料

UPDATE

SELECT customer_id, first_name, last_name
FROM customer
WHERE first_name = 'Courtney' AND last_name = 'Day';

UPDATE customer
SET last_name = 'DAY-WEBB'
WHERE customer_id = 245;

SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id = 245;

DELETING DATA

刪除資料

SOFT DELETE

• 多一個欄位紀錄刪除狀態
• 刪除時使用UPDATE更新該欄位
• 避免真的刪除資料
• 使用索引及WHERE找出未刪除資料
• 使用temporal triggers定期刪除

DELETE

• 使用刪除語法務必慎重
• 建議先用SELECT確認WHERE沒設定錯
  • 忘記加WHERE可是會災情慘重的

DELETE FROM customer WHERE customer_id = 245;

FORRIGN KEY AND DELETE

ERROR 1451 (23000): Cannot delete or update a parent
row: a foreign key constraint fails (`sakila`.`payment`,
CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`)
REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE)

• FOREIGN KEY 預設在有相依資料時無法刪除
• 使用 ON DELETE CASCADE (請自行 Google)
• 先將相依資料刪除

DELETE FROM payment WHERE customer_id = 245;
DELETE FROM rental WHERE customer_id = 245;
DELETE FROM customer WHERE customer_id = 245;

TRANSACTIONS

事務

今天 A 要轉帳給 B

1. 檢查 A 的餘額
2. 從 A 的帳戶扣錢
3. 從 B 的帳戶加錢

今天 A 要轉帳給 B

1. 檢查 A 的餘額
2. 從 A 的帳戶扣錢
3. 從 B 的帳戶加錢

A 同時收到了 X 和 Y 轉帳

X1. 取得餘額 A

X2. 將 A+X 寫回去

Y1. 取得餘額 A + X

Y2. 將 A+X+Y 寫回去

A 同時收到了 X 和 Y 轉帳

X1. 取得餘額 A

Y1. 取得餘額 A

X2. 將 A+X 寫回去

Y2. 將 A+Y 寫回去

今天 A 要轉帳給 B

START TRANSACTION;

SELECT * FROM account WHERE name IN ('A', 'B') FOR UPDATE;

-- 如果餘額不足就需要透過程式執行 ROLLBACK
ROLLBACK;

START TRANSACTION;

SELECT * FROM account WHERE name IN ('A', 'B') FOR UPDATE;

-- 如果餘額足夠就開始進行轉帳
UPDATE account SET balance = balance - 10 WHERE name = 'A';
UPDATE account SET balance = balance + 10 WHERE name = 'B';

COMMIT;

ACID

• 原子性（atomicity，或稱不可分割性）
• 一致性（consistency）
• 隔離性（isolation，又稱獨立性）
• 持久性（durability）

資料庫管理系統（DBMS）在寫入/更新資料的過程中，為保證事務（transaction）是正確可靠的，所必須具備的四個特性

JOINING TABLE

連接資料表

沒有 JOIN 的時候

如何找出前五名演出最多電影的演員？

SELECT actor_id, COUNT(actor_id) AS appearances
FROM film_actor
GROUP BY actor_id
ORDER BY appearances DESC
LIMIT 5;

SELECT actor_id, first_name, last_name
FROM actor
WHERE actor_id IN (107, 102, 198, 181, 23);

有 JOIN 的時候

如何找出前五名演出最多電影的演員？

SELECT
    a.first_name, a.last_name,
    COUNT(fa.film_id) AS appearance_count
FROM
    film_actor AS fa
    JOIN actor AS a ON a.actor_id = fa.actor_id
GROUP BY
    fa.actor_id
ORDER BY
    appearance_count DESC
LIMIT 5;

TYPES OF JOINS

JOIN 的種類

也想要計算沒有演出電影的演員時

與 LEFT JOIN 功能相同