---
# System prepended metadata

title: MySQL-2

---

# MySQL-2

## 高級數據操作

### 增 - 主鍵衝突(Duplicate key)
> #### 基本插入數據語法
> `insert into tableName [(字段列表)] values (值列表)`

> - 主鍵衝突: 插入數據時, 對應的主鍵已經存在
```sql
mysql> -- 表內有一筆數據, ID 為主鍵, 且自增長
mysql> select * from my_pri;
+----+-------+
| id | name  |
+----+-------+
|  1 | GODJJ |
+----+-------+
1 row in set (0.00 sec)

mysql> show create table my_pri;
... 
| my_pri | CREATE TABLE `my_pri` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
...

mysql> -- 當我插入重複主鍵的數據時, 當然會失敗
mysql> insert into my_pri values (1, 'GEAR');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
```

> - 解決辦法1. 更新
>   `insert into tableName [(字段列表)] values (值列表) on duplicate key update 字段 = newValue`
>   - 會有兩行受影響: insert 與 update
```sql
mysql> insert into my_pri values (1, 'GEAR') on duplicate key update name = 'GEAR';
Query OK, 2 rows affected (0.00 sec)  -- 兩行受影響

mysql> select * from my_pri;
+----+------+
| id | name |
+----+------+
|  1 | GEAR |
+----+------+
1 row in set (0.00 sec)
```

> - 解決辦法2. 替換
>   `replace into tableName [(字段列表: 含主鍵)] values (值列表)`
>   - 兩行受影響, 因為先刪除數據再新增數據
```sql
mysql> select * from my_pri;
+----+------+
| id | name |
+----+------+
|  1 | GEAR |
|  2 | BEBE |  -- 有一筆 2
+----+------+
2 rows in set (0.00 sec)

mysql> replace into my_pri values (2, 'HAHA');  -- 更新成功
Query OK, 2 rows affected (0.01 sec)  -- 兩行受影響

mysql> select * from my_pri;
+----+------+
| id | name |
+----+------+
|  1 | GEAR |
|  2 | HAHA | -- 成果
+----+------+
2 rows in set (0.00 sec)
```
> - 兩個語句都是在衝突時才會影響兩行
```sql
mysql> -- 當沒有衝突時, 都只執行插入
mysql> insert into my_pri values (null, 'HOHO') on duplicate key update name = 'HOHO';
Query OK, 1 row affected (0.01 sec)

mysql> replace into my_pri values (null, 'WOWO');
Query OK, 1 row affected (0.00 sec)
```

### 增-蠕蟲複製
> - 有時想要測試正在線上跑的數據, 但又怕影響到線上的服務器, 此時就可以考慮使用蠕蟲複製
> - 從已有數據的表中去獲取數據, 然後再自我複製, 已達到數據倍增的效果
> - 可以迅速讓表中數據快速膨脹到想要達到的量級, 以利測試壓力及效率

> #### 複製表結構
> `create table tableName like databaseName.tableName`
```sql
mysql> desc my_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)


mysql> -- 複製表結構
mysql> create table my_copy like my_test;
Query OK, 0 rows affected (0.01 sec)


mysql> desc my_copy;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
```



> #### 複製數據
> `insert into tableName [(字段列表)] select 字段列表或* from tableName`
> - 先查出數據後, 將數據新增到指定的表中
> - 所謂蠕蟲複製就是重複複製自己的數據
>   - 2->4->8->16
>   - 通常使用場景就是先把現有數據拿下來，然後迅速膨脹幾倍看未來會有什麼問題

```sql
mysql> select * from my_copy;
Empty set (0.00 sec)  -- 沒有數據

mysql> select * from my_test;
+------+
| name |
+------+
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
4 rows in set (0.00 sec)


mysql> -- 複製數據
mysql> insert into my_copy select * from my_test;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from my_copy;
+------+
| name |
+------+
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
4 rows in set (0.00 sec)

mysql> -- 蠕蟲複製
mysql> insert into my_copy select * from my_copy;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into my_copy select * from my_copy;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from my_copy;
+------+
| name |
+------+
| haha |
| wowo |
| GOGO |
| BUBU |  -- 複製前 4 筆
| haha |
| wowo |
| GOGO |
| BUBU |  -- 複製第一次 8 筆
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |  -- 複製第二次 16 筆
+------+
16 rows in set (0.00 sec)
```

### 改 - 限制條件
> #### 基本更新數據語法
> `update tableName set 字段 = 值 [where 條件]`
> #### 更新數據限制條件
> `update tableName set 字段 = 值 [where 條件] [limit 限制數量]`

```sql
mysql> select * from my_copy;
+------+
| name |
+------+
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
16 rows in set (0.00 sec)

mysql> -- 把前面 2 個 name='haha' 改成 'GGGG'
mysql> update my_copy set name = 'GGGG' where name = 'haha' limit 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0



mysql> select * from my_copy;
+------+
| name |
+------+
| GGGG |  -- 更新
| wowo |
| GOGO |
| BUBU |
| GGGG |  -- 更新
| wowo |
| GOGO |
| BUBU |
| haha |  -- 沒更新~
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
16 rows in set (0.00 sec)
```

### 刪除數據
> #### 基本刪除數據語法
> `delete from tableName [where 條件]`

> #### 刪除數據條件限制
> `delete from tableName [where 條件] [limit 數量]`

```sql
mysql> select * from my_copy;
+------+
| name |
+------+
| GGGG |
| wowo |
| GOGO |
| BUBU |
| GGGG |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
16 rows in set (0.00 sec)

mysql> -- 資料砍半
mysql> delete from my_copy limit 8;
Query OK, 8 rows affected (0.00 sec)

mysql> select * from my_copy;
+------+
| name |
+------+
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
8 rows in set (0.00 sec)
```

### truncate
`truncate tableName`
> - 數據刪除不會改變表結構, 例如自增長是幾就是幾, 不會因為數據刪掉而改變
> - 想要重置表結構, 只能刪除表再重建表
> - truncate 就是幹這件事
>   - Q. truncate 如何知道新增的 SQL 語句?
>     - 任何表的創建語句都會保存一個表的結構文件
>     - truncate 刪掉表之後會去那裡查語句來重建
>   - 說白了他就是執行 drop table xx 跟 create table xx 兩句

```sql
mysql> select * from my_pri;
+----+------+
| id | name |
+----+------+
|  1 | GEAR |
|  2 | HAHA |
|  3 | HOHO |
|  4 | WOWO |
+----+------+
4 rows in set (0.00 sec)

mysql> show create table my_pri;
...
| Table  | Create Table 
| my_pri | CREATE TABLE `my_pri` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | -- 自增長為 5
...
1 row in set (0.00 sec)


mysql> -- 刪除所有數據
mysql> delete from my_pri;
Query OK, 4 rows affected (0.00 sec)


mysql> show create table my_pri;
| Table  | Create Table                  
| my_pri | CREATE TABLE `my_pri` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | -- 自增長還是 5



mysql> -- 清空表，重置自增長
mysql> truncate my_pri;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table my_pri;
| Table  | Create Table                                           
| my_pri | CREATE TABLE `my_pri` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |  -- 搞定
```

## 查詢數據
> - 基本語法
>   `select 字段列表/* from tableName [where 條件]`
> - 完整語法
>   `select [select 選項] 字段列表[字段別名]/* from 數據源 [where 條件] [group by 子句] [having 子句] [limit 子句]`

### select 選項
> - 對查出結果的處理方式
>   - all: 保留所有結果, 默認
>   - distinct: 去重複(所有字段都相同才去)
```sql
mysql> select * from my_copy;
+------+
| name |
+------+
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
16 rows in set (0.00 sec)

mysql> select all * from my_copy;
+------+
| name |
+------+
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
16 rows in set (0.00 sec)

mysql> select distinct * from my_copy;
+------+
| name |
+------+
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
4 rows in set (0.01 sec)
```


### 字段別名
`字段名 [as] 別名`
> - 對查出的結果字段重命名

```sql
mysql> select * from my_oname;
+----+-----------+-------+
| id | num       | name  |
+----+-----------+-------+
|  1 | NUM: 0001 | GODJJ |
|  2 | NUM: 0002 | GEAR  |
|  3 | NUM: 0003 | BEBE  |
+----+-----------+-------+
3 rows in set (0.00 sec)


mysql> -- 重命名
mysql> select 
id as '自增長主鍵', 
num as '編號', 
name '名字'     -- 沒 as 也可以
from my_oname;
+-----------------+-----------+--------+
| 自增長主鍵      | 編號      | 名字   |
+-----------------+-----------+--------+
|               1 | NUM: 0001 | GODJJ  |
|               2 | NUM: 0002 | GEAR   |
|               3 | NUM: 0003 | BEBE   |
+-----------------+-----------+--------+
3 rows in set (0.00 sec)
```


### 數據源
> - 數據的來源, 
> - 關係型數據庫的數據源都是數據表: 只要是類似二維表, 都能為數據源
> - 數據源分三種
>   - 單表數據源
>   - 多表數據源
>   - 查詢語句(子查詢)

> #### 單表數據源
```sql
mysql> select * from my_oname; -- 來源只有一張表
+----+-----------+-------+
| id | num       | name  |
+----+-----------+-------+
|  1 | NUM: 0001 | GODJJ |
|  2 | NUM: 0002 | GEAR  |
|  3 | NUM: 0003 | BEBE  |
+----+-----------+-------+
3 rows in set (0.00 sec)
```

