1. 基礎(chǔ)知識

1.1 一條數(shù)據(jù)存儲的過程

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

那么，怎樣才能把用戶各種經(jīng)營相關(guān)的、紛繁復(fù)雜的數(shù)據(jù)，有序、高效地存儲起來呢？ 在 MySQL 中，一個完整的數(shù)據(jù)存儲過程總共有 4 步，分別是創(chuàng)建數(shù)據(jù)庫、確認字段、創(chuàng)建數(shù)據(jù)表、插入數(shù)據(jù)。

我們要先創(chuàng)建一個數(shù)據(jù)庫，而不是直接創(chuàng)建數(shù)據(jù)表呢？

因為從系統(tǒng)架構(gòu)的層次上看，MySQL 數(shù)據(jù)庫系統(tǒng)從大到小依次是數(shù)據(jù)庫服務(wù)器、數(shù)據(jù)庫、數(shù)據(jù)表、數(shù)據(jù)表的行與列。

MySQL 數(shù)據(jù)庫服務(wù)器之前已經(jīng)安裝。所以，我們就從創(chuàng)建數(shù)據(jù)庫開始。

1.2 標識符命名規(guī)則

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

1.3 MySQL中的數(shù)據(jù)類型

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

2. 創(chuàng)建和管理數(shù)據(jù)庫

2.1 創(chuàng)建數(shù)據(jù)庫

• 方式1：創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE 數(shù)據(jù)庫名; 

• 方式2：創(chuàng)建數(shù)據(jù)庫并指定字符集

CREATE DATABASE 數(shù)據(jù)庫名 CHARACTER SET 字符集;

• 方式3：判斷數(shù)據(jù)庫是否已經(jīng)存在，不存在則創(chuàng)建數(shù)據(jù)庫（推薦）

CREATE DATABASE IF NOT EXISTS 數(shù)據(jù)庫名; 

如果MySQL中已經(jīng)存在相關(guān)的數(shù)據(jù)庫，則忽略創(chuàng)建語句，不再創(chuàng)建數(shù)據(jù)庫。

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

2.2 使用數(shù)據(jù)庫

• 查看當前所有的數(shù)據(jù)庫

SHOW DATABASES; #有一個S，代表多個數(shù)據(jù)庫

• 查看當前正在使用的數(shù)據(jù)庫

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

• 查看指定庫下所有的表

SHOW TABLES FROM 數(shù)據(jù)庫名;

• 查看數(shù)據(jù)庫的創(chuàng)建信息

SHOW CREATE DATABASE 數(shù)據(jù)庫名;
或者：
SHOW CREATE DATABASE 數(shù)據(jù)庫名\G

• 使用/切換數(shù)據(jù)庫

USE 數(shù)據(jù)庫名;

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

2.3 修改數(shù)據(jù)庫

• 更改數(shù)據(jù)庫字符集

ALTER DATABASE 數(shù)據(jù)庫名 CHARACTER SET 字符集;  #比如：gbk、utf8等

2.4 刪除數(shù)據(jù)庫

• 方式1：刪除指定的數(shù)據(jù)庫

DROP DATABASE 數(shù)據(jù)庫名;

• 方式2：刪除指定的數(shù)據(jù)庫（推薦）

DROP DATABASE IF EXISTS 數(shù)據(jù)庫名;

3. 創(chuàng)建表

3.1 創(chuàng)建方式1

• 必須具備：
  • CREATE TABLE權(quán)限
  • 存儲空間
• 語法格式：

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

加上了IF NOT EXISTS關(guān)鍵字，則表示：如果當前數(shù)據(jù)庫中不存在要創(chuàng)建的數(shù)據(jù)表，則創(chuàng)建數(shù)據(jù)表；如果當前數(shù)據(jù)庫中已經(jīng)存在要創(chuàng)建的數(shù)據(jù)表，則忽略建表語句，不再創(chuàng)建數(shù)據(jù)表。

• 必須指定：
  • 表名
  • 列名(或字段名)，數(shù)據(jù)類型，長度
• 可選指定：
  • 約束條件
  • 默認值
• 創(chuàng)建表舉例1：

-- 創(chuàng)建表
CREATE TABLE emp (
  -- int類型
  emp_id INT,
  -- 最多保存20個中英文字符
  emp_name VARCHAR(20),
  -- 總位數(shù)不超過15位
  salary DOUBLE,
  -- 日期類型
  birthday DATE
);

DESC emp;

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

• 創(chuàng)建表舉例2：

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

DESCRIBE dept;

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

3.2 創(chuàng)建方式2

• 使用 AS subquery 選項，將創(chuàng)建表和插入數(shù)據(jù)結(jié)合起來
  

• 指定的列和子查詢中的列要一一對應(yīng)

• 通過列名和默認值定義列

CREATE TABLE emp1 AS SELECT * FROM employees;

CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 創(chuàng)建的emp2是空表

CREATE TABLE dept80
AS 
SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
FROM    employees
WHERE   department_id = 80;

DESCRIBE dept80;

3.3 查看數(shù)據(jù)表結(jié)構(gòu)

在MySQL中創(chuàng)建好數(shù)據(jù)表之后，可以查看數(shù)據(jù)表的結(jié)構(gòu)。MySQL支持使用DESCRIBE/DESC語句查看數(shù)據(jù)表結(jié)構(gòu)，也支持使用SHOW CREATE TABLE語句查看數(shù)據(jù)表結(jié)構(gòu)。

語法格式如下：

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE語句不僅可以查看表創(chuàng)建時的詳細語句，還可以查看存儲引擎和字符編碼。

