theme: github highlight: an-old-hope

MySQL創建和管理表

參考鏈接：#MySQL數據庫（mysql安裝/基礎/高級/優化）

1. 基礎知識

1.1 一條數據存儲的過程

存儲數據是處理數據的第一步。只有正確地把數據存儲起來，我們才能進行有效的處理和分析。否則，只能是一團亂麻，無從下手。

那麼，怎樣才能把用户各種經營相關的、紛繁複雜的數據，有序、高效地存儲起來呢？在 MySQL 中，一個完整的數據存儲過程總共有 4 步，分別是創建數據庫、確認字段、創建數據表、插入數據。

我們要先創建一個數據庫，而不是直接創建數據表呢？

因為從系統架構的層次上看，MySQL 數據庫系統從大到小依次是數據庫服務器、數據庫、數據表、數據表的行與列。

MySQL 數據庫服務器之前已經安裝。所以，我們就從創建數據庫開始。

1.2 標識符命名規則

數據庫名、表名不得超過30個字符，變量名限制為29個
必須只能包含 A–Z, a–z, 0–9, _共63個字符
數據庫名、表名、字段名等對象名中間不要包含空格
同一個MySQL軟件中，數據庫不能同名；同一個庫中，表不能重名；同一個表中，字段不能重名
必須保證你的字段沒有和保留字、數據庫系統或常用方法衝突。如果堅持使用，請在SQL語句中使用`（着重號）引起來
保持字段名和類型的一致性：在命名字段併為其指定數據類型的時候一定要保證一致性，假如數據類型在一個表裏是整數，那在另一個表裏可就別變成字符型了

1.3 MySQL中的數據類型

| 類型 | 類型舉例 | | ---------------- | ------------------------------------------------------------ | | 整數類型 | TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT | | 浮點類型 | FLOAT、DOUBLE | | 定點數類型 | DECIMAL | | 位類型 | BIT | | 日期時間類型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP | | 文本字符串類型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT | | 枚舉類型 | ENUM | | 集合類型 | SET | | 二進制字符串類型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB | | JSON類型 | JSON對象、JSON數組 | | 空間數據類型 | 單值：GEOMETRY、POINT、LINESTRING、POLYGON；
集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION |

其中，常用的幾類類型介紹如下：

| 數據類型 | 描述 | | ------------- | ------------------------------------------------------------ | | INT | 從-2^31到2^31-1的整型數據。存儲大小為 4個字節 | | CHAR(size) | 定長字符數據。若未指定，默認為1個字符，最大長度255 | | VARCHAR(size) | 可變長字符數據，根據字符串實際長度保存，必須指定長度 | | FLOAT(M,D) | 單精度，佔用4個字節，M=整數位+小數位，D=小數位。 D<=M<=255,0<=D<=30，默認M+D<=6 | | DOUBLE(M,D) | 雙精度，佔用8個字節，D<=M<=255,0<=D<=30，默認M+D<=15 | | DECIMAL(M,D) | 高精度小數，佔用M+2個字節，D<=M<=65，0<=D<=30，最大取值範圍與DOUBLE相同。 | | DATE | 日期型數據，格式'YYYY-MM-DD' | | BLOB | 二進制形式的長文本數據，最大可達4G | | TEXT | 長文本數據，最大可達4G |

2. 創建和管理數據庫

2.1 創建數據庫

方式1：創建數據庫

mysql CREATE DATABASE 數據庫名;

方式2：創建數據庫並指定字符集

mysql CREATE DATABASE 數據庫名 CHARACTER SET 字符集; #不指定就是使用默認的字符集

方式3：判斷數據庫是否已經存在，不存在則創建數據庫（推薦）

mysql CREATE DATABASE IF NOT EXISTS 數據庫名;

如果MySQL中已經存在相關的數據庫，則忽略創建語句，不再創建數據庫。

注意：DATABASE 不能改名。一些可視化工具可以改名，它是建新庫，把所有表複製到新庫，再刪舊庫完成的。

2.2 使用數據庫

查看當前所有的數據庫

mysql SHOW DATABASES; #有一個S，代表多個數據庫

查看當前正在使用的數據庫

mysql SELECT DATABASE(); #使用的一個 mysql 中的全局函數

查看指定庫下所有的表

mysql SHOW TABLES FROM 數據庫名;

查看數據庫的創建信息

mysql SHOW CREATE DATABASE 數據庫名; 或者： SHOW CREATE DATABASE 數據庫名\G

使用/切換數據庫

mysql USE 數據庫名;

注意：要操作表格和數據之前必須先説明是對哪個數據庫進行操作，否則就要對所有對象加上“數據庫名.”。

插卡指定數據庫下保存的數據表

sql SHOW TABLES FROM mysql;

2.3 修改數據庫

更改數據庫字符集

mysql ALTER DATABASE 數據庫名 CHARACTER SET 字符集; #比如：gbk、utf8等

更改數據庫名字(一般不會修改名字)

sql RENAME DATABASE 數據庫名 TO 新庫名;

2.4 刪除數據庫

方式1：刪除指定的數據庫

mysql DROP DATABASE 數據庫名;

方式2：刪除指定的數據庫（推薦）

mysql DROP DATABASE IF EXISTS 數據庫名;

3. 創建表

3.1 創建方式1

必須具備：
CREATE TABLE權限
存儲空間
語法格式：

mysql CREATE TABLE [IF NOT EXISTS] 表名( 字段1, 數據類型(長度) [約束條件] [默認值], 字段2, 數據類型(長度) [約束條件] [默認值], 字段3, 數據類型(長度) [約束條件] [默認值], …… [表約束條件] );

加上了IF NOT EXISTS關鍵字，則表示：如果當前數據庫中不存在要創建的數據表，則創建數據表；如果當前數據庫中已經存在要創建的數據表，則忽略建表語句，不再創建數據表。

必須指定：
表名
列名(或字段名)，數據類型，長度(varchar指定的)
可選指定：
約束條件
默認值
創建表舉例1：

mysql -- 創建表 CREATE TABLE emp ( -- int類型 emp_id INT, #int保存-2147483648~2147483647(有符號數)這個範圍內的數據 -- 最多保存20箇中英文字符 emp_name VARCHAR(20), -- 總位數不超過15位 salary DOUBLE, -- 日期類型 birthday DATE );

mysql DESC emp;

MySQL在執行建表語句時，將id字段的類型設置為int(11)，這裏的11實際上是int類型指定的顯示寬度，默認的顯示寬度為11。也可以在創建數據表的時候指定數據的顯示寬度。

創建表舉例2：

mysql CREATE TABLE dept( -- int類型，自增 deptno INT(2) AUTO_INCREMENT, dname VARCHAR(14), loc VARCHAR(13), -- 主鍵 PRIMARY KEY (deptno) );

mysql DESCRIBE dept;

在MySQL 8.x版本中，不再推薦為INT類型指定顯示長度，並在未來的版本中可能去掉這樣的語法。

3.2 創建方式2

基於現有的表創建一個表，相當於表的複製

使用 AS subquery 選項，將創建表和插入數據結合起來

指定的列和子查詢中的列要一一對應
通過列名和默認值定義列

mysql CREATE TABLE emp1 AS SELECT * FROM employees; CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 創建的emp2是空表，僅僅複製表的結構

mysql CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date，#可以使用一個別名 FROM employees WHERE department_id = 80;

mysql DESCRIBE dept80;

3.3 查看數據表結構

在MySQL中創建好數據表之後，可以查看數據表的結構。MySQL支持使用DESCRIBE/DESC語句查看數據表結構，也支持使用SHOW CREATE TABLE語句查看數據表結構。

語法格式如下：

mysql SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE語句不僅可以查看錶創建時的詳細語句，還可以查看存儲引擎和字符編碼。如果字段沒有設置字符集，就使用創建表時候的字符集，如果表也沒有設置字符集，就使用創建數據庫時候的字符集，如果數據庫也沒有設置，則使用My.ini配置文件中設置的字符集

4. 修改表

修改表指的是修改數據庫中已經存在的數據表的結構。

使用 ALTER TABLE 語句可以實現：

向已有的表中添加列
修改現有表中的列
刪除現有表中的列
重命名現有表中的列

語法：

sql alter table 表名 add|drop|modify|change column 列名【列類型約束】;

4.1 添加一個列(字段)

語法格式如下：

mysql ALTER TABLE 表名 ADD 【COLUMN】字段名字段類型【FIRST|AFTER 字段名】;

舉例：

mysql ALTER TABLE dept80 ADD job_id varchar(15);

4.2 修改一個列

可以修改列的數據類型，長度、默認值和位置
修改字段數據類型、長度、默認值、位置的語法格式如下：

mysql ALTER TABLE 表名 MODIFY 【COLUMN】字段名1 字段類型【DEFAULT 默認值】【FIRST|AFTER 字段名2】;

舉例：

mysql ALTER TABLE dept80 MODIFY last_name VARCHAR(30);

mysql ALTER TABLE dept80 MODIFY salary double(9,2) default 1000;

對默認值的修改隻影響今後對錶的修改
此外，還可以通過此種方式修改列的約束。這裏暫先不講。

4.3 重命名一個列

使用 CHANGE old_column new_column dataType子句重命名列。語法格式如下：

mysql ALTER TABLE 表名 CHANGE 【column】列名新列名新數據類型;

舉例：

mysql ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);

4.4 刪除一個列

刪除表中某個字段的語法格式如下：

mysql ALTER TABLE 表名 DROP 【COLUMN】字段名

舉例：

mysql ALTER TABLE dept80 DROP COLUMN job_id;

5. 重命名錶

方式一：使用RENAME

mysql RENAME TABLE emp TO myemp;

方式二：

mysql ALTER table dept RENAME [TO] detail_dept; -- [TO]可以省略

必須是對象的擁有者

6. 刪除表

在MySQL中，當一張數據表沒有與其他任何數據表形成關聯關係時，可以將當前數據表直接刪除。
數據和結構都被刪除，釋放表空間
所有正在運行的相關事務被提交
所有相關索引被刪除
語法格式：

mysql DROP TABLE [IF EXISTS] 數據表1 [, 數據表2, …, 數據表n];

IF EXISTS的含義為：如果當前數據庫中存在相應的數據表，則刪除數據表；如果當前數據庫中不存在相應的數據表，則忽略刪除語句，不再執行刪除數據表的操作。

舉例：

mysql DROP TABLE dept80;

DROP TABLE 語句不能回滾(即刪除了這個表之後我想撤銷這個操作，是不能實現的，run)

7. 清空表

TRUNCATE TABLE語句：
刪除表中所有的數據，但是表的結構保留
釋放表的存儲空間
舉例：

mysql TRUNCATE TABLE detail_dept;

TRUNCATE語句不能回滾，而使用 DELETE 語句刪除數據，可以回滾
對比：

```mysql

