《數據庫》 MySQL庫表操作

1. SQL語句基礎

1.2 SQL簡介

SQL：結構化查詢語言(Structured Query Language)，在關系型數據庫上執行數據操作、數據檢索以及數據維護的標準語言。使用SQL語句，程序員和數據庫管理員可以完成如下的任務

改變數據庫的結構

更改系統的安全設置

增加用戶對數據庫或表的許可權限

在數據庫中檢索需要的信息

對數據庫的信息進行更新

1.3 SQL語句分類

MySQL致力于支持全套ANSI/ISO SQL標準。在MySQL數據庫中，SQL語句主要可以劃分為以下幾類
- DDL(Data Definition Language):數據定義語言，定義對數據庫對象(庫、表、列、索引)的操作，如：CREATE、DROP、ALTER、RENAME、 TRUNCATE等
- DML(Data Manipulation Language): 數據操作語言，定義對數據庫記錄的操作，如：INSERT、DELETE、UPDATE、SELECT等
- DCL(Data Control Language): 數據控制語言，定義對數據庫、表、字段、用戶的訪問權限和安全級別，如：
  - GRANT、REVOKE等
  - Transaction Control:事務控制
  - COMMIT、ROLLBACK、SAVEPOINT等

1.4 SQL語句的書寫規范

在數據庫系統中，SQL語句不區分大小寫(建議用大寫) ，但字符串常量區分大小寫

SQL語句可單行或多行書寫，以“;”結尾。

關鍵詞不能跨多行或簡寫。

用空格和縮進來提高語句的可讀性。

子句通常位于獨立行，便于編輯，提高可讀性。

注釋：

/* */ 和// 為多行注釋

-- 和 # 為單行注釋

2. 數據庫操作

2.1 數據庫的登錄及退出

登錄格式

mysql ?-u用戶名 ?-h服務器的主機地址 -p密碼 -A

分析

u 后面跟登錄數據庫的用戶名，這里使用root

-h 后面的參數是服務器的主機地址，在這里客戶端和服務器在同一臺機器上，所以輸入 localhost 或者 IP 地址

-p 后面是用戶登錄密碼，注意：==-p 和密碼之間沒有空格。如果出現空格，系統將不會把后面的字符串當成密碼來對待==，沒有密碼時不寫

-A參數：不預讀數據庫信息，加上該參數則不預讀數據庫中的表等信息，不會有表名及列名的補全，但讀取速度會加快，不加該參數可能使用數據庫時出現下列提示（不影響使用）

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

顯示信息解釋：
- Commands end with; or\g：mysql 命令是以分號（;）或“\g”來結束的，遇到這個結束符就開始執行命令
- Your MySQL connection id is 22：數據庫的連接次數
- Server version: 8.0.37 Source distribution ：版本
- Type 'help;' or '\h' for help：輸入”help;“或者”\h“可以看到幫助信息
- Type '\c' to clear the current input statement：表示遇到”\c“就清除前面的命令，注意不是清屏
退出：exit quit \q

PS C:\Users\Administrator> mysql  -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>\q# 注意：帶有密碼登錄，會有警告，提示不安全
PS C:\Users\Administrator> mysql  -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.37 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

2.2 查看數據庫

格式

mysql> show databases  [like  條件];    # 注意有s
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

MySQL自帶數據庫

Information_schema: 主要存儲了系統中的一些數據庫對象信息，如：用戶表信息、列信息、權限信息、字符集信息、分區信息等。(數據字典表)

performance_schema：主要存儲數據庫服務器的性能參數

mysql: 存儲了系統的用戶權限信息及幫助信息

sys: 5.7新增，之前版本需要手工導入。這個庫是通過視圖的形式把information_schema 和performance_schema結合起來，查詢出更加令人容易理解的數據

使用條件查看

mysql> show databases  like  'sys';
+----------------+
| Database (sys) |
+----------------+
| sys            |
+----------------+
1 row in set (0.00 sec)

帶有通配符的條件

%：匹配任意零個或多個字符

_ ：匹配任意單個字符

mysql> show databases  like  's%';
+---------------+
| Database (s%) |
+---------------+
| sys           |
+---------------+
1 row in set (0.00 sec)

顯示時間

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-02-10 09:39:14 |
+---------------------+
1 row in set (0.00 sec)

顯示數據庫版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.37    |
+-----------+
1 row in set (0.00 sec)

2.3 創建數據庫

格式

create ?database ?數據庫名

示例

mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+

如數據庫已存在，則報錯

mysql> create database testdb;
ERROR 1007 (HY000): Can't create database 'testdb'; database exists

查看創建數據哭的語句

# 格式
show  create database 數據庫名

mysql> show create database testdb;
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| testdb   | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)# 分析：
# 上述顯示創建數據庫語句的內容可看做3段內容# CREATE DATABASE `testdb` ：表示創建數據庫testdb# /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ ：表示注釋內容，顯示字符集采用utf8mb4方案，一個字符需要 1 ~ 4 個字節。可以存儲一些 emoji 表情； utf8mb4_0900_ai_ci表示字符比較規則即ai為不區分重音，ci為不區分大小寫# /*!80016 DEFAULT ENCRYPTION='N' */ 表示數據庫未加密

注意：再創建數據庫或查看創建數據庫語句時，database沒有s

2.4 mysql中的反引號

上例中查看的sql語句有反引號，其作用可以避免一些與SQL語法沖突的問題，同時可以使代碼更加規范化和易讀性更強

如 select name, address from table; 其中table若當做表名但其為關鍵字回報錯，加上反引號可以避免與SQL關鍵字的沖突：

mysql> select name, address from `table`;

注意：

只有保留關鍵字和特殊字符需要使用反引號，普通的表名和列名不需要

數據類型之間可以加或者不加反引號，但為了代碼規范化，建議加上

2.5 切換數據庫

查看當前使用的數據庫，格式：

select  database();

注意：當前沒有使用數據庫，則顯示空

mysql> select  database();
+------------+
| database() |
+------------+
| NULL       |
+------------+

選擇數據庫格式

use  數據庫名

mysql> use testdb;
Database changed
mysql> select database();   # 再次查看
+------------+
| database() |
+------------+
| testdb     |
+------------+

2.6 查看當前用戶

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

2.7 刪除數據庫

格式：

drop database 數據庫名

示例：

mysql> drop database testdb;
Query OK, 0 rows affected (0.01 sec)mysql> show  databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2.8 小結

# 新建數據庫
create database 數據庫名# 設為當前數據庫
use  數據庫名# 查看當前使用的數據庫
select  database();# 顯示所有數據庫
show  databases;# 刪除數據庫
drop database 數據庫名# 查看當前登錄的用戶及主機名
select user();

2.9 習題

創建temp1、temp2數據庫，查看所有數據庫，使用temp1數據庫，刪除所有數據庫

見文章MySQL庫表操作-數據庫操作練習-CSDN博客

3. MySQL字符集

? ?MySQL字符集包括字符集（CHARACTER）和排序規則(校對規則)（COLLATION）兩個概念

3.1 字符集

Character Set(字符集)：是字符的編碼規則，規定了字符在數據庫中的存儲格式，比如占多少空間，支持哪些字符等
不同的字符集有不同的編碼規則，在運維和使用MySQL數據庫中，選取合適的字符集非常重要，如果選擇不恰當，輕則影響數據庫性能，嚴重的可能導致數據存儲亂碼
mysql 5.7的默認字符集是latin1，而8.0中是utf8mb4；
mysql8的庫表創建以及程序中盡可能使用utf8mb4字符集（可支持emoji）
mysql中的utf8字符集是utf8mb3字符集的別名，避免使用

latin1支持西歐字符、希臘字符等gbk支持中文簡體字符，但是不是國際通用字符集big5支持中文繁體字符utf8幾乎支持世界所有國家的字符。utf8mb4完全兼容UTF-8，用四個字節存儲更多的字符

3.2 字符序

字符序就是字符排序的規則集合

如：使用A>B>a>b的規則來進行排序或者另一種規則a>b>A>B順序排序

字符序主要對字符的排序有影響

3.3 查看MySQL字符集

查看所有支持的字符集

# 格式1：
mysql> show character set;  
+----------+---------------------------+---------------------+--------+
| Charset  | Description               | Default collation   | Maxlen |
+----------+---------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian        | armscii8_general_ci |      1 |
| ascii    | US ASCII                  | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese  | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset     | binary              |      1 |
…………
# 字段含義# Charset: 字符集的名稱；# Description：字符集的簡單描述；# Default collation：該字符集的默認字符序；# Maxlen：該字符集中字符最大存儲長度。# 或者使用\g替代定界符分號
mysql> show character set\g

# 格式2：
# \G表示將查詢結果進行按列打印，即將查到的結構旋轉90度變成縱向顯示,不需要分號
mysql> show character set\G
*************************** 1. row ***************************Charset: armscii8Description: ARMSCII-8 Armenian
Default collation: armscii8_general_ciMaxlen: 1
*************************** 2. row ***************************Charset: asciiDescription: US ASCII
Default collation: ascii_general_ciMaxlen: 1
*************************** 3. row ***************************Charset: big5Description: Big5 Traditional Chinese
Default collation: big5_chinese_ciMaxlen: 2
*************************** 4. row ***************************Charset: binaryDescription: Binary pseudo charset
Default collation: binaryMaxlen: 1
……