> #### 多表數據源
> `select * from tableName1, tableName2, ...`
```sql
mysql> select * from my_test; -- 單表數據源
+------+
| name |
+------+
| haha |
| wowo |
| GOGO |
| BUBU |
+------+
4 rows in set (0.00 sec)

mysql> select * from my_oname; -- 單表數據源
+----+-----------+-------+
| id | num       | name  |
+----+-----------+-------+
|  1 | NUM: 0001 | GODJJ |
|  2 | NUM: 0002 | GEAR  |
|  3 | NUM: 0003 | BEBE  |
+----+-----------+-------+
3 rows in set (0.00 sec)

mysql> select * from my_oname, my_test;  -- 多表數據源
+----+-----------+-------+------+
| id | num       | name  | name |
+----+-----------+-------+------+
|  1 | NUM: 0001 | GODJJ | haha |
|  2 | NUM: 0002 | GEAR  | haha |
|  3 | NUM: 0003 | BEBE  | haha |
|  1 | NUM: 0001 | GODJJ | wowo |
|  2 | NUM: 0002 | GEAR  | wowo |
|  3 | NUM: 0003 | BEBE  | wowo |
|  1 | NUM: 0001 | GODJJ | GOGO |
|  2 | NUM: 0002 | GEAR  | GOGO |
|  3 | NUM: 0003 | BEBE  | GOGO |
|  1 | NUM: 0001 | GODJJ | BUBU |
|  2 | NUM: 0002 | GEAR  | BUBU |
|  3 | NUM: 0003 | BEBE  | BUBU |
+----+-----------+-------+------+
12 rows in set (0.00 sec)
```
> - 顯示的結果是從 A 表一條數據去匹配 B 表的所有數據, 而且全部保留(數據與字段)
>   這種結果在數學上稱為 [Cartesian product](https://zh.wikipedia.org/wiki/%E7%AC%9B%E5%8D%A1%E5%84%BF%E7%A7%AF)
> - 基本上 Cartesian product 沒啥屁用又佔空間，必須盡量避免


### 子查詢
`select * from (select * from tableName) as 別名`
> - 數據來源是一條查詢語句的結果(二維表)
> - 必須要 as, 因為對系統來說 select * from tableName 只是一條語句, 要把它做成一張表

```sql
mysql> select * from (select * from my_oname); -- 沒有 as
ERROR 1248 (42000): Every derived table must have its own alias


mysql> select * from (select * from my_oname) as o;
+----+-----------+-------+
| id | num       | name  |
+----+-----------+-------+
|  1 | NUM: 0001 | GODJJ |
|  2 | NUM: 0002 | GEAR  |
|  3 | NUM: 0003 | BEBE  |
+----+-----------+-------+
3 rows in set (0.00 sec)
```


### where 子句
> - 用來判斷數據, 篩選數據
> - where 子句返回 0=false, 1=true
>   - 因為 MySQL 沒有Boolean, 所以 0 對 MySQL有特殊意義, 
>   - 所以 enum 排序等的都是從 1 開始
> - 判斷條件:
>   - 比較運算符: 
>     `>` `<` `>=` `<=` `!=` `<>`(不等於) 
>     `like` `between and` `in / not in`
>   - 邏輯運算符:
>     `&&`(and) `||`(or) `!`(not, 取反)
> - where 原理:
>   - where 是唯一一個直接從磁盤獲取數據時，就開始判斷條件
>     - where 之後的都是操作內存的數據而非磁盤數據
>   - where 從磁盤取出一條紀錄, 開始進行 where 判斷, 
>     true 就保存到內存, false 就丟掉
>   - 優點: 不會讓無效數據佔用內存

> #### 準備測試用數據
> - Note.
>   - `rand()` : 產生 0~1 隨機數
>   - `floor()` : 向下取整
>   - `ceil()` : 向上取整

```sql
mysql> select * from my_oname;
+----+-----------+-------+
| id | num       | name  |
+----+-----------+-------+
|  1 | NUM: 0001 | GODJJ |
|  2 | NUM: 0002 | GEAR  |
|  3 | NUM: 0003 | BEBE  |
+----+-----------+-------+
3 rows in set (0.00 sec)

mysql> -- 插入字段
mysql> alter table my_oname add age tinyint unsigned;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table my_oname add height tinyint unsigned;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from my_oname;
+----+-----------+-------+------+--------+
| id | num       | name  | age  | height |
+----+-----------+-------+------+--------+
|  1 | NUM: 0001 | GODJJ | NULL |   NULL |
|  2 | NUM: 0002 | GEAR  | NULL |   NULL |
|  3 | NUM: 0003 | BEBE  | NULL |   NULL |
+----+-----------+-------+------+--------+
3 rows in set (0.00 sec)



mysql> -- 插入數據
mysql> update my_oname set age = floor(rand()*10 + 20), height = floor(rand()*20+170);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from my_oname;
+----+-----------+-------+------+--------+
| id | num       | name  | age  | height |
+----+-----------+-------+------+--------+
|  1 | NUM: 0001 | GODJJ |   22 |    184 |
|  2 | NUM: 0002 | GEAR  |   29 |    180 |
|  3 | NUM: 0003 | BEBE  |   27 |    170 |
+----+-----------+-------+------+--------+
3 rows in set (0.00 sec)
```

> #### 練習1.
> - 找到 id 為 1 或 3 的數據

```sql
mysql> -- 邏輯判斷
mysql> select * from my_oname where id = 1 || id = 3;
+----+-----------+-------+------+--------+
| id | num       | name  | age  | height |
+----+-----------+-------+------+--------+
|  1 | NUM: 0001 | GODJJ |   22 |    184 |
|  3 | NUM: 0003 | BEBE  |   27 |    170 |
+----+-----------+-------+------+--------+
2 rows in set, 1 warning (0.00 sec)


mysql> -- in 代表落在集合中
mysql> select * from my_oname where id in (1,3);
+----+-----------+-------+------+--------+
| id | num       | name  | age  | height |
+----+-----------+-------+------+--------+
|  1 | NUM: 0001 | GODJJ |   22 |    184 |
|  3 | NUM: 0003 | BEBE  |   27 |    170 |
+----+-----------+-------+------+--------+
2 rows in set (0.00 sec)
```

> #### 練習2.
> - 找出年齡在 (25~30) 的數據
> - `between` 的區間左邊值必須大於右邊

```sql
mysql> -- 邏輯運算
mysql> select * from my_oname where age >= 25 && age <= 30;
+----+-----------+------+------+--------+
| id | num       | name | age  | height |
+----+-----------+------+------+--------+
|  2 | NUM: 0002 | GEAR |   29 |    180 |
|  3 | NUM: 0003 | BEBE |   27 |    170 |
+----+-----------+------+------+--------+
2 rows in set, 1 warning (0.00 sec)


mysql> -- 在某區間之間
mysql> select * from my_oname where age between 25 and 30;
+----+-----------+------+------+--------+
| id | num       | name | age  | height |
+----+-----------+------+------+--------+
|  2 | NUM: 0002 | GEAR |   29 |    180 |
|  3 | NUM: 0003 | BEBE |   27 |    170 |
+----+-----------+------+------+--------+
2 rows in set (0.01 sec)

mysql> -- between 區間的左值必須大於右值
mysql> -- 因為between 會將左值轉成 >= 右值轉乘 <=
mysql> -- age >= 30 && age <= 25, 當然啥都找不到
mysql> select * from my_oname where age between 30 and 25;
Empty set (0.00 sec)
```

> #### where 1
> - 不篩選的意思, 寫這個通常是為了語句完整性

```sql
mysql> select * from my_oname where 1;
+----+-----------+-------+------+--------+
| id | num       | name  | age  | height |
+----+-----------+-------+------+--------+
|  1 | NUM: 0001 | GODJJ |   22 |    184 |
|  2 | NUM: 0002 | GEAR  |   29 |    180 |
|  3 | NUM: 0003 | BEBE  |   27 |    170 |
+----+-----------+-------+------+--------+
3 rows in set (0.00 sec)
```

### GROUP BY
`group by 字段名`
> - 分組的意思, 根據字段進行分組

> - 分組是為了統計, SQL 提供了一些函數
> - 分組後, 每組只會產生一條統計數據
>   - `count()` : 統計分組後的紀錄數, 可以使用兩種參數:
>     - `*`
>     - `字段名`: 統計對應的字段, NULL不統計
>   - `Max()` : 統計每組最大值
>   - `Min()` : 統計每組最小值
>   - `avg()` : 統計平均值
>   - `sum()` : 統計和
>   - `group_concat()` : 對分組結果中的某字段進行字符串連接

```sql
mysql> select * from my_oname;
+----+-----------+-------+------+--------+--------+
| id | num       | name  | age  | height | sex    |
+----+-----------+-------+------+--------+--------+
|  1 | NUM: 0001 | GODJJ |   22 |    184 | male   |
|  2 | NUM: 0002 | GEAR  |   29 |    180 | male   |
|  3 | NUM: 0003 | BEBE  |   27 |    170 | male   |
|  4 | NUL: 0004 | BUBU  |   15 |    174 | female |
|  5 | NUL: 0005 | DIDI  |   18 |    179 | female |
+----+-----------+-------+------+--------+--------+
5 rows in set (0.00 sec)


mysql> select sex, count(*), max(height), min(height), avg(height), sum(age) from my_oname group by sex;
+--------+----------+-------------+-------------+-------------+----------+
| sex    | count(*) | max(height) | min(height) | avg(height) | sum(age) |
+--------+----------+-------------+-------------+-------------+----------+
| male   |        3 |         184 |         170 |    178.0000 |       78 |
| female |        2 |         179 |         174 |    176.5000 |       33 |
+--------+----------+-------------+-------------+-------------+----------+
2 rows in set (0.00 sec)


```
```sql
mysql> select * from my_oname;
+----+-----------+-------+------+--------+--------+
| id | num       | name  | age  | height | sex    |
+----+-----------+-------+------+--------+--------+
|  1 | NUM: 0001 | GODJJ | NULL |    184 | male   |
|  2 | NUM: 0002 | GEAR  |   29 |    180 | male   |
|  3 | NUM: 0003 | BEBE  |   27 |    170 | male   |
|  4 | NUL: 0004 | BUBU  |   15 |    174 | female |
|  5 | NUL: 0005 | DIDI  |   18 |    179 | female |
+----+-----------+-------+------+--------+--------+
5 rows in set (0.00 sec)



mysql> -- count(字段名)來統計字段時, null 不統計
mysql> -- 29+27/2 = 28 => 做平均時, 分母是以有統計的字段數來除
mysql> select sex, count(*), count(age), avg(age) from my_oname group by sex;
+--------+----------+------------+----------+
| sex    | count(*) | count(age) | avg(age) |
+--------+----------+------------+----------+
| male   |        3 |          2 |  28.0000 |
| female |        2 |          2 |  16.5000 |
+--------+----------+------------+----------+
2 rows in set (0.00 sec)
```

> - 多字段分組:
>   `group by 字段1, 字段2`
>   - 先對一個字段進行分組後, 再對分組後的結果進行其他字段分組
```sql
mysql> select * from my_oname;
+----+-----------+--------+------+--------+--------+------+
| id | num       | name   | age  | height | sex    | c_id |
+----+-----------+--------+------+--------+--------+------+
|  1 | NUM: 0001 | GODJJ  | NULL |    184 | male   |    2 |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |
+----+-----------+--------+------+--------+--------+------+
6 rows in set (0.00 sec)


mysql> -- 先依據班級分組, 再針對每班分出性別
mysql> select c_id,sex,count(*),group_concat(name) from my_oname group by c_id, sex;
+------+--------+----------+--------------------+
| c_id | sex    | count(*) | group_concat(name) |
+------+--------+----------+--------------------+
|    1 | male   |        2 | GEAR,BEBE          | -- 一班男生兩個
|    2 | male   |        2 | GODJJ,BADBOY       | -- 二班男生兩個
|    2 | female |        2 | BUBU,DIDI          | -- 二班女生兩個
+------+--------+----------+--------------------+
3 rows in set (0.01 sec)
mysql> -- group_concat(name) 對分組結果的name字段的數據進行字符串連接
```

> #### 回溯統計
> `with rollup`
> - 在每次分組的結果上進行總統計
> - 回朔統計的分組字段為 NULL
> - 分組統計的結果是向下統計(粉組後的各項統計), 而回朔統計則為向上統計(分組前的統計)

> - 第一次分組: 會有一次回朔
```sql
mysql> select * from my_oname;
+----+-----------+--------+------+--------+--------+------+
| id | num       | name   | age  | height | sex    | c_id |
+----+-----------+--------+------+--------+--------+------+
|  1 | NUM: 0001 | GODJJ  | NULL |    184 | male   |    2 |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |
|  8 | x         | 小花   |   20 |    179 | female |    3 |
+----+-----------+--------+------+--------+--------+------+

mysql> -- 用班級來分組
mysql> select c_id, count(*), avg(height) from my_oname group by c_id with rollup;
+------+----------+-------------+
| c_id | count(*) | avg(height) |
+------+----------+-------------+
|    1 |        2 |    175.0000 | -- 一班有兩人, 平均身高 175
|    2 |        4 |    176.0000 | -- 二班有四人, 平均身高 176
|    3 |        2 |    179.0000 | -- 三班有兩人, 平均身高 179
| NULL |        8 |    176.5000 | -- 全校共八人, 平均身高 176.5
+------+----------+-------------+
4 rows in set (0.00 sec)
```

> - 第二次分組會視組數而定
>   - 以下列為例
>     ![](https://i.imgur.com/No69Gso.jpg)
>     - 先將資料以班級分類, 總共三班(一次回朔)
>     - 再依性別分類, 總共兩性(三筆回朔, 三個班分類), 共四筆

```sql
mysql> select c_id,sex, count(*), avg(height) from my_oname group by c_id, sex with rollup;
+------+--------+----------+-------------+
| c_id | sex    | count(*) | avg(height) |
+------+--------+----------+-------------+
|    1 | male   |        2 |    175.0000 | -- 一班男生兩人, 平均身高 175
|    1 | NULL   |        2 |    175.0000 | -- 一班總共兩人,平均身高 175
|    2 | male   |        2 |    175.5000 | -- 二班男生兩人, 平均身高 175.5
|    2 | female |        2 |    176.5000 | -- 二班女生兩人, 平均身高 176.5
|    2 | NULL   |        4 |    176.0000 | -- 二班總共四人, 平均身高 176
|    3 | male   |        1 |    179.0000 | -- 三班男生一人, 平均身高 179
|    3 | female |        1 |    179.0000 | -- 三班女生一人, 平均身高 179
|    3 | NULL   |        2 |    179.0000 | -- 三班總共兩人, 平均身高 179
| NULL | NULL   |        8 |    176.5000 |
+------+--------+----------+-------------+
9 rows in set (0.00 sec)
```


> - 5.7 版本後多了一個 `ONLY_FULL_GROUP_BY` 來嚴格執行 SQL92
>   - `select oo from tableName` 的 oo 叫做 `target list`
>   - `group by oo` 的 xx 叫做 `group by column`
>   - 這兩的欄位必須完全對應, 否則報錯
> - 覺得麻煩就自己用 set 把它改掉就好了
>   - 砍掉
>   `SET GLOBAL sql_mode = '除了 ONLY_FULL_GROUP_BY 的都填進去'`
>   `SET SESSION sql_mode = '除了 ONLY_FULL_GROUP_BY 的都填進去'`
>   `SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));`
>   - 加回去
>   `SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));`
```sql
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
```txt
# 查到的其他模式意思, 先複製, 沒空消化

1.STRICT_TRANS_TABLES： 在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
3.NO_ZERO_IN_DATE： 在严格模式下，不允许日期和月份为零
5.NO_ZERO_DATE： 设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
7.ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL
9.NO_AUTO_CREATE_USER： 禁止GRANT创建密码为空的用户
11.NO_ENGINE_SUBSTITUTION： 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
13.PIPES_AS_CONCAT： 将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
15.ANSI_QUOTES： 启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
17.NO_AUTO_VALUE_ON_ZERO： 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
```

```sql
mysql> select * from my_oname;
+----+-----------+-------+------+--------+--------+
| id | num       | name  | age  | height | sex    |
+----+-----------+-------+------+--------+--------+
|  1 | NUM: 0001 | GODJJ |   22 |    184 | male   |
|  2 | NUM: 0002 | GEAR  |   29 |    180 | male   |
|  3 | NUM: 0003 | BEBE  |   27 |    170 | male   |
|  4 | NUL: 0004 | BUBU  |   15 |    174 | female |
|  5 | NUL: 0005 | DIDI  |   18 |    179 | female |
+----+-----------+-------+------+--------+--------+
5 rows in set (0.00 sec)

mysql> -- 沒開啟的 ONLY_FULL_GROUP_BY 的話是沒問題的
mysql> select * from my_oname group by sex;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test2.my_oname.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


mysql> select sex from my_oname group by sex;
+--------+
| sex    |
+--------+
| male   |
| female |
+--------+
2 rows in set (0.01 sec)
```




### having 子句
> - having 子句與 where 子句一樣, 都是進行判斷的
>   - where 是針對磁盤數據進行判斷
>   - having 是在內存進行判斷
> - having 幾乎能做到 where 能做的所有事情, 但 where 卻無法做 having 能做的所有事
>   因為大家操作的對象與範圍都不同
> - 但是能夠用 where 時就盡量用 where, 如此才能保證有效數據源進入內存, 
>   如此能夠提高內存利用率, 減少在內存出現錯誤數據的概率, 效率較高

> - 分組統計(函數)的結果, 只有 having 能判斷, 因為分組是在 where 之後才分組
```sql
mysql> -- 求超過三人的班級
mysql> -- 此時就必須先統計每班人數, 才有辦法知道哪些班級超過三人
mysql> select c_id, count(*) from my_oname group by c_id having count(*) >= 3;
+------+----------+
| c_id | count(*) |
+------+----------+
|    2 |        4 |
+------+----------+
1 row in set (0.00 sec)


mysql> -- where 使用分組後資訊根本不可能, 因為where判斷的時候根本還沒分組

mysql> select c_id, count(*) from my_oname where count(*) group by c_id;
ERROR 1111 (HY000): Invalid use of group function
```

> - having 能夠使用別名, where 不能
>   - 別名是字段進入內存才能產生
>   - where 是直接取磁盤的數據, 名字只可能是字段名
```sql
mysql> -- 拿到 B 開頭的名字
mysql> select name as 名字 from my_oname having 名字 like 'B%';
+--------+
| 名字   |
+--------+
| BEBE   |
| BUBU   |
| BADBOY |
+--------+
3 rows in set (0.00 sec)


mysql> -- where 取數據時根本還沒有別名~~
mysql> select name as 名字 from my_oname where 名字 like 'B%';
ERROR 1054 (42S22): Unknown column '名字' in 'where clause'
```

### ORDER BY
`order by 字段名 [asc|desc]`
> - 排序
> - `asc` : 升序(默認)
> - `desc` : 降序

```sql
mysql> -- 升序
mysql> select * from my_oname order by c_id;
+----+-----------+--------+------+--------+--------+------+
| id | num       | name   | age  | height | sex    | c_id |
+----+-----------+--------+------+--------+--------+------+
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |
|  1 | NUM: 0001 | GODJJ  | NULL |    184 | male   |    2 |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |
|  8 | x         | 小花   |   20 |    179 | female |    3 |
+----+-----------+--------+------+--------+--------+------+
8 rows in set (0.00 sec)


mysql> -- 降序
mysql> select * from my_oname order by c_id desc;
+----+-----------+--------+------+--------+--------+------+
| id | num       | name   | age  | height | sex    | c_id |
+----+-----------+--------+------+--------+--------+------+
|  7 | x         | 小明   |   20 |    179 | male   |    3 |
|  8 | x         | 小花   |   20 |    179 | female |    3 |
|  1 | NUM: 0001 | GODJJ  | NULL |    184 | male   |    2 |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |
+----+-----------+--------+------+--------+--------+------+
8 rows in set (0.00 sec)
```

> - 多字段排序:
>   - 先根據某字段進行排序
>   - 在排好序的基礎上再繼續做下一個字段的排序
```sql
mysql> -- 按照班級排序 (單字段排序)
mysql> select * from my_oname order by c_id;
+----+-----------+--------+------+--------+--------+------+
| id | num       | name   | age  | height | sex    | c_id |
+----+-----------+--------+------+--------+--------+------+
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |
|  1 | NUM: 0001 | GODJJ  | NULL |    184 | male   |    2 |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |
|  8 | x         | 小花   |   20 |    179 | female |    3 |
+----+-----------+--------+------+--------+--------+------+
8 rows in set (0.00 sec)


mysql> -- 按照班級排序後, 再按照身高排序(多字段排序)
mysql> select * from my_oname order by c_id, height;
+----+-----------+--------+------+--------+--------+------+
| id | num       | name   | age  | height | sex    | c_id |
+----+-----------+--------+------+--------+--------+------+
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |
|  1 | NUM: 0001 | GODJJ  | NULL |    184 | male   |    2 |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |
|  8 | x         | 小花   |   20 |    179 | female |    3 |
+----+-----------+--------+------+--------+--------+------+
8 rows in set (0.00 sec)
```





### LIMIT 子句
> - 限制結果的子句
> - Limit 有兩種使用方法

> - 限制數量
>   `limit 長度(數據量, length)`
>   - 用來限制長度(數據量)
```sql
mysql> select * from my_oname limit 2;
+----+-----------+-------+------+--------+------+------+
| id | num       | name  | age  | height | sex  | c_id |
+----+-----------+-------+------+--------+------+------+
|  1 | NUM: 0001 | GODJJ | NULL |    184 | male |    2 |
|  2 | NUM: 0002 | GEAR  |   29 |    180 | male |    1 |
+----+-----------+-------+------+--------+------+------+
2 rows in set (0.00 sec)
```

> - 限制起始位置與數據量
>   `limit 起始位置(offset), 長度(數據量, length)`
>   - 經常用來實現數據的分頁, 提升服務器響應效率, 減少資源浪費, 節省用戶時間
>     - 每頁長度(length) 基本上不會變
>     - offset = page-1 * length
>       - 假設每夜十筆資料
>         - 第一頁 `limit ((1-1)*10, 10)`
>         - 第二頁 `limit ((2-1)*10, 10)`


```sql
mysql> select * from my_oname limit 0 ,2;
+----+-----------+-------+------+--------+------+------+
| id | num       | name  | age  | height | sex  | c_id |
+----+-----------+-------+------+--------+------+------+
|  1 | NUM: 0001 | GODJJ | NULL |    184 | male |    2 |
|  2 | NUM: 0002 | GEAR  |   29 |    180 | male |    1 |
+----+-----------+-------+------+--------+------+------+
2 rows in set (0.00 sec)

mysql> 
mysql> select * from my_oname limit 2,2;
+----+-----------+------+------+--------+--------+------+
| id | num       | name | age  | height | sex    | c_id |
+----+-----------+------+------+--------+--------+------+
|  3 | NUM: 0003 | BEBE |   27 |    170 | male   |    1 |
|  4 | NUL: 0004 | BUBU |   15 |    174 | female |    2 |
+----+-----------+------+------+--------+--------+------+
2 rows in set (0.00 sec)
```



## 連接查詢
`表 join 表`
> - 將多張表依據指定字段進行紀錄連接
> - 連接查詢分成四類: 內連接、外連接、自然連接、交叉連接
> - `表 join 表` 是數據源, 如果做查詢操作(select)時, 是先連接後查詢

### 交叉連接(cross join)
> - 一張表取出一條數據去匹配另一張表的所有數據, 
> - 匹配結果全部保留(Cartesian product), 結果沒啥鳥用

```sql
mysql> select * from my_oname join my_class;
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
| id | num       | name   | age  | height | sex    | c_id | id | c_name       | subject |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |  1 | 三年二班     | JS      |
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |  2 | 一年三班     | C       |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |  1 | 三年二班     | JS      |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |  2 | 一年三班     | C       |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |  1 | 三年二班     | JS      |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |  2 | 一年三班     | C       |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |  1 | 三年二班     | JS      |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |  2 | 一年三班     | C       |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |  1 | 三年二班     | JS      |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |  2 | 一年三班     | C       |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |  1 | 三年二班     | JS      |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |  2 | 一年三班     | C       |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |  1 | 三年二班     | JS      |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |  2 | 一年三班     | C       |
|  8 | x         | 小花   |   20 |    179 | female |    3 |  1 | 三年二班     | JS      |
|  8 | x         | 小花   |   20 |    179 | female |    3 |  2 | 一年三班     | C       |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
16 rows in set (0.00 sec)
```


### 內連接
`表1 [inner] join 表2 on 表1.字段 = 表2.字段`
> - 從表1取出一條數據去匹配表2所有紀錄
> - 匹配的結果必須在 `on` 條件成立下才會保留
> - 當字段名具唯一性, 表名.字段的表名可省, 只是為了可讀性還是會寫

```sql
mysql> select * from my_oname inner join my_class on my_oname.c_id = my_class.id;
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
| id | num       | name   | age  | height | sex    | c_id | id | c_name       | subject |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |  2 | 一年三班     | C       |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |  1 | 三年二班     | JS      |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |  1 | 三年二班     | JS      |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |  2 | 一年三班     | C       |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |  2 | 一年三班     | C       |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |  2 | 一年三班     | C       |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
6 rows in set (0.00 sec)


mysql> -- 如果字段名具唯一性(c_id), 表名可省(my_oname.c_id => c_id)
mysql> select * from my_oname inner join my_class on c_id = my_class.id;
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
| id | num       | name   | age  | height | sex    | c_id | id | c_name       | subject |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |  2 | 一年三班     | C       |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |  1 | 三年二班     | JS      |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |  1 | 三年二班     | JS      |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |  2 | 一年三班     | C       |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |  2 | 一年三班     | C       |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |  2 | 一年三班     | C       |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
6 rows in set (0.00 sec)



mysql> -- on 篩選條件成立才會保留
mysql> select * from my_oname inner join my_class on my_oname.height = my_class.id;
Empty set (0.00 sec)
```

> - 從結果可看到一個問題，字段名重複(id)
> - 當名字太長或字段名重複時, 可以使用別名

```sql
mysql> select o.*, c.c_name, c.subject as c_subject from my_oname as o inner join my_class as c on o.c_id = c.id;
+----+-----------+--------+------+--------+--------+------+--------------+-----------+
| id | num       | name   | age  | height | sex    | c_id | c_name       | c_subject |
+----+-----------+--------+------+--------+--------+------+--------------+-----------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 | 一年三班     | C         |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 | 三年二班     | JS        |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 | 三年二班     | JS        |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 | 一年三班     | C         |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 | 一年三班     | C         |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 | 一年三班     | C         |
+----+-----------+--------+------+--------+--------+------+--------------+-----------+
6 rows in set (0.00 sec)
```

> - 內連接可以沒有 on, 不過就等於全部保留(Cartesian product)
```sql
mysql> select * from my_oname inner join my_class;
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
| id | num       | name   | age  | height | sex    | c_id | id | c_name       | subject |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |  1 | 三年二班     | JS      |
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |  2 | 一年三班     | C       |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |  1 | 三年二班     | JS      |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |  2 | 一年三班     | C       |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |  1 | 三年二班     | JS      |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |  2 | 一年三班     | C       |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |  1 | 三年二班     | JS      |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |  2 | 一年三班     | C       |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |  1 | 三年二班     | JS      |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |  2 | 一年三班     | C       |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |  1 | 三年二班     | JS      |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |  2 | 一年三班     | C       |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |  1 | 三年二班     | JS      |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |  2 | 一年三班     | C       |
|  8 | x         | 小花   |   20 |    179 | female |    3 |  1 | 三年二班     | JS      |
|  8 | x         | 小花   |   20 |    179 | female |    3 |  2 | 一年三班     | C       |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
16 rows in set (0.00 sec)

```

> - where 可以代替 on
>   - 但是一般都用 on, 因為 on 的效率比較高
>     - on 的匹配方式是根據索引匹配
>     - where 的匹配方式是一條一條判斷 true 或 flase
```sql
mysql> select * from my_oname inner join my_class where my_oname.c_id = my_class.id;
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
| id | num       | name   | age  | height | sex    | c_id | id | c_name       | subject |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |  2 | 一年三班     | C       |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |  1 | 三年二班     | JS      |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |  1 | 三年二班     | JS      |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |  2 | 一年三班     | C       |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female |    2 |  2 | 一年三班     | C       |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |  2 | 一年三班     | C       |
+----+-----------+--------+------+--------+--------+------+----+--------------+---------+
6 rows in set (0.00 sec)
```


### 外連接(outer join)
`左表 left/right join 右表 on 左表.字段 = 右表.字段`
> - 外連接以某表為主, 取出所有紀錄去與另張表匹配
>   - 外連接有主表
>     - `left join` : 左外連接, 以左表為主表, 最終紀錄數最少不少於左表
>     - `right join` : 右外連接, 以右表為主表,最終紀錄數最少不少於右表
>   - 顯示結果: 左表數據在左邊, 右表數據在右邊
> - 不論匹配條件是否吻合，皆會保留
>   - 匹配成功的數據會正確保留
>   - 不匹配成功的數據會為 NULL

```sql
mysql> select * from my_class;
+----+--------------+---------+
| id | c_name       | subject |
+----+--------------+---------+
|  1 | 三年二班     | JS      |
|  2 | 一年三班     | C       |
|  3 | 四年四班     | PHP     |
+----+--------------+---------+
3 rows in set (0.00 sec)


mysql> select * from my_oname;
+----+-----------+--------+------+--------+--------+------+
| id | num       | name   | age  | height | sex    | c_id |
+----+-----------+--------+------+--------+--------+------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female | NULL |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |
|  8 | x         | 小花   |   20 |    179 | female |    3 |
+----+-----------+--------+------+--------+--------+------+
8 rows in set (0.00 sec)



mysql> -- 仔細看 my_oname.id = 5
mysql> -- 左連接 ( 以 my_oname 為主 ) 
mysql> select * from my_oname left join my_class on my_oname.c_id = my_class.id;
+----+-----------+--------+------+--------+--------+------+------+--------------+---------+
| id | num       | name   | age  | height | sex    | c_id | id   | c_name       | subject |
+----+-----------+--------+------+--------+--------+------+------+--------------+---------+
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |    1 | 三年二班     | JS      |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |    1 | 三年二班     | JS      |
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |    2 | 一年三班     | C       |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |    2 | 一年三班     | C       |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |    2 | 一年三班     | C       |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |    3 | 四年四班     | PHP     |
|  8 | x         | 小花   |   20 |    179 | female |    3 |    3 | 四年四班     | PHP     |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female | NULL | NULL | NULL         | NULL    |
+----+-----------+--------+------+--------+--------+------+------+--------------+---------+
8 rows in set (0.01 sec)

mysql> -- 副表中沒有可匹配的數據時, 會為 null

mysql> -- 右連接 ( 以 my_class 為主)
mysql> select * from my_oname right join my_class on my_oname.c_id = my_class.id;
+------+-----------+--------+------+--------+--------+------+----+--------------+---------+
| id   | num       | name   | age  | height | sex    | c_id | id | c_name       | subject |
+------+-----------+--------+------+--------+--------+------+----+--------------+---------+
|    1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |  2 | 一年三班     | C       |
|    2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |  1 | 三年二班     | JS      |
|    3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |  1 | 三年二班     | JS      |
|    4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |  2 | 一年三班     | C       |
|    6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |  2 | 一年三班     | C       |
|    7 | x         | 小明   |   20 |    179 | male   |    3 |  3 | 四年四班     | PHP     |
|    8 | x         | 小花   |   20 |    179 | female |    3 |  3 | 四年四班     | PHP     |
+------+-----------+--------+------+--------+--------+------+----+--------------+---------+
7 rows in set (0.00 sec)
```


### 自然連接(natural join)
> - 系統以同名字段作為匹配條件, 多個同名都是
> - 自然連接可分為 自然內連接 與 自然外連接
> - 非常少用
>   - 因為必須確保所有表的同名字段都代表同個意思
>   - 因為內外連接就可以模擬了

> - 自然內連接
>   `左表 natural join 右表`
```sql
mysql> select * from my_class;
+----+--------------+---------+
| id | name         | subject |
+----+--------------+---------+
|  1 | 三年二班     | JS      |
|  2 | 一年三班     | C       |
|  3 | 四年四班     | PHP     |
+----+--------------+---------+
3 rows in set (0.00 sec)

mysql> select * from my_oname;
+----+-----------+--------+------+--------+--------+------+
| id | num       | name   | age  | height | sex    | c_id |
+----+-----------+--------+------+--------+--------+------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female | NULL |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 |
|  7 | x         | 小明   |   20 |    179 | male   |    3 |
|  8 | x         | 小花   |   20 |    179 | female |    3 |
+----+-----------+--------+------+--------+--------+------+
8 rows in set (0.00 sec)

mysql> -- 這兩張表都有 id 跟 name 字段


mysql> select * from my_oname natural join my_class;
Empty set (0.00 sec)
mysql> -- 事實上這句話就等於
mysql> -- select * from my_oname join my_class 
     > -- on my_oname.id = my_class.id && my_oname.name = my_class.name;
mysql> -- 雖然 id 匹配的到, 但是 name 就沒有匹配結果

mysql> alter table my_class change name c_name varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0



mysql> -- 只剩 id 字段名相同
mysql> -- 下面這句話就等於
mysql> -- select * from my_oname join my_class on my_oname.id = my_class.id;
mysql> select * from my_oname natural join my_class;
+----+-----------+-------+------+--------+------+------+--------------+---------+
| id | num       | name  | age  | height | sex  | c_id | c_name       | subject |
+----+-----------+-------+------+--------+------+------+--------------+---------+
|  1 | NUM: 0001 | GODJJ |   30 |    184 | male |    2 | 三年二班     | JS      |
|  2 | NUM: 0002 | GEAR  |   29 |    180 | male |    1 | 一年三班     | C       |
|  3 | NUM: 0003 | BEBE  |   27 |    170 | male |    1 | 四年四班     | PHP     |
+----+-----------+-------+------+--------+------+------+--------------+---------+
3 rows in set (0.01 sec)

mysql> -- 不過這結果事實上也沒啥鳥用, 因為兩張表的 id 代表的東西又不一樣, 合再一起幹嘛

```


> - 自然外連接
>   `左表 natural left/right join 右表`
```sql
mysql> select * from my_oname natural left join my_class;
+----+-----------+--------+------+--------+--------+------+--------------+---------+
| id | num       | name   | age  | height | sex    | c_id | c_name       | subject |
+----+-----------+--------+------+--------+--------+------+--------------+---------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 | 三年二班     | JS      |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 | 一年三班     | C       |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 | 四年四班     | PHP     |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 | NULL         | NULL    |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female | NULL | NULL         | NULL    |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 | NULL         | NULL    |
|  7 | x         | 小明   |   20 |    179 | male   |    3 | NULL         | NULL    |
|  8 | x         | 小花   |   20 |    179 | female |    3 | NULL         | NULL    |
+----+-----------+--------+------+--------+--------+------+--------------+---------+
8 rows in set (0.00 sec)
```



> - 模擬自然連接
>   - 直接寫
>   - 使用`using(字段)`
>     - 使用什麼來連接
>     - 該連接條件會合併

```sql
mysql> -- 模擬自然內連接
mysql> select my_oname.*, my_class.c_name, my_class.subject from my_oname join my_class on my_oname.id = my_class.id;
+----+-----------+-------+------+--------+------+------+--------------+---------+
| id | num       | name  | age  | height | sex  | c_id | c_name       | subject |
+----+-----------+-------+------+--------+------+------+--------------+---------+
|  1 | NUM: 0001 | GODJJ |   30 |    184 | male |    2 | 三年二班     | JS      |
|  2 | NUM: 0002 | GEAR  |   29 |    180 | male |    1 | 一年三班     | C       |
|  3 | NUM: 0003 | BEBE  |   27 |    170 | male |    1 | 四年四班     | PHP     |
+----+-----------+-------+------+--------+------+------+--------------+---------+
3 rows in set (0.00 sec)

mysql> select * from my_oname join my_class using(id);
+----+-----------+-------+------+--------+------+------+--------------+---------+
| id | num       | name  | age  | height | sex  | c_id | c_name       | subject |
+----+-----------+-------+------+--------+------+------+--------------+---------+
|  1 | NUM: 0001 | GODJJ |   30 |    184 | male |    2 | 三年二班     | JS      |
|  2 | NUM: 0002 | GEAR  |   29 |    180 | male |    1 | 一年三班     | C       |
|  3 | NUM: 0003 | BEBE  |   27 |    170 | male |    1 | 四年四班     | PHP     |
+----+-----------+-------+------+--------+------+------+--------------+---------+
3 rows in set (0.00 sec)



mysql> -- 模擬自然外連接
mysql> select o.*, c.c_name, c.subject from my_oname as o left join my_class as c on o.id = c.id;
+----+-----------+--------+------+--------+--------+------+--------------+---------+
| id | num       | name   | age  | height | sex    | c_id | c_name       | subject |
+----+-----------+--------+------+--------+--------+------+--------------+---------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 | 三年二班     | JS      |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 | 一年三班     | C       |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 | 四年四班     | PHP     |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 | NULL         | NULL    |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female | NULL | NULL         | NULL    |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 | NULL         | NULL    |
|  7 | x         | 小明   |   20 |    179 | male   |    3 | NULL         | NULL    |
|  8 | x         | 小花   |   20 |    179 | female |    3 | NULL         | NULL    |
+----+-----------+--------+------+--------+--------+------+--------------+---------+
8 rows in set (0.00 sec)



mysql> select * from my_oname left join my_class using(id);
+----+-----------+--------+------+--------+--------+------+--------------+---------+
| id | num       | name   | age  | height | sex    | c_id | c_name       | subject |
+----+-----------+--------+------+--------+--------+------+--------------+---------+
|  1 | NUM: 0001 | GODJJ  |   30 |    184 | male   |    2 | 三年二班     | JS      |
|  2 | NUM: 0002 | GEAR   |   29 |    180 | male   |    1 | 一年三班     | C       |
|  3 | NUM: 0003 | BEBE   |   27 |    170 | male   |    1 | 四年四班     | PHP     |
|  4 | NUL: 0004 | BUBU   |   15 |    174 | female |    2 | NULL         | NULL    |
|  5 | NUL: 0005 | DIDI   |   18 |    179 | female | NULL | NULL         | NULL    |
|  6 | NUM: 0006 | BADBOY |   20 |    167 | male   |    2 | NULL         | NULL    |
|  7 | x         | 小明   |   20 |    179 | male   |    3 | NULL         | NULL    |
|  8 | x         | 小花   |   20 |    179 | female |    3 | NULL         | NULL    |
+----+-----------+--------+------+--------+--------+------+--------------+---------+
8 rows in set (0.00 sec)
```



## 外鍵 (foreign key)
> - 表中有字段 (非主鍵) 指向另外一張表的主鍵，該字段稱為外鍵
> - 外鍵要求字段必須先是個索引(普通索引), 
>   如果字段本身沒有索引, 外鍵會先創建一個索引後才創建外鍵本身 
> - 一張表可以有多個外鍵
> - 外鍵雖然很屌, 不過會造成數據難以控制, 
>   - 例如父表被兩張表引用, 且引用外鍵的子表設置模式不同, 操作父表主鍵數據時, 對於 A表可能符合規則而可以操作, 但對於 B表可能不符合規則, 造成操作父表主鍵失敗
>   - 故外鍵會造成服務器控制數據的權限降低, 造成很多人不用外鍵
>   - 例如因為被引用外鍵而造成無法刪除父表, 必須先幹掉所有引用才能幹掉父表
>     ```sql
>     mysql> drop table class2;
>     ERROR 3730 (HY000): Cannot drop table 'class2' referenced by a foreign key constraint 'my_fore4_ibfk_1' on table 'my_fore4'.
>     ```

### 新增外鍵

> - 可以在創建表時或創建表後新增, 但要考慮數據問題

> #### 創建表時
> `[constraint 外鍵名] foreign key(外鍵字段) references 外部表(主鍵字段)`
> - 在所有表字段之後寫
> - 外鍵名不可重複


```sql
mysql> desc my_class;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name  | varchar(20) | YES  |     | NULL    |                |
| subject | varchar(20) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)


mysql> create table my_fore(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> c_id int,
    -> foreign key(c_id) references my_class(id)
    -> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)



mysql> desc my_fore;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| c_id  | int(11)     | YES  | MUL | NULL    |                | 
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> -- 外鍵欄寫 MUL(multi-column) => 因為外鍵創建前先創建一個普通索引後再創建外鍵


mysql> show create table my_fore;
| Table   | Create Table                                           
| my_fore | CREATE TABLE `my_fore` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),   -- 索引1 => KEY
  CONSTRAINT `my_fore_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`)  -- 索引2 => FOREIGN KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.01 sec)