參見第8.2

SET autocommit = FALSE; DELETE FROM emp2;

TRUNCATE TABLE emp2;

SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2; ```

阿里開發規範：

【參考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日誌資源少，但 TRUNCATE 無事務且不觸發 TRIGGER，有可能造成事故，故不建議在開發代碼中使用此語句。

説明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同。

8、COMMIT和ROLLBACK

commit：提交數據。一旦執行commit，則數據就被永久的保存在了數據庫中，意味着數據不可以回滾

rollback：回滾數據，一旦執行rollback，則可以實現數據的回滾，回滾到最近的一次COMMIT之後。【參見事務一節】

8.1、對比TRUNCATE TABLE 和 DELETE FROM

相同點：都可以實現對錶中所有數據的刪除，同時保留表結構
不同點：
- TRUNCATE TABLE：一旦執行此操作表數據全部清除，數據是不可以回滾的
- DELETE FROM：一旦執行此操作表數據可以全部清除(不帶where)，數據可以回滾

8.2、DDL和DML的説明

DDL的操作(比如TRUNCATE )一旦執行，就不可回滾，set autocommit=false 對DDL失效，因為在執行完DDL操作之後，一定會自動執行一次commit，而此commit操作不受set autocommit=false影響

DML的操作(insert/update/delete)默認情況，一旦執行，也是不可回滾，但是在執行DML之前，執行了set autocommit=false，(默認情況下為true，即自動提交)則執行的DML操作就可以實現回滾

演示：

```sql

