---
# System prepended metadata

title: 資料庫-11410期中考筆記
tags: [DataBase]

---

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

# SQL指令區

### 建立資料庫
```sql
CREATE DATABASE 資料庫名稱
-- 可以修改路徑
ON (NAME = 資料庫名稱, FILENAME = '路徑\資料庫名稱.MDF')
```
### 建立資料表
```sql
CREATE TABLE 資料表名稱
(
欄位名稱 類別(位數) #NULL/NOT NULL(可空/不可空),
...
PRIMARY KEY(元素名稱) #主鍵
UNIQUE (元素名稱) #當候選鍵
FOREIGN KEY(欄位名稱) REFERENCES 資料表名稱(欄位名稱) #外鍵(後面)
    )
```
### CHECK用法
```sql=
CREATE TABLE 資料表名稱 (
    欄位名稱1 資料型態
        CHECK (該欄位的條件),
    欄位名稱2 資料型態,
    ...
    -- 也可以在最後統一定義 (特別是用於多個欄位的條件)
    CONSTRAINT 約束名稱_CK CHECK (涉及多個欄位的條件) 
);
```

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

### 刪除資料表
```sql
DROP TABLE 資料表名稱
```    
### 新增欄位
```sql
ALTER TABLE 資料表名稱
ADD 欄位名稱 資料型態[定義]
ADD CONSTRAINT 約束的自訂名稱 CHECK (條件); #可增加條件
```
### 新增記錄到資料表中
```sql
INSERT INTO 資料表名稱
VALUES(欄位值串列)
```
![image](https://hackmd.io/_uploads/Sy7j3RT0ex.png)

### 修改資料表中的值
```sql
UPDATE 資料表名稱
SET 欄位名稱1 = 欄位新值1,
    ...
WHERE 條件
```
### 刪除資料表的紀錄
```sql
DELETE FROM 資料表名稱
WHERE 條件
```
### 選擇欄位顯示
```sql
SELECT 欄位名稱(全部為*)(替代欄位名稱:範圍名稱 AS 替代名詞)
FROM 欄位名稱
WHERE 條件
```
![image](https://hackmd.io/_uploads/BJclu0TAge.png)
#### 模糊或範圍運算
- 相似條件 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) 主鍵的候選人，具有唯一性及最小性
### 鍵值屬性
- 各鍵關係圖
![image](https://hackmd.io/_uploads/BkTzOGp0el.png)
- 三種屬性
-- 簡單屬性(Simple Attribute)
    已經無法再切割其他有意義的單位
-- 複合屬性(Composite Attribute)
    由兩個或兩個以上的其他屬性的值所組成
-- 衍生屬性(Derived Attribute)
    可以經由某種方式的計算或推論而獲得

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

### ER圖符號表
![image](https://hackmd.io/_uploads/SyCkAf6Agl.png)

### 實體
 以名詞的形式來命名 不可為形容詞或動詞
 
- 強實體(strong entity)
    - 不需要依附其他實體而存在的實體
    - 以長方形表示
- 弱實體(weak entity)
    - 需要依賴其他實體而存在的實體
    - 雙同心長方形表示

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

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

![image](https://hackmd.io/_uploads/rJx677a0gg.png =50%x )

### 關係
指用來表達兩個實體之間所隱含的關聯性
足以說明關聯性質的「動詞」或「動詞片語」命名
![image](https://hackmd.io/_uploads/ryqN09pAxl.png =50%x)
#### 範例
![image](https://hackmd.io/_uploads/H1KUCca0eg.png =50%x)
### 關係的基數性(cardinality)
基數性代表實體能參與關係的案例數
- 分成三大類
-- 利用比率關係來表示
    1. 表示兩個實體之間的關係是1對1關係(1:1)
    ![image](https://hackmd.io/_uploads/HyBSXsTRgl.png =50%x)
    2. 一對多關係(1:M)
    ![image](https://hackmd.io/_uploads/ryFu7spAel.png =50%x)
    3. 多對多關係(M:N)
    ![image](https://hackmd.io/_uploads/BypLVjp0xg.png =50%x)

-- 雞爪圖基數性來表示
1. 強調單基數
    指一個實體參與其關係的案例數最少一個，最多也一個
    ![image](https://hackmd.io/_uploads/SJ4xSoaCex.png =50%x)
    - 範例 假設每一位主管僅能分配一台車子
    ![image](https://hackmd.io/_uploads/BkSewi60xg.png =50%x)
    - 假設每一位主管僅能分配一台車子，**並且每一台車子一定要被分配給主管**
    ![image](https://hackmd.io/_uploads/rJxXDsa0le.png =50%x)

2. 強制多基數
    指一個實體參與其關係的案例數最少一個，最多有多個
    ![image](https://hackmd.io/_uploads/rkcNIopRxx.png =50%x)
    - 範例 假設每一位主管至少要管理一位員工，也可以多位
    ![image](https://hackmd.io/_uploads/BkIDwo6Cgg.png =50%x)
    - 假設每一位主管至少要管理一位員工，也可以多位，但每一位員工只能被一位主管管理
    ![image](https://hackmd.io/_uploads/B1AYPoa0le.png =50%x)
3.  選擇單基數
    指一個實體參與其關係的案例數最少 0 個，最多有一個
    ![image](https://hackmd.io/_uploads/B1tu9j60ee.png =50%x)
4. 選擇多基數
    指一個實體參與其關係的案例數最少 0 個，最多有多個
    ![image](https://hackmd.io/_uploads/r1ro9jTClg.png =50%x)

-- 基數限制條件來表示
是指在關聯型態更進一步標示「實體」允許參與關聯的範圍
1. (1,1) 指一個實體參與其關係的案例數最少一個，最多也一個
![image](https://hackmd.io/_uploads/S161ijaRex.png =50%x)
2. (1,N) 指一個實體參與其關係的案例數最少一個，最多有多個
![image](https://hackmd.io/_uploads/H10MijpAle.png =50%x)
3. (0,1) 指一個實體參與其關係的案例數最少 0 個，最多有一個
![image](https://hackmd.io/_uploads/By2Kos6Rxg.png =50%x)

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


## 正規化
就是對一個「非正規化」的原始資料表，進行一連串的「分割」，並且分割成數個「不重複」儲存的資料表
![image](https://hackmd.io/_uploads/ByipnoaRel.png =50%x)
![image](https://hackmd.io/_uploads/rkDAnop0xe.png =50%x)

### 第一正規化(1NF)
滿足所有記錄中的屬性內含值都是基元值(Atomic Value)。即無重複項目群
![image](https://hackmd.io/_uploads/HkfPRopRll.png)
### 第二正規化(2NF)
分割資料表；亦即將「部分功能相依」的欄位「分割」出去，再另外組成「新的資料表」
![image](https://hackmd.io/_uploads/H1ARy3TRxg.png)
分成了學生資料表、成績資料表及課程資料表
![image](https://hackmd.io/_uploads/HyWQW2aCgx.png =50%x)
![image](https://hackmd.io/_uploads/rJwmbh6Ree.png =50%x)
![image](https://hackmd.io/_uploads/Hy9Qb2aRgl.png =50%x)

### 第三正規化(3NF)
各欄位與「主鍵」之間沒有「遞移相依」的關係
![image](https://hackmd.io/_uploads/S1b6Z36Clx.png =50%x)

- 經過前面正規化後的範例
![image](https://hackmd.io/_uploads/SyDkMn60ex.png =50%x)

---

## 關聯式代數
![image](https://hackmd.io/_uploads/BJ_2Mh6Cxx.png)
基本運算子所成的集合稱為「完整集合」(Complete set)
- 基本運算子 不能由其他「運算子」導出的運算子
- 非基本運算子 可以由「基本運算子」導出的運算子

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

### 投影(Project)
從關聯 R 上的投影，亦即從關聯 R 中選擇出許多「欄位」後，再重新組成一個新的關聯
- 代表符號：
- 關聯式代數：π~A~( R ) ，其中：A 為 R 中的屬性欄位
#### 範例
![image](https://hackmd.io/_uploads/H1-PB3aCxg.png =50%x)
請找出「銷售部」員工的編號、姓名？
Ans:
![image](https://hackmd.io/_uploads/rk3vB26Rxg.png =50%x)

### 聯集(Union)
關聯表 R 與關聯表 S 做「聯集」時，會重新組合成一個新的關聯表
- 關聯式代數：R ∪ S
### 卡氏積(Cartesian Product)
將兩關聯表 R 與 S 的記錄利用集合運算中的乘積運算形成新的關聯表
- 關聯式代數：R×S
![image](https://hackmd.io/_uploads/rkGTw2T0le.png =50%x)

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

分成三種合併
1. 自然合併(Natural Join)
又稱為內部合併(Inner Join)
必須在左右兩邊的關聯中找到對應值組才行
一般的結合(Join) 都是屬於此種方法
![image](https://hackmd.io/_uploads/rJwXNppCex.png =50%x)

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

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)
![image](https://hackmd.io/_uploads/SyIqr6aCxg.png =50%x)

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

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

分類：基本上，外部合併可分為以下三種合併
   1. 左外部合併(Left Outer Join，以⟕表示)
        左邊的關聯表為主，右邊的關聯表為輔
![image](https://hackmd.io/_uploads/H10irAaAee.png =50%x)
![image](https://hackmd.io/_uploads/r15nHCTAxx.png =50%x)

   2. 右外部合併(Right Outer Join ，以⟖表示)
        右邊的關聯表為主，左邊的關聯表為輔
![image](https://hackmd.io/_uploads/SkieIRaClg.png =50%x)
![image](https://hackmd.io/_uploads/Byg-URpRxx.png =50%x)

   3. 全外部合併(Full Outer Join ，以⟗表示)
        左、右邊的關聯表為主
![image](https://hackmd.io/_uploads/BJhXUA6Cxe.png =50%x)
![image](https://hackmd.io/_uploads/HJA7IC60ge.png =50%x)

# 功課複習

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