mysql> -- CONSTRAINT `my_fore_ibfk_1` => 外鍵名
mysql> -- FOREIGN KEY (`c_id`) => 外鍵字段 
mysql> -- REFERENCES `my_class` (`id`) 外鍵字段
```

> #### 創建表後新增(修改表結構)
> `alter table tableName add [constraint 外鍵名] foreign key (外鍵字段) references 外部表 (主鍵字段)`

```sql
mysql> desc my_fore2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| c_id  | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)



mysql> alter table my_fore2 add constraint student_class foreign key (c_id) references my_class (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc my_fore2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| c_id  | int(11)     | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
```

### 刪除外鍵 & 修改外鍵
`alter table tableName drop foreign key 外鍵名`
> - 外鍵不可修改, 只能刪掉重增
> - 刪除外鍵後, 不會反映在表結構上, 必須看表創建語句

```sql
mysql> show create table my_fore;
| Table   | Create Table                                           
| my_fore | CREATE TABLE `my_fore` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `my_fore_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`)  -- 外鍵
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)


mysql> alter table my_fore drop foreign key my_fore_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table my_fore;
| Table   | Create Table                                           
| my_fore | CREATE TABLE `my_fore` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`)  -- 外鍵刪掉了
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

mysql> desc my_fore;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| c_id  | int(11)     | YES  | MUL | NULL    |                | -- 表結構看不出來
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
```