1、演示：DELETE FROM,以myemp3表為例

COMMIT; #先執行一次commit，將數據全部提交 SELECT * FROM myemp3;
SET autocommit=FALSE; #執行不自動提交
DELETE FROM myemp3; #刪除表中數據 SELECT * FROM myemp3; ROLLBACK; #執行回滾，數據重新恢復，相當於撤銷了上一步delete myemp3的操作 SELECT * FROM myemp3;

2、演示：TRUNCATE TABLE

COMMIT; SELECT * FROM myemp3; SET autocommit=FALSE;
TRUNCATE TABLE myemp3; #清空表 SELECT * FROM myemp3; ROLLBACK; #執行回滾，但是數據還是沒有恢復 SELECT * FROM myemp3; ```

9. 表的複製【同3.2】

sql INSERT INTO author VALUES (1,'村上春樹','日本'), (2,'莫言','中國'), (3,'馮唐','中國'), (4,'金庸','中國');

#1.僅僅複製表的結構

sql CREATE TABLE copy LIKE author; #空表 CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 創建的emp2是空表，僅僅複製表的結構

#2.複製表的結構+數據

sql CREATE TABLE copy2 //省略了AS SELECT * FROM author;

#3.只複製部分數據

sql CREATE TABLE copy3 SELECT id,au_name FROM author WHERE nation='中國';