查看指定字符集

mysql> show character set like 'gbk';
+---------+------------------------+-------------------+--------+
| Charset | Description            | Default collation | Maxlen |
+---------+------------------------+-------------------+--------+
| gbk     | GBK Simplified Chinese | gbk_chinese_ci    |      2 |
+---------+------------------------+-------------------+--------+
1 row in set (0.00 sec)mysql> show character set like 'utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
| utf8mb3 | UTF-8 Unicode    | utf8mb3_general_ci |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_0900_ai_ci |      4 |
+---------+------------------+--------------------+--------+

3.4 查看MySQL字符序

MySQL字符序命名規則：

以字符序對應的字符集名稱開頭

以國家名居中（或以general居中）

后綴：

bin：二進制；

ci：大小寫不敏感；

cs：大小寫敏感；

ai：口音（Accent）不敏感；

as：口音敏感；

ks：假名（Kanatype）敏感

查看支持的字符序

mysql> show collation;# 字段含義# Collation：字符序名稱；# Charset：該字符序關聯的字符集；# Id：字符序ID；# Default：該字符序是否是所關聯的字符集的默認字符序。armscii8_general_ci就是armscii8的默認字符序，而armscii8_bin就不是；# Compiled：字符集是否已編譯到服務器中；# Sortlen：這與對以字符集表示的字符串進行排序所需的內存量有關；# Pad_attribute：這表明了字符序在比較字符串時對末尾padding的處理。NO PAD表明在比較字符串時，末尾的padding也會考慮進去，否則不考慮。

指定條件查詢:

mysql> show collation where charset = "utf8mb4";

3.5 查看當前字符集的設置

通過變量查看

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4   # 重要            |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4   # 重要            |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

變量含義

character_set_client：MySQL客戶端的字符集。
character_set_connection：數據通信鏈路字符集，當MySQL客戶機向服務器發送請求時，請求數據以該字符集進行編碼。
character_set_database：數據庫默認使用的字符集。
character_set_filesystem：MySQL服務器文件系統字符集，該值是固定的binary。
character_set_results：服務器返回給客戶端的結果使用的字符集。
character_set_server：服務器默認的字符集。
character_set_system：服務器存儲元數據使用的字符集。

3.6?utf8和utf8mb4的區別

MySQL在5.5.3之后增加了這個utf8mb4的編碼，mb4就是most bytes 4的意思，專門用來兼容四字節的unicode。好在utf8mb4是utf8的超集，除了將編碼改為utf8mb4外不需要做其他轉換。當然，為了節省空間，一般情況下使用utf8也就夠了。

既然utf8能夠存下大部分中文漢字，那為什么還要使用utf8mb4呢？原來mysql支持的 utf8 編碼最大字符長度為 3 字節，如果遇到 4 字節的寬字符就會插入異常了。三個字節的 UTF-8 最大能編碼的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文種平面(BMP)。也就是說，任何不在基本多文本平面的 Unicode字符，都無法使用 Mysql 的 utf8 字符集存儲。包括 Emoji 表情(Emoji 是一種特殊的 Unicode 編碼，常見于 ios 和 android 手機上)，和很多不常用的漢字，以及任何新增的 Unicode 字符，如表情等等(utf8的缺點)。

因此在8.0之后，建議大家使用utf8mb4這種編碼。

4. 數據庫對象

4.1 組成

4.2 數據庫對象的命名規則

命名使用具有意義的英文詞匯，詞匯中間以下劃線分隔

名稱由字母、數字、#、下劃線、$組成，必須以英文字母開頭

不要使用MySQL的保留字

所有數據庫對象使用小寫字母，實際上MySQL中是可以設置大小寫是否敏感的，為了保證統一性應使用小寫表示

在同一個數據庫對象集合內對象不能同名

4.3 生產中對象命名規范

數據庫：

數據庫命名盡量不超過30個字符

數據庫命名一般為項目名稱+代表庫含義的簡寫，比如IM項目的工作流數據庫，可以是 im_flow

命名應使用小寫

表：

常規表：以t_開頭，t代表table的意思，命名規則即 t + 模塊（包含模塊含義的簡寫）+ 表（包含表含義的簡寫），比如用戶模塊的教育信息表：t_user_eduinfo

臨時表：temp前綴+模塊+表+日期后綴：temp_user_eduinfo_20240520

備份表（用于保存和歸檔歷史數據或者作為災備恢復的數據）命名規則，bak前綴+模塊+表+日期后綴：bak_user_eduinfo_20231219

同一個模塊的表盡可能使用相同的前綴，表名稱盡可能表達含義

多個單詞以下劃線 _ 分隔

常規表表名盡量不超過30個字符，temp表和bak表視情況而定，也盡量簡短為宜，命名應使用小寫

5. 表的基本操作

5.1 數據類型

類型組成：數字、文本、日期/時間類型
整型

