# SQL
> - 保存數據的容器
> - Array, JSON, ...: 存在Ram中, 不會永久保存
> - 文件: 存在硬碟, 但不利查詢
> - 數據庫: 大部分都直接存在硬碟又利於查詢
> - 基於存儲地不同, 可分為兩類
> - 關係型數據庫(SQL):
> - 規模
> - 大型: Oracle, DB2
> - 中型: MySQL, SQL-SERVER
> - 小型: Access
> - 安全(放在硬碟比較不會丟失), 浪費空間(表格裡不放數據還是佔據一格)
> - 非關係型數據庫(NoSQL: Not Only SQL)
> - memcached, mongodb, redis
> - 主要在 Ram 運行
> - 同步到硬碟, 開機在拿回 Ram 中
> - 效率高, 較不安全
> - KeyWord
> - DBS (Database System) = DBMS + DB
> - DB (Database): 容器
> - DBMS (DateBase Management System) : 操作 DB 的程式
> - 共享文件系統型 Access
> - c/s 結構 MySQL, Oracle, ...
> - DBA (Database Administrator): 數據管理員
> - SQL (Structured Query Language): 操作數據庫的語法
> - DDL (Data Definition Language):
> - 維護定義存儲結構(表和庫)
> - 如 `create` `drop` `alter`
> - DML (Data Manipulation Language):
> - 對數據操作(表中的內容)
> - 如 `insert` `delete` `update`
> - DQL (Data Query Language)
> - 數據查詢語言
> - 如 `select`
> - DCL (Data Control Language)
> - 權限管理(用戶)
> - 如 `grant` `revoke`
> - SQL 是一種約束, 並不強制, 亦即可能每種系統都有些許差異
> - row / record (行 / 紀錄):
> - 都是指表中的一行數據, 出發點不同而導致名稱不同
> - 行是從結構角度出發, 紀錄是從數據角度出發
> - column / field (列 / 字段): 跟上面一樣, 角度不同
> <img src='https://i.imgur.com/D3aghYq.jpg' style='width: 300px;'/>
>
> - 特色:
> - 數據放到表, 表再放到庫
> - 庫有多個表, 每表都有名
> - 表具備特性, 用來定義數據存儲方式
> - 表由列組成, 數據行儲存
## 關係型數據庫
> - 建立在關係模型(數學模型)的數據庫
> - 關係模型由三個方面
> - 數據結構: 表(行與列)
> - 操作指令: 所有 SQL 語句
> - 完整性約束:
> - 表內約束(字段與字段)
> - 表與表約束(外鍵)
> - 表頭與數據組成
> #### 選手表
> |姓名|年齡|位置|備註|
> |:--:|:--:|:--:|:--:|
> |GodJJ|20|AD|BaBABou|
> |Toyz|20|Mid|$$$|
> |月|20|||
> - 第二行所有字段都是在描述 GodJJ(內部關係)
> - 第二列只能放年齡(內部約束)
> - 表中對應沒有值, 格子還是必須留著(浪費空間)
> - 關係: 每個學生一定在某個班級, 某個班級有多個學生
> #### 戰隊表:
> |遊戲|戰隊|
> |:--:|:--:|
> |LOL|SKT|
> |LOL|AHQ|
> |跑跑|橘子熊|
>
> |姓名|年齡|位置|備註|遊戲|
> |:--:|:--:|:--:|:--:|:--:|
> |GodJJ|20|AD|BaBABou|LOL|
> |Toyz|20|Mid|$$$|LOL|
> |月|20|||跑跑|
> - 選手與遊戲的關聯關係
## MySQL
> - C/S (Client/Server)
> - 服務器需一直運行, 客戶端需要時運行
> - 交互方式:
> - 連接認證: 連接服務器, 身份認證
> - `mysql -hlocalhost -P3306 -uroot -p`
> - `程序 -h電腦 -P端口 -u帳號 -p密碼`
> - 客戶端發送 SQL 指令
> - 服務端接收 SQL 指令, 返回結果
> - 客戶端接收結果, 顯示結果
> - 斷開連接, 釋放資源(服務器併發限制)
> - `exit` `quit` `-q`
> - SQL 的註釋
> ```sql
> mysql> -- 哈哈
> mysql> # 哈哈
> ```
### MAC安裝 (homebrew)
> - 參考這篇的: https://juejin.im/post/5cc2a52ce51d456e7079f27f
> #### 版本
```shell
$ mysql -V
mysql Ver 8.0.18 for osx10.15 on x86_64 (Homebrew)
$ # 安裝的地點: /usr/local/Cellar/mysql/8.0.18_1
```
> #### 安裝
`$ brew install mysql`
> #### 配置環境變量
`$ vi ~/.zshrc`
```
echo 'export PATH="/usr/local/opt/protobuf@3.7/bin:$PATH"' >>
```
> #### 開啟 mysql 服務器
`$ mysql.server start`
> #### 設置密碼(記得先啟動服務器才設置)
`mysql_secure_installation`
```shell
$ mysql.server start
Starting MySQL
.. SUCCESS!
$ mysql_secure_installation # 接著會問一堆問題, 其實 Google 翻譯就看得懂了
# 要不要設密碼
Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: y
# 設置密碼強度, 我測試用而已 設 0
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.
New password:
Re-enter new password:
# 密碼分數,問你滿不滿意
Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
# 要不要砍掉匿名帳戶, 這是預設給你進來創建新帳戶的, 我沒砍
Remove anonymous users? (Press y|Y for Yes, any other key for No) : n
# 禁止遠程登入? 我覺得我可能會用到, 所以沒禁
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
# 要不要砍測試用的數據? 我都留著
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n
# 現在要更新表嗎? 好
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
```
> #### 連接認證
> - `mysql -uroot -p密碼` 或 `mysql -uroot -p` 後再打密碼
> #### 關閉服務器
> `mysql.server stop`
> #### 重啟服務器
> `mysql.server restart`
> #### 查看密碼策略
> `SHOW VARIABLES LIKE 'validate_password%';`
> #### 設置密碼強度
> `SET GLOBAL validate_password.policy=LOW;`
> #### 設置密碼最低長度
> `SET GLOBAL validate_password.length=6;`
> #### 重設密碼
> `ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';`
## 庫操作
### 新增數據庫
`create database databaseName [option]`
> - `[option]` : 約束數據庫用的
> - `charset` / `character` 具體字符集 (數據存儲編碼格式):
> - 常用: utf8 (沒有 `-` !!!)
> - `collate` : 數據校對集(數據比較規則)
> - 校對集依賴字符集
> - 不設置也沒差, 不設置就會使用 DBMS 的默認, 安裝 MySQL 時設置的
> - 數據庫名不能用關鍵字(已被使用的字)或保留字(以後可能會用到的字)
> ```sql
> mysql> create database database charset utf8;
> 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 'database charset utf8' at line 1
> ```
> - 如果堅持使用, 要用 `` 包起來
> ```sql
> create database `database` charset utf8;
> Query OK, 1 row affected, 1 warning (0.00 sec)
> ```
> - 使用中文也沒問題, 如果有問題的話要改編碼 `set names BIG5`
> ```sql
> mysql> create database 是在哈樓 charset utf8;
> Query OK, 1 row affected, 1 warning (0.01 sec)
>
> mysql> create database 是在哈楼 charset utf8;
> Query OK, 1 row affected, 1 warning (0.00 sec)
> ```
> - 盡量別用中文, 因為存取名字是編碼存取
> ```shell
> $ cd /usr/local/var/mysql/
> $ cat
> @662f@5728@54c8@697c ... ... ... #=> 是在哈樓
> @662f@5728@54c8@6a13 ... ... ... #=> 是在哈楼
> ```
> - 保存數據的文件夾(brew 安裝的)
> `/usr/local/var/mysql/`
> - 每個數據庫下都有一個 opt 文件, 存放庫選項 (我還沒找到==)
### 查看數據庫
> - 查看所有數據庫
> `show databases;`
> - 查看條件數據庫 (模糊查詢)
> `show databases like 'pattern'`
> - `pattern` : 匹配模式
> - `%` : 匹配多個字符
> - `_` : 匹配單個字符
> - 如果數據庫名本身就有這符號, 需要 `\` 來轉譯
```sql
mysql> -- 先創建一個 informationtest 來比較
mysql> create database informationtest charset utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| informationtest |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> show databases like 'information_%'; -- 跟 'information%' 是一樣意思
+--------------------------+
| Database (information_%) |
+--------------------------+
| information_schema |
| informationtest |
+--------------------------+
mysql> show databases like 'information\_%';
+---------------------------+
| Database (information\_%) |
+---------------------------+
| information_schema |
+---------------------------+
```
> - 查看數據庫創建語句
> `show create database 數據庫名;`
```sql
mysql> show create database informationtest;
+------------------+--------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+------------------+--------------------------------------------------------------------------------------------------------------+
| information_test | CREATE DATABASE `informationtest` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+------------------+--------------------------------------------------------------------------------------------------------------+
mysql> # 我創建時是寫 create database informationtest charset utf8;
mysql> # 但是他顯示的是如此
mysql> # 因為數據庫在執行 SQL 語句之前會先優化 SQL, 現在看到的是優化後的結果
```
### 更新數據庫
> - 數據庫名字不能改, 因為改了很麻煩, 影響層面太大(所有使用這個數據庫的人)
> - 數據庫修改僅限庫選項
> `Alter database databaseName [=] [option]`
> - `charset` / `character`
> - `Collate`
> - `=` 可寫可不寫
```sql
mysql> alter database information_test charset BIG5;
Query OK, 1 row affected (0.01 sec)
```
### 刪除數據庫
`drop database databaseName`
> - 數據庫刪除是級聯刪除, 刪了就沒了, 因為數據庫是不段的被寫入的,
> 刪掉後繼續操作寫入就會大大減少找回的機率了, 準備過從刪庫到跑路的人生
```sql
mysql> drop database `database`;
Query OK, 0 rows affected (0.01 sec)
mysql> drop database 是在哈樓;
Query OK, 0 rows affected (0.01 sec)
```
## 表
> - 表包含表本身與字段
### 新增數據表
```sql
create table [if not exists] tableName(
字段名1 數據類型,
字段名2 數據類型,
...
字段名n 數據類型 -- 最後一筆不用逗號, 參數最後沒有逗號
) [option]
```
> - `if not exists` : 判斷表名存在與否, 不存在才執行創建
> - `option`
> - `charset` / `character set` :
> - 具體字符集, 保證表中數據存儲的字符集
> - 如果沒設就會使用數據庫所設置的, 數據庫沒設就會用 DBMS 的
> - `collate`
> - `engine`
> - 存儲引擎
> - `innodb` `myisam`
> - 新增數據表時, 當然要在數據庫下新增或指定數據庫, 否則誰知道你要存哪
```sql
mysql> create table if not exists player (
-> name varchar(10),
-> age varchar(10),
-> play varchar(10)
-> ) charset utf8;
ERROR 1046 (3D000): No database selected -- 誰知道你要存哪
```
> #### 方法一: 指定數據庫創建
> `create table databaseName.tableName()`
```sql
mysql> create database test charset utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> create table if not exists test.player (
-> name varchar(10),
-> age varchar(10),
-> play varchar(10)
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
```
> #### 方法二: 進入數據庫創建
> - `use databaseName;` 進入數據庫
> - `create table tableName()`
```sql
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table if not exists coach (
-> name varchar(10),
-> age varchar(10),
-> money int
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
```
> - 指令輸入成功後, 指定數據庫下就創建了數據表
> - 對應的文件夾下會產生結構文件 (與存儲引擎有關), 這些文件是反編譯文件
> - 編譯: 高級語言編譯成二進制
> - 反編譯: 二進制轉高級語言
```shell
$ cd /usr/local/var/mysql/test/
$ ls
coach.ibd player.ibd
```
### 查看表
> #### 查看所有表
> `show tables;`
> #### 查看部分表
> `show tables like 'pattern'`
> - 前面模糊的效率低, 後面模糊的效率比較高
```sql
mysql> show tables like '%er'; -- 效率低
+----------------------+
| Tables_in_test (%er) |
+----------------------+
| player |
+----------------------+
1 row in set (0.00 sec)
mysql> show tables like 'p%';
+---------------------+
| Tables_in_test (p%) |
+---------------------+
| player |
+---------------------+
1 row in set (0.00 sec)
```
> #### 查看表的創建語句
> - `show create table tableName;`
> - `show create table tableName\g`
> - `\g === ;`
> - `show create table tableName\G`
> - `\G` 會將結果變成縱向的, 比較好看
```sql
mysql> show create table player\g
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| player | CREATE TABLE `player` (
`name` varchar(10) DEFAULT NULL,
`age` varchar(10) DEFAULT NULL,
`play` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> show create table player\G
*************************** 1. row ***************************
Table: player
Create Table: CREATE TABLE `player` (
`name` varchar(10) DEFAULT NULL,
`age` varchar(10) DEFAULT NULL,
`play` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```
> #### 查看表結構
> - 查看表中的字段類型
> - `desc` / `describe` / `show columns form` 表名
> - 三個都可
> - `Field` : 字段名
> - `Type` : 列類型, 字段(數據)類型
> - `Null` : 列屬性: 是否允許為空
> - `Key` : 索引: 索引類型, PRI 主鍵, UNI 唯一鍵
> - `Default` : 列屬性: 默認值
> - `Extra` : 列屬性: 擴充
```sql
mysql> desc player;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| age | varchar(10) | YES | | NULL | |
| play | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
```
### 修改表
> #### 修改表本身 (表名, 表選項)
> - 修改表名
> `rename table 舊表名 to 新表名;`
> - 表名習慣用 庫名的縮寫 + `_` + 表名
```sql
mysql> rename table coach to test_coach;
Query OK, 0 rows affected (0.05 sec)
```
> - 修改表選項: 字符集, 校對集, 存儲引擎
> `alter table tableName 'pattern' [=] value`
```sql
mysql> show create table test_coach;
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
...
mysql> alter table test_coach charset = BIG5;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test_coach;
...
) ENGINE=InnoDB DEFAULT CHARSET=big5 |
...
```
> #### 修改字段 (新增, 重命名, 修改, 刪除)
> - 新增字段
> `alter table tableName add [column] 字段名 數據類型 [列屬性] [位置]`
> - `[column]` 可有可無
> - `[位置]` : 字段名可以存放到任意位置
> - `first` : 首位
> - `after 字段名` : 在指定字段之後
> - 默認為最後
```sql
mysql> desc test_coach;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| ... |
+-------+-------------+------+-----+---------+-------+
mysql> -- 新增字段於首欄
mysql> alter table test_coach add column id varchar(10) first;
mysql> desc test_coach;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| ... |
+-------+-------------+------+-----+---------+-------+
```
> - 修改字段
> `alter table tableName modify 字段名 數據類型 [屬性] [位置]`
```sql
mysql> desc test_coacH;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> alter table test_coach modify name int after age;
mysql> desc test_coach;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
| age | varchar(10) | YES | | NULL | |
| name | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
```
> - 重命名字段
> `alter table tableName change 舊字段名 新字段名 數據類型 [屬性] [位置]`
```sql
mysql> desc test_coach;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
| age | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> alter table test_coach change age sex varchar(10);
mysql> desc test_coach;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
```
> - 刪除字段
> `alter table tableName drop 字段名`
```sql
mysql> desc test_coach;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| name | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> alter table test_coach drop sex;
mysql> desc test_coach;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
| name | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
```
### 刪除表
> `drop table tableName1, tableName2, ...`
> - 可以一次刪除多張表, 數據庫好像不行, 因為刪庫太危險了
```sql
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| player |
| test_coach |
+----------------+
2 rows in set (0.00 sec)
mysql> drop table player, test_coach;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
```
## 數據
### 新增數據
> #### 給全表字段插入數據
> `insert into tableName values (值列表) [, (值列表), ...];`
> - 數據必須與表中字段順序一致
> - 非數值類型, 須用引號包起來
> - 可一次插入多筆數據
```sql
mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into class values
-> (1, 'GodJJ', 20),
-> (2, 'Toyz', 20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
```
> #### 給指定字段插入數據
> `insert into tableName (選定字段列表) values (值列表) [, (值列表), ...];`
> - 值列表必須與選定的字段列表順序一致
```sql
mysql> insert into class (age, name ,id) values (30, 'BeBe', 3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into class (age, name) values (30, 'Mistake');
Query OK, 1 row affected (0.00 sec)
```
### 查看數據
`select * / 指定字段 from tableName [where 條件];`
> #### 查看所有數據
```sql
mysql> select * from class;
+------+---------+------+
| id | name | age |
+------+---------+------+
| 1 | GodJJ | 20 |
| 2 | Toyz | 20 |
| 3 | BeBe | 30 |
| NULL | Mistake | 30 |
+------+---------+------+
4 rows in set (0.01 sec)
mysql> select name from class;
+---------+
| name |
+---------+
| GodJJ |
| Toyz |
| BeBe |
| Mistake |
+---------+
4 rows in set (0.00 sec)
```
> #### 查看指定字段指定數據
```sql
mysql> select name, age from class where id = 1;
+-------+------+
| name | age |
+-------+------+
| GodJJ | 20 |
+-------+------+
```
### 修改數據
`update tableName set 字段 = 值 [where 條件]`
> - 實務上都有條件, 例如生日到了多一歲, 不可能在某一天大家集體都多一歲
> - 更新不一定會成功, 例如數據沒有實質更動
```sql
mysql> select * from class;
+------+---------+------+
| id | name | age |
+------+---------+------+
| 1 | GodJJ | 20 |
| 2 | Toyz | 20 |
| 3 | BeBe | 30 |
| NULL | Mistake | 30 |
+------+---------+------+
4 rows in set (0.00 sec)
mysql> update class set age = 25 where id = 1;
Query OK, 1 row affected (0.00 sec) -- 更新了 1 條數據
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from class;
+------+---------+------+
| id | name | age |
+------+---------+------+
| 1 | GodJJ | 25 |
| 2 | Toyz | 20 |
| 3 | BeBe | 30 |
| NULL | Mistake | 30 |
+------+---------+------+
4 rows in set (0.00 sec)
mysql> update class set age = 25 where id = 1;
Query OK, 0 rows affected (0.00 sec) -- 更新了 0 條數據
Rows matched: 1 Changed: 0 Warnings: 0
```
### 刪除數據
#### 物理刪除
`delete from tableName [where 條件]`
```sql
mysql> select * from class;
+------+---------+------+
| id | name | age |
+------+---------+------+
| 1 | GodJJ | 25 |
| 2 | Toyz | 20 |
| 3 | BeBe | 30 |
| NULL | Mistake | 30 |
+------+---------+------+
mysql> delete from class where age = 30;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from class;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | GodJJ | 25 |
| 2 | Toyz | 20 |
+------+-------+------+
```
#### 邏輯刪除
> - 邏輯刪除不是真的把數據刪掉, 而是再創表的時候會多一個字段 `x_delete default 0`
> - 想要刪除數據時, 把該字段的值改成 1, 代表不需要該欄數據了, 未來在操作都加where 來篩掉就不會使用到了
> - 優點就是數據事實上還在, 以後找得到
> - 另外還有一種是把刪除的數據移到另外一個數據庫中
> - 在另外一庫創建一張同架構的表, INSERT 進去後刪除原表數據
## 字符集
### 中文數據問題
`ERROR 1366 (HY000): Incorrect string value.`
> - 有些客戶端向服務器插入中文數據會失敗, 因為有些電腦的字符集不是全球通用的,
> 例如中國的版本使用的是 gbk, 而服務器是 utf8
> - 以 哈哈 為例:
> - 哈哈的 GBK 編碼為 B9FE B9FE => 2Byte
> - 哈哈的 utf8 編碼為 %u54C8 %u54C8 => 3Byte
> - 服務器以 utf8 讀取 B9FE B9FE
> - 第一個哈讀取前三字節 B9FEB9 => 可能有可能沒
> - 第二個哈讀取 FE 少兩字節 => 失敗
> - 各種字符集可以想像成不同語言,
> 我跟你講英文, 你用中文模式來聽, 當然聽不懂
> - 字符集就是二進制與字符之間的對應關係
### 查看服務器支援的字符
`show character set;`
> - 基本上服務器所有字符集都支持
```sql
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| ... |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| ... |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| ... |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ... |
+----------+---------------------------------+---------------------+--------+
```
### 查看服務器默認字符集
`show variables like 'character_set%';`
> - `character_set_client` : 服務器處理客戶端數據的字符集
> - `character_set_connection` : 連接層字符集
> - 字符集轉換的中間層, 與 `client` `results` 統一, 效率會比較高
> - `character_set_database` : 當前所在庫的字符集
> - `character_set_results` : 服務器返回給客戶端的字符集
```sql
mysql> show variables like 'character_set%';
+--------------------------+--------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/Cellar/mysql/8.0.18_1/share/mysql/charsets/ |
+--------------------------+--------------------------------------------------------+
```
### 修改服務器默認字符集
`set character_set_client = 字符集;`
`set character_set_results = 字符集;`
> - 以某些中國的客戶端(GBK)為例,
> 他就只會講簡體中文, 而服務器啥都會講, 所以當然是改服務器
> 接收跟傳輸都必須改, 因為他只懂 GBK
> - 不過我沒測試, 因為我的電腦沒有問題~
> - `set key = value` 修改為會話級別(當次連接有效, 關閉後改回)
### 快捷設置字符集
`set names 字符集`
> - 快捷設置會一次改
> `character_set_client` `character_set_connection` `character_set_results`
> - 最好每次連線第一件事情就是先設置字符集
```sql
mysql> set names utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show variables like 'character_set%';
+--------------------------+--------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/Cellar/mysql/8.0.18_1/share/mysql/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)
```
### QA
> - Q. 為什麼數據表數據庫的字符集隨便改都沒問題?
> - 服務器支援幾十種, 他全部都看得懂
> 想像一下你去存錢一定是用台幣存? 你要存美元存黃金隨便, 銀行想收的他都收
> - 但是你要跟銀行說你存的是什麼幣 `character_set_client`
> - 你領錢時也要跟銀行說你要拿什麼幣 `character_set_results`
### WEB 亂碼問題
> - 動態網站組成: 瀏覽器 + 服務器 + 數據庫服務器
> - 當大家都有自己的字符集時, 就很容易產生亂碼,
> - 麻煩的地方在於瀏覽器不可控, 那是用戶的東西, 所以只能依靠服務器來居中協調
> - 服務器對瀏覽器
> - 服務器在傳送資料時, 必須在 header 上寫編碼方式
> - `header(content-type:text/html;charset=utf-8)`
> - 但如果直接拿html文件, 這 header 就沒啥用, 所以必須在 html 上寫編碼方式
> - `<meta http-equir="content-type" content="text/html;charset=utf-8">`
> - 瀏覽器提交數據給服務器, 大致上沒有問題, 因為表單是由服務器提供的,
> 提供時限定字符集為 utf-8 即可
> - 服務器對數據庫
> - 數據庫必須設定接收瀏覽器的編碼方式
> `set character_set_client = utf8`
> - 數據庫必須設定返回瀏覽器的編碼方式
> `set character_set_results = utf8`
> - 一次改三個
> `set names utf8`
> - 服務器對本地文件
> - 服務器沒辦法改操作系統
> - 服務器只能利用 `iconv()` 來轉換接收到的數據
> `iconv(系統字符, 服務器字符, 數據)`
> - 但服務器必須知道本地的 ANSI 版本, 這個只能利用 MB擴展
## 校對集
> - 數據比較的方式
> - 三種方式:
> - `_bin` :
> - binary,
> - 二進制比較(取出二進制位, 由左至右相比),
> - 區分大小寫 (a = 97, A = 65)
> - `_cs` :
> - case sensitive
> - 區分大小寫
> - 因為_bin也區分大小寫, 所以`_cs`很少見
> - `_ci` :
> - case insensitive
> - 不區分大小寫
> - cf. utf8 沒有中文校對, 但是 utf8mb4 有 (我猜是用中文拼音去比較, 不確定)
### 查看數據庫支援的校對集
`show collation`
```sql
mysql> show collation;
+----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+----------+-----+---------+----------+---------+---------------+
| ... |
| utf8_bin | utf8 | 83 | | Yes | 1 | PAD SPACE |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 | PAD SPACE |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 | PAD SPACE |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 | PAD SPACE |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 | PAD SPACE |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 | PAD SPACE |
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | PAD SPACE |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 | PAD SPACE |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 | PAD SPACE |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 | PAD SPACE |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 | PAD SPACE |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 | PAD SPACE |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 | PAD SPACE |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 | PAD SPACE |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 | PAD SPACE |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 | PAD SPACE |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 | PAD SPACE |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 | PAD SPACE |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 | PAD SPACE |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 | PAD SPACE |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 | PAD SPACE |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 | PAD SPACE |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 | PAD SPACE |
| utf8_tolower_ci | utf8 | 76 | | Yes | 1 | PAD SPACE |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 | PAD SPACE |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 | PAD SPACE |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 | PAD SPACE |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 | PAD SPACE |
+----------------------------+----------+-----+---------+----------+---------+---------------+
```
### 應用
> - 當數據產生比較時, 校對集才會生效
> #### 準備數據
```sql
mysql> -- 新增兩個表, 一個_bin, 一個 _ci
mysql> create table text_bin (name char(10)) charset utf8 collate utf8_bin;
mysql> create table text_g_ci (name char(10)) charset utf8 collate utf8_general_ci; -- 事實上不用寫也沒差, 默認就是這個
mysql> -- 添加數據
mysql> insert into text_bin values ('a'), ('A'), ('b'), ('B');
mysql> insert into text_g_ci values ('a'), ('A'), ('b'), ('B');
```
> #### 比較
> `order by 字段名 [asc|desc]`
> - `asc` : 升序, 默認
> - `desc` : 降序
```sql
mysql> select * from text_g_ci order by name;
+------+
| name |
+------+
| a | -- ci 不區分大小寫
| A |
| b |
| B |
+------+
4 rows in set (0.00 sec)
mysql> select * from text_bin order by name;
+------+
| name |
+------+
| A | -- 65
| B | -- 66
| a | -- 97
| b | -- 98
+------+
4 rows in set (0.00 sec)
```
> #### 設置時機
> - 必須在有數據前設置, 有數據後修改無效
```sql
mysql> select * from text_g_ci order by name;
+------+
| name |
+------+
| a |
| A |
| b |
| B |
+------+
mysql> alter table text_g_ci collate = utf8_bin;
mysql> select * from text_g_ci order by name;
+------+
| name |
+------+
| a |
| A |
| b |
| B |
+------+
4 rows in set (0.00 sec)
```
## 數據類型
> - 對數據分類, 目的是為了更好的管理數據, 更有效的利用空間
> - SQL 將數據分為三大類
> - 數值類型
> - 整數:
> - tinyint
> - smallint
> - mediumint
> - int
> - bigint
> - 小數型
> - 浮點
> - float
> - Double
> - 定點
> - decimal
> - 字符串類型
> - set
> - enum
> - blob
> - text
> - varchar
> - char
> - 時間日期類型
> - year
> - timestamp
> - time
> - date
> - datetime
### 數值類型
### 整數型
> - SQL 為了省空間, 又分為五類
|類型|字節|singed|unsigned|
|:--:|:--:|:--:|:--:|
|TINYINT|1|-128~127|0~255|
|SMALLINT|2|-32768~32767|0~65535|
|MEDIUNINT|3|-8388608~8388607|0~16777215|
|INT|4|-2147483648~2147483647|0~4294967295|
|BIGINT|8|-9223372036854775808~9223372036854775807|0~18446744073709551615|
```sql
mysql> create table test_int (
-> int_1 tinyint,
-> int_2 smallint,
-> int_3 mediumint,
-> int_4 int,
-> int_5 bigint
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> desc test_int;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| int_1 | tinyint(4) | YES | | NULL | |
| int_2 | smallint(6) | YES | | NULL | |
| int_3 | mediumint(9) | YES | | NULL | |
| int_4 | int(11) | YES | | NULL | |
| int_5 | bigint(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
mysql> insert into test_int values (1,1,1,1,1);
Query OK, 1 row affected (0.01 sec)
mysql> -- 表創建時就已經限制了每筆數據的類型與範圍
mysql> -- 類型不符
mysql> insert into test_int values ('a', 'b', 'c', 'd', 'e');
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'int_1' at row 1
mysql> -- 超出範圍
mysql> insert into test_int values (100000,1000000,10000000,1000000,10);
ERROR 1264 (22003): Out of range value for column 'int_1' at row 1
```
> - singed & unsigned
> - SQL 中數據類型都是默認有符號的(`singed`, 把數據切半分正負號)
> - 限制數據為無符號類型 `field type unsigned` : 從 0 開始
> - 依據需求選擇, 例如年齡不會有負數且不會超過255, 所以使用 tinyint 即可
```sql
mysql> create table test_int2 (
-> int_1 tinyint, -- 有符號 (-128~127)
-> int_2 tinyint unsigned -- 無符號 (0~255)
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> desc test_int2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| int_1 | tinyint(4) | YES | | NULL | |
| int_2 | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test_int2 values (255, 255); -- 第一個超過了
ERROR 1264 (22003): Out of range value for column 'int_1' at row 1
mysql> insert into test_int2 values (127, 255); --符合條件
Query OK, 1 row affected (0.00 sec)
```
> - 顯示寬度
> - 查看表結構時, 數據類型後面都默認會有 `(num)`
> - 該數字表示顯示寬度, 純粹告訴用戶可以顯示的位數形式而已,
> 即使設定了裡面的數字也不會改變數字本身大小, 數據大小是由數據類型控制
> - 顯示寬度真正的用途在於保證數據寬度, 所以會搭配 `zerofill` 來使用
> - `zerofill` 會在不足的前面補零
> - `zerofill` 會將數據存放方式自動轉為 `unsigned`,
> 因為假設預設三位, 存放 `-1`, 此時不足 1 為而補零 `0-1`, 變成計算式
```sql
mysql> desc test_int2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| int_1 | tinyint(4) | YES | | NULL | |
| int_2 | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
mysql> -- tinyint(4) tinyint(3) unsigned 後面都有(數字)
mysql> -- 這數字就是顯示寬度, 4 代表四位, 3 代表三位
mysql> -- 因為 -128~+127 => 最多 4 位; 0~255 => 最多三位
mysql> -- ------------ 顯示寬度不會影響數據大小 -----------------------------
mysql> alter table test_int2 add int_3 tinyint(1) unsigned; -- 設寬度 1
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> insert into test_int2 values (100, 100, 100); -- 給三位數
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_int2;
+-------+-------+-------+
| int_1 | int_2 | int_3 |
+-------+-------+-------+
| 127 | 255 | NULL |
| 100 | 100 | 100 | -- 還是三位
+-------+-------+-------+
2 rows in set (0.00 sec)
mysql> -- ------------ zerofill -----------------------------
mysql> alter table test_int2 add int_4 tinyint(2) zerofill;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> desc test_int2;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| int_1 | tinyint(4) | YES | | NULL | |
| int_2 | tinyint(3) unsigned | YES | | NULL | |
| int_3 | tinyint(1) unsigned | YES | | NULL | |
| int_4 | tinyint(2) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
mysql> -- zerofill 會自動轉 unsigned
mysql> insert into test_int2 values (1, 1, 1, 1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_int2;
+-------+-------+-------+-------+
| int_1 | int_2 | int_3 | int_4 |
+-------+-------+-------+-------+
| 127 | 255 | NULL | NULL |
| 100 | 100 | 100 | NULL |
| 1 | 1 | 1 | 01 | -- 自動補 0
+-------+-------+-------+-------+
4 rows in set (0.00 sec)
```
### 小數型(還沒懂)
> - SQL 將小數型細分為浮點型跟定點型, 已解決精度丟失問題
> #### 浮點型:
> - 精度型數據, 超出精度範圍會四捨五入
> - `FLOAT` `DOUBLE`
> - 有精度丟失問題(超出會四捨五入)
> - 精度約 7 位
|TYPE|Byte|RANGE|
|:--:|:--:|:--:|
|FLOAT|4|-3.402823466E38 ~ +3.402823466E38|
|DOUBLE|8|-17976931348623157E308 ~ +17976931348623157E308|
> #### 創建浮點數表:
> - 直接 float : 沒有小數
> - float(M,D) :
> - `M` : 總長度
> - `D` : 小數長度
> - `M-D` : 正數長度
> #### 插入數據
> - 超出精度會四捨五入進位
> - 精度 "大約" 7 位, 亦即超出會四捨五入由左至右第七位以後
> - 四捨五入進位後如果大於設定的總長度是沒關係的, 系統會接受而不會報錯
> - 科學計數法插入也沒問題
```sql
mysql> create table test_fl (
-> fl_1 float,
-> fl_2 float(8,2), -- M 超出精度
-> fl_3 float(6,2) -- M 符合精度
-> ) charset utf8;
Query OK, 0 rows affected, 3 warnings (0.03 sec)
mysql> -- 完全符合精度
mysql> insert into test_fl values (1000.99, 100000.99, 1000.99);
Query OK, 1 row affected (0.01 sec)
mysql> -- fl_1 跟 fl2 超出 "大約 7 位" 的精度
mysql> insert into test_fl values (99999999, 999999.99, 9999.99);
Query OK, 1 row affected (0.01 sec)
mysql> -- 科學計數也可使用
mysql> insert into test_fl values (3e38, 3e5, 3e3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_fl;
+-----------+------------+---------+
| fl_1 | fl_2 | fl_3 |
+-----------+------------+---------+
| 1000.99 | 100000.99 | 1000.99 | -- 沒有超出精度
| 100000000 | 1000000.00 | 9999.99 | -- 超出精度, 四捨五入
| 3e38 | 300000.00 | 3000.00 | -- 科學計數也被寫入了
+-----------+------------+---------+
mysql> -- fl_2 設定 8 位, 超過精度而進位到九位, 系統沒有報錯~
```
> - 超出設定長度
> - 小數超出時, 會自動四捨五入到原本設定的長度
> - 整數超出時, 會直接報錯
```sql
mysql> desc test_fl;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| fl_1 | float | YES | | NULL | |
| fl_2 | float(8,2) | YES | | NULL | |
| fl_3 | float(6,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> -- 小數超出無所謂, 因為超出部分會四捨五入
mysql> insert into test_fl values (1, 100000.9999999, 1000.9999);
Query OK, 1 row affected (0.00 sec)
mysql> -- 整數部分超出就會報錯
mysql> insert into test_fl values (1, 10000000.12, 10000000.123);
ERROR 1264 (22003): Out of range value for column 'fl_2' at row 1
mysql> select * from test_fl;
+-----------+------------+---------+
| fl_1 | fl_2 | fl_3 |
+-----------+------------+---------+
| ... | ... | ... |
| 1 | 100001.00 | 1001.00 | -- 小數超出部分都被四捨五入而直接剩兩位
+-----------+------------+---------+
```
> - 精度測試
```sql
mysql> create table test_fl (
-> fl_1 float,
-> fl_2 float(100,2)
-> ) charset utf8;
mysql> insert into test_fl values (9999989,0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_fl values (9999989999,0);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_fl values (0,99999.99);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_fl values (0,999998.99);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_fl;
+------------+------------+
| fl_1 | fl_2 |
+------------+------------+
| 9999990 | 0.00 | -- 整數七位 => 超出
| 9999990000 | 0.00 | -- 第七位之後四捨五入到第六位
| 0 | 99999.99 | -- 含小數七位 => 沒超出
| 0 | 999999.00 | -- 含小數八位 => 超出 => 第七位之後四捨五入到第六位
+------------+------------+
```
> #### 定點型:
> - `DECIMAL`
> - 絕對保證整數部分不會四捨五入(丟失精度),
> 因為 Byte 是會變動的, 一但超出就會變大Byte, 而不是四捨五入原數據
> - 小數部分有可能丟失精度, 不過情況很少(小數最大裝 30 位, 能超過 30 位的很情況很少)
> - DECIMAL 的 M 最大 65, 默認 10
> - DECINAL 的 D 最大 30, 默認 2 => 整數部分最大 35 位
> - 浮點數進位而超出 M 值沒有問題, 但是定點數因為進位而超出 M 不行
|TYPE|Byte|RANGE|
|:--:|:--:|:--:|
|DECIMAL|變動的, 約 9 個數字用 4Byte 裝, 整數小數分開計算| -35個9.30個9 ~ +35個9.30個9|
```sql
mysql> create table test_decimal (
-> d decimal(10,2),
-> f float(10,2)
-> ) charset utf8;
mysql> insert into test_decimal values (12345678.12, 12345678.12);
Query OK, 1 row affected (0.00 sec)
mysql> -- 超出的小數會四捨五入回兩位, 所以可以隨意超出
mysql> insert into test_decimal values (12345678.1234567, 12345678.12234567);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> -- 符合規則數據, 但 float 超出精度, 會四捨五入進位而超出設定的位數, 不過沒事
mysql> insert into test_decimal values (99999999.99, 99999999.99);
Query OK, 1 row affected (0.01 sec)
mysql> -- 定點型整數部分一定不能超出限制, 即時是進位也不行
mysql> insert into test_decimal values (99999999.999, 99999999.99);
ERROR 1264 (22003): Out of range value for column 'd' at row 1
mysql> -- 99999999.999 小數超出一位而四捨五入 100000000.00 => 11 位
mysql> -- 11 位, 超出 decimal(10,2) 的總共十位而報錯
mysql> select * from test_decimal;
+-------------+--------------+
| d | f |
+-------------+--------------+
| 12345678.12 | 12345678.00 | -- float 超出精度會進位
| 12345678.12 | 12345678.00 |
| 99999999.99 | 100000000.00 | -- float 進位超出 M 不會有問題
+-------------+--------------+
```
> #### 小結論:
> - 我還真的看不懂...
> - 不過結論就是要準確點就用 `DECIMAL`, 如果需求一個大概就用 `float` `double`
>
### 時間日期類型
|TYPE|格式|值|存儲空間|零值|
|:--:|:--:|:--:|:--:|:--:|
|DATETIME|YYYY-MM-DD HH:MM:SS|1000-01-01 00\:00:00~ 9999-12-31 23\:59:59|8|0000-00-00 00\:00:00|
|TIMESTAMP|YYYY-MM-DD HH:MM:SS|1970-01-01 00\:00:00~ 2038-01-19 03\:14:07|4|0000-00-00 00\:00:00|
|DATE|YYYY-MM-DD|1000-01-01 ~ 9999-12-31|3|0000-00-00|
|TIME|HH:MM:SS|-838\:59:59 ~ +838\:59:59|3|00\:00:00|
|YEAR|YYYY|1901~2155|1|0000|
> #### time
> - 可以是負數: 代表過去多少時間
> - 小時可以超過24
> - `2 20:xx:xx` : 前面的 2 代表天數, 顯示時會轉換成小時
```sql
mysql> insert into test_time values
('20:20:20'),
('-20:20:20'),
('200:20:20'),
('-2 20:20:20'); -- 2 20 代表 2*24+20 = 68
mysql> select * from test_time;
+-----------+
| d |
+-----------+
| 20:20:20 |
| -20:20:20 |
| 200:20:20 |
| -68:20:20 |
+-----------+
```
> #### year
> - 可以使用 2 位數插入與 4 位數插入
> - 兩位插入的範圍在 1970~2069 年間
> - 四位插入的範圍在 1901~2155 年間
```sql
mysql> create table test_year (d year) charset utf8;
mysql> insert into test_year values (2020), (69), (70);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_year;
+------+
| d |
+------+
| 2020 |
| 2069 | -- 兩位數插入數據的範圍在 1970~2069 年間
| 1970 |
+------+
3 rows in set (0.00 sec)
mysql> -- 四位數插入數據的範圍在 1900~2156 年間
mysql> insert into test_year values (1901), (2155);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into test_year values (1900);
ERROR 1264 (22003): Out of range value for column 'd' at row 1
mysql> insert into test_year values (2156);
ERROR 1264 (22003): Out of range value for column 'd' at row 1
```
> #### timestamp
> - 可以設定字段為是否初始化時間以及是否自動更新時間
> - 初始化時間
> `default current_timestamp`
> - 自動更新時間
> `on update current_timestamp`
```sql
mysql> create table apple (
-> y year,
-> t1 timestamp, -- 啥都沒設
-> t2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 設置初始化時間
-> t3 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 設置自動更新時間
-> t4 timestamp default current_timestamp on update current_timestamp -- 我全都要
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc apple;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| y | year(4) | YES | | NULL | |
| t1 | timestamp | YES | | NULL | |
| t2 | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| t3 | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
| t4 | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
5 rows in set (0.01 sec)
mysql> -- 新增數據
mysql> insert into apple values ();
Query OK, 1 row affected (0.01 sec)
mysql> select * from apple;
+------+------+---------------------+------+---------------------+
| y | t1 | t2 | t3 | t4 |
+------+------+---------------------+------+---------------------+
| NULL | NULL | 2020-01-12 15:59:49 | NULL | 2020-01-12 15:59:49 |
+------+------+---------------------+------+---------------------+
1 row in set (0.00 sec)
mysql> -- 新增數據時, DEFAULT CURRENT_TIMESTAMP 的欄位自動寫入當下時間
mysql> -- 修改數據
mysql> update apple set y = 2020 where t2 = '2020-01-12 15:59:49';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from apple;
+------+------+---------------------+---------------------+---------------------+
| y | t1 | t2 | t3 | t4 |
+------+------+---------------------+---------------------+---------------------+
| 2020 | NULL | 2020-01-12 15:59:49 | 2020-01-12 16:00:49 | 2020-01-12 16:00:49 |
+------+------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> -- 修改數據時, on update CURRENT_TIMESTAMP 的欄位會自動寫入當下時間
```
> #### unix_timestamp()
> - 可以拿到當前時間
> - 實務上服務器比較常用這個, 因為這個比較好自己轉自己要的格式 (DATA 函數)
```sql
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1578816689 | -- 整數
+------------------+
1 row in set (0.00 sec)
```
### 字符串類型
> - SQL 將其分成六類: `char` `varchar` `text` `blob` `enum` `set`
|TYPE|MAX_LENGTH||
|:--:|:--:|:--:|
|CHAR|255|CHAR(M), M=字符數|
|VARCHAR|65535, 但需要1~2保存訊息, 且由於紀錄限制, 最大為 65532|編碼不同而字符數不同: GBK<=32767; UTF8<=21854|
|TintText, Text, MediumText, LongText|2^8+1, 2^16+2, 2^24+3, 2^32+4|定義時, 通常不用指定長度|
|enum|enum用1~2個字節存 => 枚舉選項量(65535)|內部存儲是整數表示|
|Set|1,2,3,4,8。元素數量: 64||
|Binart, Varbinary, Blob|與Char, Varchar, Text 做類比|二進制數據存儲(字節)|
> #### 定長字符串
> - 磁盤(二維表)在定義結構時就已經確定最終數據長度
> - `char(L)`
> - `L` : length
> - 單位為字符
> - vs 字節(Byte):
> - 字符是電腦使用的文字與符號, 字節是電腦的存儲單位
> - 1 字符 >= 1 字節, 視字符集而定
> - char(4) 在 utf8 編碼下, 磁盤需要分配 4 * 3 = 12 字節來存儲
> - utf8 下, 中文占 3~4 字節, 英文占 1 字節
> - https://blog.csdn.net/bluetjs/article/details/52936943
> - https://blog.csdn.net/bigapple88/article/details/5601295
> - https://zhidao.baidu.com/question/454436588167256525.html
> - 最長可達 255 字符, 0 會做 Boolean 運算, 所以 2^8-1
> #### 變長字符串
> - 分配空間時, 按照最大空間來分配, 實際用了多少則是按照具體數據確定
> - `varchar(L)`
> - `L` : length
> - 單位為字符
> - 理論最長: 65536 個字符, 但會花 1 到 2 字節來確定存儲長度
> 亦即變長有可能會比實際分配的空間還要大一點點
> - varchar(10), 且存儲了10個中文, utf8下佔用多少字節? 10 * 3 + 1 = 31
> - varchar(10), 且存儲了 3個中文, utf8下佔用多少字節? 3 * 3 + 1 = 9
> - 多花 1 字節還是 2 字節是視存儲字符數而定,
> 如果 L < 255, 那 1 Byte 就夠了 2^8 = 256
> 如果 L > 255, 那就需 2 Byte => 2^16 = 65536
> 兩字節剛好足夠應付 varchar(L) 最長存儲字符長度
> - 雖然理論上可以存到 65535 個字符, 但是一般超過 255 的資料都會用 TEXT 來存, 效率較高
> - MySQL 規定, 任何一條紀錄長度不能超過 65535 個字節
> - 所以 varchar 永遠無法存到理論長度, 因為一個字符至少為 1~2 字節
> - 即使每個字符為 1 字節,
> `varchar` 不只需要存數據, 還需要額外拿出 1~2 字節來保存長度,
> - varchar 實際存儲長度(在 utf8 的編碼下): 21844 字符
```sql
mysql> create table my_utf8 (
-> utf varchar(65535)
-> ) charset utf8;
ERROR 1074 (42000): Column length too big for column 'utf' (max = 21845); use BLOB or TEXT instead
mysql> -- 21845 * 3 = 65535
mysql> create table my_utf8 (
utf varchar(21845)
) charset utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> -- 又報錯, 因為還需要 2 字節來存儲長度
mysql> -- 21845 * 3 + 2 = 65537 => 超過
mysql> create table my_utf8 ( utf varchar(21844) ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec) -- 可以~
```
> - Null 存儲
> - 如果任何字段允許為空, 那系統會自動保留一個字節來存儲 NULL
> - 反之, 如果想釋放 NULL 佔用的字節, 必須保證所有字段都不允許為空
```sql
mysql> -- 21844 * 3 + 2 = 65534
mysql> -- 用 tinyint 把那個字節塞滿看看
mysql> create table my_utf8_1 (
tin tinyint, -- 1 B
utf varchar(21844) -- 65534 B
) charset utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> -- 因為字段有允許為空, 系統保留了一字節來保存NULL
mysql> -- 釋放 NULL 字節
mysql> create table my_utf8_1 (
tin tinyint not null,
utf varchar(21844) not null
) charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
```
> #### 比較
> - 在UTF8的環境下
|實際存儲數據|char(4)|varchar(4)|char佔用字節|varchar佔用字節||
|:--:|:--:|:--:|:--:|:--:|:--:|
|ABCD|ABCD|ABCD|4*3=12|4*3+1=13|雖然英文只佔一字節, 但分配空間是依據char(4)而分配, 跟電腦存進什麼沒有關係，必須先買水壺，水裝多少是另一件事|
|A|A|A|12(定長)|1*3+1=4|
|ABCDE|X|X|數據超過長度|數據超過長度|在嚴格模式下存不進去(註)|
> - 如何選擇定長與變長?
> - 定長磁盤空間較浪費, 但是效率較高(不用還去數裡面有多長才開始作業)
> 當資料長度較固定時使用定長, 例如身分證號碼, 電話號碼... 等
> - 變長磁盤空間較節省,但是效率較低
> 當資料長度較不固定時實用變長, 例如姓名,地址...等
> - 註:
> - 查詢 SQL MODES
> `SELECT @@GLOBAL.sql_mode;` `SELECT @@SESSION.sql_mode;`
> - `@@GLOBAL.sql_mode` : 服務器設定值
> - `@@SESSION.sql_mode` : 當前連線設定值
> - 修改 SQL MODES 三種方式
> - 修改設定檔 `my.cnf` 或 `my.ini` 裡的 `sql-mode="modes"`
> - 啟動 MySQL 時新增引數 `--sql-mode="modes"`
> - `SET GLOBAL sql_mode='modes';` `SET SESSION sql_mode='modes';`
> - modes 是由逗號區隔不同模式名字
> - MODES 模式我懶得打了
> - https://www.itread01.com/p/1130597.html
> - https://xyz.cinc.biz/2013/08/mysql-server-sql-modes.html
> - http://www.mzh.ren/mysql-turn-on-strict-mode.html
### 文本字符串
> - 當數據量非常大(超過 255 個字符)時, 就會使用文本字符串
> - 文本字符串根據數據格式不同分成兩類
> - Text : 存儲文字
> - Blob : 存儲二進制數據
> - 雖然 Blob 是存儲二進制數據, 但通常很少用
> - 一般常把二進制的資料另外存放, 並以 TEXT 存儲路徑
> 因為在 SQL 存儲二進制沒有太大意義
> - MySQL 中, text 不佔用紀錄長度, 他是額外存儲的
> 但是 text 也是屬於紀錄的一部分, 事實上他會佔據紀錄中的 10 Byte
> 用來保存文本的地址與長度而非內容, 不論哪種編碼都是 10 Byte
```sql
mysql> create table my_text (
-> var varchar(21841) not null,
-> content text
-> ) charset utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> 記得釋放 NULL 的那個字節
mysql> create table my_text (
-> var varchar(21841) not null, -- 21841 * 3 + 2 = 65523 + 2 = 65525
-> content text not null -- 10
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
```
### 枚舉字符串
> - `enum`
> - 事先將可能出現的結果設計好, 存儲數據必須使用規定好的數據
> - 枚舉的別名叫做『單選框』
> - 作用1. 保證格式
```sql
mysql> create table my_enum (
-> sex enum('male', 'female', 'other')
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> insert into my_enum values ('male'), ('female'); -- 設計好的格式
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into my_enum values ('男'); -- 不是設計好的格式
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
```
> - 作用2. 節省存儲空間
> - 枚舉存儲的實際上是 `數值` 而不是字符本身
> - 枚舉存儲數據會用 1~2 個字節存(選取超過255以後的選項就用兩字節存),
> 亦即枚舉選項最多可以有 2^16 = 65536-1 個選項
> - 用一個字節就可以存一大串字符, 當然節省空間
> - 假設要存 `舒潔衛生紙`, 在 utf8編碼下, 需要 5*3=15個字節存放
> - 枚舉最多用兩個字節就搞定了(視編號而定),
> - 一條五個中文字的數據就節省了 13 Byte
> - 證明枚舉存的是數值:
> - MySQL 中也會自動轉換格式
> - 在運算時, 會找字符串的開頭字符數字當值, 如果字符串的開頭不是數字, 那就等於0
```sql
mysql> select 1 + '1a'; -- 1 + 1
+----------+
| 1 + '1a' |
+----------+
| 2 |
+----------+
1 row in set, 1 warning (0.00 sec)
mysql> select 1 + 'a1'; -- 1 + 0
+----------+
| 1 + 'a1' |
+----------+
| 1 |
+----------+
1 row in set, 1 warning (0.01 sec)
mysql> -- 證明枚舉存儲的是數值:
mysql> -- 將值取出後 + 0, 如果為 0 , 表示枚舉存儲的是字符串
mysql> select sex + 0, sex from my_enum;
+---------+--------+
| sex + 0 | sex |
+---------+--------+
| 1 | male | -- 結果不為 0
| 2 | female |
+---------+--------+
mysql> desc my_enum;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| sex | enum('male','female','other') | YES | | NULL | |
+-------+-------------------------------+------+-----+---------+-------+
mysql> -- 根據結果可得, 枚舉第一個選項為數值 1, 依序編號
```
> - 直接插入數值
> - 因為枚舉存儲的是數值, 所以也可以直接存放數值
```sql
mysql> insert into my_enum values (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from my_enum;
+--------+
| sex |
+--------+
| male |
| female |
| male | -- 存儲正常
| female |
+--------+
4 rows in set (0.00 sec)
```
> - enum 原理：
> - 在進行數據規範時(定義表), 系統會自動建立一個數字與枚舉元素的對應關係(放在日誌中)
> - 插入數據時, 系統會自動將字符轉為對應的數值存儲
> - 提取時, 系統會將數值轉為對應的字符顯示
> - 優缺點
> - 優點就是省空間
> - 缺點就是效率低(還要經過轉換, 不能直接用)
> - 使用時機: 『急迫』節省空間時, 因為即使一條數據省了10B, 一百萬條也才 10M左右
### 集合字符串
> - 存儲的也是數值
> - 集合是多選
> - 既然是多選, 又存數值, 那假設我存 (3) , 要如何知道我是存了哪些選項?
> - 像 enum 1 就是選項1, 2 就是選項 2,..., 因為他只有選一個
> - 集合為了分辨選了哪些, 將每個選項用二進制來表示, 有選就是1, 沒選就是 0
> - 3 = 00000011 => 反過來 11000000 => 選項1跟2
> - 集合的重點在於可以規範數據與節省空間
> #### 定義
> `set(元素列表)`
> #### 插入數據
> `set('元素1,元素2,..')`
> - 使用逗號分隔
> - 不要有空格
> - 也可以直接插入數值, 因為他存儲的本來也是數值
> - Set 用 1,2,3,4,8 個字節存, 故最多有 64 個元素選項
> - 1~8個元素=>1; 9~16個元素=>2B; ...; 33~64個元素=>8B
> - 剛好跟整型對應位置相同~, 集合就是用整型在存的
```sql
mysql> create table my_set (
pg set('C', 'PYTHON', 'C+', 'JAVA', 'JS', 'PHP', 'GO', 'C#')
) charset utf8;
mysql> desc my_set;
+-------+----------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------------------------------+------+-----+---------+-------+
| pg | set('C','PYTHON','C+','JAVA','JS','PHP','GO','C#') | YES | | NULL | |
+-------+----------------------------------------------------+------+-----+---------+-------+
mysql> insert into my_set values ('Python,js,go');
Query OK, 1 row affected (0.01 sec)
mysql> insert into my_set values (3);
Query OK, 1 row affected (0.00 sec)
mysql> select pg + 0, pg from my_set;
+--------+--------------+
| pg + 0 | pg |
+--------+--------------+
| 82 | PYTHON,JS,GO |
| 3 | C,PYTHON |
+--------+--------------+
2 rows in set (0.00 sec)
```
> - 分析數值:
> - 82 轉乘二進制為 = 64 + 16 + 2 = 01010010
> - 二進制技巧: 把值拆成 2的次方相加,
> `128` `64` `32` `16` `8` `4` `2` `1`, 由這些組成的位置寫1 ,不能就寫 0
> - 表結構: `C,PYTHON,C+,JAVA,JS,PHP,GO,C#`
> - 數據 : `X, V, X, X, V, X, V, X`, 有選的寫 1 沒選的寫 0: 01001010
> - 反過來就是二進制的值: 01001010
> - 集合中數據, 每個元素都對應一個二進制位, 選中為1, 沒選中為0, 最後再反過來
> - 所以存值為 3 時, 3的二進制為 00000011 反過來的對應位置就是 `C,PYTHON`
> 所以如果存 255, 就等於八個選項全選
> - 集合沒有順序關係, 系統會自行匹配
```sql
mysql> -- 顛倒剛剛插入的順序
mysql> insert into my_set values ('GO,JS,PYTHON');
Query OK, 1 row affected (0.00 sec)
mysql> insert into my_set values (255);
Query OK, 1 row affected (0.00 sec)
mysql> select pg + 0, pg from my_set;
+--------+-------------------------------+
| pg + 0 | pg |
+--------+-------------------------------+
| 82 | PYTHON,JS,GO |
| 3 | C,PYTHON |
| 82 | PYTHON,JS,GO | -- 顛倒存的結果還是一樣
| 255 | C,PYTHON,C+,JAVA,JS,PHP,GO,C# | -- 255: 11111111
+--------+-------------------------------+
mysql> -- 想像一下 255 這筆數據需要耗費 29 Byte 存儲,
mysql> -- 用 set 只要 1 Byte 就搞定, 差了 28 Byte / 1 筆
mysql> -- 也比 enum 1~2 Byte 存一個選項來的省一些
```
## 列屬性
> - 約束字段的主要是數據類型, 但數據類型的約束單一, 只要求數據存什麼存多少
> - 列屬性就是用對字段做額外的約束, 保證數據更具體
> - `null / not null` `default` `primary key` `unique key` `auto_increment` `comment`
### 空屬性
> - `NULL(默認)` `NOT NULL`
> - 雖然默認預設為允許字段為空, 但是實際上都應該盡量要求字段不為空
> 因為空字段本身並沒有意義, 並且空字段在 MySQL 無法做運算
```sql
mysql> -- null 無法做運算
mysql> select 1 + null;
+----------+
| 1 + null |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)
mysql> -- 創建一個水果表, 水果名當然不能為空, 價格可能還沒定, 所以先允許為空
mysql> create table fruit (
-> name varchar(20) not null,
-> price decimal(10,2) null -- 不寫默認也為 null
-> ) charset utf8
-> ;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc fruit;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| price | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
### 描述
> - 用來描述字段, 讓工程師看的, 會隨著表『創建語句』保存
```sql
mysql> create table employee (
-> name varchar(10) not null comment '姓名',
-> money decimal(10,2) not null comment '薪水'
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc employee; -- 這裡是看不到的
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name | varchar(10) | NO | | NULL | |
| money | decimal(10,2) | NO | | NULL | |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table employee; -- 保存在表創建語句裡
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
`name` varchar(10) NOT NULL COMMENT '姓名',
`money` decimal(10,2) NOT NULL COMMENT '薪水'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
### 默認值
```sql
mysql> -- 創建一個有默認值的表
mysql> create table baby (
-> name varchar(10) not null comment '姓名',
-> age tinyint unsigned not null default 0,
-> sex enum('male', 'female') not null default 'male'
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc baby;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| sex | enum('male','female') | NO | | male | |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> -- 使用默認值
mysql> -- 1. 只填指定字串, 沒填的就會走默認值
mysql> insert into baby (name) values ('GODJJ');
Query OK, 1 row affected (0.01 sec)
mysql> -- 2. 直接填 default
mysql> insert into baby values ('GODJJ', default, default);
Query OK, 1 row affected (0.00 sec)
mysql> select * from baby;
+-------+-----+------+
| name | age | sex |
+-------+-----+------+
| GODJJ | 0 | male |
| GODJJ | 0 | male |
+-------+-----+------+
2 rows in set (0.00 sec)
```
### 主鍵
> - `primary key`
> - 一張表只能有一個主鍵
> - 主鍵用來約束數據不能重複
> - 主鍵本身不允許為空(設置主鍵後, 他會自己轉)
> - 主鍵就是索引, 但索引不一定是主鍵
> #### 新增主鍵
```sql
mysql> create table student (
-> name varchar(10) not null comment '姓名',
-> id char(10) primary key comment '學號'
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | NO | | NULL | |
| id | char(10) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
> - 缺點:只能用一個字段當主鍵
> #### 複合主鍵
> - 事實上還是一個主鍵, 只是用兩個字段來表示主鍵, 讓不夠具體的主鍵更具體
```sql
mysql> create table class (
-> id char(10) comment '學號',
-> class char(10) comment '課程號碼',
-> score tinyint unsigned default 0 comment '成績',
-> -- 寫到這裡會有個問題: 一個學號可能有多個課程號碼, 單一字段無法具體描述成績指向
-> -- 此時就可以使用複合主鍵, 把字段包起來, 更具體的限制成績是哪個學生哪堂課的
-> -- 限制學號與課程號應該對應, 具有唯一性
-> primary key(id, class)
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> desc class;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | char(10) | NO | PRI | NULL | |
| class | char(10) | NO | PRI | NULL | |
| score | tinyint(3) unsigned | YES | | 0 | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
```
> #### 追加主鍵
> - 當表創立後, 額外追加主鍵
> - 前提: 表中字段對應的數據本身不重複, 如果數據重複就加不了
> - 方法1. 修改表字段
> `alter table tableName modify 字段名 數據類型 [屬性] [位置]`
```sql
mysql> -- 新增一個沒主鍵的表
mysql> create table modi_pri (id char(10), name varchar(10)) charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc modi_pri;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> -- 修改表字段
mysql> alter table modi_pri modify id char(10) primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc modi_pri;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | char(10) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
> - 方法2. 直接追加
> `alter table tableName add primary key (字段列表)`
```sql
mysql> create table add_pri (id char(10), name varchar(10)) charset utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> desc add_pri;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table add_pri add primary key (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc add_pri;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | char(10) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
> #### 主鍵約束效果
> - 主鍵對應的字段不允許重複, 一但重複, 數據操作(增或改)就會失敗
```sql
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | NO | | NULL | |
| id | char(10) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> -- 沒有重複數據
mysql> insert into student values ('GodJJ', 1), ('BEBE', 2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+-------+----+
| name | id |
+-------+----+
| GodJJ | 1 |
| BEBE | 2 |
+-------+----+
2 rows in set (0.00 sec)
mysql> -- 重複數據
mysql> insert into student values ('GEAR', 1); -- 1 已經被 GODJJ 用走了
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
```
```sql
mysql> desc class;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | char(10) | NO | PRI | NULL | |
| class | char(10) | NO | PRI | NULL | |
| score | tinyint(3) unsigned | YES | | 0 | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> -- 沒有重複數據
mysql> -- 複合主鍵要兩個都ㄧ樣才叫重複
mysql> insert into class values (1,'JS', 60), (1,'CSS',70);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from class;
+----+-------+-------+
| id | class | score |
+----+-------+-------+
| 1 | CSS | 70 |
| 1 | JS | 60 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> -- 重複數據
mysql> insert into class values (1,'JS', 79);
ERROR 1062 (23000): Duplicate entry '1-JS' for key 'PRIMARY'
mysql> -- 觀察錯誤訊息: 主鍵在服務器是寫 '1-JS' => 複合主鍵被綁起來了
```
> #### 刪除主鍵
> `alter table tableName drop primary key;`
> - 主鍵只有一個, 所以不用指定什麼鳥, 直接刪就行了
> - 沒有辦法更新主鍵, 只能先刪除後增加
```sql
mysql> desc add_pri;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | char(10) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table add_pri drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc add_pri;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | char(10) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
> #### 邏輯主鍵
> - 實際上, 主鍵很少設置在有意義的數據上(身分證,學號等), 雖然他不會重複
> - 最常設置於邏輯性字段(字段本身沒有含義, 數據是啥都沒差),
> 因為主鍵經常搭配自增長 (auto_increment) 一起使用
> - 假設使用身分證當主鍵, 每次都需要手動輸入身分證號碼, 比較麻煩
> 使用邏輯主鍵, 讓他自己增加數值, 反正他的值沒有其他意義, 隨便他加, 又不重複, 讚
```sql
mysql> create table people (
-> id int primary key auto_increment comment '邏輯主鍵, 自增長',
-> number char(10) not null comment '身分證',
-> name varchar(10) not null comment '姓名'
-> ) charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc people;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| number | char(10) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
```
### 自動增長
`auto_increment`
> - 當對應的字段不給值(NULL)或給默認值, 會自動被系統觸發, 將當前字段中最大值 +1
> - 自增長字段必須是數字(整型)
```sql
mysql> create table my_auto (
-> id varchar(10) auto_increment, -- 自增長類型必須是數字
-> name varchar(10) not null
-> ) charset utf8;
ERROR 1063 (42000): Incorrect column specifier for column 'id'
```
> - 字段要做自增長, 必須前提本身是一個索引(key 欄有值)
> - 主鍵是索引的一種, 但索引不一定是主鍵
> - 如果要刪除帶自增長的索引時, 必須先刪掉自增長, 因為自增長必須跟著索引
```sql
mysql> create table my_auto (
id int auto_increment, -- 自增長本身必須是索引
name varchar(10) not null
) charset utf8;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table my_auto (
id int primary key auto_increment,
name varchar(10) not null
) charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
```
> - 一張表最多只能一個自增長
> #### 自增長觸發
> - 自增長欄位不給值或給 Null 或 default
> - 自增長欄位輸入值時, 自增長會失效
> - 再次觸發自增長時, 會從最大值 +1
```sql
mysql> desc my_auto;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> -- 不給值
mysql> insert into my_auto (name) values ('GODJJ');
Query OK, 1 row affected (0.00 sec)
mysql> -- 給 null
mysql> insert into my_auto values (null, 'TOYZ');
Query OK, 1 row affected (0.00 sec)
mysql> -- 給 default
mysql> insert into my_auto values (default, 'GEAR');
Query OK, 1 row affected (0.01 sec)
mysql> -- 給值
mysql> insert into my_auto values (1000, 'HAHA');
Query OK, 1 row affected (0.01 sec)
mysql> -- 再次觸發
mysql> insert into my_auto values (null, 'GOGO');
Query OK, 1 row affected (0.00 sec)
mysql> select * from my_auto;
+------+-------+
| id | name |
+------+-------+
| 1 | GODJJ |
| 2 | TOYZ |
| 3 | GEAR |
| 1000 | HAHA |
| 1001 | GOGO | -- 再次觸發時, 最大值 +1
+------+-------+
5 rows in set (0.00 sec)
```
> #### 查看下次自增長的值
> - 通過查看創建語句
> `show create table tableName`
```sql
mysql> show create table my_auto;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_auto | CREATE TABLE `my_auto` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8 | -- AUTO_INCREMENT=1002
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
> #### 修改自增長
> - 使用別的字段自增長:
> - 只能先刪除原先的自增長, 在設置新的自增長
> - 因為一張表只能有一個自增長
> - 修改自增長的值
> `alter table tableName auto_increment = value`
> - 自增長的值改小, 不生效
> - 自增長的值改大, 才有效
```sql
mysql> show create table my_auto;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_auto | CREATE TABLE `my_auto` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8 | -- 當前自增長的值為 1002
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> -- 向下修改
mysql> alter table my_auto auto_increment = 10;
Query OK, 0 rows affected (0.01 sec) -- 雖然成功
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table my_auto;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_auto | CREATE TABLE `my_auto` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8 | -- 但是其實不會生效
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> -- 向上修改
mysql> alter table my_auto auto_increment = 2000;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table my_auto;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_auto | CREATE TABLE `my_auto` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000 DEFAULT CHARSET=utf8 | -- 真的修改成功
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
> #### 查看自增長變量
> `show variables like 'auto_increment%';`
> - 為什麼自增長是從 1 開始? 為什麼每次都 +1?
> - 所有系統表現 (字符集, 校對集, ...) 都是由系統內部的變量控制
```sql
mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
```
> #### 修改自增長變量
> - 修改步長
> `set auto_increment_increment = value`
> - 修改初始
> `set auto_increment_offset = value`
> - 這個修改是對整個數據庫修改
> - 修改是會話級 (登出登入就重置了)
```sql
mysql> -- 查看現在自增長變量
mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> -- 查看下次自增長數值
mysql> show create table my_auto;
... AUTO_INCREMENT=2000 ...
mysql> -- 修改自增長步長
mysql> set auto_increment_increment = 5;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> -- 開始觸發自增長
mysql> insert into my_auto values (null, 'WOWO');
Query OK, 1 row affected (0.00 sec)
mysql> insert into my_auto values (null, 'DODO');
Query OK, 1 row affected (0.01 sec)
mysql> insert into my_auto values (null, 'GG');
Query OK, 1 row affected (0.01 sec)
mysql> select * from my_auto;
+------+-------+
| id | name |
+------+-------+
| 1 | GODJJ |
| 2 | TOYZ |
| 3 | GEAR |
| 1000 | HAHA |
| 1001 | GOGO |
| 2001 | WOWO | -- 2001? 不是 2000 嗎？
| 2006 | DODO |
| 2011 | GG |
+------+-------+
8 rows in set (0.01 sec)
```
> - 修改後第一筆資料會有一些誤差, 他會重新讀取一次, 多加了一次原本的自增長步長
> #### 刪除自增長
> `alter table tableName modify 字段 類型`
> - 自增長是字段的一個屬性, 直接透過 modify 來改就好
> - 修改有主鍵的字段時, 不要再加 primary key, 否則會有兩個主鍵而報錯
> - primary key 不是存在字段的屬性, 而是單獨存放
```sql
mysql> desc my_auto;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> alter table my_auto modify id int primary key;
ERROR 1068 (42000): Multiple primary key defined
mysql> -- 因為 primary key 是單獨存在的, 並不是字段的屬性
mysql> -- 所以不能再寫一次 primary key, 否則就會有兩個主鍵
mysql> show create table my_auto;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_auto | CREATE TABLE `my_auto` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`) -- PRIMARY KEY (`id`) 在這
) ENGINE=InnoDB AUTO_INCREMENT=2016 DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table my_auto modify id int;
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> desc my_auto;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
### 唯一鍵
`unique` `unique key`
> - 一張表可能有很多需要不重複的限定, 但是主鍵只能有一個
> - 唯一鍵就可以解決這個問題
> - 唯一鍵與主鍵用法差不多, 差別在於唯一鍵允許為空, 且可以多個為空 (空字段不參與唯一性比較)
> #### 新增唯一鍵
> - 創建表時, 直接在字段後面寫 `unique` 或 `unique key`
```sql
mysql> create table my_uni (
num char(10) unique,
name varchar(10) unique
);
Query OK, 0 rows affected (0.01 sec)
mysql> desc my_uni;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num | char(10) | YES | UNI | NULL | |
| name | varchar(10) | YES | UNI | NULL | | -- 唯一鍵可以好幾個
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
> - `unique(元素)`
```sql
mysql> create table my_uni2 (
num char(10),
name char(10),
unique(num),
unique(name)
);
Query OK, 0 rows affected (0.01 sec)
mysql> desc my_uni2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| num | char(10) | YES | UNI | NULL | |
| name | char(10) | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
```
> - 追加唯一鍵
> `alter table tableName add unique(字段)`
```sql
mysql> -- 沒有唯一鍵表
mysql> create table my_uni3 (
num char(10),
name char(10)
);
Query OK, 0 rows affected (0.01 sec)
mysql> desc my_uni3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| num | char(10) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> -- 追加唯一鍵
mysql> alter table my_uni3 add unique(num);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc my_uni3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| num | char(10) | YES | UNI | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
> #### 唯一鍵的幻覺
> - 當表沒有主鍵, 且唯一鍵剛好不能為空, 此時系統會把該字段當成主鍵
> - 不過這一切都是錯覺, 因為他還是唯一鍵
```sql
mysql> -- 當表沒有主鍵,且設置唯一鍵剛好不能為空
mysql> create table my_uni4 (
-> id char(10) not null unique,
-> name char(10)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc my_uni4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | char(10) | NO | PRI | NULL | | -- 直接被當成主鍵(幻覺)
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> show create table my_uni4;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_uni4 | CREATE TABLE `my_uni4` (
`id` char(10) COLLATE utf8mb4_general_ci NOT NULL,
`name` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
UNIQUE KEY `id` (`id`) -- 但實際上還是唯一鍵
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> -- 追加主鍵後, 就會恢復正常了
mysql> alter table my_uni4 add primary key(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc my_uni4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | char(10) | NO | UNI | NULL | |
| name | char(10) | NO | PRI | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
> #### 唯一鍵約束
> - 與主鍵幾乎相同, 差別在於唯一鍵允許為空,
> - 當唯一鍵設置 not null, 那作用與 primary key 相同
```sql
mysql> insert into my_uni values (null, 1), (null, 2), ('GODJJ', 3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from my_uni;
+-------+------+
| num | name |
+-------+------+
| NULL | 1 |
| NULL | 2 | -- null 不參與唯一鍵比較
| GODJJ | 3 |
+-------+------+
3 rows in set (0.00 sec)
mysql> insert into my_uni values ('GODJJ', 4); -- 重複而報錯
ERROR 1062 (23000): Duplicate entry 'GODJJ' for key 'num'
```
> #### 更新唯一鍵 & 刪除唯一鍵
> - 唯一鍵可以有多個, 直接新增就好了, 不用刪除後新增
> - 刪除唯一鍵
> `alter table tableName drop index 索引名字`
> - 唯一鍵默認使用字段名字作為索引名
> - `alter table tableName drop unique key` 是不行的
> - 因為唯一鍵不只一個
```sql
mysql> show create table my_uni;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_uni | CREATE TABLE `my_uni` (
`num` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
UNIQUE KEY `num` (`num`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> -- `num` (`num`) => 名字(字段) => 為一鍵默認使用字段名作為索引名
mysql> alter table my_uni drop index num;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc my_uni;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num | char(10) | YES | | NULL | |
| name | varchar(10) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```
## 索引
> - 幾乎所有的索引都建立在字段上
> - 系統根據某種算法, 將已有的數據或未來可能增加的數據, 單獨建立一個文件,
> 實現快速匹配數據, 並能快速找到對應的表中的紀錄
> - 所以索引能實現:
> - 提升查詢數據效率
> - Q. 既然可以增加查詢效率, 為何不每條數據都加索引?
> - 索引本身會產生索引文件, 有時候甚至比數據文件大, 非常耗費空間
> - 想像一下書本的目錄也不會每頁都寫
> - Q. 既然耗費空間, 那就都不加索引?
> - 在沒有索引的情況下, 系統是從第一條開始取出來匹配,
> 並且即使匹配成功後, 系統還是會繼續往下匹配, 直到沒有數據為止(全表查詢/索引)
> - 有索引的話, 系統會先去索引文件找, 然後再到索引文件指向的地方找, 其他的都不會找
> - 約束數據有效性(例如唯一性)
> - 索引使用時機
> - 當某字段經常需要作為查詢條件而 `經常使用` 時, 再新增索引即可
> - 當某字段需要進行數據有效性約束, 例如主鍵, 唯一鍵
> - MySQL 提供的索引
> - 主鍵索引: `primary key`
> - 唯一索引: `unique key`
> - 全文索引: `fulltext index`
> - 普通索引: `index`
### 全文索引
> - 針對文章內部的關鍵字進行索引
> - 全文索引最大的難題是如何確定關鍵字
> - 英文相對容易, 因為英文單詞間有空格
> - 中文相當困難
> - 中文單詞間沒有空格
> - 中文的字可以隨意組合, 每個字在不同組合有不同意思
> - 這個技術叫做分詞 sphinx
## 關係
> - 實體間的關係, 映射到數據庫表上可分為三種: 一對一, 一對多, 多對多
> - 一對一: A表中的一條紀錄只能對應到B表中的一條紀錄, 反之亦然
> - 例如你的常用基本資料(名字電話年齡...)對上你的不常用基本資料(婚姻工作...)
> - 為保證兩個表之間的信息訊息對應上,
> 必須找一個具有唯一性的字段來共同連接兩張表
> - 一對多:
> - A表的一條紀錄可對應B表的多條紀錄,
> 但B表中的一條紀錄只能對應A表中的一條紀錄
> - 例如一個媽媽可能有好多孩子, 但好多孩子通常只有一個媽媽
> - 多對多:
> - A表中一條紀錄能對應B表中的多條紀錄, 反之亦然
> - 例如老師與學生, 老師教過多個學生, 學生也被多個老師教過
> - 所有關係都是表與表的關係
> - 問題: 當遇到多對一時, 會遇到一個字段想要保存多個數據的窘境
> - 例如媽媽的孩子主鍵欄位需要填兩個以上
> - 又例如老師的學生ID可能需要十個以上的數據
> - 但這不符合設計規範, 此時需要一張關係表來描述大家的關係
### 中間關係表
> - 商品表
|Item_ID|NAME|PRICE|CUSTOMER_ID|
|:--:|:--:|:--:|:--:|
|1|肥皂|20|1(小明)?2(小花)? 有可能兩個人都有買肥皂啊|
|2|牙刷|15||
> - 顧客表
|Customer_ID|NAME|AGE|Item_ID|
|:--:|:--:|:--:|:--:|
|1|小明|20|1(肥皂)?2(牙刷)? 有可能小明兩個都買啊|
|2|小花|20||
> - 中間關係表
|ID|Item_ID|Customer_ID||
|:--:|:--:|:--:|:--:|
|1|1|1|小明買了肥皂|
|2|1|2|小明買了牙刷|
|3|2|1|小花買了肥皂|
|4|2|2|小花買了牙刷|
## 範式(Normal Format)
> - 離散數學的知識
> - 為解決數據存儲與優化問題
> - 存儲數據之後, 凡是能夠通過關係尋找出來的數據, 就不再重複存儲, 以減少冗餘的數據
> - 範式是一種分層結構, 總共六層,
> - 1NF ~ 6NF
> - 1NF 最寬鬆, 6NF 最嚴謹
> - 滿足上層的前提是當前層要滿足
> - MySQL 屬於關係型數據庫,
> - 因為有空間浪費, 故致力於節省空間, 與範式要解決的問題相同
> - 但數據庫還需保證效率問題, 故不會完全按照範式要求實現, 通常只需滿足到 3NF
> - 範式並非強制規範, 僅為設計數據庫的參考指南
### 1NF
> - 設計表存儲數據時, 如表中數據字段存儲的數據在取出來前需額外拆分，表示不滿足 1NF
> - 反過來說 1NF 要求字段具有原子性(不可拆分)
> - 不符合 1NF
|課程|時間(上課, 下課)|
|:--:|:--:|:--:|:--:|
|JS|0900, 1200|
|CSS|1300, 1600|
> - 符合 1NF
|課程|上課時間|下課時間|
|:--:|:--:|:--:|
|JS|0900|1200|
|CSS|1300|1600|
### 2NF
> - 如有複合主鍵, 且表中字段並非完整依賴複合主鍵, 而僅依賴複合主鍵的某個字段, 那就不符合 2NF
> - 亦即 2NF 要求設計不允許出現部分依賴
> - 下表不符合 2NF
> - 下表的主鍵必須為老師+班級, 因為老師可能教好多班級
> - 開始課程與結束課程為完全依賴主鍵(老師+班級)
> - 性別僅依賴老師, 教室僅依賴班級 => 部分依賴
|老師P_Key|性別|班級P_Key|教室|開始課程|結束課程|
|:--:|:--:|:--:|:--:|:--:|:--:|
|GODJJ|男|一年三班|103|1/1|3/1|
|GODJJ|男|二年四班|204|4/1|6/1|
|GEAR|男|一年三班|103|4/1|7/1|
> - 解法1. : 將部分依賴的主鍵單獨成表, 例如老師加性別, 班級加教室
> - 這個等到 3NF 會使用
> - 解法2. : 取消複合主鍵, 使用邏輯主鍵
> - 既然因為複合主鍵有部分依賴問題, 那就不要複合了
> - 使用邏輯主鍵的問題:
> - 沒有任何約束性, 純粹達到數據不重複的效果
> - 只能透過業務邏輯來約束, 可以考慮使用複合唯一鍵
|ID\(P\)|老師|性別|班級|教室|開始課程|結束課程|
|:--:|:--:|:--:|:--:|:--:|:--:|:--:|
|1|GODJJ|男|一年三班|103|1/1|3/1|
|2|GODJJ|男|二年四班|204|4/1|6/1|
|3|GEAR|男|一年三班|103|4/1|7/1|
### 3NF
> - 理論上表上所有字段都應該`直接`依賴主鍵(邏輯主鍵除外, 但應該依賴邏輯主鍵代表的業務主鍵)，但當表中字段透過非主鍵字段依賴而實現依賴主鍵時, 不符合 3NF
> - 換言之，3NF 不允許傳遞依賴
> #### 下表不符合 3NF
> - 性別依賴老師，老師依賴主鍵(ID: 老師加班級)
> - 教室依賴班級，班級依賴主鍵(ID: 老師加班級)
> - 性別和教室都屬於傳遞依賴
|ID\(P\)|老師|性別|班級|教室|開始課程|結束課程|
|:--:|:--:|:--:|:--:|:--:|:--:|:--:|
|1|GODJJ|男|一年三班|103|1/1|3/1|
|2|GODJJ|男|二年四班|204|4/1|6/1|
|3|GEAR|男|一年三班|103|4/1|7/1|
> #### 解決方法: 拆表
> - 將傳遞依賴的表拆出來
> - 並在對應信息使用實體表的主鍵
> - 節省空間, 避免冗余(1NF)
> - 例如課程資訊表
> - 老師ID 用 int 存只要 4B
> - 如果要存全名, 可能要用 varchar(xx)
> - 課程資訊表
|ID\(P\)|老師ID|班級ID|開始課程|結束課程|
|:--:|:--:|:--:|:--:|:--:|
|1|1|1|1/1|3/1|
|2|1|2|4/1|6/1|
|3|2|3|4/1|7/1|
> - 班級表
> - 雖然教室對應主鍵(ID)還是傳遞依賴,
> 但邏輯主鍵(ID)應該直接視為他代表的業務主鍵(班級)
> 否則永遠會有傳遞依賴問題
|ID\(P\)|班級|教室|
|:--:|:--:|:--:|
|1|一年三班|103|
|2|二年四班|204|
|3|一年三班|103|
> - 講師表
|ID\(P\)|老師|性別|
|:--:|:--:|:--:|
|1|GODJJ|男|
|2|GEAR|男|
### 逆規範化
> - 當表中的某些字段需要透過查詢其他表來獲得訊息時，查詢效率當然會降低
> - 此時實務上可能會考量犧牲空間來換取效率,
> 直接在那些字段保存查詢後的數據，如此查詢表時就會快速許多，但會增加數據冗余
> - 逆規範化就是空間(磁盤利用率)與效率的取捨
|ID\(P\)|老師ID|班級ID|開始課程|結束課程|
|:--:|:--:|:--:|:--:|:--:|
|1|GODJJ|一年三班|1/1|3/1|
|2|GODJJ|二年四班|4/1|6/1|
|3|GEAR|一年三班|4/1|7/1|
Sign in with Wallet
Connect another wallet