#4.僅僅複製某些字段

sql CREATE TABLE copy4 SELECT id,au_name FROM author WHERE 0;

10. 內容拓展

拓展1：阿里巴巴《Java開發手冊》之MySQL字段命名

【強制】表名、字段名必須使用小寫字母或數字，禁止出現數字開頭，禁止兩個下劃線中間只出現數字。數據庫字段名的修改代價很大，因為無法進行預發佈，所以字段名稱需要慎重考慮。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
【強制】禁用保留字，如 desc、range、match、delayed 等，請參考 MySQL 官方保留字。
【強制】表必備三字段：id, gmt_create, gmt_modified。
説明：其中 id 必為主鍵，類型為BIGINT UNSIGNED、單表時自增、步長為 1。gmt_create, gmt_modified 的類型均為 DATETIME 類型，前者現在時表示主動式創建，後者過去分詞表示被動式更新
【推薦】表的命名最好是遵循 “業務名稱_表的作用”。
正例：alipay_task 、 force_project、 trade_config
【推薦】庫名與應用名稱儘量一致。
【參考】合適的字符存儲長度，不但節約數據庫表空間、節約索引存儲，更重要的是提升檢索速度。
正例：無符號值可以避免誤存負數，且擴大了表示範圍。

拓展2：如何理解清空表、刪除表等操作需謹慎？！

表刪除操作將把表的定義和表中的數據一起刪除，並且MySQL在執行刪除操作時，不會有任何的確認信息提示，因此執行刪除操時應當慎重。在刪除表前，最好對錶中的數據進行備份，這樣當操作失誤時可以對數據進行恢復，以免造成無法挽回的後果。

同樣的，在使用 ALTER TABLE 進行表的基本修改操作時，在執行操作過程之前，也應該確保對數據進行完整的備份，因為數據庫的改變是無法撤銷的，如果添加了一個不需要的字段，可以將其刪除；相同的，如果刪除了一個需要的列，該列下面的所有數據都將會丟失。

拓展3：MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事務完整性，即DDL操作要麼成功要麼回滾。DDL操作回滾日誌寫入到data dictionary數據字典表mysql.innodb_ddl_log（該表是隱藏的表，通過show tables無法看到）中，用於回滾操作。通過設置參數，可將DDL操作日誌打印輸出到MySQL錯誤日誌中。

分別在MySQL 5.7版本和MySQL 8.0版本中創建數據庫和數據表，結果如下：

```mysql CREATE DATABASE mytest;

USE mytest;

CREATE TABLE book1( book_id INT , book_name VARCHAR(255) );

SHOW TABLES; ```

（1）在MySQL 5.7版本中，測試步驟如下：刪除數據表book1和數據表book2，結果如下：

mysql mysql> DROP TABLE book1,book2; ERROR 1051 (42S02): Unknown table 'mytest.book2' #book2不存在

再次查詢數據庫中的數據表名稱，結果如下：

mysql mysql> SHOW TABLES; Empty set (0.00 sec)

從結果可以看出，雖然刪除表這個DDL操作時報錯了，但是仍然刪除了數據表book1(從上至下，Book1先刪除了)。

（2）在MySQL 8.0版本中，測試步驟如下：刪除數據表book1和數據表book2，結果如下：

mysql mysql> DROP TABLE book1,book2; ERROR 1051 (42S02): Unknown table 'mytest.book2'