# 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)
```
> - 第二次分組會視組數而定
> - 以下列為例
> 
> - 先將資料以班級分類, 總共三班(一次回朔)
> - 再依性別分類, 總共兩性(三筆回朔, 三個班分類), 共四筆
```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, 一般都會自動轉用這個
Sign in with Wallet
Connect another wallet