數據類型	字節數	帶符號值范圍	不帶符號值范圍
tinyint	1	[-128,[127]	[0,255]
smallint	2	[-32768,32767]	[0,65535]
mediumint	3	[-8388608,8388607]	[0,16777215]
int	4	[-2147483648,2147483647]	[0,4294967295]
bigint	8	[-9223372036854775808,9223372036854775807]	[0,18446744073709551616]

- 注意：
? 實際開發的角度，一定要為合適的列選取合適的數據類型，如

? ? 一個枚舉字段只有0和1兩個枚舉值，選用TINYINT就足夠了，但在開發場景下卻使用了BIGINT，這就造成了資源浪費
? ? 簡單計算一下，假使該數據表中有100W數據，那么總共浪費了700W字節也就是6.7M左右，如果更多的表這么做了，那么浪費的更多

? - 整型(N)表現形式：有時代碼寫法為int(10),表示數字寬度限制，要求如下：

? ? ? 無論N等于多少，int永遠占4個字節
? ? ? N表示的是顯示寬度，不足則補0，超過長度則會無視長度直接顯示整個數字

浮點型

數據類型	字節數	備注
float	4	單精度浮點型
double	8	雙精度浮點型

注意：可以使用float(M，D)、double(M，D)格式限制寬度按（M）和精度（D），如float（3,2），不指定M、D的時，會按照實際的精度來處理

定點型：decimal(size,d) ，作為字符串存儲的 DOUBLE 類型，允許固定的小數點，由于float、double類型存在精度丟失問題，即**寫入數據庫的數據未必是插入數據庫的數據**，而decimal無論寫入數據中的數據是多少，都不會存在精度丟失問題，這就是要引入decimal類型的原因，decimal類型常見于銀行系統、互聯網金融系統等對小數點后的數字比較敏感的系統中，結論：float/double在db中存儲的是近似值，而decimal則是以字符串形式進行保存。

mysql> create database  mydb1_test;mysql> use mydb1_test;
Database changedmysql> create table t1(float_num float(10,2) , double_num  double(20,2) , decimal_num decimal(20,2));
Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql> insert into t1 value (1234567.66,1234567899000000.66,123456789000000.66);
Query OK, 1 row affected (0.00 sec)mysql> select * from  t1;
+------------+---------------------+--------------------+
| float_num  | double_num          | decimal_num        |
+------------+---------------------+--------------------+
| 1234567.62 | 1234567899000000.80 | 123456789000000.66 |
+------------+---------------------+--------------------+

# 注意：Query OK, 0 rows affected, 2 warnings (0.03 sec)含義如下：該信息是MySQL數據庫執行SQL語句的結果。它表示SQL語句成功執行，但是沒有影響到任何行，并且出現了兩個警告。"Query OK" 表示查詢操作成功。"0 rows affected" 表示SQL語句執行后，影響到數據庫中的0行數據。"2 warnings" 表示在執行這個查詢時，發出了兩個警告。"(0.03 sec)" 表示查詢執行的時間。
可以通過命令  show  warnings; 查看警告信息

文本型

數據類型	描述
char(size)	保存固定長度的字符串（可包含字母、數字以及特殊字符）。在括號中指定字符串的長度。最多 255 個字符。
varchar(size)	保存可變長度的字符串（可包含字母、數字以及特殊字符）。在括號中指定字符串的最大長度。最多 255 個字符。注釋：如果值的長度大于 255，則被轉換為 TEXT 類型。
tinytext	存放最大長度為 255 個字符的字符串。
text	存放最大長度為 65,535 個字符的字符串。
blob	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字節的數據。
mediumtext	存放最大長度為 16,777,215 個字符的字符串。
mediumelob	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字節的數據。
longtext	存放最大長度為 4,294,967,295 個字符的字符串。
longblob	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字節的數據。
enum(x,y,z,etc.)	允許輸入可能值的列表。可以在 ENUM 列表中列出最大 65535 個值。如果列表中不存在插入的值，則插入空值。注釋：這些值是按照你輸入的順序存儲的。可以按照此格式輸入可能的值： ENUM('X','Y','Z')
set	與 enum 類似， SET 最多只能包含 64 個列表項，不過 SET 可存儲一個以上的值。

文本型

數據類型	描述
char(size)	保存固定長度的字符串（可包含字母、數字以及特殊字符）。在括號中指定字符串的長度。最多 255 個字符。
varchar(size)	保存可變長度的字符串（可包含字母、數字以及特殊字符）。在括號中指定字符串的最大長度。最多 255 個字符。注釋：如果值的長度大于 255，則被轉換為 TEXT 類型。
tinytext	存放最大長度為 255 個字符的字符串。
text	存放最大長度為 65,535 個字符的字符串。
blob	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字節的數據。
mediumtext	存放最大長度為 16,777,215 個字符的字符串。
mediumelob	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字節的數據。
longtext	存放最大長度為 4,294,967,295 個字符的字符串。
longblob	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字節的數據。
enum(x,y,z,etc.)	允許輸入可能值的列表。可以在 ENUM 列表中列出最大 65535 個值。如果列表中不存在插入的值，則插入空值。注釋：這些值是按照你輸入的順序存儲的。可以按照此格式輸入可能的值： ENUM('X','Y','Z')
set	與 enum 類似， SET 最多只能包含 64 個列表項，不過 SET 可存儲一個以上的值。

注意：

- blob和text都是為了存儲很大數據而設計的字符串數據類型，blob采用二進制形式存儲無排序規則和字符集，text采用字符形式存儲且有排序規則和字符集適用于存儲如文章內容、評論等

- char是固定長度字符串，其長度范圍為0~255且與編碼方式無關，無論字符實際長度是多少，都會按照指定長度存儲，不夠的用空格補足，char類型數據時會將結尾的所有空格處理掉

mysql> create table t2(char_value char(5) , varchar_value varchar(5));
Query OK, 0 rows affected (0.01 sec)mysql> insert  into t2 value('a','a');
Query OK, 1 row affected (0.00 sec)mysql> insert  into t2 value('  a','  a');
Query OK, 1 row affected (0.01 sec)mysql> insert  into t2 value('a  ','a  ');
Query OK, 1 row affected (0.00 sec)mysql> insert  into t2 value('  a  ','  a  ');
Query OK, 1 row affected (0.00 sec)mysql> select  * from  t2;
+------------+---------------+
| char_value | varchar_value |
+------------+---------------+
| a          | a             |
|   a        |   a           |
| a          | a             |
|   a        |   a           |
+------------+---------------+
4 rows in set (0.00 sec)mysql> select length(char_value),length(varchar_value) from  t2;
+--------------------+-----------------------+
| length(char_value) | length(varchar_value) |
+--------------------+-----------------------+
|                  1 |                     1 |
|                  3 |                     3 |
|                  1 |                     3 |
|                  3 |                     5 |
+--------------------+-----------------------+
4 rows in set (0.00 sec)

date 類型

數據類型	字節數	格式	備注
date	3	yyyy-MM-dd	存儲日期值
time	3	HH:mm:ss	存儲時分秒
year	1	yyyy	存儲年
datetime	8	yyyy-MM-dd HH:mm:ss	存儲日期+時間
timestamp	4	yyyy-MM-dd HH:mm:ss	存儲日期+時間，可作時間戳

mysql> create table t3( date_value date,time_value time,year_value year,datetime_value datetime,timestamp_value timestamp);
Query OK, 0 rows affected (0.02 sec)mysql> insert  into  t3  values(now(), now(), now(), now(), now());
Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t3;

總結：常用數據類型：

5.2 創建表

格式：

create  table  表名 （表選項）

表定義選項格式為：

列名1  列類型1  約束, 列名2  列類型2  約束,……

默認的情況是，表被創建到當前的數據庫中。若表已存在、沒有當前數據庫或者數據庫不存在，則會出現錯誤
使用 crate table 創建表時，必須指定以下信息：

要創建的表的名稱不區分大小寫，不能使用SQL語言中的關鍵字，如DROP、ALTER、INSERT等。

必須指定數據表中每個列（字段）的名稱和數據類型，如果創建多個列，要用逗號隔開

示例1：創建數據庫并新建表

數據表屬于數據庫，在創建數據表之前，應使用語句“use <數據庫>”指定操作在哪個數據庫中進行，如果沒有選擇數據庫，就會拋出 No database selected 的錯誤

mysql> select  database();  # 查看當前數據庫mysql> create database mydb2_stuinfo;  # 創建數據庫
Query OK, 1 row affected (0.02 sec)mysql> use mydb2_stuinfo;  # 使用數據庫
Database changedmysql> create table student1(id int, name varchar(30), sex char(2), age int unsigned, score float, addr varchar(50));
Query OK, 0 rows affected (0.02 sec)mysql> show tables;
+-------------------------+
| Tables_in_mydb2_stuinfo |
+-------------------------+
| student1                |
+-------------------------+

示例2：新建員工表

mysql> create database mydb3_employee;
Query OK, 1 row affected (0.01 sec)mysql>  use mydb3_employee;
Database changedmysql> create table tb1(id int(11), name varchar(25), salary float); 
Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> show tables;
+--------------------------+
| Tables_in_mydb3_employee |
+--------------------------+
| tb1                      |
+--------------------------+
1 row in set (0.00 sec)

練習：

在mydb2_stuinfo庫中新建班級表class1，字段如下：cla_num int(5) , cla_name varchar(10) , number int(4) , monitor varchar(20) ,creation_date date

mysql>  use  mydb2_stuinfo;mysql> create table class1(cla_num int(5), cla_name varchar(10), number int(4),monitor  varchar(20),creation_date  date);mysql> desc  class1;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| cla_num       | int         | YES  |     | NULL    |       |
| cla_name      | varchar(10) | YES  |     | NULL    |       |
| number        | int         | YES  |     | NULL    |       |
| monitor       | varchar(20) | YES  |     | NULL    |       |
| creation_date | date        | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

5.3 查看表

show語句：顯示當前數據庫中已有的數據表

describe語句：查看數據表中各列的信息

字段意義分析：
- Field :字段名稱
- type：字段類型
- null：是否允許為空
- key：索引類型
- default：默認值
- extra：填充
使用\G可以查看更全面的表定義信息

mysql> show  create  table  student1\G
*************************** 1. row ***************************Table: student1
Create Table: CREATE TABLE `student1` (`id` int DEFAULT NULL,`name` varchar(30) DEFAULT NULL,`sex` char(2) DEFAULT NULL,`age` int unsigned DEFAULT NULL,`score` float DEFAULT NULL,`addr` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

5.4 刪除表

格式

drop table [if exists] 表名；

mysql> use mydb3_employee;
Database changedmysql> show columns from tb1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> drop table tb1;
Query OK, 0 rows affected (0.01 sec)mysql> show  tables;
Empty set (0.00 sec)

注意：

用戶必須擁有執行 drop table 命令的權限，否則數據表不會被刪除
推薦使用if exists字句，即先判斷是否存在，存在則刪除，如：

mysql> drop table  if exists  tb1;
Query OK, 0 rows affected (0.01 sec)

5.5 修改表

# 法1：
alter  table  表名  rename  新表名;  # 法2：
rename  table  表名  to  新表名;

mysql> use mydb2_stuinfo;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_mydb2_stuinfo |
+-------------------------+
| class1                  |
| student1                |
+-------------------------+
1 row in set (0.00 sec)mysql> create table student2(id int(11), name varchar(30), salary float);
Query OK, 0 rows affected, 1 warning (0.05 sec)mysql> desc student2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> alter table student2 rename student3; # 修改
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
+-------------------------+
| Tables_in_mydb2_stuinfo |
+-------------------------+
| class1                  |
| student1                |
| student3                |
+-------------------------+
2 rows in set (0.01 sec)mysql> rename table student3 to teacher1;  # 修改
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
+-------------------------+
| Tables_in_mydb2_stuinfo |
+-------------------------+
| class1                  |
| student1                |
| teacher1                |
+-------------------------+
2 rows in set (0.00 sec)

添加新列

alter table 表名 add  新列名 列類型  [after|first] 列名;# after：在指定列之后插入新列
# first：在第一列插入新列
# 注意：無before關鍵字

# 例：增加一列password	
mysql> alter table student1 add password char(8);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0# 注意：數據庫操作后得到的反饋，表明操作的結果。這個信息表示：Records: 2: 表示成功導入或處理的記錄總數是2條。Duplicates: 0: 表示在操作過程中沒有發現重復的記錄。Warnings: 0: 表示在操作過程中沒有產生任何警告。mysql> desc student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int unsigned | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| password | char(8)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)# 例:再添加一個新列notes varchar(30)，位置在score之前
mysql> alter table student1 add notes varchar(30)  after  age;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc  student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int unsigned | YES  |     | NULL    |       |
| notes    | varchar(30)  | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| password | char(8)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+# 例：再第一列添加一個新列
mysql> alter table student1 add aa int  first;mysql> desc  student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| aa       | int          | YES  |     | NULL    |       |
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int unsigned | YES  |     | NULL    |       |
| notes    | varchar(30)  | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| password | char(8)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

刪除列

alter table 表名 DROP 列名;

# 例：刪除上例的notes、aa列
mysql> alter table student1  drop notes;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> alter table student1  drop aa;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc  student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int unsigned | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| password | char(8)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

修改列名

alter  table  表名 change  舊列名  新列名 列類型;

mysql> alter table student1 change password passwd char(8);  # 改名
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student1;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int          | YES  |     | NULL    |       |
| name   | varchar(30)  | YES  |     | NULL    |       |
| sex    | char(2)      | YES  |     | NULL    |       |
| age    | int unsigned | YES  |     | NULL    |       |
| score  | float        | YES  |     | NULL    |       |
| addr   | varchar(50)  | YES  |     | NULL    |       |
| passwd | char(8)      | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

修改列類型

alter  table  表名  modify  列名  列類型;

# 插入新列，修改類型
mysql> alter table student1 add birthday char(10); # 增加
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int unsigned | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| passwd   | char(8)      | YES  |     | NULL    |       |
| birthday | char(10)     | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)mysql> alter table student1 modify birthday date; # 修改
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int unsigned | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| passwd   | char(8)      | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

5.6 修改列位置

alter table 表名 modify  列名  列類型 after 某列;

# 例：添加新列
mysql> alter table student1 add notes varchar(30) after name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0# 將notes移動到最后
mysql> alter table student1 modify  notes varchar(30) after birthday;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0# 刪除該列
mysql> alter table  student1 drop notes;

5.7 練習

新建商品數據庫mydb4_product,

使用該數據庫

創建商品表pro_tb1，字段包含編號 num int（4），商品名trade_name varchar(30)，數量number int(4) ，單價price float(5,2)

顯示創建表的字段內容

新增一列備注notes varchar(30)

將編號num字段類型修改為char（4）

刪除該表（先判斷）

mysql> create  database  mydb4_product;
Query OK, 1 row affected (0.00 sec)mysql> use  mydb4_product;
Database changedmysql> create table  pro_tb1(num  int(4), trade_name  varchar(30), number int(4), price float(5,2));
Query OK, 0 rows affected, 3 warnings (0.03 sec)mysql> desc  pro_tb1;mysql> alter table  pro_tb1 add notes varchar(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc  pro_tb1;mysql> alter table pro_tb1 modify num char(4);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc  pro_tb1;mysql> drop table if exists  pro_tb1;
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
Empty set (0.00 sec)

5.8 復制表的結構

方法一：在create table語句的末尾添加like子句，可以將源表的表結構復制到新表中，語法格式如下:

create table 新表名 like 源表

mysql> use  mydb2_stuinfo;mysql> insert into student1 value(1000,"zhangsan","M",18,98.5,"xi'an","12345678","2005-10-01");
Query OK, 1 row affected (0.00 sec)mysql> insert into student1 value(1001, "lisi", "W", 20,66.79, "baoji","87654321","2003-5-01");mysql> select * from  student1;mysql> create table student2 like student1;
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
+-------------------------+
| Tables_in_mydb2_stuinfo |
+-------------------------+
| class1                  |
| student1                |
| teacher1                |
+-------------------------+
3 rows in set (0.00 sec)mysql> select * from  student2;   # 注意：只是復制結構框架
Empty set (0.00 sec)

方法二：在create table語句的末尾添加一個select語句，可以實現表結構的復制，甚至可以將源表的表記錄拷貝到新表中，下面的語法格式將源表的結構和記錄都拷貝到新表中。

create table 新表名 select * from 源表

mysql> create table student3 select * from student1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show tables;
+-------------------------+
| Tables_in_mydb2_stuinfo |
+-------------------------+
| class1                  |
| student1                |
| student2                |
| student3                |
| teacher1                |
+-------------------------+
4 rows in set (0.00 sec)mysql> select * from  student3;

方法三：如果已經存在一張結構一致的表，復制數據:

mysql> select * from student2;
Empty set (0.00 sec)mysql> insert into student2 select * from student1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from student2;mysql> drop table if exists student2;
mysql> drop table if exists student3;
mysql> drop table if exists teacher1;

5.9 數據庫字典

數據字典：類似于系統編目或花名冊，它保存數據庫服務器上的元數據信息（數據庫的整體屬性信息）

元數據(meta data)：即“data about data” 關于數據的數據，理解為描述數據的數據，內容包括：數據庫的屬性信息、數據表的屬性信息、字段的屬性信息、視圖的屬性信息、用戶信息、統計類信息等。

information_schema數據庫是MySQL系統自帶的數據庫，它提供了數據庫元數據的訪問方式

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1_test         |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

information_schema就像是MySQL實例的一個百科全書，記錄了數據庫當中大部分我們需要了結的信息，比如字符集，權限相關，數據庫實體對象信息，外檢約束，分區，壓縮表，表信息，索引信息，參數，優化，鎖和事物等等。
通過information_schema我們可以窺透整個MySQL實例的運行情況，可以了結MySQL實例的基本信息，甚至優化調優，維護數據庫等，可以說是真正的一部百科全書。

mysql> use information_schema ;mysql> show tables;# 常用表解釋
tables－存放數據庫里所有的數據表、以及每個表所在數據庫。
schemata－存放數據庫里所有的數據庫信息
views－存放數據庫里所有的視圖信息。
columns－存放數據庫里所有的列信息。
triggers－存放數據庫里所有的觸發器。
routines－存放數據庫里所有存儲過程和函數。
key_column_usage－存放數據庫所有的主外鍵
table_constraints－存放數據庫全部約束。
statistics－存放了數據表的索引。
……

5.10 小結

# 新建表
create tables  表名(列名1 類型  ， 列名2  類型 ……);# 查看表結構
desc  表名;# 修改表名
alter  table  舊表名  rename  新表名;# 表中添加新列
alter table 表名 add  新列名 列類型  [after|first] 列名;   （after為之后）# 刪除列
alter table 表名 DROP 列名;# 修改列名
alter  table  表名 change  舊列名  新列名 列類型;# 修改列類型
alter  table  表名  modify  列名  列類型;# 移動列
alter table 表名 modify  列名  列類型 after 某列;# 刪除表
drop table  if  exists  表名;

5.11 刪除的注意事項

1.刪除數據庫表之前，一定要備份數據，以免數據丟失。

2.在刪除數據庫表之前，要確保該表沒有被其他的表所引用，否則可能會破壞外鍵約束。

3.在刪除數據庫表之前，要確保該表的數據已經備份，并且不再需要使用。

4.在刪除數據庫表之前，要確保該表的數據已經被清空，否則會破壞外鍵約束。

5.在刪除數據庫表之前，要確保該表的所有索引都已經被刪除。如果還存在索引，可能會導致刪除失敗。
6.在刪除數據庫表之前，要確保該表的所有相關程序已經停止運行，以免影響其他程序的正常運行。

6. 表的約束

6.1 概念

為什么需要約束
- 真正約束字段的是數據類型,但是數據類型約束很單一,需要有一些額外的約束,更好的保證數據的合法性,從業務邏輯角度保證數據的正確性，如：有一個字段是身份證,要求是唯一的
- 定義字段的類型無法滿足對表的約束：
  - 表字段是否可以為NULL,有沒有默認值,表字段的解釋能不能加上
  - 對于數字類型的字段可不可以指定默認表示的位數,可不可以將這個字段設置稱為唯一標識該行的數據
- 本質上Mysql是一套數據存儲解決方案,除了解決基本的數據存儲功能之外,還要保證數據盡可能的安全,減少用戶的誤操作可能性, 約束的體現,不僅僅可以體現在數據庫層面,在我們進行用戶業務邏輯編碼的時候,我們其實也可以做判斷(約束),約束其實不僅僅只能出現在數據庫層而上,編碼層面也是可以體現
約束是什么：約束是在==表上強制執行的數據校驗規則==，本質上是Mysql通過限制用戶操作的方式,來達到維護數據本身安全及數據完整性的一套方案
數據的完整性要從以下四個方面考慮：
- 實體完整性（Entity Integrity）：例如，同一個表中，不能存在兩條完全相同無法區分的記錄
- 域完整性（Domain Integrity）：例如：年齡范圍0-120，性別范圍“男/女”
- 引用完整性（Referential Integrity）：例如：員工所在部門，在部門表中要能找到這個部門
- 用戶自定義完整性（User-defined Integrity）：例如：用戶名唯一、密碼不能為空等，本部門經理的工資不得高于本部門職工的平均工資的5倍。
約束作為數據庫對象，存放在系統表中，也有自己的名字
常見約束：
- 非空約束
- 默認值約束
- 主鍵約束
- 外鍵約束
- 唯一約束
- 檢查約束
創建約束的時機：
- 在建表的同時創建
- 建表后創建（修改表）
約束可定義列級或表級約束

6.2 語法

列級約束：在定義列的同時定義約束

create  table  表名 (列名  類型 約束 )

表級約束：在定義了所有列之后定義的約束

create  table  表名 constraint 約束名 約束類型  (列信息)

創建表之后添加約束：

alter table 表名 add constraint 約束名 約束類型(要約束的列名)

6.3 非空約束

作用：限定某個字段/某列的值不允許為空

兩個值：null（默認的）和not null(不為空)

列字段默認一般都是空,但是實際開發時,盡可能保證字段不為空,因為數據為空沒辦法參與運算

所有數據類型的值都可以是NULL，空字符串不等于NULL，0也不等于NULL示例：

mysql> use mydb2_stuinfo;
Database changedmysql> desc student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int unsigned | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| passwd   | char(8)      | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)# 插入數據
mysql> insert into student1(id,name) values('1002','張三'); 
Query OK, 1 row affected (0.00 sec)mysql> insert into student1(id,name) values('1003','李四'); 
Query OK, 1 row affected (0.00 sec)mysql> select * from  student1;
+------+----------+------+------+-------+-------+----------+------------+
| id   | name     | sex  | age  | score | addr  | password | birthday   |
+------+----------+------+------+-------+-------+----------+------------+
| 1000 | zhangsan | M    |   18 | 98.50 | xi'an | 12345678 | 2005-10-01 |
| 1001 | lisi     | w    |   20 | 66.79 | baoji | 87654321 | 2003-05-01 |
| 1002 | 張三      | NULL | NULL |  NULL | NULL  | NULL     | NULL       |
| 1003 | 李四      | NULL | NULL |  NULL | NULL  | NULL     | NULL       |
+------+----------+------+------+-------+-------+----------+------------+
4 rows in set (0.00 sec)# 顯示sex之后的列都為NULL，表示內容沒有填

若需要某些字段必須寫入，應該怎么處理，如：創建一個班級表，包含班級名和班級所在的教室，站在正常的業務邏輯中：如果班級沒有名字，你不知道你在哪個班級，如果教室名字可以為空，就不知道在哪上課，所以我們在設計數據庫表的時候，一定要在表中進行限制，滿足上面條件的數據就不能插入到表中，這就是“約束”

mysql> create table myclass1(class_name varchar(20) not null, class_room varchar(20) not null, class_id int);   # 無約束
Query OK, 0 rows affected (0.02 sec)mysql> desc myclass1;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(20) | NO   |     | NULL    |       |
| class_id   | int         | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)# 插入數據時：
mysql> insert into myclass1 (class_room) values('計科1');
ERROR 1364 (HY000): Field 'class_name' doesn't have a default value  # 失敗，因為name有約束不能為空mysql> insert into myclass1 (class_name,class_room) values('A104','計科1');     # 同時插入成功
Query OK, 1 row affected (0.00 sec)mysql> select * from myclass1;
+------------+------------+----------+
| class_name | class_room | class_id |
+------------+------------+----------+
| A104       | 計科1      |     NULL |
+------------+------------+----------+
1 row in set (0.00 sec)
# id字段無約束，可以為空

