# MySQL 個人筆記

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


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

---
# 基本觀念

* 分成5種子語言(DML、TCL、DQL、DDL、DCL)
* 每個子語言又分成數個敘述(Statement)
* 每個敘述又分成多個子句(Clause)
---
# 註解文字
> --  單行註解
> # 單行註解
> /*  多行註解  */
---
# 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
> 
---
## 其他語法 (待更新)
* 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> 連接條件

> 
#### 內部連接：等值連接 - 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

> 
---
### 外部連接 - sql99
* 主表引入副表,用主表查副表
* 如果副表有匹配的值,則顯示之
* 如果副表沒有匹配的值,則會顯示NULL(可用WHERE ... IS NOT NULL語法來篩掉NULL)

> 
---
#### 外部連接：右外連接/左外連接 - sql99
* RIGHT JOIN ,則右邊會是主表
> <font color="#FF5733">RIGHT (OUTER) JOIN</font> 表2
> <font color="#FF5733">ON</font> 連接條件
> 

* LEFT JOIN ,則左邊會是主表
> <font color="#FF5733">LEFT (OUTER) JOIN</font> 表2
> <font color="#FF5733">ON</font> 連接條件
> 
---
### 交叉連接 - 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 結果集包含重複項目
> 

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

## 修改單表
> 
---
## 修改多表
> 

---
## 刪除多表
> 

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

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

---
## 庫的管理
### 庫的創建
> 
---
### 庫的修改
* **修改庫名**需要透過進入檔案總管修改資料夾名稱
因為舊語法造成許多後遺症，因此已經被刪除該語法
> 

---
## 表的創建
> 
> 

> **<font color="#D92D71">進階-創建表</font>**
> 
---
### 表的修改
> 

> <font color="#D92D71">**案例**</font>
> 

> **<font color="#D92D71">進階-刪除Col</font>**
> 

> **<font color="#D92D71">進階-加入Col時插入特定順序 FIRST/AFTER</font>**
> 

> **<font color="#D92D71">進階-加入到第一row</font>**


---
### 表的刪除
> <font color="#D92D71">**案例**</font>
> 

> <font color="#D92D71">**進階**</font>
> 

> <font color="#D92D71">**查詢庫中所有的表**</font>
> 
---
### 表的複製
> <font color="#D92D71">**僅複製表的Col**</font>
> **案例：author表的所有Col**
> 

> <font color="#D92D71">**複製表的Col+全部資料**</font>
> **案例：複製author表全部資料**
> 

> <font color="#D92D71">**複製表的Col+部分資料**</font>
> **案例：只複製nation欄位顯示'中國'的Row**
> 
>
> 但是會顯示原有的全部row數,沒資料的會顯示null,
> 因此如果不想顯示null,需先另外刪除顯示null的row

> <font color="#D92D71">**只複製表的部分Col，不含資料**</font>
> **案例：只複製id,au_name, 在WHERE設定不可能達成的條件**
> 
> 

### 一般建新庫、表的流程與寫法
> 
----

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

| 整數類型 | 字節 | 範圍 |
|:------------ |:-------:|:----------------------------------- |
| 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>**
> 

> **<font color="#D92D71">預設長度</font>**：
> 

ZEROFILL
>
> 

ZEROFILL之後,默認為無符號類型
* 小數：點數

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

* 小數：浮點數

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

M為全部幾位數,包含小數部位
D為小數後幾位數
如果超出,則報out of rang異常,並插入臨界值


---
### 字符型
* 較短文本：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

* Enum 類型
* 用於保存枚舉
不分大小寫,全部一律顯示小寫

>
>
>

* Set 類型
* 用於保存集合
不分大小寫,全部一律顯示小寫

---
### 日期型


查看時區：顯示SYSTEM

更改時區

更改時區後
(案例時區為+8:00,GMT+8)更改時區後,t2時間受到影響,可見TIMESTAME語法會受時區影響


---

## 常見約束

+一致性

六大約束


外鍵約束 在列級約束中不生效,但也不報錯
CEHCK約束 MySQL中不生效,但也不報錯
添加列級約束(陸)

查看表中所有索引,包含主鍵,外鍵,唯一鍵


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

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


---
# 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'@'%';