### 外鍵效果

#### 對子表(外鍵所在的表)約束
> - 對子表進行增或改操作時, 如果對應的外鍵字段在父表中找不到匹配，系統就會報錯

```sql
mysql> select * from my_class;
+----+--------------+---------+
| id | c_name       | subject |
+----+--------------+---------+
|  1 | 三年二班      | JS      |
|  2 | 一年三班      | C       |
|  3 | 四年四班      | PHP     |
+----+--------------+---------+
3 rows in set (0.00 sec)

mysql> insert into my_fore2 values (null, 'GODJJ', 2);
Query OK, 1 row affected (0.00 sec)


mysql> insert into my_fore2 values (null, 'GODJJ', 4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test2`.`my_fore2`, CONSTRAINT `student_class` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`))
mysql> -- c_id 的 id 只有 123, 4無法匹配而失敗
```

#### 對父表(被引用的表)約束
> - 對父表主鍵進行刪或改時, 對應的主鍵被子表引用數據時, 操作會失敗
```sql
mysql> select * from my_class;
+----+--------------+---------+
| id | c_name       | subject |
+----+--------------+---------+
|  1 | 三年二班     | JS      |
|  2 | 一年三班     | C       |
|  3 | 四年四班     | PHP     |
+----+--------------+---------+
3 rows in set (0.00 sec)

mysql> select * from my_fore2;
+----+-------+------+
| id | name  | c_id |
+----+-------+------+
|  1 | GODJJ |    2 |
+----+-------+------+
1 row in set (0.00 sec)


mysql> update my_class set id = 4 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> -- 因為 my_fore2.c_id 已經引用了 my_class.id = 2 的數據, 所以報錯
mysql> update my_class set id = 5 where id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test2`.`my_fore2`, CONSTRAINT `student_class` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`))
```