以上為創建表時的設置，也可以創建之后修改：

# 語法：
alter table <數據表名> change column <字段名>      <字段名> <數據類型> not null;

mysql> alter table student1 change column id id int not null;

# 也可以通過modify修改
mysql> alter table student1 modify name varchar(30) not null;# modify字句中省略not null 相當于設置為可以為空

mysql> desc  student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   |     | NULL    |       |
| name     | varchar(30)  | NO   |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int unsigned | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| passwd   | char(8)      | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

刪除 not null

mysql> alter table student1 modify id int null;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 或者
mysql> alter table student1 modify name varchar(30);mysql> desc student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | NULL    |       |
| age      | int unsigned | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| passwd   | char(8)      | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

6.4 默認值約束

作用：給某個字段/某列指定默認值，一旦設置默認值，在插入數據時，如果此字段沒有顯式賦值，則賦值為默認值。

mysql> create table teacher1(id int not null, name varchar(30) not null, sex char(2) default '女');
Query OK, 0 rows affected (0.03 sec)mysql> desc teacher1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
| sex   | char(2)     | YES  |     | 女      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> insert into teacher1(id,name) values(1001,'林雪');
Query OK, 1 row affected (0.00 sec)mysql> select * from teacher1;
+------+--------+------+
| id   | name   | sex  |
+------+--------+------+
| 1001 | 林雪   | 女   |
+------+--------+------+
1 row in set (0.00 sec)mysql> insert into teacher1(id,name,sex) values(1002,'石磊','男');                # 也可以全字段插入
Query OK, 1 row affected (0.01 sec)mysql> select * from teacher1;
+------+--------+------+
| id   | name   | sex  |
+------+--------+------+
| 1001 | 林雪   | 女   |
| 1002 | 石磊   | 男   |
+------+--------+------+
2 rows in set (0.00 sec)

