# 資料庫 114/10 期中考筆記 廖元勳教授

# SQL指令區

### 建立資料庫

CREATE DATABASE 資料庫名稱
-- 可以修改路徑
ON (NAME = 資料庫名稱, FILENAME = '路徑\資料庫名稱.MDF')

### 建立資料表

CREATE TABLE 資料表名稱
(
欄位名稱 類別(位數) #NULL/NOT NULL(可空/不可空),
...
PRIMARY KEY(元素名稱) #主鍵
UNIQUE (元素名稱) #當候選鍵
FOREIGN KEY(欄位名稱) REFERENCES 資料表名稱(欄位名稱) #外鍵(後面)
    )

### CHECK用法

CREATE TABLE 資料表名稱 (
    欄位名稱1 資料型態
        CHECK (該欄位的條件),
    欄位名稱2 資料型態,
    ...
    -- 也可以在最後統一定義 (特別是用於多個欄位的條件)
    CONSTRAINT 約束名稱_CK CHECK (涉及多個欄位的條件) 
);

#### 常用類別:
- 純數字: INT
- 英數夾雜: CHAR
- 特殊及國家語言的字元: NVARCHAR

### 刪除資料表

DROP TABLE 資料表名稱

### 新增欄位

ALTER TABLE 資料表名稱
ADD 欄位名稱 資料型態[定義]
ADD CONSTRAINT 約束的自訂名稱 CHECK (條件); #可增加條件

### 新增記錄到資料表中

INSERT INTO 資料表名稱
VALUES(欄位值串列)


### 修改資料表中的值

UPDATE 資料表名稱
SET 欄位名稱1 = 欄位新值1,
    ...
WHERE 條件

### 刪除資料表的紀錄

DELETE FROM 資料表名稱
WHERE 條件

### 選擇欄位顯示

SELECT 欄位名稱(全部為*)(替代欄位名稱:範圍名稱 AS 替代名詞)
FROM 欄位名稱
WHERE 條件

#### 模糊或範圍運算
- 相似條件 LIKE'相似條件'
- 集合條件 IN(資料)
- 範圍條件 BETWEEN ... AND ...

##### LIKE模糊相似條件
1. WHERE 姓名 LIKE '王%' -> 查詢姓名開頭為'王'的所有資料
2. WHERE 姓名 LIKE '%王' -> 查詢姓名結尾為'王'的所有資料
3. WHERE 姓名 LIKE '%王%' -> 查詢姓名中含有'王'的所有資料
4. WHERE 姓名 LIKE '王__' ->查詢姓名中姓'王'且3個字的所有資料

## 運算子
| 運算子 | 功能 |
| ------- | ---------- |
| +(加) | 相加 |
| -(減) | 相減 |
| **(乘)* | 相乘 |
| /(除) | 相除 |
| %(餘除) | 相除取餘數 |

## 聚合函數 可放入SELECT 或 HAVING 指令
| 聚合函數 | 說明 |
| --------------- | ------------------------------ |
| Count(*) | 計算個數函數 |
| Count(欄位名稱) | 計算該欄位不具NULL的值列的個數 |
| Avg | 平均函數 |
| Sum | 總和函數 |
| Max | 最大值函數 |
| Min | 最小值函數 |

## WHERE v.s. HAVING

| | WHERE | HAVING |
| -------- | ---------------- | ------------- |
| 執行順序 | GROUP BY 之前 | GROUP BY 之後 |
| 聚合函數 | 不能使用聚合函數 | 可以使用 |

## 排序及排名次函數表
- 補充: ASC : Ascending(遞增) _ Desc : Descending(遞減)

| 指令 | 說明 |
| ------------------| ---------------------------- |
| ORDER BY 數量 Asc | Asc <- 可以省略(由小至大) |
| ORDER BY 數量 Desc | Desc <- 不可以省略(由大至小) |

---