### 外鍵條件
> - 存儲引擎必須為 `innodb`, 如果非 `innodb`, 外鍵可創建成功, 但不會約束
> - 外鍵字段的字段類型必須與父表主鍵完全一致
> - 外鍵名不能重複
> - 如果創建外鍵的字段已經有數據, 必須保證該數據在父表可以對應


```sql
mysql> -- my_fore 沒有外鍵~
mysql> show create table my_fore;
| Table   | Create Table                                         
| my_fore | CREATE TABLE `my_fore` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)


mysql> select * from my_fore;
+----+-------+------+
| id | name  | c_id |
+----+-------+------+
|  1 | GODJJ |    2 |
|  2 | GODJJ |    2 |
|  3 | GODJJ |    9 |  -- c_id = 9 匹配 my_class 的 id 時會匹配不到
+----+-------+------+
3 rows in set (0.00 sec)

mysql> select * from my_class;
+----+--------------+---------+
| id | c_name       | subject |
+----+--------------+---------+
|  2 | 一年三班     | C       |
|  3 | 四年四班     | PHP     |
|  4 | 三年二班     | JS      |
|  6 | 可憐吶       | 1       |
+----+--------------+---------+
4 rows in set (0.00 sec)


mysql> -- 當我字段裡有數據時設置外鍵, 且數據在父表會匹配不到時, 操作失敗
mysql> alter table my_fore add foreign key (c_id) references my_class (id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test2`.`#sql-e8d_8`, CONSTRAINT `my_fore_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`))

mysql> -- 數據改成可以匹配到的數值時, 就沒問題了
mysql> update my_fore set c_id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 3  Changed: 1  Warnings: 0

mysql> alter table my_fore add foreign key (c_id) references my_class (id);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0



mysql> show create table my_fore;
| Table   | Create Table                                           
| my_fore | CREATE TABLE `my_fore` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `my_fore_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`) -- 設置成功
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
1 row in set (0.01 sec)
```


### 外鍵約束
> - 外鍵效果
>   - 可以限制子表字段必須與父表主鍵匹配
>   - 可以限制父表不能亂刪改主鍵
>     - 問題: 如果我真的必須刪改呢?
> - 外鍵約束有三種
>   - `district` (嚴格模式, 約束模式) : 被引用的父表主鍵對應的數據不能刪改, 預設
>   - `cascade` (階層模式) : 被引用的父表主鍵對應的數據刪改時, 對應子表關聯數據跟著刪改
>   - `set null` (置空模式) : 被引用的父表主鍵對應的數據刪改時, 對應子表關聯數據置空
>     - `set null` 的前提: 外鍵必須允許為 null
> - 選擇模式應視情況而定, 但更新用階層刪除用置空為大宗
>   - 假設我有需求要更改父表主鍵數據
>     - 嚴格模式無法改
>     - 階層模式下, 子表跟著連動 (較適合)
>     - 置空模式下, 子表會置空
>   - 假設我有需求要刪除父表主鍵數據
>     - 嚴格模式無法改
>     - 階層模式下, 子表跟著連動而刪除
>     - 置空模式下, 子表會置空 (較適合)
> - 設置約束模式
>   `foreign key (外鍵) references 父表 (主鍵) on delete 模式 on update 模式`

```sql
mysql> create table my_fore3( 
    -> id int primary key auto_increment, 
    -> name varchar(20), 
    -> c_id int,
    -> foreign key (c_id) references my_class (id) on delete set null on update cascade
    -> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.04 sec)



mysql> show create table my_fore3;
| Table    | Create Table                                         
| my_fore3 | CREATE TABLE `my_fore3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `my_fore3_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
```

```sql
mysql> select * from my_class;
+----+--------------+---------+
| id | c_name       | subject |
+----+--------------+---------+
|  2 | 一年三班     | C       |
|  3 | 四年四班     | PHP     |
|  4 | 三年二班     | JS      |
|  6 | 可憐吶       | 1       |
+----+--------------+---------+
4 rows in set (0.00 sec)

mysql> select * from my_fore3;
+----+--------+------+
| id | name   | c_id |
+----+--------+------+
|  1 | 大哥   |    2 |
|  2 | 小弟   |    3 |
+----+--------+------+
2 rows in set (0.00 sec)