# 創建表之后增加默認值：
mysql> alter table student1 modify sex char(2) default '女';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| sex      | char(2)      | YES  |     | 女      |       |
| age      | int unsigned | YES  |     | NULL    |       |
| score    | float        | YES  |     | NULL    |       |
| addr     | varchar(50)  | YES  |     | NULL    |       |
| passwd   | char(8)      | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)mysql> insert into student1 (id,name) values(1004,'王五');
Query OK, 1 row affected (0.01 sec)mysql> select * from student1;

默認值的生效：數據在插入的時候不給該字段賦值，就使用默認值
注意：若某字段都設置了not null和default約束，則按照不插入時會選擇默認值，插入時選擇插入值，所以此時not null毫無意義

mysql> alter table teacher1 add age int not null default 18;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc teacher1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
| sex   | char(2)     | YES  |     | 女      |       |
| age   | int         | NO   |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)# not null和defalut一般不需要同時出現,因為default本身有默認值,不會為空

6.5 列描述--comment

? ? 列描述沒有實際含義,專門用來描述字段,會根據表創建語句保存,供操作者來查看該列的含義，相當于C/C++的注釋

mysql> create table myclass2( class_name varchar(20) not null comment '教室',class_room  varchar(20)  default  '計科2');
Query OK, 0 rows affected (0.02 sec)mysql> desc myclass2;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(20) | YES  |     | 計科2   |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> show create table myclass2\G
*************************** 1. row ***************************Table: myclass2
Create Table: CREATE TABLE `myclass2` (`class_name` varchar(20) NOT NULL COMMENT '教室',`class_room` varchar(20) DEFAULT '計科2' COMMENT '班級'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

6.6 主鍵約束

作用：數據庫存儲數據不是唯一的目的,還要考慮到提取數據，一般要求數據庫表中的記錄要有一個特定的唯一標識，來表示唯一性,這個唯一的特定標識就是主鍵,類似于序號學號這樣的唯一標識,可以根據主鍵來唯一地篩選出一條記錄

主鍵：primary key，用來唯一的約束該字段里面的數據

特點：

主鍵字段不能為空，不能重復

一張表中最多只能有一個主鍵

主鍵所在的列通常是整數類型

主鍵約束是最頻繁的約束

注意：當創建主鍵約束時，系統默認會在所在的列或列組合上建立對應的主鍵索引（能夠根據主鍵查詢的，就根據主鍵查詢，效率更高）。如果刪除主鍵約束了，主鍵約束對應的索引就自動刪除了，需要注意的一點是，不要修改主鍵字段的值。因為主鍵是數據記錄的唯一標識，如果修改了主鍵的值，就有可能會破壞數據的完整性。

示例：創建時增加主鍵

mysql> create table t1 ( id int unsigned primary key comment '學號不能為空', name varchar(20) not null ,sex char(2) default '男');
Query OK, 0 rows affected (0.02 sec)mysql> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)  | NO   |     | NULL    |       |
| sex   | char(2)      | YES  |     | 男      |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)mysql> insert into t1 values(1,'孫文','女');
Query OK, 1 row affected (0.00 sec)mysql> select * from t1;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  1 | 孫文   | 女   |
+----+--------+------+
1 row in set (0.00 sec)mysql> insert into t1(id,name) values(2,'李文華');
Query OK, 1 row affected (0.00 sec)mysql> select * from t1;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  1 | 孫文      | 女   |
|  2 | 李文華    | 男   |
+----+-----------+------+
2 rows in set (0.00 sec)mysql> insert into t1(id,name) values(1,'黎明'); 
ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'# 再次插入出錯，因為需要保持唯一性

示例：修改表，追加主鍵