# 重點整理
## SQL之鍵
### 鍵種(沒在罵人)
- 主鍵(Primary Key, PK) 具有 **唯一性** 的鍵，在欄位名稱中要加底線，且不可重複,不可NULL
- 外鍵(Foreign Key, FK) 由父關聯表嵌入的鍵，且外鍵會在父關聯表中為主鍵，用來確定資料完整性
- 複合鍵(Composite Key) 由兩個或兩個欄位以上組成的主鍵
- 候選鍵(Candidate Key) 主鍵的候選人，具有唯一性及最小性
### 鍵值屬性
- 各鍵關係圖
- 三種屬性
-- 簡單屬性(Simple Attribute)
已經無法再切割其他有意義的單位
-- 複合屬性(Composite Attribute)
由兩個或兩個以上的其他屬性的值所組成
-- 衍生屬性(Derived Attribute)
可以經由某種方式的計算或推論而獲得

## ER Model 實體關係圖
### 實體關係模式(Entity-Relation Model)
描述實體與實體之間關係的工具
- 三種關係
-- 一對一關係
-- 一對多關係
-- 多對多關係

### ER圖符號表

### 實體
以名詞的形式來命名 不可為形容詞或動詞

- 強實體(strong entity)
- 不需要依附其他實體而存在的實體
- 以長方形表示
- 弱實體(weak entity)
- 需要依賴其他實體而存在的實體
- 雙同心長方形表示

### 屬性
用來描述實體的性質(Property)
- 簡單屬性(simple attribute)
已經不能再細分為更小單位的屬性
- 單值屬性(single-valued attribute)
屬性中只會存在一個單一值
(簡單屬性跟單值屬性圖相同)
- 複合屬性(composite attribute)
由兩個或兩個以上的其他屬性的值所組成，並且代表未來該屬性可以進一步作切割
- 鍵屬性(Key attribute)
該屬性的值在某個環境下具有唯一性
- 多值屬性(Multi-valued attribute)
屬性中會存在多個數值
- 衍伸屬性(Derived attribute)
由其他屬性或欄位計算而得的屬性

#### 範例(點開放大)


### 關係
指用來表達兩個實體之間所隱含的關聯性
足以說明關聯性質的「動詞」或「動詞片語」命名
#### 範例
### 關係的基數性(cardinality)
基數性代表實體能參與關係的案例數
- 分成三大類
-- 利用比率關係來表示
1. 表示兩個實體之間的關係是1對1關係(1:1)
2. 一對多關係(1:M)
3. 多對多關係(M:N)

-- 雞爪圖基數性來表示
1. 強調單基數
指一個實體參與其關係的案例數最少一個，最多也一個
- 範例 假設每一位主管僅能分配一台車子
- 假設每一位主管僅能分配一台車子，**並且每一台車子一定要被分配給主管**

2. 強制多基數
指一個實體參與其關係的案例數最少一個，最多有多個
- 範例 假設每一位主管至少要管理一位員工，也可以多位
- 假設每一位主管至少要管理一位員工，也可以多位，但每一位員工只能被一位主管管理
3. 選擇單基數
指一個實體參與其關係的案例數最少 0 個，最多有一個
4. 選擇多基數
指一個實體參與其關係的案例數最少 0 個，最多有多個

-- 基數限制條件來表示
是指在關聯型態更進一步標示「實體」允許參與關聯的範圍
1. (1,1) 指一個實體參與其關係的案例數最少一個，最多也一個
2. (1,N) 指一個實體參與其關係的案例數最少一個，最多有多個
3. (0,1) 指一個實體參與其關係的案例數最少 0 個，最多有一個

### 關係的分支度(Degree)
指參與關係的實體的個數，稱之為「分支度」(Degree)
分為: 一元關係、二元關係、三元關係

## 正規化
就是對一個「非正規化」的原始資料表，進行一連串的「分割」，並且分割成數個「不重複」儲存的資料表

### 第一正規化(1NF)
滿足所有記錄中的屬性內含值都是基元值(Atomic Value)。即無重複項目群
### 第二正規化(2NF)
分割資料表；亦即將「部分功能相依」的欄位「分割」出去，再另外組成「新的資料表」
分成了學生資料表、成績資料表及課程資料表

### 第三正規化(3NF)
各欄位與「主鍵」之間沒有「遞移相依」的關係

- 經過前面正規化後的範例

---

## 關聯式代數
基本運算子所成的集合稱為「完整集合」(Complete set)
- 基本運算子 不能由其他「運算子」導出的運算子
- 非基本運算子 可以由「基本運算子」導出的運算子