mysql> -- 修改使用 cascade 模式下, 修改父表主鍵數據, 子表對應數據會跟著改
mysql> update my_class set id = 9 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_class;
+----+--------------+---------+
| id | c_name       | subject |
+----+--------------+---------+
|  3 | 四年四班     | PHP     |
|  4 | 三年二班     | JS      |
|  6 | 可憐吶       | 1       |
|  9 | 一年三班     | C       |   -- 修改
+----+--------------+---------+
4 rows in set (0.00 sec)

mysql> select * from my_fore3;
+----+--------+------+
| id | name   | c_id |
+----+--------+------+
|  1 | 大哥   |    9 |   -- 連動
|  2 | 小弟   |    3 |
+----+--------+------+
2 rows in set (0.00 sec)




mysql> -- 刪除使用 set null 模式下, 刪除父表主鍵數據, 對應子表數據會為 null
mysql> delete from my_class where id = 3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_class;
+----+--------------+---------+
| id | c_name       | subject |
+----+--------------+---------+
|  4 | 三年二班     | JS      |
|  6 | 可憐吶       | 1       |
|  9 | 一年三班     | C       |
+----+--------------+---------+
3 rows in set (0.00 sec)

mysql> select * from my_fore3;
+----+--------+------+
| id | name   | c_id |
+----+--------+------+
|  1 | 大哥   |    9 |
|  2 | 小弟   | NULL |   -- null
+----+--------+------+
2 rows in set (0.00 sec)
```


```sql
mysql> -- set null 的前提為外鍵允許為 null

mysql> desc class2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc my_fore4;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| c_id   | int(11)     | NO   |     | NULL    |                |
| c_name | varchar(20) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


mysql> -- c_id 不允許為 null 時設置 set null
mysql> alter table my_fore4 add foreign key (c_id) references class2 (id) on delete set null;
ERROR 1830 (HY000): Column 'c_id' cannot be NOT NULL: needed in a foreign key constraint 'my_fore4_ibfk_1' SET NULL


mysql> -- 沒有設置 set null 就成功
mysql> alter table my_fore4 add foreign key (c_id) references class2 (id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
```




## 聯合查詢
> - 將多次查詢 (多條 select) 進行拼接 (字段不會增加)
> - 意義:
>   - 查詢同張表但需求不同, 例如一些數據要升序, 一些數據要降序
>   - 多表查詢(表結構都相同, 數據結構也相同), 因為數據過於龐大, 常用於拆表查詢
>     - 水平分表: 十條數據分成兩張表, 每張各五
>     - 垂直分表: 十個字段拆成兩張表, 每張各五

### 基本語句
`select 語句1 union [union 選項] select 語句2...`
> - union 選項
>   - all: 保留全部
>   - distinct : 去重複, 預設
> - 每條語句的字段數必須一致

```sql
mysql> -- 字段數必須一致
mysql> desc my_stu;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| c_id  | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc my_class;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


mysql> select * from my_class union select * from my_stu;
ERROR 1222 (21000): The used SELECT statements have a different number of columns


mysql> -- 數據類型並無要求
mysql> select id,name  from my_class union select name, id from my_class;
+------+------+
| id   | name |
+------+------+
| 1    | JS   |
| 2    | GO   |
| JS   | 1    |
| GO   | 2    |
+------+------+
4 rows in set (0.00 sec)
```

```sql
mysql> -- 去重
mysql> select * from my_class union select * from my_class;
+----+------+
| id | name |
+----+------+
|  1 | JS   |
|  2 | GO   |
+----+------+
2 rows in set (0.00 sec)


mysql> -- 不去重
mysql> select * from my_class union all select * from my_class;
+----+------+
| id | name |
+----+------+
|  1 | JS   |
|  2 | GO   |
|  1 | JS   |
|  2 | GO   |
+----+------+
4 rows in set (0.00 sec)
```


### order by
`(select 子句 order by 字段 [asc||desc] limit 長度) union (select 子句 order by 字段 [asc||desc] limit 長度)`
> - 聯合查詢中, order by 不能直接使用, 必須使用 () 包起來
> - 若要 order by 生效, 必須搭配 limit, limit 限定最大數即可
```sql
mysql> -- 聯合查詢用 order by時, 必須用 () 包起來
mysql> select * from my_stu where sex = 'male' order by age 
    -> union select * from my_stu where sex = 'female' order by age desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union select * from my_stu where sex = 'female' order by age desc' at line 2


mysql> -- 可以聯合, 但 order by 沒有效果
mysql> (select * from my_stu where sex = 'male' order by age)
    -> union (select * from my_stu where sex = 'female' order by age desc);
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  4 | KKBOX |    2 | male   |   18 |
|  3 | MEME  |    2 | female |   28 |
|  5 | MARY  |    1 | female |   20 |
|  6 | MAY   |    2 | female |   26 |
+----+-------+------+--------+------+
6 rows in set (0.00 sec)


mysql> -- 加 limit 後, order by 才有效果
mysql> (select * from my_stu where sex = 'male' order by age limit 9999) union (select * from my_stu where sex = 'female' order by age desc limit 9999);
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  4 | KKBOX |    2 | male   |   18 |
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  3 | MEME  |    2 | female |   28 |
|  6 | MAY   |    2 | female |   26 |
|  5 | MARY  |    1 | female |   20 |
+----+-------+------+--------+------+
6 rows in set (0.00 sec)

```


## 子查詢 (sub query)
> - 在查詢結果上進行查詢
> - 子查詢分類: 子查詢的分類只是把返回的結果或使用的情境分類, 純粹方便理解用
>   - from 子查詢 : 子查詢在 from 後面
>     - 表子查詢 : 子查詢結果為多行多列
>   - where 子查詢 : 子查詢在 where 條件中
>     - 標量子查詢 : 子查詢結果為一行一列
>     - 列子查詢 : 子查詢結果為多行一列
>     - 行子查詢 : 子查詢結果為一行多列(多行多列)
>   - exists 子查詢 : 子查詢在 exists 裡面


### 標量子查詢
> - 返回的結果為一行一列
```sql
mysql> -- 標量子查詢: 結果只有一行一列
mysql> select id from my_class where name = 'JS';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
```

```sql
mysql> select * from my_class;
+----+------+
| id | name |
+----+------+
|  1 | JS   |
|  2 | GO   |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from my_stu;
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  3 | MEME  |    2 | female |   28 |
|  4 | KKBOX |    2 | male   |   18 |
|  5 | MARY  |    1 | female |   20 |
|  6 | MAY   |    2 | female |   26 |
+----+-------+------+--------+------+
6 rows in set (0.00 sec)

mysql> -- 應用: 我想要查詢 JS 班上所有學生
mysql> select * from my_stu where c_id = (select id from my_class where name = 'JS');
+----+------+------+--------+------+
| id | name | c_id | sex    | age  |
+----+------+------+--------+------+
|  2 | BEBE |    1 | male   |   22 |
|  5 | MARY |    1 | female |   20 |
+----+------+------+--------+------+
2 rows in set (0.01 sec)

```


### 列子查詢
> - 返回的結果為單行多列
> - 由於返回的結果有多個值, 不能直接 `=`, 一般都是用 `in` 來判斷
>   - 其他類似的判斷條件關鍵字 `any` `some` `all`
>     - `in` == `=any` == `=some`
>     - `!=any` == `!=some`
>       - `!=any` 本來想表達的是不等於全部值, 也就是全選的意思
>       - 只是在英文的語意上發現怪怪的, 所以後來再多一個 some
>       - 也就是說 any 跟 some 是一模一樣的東西
>     - `not in` == `!=any`
```sql
mysql> select id from my_class;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)
```
```sql
mysql> select * from my_class;
+----+------+
| id | name |
+----+------+
|  1 | JS   |
|  2 | GO   |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from my_stu;
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  3 | MEME  |    2 | female |   28 |
|  4 | KKBOX |    2 | male   |   18 |
|  5 | MARY  |    1 | female |   20 |
|  6 | MAY   |    2 | female |   26 |
|  7 | GOGO  |    9 | male   |   28 |
+----+-------+------+--------+------+
7 rows in set (0.00 sec)


mysql> -- 應用: 查出所有在 my_class 的學員資訊
mysql> select * from my_stu where c_id in (select id from my_class);
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  3 | MEME  |    2 | female |   28 |
|  4 | KKBOX |    2 | male   |   18 |
|  5 | MARY  |    1 | female |   20 |
|  6 | MAY   |    2 | female |   26 |
+----+-------+------+--------+------+
6 rows in set (0.00 sec)
```
```sql
mysql> -- 肯定語句

mysql> select * from my_stu where c_id in (select id from my_class);
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  3 | MEME  |    2 | female |   28 |
|  4 | KKBOX |    2 | male   |   18 |
|  5 | MARY  |    1 | female |   20 |
|  6 | MAY   |    2 | female |   26 |  
+----+-------+------+--------+------+
6 rows in set (0.00 sec)


mysql> select * from my_stu where c_id = any (select id from my_class);
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  3 | MEME  |    2 | female |   28 |
|  4 | KKBOX |    2 | male   |   18 |
|  5 | MARY  |    1 | female |   20 |
|  6 | MAY   |    2 | female |   26 |
+----+-------+------+--------+------+
6 rows in set (0.00 sec)
mysql> -- 任何 c_id 等於 1 或 2 就返回 true


mysql> select * from my_stu where c_id = some (select id from my_class);
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  3 | MEME  |    2 | female |   28 |
|  4 | KKBOX |    2 | male   |   18 |
|  5 | MARY  |    1 | female |   20 |
|  6 | MAY   |    2 | female |   26 |
+----+-------+------+--------+------+
6 rows in set (0.00 sec)


mysql> select * from my_stu where c_id = all (select id from my_class);
Empty set (0.00 sec)
mysql> -- 任何值都不可能等於每一個值


mysql> -- 否定語句
mysql> select * from my_stu where c_id not in (select id from my_class);
+----+------+------+------+------+
| id | name | c_id | sex  | age  |
+----+------+------+------+------+
|  7 | GOGO |    9 | male |   28 |
+----+------+------+------+------+
1 row in set (0.01 sec)


mysql> select * from my_stu where c_id != any (select id from my_class);
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  3 | MEME  |    2 | female |   28 |
|  4 | KKBOX |    2 | male   |   18 |
|  5 | MARY  |    1 | female |   20 |
|  6 | MAY   |    2 | female |   26 |
|  7 | GOGO  |    9 | male   |   28 |
+----+-------+------+--------+------+
7 rows in set (0.00 sec)


mysql> select * from my_stu where c_id != some (select id from my_class);
+----+-------+------+--------+------+
| id | name  | c_id | sex    | age  |
+----+-------+------+--------+------+
|  1 | GODJJ |    2 | male   |   21 |
|  2 | BEBE  |    1 | male   |   22 |
|  3 | MEME  |    2 | female |   28 |
|  4 | KKBOX |    2 | male   |   18 |
|  5 | MARY  |    1 | female |   20 |
|  6 | MAY   |    2 | female |   26 |
|  7 | GOGO  |    9 | male   |   28 |
+----+-------+------+--------+------+
7 rows in set (0.01 sec)