mysql> alter table teacher1 add primary key(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc teacher1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
| sex   | char(2)     | YES  |     | 女      |       |
| age   | int         | NO   |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

注意:如果該字段內有重復值,則不能以該字段新增為主鍵,必須先把重復的數據去掉然后才能添加該列為主鍵
刪除主鍵

alter table 表名 drop primary key;

mysql> alter table teacher1  drop primary key;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> desc teacher1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
| sex   | char(2)     | YES  |     | 女      |       |
| age   | int         | NO   |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

復合主鍵：在創建表的時候,在所有字段之后,使用primary key(主鍵字段列表)來創建主鍵,如果有多個字段作為主鍵,可以使用復合主鍵,這些字段合在一起是一個主鍵，也就是讓多個字段聯合在一起作為唯一標識,單個字段主鍵重復是沒有問題的,只要不是成為復合主鍵的字段一起沖突就行

mysql> create table t2 (id int, hostname varchar(10), ip varchar(20), port int unsigned, primary key (ip,port));
Query OK, 0 rows affected (0.02 sec)mysql> desc t2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| hostname | varchar(10)  | YES  |     | NULL    |       |
| ip       | varchar(20)  | NO   | PRI | NULL    |       |
| port     | int unsigned | NO   | PRI | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> insert into t2 values(1,'node1','192,168,48,2',120);
Query OK, 1 row affected (0.00 sec)mysql> select * from t2;
+------+----------+--------------+------+
| id   | hostname | ip           | port |
+------+----------+--------------+------+
|    1 | node1    | 192,168,48,2 |  120 |
+------+----------+--------------+------+
1 row in set (0.00 sec)mysql> insert into t2 values(2,'node2','192,168,48,3',120);
Query OK, 1 row affected (0.00 sec)  # 可以單個記錄重復mysql> insert into  t2  values(3,'node2','192,168,48,2',120);
ERROR 1062 (23000): Duplicate entry '192,168,48,2-120' for key 't2.PRIMARY'  # 復合主鍵一起沖突，報錯

6.7 主鍵自增長

作用：給主鍵添加自增長的數值
auto_increment：當對應的字段，不給值，會自動的被系統觸發，系統會從當前字段中已經有的最大值+1操作，得到一個新的不同的值。通常和主鍵搭配使用，作為邏輯主鍵
注意：
- 自增長字段必須是==整數==,自增長字段可以不設置初始值,默認從1開始遞增.
- 被自增長的字段必須作為主鍵或者其他具有唯一性的鍵使用,(必須保證這一列字段具有唯一性的字段)
- 自增長字段也可以插入數據,只要不與已有數據重復即可,之后新增數據會從最大值開始遞增.
- 任何一個字段要做自增長,前提是本身是一個索引（key一欄有值）.
- 一張表當中最多只能有一個自增長的列
- 約束的字段必須具備 NOT NULL 屬性
示例：

# 先增加主鍵
mysql> use mydb2_stuinfo;
Database changedmysql> alter table student1 modify id int primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0# 設置自增長
mysql> alter table student1 modify id int auto_increment;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0# 查看
mysql> desc student1;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)  | YES  |     | NULL    |                |
| sex      | char(2)      | YES  |     | 女      |                |
| age      | int unsigned | YES  |     | NULL    |                |
| score    | float        | YES  |     | NULL    |                |
| addr     | varchar(50)  | YES  |     | NULL    |                |
| passwd   | char(8)      | YES  |     | NULL    |                |
| birthday | date         | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)mysql> insert into student1(name) values('關六');
Query OK, 1 row affected (0.00 sec)mysql> select * from student1;# 發現自動增長

指定自增長的起始值：如果第一條記錄設置了該字段的初始值，那么新增加的記錄就從這個初始值開始自增。例如，如果表中插入的第一條記錄的 id 值設置為 5，那么再插入記錄時，id 值就會從 5 開始往上增加

# 指定自增長的起始值為100
mysql> create table t3 ( id int primary key auto_increment, systemname varchar(10) not null )auto_increment=100;
Query OK, 0 rows affected (0.02 sec)# 插入一條記錄
mysql> insert into t3(systemname) values('Linux');
Query OK, 1 row affected (0.00 sec)# 插入一條記錄
mysql> insert into t3(systemname) values('windows');
Query OK, 1 row affected (0.00 sec)mysql> select * from t3;
+-----+------------+
| id  | systemname |
+-----+------------+
| 100 | Linux      |
| 101 | windows    |
+-----+------------+
2 rows in set (0.01 sec)

6.8 唯一性約束

產生原因：一張表中有往往有很多字段需要唯一性,數據不能重復,但是一張表中只能有一個主鍵,唯一鍵就可以解決表中有多個字段需要唯一性約束的問題
作用：限制某個字段/某列的值不能重復
唯一鍵和主鍵的關系：
- 主鍵更多的是標識唯一性
- 唯一鍵更多的是保證在業務上,不要和別的信息出現重復
- 主鍵只能有一個,唯一鍵能設置多個
- 主鍵和唯一鍵不是對立關系而是補充關系,目的是讓數據庫創建出一個表具有非常強的約束,徹底杜絕用戶的誤插入，在一張表當中,可以有一個主鍵,也可以同時具有唯一鍵, 它們不是用來對比的,只是負責數據在不同層面的唯一性,既要保證選擇成為主鍵的屬性字段不能重復不能為空,同時也要保證本身具有唯一性的列的字段不能出現沖突
- 主鍵用來查找,唯一鍵用來保證數據的完整性
- 如：學生信息中有身份證號和學號兩個字段，可以把身份證號設置成為主鍵,而所有學生的學號也不能重復,此時就可以把學號設置成唯一鍵
示例：創建表時實現

mysql> create table t4( id int primary key, name varchar(20) unique comment '名子不能重名，可以為空' );
Query OK, 0 rows affected (0.02 sec)mysql> desc t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)# 插入數據
mysql> insert into t4  values(1,'劉文');
Query OK, 1 row affected (0.00 sec)# 唯一鍵數據重復，插入失敗
mysql> insert into t4  values(1,'劉文');
ERROR 1062 (23000): Duplicate entry '1' for key 't4.PRIMARY'
mysql> insert into t4  values(2,'張磊');
Query OK, 1 row affected (0.00 sec)mysql> select * from t4;
+----+--------+
| id | name   |
+----+--------+
|  1 | 劉文   |
|  2 | 張磊   |
+----+--------+
2 rows in set (0.00 sec)

唯一鍵沖突，自增漲字段值不連續

# 設置id列為自增長
mysql> alter table t4 modify id int auto_increment;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0# 插入一條記錄
mysql> insert into t4(name)  values('張磊磊');ysql> select * from t4;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 劉文      |
|  2 | 張磊      |
|  3 | 張磊磊    |
+----+-----------+
3 rows in set (0.00 sec)# 插入一條相同姓名記錄
mysql> insert into t4(name)  values('張磊磊');
ERROR 1062 (23000): Duplicate entry '張磊磊' for key 't4.name'# 在插入一條記錄，由于上述唯一鍵沖突，自增長會出現不連續
mysql> insert into t4(name)  values('錢明');
Query OK, 1 row affected (0.00 sec)mysql> select * from t4;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 劉文      |
|  2 | 張磊      |
|  3 | 張磊磊    |
|  5 | 錢明      |
+----+-----------+
4 rows in set (0.00 sec)

6.9 外鍵約束

作用：限定某個表的某個字段的==引用完整性==
概念
- 主表（父表）：被引用的表、被參考的表
- 從表（子表）：引用別人的表，參考別人的表
- 外鍵：從表中的某個字段引用自主表的某個字段或多個字段
- 引用鍵：主表被引用的字段
外鍵約束主要定義在從表上,主表則必須是有主鍵約束或唯一鍵約束.當定義外鍵后,要求外鍵列數據必須在主表的主鍵列存在或為null.
格式：

foreign key (從表的字段名稱) references 主表名字(主表的字段名稱)  # 建立外鍵關聯

示例：

學生表通過class_id和班級表產生關聯,在語義上,class_id稱為外鍵,此時的這個學生表就相當于從表,班級表相當于是主表, 比如我們新增一個class_id為30的學生,但是班級表中得先有id為30的班級,才能在學生表中添加, 要刪除班級表中id為20的班級,首先必須保證學生表中沒有對應班級為class_id =20的學生.,當然我們也可以新增一個class_id為NULL的學生,表示該學生還沒有分配班級, 外鍵可以為空！
注意：
- 主表必須已經存在于數據庫中，或者是當前正在創建的表。如果是后一種情況，則主表與從表是同一個表，這樣的表稱為自參照表，這種結構稱為自參照完整性。
- 必須為主表定義主鍵。
- 主鍵不能包含空值，但允許在外鍵中出現空值。也就是說，只要外鍵的每個非空值出現在指定的主鍵中，這個外鍵的內容就是正確的。
- 在主表的表名后面指定列名或列名的組合。這個列或列的組合必須是主表的主鍵或候選鍵。
- 外鍵中列的數目必須和主表的主鍵中列的數目相同。
- 外鍵中列的數據類型必須和主表主鍵中對應列的數據類型相同。
定義從表的時候,設置外鍵其實就是設置了一個關系,在從表當中插入數據的時候,會對插入數據進行校驗,校驗插入的數據是否存在于主表字段當中,外鍵就是增加了表和表之間的約束關系.
示例：

# 定義主表
mysql> create table myclass3(id int primary key, name varchar(20) not null comment '班級名');
Query OK, 0 rows affected (0.02 sec)mysql> desc myclass3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 定義從表：
mysql> create table  myclass3_stu( id int primary key, name varchar(30) not null comment '學生名', class_id int, foreign key (class_id) references myclass3(id) );
Query OK, 0 rows affected (0.02 sec)mysql> desc myclass3_stu;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | NO   |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 主表中插入數據
mysql> insert into myclass3 values(10,'C++'),(20,'Java');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from myclass3;
+----+------+
| id | name |
+----+------+
| 10 | C++  |
| 20 | Java |
+----+------+
2 rows in set (0.00 sec)