### 限制(Restrict)
又稱為選擇操作(Select Operation)，「選擇運算子」含有兩個參數
(1) 選取條件(Predicate) P
(2) 關聯表名稱 R
從 R 中選出符合條件 P 的值組是指在關聯 R 中選擇滿足條件 P 的所有值組
- 代表符號：σ (sigma)
- 關聯式代數：σ~P~( R )

### 投影(Project)
從關聯 R 上的投影，亦即從關聯 R 中選擇出許多「欄位」後，再重新組成一個新的關聯
- 代表符號：
- 關聯式代數：π~A~( R ) ，其中：A 為 R 中的屬性欄位
#### 範例
請找出「銷售部」員工的編號、姓名？
Ans:

### 聯集(Union)
關聯表 R 與關聯表 S 做「聯集」時，會重新組合成一個新的關聯表
- 關聯式代數：R ∪ S
### 卡氏積(Cartesian Product)
將兩關聯表 R 與 S 的記錄利用集合運算中的乘積運算形成新的關聯表
- 關聯式代數：R×S

### 差集(Difference)
關聯 R 差集關聯 S 之後的結果，則為關聯 R 減掉 RS 兩關聯共同的值組
- 關聯式代數：R – S
### 合併(Join)
將兩關聯表 R 與 S 依合併條件合併成一個新的關聯表R3，假設 P 為合併條件，以R ⋈ ~p~S表示此合併運算
- 關聯式代數：R ⋈ ~p~S

分成三種合併
1. 自然合併(Natural Join)
又稱為內部合併(Inner Join)
必須在左右兩邊的關聯中找到對應值組才行
一般的結合(Join) 都是屬於此種方法

2. θ - 合併(Theta Join)
以「等於」以外的條件為基礎來合併兩個關聯的運算
- 語法：(A×B) WHERE A.X θ B.Y
其中 A,B 為無共同屬性的關聯，A 具有屬性 X， 而 B 具有屬性 Y
θ 合併的運算子：=、＜、≦、＞、≧、≠
** 注意：相同名稱的欄位會同時出現在運算結果的表格中。亦即重複欄位，會出現兩次
- 範例

4. 對等合併(Equi-Join) 是θ - 合併的特例
- 若θ 為「等於比較」的狀況時，θ -Join 稱為對等合併(Equi-Join)
目前都是Equi-Join 為主
- 作法：它是從關聯 R 與 S 的卡氏積中，分別選取關聯 R 的 C 屬性值等於與關聯 S 的 C 屬性值，即等位合併為： R~R.c~=~S.c~S

- 對應SQL 指令：
透過 SELECT 指令 WHERE 部分的等式
例如：From R, S
　　　 Where (R.c=S.c)

### 交集
指關聯 R 與關聯 S 做「交集」時，將原來在兩個關聯式中都有出現的值組(記錄) 組合在一起，
- 關聯式代數：R ∩ S
### 除法
- 關聯式代數：R ÷ S

## 外部合併
當在進行合併(Join)時不管紀錄是否符合條件，都會被列出某一個資料表的所有記錄時，則稱為「外部合併」

分類：基本上，外部合併可分為以下三種合併
1. 左外部合併(Left Outer Join，以⟕表示)
左邊的關聯表為主，右邊的關聯表為輔

2. 右外部合併(Right Outer Join ，以⟖表示)
右邊的關聯表為主，左邊的關聯表為輔

3. 全外部合併(Full Outer Join ，以⟗表示)
左、右邊的關聯表為主

# 功課複習

## ch4
1.請依下列的述敘來畫出完整的實體-關係圖(ERD)：
(1) 「員工實體」和「產品實體」之間有「銷售」的關係。
(2) 「員工實體」有編號、姓名、生日、年齡、地址、電話及專長等屬性, 其中「編號」為鍵屬性、「年齡」需要利用生日導出來, 而學生有兩個以上的「專長」。「產品實體」有產品編號、產品名稱、定價等屬性, 「產品編號」為鍵屬性。
2.試根據以下E-R模式，將關係的動詞填入，並簡述其意義所在。
- R1: 選課
學生有選到這門課就會把學生資訊連結到課程資料中
- R2: 排程
每個課程要依照每一週的時間去做安排，避免衝堂
- R3: 授課
每個課程都會有授課老師，所以會連結教師資料

## ch5

## ch6
## ch8