mysql> select * from my_stu where c_id != all (select id from my_class);
+----+------+------+------+------+
| id | name | c_id | sex  | age  |
+----+------+------+------+------+
|  7 | GOGO |    9 | male |   28 |
+----+------+------+------+------+
1 row in set (0.00 sec)
```

### 行子查詢
> - 返回的結果多行多列(一行多列)
```sql
mysql> select * from my_class;
+----+------+
| id | name |
+----+------+
|  1 | JS   |
|  2 | GO   |
+----+------+
2 rows in set (0.00 sec)
```

```sql
mysql> select * from my_stu;
+----+--------+------+--------+------+--------+
| id | name   | c_id | sex    | age  | height |
+----+--------+------+--------+------+--------+
|  1 | GODJJ  |    2 | male   |   21 |    171 |
|  2 | BEBE   |    1 | male   |   22 |    163 |
|  3 | MEME   |    2 | female |   28 |    166 |
|  4 | KKBOX  |    2 | male   |   18 |    178 |
|  5 | MARY   |    1 | female |   20 |    172 |
|  6 | MAY    |    2 | female |   26 |    170 |
|  7 | GOGO   |    9 | male   |   28 |    172 |
|  8 | HOWHOW |    2 | male   |   79 |    200 |
+----+--------+------+--------+------+--------+
8 rows in set (0.00 sec)


mysql> -- 應用: 調出那個年齡最大且剛好最高的老人資料


mysql> -- 方法1. 用兩次標量子查詢
mysql> select * from my_stu where age = (select max(age) from my_stu) and height = (select max(height) from my_stu);
+----+--------+------+------+------+--------+
| id | name   | c_id | sex  | age  | height |
+----+--------+------+------+------+--------+
|  8 | HOWHOW |    2 | male |   79 |    200 |
+----+--------+------+------+------+--------+
1 row in set (0.01 sec)


mysql> -- 方法2. 組出行元素來用行子查詢比較
mysql> select * from my_stu 
    -> where (age, height)  -- 行元素
    -> = (select max(age), max(height) from my_stu);  -- 行子查詢
+----+--------+------+------+------+--------+
| id | name   | c_id | sex  | age  | height |
+----+--------+------+------+------+--------+
|  8 | HOWHOW |    2 | male |   79 |    200 |
+----+--------+------+------+------+--------+
1 row in set (0.00 sec)
```

### 表子查詢
> - 子查詢返回的是多行多列的表, 使用該表當成數據源來使用
```sql
mysql> -- 暫時想不到情境, 寫個形式出來

mysql> select * from (select * from my_stu order by age) as a;
+----+--------+------+--------+------+--------+
| id | name   | c_id | sex    | age  | height |
+----+--------+------+--------+------+--------+
|  4 | KKBOX  |    2 | male   |   18 |    178 |
|  5 | MARY   |    1 | female |   20 |    172 |
|  1 | GODJJ  |    2 | male   |   21 |    171 |
|  2 | BEBE   |    1 | male   |   22 |    163 |
|  6 | MAY    |    2 | female |   26 |    170 |
|  3 | MEME   |    2 | female |   28 |    166 |
|  7 | GOGO   |    9 | male   |   28 |    172 |
|  8 | HOWHOW |    2 | male   |   79 |    200 |
+----+--------+------+--------+------+--------+
8 rows in set (0.00 sec)
```
> - 把返回來的二維表再做成一個表使用


### exists 子查詢
`exists()`
> - `exists()` 返回的是 0 或 1, 表達是否存在的意思
> - 通常用在跨表上, 接在 where 之後

```sql
mysql> select exists(select * from my_stu where id = 2);
+-------------------------------------------+
| exists(select * from my_stu where id = 2) | -- id = 2 的資料有存在
+-------------------------------------------+
|                                         1 | -- 返回 1 
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select exists(select * from my_stu where id = 100);
+---------------------------------------------+
| exists(select * from my_stu where id = 100) | -- id = 100 的資料不存在
+---------------------------------------------+
|                                           0 | -- 返回 0
+---------------------------------------------+
1 row in set (0.00 sec)
```

```sql
mysql> -- 目前想不到情境, 形式寫一下
mysql> select * from my_stu where exists(select * from my_stu where id = 1);
+----+--------+------+--------+------+--------+
| id | name   | c_id | sex    | age  | height |
+----+--------+------+--------+------+--------+
|  1 | GODJJ  |    2 | male   |   21 |    171 |
|  2 | BEBE   |    1 | male   |   22 |    163 |
|  3 | MEME   |    2 | female |   28 |    166 |
|  4 | KKBOX  |    2 | male   |   18 |    178 |
|  5 | MARY   |    1 | female |   20 |    172 |
|  6 | MAY    |    2 | female |   26 |    170 |
|  7 | GOGO   |    9 | male   |   28 |    172 |
|  8 | HOWHOW |    2 | male   |   79 |    200 |
+----+--------+------+--------+------+--------+
8 rows in set (0.00 sec)
mysql> -- 如果一班存在, 顯示全校資料
```




## 視圖 (view)
> - 視圖是一種有結構沒結果的虛擬表
> - 視圖結構來源來自於對應的基表產生(視圖數據來源)

### 視圖作用
> - 視圖可以節省 SQL 語句: 將重複執行的查詢語句儲存, 減少打錯的可能
> - 數據安全: 
>   - 視圖本身不帶數據, 刪了也沒差
>   - 視圖經常用於多系統, 對外提供數據時, 可以隱藏關鍵或沒用的數據, 
>     甚至每個字段名都可以是重命名過的
>   - 可以通過視圖來客製化數據提供


### 創建視圖
`create view 視圖名字 as select 語句`
> - 如果創建多表視圖, 注意字段不能重複
> - 視圖創建後, 系統會在對應的數據庫文件下創建一個結構文件(frm)
```sql
mysql> -- 單表視圖
mysql> create view my_view as select * from my_class;
Query OK, 0 rows affected (0.02 sec)

mysql> -- 多表視圖
mysql> -- 字段名不能重複
mysql> create view my_view2 as select * from my_class left join my_stu on my_class.id = my_stu.c_id;
ERROR 1060 (42S21): Duplicate column name 'id' -- id 重複了


mysql> create view my_view2 as select my_stu.*, my_class.name as c_name from my_stu left join my_class on my_stu.c_id = my_class.id;
Query OK, 0 rows affected (0.01 sec)
```


### 查看視圖結構
> - 表查看的語句, 視圖都能用
> - 視圖在查看表創建語句時, table 可以改成 view

```sql
mysql> show create table my_view\G
*************************** 1. row ***************************
                View: my_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `my_view` AS select `my_class`.`id` AS `id`,`my_class`.`name` AS `name` from `my_class`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)


mysql> show create view my_view\G
*************************** 1. row ***************************
                View: my_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `my_view` AS select `my_class`.`id` AS `id`,`my_class`.`name` AS `name` from `my_class`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)
```


### 使用視圖
> - 視圖使用主要是為了查詢, 當作普通的表使用即可
> - 視圖的執行, 本質上就是執行封裝的 select 語句

```sql
mysql> select * from my_view;
+----+------+
| id | name |
+----+------+
|  1 | JS   |
|  2 | GO   |
+----+------+
2 rows in set (0.00 sec)
mysql> -- 本質上就是執行 select * from my_class;


mysql> select * from my_view2;
+----+--------+------+--------+------+--------+--------+
| id | name   | c_id | sex    | age  | height | c_name |
+----+--------+------+--------+------+--------+--------+
|  2 | BEBE   |    1 | male   |   22 |    163 | JS     |
|  5 | MARY   |    1 | female |   20 |    172 | JS     |
|  1 | GODJJ  |    2 | male   |   21 |    171 | GO     |
|  3 | MEME   |    2 | female |   28 |    166 | GO     |
|  4 | KKBOX  |    2 | male   |   18 |    178 | GO     |
|  6 | MAY    |    2 | female |   26 |    170 | GO     |
|  8 | HOWHOW |    2 | male   |   79 |    200 | GO     |
|  7 | GOGO   |    9 | male   |   28 |    172 | NULL   |
+----+--------+------+--------+------+--------+--------+
8 rows in set (0.01 sec)
mysql> -- 本質上就是執行 select my_stu.*, my_class.name as c_name from my_stu left join my_class on my_stu.c_id = my_class.id;
mysql> -- 如果每次都要寫那麼長, 那去賣雞排比較好, 視圖本質就是用來封裝長 select
```

### 修改視圖
`alter view 視圖名字 as 新的 select 語句`
> - 視圖本身不能修改, 但是可以修改視圖的來源
```sql
mysql> select * from my_view;
+----+------+
| id | name |
+----+------+
|  1 | JS   |
|  2 | GO   |
+----+------+
2 rows in set (0.00 sec)


mysql> -- 修改視圖
mysql> alter view my_view as select * from my_stu;
Query OK, 0 rows affected (0.01 sec)


mysql> select * from my_view;
+----+--------+------+--------+------+--------+
| id | name   | c_id | sex    | age  | height |
+----+--------+------+--------+------+--------+
|  1 | GODJJ  |    2 | male   |   21 |    171 |
|  2 | BEBE   |    1 | male   |   22 |    163 |
|  3 | MEME   |    2 | female |   28 |    166 |
|  4 | KKBOX  |    2 | male   |   18 |    178 |
|  5 | MARY   |    1 | female |   20 |    172 |
|  6 | MAY    |    2 | female |   26 |    170 |
|  7 | GOGO   |    9 | male   |   28 |    172 |
|  8 | HOWHOW |    2 | male   |   79 |    200 |
+----+--------+------+--------+------+--------+
8 rows in set (0.00 sec)
```

### 刪除視圖
`drop view 視圖名字`
> - 視圖本身根本沒有數據, 所以刪除視圖對數據沒有任何影響
```sql
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| my_class        |
| my_stu          |
| my_view         |
| my_view2        |
+-----------------+
4 rows in set (0.00 sec)

mysql> drop view my_view2;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| my_class        |
| my_stu          |
| my_view         |
+-----------------+
3 rows in set (0.00 sec)
```


### 數據新增
> - 事實上視圖是可以對基表操作的
> - 數據新增條件:
>   - 多表視圖無法新增數據
>   - 如果基表有字段要求不能為空且無預設值, 而視圖沒有該字段可以填, 那當然填不進去
>     換言之, 視圖必須有基表不能為空的字段

```sql
mysql> --單表視圖插入數據

mysql> desc my_stu;
+---------+-----------------------+------+-----+---------+----------------+
| Field   | Type                  | Null | Key | Default | Extra          |
+---------+-----------------------+------+-----+---------+----------------+
| id      | int(11)               | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)           | YES  |     | NULL    |                |
| c_id    | int(11)               | YES  |     | NULL    |                |
| sex     | enum('male','female') | YES  |     | NULL    |                |
| age     | tinyint(3) unsigned   | YES  |     | NULL    |                |
| height  | tinyint(3) unsigned   | YES  |     | NULL    |                |
| notNull | varchar(20)           | NO   |     | NULL    |                | -- 這個字段不能為空
+---------+-----------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)



mysql> desc my_view;  -- 這張視圖沒有該字段欄位
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id     | int(11)               | NO   |     | 0       |       |
| name   | varchar(20)           | YES  |     | NULL    |       |
| c_id   | int(11)               | YES  |     | NULL    |       |
| sex    | enum('male','female') | YES  |     | NULL    |       |
| age    | tinyint(3) unsigned   | YES  |     | NULL    |       |
| height | tinyint(3) unsigned   | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)


mysql> -- 插不進去
mysql> insert into my_view values (null, 'TEST2', 20, 'male', 37, 109);
ERROR 1423 (HY000): Field of view 'test2.my_view' underlying table doesn't have a default value