# 從表中正常插入數據：
mysql> insert into myclass3_stu values(1,'Li',10),(2,'Sun',20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from myclass3_stu;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | Li   |       10 |
|  2 | Sun  |       20 |
+----+------+----------+
2 rows in set (0.00 sec)

# 從表中插入沒有班級的記錄，受外鍵控制，報錯
mysql> insert into myclass3_stu values(3,'wang',30);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`stu_info`.`myclass3_stu`, CONSTRAINT `myclass3_stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `myclass3` (`id`))

# 從表中，收到外鍵控制，可以使用null替代，表示沒有分配班級
mysql> insert into myclass3_stu values(3,'wang',null);
Query OK, 1 row affected (0.00 sec)mysql> select * from myclass3_stu;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | Li   |       10 |
|  2 | Sun  |       20 |
|  3 | wang |     NULL |
+----+------+----------+
3 rows in set (0.00 sec)

6.10 檢查約束

作用：檢查約束（check）是用來檢查數據表中字段值有效性的一種手段，一般用于設置值的范圍
注意
- 設置檢查約束時要根據實際情況進行設置，這樣能夠減少無效數據的輸入
- 在默認值和非空約束可看作是特殊的檢查約束
- 注意檢查約束在8.0.16之前，MySQL默認但不會強制的遵循check約束（寫不報錯，但是不生效，需要通觸發器完成），之后就開始正式支持這個約束了
示例1：創建表時設置檢查約束

mysql> create table t5(id int primary key, name varchar(20), salary float, check(salary>0 and salary<10000) );
Query OK, 0 rows affected (0.02 sec)mysql> desc t5;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into t5 values(1,'Li',5984);
Query OK, 1 row affected (0.00 sec)mysql> insert into t5 values(2,'wang',12000);
ERROR 3819 (HY000): Check constraint 't5_chk_1' is violated.

示例2：修改表時添加檢查約束

# 格式：
alter table 表名 add constraint <檢查約束名> check(檢查約束)

mysql> alter table t5 add constraint check_id check(id>0);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

6.11 刪除表的約束

刪除not null約束alter table 表名 modify 列名 類型;刪除unique約束alter table 表名 drop index 惟一約束名;刪除primary key約束alter table 表名 drop primary key;刪除foreign key約束alter table 表名 drop foreign key 外鍵名;

6.12 存儲引擎

數據庫存儲引擎是數據庫底層軟件組織，數據庫管理系統（DBMS）使用數據引擎進行創建、查詢、更新和刪除數據，不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能。現在許多不同的數據庫管理系統都支持多種不同的數據引擎。MySQL的核心就是存儲引擎。
插件式存儲引擎是MySQL數據庫最重要的特性之一，用戶可以根據應用的需要選擇如何存儲和索引數據、是否使用事務等，如：
- MyISAM：
  - Mysql 5.5之前的默認數據庫引擎，最為常用。
  - 擁有較高的插入，查詢速度，但不支持事務應用于以讀寫操作為主，很少更新、刪除，并對事務的完整性、并發性要求不高的情況
- InnoDB:
  - 事務型速記的首選引擎，支持ACID事務，支持行級鎖定
  - 應用于對事務的完整性要求高,在并發條件下要求數據的一致性的情況。
  - InnoDB:是MySQL的默認數據庫引擎
- MEMORY：
  - 所有數據置于內存的存儲引擎，擁有極高的插入，更新和查詢效率。
  - 但是會占用和數據量成正比的內存空間。并且其內容會在MYSQL重新啟動是會丟失。
- Archive ：
  - 非常適合存儲大量的獨立的，作為歷史記錄的數據。
  - 因為它們不經常被讀取，則擁有高效的插入速度，但其對查詢的支持相對較差
- Federated ：將不同的 MySQL 服務器聯合起來，邏輯上組成一個完整的數據庫。非常適合分布式應用
- CSV ：
  - 邏輯上由逗號分割數據的存儲引擎。
  - 它會在數據庫子目錄里為每個數據表創建一個 .csv 文件。
  - 這是一種普通文本文件，每個數據行占用一個文本行。
  - CSV 存儲引擎不支持索引。
- BlackHole：黑洞引擎，寫入的任何數據都會消失，一般用于記錄 binlog 做復制的中繼
- ERFORMANCE_SCHEMA：該引擎主要用于收集數據庫服務器性能參數。

默認情況下，創建表不指定表的存儲引擎，則會使用配置文件的my.ini中default-storage-engine=InnoDB指定的InnoDB
查看支持的引擎命令：

show engines \g

查看當前默認存儲引擎

mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+

在創建表時，可以指定表的存儲引擎:

6.13 總結-完整的建表語句

create table 表名（列名 列類型 [auto_increment] [default 默認值][列約束]...[表約束]) [engine=表類型] [default] charset=字符集;列類型： 該列的數據的存儲類型
auto_increment: 自動增尙只能是數值類型的列
defaul 默認值： 設置該列的默認值
約束： 對列的一些限制
engine: 表類型， 也叫表的存儲引擎
charset： 設置表的字符篥

6.14 練習

創建銷售數據庫mydb5_sales

使用該數據庫

創建產品表pro_tb (產品編號pro_num int(10) 唯一鍵，產品名pro_name varchar(30) ，產品單價pro_price float(5,2) , 產品規格pro_spe varchar（8）默認值：件)

創建訂單表orders (訂單號id int(6) ,客戶號customer_id int(4),產品編號pro_num int(10) 外鍵 ,簽訂日期signing_date date,金額amount float(5,2) ) , 其中訂號單為主鍵、自增長，金額設置check（大于0）

mysql> create database mydb5_sales;
Query OK, 1 row affected (0.01 sec)mysql> use mydb5_sales;
Database changedmysql> create table pro_tb(pro_num int(10) unique, pro_name varchar(30) not null, pro_price float(5,2) not null, pro_spe varchar(8) default '件' );
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> desc  pro_tb;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| pro_num   | int         | YES  | UNI | NULL    |       |
| pro_name  | varchar(30) | NO   |     | NULL    |       |
| pro_price | float(5,2)  | NO   |     | NULL    |       |
| pro_spe   | varchar(8)  | YES  |     | 件      |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)mysql> create table orders(id int(6) primary key  auto_increment, customer_id int(4) not null, pro_num int(10) not null, signing_date date, amount float(5,2), check(amount>0), foreign key(pro_num) references pro_tb(pro_num));
Query OK, 0 rows affected, 4 warnings (0.02 sec)mysql> desc  orders;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int        | NO   | PRI | NULL    | auto_increment |
| customer_id  | int        | NO   |     | NULL    |                |
| pro_num      | int        | NO   |     | NULL    |                |
| signing_date | date       | YES  |     | NULL    |                |
| amount       | float(5,2) | YES  |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

7.MySQL庫表設計：范式

7.1 概念

7.2 第一范式（1NF）

原則：庫表設計時為了確保原子性，其存儲數據具備不可再分性,例：

在上述的學生表中，其中有一個student學生列，這一列存儲的數據則明顯不符合第一范式：原子性的規定，因為這一列的數據還可以再拆分為姓名、性別、身高三項數據，因此為了符合第一范式，應該將表結構更改為：

如果不去拆分列滿足第一范式，會造成什么影響?
- 客戶端語言和表之間無法很好的生成映射關系。
- 查詢到數據后，需要處理數據時，還需要對student字段進行額外拆分。
- 插入數據時，對于第一個字段的值還需要先拼裝后才能進行寫入。

7.3 第二范式（2NF）

原則：表中的所有列，其數據都必須依賴于主鍵，也就是一張表只存儲同一類型的數據，不能有任何一列數據與主鍵沒有關系，例：

雖然此時已經滿足了數據庫的第一范式，但此刻觀察course課程、score分數這兩列數據，跟前面的幾列數據實際上依賴關系并不大，同時也由于這樣的結構，導致前面幾列的數據出現了大量冗余，所以此時可以再次拆分一下表結構：

經過上述結構優化后，之前的一張表被拆分成學生表、課程表、成績表三張，每張表中的id字段作為主鍵，其他字段都依賴這個主鍵。無論在那張表中，都可以通過id主鍵確定其他字段的信息，每張表的業務屬性都具備“唯一性”，也就是每張表都只會描述了“一件事情”，不會存在一張表中會出現兩個業務屬性。

7.4 第三范式（3NF）

原則：表中每一列數據不能與主鍵之外的字段有直接關系，例：

比如這張學生表，目前即符合第一范式，也符合第二范式，但看最后的兩個字段，department表示當前學生所屬的院校，dean則表示這個院系的院長是誰。一般來說，一個學生的院長是誰，首先是取決于學生所在的院系的，因此最后的dean字段明顯與department字段存在依賴關系，因此需要進一步調整表結構。

經過進一步的結構優化后，又將原本的學生表拆為了院系表、學生表兩張，學生表中則是只存儲一個院系ID，由院系表存儲院系相關的所有數據。至此，學生表中的每個非主鍵字段與其他非主鍵字段之間，都是相互獨立的，之間不會再存在任何依賴性，所有的字段都依賴于主鍵。
為什么要這樣調整？不調整會發生什么問題：
- 當一個院系的院長換人后，需要同時修改學生表中的多條數據。
- 當一個院長離職后，需要刪除該院長的記錄，會同時刪除多條學生信息。
- ......
- 如果設計的表結構，無法滿足第三范式，在操作表時就會出現異常，使得整個表較難維護。

7.5 三范式小結

范式小結
- 1NF：確保原子性，表中每一個列數據都必須是不可再分的字段。
- 2NF：確保唯一性，每張表都只描述一種業務屬性，一張表只描述一件事。
- 3NF：確保獨立性，表中除主鍵外，每個字段之間不存在任何依賴，都是獨立的。
沒有按照范式設計表時，會存在幾個問題
- 整張表數據比較冗余，同一個學生信息會出現多條。
- 表結構特別臃腫，不易于操作，要新增一個學生信息時，需添加大量數據。
- 需要更新其他業務屬性的數據時，比如院系院長換人了，需要修改所有學生的記錄。
經過三范式的設計優化后，整個庫中的所有表結構，會顯得更為優雅，靈活性也會更強。

7.6?巴斯-科德范式（BCNF）

概念：
- 前題：一般在一張表中，可以用于區分每行數據的一個列，通常會被咱們設為主鍵，例如常用的ID字段就是如此，這類主鍵通常被稱為單一主鍵，即一個列組成的主鍵。但除此之外，還有一個聯合主鍵的概念，也就是由多個列組成的主鍵
- 巴斯-科德范式也被稱為3.5NF，是第三范式的補充版
- 第三范式的要求是：任何非主鍵字段不能與其他非主鍵字段間存在依賴關系，也就是要求每個非主鍵字段之間要具備獨立性。而巴斯-科德范式在第三范式的基礎上，進一步要求：任何主屬性不能對其他主鍵子集存在依賴。
- 大白話：規定了聯合主鍵中的某列值，不能與聯合主鍵中的其他列存在依賴關系

例：

分析
- 這張學生表，此時假設以classes班級字段、class_adviser班主任字段、name學生姓名字段，組合成一個聯合主鍵，在這里我們可以通過聯合主鍵，確定學生表中任何一個學生的信息，比如：熊竹老師管的計算機-2201班，哪個竹子同學有多高啊？可以通過上述的聯合主鍵精準定位到表中第一條數據，并且最終能夠給出答案為185cm。
- 出現問題：在這張表中，一條學生信息中的班主任，取決于學生所在的班級，比如「竹子同學、子竹同學」在「計算機-2201班」，所以它們的班主任都是「熊竹老師」，因此班主任字段其實也依賴于班級字段。那會造成什么問題呢？
  - 當一個班級的班主任老師換人后，需要同時修改學生表中的多條數據。
  - 當一個班主任老師離職后，需要刪除該老師的記錄，會同時刪除多條學生信息。
  - 想要增加一個班級時，同時必須添加學生姓名數據，因為主鍵不允許為空。
通過上述分析可以明顯得知，如果聯合主鍵中的一個字段依賴于另一個字段，同樣也會造成不小的問題，使得整張表的維護性變差，因此這里需要進一步調整結構：

經過結構調整后：
- 原本的學生表則又被拆為了班級表、學生表兩張，在學生表中只存儲班級ID，然后使用classes_id班級ID和name學生姓名兩個字段作為聯合主鍵。
- 之前的三個問題也不存在，如換班主任后只需要更改班級表，無需修改學生表中的學生信息；增加班級時，只需要在班級表中新增數據，也不會影響學生表。
小結：第三范式只要求非主鍵字段之間，不能存在依賴關系，但沒要求聯合主鍵中的字段不能存在依賴，因此第三范式并未考慮完善，巴斯-科德范式修正的就是這點，是對第三范式的補充及完善，修正了第三范式。

7.7 第四范式（4NF）

多值依賴：表中的字段之間存在一對多的關系，也就是一個字段的具體值會由多個字段來決定（一個表中至少需要有三個獨立的字段才會出現多值依賴問題）
示例：

經典的業務，用戶角色權限表，各字段含義：

user_name字段 -- 用戶名

role字段 -- 角色信息：

USER：普通用戶角色。

ADMIN：管理員角色。

ROOT：超級管理員角色。

permission字段 -- 權限信息：

*：超級管理員擁有的權限級別，*表示所有。

BACKSTAGE：管理員擁有的權限級別，表示可以操作后臺。

LOGIN：普通用戶擁有的權限級別，表示可以登錄訪問平臺。

此時假設我們需要新增一條數據，那表中的權限字段究竟填什么？這個值是需要依賴多個字段決定的，權限來自于角色，而角色則來自于用戶。也就是說，一個用戶可以擁有多個角色，同時一個角色可以擁有多個權限，所以此時咱們無法單獨根據用戶名去確定權限值，權限值必須依賴用戶、角色兩個字段來決定，這種一個字段的值取決于多個字段才能確定的情況，就被稱為多值依賴。
因此第四范式的定義就是要消除表中的多值依賴關系，上述表格拆分為：

觀察上述的五張表正是大名鼎鼎的權限五表，將原本的用戶角色權限表，拆分成了用戶表、角色表、權限表、用戶角色關系表、角色權限關系表。
經過這次拆分之后，一方面用戶表、角色表、權限表中都不會有數據冗余，第二方面無論是要刪除亦或新增一個角色、權限時，都不會影響其他表。后面的兩張關系表，主要是為了維護用戶、角色、權限三者之間的關系。

7.8?第五范式（5NF）/完美范式

定義：建立在4NF的基礎上，進一步消除表中的連接依賴，直到表中的連接依賴都是主鍵所蘊含的
第五范式解決的是無損連接問題，基本沒有實際意義，了解即可，因為無損連接很少出現，而且難以察覺

7.9?第六范式（6NF)/域鍵范式

域鍵范式，也被稱之為終極范式，但目前也僅有學術機構在研究，在生產環境中實際的用途也不大

7.10 反范式

概念：不遵循數據庫范式設計的結構，就被稱為反范式結構。
遵循數據庫范式設計優點如下：
- 避免了大量的數據冗余
- 節省了大量存儲空間
- 表整體結構更為優雅，能讓SQL操作更加便捷且減少出錯。
但隨著范式的級別越高，設計出的結構會更加精細化，原本一張表的數據會被分攤到多張表中存儲，表的數量隨之越來越多。會存在一個致命問題，也就是當同時需要這些數據時，只能采用聯表查詢的形式檢索數據，有時候甚至為了一個字段的數據，也需要做一次連表查詢才能獲得。這其中的開銷無疑是花費巨大的，尤其是當連接的表不僅兩三張而是很多張時，有可能還會造成索引失效，這種情況帶來的資源、時間開銷簡直是一個噩夢，這會嚴重地影響整個業務系統的性能。
因此，也正是由于上述一些問題，在設計庫表結構時，我們不一定要100%遵守范式準則。這種違反數據庫范式的設計方法，就被稱之為 反范式設計。
設計原則：無論那種范式只要能夠對業務有利，那就可以稱之為好的設計方案。在設計時千萬不要拘泥于規則之內，一定要結合實際業務考慮，遵循業務優先的原則去設計結構。
注意：不是所有不遵循數據庫范式的結構設計都被稱為反范式，反范式設計是指自己知道會破壞范式，但對業務帶來好處大于壞處時，刻意設計出破壞范式的結構。

7.11 數據庫范式設計總結

經過一系列的闡述后，其實不難發現，越到后面的范式，越難令人理解，同時為了讓表滿足更高級別的范式，越往后付出代價也越大，而且拆分出的表數量也會越多
一般項目中僅需滿足到第三范式或BC范式即可，因為這個度剛剛好，再往后就會因為過于精細化設計，導致整體性能反而下降。
控制到第三范式的級別，一方面數據不會有太多冗余，第二方面也不會對性能影響過大。同時，如若打破范式的設定能對業務更有利，那也可以違背范式原則去設計。
生產項目中庫表結構設計的是否合理，區別如下：
- 不合理的結構設計會造成的問題：
  - 數據冗余，會浪費一定程度上的存儲空間
  - 不便于常規SQL操作（例如插入、刪除），甚至會出現異常
- 合理的結構設計帶來的好處：
  - 節省空間，SQL執行時能節省內存空間，數據存儲時能節省磁盤空間
  - 數據劃分較為合理，DB性能整體較高，并且數據也非常完整
  - 結構便于維護和進行常規SQL操作
各范式之間的遞進關系圖：

范式概念：
- 第一范式：原子性，每個字段的值不能再分。
- 第二范式：唯一性，表內每行數據必須描述同一業務屬性的數據。
- 第三范式：獨立性，表中每個非主鍵字段之間不能存在依賴性。
- 巴斯范式：主鍵字段獨立性，聯合主鍵字段之間不能存在依賴性。
- 第四范式：表中字段不能存在多值依賴關系。
- 第五范式：表中字段的數據之間不能存在連接依賴關系。
- 域鍵范式：試圖研究出一個庫表設計時的終極完美范式。