---
tags: 學習筆記, DB
---
SQL 基本
======
## SQL 是什麼
幫助我們記憶許多的資料，如果不希望遺失任何資訊，SQL 會成為我們的最佳選擇。
SQL 可以幫我們把資料做好分類並適當的保存，就像是表格一樣。
|last_name|first_name|email|
|:---:|:---:|:---:|
|Branson|Ann|annie@boards-r-us.com|
|Hamilton|Jamie|dontbother@breakneckpizza.com|
|Soukup|Alan|soukup@breakneckpizza.com|
### 與 Excel 的不同
除了提供空間幫助我們儲存資料外，他也為這些 SQL structure 或 table 做了許多關聯以便操作。
### 名詞釋義
- database： 資料庫
- table：資料表，一個資料庫可以有許多資料表就像是上面的 table 一樣紀錄許多類似內容的資料。
- row：經常與 record 一起被提及
### 如何使用資料庫
使用 SQL 指令輸入命令使用，以下皆為 MySQL 語法。
### 建立資料庫
資料庫命名不得有空白
```sql
CREATE DATABASE myDataBase;
```
### 使用資料庫
透過 use 指令我們可以指定要使用哪個資料庫來操作。
```sql
USE mydataBase;
```
### 建立資料表
透過 CREATE TABLE 可以建立資料表並定義要記錄的資料欄位以及資料型態。
```sql
CREATE TABLE person
(
firstName VARCHAR(10),
lastName VARCHAR(10),
email VARCHAR(20)
);
```
### 常見形態
- VARCHAR：文字，透過(數字)表達允許儲存多長的內容。
- CHAR：與 VARCHAR 主要差異在於 CHAR 所限制的長度是固定的，通常適用於該欄位資料格式一致。
- INT
- DATE
- TIME
- FLOAT
### 檢視資料表的定義
```sql
DESC person;
```
|Column|Type|Null|Key|Defult|extra|
|:---:|:---:|:---:|:---:|:---:|:---:|
|firstName|varchar(10)|YES||NULL||
|lastName|varchar(10)|YES||NULL||
|email|varchar(20)|YES||NULL||
不能重複建立已存在的資料庫或資料表。
那麼當我們發現之前定義的資料表有些疏漏，需要重新增加欄位該怎麼辦？重新來過。
### 清空資料表
```sql
DROP TABLE person;
```
將會移除該資料表並清空該資料表中所有資料，並告訴你這次指令影響了多少資料。
### 插入資料
完成資料庫及資料表的建置後，我們就可以正式把我們希望紀錄的資料輸入。
```sql
INSERT INTO person(firstName, lastName, email)
VALUES('Branson', 'Ann', 'annie@boards-r-us.com');
INSERT INTO person
VALUES('Branson', 'Ann', 'annie@boards-r-us.com');
INSERT INTO person(firstName)
VALUES('Branson');
```
未輸入插入表格欄位順序時將依照資料表定義順序來插入資料。
如果該資料欄位有設置預設值時，可以省略該欄位的資料插入，這種情況將會以預設值取代。
### 插入多筆資料
```sql
INSERT INTO person
VALUES('Branson', 'Ann', 'annie@boards-r-us.com')
, ('person2', 'person22', 'person2@boards-r-us.com')
, ('person3', 'person3', 'person3@boards-r-us.com');
```
### 取得資料
```sql
SELECT * FROM person;
```
|last_name|first_name|email|
|:---:|:---:|:---:|
|Branson|Ann|annie@boards-r-us.com|
|Hamilton|Jamie|dontbother@breakneckpizza.com|
|Soukup|Alan|soukup@breakneckpizza.com|
```sql
SELECT firstName FROM person;
```
|first_name|
|:---:|
|Ann|
|Jamie|
|Alan|
### NULL
正常來說，我們並不期望自己的資料中有 NULL 的存在，畢竟那意味著資料的不完善。因此很多時候我們建立資料表時就會限制該欄位為 NOT NULL。
```sql
CREATE TABLE person
(
firstName VARCHAR(10) NOT NULL,
lastName VARCHAR(10) NOT NULL,
email VARCHAR(20) NOT NULL,
money INT NOT NULL DEFAULT 0;
);
```
透過這樣的方式我們可以要求欄位不得為空，甚至設定好一些初始值以防漏填出錯。
## SELECT
很多時候我們並不期望取得所有的資料，也許只想要符合要求的某部分資訊而已。
因此 SELECT 提出了強大的搜尋功能，幫助我們篩選這些資料。
### WHERE：選取符合要求的資料
```sql
SELECT *
FROM person
WHERE firstName = 'Ann';
```
|last_name|first_name|email|
|:---:|:---:|:---:|
|Branson|Ann|annie@boards-r-us.com|
進一步簡化我希望取得的資訊。
```sql
SELECT email
FROM person
WHERE firstName = 'Ann';
```
|email|
|:---:|
|annie@boards-r-us.com|
### single quote '
這是個特殊的符號兩兩出現標注出一些特殊的資料：
- CHAR
- VARCHAR
- DATE
- DATETIME
- TIME
- TIMESTAMP
- BLOB
也因此如果我們真的希望在儲存的文字中儲存`'`，需要透過`\`來跳脫。
`\'`
### AND OR
WHERE 子句可以幫我們將結果限制在某一個條件，然而我們有時候需要一些更複雜的條件。
```sql
SELECT location
FROM doughnut_rating
WHERE type = 'plai\'n glazed'
AND rating = 10;
```
type 必須是 plain glazed
而且 rating 為 10
```sql
SELECT location
FROM doughnut_rating
WHERE type = 'plain glazed'
OR rating = 10;
```
type 是 plain glazed
或 raing 為 10
### =, >, <, <>, <=, >=
- =：相等
- \>：左邊大於右邊
- <：左邊小於右邊
- <>：不相等
- <=：左邊小於等於右邊
- \>=：大於等於
### NULL
某些時候我們也會希望特定出資料為空的資料。
我們可以使用 is 來選擇。
```sql
SELECT *
FROM drink_info
WHERE calories is NULL
```
### LIKE
關於字串的尋找，很多時候我們想要找的是一些相近資料，例如姓許的人、姓陳的人。
```sql
SELECT name, career
FROM person
WHERE career LIKE '%師%';
```
透過 IKE 關鍵字以及%(表萬用字元，可以是任意數量的任何文字)_(表萬用字元，一個任意文字)，以此例我們可以找出職業有師字的人，老師、工程師、師父。
萬用字元同時也可以是沒有字的意思->'老師'師後面沒東西但也會被找出來。
### BETWEEN
透過 BETWEEN 關鍵字，我們可以取出符合某個數值至某個數值的資料。
```sql
SELECT drink_name FROM drink_info
WHERE calories BETWEEN 30 AND 60;
```
注意：BETEEN ? AND ? 必須小的數值在前。
### IN
```sql
SELECT date_name FROM black_book
WHERE rating = 'innovative'
OR rating = 'fabulous'
OR ....;
```
當我們希望取得符合某個欄位某些值的資料時，雖然已經可以透過寫一長串的 OR 來達成，但 SQL 提供了我們更方便的關鍵字 IN。
```sql
SELECT date_name
FROM black_book
WHERE rating IN ('innovative', 'fabulous', 'delightful', 'pretty good');
```
### NOT
WHERE 子句中我們已經學會了許多種取出特定資料的方法：
- AND
- OR
- LIKE
- \>, <, <>, >=, <=
- LIKE
- BETWEEN
- IN
現在我們還可以透過 NOT 關鍵字來反轉結果。
```sql
SELECT name
FROM person
WHERE NOT age > 10;
```
```sql
SELECT name
FROM person
WHERE age <= 10;
```
## DELETE, UPDATE
### DELETE
有些時候我們也許會有些不必要的舊資料希望刪除，這時候我們可以利用 DELETE 以及 WHERE 來鎖定刪除我們不需要的資料。
```sql
DELETE FROM clown_info
WHERE activities = 'dancing';
```
### DELETE 的限制
- 只能刪除完整資料，不能只刪除某一欄資料。
- DELETE 不加 WHERE 子句將會把該表中所有資料刪除。
- 但也許這種情境你會想用 TRUNCATE 語法
```sql
TRUNCATE TABLE clown_info;
```
### UPDATE
雖然我們可以利用 DELETE 及 INSERT 來達成修改資料的目的，但這樣很容易產生一些問題以及產生麻煩，因此單純想修改資料時最好使用 UPDATE 及 WHERE 子句來修改特定資料。
```sql
UPDATE doughnut_rating
SET type = 'glazed'
WHERE type = 'plain glazed';
UPDATE table_name
SET first_colunm = first_new_value
, second_column = second_new_value
WHERE type = 'plain glazed';
UPDATE drink_info
SET cost = cost + 1
WHERE dring_name IN('Blue Moon'
, 'Oh My Gosh'
, 'Lime Fizz');
```
## 資料表設計
目前來說我們已經可以正常的使用資料庫功能了，能夠產生我們想要的資料表，紀錄我們的資料，隨時取得符合要求的資料，刪除或是修改這些資料。
然而，一個隨意設計的資料表可能會讓我們陷入不斷修改的困境，也許需要加入一個欄位、去除一個欄位、不斷重複的修正某些資料。
欄位的設計應該遵循一些原則：
1. 你的資料表想要描述什麼
2. 使用這張表時希望得到哪些資料
3. 整理這些需求並將資料化為適合達到這些目的的最小單位
4. 確定這張表所要描述的東西
5. 你將透過什麼來查找這張表(優化搜尋的方式)
6. 資料是否有達成最小單位以讓搜尋簡單而有效
具體來說我們可以：
1. 消除每個資料表中同類型的欄位
2. 為每個相關的資料建立一個獨立的資料表
3. 使用一個主鍵值(KEY)來識別每一筆資料
### KEY
又叫做 Primary Key，可以幫助我們識別資料表中的資料，因為 Primary Key 不能為 NULL、插入資料時必須決定、在整張表中這個 Key 是唯一的、無法被改變。如果在資料表中無法找到適合的主鍵欄位，我們新增一欄流水序號定義一個簡單的主鍵。
### 重建資料表
我們可以直接刪除整張表並重新建立來完成這項工作。
```sql
CREATE TABLE my_contacts
(
contact_id INT NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) DEFAULT NULL,
first_name VARCHAR(30) DEFAULT NULL,
email VARCHAR(50) DEFAULT NULL,
gender CHAR(1) DEFAULT NULL,
birthday DATE DEFAULT NULL,
....,
....,
....,
PRIMARY KEY(contact_id)
);
```
然而這樣也意味著會失去所有過往儲存的資料，因此 SQL 提供了 ALTER。
### ALTER
```sql
ALTER TABLE my_contacts
ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY(contact_id);
```
我們可以透過在 ADD COLUMN 最後加上 FIRST、LAST、SECOND、AFTER column_name、BEFORE column_name 來調整新欄位加入的位置。
除了新增欄位外我們其實也可以透過 ALTER 與其他語句的配合做到：
- CHANGE：改變某個欄位的名稱及類型
- MODIFY：改變某個欄位的位置以及類型
- DROP：移除某個欄位
### 資料表更名
```sql
ALTER TABLE projekts RENAME TO project_list;
```
### 一些變更資料表的例子
|Filed|Type|Null|Key|Default|Extra|
|:---:|:---:|:---:|:---:|:---:|:---:|
|number|INT(11)|YES||NULL||
|descriptionofproj|VARCHAR(50)|YES||NULL||
|contractoronjob|VARCHAR(10)|YES||NULL||
```sql
ALTER TABLE project_list
CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY('proj_id');
```
|Filed|Type|Null|Key|Default|Extra|
|:---:|:---:|:---:|:---:|:---:|:---:|
|proj_id|INT|NO|YES|||
|descriptionofproj|VARCHAR(50)|YES||NULL||
|number|VARCHAR(10)|YES||NULL||
```sql
ALTER TABLE project_list
CHANGE COLUMN descriptionofproj proj_desc VARCHAR(100),
CHANGE COLUMN contractoronjob con_name VARCHAR(30);
```
|Filed|Type|Null|Key|Default|Extra|
|:---:|:---:|:---:|:---:|:---:|:---:|
|number|INT|NO|YES|||
|proj_desc|VARCHAR(100)|YES||NULL||
|con_name|VARCHAR(30)|YES||NULL||
注意：若某些字串形式資料更改成更短的格式限制，很可能會切掉一些舊的資料。
例如：VARCHAR(5) -> VARCHAR(1)
BOB -> B
### 拆分欄位
為了正規化資料，我們很常會需要將一個欄位拆成許多欄位，為了不喪失資料，甚至讓舊的資料協助我們填充新欄位，我們可以透過 SQL 提供的一些 STRING FUNCTION 來切割字串(可以用在 SELECT、UPDATE、DELETE)。
- RIGHT('STRING', 2)：NG
- LEFT('STRING', 2)：ST
- SUBSTRING('San Antonio, TX', 5, 3)：Ant(從第五個字開始長度為三的子字串)
- UPPER('uSa')：USA
- LOWER('uSa')：usa
- REVERSE('spaGHEtti')：ittEHGaps
- LTRIM(' catfood ')：`catfood `
- RTRIM(' catfood ')：` catfood`
- LENGTH(' catfood ')：` catfood`
TestTable
|location|
|:---:|
|Seattle, WA|
|Natchez, MS|
|Las Vegas, NV|
|Palo Alto, CA|
```sql
UPDATE TestTable
SET location = RIGHT(location, 2);
```
TestTable
|location|
|:---:|
|WA|
|MS|
|NV|
|CA|
### CASE, WHEN, THEN, ELSE
|movie_id|title|rating|drama|comedy|for_kids|cartoon|purchased|
|:---:|:---:|:---:|:---:|:---:|:---:|:---:|:---:|
|1|Monsters, Inc.|G|F|T|T|T|3-6-2002|
|2|The Godfather|R|F|F|F|F|2-5-2001|
|3|Gone with the Wind|G|T|F|F|F|11-20-1999|
取出資料時我們看著 T、F 難以辨識這些資料的實際含意。
應該將這些資料統整成一個 category。
```sql
UPDATE movie_table
SET category = 'drama'
WHERE drama = 'T';
UPDATE movie_table
SET category = 'comedy'
WHERE comedy = 'T';
UPDATE movie_table
SET category = 'family'
WHERE for_kids = 'T';
UPDATE movie_table
SET category = 'family'
WHERE cartoon = 'T'
AND RATING = 'G';
UPDATE movie_table
SET category = 'misc'
WHERE cartoon = 'T'
AND RATING <> 'G';
```
但這樣寫又顯得太麻煩。
```sql
UPDATE movie_table
SET category = (
CASE
WHEN drama = 'T'
THEN 'drama'
WHEN comedy = 'T'
THEN 'comedy'
WHEN for_kids = 'T'
THEN 'family'
WHEN cartoon = 'T' AND RATING = 'G'
THEN 'family'
WHEN cartoon = 'T' AND RATING <> 'G'
THEN 'misc'
ELSE 'other'
END
);
```
除了用於 UPDATE，CASE 還可作用於 SELECT、INSERT、DELETE。
## 進階 SELECT
單純的取出資料表內容未必能完全滿足我們的需求，這時我們需要更強力的工具來協助我們調整資料呈現。
### ORDER
當我們希望取出資料時，資料是經過排序的。可以使用 ORDER 來協助我們。
```sql
SELECT title, category
FROM movie_table
WHERE category = 'family'
ORDER BY title;
```
|title|category|
|:---:|:---:|
|1 Crazy Alien|family|
|10 Big Bugs|family|
|101 Alsation|family|
|....|family|
|....|family|
|....|family|
|....|family|
|....|family|
此時會以 SQL 的 order rule 來排序。
012345abcdABCD!@#$%
排序時也可以要求其他欄位也加入排序。
```sql
SELECT title, category, purchased
FROM movie_table
WHERE category = 'family'
ORDER BY title, purchased;
```
可透過 DESC 關鍵字去反轉排序順序，預設為 ASC。
```sql
SELECT title, category, purchased
FROM movie_table
WHERE category = 'family'
ORDER BY title ASC, purchased DESC;
```
### GROUP、SUM、AVG
|ID|fistName|sales|saleDate|
|:---:|:---:|:---:|:---:|
|1|Lindsay|32.02|3-6-2007|
|2|Paris|26.53|3-6-2007|
|3|Britney|11.25|3-6-2007|
|4|Nicole|18.96|3-6-2007|
|5|Lindsay|9.16|3-7-2007|
|6|Paris|1.52|3-7-2007|
|7|Britney|43.21|3-7-2007|
|8|Nicole|8.05|3-7-2007|
有時候我們會儲存這樣的資料，也許我想知道某一天的銷售額總額，也許想知道某人的總銷售額。
這時候 GROUP 跟一些 MATH FUNCTION 就派上用場了。
```sql
SELECT SUM(sales)
FROM coockie_sales
WHERE first_name = 'Nicole';
```
|SUM(sales)|
|:---:|
|27.01|
透過 SUM 以及 WHERE 我們已經可以知道某人的銷售總額了，但這樣要取出所有人的各自總額就顯得很麻煩。
```sql
SELECT firstName, SUM(sales)
FROM coockie_sales
GROUP BY firstName
ORDER BY SUM(sales);
```
|firstName|SUM(sales)|
|:---:|:---:|
|Nicole|27.01|
|Paris|28.05|
|Lindsay|41.18|
|Britney|54.46|
透過 GROUP 將資料分組，可以幫助我們取得每個人各自的總銷售額。
```sql
SELECT firstName, AVG(sales)
FROM coockie_sales
GROUP BY firstName
ORDER BY AVG(sales);
```
|firstName|AVG(sales)|
|:---:|:---:|
|Nicole|13.505|
|Paris|14.025|
|Lindsay|20.59|
|Britney|27.23|
### MAX、MIN
透過 GROUP 以及 MIN、MAX 的組合，我們也可以將資料集中最大或最小的資料找出。
```sql
SELECT firstName, MIN(sales)
FROM coockie_sales
GROUP BY firstName;
```
|fistName|MIN(sales)|
|:---:|:---:|
|Britney|11.25|
|Lindsay|9.16|
|Paris|1.52|
|Nicole|8.05|
除此之外還有一種 COUNT FUNCTION，可以告訴你資料表中有多少資料被選取。
```sql
SELECT COUNT(saleDate)
FROM coockie_sales
GROUP BY firstName;
```
4
```sql
SELECT COUNT(saleDate) FROM coockie_sales;
```
8
### DISTINCT
```sql
SELECT DISTINCT saleDate
FROM coockie_sales
ORDER BY saleDate;
```
|saleDate|
|:---:|
|3-6-2007|
|3-7-2007|
```sql
SELECT COUNT(DISTINCT saleDate) FROM coockie_sales;
```
2
透過 DISTINCT 關鍵字可以讓重覆資料只出現一次。
### LIMIT
有時候我們未必需要取得所有資料，可能只會需要前三位或是第幾位資料。
這時候我們可以利用 LIMIT 來縮限所需的資料。
```sql
LIMIT $1
LIMIT $1,$2
```
LIMIT 可給予一個或兩個參數，只給予一個參數時結果會僅止給出 $1 筆資料；給予兩個參數時會先跳過 $1 筆資料再取得 $2 筆資料。
```sql
SELECT firstName, SUM(sales)
FROM coockie_sales
GROUP BY firstName
ORDER BY SUM(sales);
```
|firstName|SUM(sales)|
|:---:|:---:|
|Nicole|27.01|
|Paris|28.05|
|Lindsay|41.18|
|Britney|54.46|
以此例
```sql
SELECT firstName, SUM(sales)
FROM coockie_sales
GROUP BY firstName
ORDER BY SUM(sales)
LIMIT 2;
```
|firstName|SUM(sales)|
|:---:|:---:|
|Nicole|27.01|
|Paris|28.05|
```sql
SELECT firstName, SUM(sales)
FROM coockie_sales
GROUP BY firstName
ORDER BY SUM(sales)
LIMIT 1,1;
```
|firstName|SUM(sales)|
|:---:|:---:|
|Paris|28.05|
跳過第一筆資料取得一筆資料意味著選取第二筆資料。
## 過大的資料表
在資料不龐大時，單一資料表足以應對所有問題。然而隨著資料不斷增加，原本好用的資料表可能漸漸的變得越來越龐大，搜尋也越來越複雜，浪費了空間以及效能。
例如我們可能有個
|||
|:---:|:---:|
|contactId|873|
|lastName|Suliivan|
|firstName|Regies|
|phone|5552311122|
|me@kathieleeisaflake.com|
|gender|M|
|birthday|1955-03-20|
|profession|Comdian|
|city|Cambridge|
|state|MA|
|status|single|
|interests|animals, trading cards, geocaching|
|seeking|single F|
這樣一個表表示一個人的基本訊息，以及興趣、單身與否。
興趣欄位顯然是一個會有不定數量資料的欄位，當我們希望尋找有相似興趣的人時會變得十分麻煩。
```sql
SELECT name, interests
FROM my_contacts
WHERE interests LIKE '%animals%'
AND interests LIKE '%trading cards%'
AND interests LIKE '%geocaching%';
```
這樣可以幫助我們找到有完全相同興趣的人，但 query 的撰寫變得十分麻煩，我們需要透過寫死的方式輸入各個興趣的資料。
```sql
SELECT name, interests
FROM my_contacts
WHERE SUBSTRING_INDEX(intersts, ',', 1) = 'animals';
```
為了讓搜尋能夠較為適用，我們改為只要求第一個興趣相同即可(會產生些許問題，配對到的人無法足夠精準)，而且需要透過 SUBSTRING_INDEX 去切割興趣欄位。
如果將只有一個 interests 改為 interest1, interest2, interest3？
|||
|:---:|:---:|
|contactId|873|
|lastName|Suliivan|
|firstName|Regies|
|phone|5552311122|
|me@kathieleeisaflake.com|
|gender|M|
|birthday|1955-03-20|
|profession|Comdian|
|city|Cambridge|
|state|MA|
|status|single|
|interest1|animals|
|interest2|trading cards|
|interests|geocaching|
|seeking|single F|
```sql
SELECT name, interests
FROM my_contacts
WHERE ((
interest1 = 'animals' OR
interest2 = 'animals' OR
interest3 = 'animals'
)
AND (
interest1 = 'trading cards' OR
interest2 = 'trading cards' OR
interest3 = 'trading cards'
)
AND (
interest1 = 'geocaching' OR
interest2 = 'geocaching' OR
interest3 = 'geocaching'
)
);
```
省了 SUBSTRING_INDEX 的麻煩，也能保證結果精準，但顯得資料表更加多於冗長了。
因此根本問題在於資料表設計得太過冗雜，我們應該想辦法將這張大表格切割成幾張小表格，好簡化搜尋的難度。
將性質類似的欄位分割出來獨立成一張表格，並與原本資料使用一個簡單的 schema 來關聯。
例如：我們可以直接將 interests 取出來獨立成一張表，
|interests|
|:---:|
|interestId|
|interest|
接著，與原資料關聯。
||
|:---:|
|contactId|
|lastName|
|firstName|
|phone|
|me@kathieleeisaflake.com|
|gender|
|birthday|
|profession|
|city|
|state|
|status|
|seeking|
|interests|
|:---:|
|interestId|
|interest|
|lastName|
|firstName|
現在我們可以透過兩次 SQL 執行來達成一些原本的工作。
不過目前仍然有個問題，如果有人同名同姓有不同的興趣時關聯會出現問題。
|interests|
|:---:|
|interestId|
|interest|
|contactId|
因此我們改用表中唯一的 Primary Key(contactId)做成 Foreign Key 來作關聯。
### Foreign Key
Foreign Key 用來幫助我們聯結其他表格，與 Primary Key 有些不同：
- 可以為 NULL
- 不必是唯一的(通常情況下常常不是唯一)
- 聯結的兩個欄位必須是相同類型相同寬度的
Foreign Key 為 NULL 時，代表這筆資料並未與其他表格之資料產生聯結。
儘管 Foreign Key 可以為 NULL，但我們也可以透過 constrant 去限制必須與其他表格資料聯結。
我們可以要求 Foreign Key 必須為父表格中已存在的資料(關聯完整性)。
```sql
CREATE TABLE interests (
interestId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
interest VARCHAR(50) NOT NULL,
contactId INT NOT NULL,
CONSTRAINT myContacts_contactId_fk
FOREIGN KEY(contactId)
REFERENCES myContacts(contactId);
)
```
資料表上的關聯通常有三種形式，主資料對副資料：
- 一對一
- 一對多
- 多對多
當我們定義好這些資料的關聯方式後，我們就可以輕易的設計出我們需要的資料表，我們的資料欄位也會變得更加簡單明瞭。
### 一對一關聯
|employeeId(PK)|firstName|lastName|
|:---:|:---:|:---:|
|1|Beyonce|knowles|
|2|Shawn|Carler|
|3|Shakira|Ripoll|
|ssn(PK)|salaryLevel|employeeId(FK)|
|:---:|:---:|:---:|
|234567891|2|6|
|345678912|5|35|
|123456789|7|1|
這樣就屬於一個簡單的一對一關聯，一個員工(主資料)對應一個屬於他的薪水規定(子資料)。然而實際上我們並不常採用這樣的關聯方式，實際採用一對一關聯時多為：
1. 加快執行 SQL 的速度，當我們在這樣的例子中希望修改某一人特定的薪水規定時，只需要在 salary 中 SELECT 出正確的 ssn 就可以完成修改。
2. 透過這樣的分離方式，可以將員工與薪水孤立開來，保護限制薪水資料的存取。
3. 假設有較大資料的資料希望儲存(BLOB)，通常也會將該檔案與主資料分隔開來。
### 一對多關聯
|profession|
|:---:|
|professionId(PK)|
|profession|
|person|
|:---:|
|contactId(PK)|
|lastName|
|firstName|
|phone|
|me@kathieleeisaflake.com|
|gender|
|birthday|
|professionId(FK)|
|city|
|state|
|status|
|seeking|
由於在 profession 表中 professionId 為 PK 因此整張表中同樣的 professionId 只能有一個，在 person 中 professionId 是 FK，並不需要是唯一的。因此可能有很多人共同擁有同一個 professionId。
具體表現形式就像是，一個 profession 可能可以被很多 person 擁有。
### 多對多關聯
有些時候我們可能會發生一些複雜的關聯行為，例如上面 interest 的例子，一個人可能有很多種喜好，一種喜好可能會有人多人擁有。
這種情況會使狀況使資料的聯結變得複雜，我們很難去維護資料完整性，因此我們可能會透過一個中介資料表，取代多對多的關聯。
`A->B<-C`
- A 對 B 是一對多的關聯
- C 對 B 也是一對多的關聯
|womanId(PK、FK)|woman|
|:---:|:---:|
|1|Carrie|
|2|Samantha|
|3|Charlotte|
|4|Miranda|
|womanId(FK)|shoeId(FK)|
|:---:|:---:|
|1|3|
|1|4|
|2|1|
|3|1|
|3|2|
|3|3|
|3|4|
|4|1|
|4|3|
|4|4|
|shoeId(PK、FK)|shoeName|
|:---:|:---:|
|1|Manolo Strappies|
|2|Crocs Clogs|
|3|Old Navy Flops|
|4|Prada boots|
### First Normal Form(1NF)
1. 欄位資料屬於最小原子
2. 沒有重複的資料欄位
3. 有一個 PK，且其他欄位功能相依於 PK
|toyId|toy|colors|
|:---:|:---:|:---:|
|5|whiffleball|white, yellow, blue|
|6|frisbee|green, yellow|
|9|kite|red, blue, green|
|12|yoyo|white, yellow|
資料不屬於最小原子
|toyId|toy|color1|color2|color3|
|:---:|:---:|:---:|:---:|:---:|
|5|whiffleball|white|yellow|blue|
|6|frisbee|green|yellow||
|9|kite|red|blue|green|
|12|yoyo|white|yellow||
有重複的資料欄位
|toyId(PK)|toy|
|:---:|:---:|
|5|whiffleball|
|6|frisbee|
|9|kite|
|12|yoyo|
|toyId(FK、PK)|color(PK)|
|:---:|:---:|
|5|white|
|5|yellow|
|5|blue|
|6|green|
|6|yellow|
|9|red|
|9|blue|
|9|green|
|12|white|
|12|yellow|
採用複合 PK 讓 color 表每個欄位都有唯一的 PK。
### Functional Dependency
功能相依：描述表中屬性的相依關係，是關聯資料庫的基礎，1NF 就是要找出這些相依性。
找出屬性間的功能相依，可以幫助我們找出重複資料以及 PK(若表中所有屬性都功能相依於某個屬性那該屬性就是 PK、所有屬性都功能相依於某組屬性，那組屬性就是候選鍵)
若關聯表 R 有屬性 A 與 B，A 與 B 可以是複合屬性。
如果 B 功能相依於 A，或稱 A 功能決定 B，則寫成：R.A→R.B，或簡寫成：A→B
A→B 表示知道 A 的值就可以決定 B 的值。
功能相依又可以分為：
- 完全相依：A 和 B 是關聯表屬性，A 或 B 可能是複合屬性，B 功能相依於 A，而且 B 沒有功能相依於任何 A 的子集。
- 部分相依：A(複合屬性,例如 a1+a2)和 B 是關聯表的屬性，如果 B 是功能相依於 A(A→B)，而且 B 又功能相依於 A 的部分屬性(a1→B 或 a2→B)
- 遷移相依：A、B 和 C 是關聯表的屬性，如果 A→B 且 B→C，則稱 C 是遞移相依於 A，A→C
### Second Normal Form(2NF)
1. 完成 1NF
2. 沒有部分相依的欄位
|toyId(PK)|toy|
|:---:|:---:|
|5|whiffleball|
|6|frisbee|
|9|kite|
|12|yoyo|
|toyId(FK、PK)|storeId(PK)|color|inventory|storeAddress|
|:---:|:---:|:---:|:---:|:---:|
|5|1|white|34|23 Maple|
|5|3|yellow|12|100 E.North St.|
|5|1|blue|5|23 Maple|
|6|2|green|10|1902 Amber Ln.|
|6|4|yellow|24|17 Engliside|
|9|1|red|50|23 Maple|
|9|2|blue|2|1902 Amber Ln.|
|9|2|green|18|1902 Amber Ln.|
|12|4|white|28|17 Engliside|
|12|4|yellow|11|17 Engliside|
完成 2NF 後會變成三張表，某個玩具(包含顏色等資訊)在某個倉庫有多少庫存、玩具、倉庫。
|toyId|storeId|inventory|
|:---:|:---:|:---:|
|5|1|34|
|5|3|12|
|5|1|5|
|6|2|10|
|6|4|24|
|9|1|50|
|9|2|2|
|9|2|18|
|12|4|28|
|12|4|11|
|storeId|address|
|:---:|:---:|
|1|23 Maple|
|2|1902 Amber Ln.|
|3|100 E.North St.|
|4|17 Engliside|
|toyId|toy|color|
|:---:|:---:|:---:|
|5|whiffleball|white|
|5|whiffleball|yellow|
|5|whiffleball|blue|
|6|frisbee|green|
|6|frisbee|yellow|
|9|kite|red|
|9|kite|blue|
|9|kite|green|
|12|yoyo|white|
|12|yoyo|yellow|
補上一些新的欄位。
|toyId|storeId|inventory|
|:---:|:---:|:---:|
|5|1|34|
|5|3|12|
|5|1|5|
|6|2|10|
|6|4|24|
|9|1|50|
|9|2|2|
|9|2|18|
|12|4|28|
|12|4|11|
|storeId|address|phone|manager|
|:---:|:---:|:---:|:---:|
|1|23 Maple|555-6712|Joe|
|2|1902 Amber Ln.|555-3478|Susan|
|3|100 E.North St.|555-0987|Tara|
|4|17 Engliside|555-6554|Gordon|
|id|toyId|toy|color|cast|weight|
|:---:|:---:|:---:|:---:|:---:|:---:|
|1|5|whiffleball|white|1.95|0.3|
|2|5|whiffleball|yellow|2.20|0.4|
|3|5|whiffleball|blue|1.95|0.3|
|4|6|frisbee|green|3.50|0.5|
|5|6|frisbee|yellow|1.50|0.2|
|6|9|kite|red|5.75|1.2|
|7|9|kite|blue|5.75|1.2|
|8|9|kite|green|3.15|0.8|
|9|12|yoyo|white|4.25|0.4|
|10|12|yoyo|yellow|1.50|0.2|
經過 2NF 後表中欄位間不再部分相依。
### Third Normal Form(3NF)
1. 完成 2NF
2. 沒有遷移相依的欄位
遷移相依意味著有非鍵值屬性相依於其他非鍵值屬性，造成改動一筆資料的非鍵值屬性很可能需要同時去影響該資料的另一非鍵值屬性。
## JOIN
在完成了這麼多動作後，我們會發現原本一張完整的資料表，被我們逐漸切割成了許多更單純的表，這幫助了我們避免出現太多重複資料也順利的節省空間。
然而現在搜尋時需要取得完整資料，我們卻得同時搜尋好幾張表格，變得十分麻煩。
### Cartesian Join(Cross Join)
也叫做交叉連線。
| LastName | DepartmentID |
| --- | --- |
| Rafferty | 31 |
| Jones | 33 |
| Steinberg | 33 |
| Robinson | 34 |
| Smith | 34 |
| Jasper | _NULL_ |
| DepartmentID | DepartmentName |
| --- | --- |
| 31 | 銷售部 |
| 33 | 工程部 |
| 34 | 秘書 |
| 35 | 市場部 |
```sql
SELECT *
FROM employee
CROSS JOIN department;
SELECT *
FROM employee, department;
```
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
| --- | --- | --- | --- |
| Rafferty | 31 | 銷售部 | 31 |
| Jones | 33 | 銷售部 | 31 |
| Steinberg | 33 | 銷售部 | 31 |
| Smith | 34 | 銷售部 | 31 |
| Robinson | 34 | 銷售部 | 31 |
| Jasper | _NULL_ | 銷售部 | 31 |
| Rafferty | 31 | 工程部 | 33 |
| Jones | 33 | 工程部 | 33 |
| Steinberg | 33 | 工程部 | 33 |
| Smith | 34 | 工程部 | 33 |
| Robinson | 34 | 工程部 | 33 |
| Jasper | _NULL_ | 工程部 | 33 |
| Rafferty | 31 | 秘書 | 34 |
| Jones | 33 | 秘書 | 34 |
| Steinberg | 33 | 秘書 | 34 |
| Smith | 34 | 秘書 | 34 |
| Robinson | 34 | 秘書 | 34 |
| Jasper | _NULL_ | 秘書 | 34 |
| Rafferty | 31 | 市場部 | 35 |
| Jones | 33 | 市場部 | 35 |
| Steinberg | 33 | 市場部 | 35 |
| Smith | 34 | 市場部 | 35 |
| Robinson | 34 | 市場部 | 35 |
| Jasper | _NULL_ | 市場部 | 35 |
將兩張表所有資料交叉連結後產生，如 A 表有五筆資料 B 表有六筆資料，那麼 AB 交叉連線後會產生 30 筆資料。
### Inner Join
很多時候我們可以透過 Inner Join 來幫助我們把關聯的資料表聯結在一起，只要 Cross Join 後，利用 ON 將 foreign key 相等的對應資料取出即可。
```sql
SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID;
```
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
| --- | --- | --- | --- |
| Robinson | 34 | 秘書 | 34 |
| Jones | 33 | 工程部 | 33 |
| Smith | 34 | 秘書 | 34 |
| Steinberg | 33 | 工程部 | 33 |
| Rafferty | 31 | 銷售部 | 31 |
某些情況我們也可以做些使用上的轉換，想像有兩張表一張紀錄顧客購買紀錄，另一張紀錄商品。
透過 Inner Join ON 的條件設為不相等，我們就可以找出某位顧客仍未購買過什麼產品。
```sql
SELECT c.name, p.name FROM
custumer AS c
INNER JOIN
product AS p
ON c.productName <> p.name;
```
### Natural Join
其實就是 Inner Join 自動比對相同名稱欄位
```sql
SELECT *
FROM employee
NATURAL JOIN department;
```
| DepartmentID | Employee.LastName | Department.DepartmentName |
| --- | --- | --- |
| 34 | Smith | 秘書 |
| 33 | Jones | 工程部 |
| 34 | Robinson | 秘書 |
| 33 | Steinberg | 工程部 |
| 31 | Rafferty | 銷售部 |
### Outer Join
Inner Join 要求在兩張表中都必須有值，Outer Join 則不受此限，無值的部分會以 NULL 取代顯示。
外連線又可分為：
1. 左外連線：以左側資料為主顯示，右表無資料則以 NULL 呈現。
```sql
SELECT *
FROM employee
LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
```
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
| --- | --- | --- | --- |
| Jones | 33 | Engineering | 33 |
| Rafferty | 31 | Sales | 31 |
| Robinson | 34 | Clerical | 34 |
| Smith | 34 | Clerical | 34 |
| _Jasper_ | _NULL_ | _NULL_ | _NULL_ |
| Steinberg | 33 | Engineering | 33 |
2. 右外連線：與左外連線一樣概念，只是順序相反，保留右側的。
3. 全連線：一些資料庫系統(如 MySQL)並不直接支援全連線, 但它們可以通過左右外連線的並集(參考：[union](https://zh.wikipedia.org/w/index.php?title=Union_(SQL)&action=edit&redlink=1 "Union (SQL)（頁面不存在）"))來模擬實現。
```sql
SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
```
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
| --- | --- | --- | --- |
| Smith | 34 | Clerical | 34 |
| Jones | 33 | Engineering | 33 |
| Robinson | 34 | Clerical | 34 |
| _Jasper_ | _NULL_ | _NULL_ | _NULL_ |
| Steinberg | 33 | Engineering | 33 |
| Rafferty | 31 | Sales | 31 |
| _NULL_ | _NULL_ | _Marketing_ | _35_ |
## Subqueries
解決了關聯資料表的問題後，有些時候為了取得更精確的資料，我們不得不依序輸入幾條不同的 Query 去逐步縮減結果。
```sql
SELECT title
FROM job_listings
GROUP BY title
ORDER BY title;
SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc
INNER JOIN my_contacts AS mc
ON jc.current_id = mc.current_id
WHERE jc.title IN ('Cook', 'Hairdresser', 'Waiter', 'Web Designer', 'Web Developer');
```
第一條 Query 取得 Cook, Hairdresser, Waiter, Web Designer, Web Developer 後將這些做為條件輸入第二條 Query。
這樣就顯得十分不方便，因此 SQL 提供了 Subqueries 幫助我們整合 Query。
以這個例子：第一條 Query 被稱為 Inner Query 他會先取得符合的資料並套入 Outer Query 也就是第二條 Query。
而 Inner Query + Outer Query = Subqueries。
```sql
SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc
INNER JOIN my_contacts AS mc
ON jc.current_id = mc.current_id
WHERE jc.title IN (SELECT title
FROM job_listings
GROUP BY title
ORDER BY title
);
```
透過 Subqueries 我們也能做到與 join 相同的效果。
### 子查詢的使用範圍
- SELECT：
```sql
SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500
);
```
- INSERT：可以使用從子查詢收到的資料插入到表中。
```sql
INSERT INTO CUSTOMERS_BKP
SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS);
```
- UPDATE：可以使用從子查詢收到的資料插入到表中
```sql
UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE
FROM CUSTOMERS_BKP
WHERE AGE >= 27
);
```
- DELETE：可以使用從子查詢收到的資料做為刪除標準。
```sql
DELETE FROM CUSTOMERS
WHERE AGE IN (SELECT AGE
FROM CUSTOMERS_BKP
WHERE AGE > 27
);
```
### 關聯子查詢 與 非關聯子查詢
- 非關聯子查詢：子查詢與主查詢無關。
```sql
SELECT p.ProductID, p.ProductName, p.UnitPrice
FROM Products AS p
WHERE p.UnitPrice > (SELECT AVG(P2.UnitPrice) FROM Products P2);
```
- 關聯子查詢：子查詢會用到主查詢的結果。
```sql
SELECT p.ProductID, p.ProductName, p.UnitPrice
FROM Products AS p
WHERE Exists (SELECT ProductID
FROM OrderDetails OD
WHERE OD.ProductID = p.ProductID
);
```
在子查詢中用到了主查詢的 p.ProductID，這種情況下因為會需要不斷重複比對主查詢子查詢，當資料龐大時效能影響會十分嚴重。
## 資料集的操作
必須是同樣類型的資料集。
### UNION、UNION ALL
UNION：聯集，可以將結果合併起來。
```sql
SELECT * FROM A
UNION
SELECT * FROM B;
```
A 與 B 的結果會合併在一起出來，重複的行只會出現一次。
UNION ALL：可以將結果合併起來。
```sql
SELECT * FROM A
UNION ALL
SELECT * FROM B;
```
A 與 B 的結果會合併在一起出來，重複的行會被保留。
### EXCEPT
差集：去除掉一部分結果。
```sql
SELECT * FROM A
EXCEPT
SELECT * FROM B;
```
顯示只出現在 A 中沒出現在 B 的結果。
### INTERSECT
交集：共同出現的結果。
```sql
SELECT * FROM A
INTERSECT
SELECT * FROM B;
```
A 與 B 共有的結果。
### JOIN & SUBQUERIES
一個 SUBQUERIES 與 JOIN 之間通常可以互相轉換。
```sql
SELECT c1.name, c2.name AS boss
FROM clown_info AS c1
INNER JOIN clown_info AS c2
ON c1.boss_id = c2.id;
```
```sql
SELECT c1.name
, (SELECT name FROM clown_info WHERE c1.boss_id = id) AS BOSS
FROM clown_info AS c1;
```
## 維護資料庫
當資料庫越來越龐大、使用者與有能力存取資料庫的人越來越多，漸漸的就會有些問題發生。
### 欄位資料規則
也許某些欄位你已經假定只會出現某幾種值，例如：性別：F、M 或是年齡：0~120。
我們最好要透過一些方式去明確限制這點，使人在輸入不正確資料時會被資料庫拒絕。
```sql
CREATE TABLE mystery_table
(
column1 INT CHECK(column1 >200),
column2 CHAR(1) CHECK(column2 NOT IN('x', 'y', 'z')),
column3 VARCHAR(3) CHECK('A' = SUBSTRING(column3, 1, 1))
)
```
例如我們可以透過 CHECK，要求該欄位在被 INSERT 或 UPDATE 時檢查是否符合狀況。
### VIEW
```sql
CREATE VIEW web_design AS
SELECT mc.first_name, mc.last_name, mc.phone, mc.email
FROM my_contacts AS mc
NATURAL JOIN job_desired AS jd
WHERE jd.title = 'Web Design';
```
透過 CREATE VIEW 我們建立一個常用的 SELECT，日後可以直接去查看這個 VIEW 以取得資料。
```sql
SELECT * FROM web_design;
```
VIEW 就像是一個虛擬的 TABLE。
使用 VIEW 可以：
1. 讓搜尋變得簡單，一些複雜的 SELECT 指令，我們可以事先儲存好。
2. 使用者透過 VIEW 來作搜尋，而不是直接查表，可以讓資料庫多一層保護，也更方便日後 TABLE 的異動。
除此之外，如果 VIEW 所選取的欄位包含該表所有非 NULL 欄位的話，我們也可以對其作 INSERT、UPDATE、DELETE(不會影響超過一個基底資料表、且通過 CHECK OPTION)
### TRANSACTION
- Atomicity：單獨性．比如，一個 Transaction 裡有一個 Update command，一個 Delete command．如果 Update command 成功了而 Delete command 失敗了，則這個 Transaction 便是失敗的，所以 Update command 必須回復修改過的資料。
- Consistency：一致性在 Transaction 執行前後，資料皆處於合乎所有規則的狀態．例如，某個欄位具有 foreign key 的關係在執行後，這樣的關係必需持續下去。
- Isolation：不同的 Transaction 之間執行時不能被彼此干擾．假設有兩個 Transaction 在同一時間對相同的一百筆資料進行更新動作，而其中一個 Transaction 在更新到一半時產生錯誤，於是進行資料回復．然而，另一個 Transaction 是完成的．可想而知最後的資料狀態一定是無法預測，因為不清楚那些資料是失敗的 Transaction 做資料回復的，還是成功的 Transaction 所更新完成的。
- Durability：資料庫引擎必須要保證一旦 Transaction 完成後，不論發生任何事情，如系統當機或電力中斷等，運作後的資料都必須存在於儲存媒介中，也就是在硬碟裡。
有時候我們會對資料庫進行一系列操作(邏輯上是同一件事情的各項小操作)，例如：銀行轉帳，必須先確認
1. 帳號餘額是否足夠(SELECT)
2. 對帳號進行扣款(UPDATE)
3. 對轉帳帳號進行入帳(UPDATE)
這三件事情必須一起完成才是一個合理的轉帳操作，如果扣款成功了卻沒對轉帳對象入帳，就會出問題。
TRANSACTION 幫助我們解決了這個問題，他可以將一連串的操作包裹起來，若有任一動作執行未成功可以 rollback 回正常狀態；所有動作都執行成功時才正確將操作改變寫入資料。
有些 TABLE TYPE 不支援 TRANSACTION，因此預計需要保證安全性的資料表，會使用 TRANSACTION 的資料表應進行確認。(MySQL 中：BDB、InnoDB)
```sql
START TRANSACTION;
SELECT * FROM piggy_bank;
UPDATE piggy_bank
SET coin = 'Q'
WHERE coin = 'P';
SELECT * FROM piggy_bank;
COMMIT(ROLLBACK);
```
COMMIT 代表確認這些操作執行；ROLLBACK 則代表還原這些操作。
Microsoft SQL Server 使用 BEGIN TRANSACTION;來開始。
### 權限設定
在一開始，我們的資料庫只有一位 root 使用者且沒有密碼，為了保證安全我們需要對這樣的狀況做些處理。
設定密碼(MySQL)
```sql
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('aB2Psad78');
```
新增其他使用者(MySQL)
```sql
CREATE USER elsie IDENTIFIED BY 'elsiePassword';
```
發放權限
(給予 elsie 在 clown_info 使用 SELECT 的權限)
(給予 elsie 在 clown_info 使用 ALL(SELECT、DELETE、UPDATE、INSERT)的權限)
(給予 elsie 在 TODO 資料庫中所有表使用 SELECT 的權限)
(給予 elsie 在 clown_info 使用 ALL(SELECT、DELETE、UPDATE、INSERT)的權限，並允許他給予其他人同樣權限)
```sql
GRANT SELECT ON clown_info TO elsie;
GRANT ALL ON clown_info TO elsie;
GRANT SELECT ON TODO.* TO elsie;
GRANT ALL ON clown_info TO elsie WITH GRANT OPTION;
```
撤銷權限
(CASCADE：撤銷被 elsie 所給予的人權限
RESTRICT：若撤銷 elsie 權限會影響到其他人則回傳 ERROR)
```sql
REVOKE SELECT ON clown_info FROM elsie;
REVOKE SELECT ON clown_info FROM elsie CASCADE;
REVOKE SELECT ON clown_info FROM elsie RESTRICT;
```
### ROLE
除了針對各個使用者設定權限，也可以設置一些群組，透過給予群組權限再將各個使用者分發到群組中，以便管理。
```sql
CREATE ROLE data_entry;
GRANT SELECT ON clown_info TO data_entry;
GRANT data_entry TO elsie;
```
與 GRANT OPTION 類似，ROLE 也有 ADMIN OPTION。
擁有 ROLE ADMIN OPTION 的人，可以將其他人編入此 ROLE。
### 合併 CREATE USER 以及 GRANT
```sql
GRANT SELECT ON clown_info TO
elsie IDENTIFIED BY 'elsiePassword';
```
## Stored Procedure、Function
- 更彈性的 SQL 語法
- 錯誤處理能力
- 合於 SQL 標準
- 程式碼封裝
- 可重覆利用
- 分離程式邏輯
- 易於維護
- 減少所需的網路頻寬
- Server 端的 upgrade 也可以嘉惠 Client 端
- 更好的安全性
### 差異
- Procedure 的 Parameter 可以定義為 IN, OUT, INOUT，而 Function 的 Parameter 則必定是 IN(系統預設，不能自行指定)。
- Function 必定有回傳值，因此必須包含一個 RETURNS 子句來定義傳回值的資料型態。
- Procedure 可以直接產生單一或多個 Result Set，但 Function 不行。
```sql
CREATE FUNCTION teamData(
@teamNumber CHAR(8)
)
RETURNS @TABLE TABLE (
teamName VARCHAR(20),
coachName VARCHAR(20),
memberName VARCHAR(20),
memberBackNumber INT
)
AS
BEGIN
INSERT INTO @TABLE (teamName, coachName, memberName, memberBackNumber)
SELECT team.teamName, coach.coachName, member.memberName, member.memberBackNumber
FROM team
INNER JOIN coach
ON team.teamNumber = coach.teamNumber
INNER JOIN member
ON team.teamNumber = member.teamNumber
WHERE team.teamNumber = @teamNumber;
RETURN (@TABLE);
END
```
### stored procedure
```sql
CREATE PROCEDURE insertMember
(
@number CHAR(8),
@memberNumber CHAR(6),
@memberName VARCHAR(20),
@memberBackNumber INT
)
AS
BEGIN
INSERT INTO member VALUES(@number,
@memberNumber,
@memberName,
@memberBackNumber);
END
```
### Trigger
```sql
CREATE TRIGGER trg_tblGrade ON tblGrade
INSTEAD OF INSERT
AS
INSERT INTO tblGrade2( StudentNo, ClassID, Grade )
SELECT StudentNo, ClassID, Grade FROM inserted
```
```sql
CREATE TRIGGER trg_tblGrade_after ON tblGrade
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @OriginalData XML;
DECLARE @NewData XML;
DECLARE @Operation char(1);
SET @OriginalData='';
SET @NewData='';
--Update ( deleted + inserted )
IF EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted)
BEGIN
SET
@OriginalData = ( SELECT * FROM deleted FOR XML RAW('GradeTable') )
SET
@NewData = ( SELECT * FROM inserted FOR XML RAW('GradeTable') )
END
--Insert ( inserted )
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
BEGIN
SET @NewData = ( SELECT * FROM inserted FOR XML RAW('GradeTable') )
END
--Delete ( deleted )
IF EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted)
BEGIN
SET @OriginalData = ( SELECT * FROM deleted FOR XML RAW('GradeTable') )
END
INSERT INTO Log(TableName, OriginalData, NewData) VALUES
('GradeTable', @OriginalData, @NewData)
```
Sign in with Wallet
Connect another wallet