mysql> - 把該欄位改成可以為空後
mysql> alter table my_stu modify notNull varchar(20);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> -- 數據就能寫進去了, 該欄位沒有預設值, 所以寫入 null
mysql> insert into my_view values (null, 'TEST2', 20, 'male', 37, 109);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_stu;
+----+--------+------+--------+------+--------+---------+
| id | name   | c_id | sex    | age  | height | notNull |
+----+--------+------+--------+------+--------+---------+
|  1 | GODJJ  |    2 | male   |   21 |    171 |         |
|  2 | BEBE   |    1 | male   |   22 |    163 |         |
|  3 | MEME   |    2 | female |   28 |    166 |         |
|  4 | KKBOX  |    2 | male   |   18 |    178 |         |
|  5 | MARY   |    1 | female |   20 |    172 |         |
|  6 | MAY    |    2 | female |   26 |    170 |         |
|  7 | GOGO   |    9 | male   |   28 |    172 |         |
|  8 | HOWHOW |    2 | male   |   79 |    200 |         |
|  9 | TEST   |   20 | male   |   37 |    109 |         |
| 10 | TEST2  |   20 | male   |   37 |    109 | NULL    |
+----+--------+------+--------+------+--------+---------+
10 rows in set (0.00 sec)
```

```sql
mysql> -- 多表視圖

mysql> desc my_view2; -- 這張是多表視圖
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id     | int(11)               | NO   |     | 0       |       |
| name   | varchar(20)           | YES  |     | NULL    |       |
| c_id   | int(11)               | YES  |     | NULL    |       |
| sex    | enum('male','female') | YES  |     | NULL    |       |
| age    | tinyint(3) unsigned   | YES  |     | NULL    |       |
| height | tinyint(3) unsigned   | YES  |     | NULL    |       |
| c_name | varchar(20)           | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)


mysql> -- 插入數據報錯
mysql> insert into my_view2 values (null, 'KK', 2, 'male', 100, 100, 'JAVA');
ERROR 1471 (HY000): The target table my_view2 of the INSERT is not insertable-into
```


### 刪除數據
`delete from 視圖名 [where]`
> - 多表視圖 (join 系列) 不能刪
```sql
mysql> select * from my_view2;
+----+-------+------+--------+------+--------+--------+
| id | name  | c_id | sex    | age  | height | c_name |
+----+-------+------+--------+------+--------+--------+
|  2 | BEBE  |    1 | male   |   22 |    163 | JS     |
|  5 | MARY  |    1 | female |   20 |    172 | JS     |
|  1 | GODJJ |    2 | male   |   21 |    171 | GO     |
|  3 | MEME  |    2 | female |   28 |    166 | GO     |
|  4 | KKBOX |    2 | male   |   18 |    178 | GO     |
|  6 | MAY   |    2 | female |   26 |    170 | GO     |
|  7 | GOGO  |    9 | male   |   28 |    172 | NULL   |
| 10 | TEST2 |   20 | male   |   37 |    109 | NULL   |
+----+-------+------+--------+------+--------+--------+
8 rows in set (0.00 sec)

mysql> delete from my_view2 where id = 10;
ERROR 1288 (HY000): The target table my_view2 of the DELETE is not updatable
```
> - 單表視圖可以刪
```sql
mysql> select * from my_stu;
+----+--------+------+--------+------+--------+---------+
| id | name   | c_id | sex    | age  | height | notNull |
+----+--------+------+--------+------+--------+---------+
|  1 | GODJJ  |    2 | male   |   21 |    171 |         |
|  2 | BEBE   |    1 | male   |   22 |    163 |         |
|  3 | MEME   |    2 | female |   28 |    166 |         |
|  4 | KKBOX  |    2 | male   |   18 |    178 |         |
|  5 | MARY   |    1 | female |   20 |    172 |         |
|  6 | MAY    |    2 | female |   26 |    170 |         |
|  7 | GOGO   |    9 | male   |   28 |    172 |         |
|  8 | HOWHOW |    2 | male   |   79 |    200 |         |
|  9 | TEST   |   20 | male   |   37 |    109 |         |
| 10 | TEST2  |   20 | male   |   37 |    109 | NULL    |
+----+--------+------+--------+------+--------+---------+
10 rows in set (0.00 sec)


mysql> delete from my_view where id in (8,9);
Query OK, 2 rows affected (0.01 sec)


mysql> select * from my_stu;
+----+-------+------+--------+------+--------+---------+
| id | name  | c_id | sex    | age  | height | notNull |
+----+-------+------+--------+------+--------+---------+
|  1 | GODJJ |    2 | male   |   21 |    171 |         |
|  2 | BEBE  |    1 | male   |   22 |    163 |         |
|  3 | MEME  |    2 | female |   28 |    166 |         |
|  4 | KKBOX |    2 | male   |   18 |    178 |         |
|  5 | MARY  |    1 | female |   20 |    172 |         |
|  6 | MAY   |    2 | female |   26 |    170 |         |
|  7 | GOGO  |    9 | male   |   28 |    172 |         |
| 10 | TEST2 |   20 | male   |   37 |    109 | NULL    |
+----+-------+------+--------+------+--------+---------+
8 rows in set (0.00 sec)
```



### 更新數據
> - 單表視圖可更新
```sql
mysql> select * from my_view;
+----+-------+------+--------+------+--------+
| id | name  | c_id | sex    | age  | height |
+----+-------+------+--------+------+--------+
|  1 | GODJJ |    2 | male   |   21 |    171 |
|  2 | BEBE  |    1 | male   |   22 |    163 |
|  3 | MEME  |    2 | female |   28 |    166 |
|  4 | KKBOX |    2 | male   |   18 |    178 |
|  5 | MARY  |    1 | female |   20 |    172 |
|  6 | MAY   |    2 | female |   26 |    170 |
|  7 | GOGO  |    9 | male   |   28 |    172 |
| 10 | TEST2 |   20 | male   |   37 |    109 |
+----+-------+------+--------+------+--------+
8 rows in set (0.00 sec)

mysql> update my_view set height = 150 where id = 10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_stu;
+----+-------+------+--------+------+--------+---------+
| id | name  | c_id | sex    | age  | height | notNull |
+----+-------+------+--------+------+--------+---------+
|  1 | GODJJ |    2 | male   |   21 |    171 |         |
|  2 | BEBE  |    1 | male   |   22 |    163 |         |
|  3 | MEME  |    2 | female |   28 |    166 |         |
|  4 | KKBOX |    2 | male   |   18 |    178 |         |
|  5 | MARY  |    1 | female |   20 |    172 |         |
|  6 | MAY   |    2 | female |   26 |    170 |         |
|  7 | GOGO  |    9 | male   |   28 |    172 |         |
| 10 | TEST2 |   20 | male   |   37 |    150 | NULL    |  -- 150 高
+----+-------+------+--------+------+--------+---------+
8 rows in set (0.00 sec)
```
> #### 更新限制
> `with check option`
> - 如果在創建視圖時限制了某些字段, 對視圖進行更新操作時, 系統會進行驗證:
>   必須保證更新後數據依然可以被查到, 否則失敗
> - 如果操作非視圖外的數據, 操作沒有任何效果

```sql
mysql> select * from my_stu;
+----+-------+------+--------+------+--------+---------+
| id | name  | c_id | sex    | age  | height | notNull |
+----+-------+------+--------+------+--------+---------+
|  1 | GODJJ |    2 | male   |   21 |    171 |         |
|  2 | BEBE  |    1 | male   |   22 |    163 |         |
|  3 | MEME  |    2 | female |   28 |    166 |         |
|  4 | KKBOX |    2 | male   |   18 |    178 |         |
|  5 | MARY  |    1 | female |   20 |    172 |         |
|  6 | MAY   |    2 | female |   26 |    170 |         |
|  7 | GOGO  |    9 | male   |   28 |    172 |         |
| 10 | TEST2 |   20 | male   |   37 |    150 | NULL    |
+----+-------+------+--------+------+--------+---------+
8 rows in set (0.00 sec)

mysql> -- 創建視圖時, 限制年齡必須大於 25
mysql> create view my_view3 as select * from my_stu where age > 25 with check option;
Query OK, 0 rows affected (0.01 sec)


mysql> -- 創建的視圖
mysql> select * from my_view3;
+----+-------+------+--------+------+--------+---------+
| id | name  | c_id | sex    | age  | height | notNull |
+----+-------+------+--------+------+--------+---------+
|  3 | MEME  |    2 | female |   28 |    166 |         |
|  6 | MAY   |    2 | female |   26 |    170 |         |
|  7 | GOGO  |    9 | male   |   28 |    172 |         |
| 10 | TEST2 |   20 | male   |   37 |    150 | NULL    |
+----+-------+------+--------+------+--------+---------+
4 rows in set (0.00 sec)


mysql> -- 把年齡改成 20 , 由於低於 25, 改之後會查不到, 所以失敗
mysql> update my_view3 set age = 20 where id = 10;
ERROR 1369 (HY000): CHECK OPTION failed 'test2.my_view3'

mysql> -- 改成 30 就可以
mysql> update my_view3 set age = 30 where id = 10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> -- 修改非視圖的數據時, 不會有任何效果
mysql> update my_view3 set age = 30 where id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0   -- Changed: 0


mysql> select * from my_stu;
+----+-------+------+--------+------+--------+---------+
| id | name  | c_id | sex    | age  | height | notNull |
+----+-------+------+--------+------+--------+---------+
|  1 | GODJJ |    2 | male   |   21 |    171 |         |
|  2 | BEBE  |    1 | male   |   22 |    163 |         |
|  3 | MEME  |    2 | female |   28 |    166 |         |
|  4 | KKBOX |    2 | male   |   18 |    178 |         |  -- 還是 18
|  5 | MARY  |    1 | female |   20 |    172 |         |
|  6 | MAY   |    2 | female |   26 |    170 |         |
|  7 | GOGO  |    9 | male   |   28 |    172 |         |
| 10 | TEST2 |   20 | male   |   30 |    150 | NULL    |
+----+-------+------+--------+------+--------+---------+
8 rows in set (0.00 sec)
```

### 視圖算法
`create [ALGORITHM = 視圖算法] view 視圖名字 as select 語句`
> - 視圖本身就是一組 select 語句, 那麼再次對視圖執行 select 語句時, 就會形成子查詢, 
>   如果對視圖操作的 select 語句用了規定上比視圖的 select 語句還要前面的查詢語句
>   那麼應該先操作視圖 (數據源) 的語句, 還是按照原本規定的順序?
```sql
mysql> -- 創建一張視圖, 裡面帶有 having 判斷
mysql> create view my_view4 as select * from my_stu having age < 25;
Query OK, 0 rows affected (0.01 sec)


mysql> -- 對視圖進行查詢操作且帶有 group 操作時, 會產生先做 group 還是 having 的問題
mysql> -- 下面這句攤開來會長這樣:
mysql> -- select age from (select * from my_stu having age < 25) as tmp group by age;
mysql> -- 此時究竟是先做數據源的 select having, 還是先做 group?

mysql> select age from my_view4 group by age;
+------+
| age  |
+------+
|   21 |
|   22 |
|   18 |
|   20 |
+------+
4 rows in set (0.00 sec)
```



> - 系統對此情況的解析方式, 分為三種
>   - `undefined` : 
>     - 未定義(默認), 讓系統自行依情況選擇
>     - 大部分情況下, 系統都會使用 `merge`
>   - `temptable` : 
>     - 先執行視圖的 select 語句, 後執行外部查詢語句
>     - 以上面的例子來說, 就是先 having 後 group
>   - `merge` : 
>     - 將兩個查詢語句合併後查詢(只查詢一次, 效率較高)
>     - 以上面的例子來說, 會將兩個語句合併而變成
>       `select age from my_stu group by age having age < 25;`
>       而先 group 後 having

> - 只考慮是否達成想要的效果, 而想執行這類違反順序規定的語句時, 就改成 tempable, 
>   其他時候默認即可, 因為系統比較愛 merge, 一般都會自動轉用這個