4. 修改表

修改表指的是修改數(shù)據(jù)庫中已經(jīng)存在的數(shù)據(jù)表的結(jié)構(gòu)。

使用 ALTER TABLE 語句可以實現(xiàn)：

• 向已有的表中添加列

• 修改現(xiàn)有表中的列

• 刪除現(xiàn)有表中的列

• 重命名現(xiàn)有表中的列

4.1 追加一個列

語法格式如下：

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

舉例：

ALTER TABLE dept80 
ADD job_id varchar(15);

4.2 修改一個列

• 可以修改列的數(shù)據(jù)類型，長度、默認值和位置

• 修改字段數(shù)據(jù)類型、長度、默認值、位置的語法格式如下：

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

• 舉例：

ALTER TABLE	dept80
MODIFY last_name VARCHAR(30);

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

• 對默認值的修改只影響今后對表的修改
• 此外，還可以通過此種方式修改列的約束。這里暫先不講。

4.3 重命名一個列

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

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新數(shù)據(jù)類型;

舉例：

ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15); 

4.4 刪除一個列

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

ALTER TABLE 表名 DROP 【COLUMN】字段名

舉例：

ALTER TABLE  dept80
DROP COLUMN  job_id; 

5. 重命名表

• 方式一：使用RENAME

RENAME TABLE emp
TO myemp;

• 方式二：

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

• 必須是對象的擁有者

6. 刪除表

• 在MySQL中，當一張數(shù)據(jù)表沒有與其他任何數(shù)據(jù)表形成關(guān)聯(lián)關(guān)系時，可以將當前數(shù)據(jù)表直接刪除。

• 數(shù)據(jù)和結(jié)構(gòu)都被刪除

• 所有正在運行的相關(guān)事務(wù)被提交

• 所有相關(guān)索引被刪除

• 語法格式：

DROP TABLE [IF EXISTS] 數(shù)據(jù)表1 [, 數(shù)據(jù)表2, …, 數(shù)據(jù)表n];

IF EXISTS的含義為：如果當前數(shù)據(jù)庫中存在相應(yīng)的數(shù)據(jù)表，則刪除數(shù)據(jù)表；如果當前數(shù)據(jù)庫中不存在相應(yīng)的數(shù)據(jù)表，則忽略刪除語句，不再執(zhí)行刪除數(shù)據(jù)表的操作。

• 舉例：

DROP TABLE dept80;

• DROP TABLE 語句不能回滾

7. 清空表

• TRUNCATE TABLE語句：

  • 刪除表中所有的數(shù)據(jù)
  • 釋放表的存儲空間

• 舉例：

TRUNCATE TABLE detail_dept;

• TRUNCATE語句不能回滾，而使用 DELETE 語句刪除數(shù)據(jù)，可以回滾

• 對比：

SET autocommit = FALSE;
  
DELETE FROM emp2; 
#TRUNCATE TABLE emp2;
  
SELECT * FROM emp2;
  
ROLLBACK;
  
SELECT * FROM emp2;

阿里開發(fā)規(guī)范：

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

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

8. 內(nèi)容拓展

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

• 【強制】表名、字段名必須使用小寫字母或數(shù)字，禁止出現(xiàn)數(shù)字開頭，禁止兩個下劃線中間只出現(xiàn)數(shù)字。數(shù)據(jù)庫字段名的修改代價很大，因為無法進行預(yù)發(fā)布，所以字段名稱需要慎重考慮。

  • 正例：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 類型，前者現(xiàn)在時表示主動式創(chuàng)建，后者過去分詞表示被動式更新

• 【推薦】表的命名最好是遵循 “業(yè)務(wù)名稱_表的作用”。

  • 正例：alipay_task 、 force_project、 trade_config

• 【推薦】庫名與應(yīng)用名稱盡量一致。

• 【參考】合適的字符存儲長度，不但節(jié)約數(shù)據(jù)庫表空間、節(jié)約索引存儲，更重要的是提升檢索速度。

  • 正例：無符號值可以避免誤存負數(shù)，且擴大了表示范圍。

  

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

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

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

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

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

分別在MySQL 5.7版本和MySQL 8.0版本中創(chuàng)建數(shù)據(jù)庫和數(shù)據(jù)表，結(jié)果如下：

CREATE DATABASE mytest;

USE mytest;

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

SHOW TABLES;

（1）在MySQL 5.7版本中，測試步驟如下：
刪除數(shù)據(jù)表book1和數(shù)據(jù)表book2，結(jié)果如下：

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

再次查詢數(shù)據(jù)庫中的數(shù)據(jù)表名稱，結(jié)果如下：

mysql> SHOW TABLES;
Empty set (0.00 sec)

從結(jié)果可以看出，雖然刪除操作時報錯了，但是仍然刪除了數(shù)據(jù)表book1。

（2）在MySQL 8.0版本中，測試步驟如下：
刪除數(shù)據(jù)表book1和數(shù)據(jù)表book2，結(jié)果如下：

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

再次查詢數(shù)據(jù)庫中的數(shù)據(jù)表名稱，結(jié)果如下：

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1            |
+------------------+
1 row in set (0.00 sec)

從結(jié)果可以看出，數(shù)據(jù)表book1并沒有被刪除。文章來源地址http://www.zghlxwxcb.cn/news/detail-847696.html

到了這里，關(guān)于MySQL-創(chuàng)建和管理表：基礎(chǔ)知識、創(chuàng)建和管理數(shù)據(jù)庫、創(chuàng)建表、修改表、重命名表、刪除表、清空表、拓展的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！