---
# System prepended metadata

title: MySQL 個人筆記

---

# MySQL 個人筆記

台灣是「直行橫列」，中國是「直列橫行」

![](https://i.imgur.com/Lb8dm0t.png =300x)

---
# 待解問題
DDL的TRUNCATE 這是屬於DML?
DDL-DROP /DDL-Truncte?
DML-Delet

---
# 基本觀念
![](https://i.imgur.com/oWBan9X.png =700x)

* 分成5種子語言(DML、TCL、DQL、DDL、DCL)
* 每個子語言又分成數個敘述(Statement)
* 每個敘述又分成多個子句(Clause)
![](https://i.imgur.com/iaUaTJC.png)
---
# 註解文字
> --&emsp; 單行註解
> #&emsp;單行註解
> /* &emsp;多行註解&emsp; */
---
# DQL (Data Query Language)資料查詢語言
## SELECT
* SELELCT敘述有7個子句：
    SELELCT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIIMIT

**2. WHERE 篩選條件**

**<font color="#D92D71">進階用法 1：使用in可設置限制的條件</font>**

> <font color="#FF5733">WHERE</font> COLUMN_A <font color="#FF5733">IN</font> (值1, 值2,...)

**<font color="#D92D71">進階用法 2：LIKE 模糊查詢</font>**

> <font color="#FF5733">WHERE</font> COLUMN_A <font color="#FF5733">LIKE</font> (值1)
> #用'<font color="#FF5733"> % </font>','<font color="#FF5733"> _ </font>' 描述值1,去進行模糊查詢

**<font color="#D92D71">進階用法 3：BETWEEN ... AND</font>**

> <font color="#FF5733">WHERE</font> COLUMN_A <font color="#FF5733">BETWEEN</font> 值1 <font color="#FF5733">AND</font> 值2


**3. ORDER BY 排序**
> <font color="#FF5733">ORDER BY</font> 欄1 排序方式
> <font color="#FF5733">ASC</font> (升序)/ <font color="#FF5733">DESC</font> (降序)

**4. GROUP BY 分組查詢**
**5. HAVING 分組查詢後篩選條件**
* 須配合GROUP BY使用

**6. FROM 指定資料來源**
**7. LIMIT 分頁查詢**
* (OFFSET,SIZE)
    (忽略頁數,顯示條目個數)
    (顯示條目的起始索引,從0開始,顯示條目個數)
    
    如果從第一條資料開始顯示,則可以以忽略OFFSET
> ![](https://i.imgur.com/FDK9vcz.png =500x)
---
## 其他語法 (待更新)
* AND/ OR/ NOT (待更新)
* ANY SOME (待更新)
* EXISTS (待更新)
* CASE 
    WHEN "條件式" 
    THEN "條件為成立" 
    ELSE "條件為不成立" 
    END
* IF("條件式",真,假)(待更新)
    SELECT "欄位"
    FROM "表格名稱"
    WHERE 欄位=IF("條件式",真,假) 

## 連接

### 內部連接 - sql92
* 內連接基於連接謂詞將兩張表（如 A 和 B）的列組合在一起，產生新的結果表。
---
#### 內部連接：等值連接 - sql92

> <font color="#FF5733">SELECT</font> 表1欄1 <font color="#FF5733">,</font> 表2欄1
> <font color="#FF5733">FROM</font> &ensp;   表1 <font color="#FF5733">,</font> 表2
> <font color="#FF5733">WHERE</font> 表1欄2 <font color="#FF5733">=</font> 表2欄2 具關係之兩表

---
#### 內部連接：非等值連接 - sql92

> <font color="#FF5733">SELECT</font> 表1欄1 <font color="#FF5733">,</font> 表2欄1
> <font color="#FF5733">FROM</font> 表1 <font color="#FF5733">,</font> 表2
> <font color="#FF5733">WHERE</font>  表1欄2 <font color="#FF5733">BETWEEN</font> 表2條件 <font color="#FF5733">AND</font> 表2條件 

---
#### 內部連接：自我連結(Self Join) - sql92

---
### 內部連接 - sql99
* 表的順序可以交換
* 內連接的結果為多表的交集
* n表至少需要n-1個連接條件
> <font color="#FF5733">(INNER) JOIN</font>  表2
> <font color="#FF5733">ON</font>   連接條件

> ![](https://i.imgur.com/7VR4F0J.png =300x)
#### 內部連接：等值連接 - sql99
---
#### 內部連接：非等值連接 - sql99
---
#### 內部連接：自我連結(Self Join) - sql99
---
#### 內部連接：相等連結(Equi-join) - sql99
* 語法後有等於運算子
---
#### 內部連接：不相等連結(Non-Equi-join) - sql99
* 語法後無等於運算子

> <font color="#FF5733">SELECT</font> 表1欄1 <font color="#FF5733">,</font> 表2欄1
> <font color="#FF5733">FROM</font> 表1 
> <font color="#FF5733">JOIN</font>  表1欄2 <font color="#FF5733">BETWEEN</font> 表2條件欄 <font color="#FF5733">AND</font> 表2條件欄 
---
#### 內部連接：自然連結(Natual join) - sql99
* 依2資料來源 的 相同的欄位名連結
> <font color="#FF5733">NATURAL JOIN</font>  表2

> ![](https://i.imgur.com/ziN4Wr3.png =100x)
---
### 外部連接 - sql99
 * 主表引入副表,用主表查副表
 * 如果副表有匹配的值,則顯示之
 * 如果副表沒有匹配的值,則會顯示NULL(可用WHERE ... IS NOT NULL語法來篩掉NULL)

> ![](https://i.imgur.com/nSrasoZ.png =700x)
---
#### 外部連接：右外連接/左外連接 - sql99
 * RIGHT JOIN ,則右邊會是主表
> <font color="#FF5733">RIGHT (OUTER) JOIN</font>  表2
> <font color="#FF5733">ON</font>  連接條件
> ![](https://i.imgur.com/G2kgbxb.png =100x)

 * LEFT JOIN ,則左邊會是主表 
> <font color="#FF5733">LEFT (OUTER) JOIN</font>  表2
> <font color="#FF5733">ON</font>  連接條件
> ![](https://i.imgur.com/ziN4Wr3.png =100x)
---
### 交叉連接 - sql99
 * 交叉連接結果為：表1所有欄位x表2有欄位
 * 類似於笛卡爾乘積
> <font color="#FF5733">CROSS JOIN</font>  表2

---

### 全外連接 (Full Join)
 * MySQL未支持
 * 全外連接結果為：
 內連結的結果+
 表1中有但表2沒有的結果+
 表2中有但表1沒有的結果
 
 
---
## 子查詢
<font color="#D92D71">1. **分類：按照出現位置**</font>
    SELECT後面：僅支持 <font color="#FF5733">標量子查詢</font>
    FROM後面：<font color="#FF5733">表子查詢</font>
    WHERE/ HAVING 後面：<font color="#FF5733">標量子查詢、Col子查詢、Row子查詢</font>
    EXISTS 後面：<font color="#FF5733">標量子查詢</font>

<font color="#D92D71">2. **分類：按結果集的行列**</font>
    標量子查詢(單row子查詢)：<font color="#FF5733">結果集為一Row一 Col</font>
    Col子查詢(多row子查詢)：<font color="#FF5733">結果集為多Row一Col</font>
    Row子查詢：<font color="#FF5733">結果集為多Row多Col</font>
    表子查詢：<font color="#FF5733">結果集為多Row多Col</font>

---

## 聯合查詢 UNION
* 要求多條查詢語句的查詢Col數是一致的
* 要求多條查詢語句的查詢Col數的每一Col的類型和順序為最好一致
* UNION 會自動去除另一個表的重複項
* UNION ALL 結果集包含重複項目
> ![](https://i.imgur.com/2cxT7LR.png =450x)


---
# DML (Data Manipulation Language) 資料操作語言

## 修改單表
> ![](https://i.imgur.com/KAtRPzo.png =280x)
---
## 修改多表
> ![](https://i.imgur.com/Rs2MXly.png =400x)

---
## 刪除多表
> ![](https://i.imgur.com/4n8aM6w.png =480x)

---
# DDL (Data Definition Language) 資料定義語言

| 英文     | 中文           |
| -------- |:-------------- |
| CREATE   | 新建物件       |
| ALTER    | 修改物件       |
| DROP     | 移除物件       |
| TRUNCATE | 截斷物(待更新) |

---
## 庫的管理
### 庫的創建
> ![](https://i.imgur.com/1zMRjOn.png =350x)
---
### 庫的修改
* **修改庫名**需要透過進入檔案總管修改資料夾名稱
    因為舊語法造成許多後遺症，因此已經被刪除該語法
> ![](https://i.imgur.com/xbB1HZ4.png =350x)

---
## 表的創建
> ![](https://i.imgur.com/DjBHAoe.png =350x)
> ![](https://i.imgur.com/OvPfYK7.png =350x)

> **<font color="#D92D71">進階-創建表</font>**
> ![](https://i.imgur.com/J5I112B.png =330x)
---
### 表的修改
> ![](https://i.imgur.com/vPo4dwt.png)

> <font color="#D92D71">**案例**</font>
> ![](https://i.imgur.com/Q28qcvn.png =600x)

> **<font color="#D92D71">進階-刪除Col</font>**
> ![](https://i.imgur.com/V8AN7kW.png =470x)

> **<font color="#D92D71">進階-加入Col時插入特定順序 FIRST/AFTER</font>**
> ![](https://i.imgur.com/m4900fj.png =450x)

> **<font color="#D92D71">進階-加入到第一row</font>**
![](https://i.imgur.com/Cl5KnmF.png =500x)


![](https://i.imgur.com/LWOLSm1.png =300x)

---
### 表的刪除
> <font color="#D92D71">**案例**</font>
> ![](https://i.imgur.com/7aJ0k9u.png =250x)

> <font color="#D92D71">**進階**</font>
> ![](https://i.imgur.com/sdKewJj.png =300x)

> <font color="#D92D71">**查詢庫中所有的表**</font>
> ![](https://i.imgur.com/dEhHe5k.png =130x)
---
### 表的複製
> <font color="#D92D71">**僅複製表的Col**</font>
> **案例：author表的所有Col** 
> ![](https://i.imgur.com/z1b0t3Q.png =350x)

> <font color="#D92D71">**複製表的Col+全部資料**</font>
> **案例：複製author表全部資料**
> ![](https://i.imgur.com/q6OZUpC.png =250x)

> <font color="#D92D71">**複製表的Col+部分資料**</font>
> **案例：只複製nation欄位顯示'中國'的Row**
> ![](https://i.imgur.com/d9D15Zi.png =250x)
> 
> 但是會顯示原有的全部row數,沒資料的會顯示null,
> 因此如果不想顯示null,需先另外刪除顯示null的row
![](https://i.imgur.com/O78grVs.png =200x)

> <font color="#D92D71">**只複製表的部分Col，不含資料**</font>
> **案例：只複製id,au_name, 在WHERE設定不可能達成的條件**
> ![](https://i.imgur.com/HDg3yCC.png =200x)
> ![](https://i.imgur.com/Pmnt86l.png =200x)

### 一般建新庫、表的流程與寫法
> ![](https://i.imgur.com/AGg1M1F.png =350x)
----

## 常見數據類型
### 數值型
* 整型

| 整數類型     |  字節   | 範圍                                |
|:------------ |:-------:|:----------------------------------- |
| Tinyint      | 占1字節 | 有符號-128~127 or 無符號0~255       |
| Smallint     | 占2字節 | 有符號-32768~32767 or 無符號0~65535 |
| Mediumint    | 占3字節 | unchecked                           |
| Int、Integer | 占4字節 | unchecked                           |
| Biting       | 占8字節 | unchecked                           |

> **<font color="#D92D71">新增表單及設定col屬性有無符號</font>**
> ![](https://i.imgur.com/quubBXE.png =400x)

> **<font color="#D92D71">預設長度</font>**：
> ![](https://i.imgur.com/J9ZKs1o.png =250x)

ZEROFILL
>![](https://i.imgur.com/Dyfb4EC.png =150x)
> ![](https://i.imgur.com/psDuIbu.png =250x)

![](https://i.imgur.com/DaYaU89.png =600x)
ZEROFILL之後,默認為無符號類型
* 小數：點數

|    浮點數類型    |  字節   | 範圍                                                       |
|:--------:|:-------:|:------ |
|  Float(M,D)   | 占4字節 | +-很大 |
|  Double(M,D)  | 占8字節 | +-很大 |

* 小數：浮點數

|  定點數類型  | 字節 | 範圍 |
|:--------:|:-------:|:------ |
|  DEC(DECMAL/ M,D)  | M+2 | 最大取範圍值與double相同,給定Decimal的有效取範圍由M和D決定 |

M為全部幾位數,包含小數部位
D為小數後幾位數
如果超出,則報out of rang異常,並插入臨界值
![](https://i.imgur.com/PA5Y0Gk.png =600x)

![](https://i.imgur.com/wu3KmgN.png =400x)

---
### 字符型
* 較短文本：char、varchar
* 較長文本：text、blob(較長的二進位數據)


| 字符串類型 | 最多字符數 | 描述與儲存需求 |
|:----------:|:----------:|:--------------:|
|  Char(M)   |     M      | M為0~255的整數 |
| Varchar(M) |     M      | M為0~255的整數 |

Char 固定字符數
Varchar 可變長度字符(不超過最大字符數的前提,按實際字符數給出字符數)

Varchar 較節省空間,但效率較低
Char比較耗費空間,但是效率較高

Char可以省略M,默認為1
Varchar

* 位類型

| 位類型 | 字節 |      範圍      |
|:------:|:----:|:--------------:|
| Bit(M) | 1~8  | Bit(1)~ Bit(8) |


* 較短二進制數據
可使用Binary, varbinary
![](https://i.imgur.com/l9NXb4O.png =450x)


* Enum 類型
* 用於保存枚舉
不分大小寫,全部一律顯示小寫
![](https://i.imgur.com/WMmCWbd.png =450x)

>![](https://i.imgur.com/2F5dYqR.png =300x)
>![](https://i.imgur.com/TSgJnrI.png =340x)
>![](https://i.imgur.com/gbz5qrf.png =80x)

* Set 類型
* 用於保存集合
不分大小寫,全部一律顯示小寫
![](https://i.imgur.com/YdDY7Ey.png =400x)
![](https://i.imgur.com/fNmbV0F.png =300x)
![](https://i.imgur.com/pgSEp19.png =350x)
![](https://i.imgur.com/z18qvYi.png =90x)

---
### 日期型

![](https://i.imgur.com/cC1CqRb.png =500x)
![](https://i.imgur.com/7WuiGL9.png =350x)
![](https://i.imgur.com/VmQjVJN.png =220x)
![](https://i.imgur.com/y9C72sy.png =400x)
![](https://i.imgur.com/X9s9W3r.png =300x)


查看時區：顯示SYSTEM
![](https://i.imgur.com/uC1Y6r7.png =300x)
![](https://i.imgur.com/kPSCe9B.png =200x)

更改時區
![](https://i.imgur.com/RpdGGuv.png =300x)
![](https://i.imgur.com/hKDjU8I.png =200x)

更改時區後
(案例時區為+8:00,GMT+8)更改時區後,t2時間受到影響,可見TIMESTAME語法會受時區影響
![](https://i.imgur.com/VvJhFd7.png =300x)

![](https://i.imgur.com/eO3BpWK.png =200x)
![](https://i.imgur.com/ydxiMBw.png =550x)

---

## 常見約束
![](https://i.imgur.com/u0e4slD.png =250x)

![](https://i.imgur.com/T1aH3PF.png =500x)
+一致性

六大約束
![](https://i.imgur.com/7zUGDoy.png =650x)

![](https://i.imgur.com/BaMcLxr.png =460x)

外鍵約束 在列級約束中不生效,但也不報錯 
CEHCK約束 MySQL中不生效,但也不報錯
添加列級約束(陸)
![](https://i.imgur.com/qbC8xJd.png =600x)

查看表中所有索引,包含主鍵,外鍵,唯一鍵
![](https://i.imgur.com/ZlwBvkL.png =250x)


![](https://i.imgur.com/KQY14Ey.png =700x)

table：索引處的表
Non_unique：是否具有唯一性 0=true 1=false
Key_name：索引名
Seq_in_index：序列值
Column_name：代表索引是為了哪一個列來設置的

主鍵,外鍵,唯一鍵 會自動生成索引


![](https://i.imgur.com/Hn9gVOJ.png =350x)

---
# TCL (Transaction Control Language) 交易控制語言
* 用以控制資料庫中的交易
* 交易內容是由多個SQL敘述所組成
* 多個DML敘述在商業邏輯上，常控制成: 要就全部執行成功，否則就全部執行失敗(或不執* 行)
* 成功時送交(Commit)，失敗時還原(Rollback)
* MySQL只有InnoDB引擎支援交易控制
* 交易控制開始時，DML敘述會產生列鎖定(Row Locking

---
## set autocommit: 啟用/停用交易控制模式。是全局設定，之後的所有敘述都會受影響
0 / off:啟用交易控制模式
1 / on:停用交易控制模式(預設)
1. 變數autocommit的意義是自動送交，所以設為0或off才是啟用交易控制(手動控制)
2. set autocommit敘述只會影響當前連線，其他連線不會受影響
3. 通常交易控制結束後，會馬上改回1或on，保持自動送交
4. 可用右邊敘述查詢目前變數autocommit之值

---
## start transaction: 開啟單一交易控制。只有當前範圍內敘述會受影響
1. 配合commit/rollback使用
2. 從start transaction至commit/rollback稱為一個單一交易
3. 執行到commit/rollback時，就會結束此單一交易

---
## commit: 送交，配合start transaction使用時，會結束交易
1. 需先執行set autocommit = 0或start transaction，commit敘述才有意義
2. 配合start transaction使用時，同時會結束交易

---
## rollback: 還原，配合start transaction使用時，會結束交易
儲存點識別名：
    欲還原的儲存點
    此儲存點是經由savepoint敘述所設定
    
1. 需先執行set autocommit = 0或start transaction，rollback敘述才有意義
2. 配合start transaction使用時，同時會結束交易

---
## savepoint: 設定一個儲存點。rollback可指定還原至儲存點
儲存點識別名：
	儲存點的識別名，自訂但不可重複，且區分大小寫
	rollback敘述用此名稱還原至此點
1. 配合rollback使用才有意義


on Update / on Delete 
說明
動作: 對應動作，可以是以下4種之一
restrict(禁止): 被參考端禁止修改/刪除。預設的動作
cascade(連動): 參考端的資料Row會連動被修改/刪除
set null (設為空值): 將參考端的值設成null
**no action: 原意為無動作。但在MySQL中等同restrict**

---

# DCL (Data Control Language) 資料控制語言
用以控制使用者(User)對資料庫各物件的權限

使用者帳號(User Account)
說明: 連線時所輸入的使用者名(Username)，是使用者帳號的一部分
組成: 使用者名@使用者IP或主機名
EX. william@192.168.43.5
使用者william可以從IP:192.168.43.5連線
另可用%表示不限定某段IP，EX. william@192.168.43.%、william@%
Workbench: 可依下頁方式新增使用者帳號

---
## grant
grant 權限1, ..,權限N
on [資料庫.]資料表
to 使用者帳號1, .., 使用者帳號N
[with grant option]

權限: 欲授予給使用者的權限。請參考 https://dev.mysql.com/doc/refman/8.0/en/grant.html
資料庫/資料表: 可操作的對象。可用星號(*)表示全部
	              EX. EXAMPLE.* 表示資料庫EXAMPLE底下的全部資料表
使用者帳號: 得到權限的使用者帳號，EX. william@192.168.43.5
with grant option: 授權給其他使用者的權限

範例
grant all
on *.*
to 'william'@'192.168.43.5'
with grant option;

---
## revoke
revoke 權限1, ..,權限N
on [資料庫.]資料表
from 使用者帳號1, .., 使用者帳號N

-- 僅用來撤銷授權給其他使用者的權限
revoke grant option
on [資料庫.]資料表
from 使用者帳號1, .., 使用者帳號N

權限: 欲從使用者移除的權限。請參考 https://dev.mysql.com/doc/refman/8.0/en/grant.html
資料庫/資料表: 撤銷操作的物件。可用星號(*)表示全部
	              EX. EXAMPLE.* 表示資料庫EXAMPLE底下的全部資料表
使用者帳號: 被撤銷權限的使用者帳號，EX. william@192.168.43.5

範例-revoke敘述1
-- 從使用者william撤銷所有操作物件的權限
revoke all
on *.*
from 'william'@'192.168.43.5';

範例-revoke敘述2
-- 從使用者william撤銷授權給其他使用者的權限
revoke grant option
on *.*
from 'lee'@'%';
















