MySQL入門系列：資料庫和表的基本操作

小孩子4919發表於2019-03-07
MySql資料庫

標籤： MySQL入門

上集回顧

MySQL使用表來存放資料，表的每一列都需要存放特定格式的資料，所以MySQL為不同的格式的資料定義了不同的型別，我們介紹了各種數值、字串、時間和二進位制型別的含義以及要求的儲存空間。本集來看一下在MySQL中關於資料庫和表的各種操作。

命令使用注意事項

我們知道MySQL的基本執行過程就是：通過客戶端程式傳送命令給伺服器程式，伺服器程式按照接收的命令去操作實際的資料。在我們使用黑框框啟動了MySQL客戶端程式之後，介面上會一直顯示一行mysql>的提示符，你可以在它後邊輸入我們的命令然後按一下Enter鍵，在書寫命令的時候需要注意下邊這幾點：

  1. 命令結束符號。

    在書寫完一個命令之後需要以下邊這幾個符號之一結尾：

    • ;
    • \g
    • \G

    比如說我們執行一個簡單的查詢當前時間的命令：

    mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2018-02-06 17:50:55 |
+---------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

    其中的SELECT意味著這是一個查詢命令，NOW()是MySQL內建的函式，用於返回當前時間。不過我們現在並不是深究具體的某個命令是什麼意思，只是想介紹一下書寫命令時需要注意的一些事情。結果中1 row in set (0.00 sec)的意思是結果只有1行資料，用時0.00秒。使用\g可以起到一樣的效果：

    mysql> SELECT NOW()\g
+---------------------+
| NOW()               |
+---------------------+
| 2018-02-06 17:50:55 |
+---------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

    \G有一點特殊，它並不以表格的形式返回查詢資料，而是以垂直的形式展現查詢資料：

    mysql> SELECT NOW()\G
*************************** 1. row ***************************
NOW(): 2018-02-06 17:51:51
1 row in set (0.00 sec)

mysql>
複製程式碼

    如果查詢結果非常長的話，使用\G可以讓我們看清結果。如果顯式格式沒啥問題，那我們平時都使用分號;作為命令結束符了～。

  2. 命令可以隨意換行。

    並不是按了Enter鍵就提交命令了，只要按Enter鍵的時候輸入的語句裡沒有;、\g或者\G就算是語句沒結束。比如上邊查詢當前時間的命令還可以這麼寫：

    mysql> SELECT
    -> NOW()
    -> ;
+---------------------+
| NOW()               |
+---------------------+
| 2018-02-06 17:57:15 |
+---------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

  3. 可以一次提交多個命令

    我們可以在一條語句裡寫多個命令(命令之間用上面說的結束符分隔)，比如這樣：

    mysql> SELECT NOW(); SELECT NOW(); SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2018-02-06 18:00:05 |
+---------------------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2018-02-06 18:00:05 |
+---------------------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2018-02-06 18:00:05 |
+---------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

    連著輸入了3個查詢當前時間的命令，只要沒按Enter鍵，就不會提交命令。

    小貼士： 後邊我們還會介紹把命令都寫在檔案裡，然後再批量執行檔案中的命令，那個感覺更爽！

  4. 使用\c清除本次操作。

    如果你想放棄本次編寫的命令，可以使用\c來清除，比如這樣：

    mysql> SELECT NOW()\c
mysql>
複製程式碼

    如果不使用\c，那客戶端會以為這是一個多行命令，還在一直傻傻的等你輸入命令～

  5. 大小寫問題。

    MySQL預設對命令的大小寫並沒有限制，也就是說我們這樣查詢當前時間也是可以的：

    mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-02-06 18:23:01 |
+---------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

    不過按照習俗，這些命令、函式什麼的都是要大寫的，而一些名稱類的東西，比如資料庫名，表名、列名啥的都是要小寫的，更多具體的書寫規範等我們遇著再詳細介紹。

  6. 字串的表示。

    在命令裡有時會使用到字串，我們可以使用單引號''或者雙引號""把字串內容引起來，比如這樣：

    mysql> SELECT 'aaa';
+-----+
| aaa |
+-----+
| aaa |
+-----+
1 row in set (0.00 sec)

mysql>
複製程式碼

    這個語句只是簡單的把字串'aaa'又輸出來了而已。但是一定要在字串內容上加上引號，不然的話MySQL伺服器會把它當作列名，比如這樣就會返回一個錯誤：

    mysql> SELECT aaa;
ERROR 1054 (42S22): Unknown column 'aaa' in 'field list'
mysql>
複製程式碼

    但是MySQL中有一種叫ANSI_QUOTES的模式，如果開啟了這種模式，雙引號就有其他特殊的用途了，可能你並不能看懂我在說什麼，但是這都不重要，重要的是建議你最好使用單引號來表示字串～

當一條命令傳送給了MySQL伺服器之後，伺服器處理完後就會給你傳送回來響應的結果，然後顯示到介面上。然後你就可以接著輸入下一條命令了。

資料庫相關操作

MySQL中把某種型別的表的集合稱為一個資料庫，MySQL伺服器管理著若干個資料庫，每個資料庫下都可以有若干個表，畫個圖就是這樣：

image_1c4jjlck0101k1lbq100u1ggd4ml46.png-75.7kB

展示資料庫

在我們剛剛安裝好MySQL的時候，它已經內建了許多資料庫和表了，我們可以使用下邊這個命令來看一下都有哪些資料庫：

SHOW DATABASES;
複製程式碼

我自己的電腦上安裝的MySQL版本是5.7.21，看一下在這個版本里內建了哪些資料庫(啟動客戶端軟體，用你的使用者名稱和密碼登入後輸入命令)：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql>
複製程式碼

可以看到，這一版本的MySQL已經為我們內建了4個資料庫，這些資料庫都是給MySQL自己使用的，如果我們想使用MySQL存放自己的資料的話，首先需要建立一個屬於自己的資料庫。

建立資料庫

建立資料庫的語法賊簡單：

CREATE DATABASE 資料庫名;
複製程式碼

來實際操作一下：

mysql> CREATE DATABASE xiaohaizi;
Query OK, 1 row affected (0.00 sec)

mysql>
複製程式碼

我把我的名字xiaohaizi作為了資料庫名稱，敲完命令回車之後提示了一個Query OK, 1 row affected (0.00 sec)說明資料庫建立成功了。然後我們再用SHOW DATABASES的命令檢視一下現在有哪些資料庫：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xiaohaizi          |
+--------------------+
5 rows in set (0.00 sec)

mysql>
複製程式碼

看到我們自己建立的資料庫xiaohaizi就已經在列表裡了。

IF NOT EXISTS

我們在一個資料庫已經存在的情況下再使用CREATE DATABASE去建立這個資料庫會產生錯誤：

mysql> CREATE DATABASE xiaohaizi;
ERROR 1007 (HY000): Can't create database 'xiaohaizi'; database exists
mysql>
複製程式碼

執行結果提示了一個ERROR，意思是資料庫xiaohaizi已經存在！所以如果我們並不清楚資料庫是否存在，可以使用下邊的語句來建立資料庫：

CREATE DATABASE IF NOT EXISTS 資料庫名;
複製程式碼

這個命令的意思是如果指定的資料庫不存在的話就建立它，否則什麼都不做。我們試一試：

mysql> CREATE DATABASE IF NOT EXISTS xiaohaizi;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
複製程式碼

可以看到語句執行成功了，報的ERROR錯誤也沒有了，只是結果中有1個warning而已。這個warning只是MySQL善意的提醒我們資料庫xiaohaizi不存在而已，並不會影響語句的執行。

小貼士： 前邊說過MySQL的命令可以多條一起執行，可以在黑框框中一次輸入多個命令，也可以把好多命令放到一個檔案中執行。如果某一條命令的執行結果是產生了一個`ERROR`，MySQL會停止執行該命令之後的命令，但是如果僅僅是在執行結果中有`warning`的話，是不會中斷執行的。

切換當前資料庫

對於每一個連線到MySQL伺服器的客戶端，都有一個當前資料庫的概念，我們建立的表預設都會被放到當前資料庫中，切換當前資料庫的命令也賊簡單：

USE 資料庫名稱;
複製程式碼

所以在介紹建立表之前，我們應該把當前資料庫切換到剛剛建立的資料庫xiaohaizi上：

mysql> USE xiaohaizi;
Database changed
mysql>
複製程式碼

看到顯示了Database changed說明當前資料庫已經切換成功了。需要注意的是，在退出當前客戶端之後，也就是你輸入了exit或者quit命令之後或者直接把當前的黑框框頁面關掉，當你再次呼叫mysql -h 主機名 -u 使用者名稱 -p 密碼的時候，相當於重新開啟了一個客戶端，需要重新呼叫USE 資料庫名稱的命令來選擇一下當前資料庫。

刪除資料庫

如果你建立的資料庫沒用了，我們還可以把它刪掉，語法如下：

DROP DATABASE 資料庫名;
複製程式碼

在真實的工作環境裡，在刪除資料庫之前你需要先拿體溫計量量是不是發高燒了，然後再找至少兩個人核實一下自己是不是發燒了，然後你才敢執行刪除資料庫的命令。刪除資料庫意味著裡邊的表就都被刪除了，也就意味著你的資料都沒了，所以是個極其危險的操作，使用時需要極其謹慎。不過我們這是學習環境，而且剛剛建立了xiaohaizi資料庫，什麼表都沒往裡頭放，刪了就刪了吧：

mysql> DROP DATABASE xiaohaizi;
Query OK, 0 rows affected (0.01 sec)

mysql>
複製程式碼

然後看一下現在還有哪些資料庫：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql>
複製程式碼

可以看到我們前邊建立的xiaohaizi資料庫就沒有啦。

IF EXISTS

如果某個資料庫並不存在，我們仍舊呼叫DROP TABLE語句去刪除它，會報錯的：

mysql> DROP DATABASE xiaohaizi;
ERROR 1008 (HY000): Can't drop database 'xiaohaizi'; database doesn't exist
mysql>
複製程式碼

如果想避免這種報錯，可以使用這種形式的語句來刪除資料庫：

DROP DATABASE IF EXISTS 表名;
複製程式碼

再次刪除一下xiaohaizi：

mysql> DROP DATABASE IF EXISTS xiaohaizi;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
複製程式碼

這回就不會報錯啦！演示完刪除資料庫的流程之後還是把xiaohaizi資料庫建立出來並且切換到當前資料庫吧，畢竟我們之後還要在這個資料庫裡建立各種表呢～

表的操作

資料庫建好之後，我們就可以接著建立真正儲存資料的表了。建立表的時候首先需要描述清楚這個表長什麼樣，它有哪些列，這些列都是用來存什麼型別的資料等等，這個對錶的描述稱為表的模式(scheme)。有了表的模式之後，我們就可以著手把資料塞到這個表裡了。表中的每一行也叫做一條記錄，每一列也叫做一個欄位。

建立表

基本語法

建立一個表需要至少要完成下列事情：

  1. 給表起個名。
  2. 給表定義一些列，並且給這些列都起個名。
  3. 每一個列都需要定義一種資料型別，來規範它能儲存的資料。
  4. 如果有需要的話，可以給這些列定義一些列的屬性，比如不許為空，設定預設值等等，這些屬性也叫做列的約束性條件，具體有哪些列的屬性我們稍後會詳細嘮叨。

MySQL中建立表的基本語法就是這樣的：

CREATE TABLE 表名 (
    列名1    列的型別    [列的屬性],
    列名2    列的型別    [列的屬性],
    ...
    列名n    列的型別    [列的屬性]
);
複製程式碼

也就是說，我們在CREATE TABLE後寫清楚我們定義的表的表名，然後在小括號()中定義上這個表的各個列的資訊，包括列的列名、型別，如果有需要的話也可以定義這個列的屬性，其中列的屬性用中括號[]引起來的意思是可選的，也就是說可以有也可以沒有，列名、列的型別、列的屬性之間用空白字元分開就好，然後各個列的資訊之間用逗號,分隔開。

小貼士： 我們也可以把這個建立表的語句都放在一行裡(把換行刪掉)，分成多行並且加上縮排僅僅是為了美觀而已～

廢話不多說，趕緊建立一個超級簡單的表瞅瞅：

CREATE TABLE first_table (
    first_column INT,
    second_column VARCHAR(100)
);
複製程式碼

我們新建立的這個表的名字叫做first_table，它有兩個列，第一個列的列名是first_column，列的型別是INT，意味著只能存放整數型資料，第二個列的列名是second_column，列的型別是VARCHAR(100)，意味著這個列可以存放長度不超過100個字元的字串。我們在客戶端執行一下這個語句(當前資料庫是xiaohaizi)：

mysql> CREATE TABLE first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
複製程式碼

輸出Query OK, 0 rows affected (0.02 sec)意味著建立成功了，並且耗時0.02秒。

有了建立first_table的經驗，我們就可以著手用MySQL把之前提到的學生基本資訊表和成績表給建立出來了，先把學生資訊表搬下來看看：

**學生基本資訊表**
學號 姓名 性別 身份證號 學院 專業 入學時間
20180101 杜子騰 男 158177199901044792 計算機學院 電腦科學與工程 2018/9/1
20180102 杜琦燕 女 151008199801178529 計算機學院 電腦科學與工程 2018/9/1
20180103 範統 男 17156319980116959X 計算機學院 軟體工程 2018/9/1
20180104 史珍香 女 141992199701078600 計算機學院 軟體工程 2018/9/1

很顯然，這個表有學號、姓名、性別、身份證號、學院、專業、入學時間這幾個列，其中的學號是整數型別的，入學時間是日期型別的，由於身份證號是固定的18位，我們可以把身份證號這一列定義成固定長度的字串型別，性別一列只能填男或女，所以我們這裡把它定義為ENUM型別的，其餘各個欄位都是可變的字串型別的。看一下建立學生基本資訊表的語句：

CREATE TABLE student_info (
    number INT,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);
複製程式碼

然後再看一下學生成績表：

學生成績表
學號 科目 成績
20180101 母豬的產後護理 78
20180101 論薩達姆的戰爭準備 88
20180102 母豬的產後護理 100
20180102 論薩達姆的戰爭準備 98
20180103 母豬的產後護理 59
20180103 論薩達姆的戰爭準備 61
20180104 母豬的產後護理 55
20180104 論薩達姆的戰爭準備 46

這個表有學號、科目、成績這幾個列，學號和成績是整數型別的，科目是字串型別的，所以我們建立一下這個表：

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT
);
複製程式碼

趕緊到你的客戶端裡填寫建立這兩個表的命令吧～

展示當前資料庫中的表

我們剛才在xiaohaizi資料庫裡建立了幾個表，那我們怎麼檢視xiaohaizi資料庫下都有哪些表呢？MySQL提供了這樣的一個命令：

SHOW TABLES;
複製程式碼

該命令會展示出當前資料庫中都有哪些表，我們執行一下：

mysql> show tables;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| first_table         |
| student_info        |
| student_score       |
+---------------------+
3 rows in set (0.01 sec)

mysql>
複製程式碼

我們剛才建立的表就都被展示出來了。

IF NOT EXISTS

和重複建立資料庫一樣，如果建立一個已經存在的表的話是會報錯的，我們來試試重複建立一下first_table表：

mysql> CREATE TABLE first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100)
    -> );
ERROR 1050 (42S01): Table 'first_table' already exists
mysql>
複製程式碼

執行結果提示了一個ERROR，意思是first_table已經存在！所以如果想要避免這種尷尬，我們可以在建立表的時候使用這種形式：

CREATE TABLE IF NOT EXISTS 表名(
    各個列資訊的定義 ...
);
複製程式碼

加入了IF NOT EXISTS的語句表示如果指定的表名不存在則建立這個表，如果不存在那就什麼都不做。我們使用這種IF NOT EXISTS的語法再來建立一遍first_table表：

mysql> CREATE TABLE IF NOT EXISTS first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100)
    -> );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
複製程式碼

可以看到語句執行成功了，報的ERROR錯誤也沒有了，只是結果中有1個warning而已。

簡單的表操作語句

在建立完表之後，我們只是建立了一個殼子，裡邊什麼資料都沒有。使用表的目的當然是儲存資料啦，下邊我們來看幾個簡單的查詢與插入語句，更多關於表中資料的操作語句我們之後會詳細嘮叨的。

簡單的查詢語句

如果我們想檢視某個表裡已經儲存了哪些資料，可以用下邊這個語句：

SELECT * FROM 表名;
複製程式碼

比如我們想看看前邊建立的first_table表中有哪些資料，可以這麼寫：

mysql> SELECT * FROM first_table;
Empty set (0.01 sec)

mysql>
複製程式碼

很遺憾，我們從來沒有向表中插入過資料，所以查詢結果顯示的是Empty set，表示什麼都沒查出來～

簡單插入語句

MySQL插入資料的時候是以行為單位的，語法格式如下：

INSERT INTO 表名(列1, 列2, ...) VALUES(列1的值，列2的值, ...);
複製程式碼

也就是說我們可以在表名後邊的括號中指定要插入資料的列，然後在VALUES後邊的括號中按指定的列順序填入對應的值，我們來為first_table表插入第一行資料：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa');
Query OK, 1 row affected (0.00 sec)

mysql>
複製程式碼

這個語句的意思就是我們要向first_table表中插入一行資料，first_column列的值是1，second_column列的值是'aaa'。看一下現在表中的資料：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
+--------------+---------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

第一行資料就插入成功了！

我們也可以只指定部分的列，沒有顯式指定的列的值將被設定為NULL，意思是還沒有指定值，比如這樣寫：

mysql> INSERT INTO first_table(first_column) VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO first_table(second_column) VALUES('ccc');
Query OK, 1 row affected (0.00 sec)

mysql>
複製程式碼

第一條插入語句我們只指定了first_column列的值是2，而沒有指定second_column的值，所以second_column的值就是NULL；第二條插入語句我們只指定了second_column的值是'ccc'，而沒有指定first_column的值，所以first_column的值就是NULL，也表示沒有資料～ 看一下現在表中的資料：

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|         NULL | ccc           |
+--------------+---------------+
3 rows in set (0.00 sec)

mysql>
複製程式碼
批量插入

每插入一行資料寫一條語句也不是不行，但是對人來說太煩了，而且每插入一行資料提交一個請求給伺服器遠沒有一次把所有插入的資料提交給伺服器效率高，所以MySQL為我們提供了批量插入的語句：

INSERT INTO 表名(列1,列2, ...) VAULES(列1的值，列2的值, ...), (列1的值，列2的值, ...), (列1的值，列2的值, ...), ...;
複製程式碼

也就是在原來的單條插入語句後邊多寫幾項插入行的內容，用逗號分隔開就好了，舉個例子：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(4, 'ddd'), (5, 'eee'), (6, 'fff');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|         NULL | ccc           |
|            4 | ddd           |
|            5 | eee           |
|            6 | fff           |
+--------------+---------------+
6 rows in set (0.01 sec)

mysql>
複製程式碼

可以看到3行記錄就插入成功了！

刪除表

如果一個表不用了，就可以刪掉了，在真實環境中刪除表一定要慎重謹慎，失去了的就再也回不來了～ 看一下刪除的語法：

DROP TABLE 表1, 表2, ..., 表n;
複製程式碼

也就是說我們可以同時刪除多個表。我們現在把first_table表給刪掉看看：

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| student_info        |
| student_score       |
+---------------------+
2 rows in set (0.00 sec)

mysql>
複製程式碼

可以看到現在資料庫xiaohaizi中沒有了first_table表，說明刪除成功了！

IF EXISTS

如果我們嘗試刪除某個不存在的表的話會報錯：

mysql> DROP TABLE first_table;
ERROR 1051 (42S02): Unknown table 'xiaohaizi.first_table'
mysql>
複製程式碼

提示我們要刪除的表並不存在，如果想避免報錯，可以使用這種刪除語法：

DROP TABLE IF EXISTS 表名;
複製程式碼

然後再刪除一下不存在的first_table表：

mysql> DROP TABLE IF EXISTS first_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
複製程式碼

這樣就不報錯了～

約束性條件（列的屬性）

對於某些列來說，可能有一些特殊含義或者用法，我們把這些特殊的含義或用法稱為列的屬性，也可以稱為列的約束性條件，在建立表的時候可以顯式的定義出來。下邊我們看都有哪些約束性條件以及怎麼在建立表的時候把它們定義出來。

預設值

前邊介紹INSERT語句的時候說過，如果在指定的插入列中省略了某些列，那這些列的值將被設定為NULL，也就是列的預設值為NULL，表示沒有設定值。我們在建立表的時候也可以指定一些有意義的預設值，指定方式如下：

列名 列的型別 DEFAULT 預設值
複製程式碼

比如我們把first_table的second_column列的預設值指定為'abc'，建立一下這個表：

mysql> CREATE TABLE first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
複製程式碼

然後插入一條資料後看看預設值是不是起了作用：

mysql> INSERT INTO first_table(first_column) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | abc           |
+--------------+---------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

我們的插入語句並沒有指定second_column的值，但是可以看到插入結果是按照我們規定的預設值'abc'來設定的。

如果我們不設定預設值，其實就相當於指定的預設值為NULL，比如first_table表並沒有設定first_column列的預設值，那它的預設值就是NULL，也就是說上邊的表定義語句和下邊這個是等價的：

CREATE TABLE first_table (
    first_column INT DEFAULT NULL,
    second_column VARCHAR(100) DEFAULT 'abc'
);
複製程式碼

非空約束

對於某些列，我們要求它們是必填的，也就是不允許存放NULL值，我們用這樣的語法來定義這個列：

列名 列的型別 NOT NULL
複製程式碼

比如我們把first_table的first_column列定義為NOT NULL。當然，我們在重新定義表之前需要把原來的表刪掉：

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE first_table (
    ->     first_column INT NOT NULL,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
複製程式碼

這樣的話，我們就不能再往這個欄位裡插入NULL值了，比如這樣：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(NULL, 'aaa');
ERROR 1048 (23000): Column 'first_column' cannot be null
mysql>
複製程式碼

可以看到，彈出了錯誤提示。

一旦對某個列定義了NOT NULL屬性，那這個列的預設值就不為NULL了。上邊first_column並沒有指定預設值，意味著我們在使用INSERT插入行時必須顯式的指定這個列的值，而不能省略它，比如這樣就會報錯的：

mysql> INSERT INTO first_table(second_column) VALUES('aaa');
ERROR 1364 (HY000): Field 'first_column' doesn't have a default value
mysql>
複製程式碼

可以看到執行結果提示我們first_column並沒有設定預設值，所以在插入資料的時候不能省略掉這個列的值。

主鍵

有時候在我們的表裡可以通過某個列或者某些列確定唯一的一條記錄，我們就可以把這個列或者這些列稱為候選鍵。比如在學生資訊表student_info中，只要我們知道某個學生的學號，就可以確定一個唯一的學生資訊，也就是一條記錄。當然，我們也可以通過身份證號來確定唯一的一條學生資訊記錄，所以學號和身份證號都可以作為學生資訊表的候選鍵。在學生成績表student_score中，我們可以通過學號和科目這兩個列的組合來確定唯一的一條成績記錄，所以學號、科目這兩個列的組合可以作為學生成績表的候選鍵。

一個表可能有多個候選鍵，我們可以選擇一個候選鍵作為表的主鍵。從定義中就可以看出，一個表最多隻能有一個主鍵，主鍵的值不能重複，通過主鍵可以找到唯一的一條記錄。如果我們的表中有定義主鍵的需求可以選用下邊這兩種方式之一來指定主鍵：

  1. 如果主鍵只是單個列的話，可以直接在該列後宣告PRIMARY KEY，比如我們把學生資訊表student_info的學號列宣告為主鍵可以這麼寫：

    CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);
複製程式碼

  2. 我們也可以把主鍵的宣告單獨提取出來，用這樣的形式宣告：

    PRIMARY KEY (列名1, 列名2, ...)
複製程式碼

    然後把這個主鍵宣告放到列定義的後邊就好了。比如student_info的學號列宣告為主鍵也可以這麼寫：

    CREATE TABLE student_info (
    number INT,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    PRIMARY KEY (number)
);
複製程式碼

    值得注意的是，對於多個列的組合作為主鍵的情況，必須使用這種單獨宣告的形式，比如student_score表裡的學號,科目的列組合作為主鍵，可以這麼宣告：

    CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject)
);
複製程式碼

在我們建立表的時候就宣告瞭主鍵的話，MySQL會對我們插入的記錄做校驗，如果兩條記錄裡有相同的主鍵值的話就會報錯。

另外，主鍵列預設是NOT NULL的，也就是必填的，如果填入NULL值會報錯(先刪除原來的student_info表，使用上邊的兩種方式之一重新建立表)：

mysql> INSERT INTO student_info(number) VALUES(NULL);
ERROR 1048 (23000): Column 'number' cannot be null
mysql>
複製程式碼

所以大家在插入資料的時候至少別忘了給主鍵列賦值哈～

唯一性約束

對於不是主鍵的其他候選鍵，如果我們也想讓MySQL替我們校驗資料的唯一性，那我們可以把這個列或列組合宣告為UNIQUE的，表明該列或者列組合的值是不允許重複的，這種列的屬性叫做唯一性約束。同主鍵的定義一樣，唯一性約束的定義也有兩種方式：

  1. 如果具有唯一性約束的列是單個列的話，可以直接在該列後宣告UNIQUE或者UNIQUE KEY，比如在學生資訊表student_info中，我們不允許兩條學生記錄中的身份證號是一樣的，那我們讓id_number這一列具有唯一性約束。

    CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18) UNIQUE,
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);
複製程式碼

  2. 我們也可以把唯一性約束的宣告單獨提取出來，用這樣的形式宣告：

    UNIQUE [約束名稱] (列名1, 列名2, ...)
複製程式碼

    或者：

    UNIQUE KEY [約束名稱] (列名1, 列名2, ...)
複製程式碼

    其中的約束名稱是可選的，其實就是我們為這個唯一性約束起的一個名字而已，如果不起名字的話該名稱預設和列名相同，這個不重要哈～ 然後把這個唯一性約束宣告放到列定義的後邊就好了。比如student_info的身份證號列宣告唯一性約束的屬性也可以這麼寫：

    CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    UNIQUE KEY (id_number)
);
複製程式碼

    值得注意的是，對於多個列的組合具有唯一性約束的情況，必須使用這種單獨宣告的形式。

如果表中定義了唯一性約束的話，MySQL會對我們插入的記錄做校驗，如果插入的值違反了唯一性約束的話就會報錯！

主鍵和唯一性約束的區別

主鍵和唯一性約束都能保證某個列或者列組合的唯一性，但是：

  1. 一張表中只能定義一個主鍵，卻可以定義多個唯一性約束！
  2. 主鍵列不允許存放NULL值，而普通的唯一性約束列可以存放NULL值！

小貼士： 你可能會問為啥主鍵列不允許存放NULL值，而普通的唯一性約束列卻可以呢？哈哈，這涉及到底層儲存的事情，現在你只需要記住這個規定就好了，如果你想知道更多的事情，那就繼續往後看唄～

外來鍵

插入到學生成績表student_score中的學號(number)列中的值必須能在學生基本資訊表student_info中的學號列中找到，否則如果一個學號只在成績表裡出現，而在資訊表裡找不到相應的記錄的話，就相當於插入了一個不知道是哪個學生的成績，這顯然是荒謬的。為了防止這樣荒謬的情況出現，MySQL給我們提供了外來鍵約束機制。定義外來鍵的語法是這樣的：

CONSTRAINT [外來鍵名稱] FOREIGN KEY(列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...);
複製程式碼

其中的外來鍵名稱也是可選的，一個名字而已有沒有都行，不是很重要～ 如果A表中的某個列或者某些列依賴與B表中的某個列或者某些列，那麼就稱A表為子表，B表為父表。子表和父表可以使用外來鍵來關聯起來，上邊例子中student_info就是一個父表，student_score就是子表，我們可以這樣來定義student_score列，來使用外來鍵關聯起父表和子表：

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);
複製程式碼

這樣，在對student_score表插入資料的時候，MySQL都會為我們檢查一下插入的學號是否能在student_info中找到，如果找不到則會報錯。

小貼士： 父表中作為外來鍵的列或者列組合必須建立索引，主鍵和具有唯一性約束的列預設的都建立了索引，置於什麼是索引，我們之後會詳細嘮叨的。

自增

如果一個表中的某個列是數值型別的，包括整數型別和浮點數型別，那麼這個列可以設定自增屬性。所謂自增，意思是如果我們在插入資料的時候不指定該列的值，那麼該列的值就是上一列的值加1後的值，定義語法就是這樣：

列名 列的型別 AUTO_INCREMENT
複製程式碼

比如我們想在first_table裡設定一個自增列id，並把這個列設定為主鍵，來唯一標記一行記錄，我們可以這麼寫：

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE first_table (
    ->     id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     first_column INT,
    ->     second_column VARCHAR(100) DEFAULT 'abc'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
複製程式碼

先把原來的表刪掉，然後在新表中增加了一個非負int型別的id列，並把它設定為主鍵而且具有遞增屬性，那我們插入資料的時候就可以不用管這個列，但是它的值將會遞增，看：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa'), (2, 'bbb'), (3, 'ccc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM first_table;
+----+--------------+---------------+
| id | first_column | second_column |
+----+--------------+---------------+
|  1 |            1 | aaa           |
|  2 |            2 | bbb           |
|  3 |            3 | ccc           |
+----+--------------+---------------+
3 rows in set (0.00 sec)

mysql>
複製程式碼

可以看到，列id是從1開始遞增的。在使用遞增屬性的時候需要注意這幾點：

  1. 一個表中最多有一個遞增列。
  2. 一般只為整數型別的列定義遞增屬性，浮點數型別基本不用遞增屬性。
  3. 具有AUTO_INCREMENT屬性的列必須建立索引。主鍵和具有唯一性約束的列會自動建立索引，至於什麼是索引，我們後邊會詳細嘮叨。
  4. 一般遞增列都是作為主鍵的屬性，來自動生成唯一標識一個記錄的主鍵值。
  5. 因為具有AUTO_INCREMENT屬性的列是從1開始遞增的，所以最好用UNSIGNED來修飾這個列，可以提升正數的表示範圍。

約束性條件的組合

每個列可以有多個約束性條件，宣告的順序無所謂，各個約束性條件之間用空白隔開就好了～

小貼士： 注意，有的約束性條件是衝突的，一個列不能具有兩個衝突的約束性條件，比如一個列不能既宣告為`PRIMARY KEY，又宣告為UNIQUE KEY`，不能既宣告為`DEFAULT NULL`，又宣告為`NOT NULL`。大家在使用過程中需要注意這一點。

新增註釋

我們可以對每個列資訊以及表作註釋，具體語法如下：

CREATE TABLE 表名 (
    列1 列的型別 [列的屬性] COMMENT '列1的註釋資訊',
    列2 列的型別 [列的屬性] COMMENT '列2的註釋資訊',
    列3 列的型別 [列的屬性] COMMENT '列3的註釋資訊',
) COMMENT '表的註釋資訊';
複製程式碼

比如我們可以這樣建立我們的first_table表：

CREATE TABLE first_table (
    id int UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主鍵',
    first_column INT COMMENT '第一列',
    second_column VARCHAR(100) DEFAULT 'abc' COMMENT '第二列'
) COMMENT '第一個表';
複製程式碼

註釋沒必要太長，言簡意賅即可，畢竟是給人看的，讓人看明白是個啥意思就OK了。為了我們自己的方便，也為了閱讀你建立的人的方便，請遵守一下職業道德，寫個註釋吧～ 求求你了～ 求求你了～ 求求你了～

識別符號的命名

像資料庫名、表名、列名、約束名稱或者我們之後會遇到的別的名稱，這些我們起的名字統統被稱為識別符號。雖然MySQL中對識別符號的命名沒多少限制，但是卻不歡迎下邊的這幾種命名：

  1. 名稱中全都是數字。

    因為在一些MySQL命令中也會使用到數字，如果你起的名稱中全部都是數字，會讓MySQL伺服器分別不清哪個是名稱，哪個是數字了。比如名稱1234567就是非法的。

  2. 名稱中有空白字元

    MySQL命令是靠空白字元來分隔各個單詞的，比如下邊這兩行命令是等價的：

    CREATE DATABASE xiaohaizi;
CREATE   DATABASE   xiaohaizi;
複製程式碼

    但是如果你定義的名稱中有空白字元，這樣會被當作兩個詞去處理，就會造成歧義。比如名稱word1 word2 word3就是非法的。

  3. 名稱使用了MySQL中的保留字

    MySQL中有很多保留的詞是會被當作命令處理的，比如CREATE、DATABASE、DROP、TABLE等等等等，我們稍後還會介紹大量的命令，這些命令用到的單詞都是作為MySQL的保留字，如果你的名稱用到了這些詞兒也會導致歧義。比如名稱create就是非法的。

雖然某些名稱可能會導致歧義，但是如果你堅持要使用的話，也不是不行，你可以使用反引號``來將你定義的名稱擴起來，這樣MySQL的伺服器就能檢測到你提供的是一個名稱而不是別的什麼東西，比如說把上邊幾個非法的名稱加上反引號``就變成合法的名稱了：

`1234567`
`word1 word2    word3`
`create`
複製程式碼

我們上邊對錶first_table的定義可以把裡邊的識別符號全都使用反引號``引起來，這樣語義更清晰一點：

CREATE TABLE `first_table` (
    `id` int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `first_column` INT,
    `second_column` VARCHAR(100) DEFAULT 'abc'
);
複製程式碼

雖然反引號比較強大，但是我們還是建議大家不要起各種非主流的名稱，也不要使用全數字、帶有空白字元或者MySQL保留字的名稱。由於MySQL是C語言實現的，所以在名稱定義上還是儘量遵從C語言的規範吧，就是用小寫字母、數字、下劃線、美元符號等作為名稱，如果有多個單詞的話，各個單詞之間用下劃線連線起來，比如student、student_info啥的～

檢視錶結構

有時候我們可能忘記了自己定義的表的結構，可以使用下邊這些語句來檢視，它們起到的效果都是一樣的：

DESCRIBE 表名;
DESC 表名;
EXPLAIN 表名;
SHOW COLUMNS FROM 表名;
SHOW FIELDS FROM 表名;
複製程式碼

比如我們看一下student_info這個表的結構：

mysql> DESC student_info;
+-----------------+-------------------+------+-----+---------+-------+
| Field           | Type              | Null | Key | Default | Extra |
+-----------------+-------------------+------+-----+---------+-------+
| number          | int(11)           | NO   | PRI | NULL    |       |
| name            | varchar(5)        | YES  |     | NULL    |       |
| sex             | enum('男','女')   | YES  |     | NULL    |       |
| id_number       | char(18)          | YES  | UNI | NULL    |       |
| department      | varchar(30)       | YES  |     | NULL    |       |
| major           | varchar(30)       | YES  |     | NULL    |       |
| enrollment_time | date              | YES  |     | NULL    |       |
+-----------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql>
複製程式碼

可以看到，這個student_info表的各個列的型別和屬性就都展示出來了，其中PRI是PRIMARY KEY的縮寫，UNI是UNIQUE KEY的縮寫。

小貼士： 請注意`number`列的型別是`int(11)`，這個小括號裡的`11`是什麼意思？這個`11`其實是所謂的顯示寬度，我們稍後馬上嘮叨。

如果你看不慣這種詳細展示各個欄位的的資訊，我們還可以使用下邊這個語句來檢視錶結構：

SHOW CREATE TABLE 表名;
複製程式碼

比如：

mysql> SHOW CREATE TABLE student_info;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                        |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_info | CREATE TABLE `student_info` (
  `number` int(11) NOT NULL,
  `name` varchar(5) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `id_number` char(18) DEFAULT NULL,
  `department` varchar(30) DEFAULT NULL,
  `major` varchar(30) DEFAULT NULL,
  `enrollment_time` date DEFAULT NULL,
  PRIMARY KEY (`number`),
  UNIQUE KEY `id_number` (`id_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8   |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

由於這行資料太長了，所以輸出效果並不是很好，所以把原來用於標記語句結束的分號;改為\G，這樣的效果可能好點：

mysql> SHOW CREATE TABLE student_info\G
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `number` int(11) NOT NULL,
  `name` varchar(5) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `id_number` char(18) DEFAULT NULL,
  `department` varchar(30) DEFAULT NULL,
  `major` varchar(30) DEFAULT NULL,
  `enrollment_time` date DEFAULT NULL,
  PRIMARY KEY (`number`),
  UNIQUE KEY `id_number` (`id_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>
複製程式碼

可以看到，使用SHOW CREATE TABLE這種語句展示出來的表結構就是我們平時建立表的語句。而且MySQL預設為主鍵欄位加了NOT NULL屬性，為其他欄位加了DEFAULT NULL屬性，而且還給身份證號的唯一性約束起了和列名一樣的名稱。

小貼士： 你可能疑惑的是在表定義末尾的`ENGINE=InnoDB DEFAULT CHARSET=utf8`是什麼意思，這個是指定表的儲存引擎和預設字符集，這些內容後邊會有專門的專題來詳細嘮叨的，你現在不用關心～

ZEROFILL與顯示寬度

對於無符號整數型別的列，我們可以在查詢資料的時候讓數字左邊補0，如果想實現這個效果需要給該列加一個ZEROFILL屬性，就像這樣：

mysql> CREATE TABLE zero_table (
    ->     i1 INT UNSIGNED ZEROFILL,
    ->     i2 INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
複製程式碼

我們在zero_table表中建立了兩個整數列，不同的是i1列具有ZEROFILL屬性，下邊我們為這個表插入一條記錄：

mysql> INSERT INTO zero_table(i1, i2) VALUES(1, 1);
Query OK, 1 row affected (0.00 sec)

mysql>
複製程式碼

然後我們使用查詢語句來顯示一下剛插入的資料：

mysql> SELECT * FROM zero_table;
+------------+------+
| i1         | i2   |
+------------+------+
| 0000000001 |    1 |
+------------+------+
1 row in set (0.00 sec)

mysql>
複製程式碼

對於具有ZEROFILL屬性的i1列，在顯示的時候補了一堆0，仔細數數發現是9個0，而沒有ZEROFILL屬性的i2列，在顯示的時候並沒有補0。為什麼i1列會補9個0呢？我們檢視一下zero_table的表結構：

mysql> DESC zero_table;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| i1    | int(10) unsigned zerofill | YES  |     | NULL    |       |
| i2    | int(10) unsigned          | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>
複製程式碼

可以看到，其實i1和i2列的型別INT後邊都加了一個(10)，這個10就是所謂的顯示寬度。顯示寬度是在查詢語句顯示的結果中，如果宣告瞭 ZEROFILL 屬性的整數列的實際值的位數小於顯示寬度時，會在實際值的左側補0，使補0的位數和實際值的位數相加正好等於顯示寬度。我們也可以自己指定顯示寬度，比方說這樣：

mysql> DROP TABLE zero_table;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE zero_table (
    ->     i1 INT(5) UNSIGNED ZEROFILL,
    ->     i2 INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO zero_table(i1, i2) VALUES(1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM zero_table;
+-------+------+
| i1    | i2   |
+-------+------+
| 00001 |    1 |
+-------+------+
1 row in set (0.00 sec)

mysql>
複製程式碼

新建立的表中，i1欄位的顯示寬度是5，所以最後的顯示結果中補了4個0。

注意事項

  1. 在查詢時，資料自動補0的條件有這幾個：

    • 該列必須是整數型別的
    • 該列必須有UNSIGNED ZEROFILL的屬性
    • 該列的實際值的位數必須小於顯示寬度

  2. 在建立表的時候，如果宣告瞭ZEROFILL屬性的列沒有宣告UNSIGNED屬性，那MySQL會為該列自動生成UNSIGNED屬性。

    也就是說如果我們建立表語句是這樣的：

    CREATE TABLE zero_table (
    i1 INT ZEROFILL,
    i2 INT UNSIGNED
);
複製程式碼

    MySQL會自動幫我們為i1列加上UNSIGNED屬性，也就是這樣：

    CREATE TABLE zero_table (
    i1 INT UNSIGNED ZEROFILL,
    i2 INT UNSIGNED
);
複製程式碼

  3. 每個整數型別都會有預設的顯示寬度。

    比如TINYINT的預設顯示寬度是4，INT的預設顯示寬度是(11)... 如果加了UNSIGNED屬性，則該型別的顯示寬度減1，比如TINYINT UNSIGNED的顯示寬度是3，INT UNSIGNED的顯示寬度是11...

  4. 顯示寬度並不會影響實際型別的實際儲存空間。

    也就是說INT(1)和INT(10)其實並沒有什麼區別，比方說zero_table表中i1列的顯示寬度是5，而數字12345678的位數是8，它照樣可以被填入i1列中：

    mysql> INSERT INTO zero_table(i1, i2) VALUES(12345678, 12345678);
Query OK, 1 row affected (0.01 sec)
複製程式碼

  5. 對於沒有宣告ZEROFILL屬性的列，顯示寬度沒有一毛錢卵用。

    記住，只有在查詢宣告瞭ZEROFILL屬性的列時，顯示寬度才會起作用，否則忽略顯示寬度這個東西的存在。

  6. 只有列的實際值的位數小於顯示寬度時才會補0，實際值的位數大於顯示寬度時照原樣輸出。

    比方說我們剛剛把12345678存到了i1列裡，在展示這個值時，並不會截短顯示的資料，而是照原樣輸出：

    mysql> SELECT * FROM zero_table;
+----------+----------+
| i1       | i2       |
+----------+----------+
|    00001 |        1 |
| 12345678 | 12345678 |
+----------+----------+
2 rows in set (0.00 sec)

mysql>
複製程式碼

沒有選擇預設資料庫時對錶的操作

有時候我們並沒有使用USE語句來選擇當前的資料庫，或者在一條語句中遇到的表分散在不同的資料庫中，我們就必須顯式的指定這些表所屬的資料庫了。比如不管當前資料庫是不是xiaohaizi，我們都可以呼叫這個語句來展示資料庫xiaohaizi裡邊的表：

mysql> SHOW TABLES FROM xiaohaizi;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| first_table         |
| student_info        |
| student_score       |
+---------------------+
3 rows in set (0.00 sec)

mysql>
複製程式碼

其他地方如果使用到表名的話，需要顯式指定這個表所屬的資料庫，指明方式是這樣的：

資料庫名.表名
複製程式碼

比如我們需要查詢first_table表中的資料，可以這麼寫：

SELECT * FROM xiaohaizi.first_table;
複製程式碼

檢視錶結構：

SHOW CREATE TABLE xiaohaizi.first_table\G
複製程式碼

在其他不確定當前資料庫的情況下，使用到表的地方也都需要加上所屬的資料庫名，就不一一列舉了。

修改表

在表建立好之後如果對錶的結構不滿意，比如想增加或者刪除一列，想修改某一列的資料型別或者約束性條件，想對錶名或者列名進行重新命名，這些操作統統都算是修改表結構。MySQL給我們提供了一系列修改表結構的語句。

修改表名

我們可以通過下邊這兩種方式來修改表的名稱：

  1. 方式一：

    ALTER TABLE 舊錶名 RENAME TO 新表名;
複製程式碼

    我們把first_table表的名稱修改為first_table1：

    mysql> ALTER TABLE first_table RENAME TO first_table1;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| first_table1        |
| student_info        |
| student_score       |
+---------------------+
3 rows in set (0.00 sec)

mysql>
複製程式碼

    通過SHOW TABLES命令可以看到已經改名成功了。

  2. 方式二：

    RENAME TABLE 舊錶名1 TO 新表名1, 舊錶名2 TO 新表名2, ... 舊錶名n TO 新表名n;
複製程式碼

    這種改名方式的牛逼之處就是它可以在一條語句中修改多個表的名稱。這裡就不舉例了，自己測試一下吧。

如果在修改表名的時候指定了資料庫名，還可以轉移表所屬的資料庫，我們先再建立一個資料庫dahaizi：

mysql> CREATE DATABASE dahaizi;
Query OK, 1 row affected (0.00 sec)

mysql>
複製程式碼

然後把first_table1錶轉移到這個資料庫下：

mysql> ALTER TABLE xiaohaizi.first_table1 RENAME TO dahaizi.first_table1;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES FROM dahaizi;
+-------------------+
| Tables_in_dahaizi |
+-------------------+
| first_table1      |
+-------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES FROM xiaohaizi;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| student_info        |
| student_score       |
+---------------------+
2 rows in set (0.00 sec)

mysql>
複製程式碼

可以看到first_table1就從資料庫xiaohaizi轉移到dahaizi裡邊了。我們再換一種方式把它轉回來，並且更名為first_table：

mysql> RENAME TABLE dahaizi.first_table1 TO xiaohaizi.first_table;
Query OK, 0 rows affected (0.00 sec)

mysql>
複製程式碼

增加列

我們可以使用下邊的語句來增加表中的列：

ALTER TABLE 表名 ADD COLUMN 列名 列的型別 [列的屬性];
複製程式碼

比如我們向first_table裡新增一個名叫third_column的列就可以這麼寫：

mysql> ALTER TABLE first_table ADD COLUMN third_column CHAR(4) DEFAULT '1234' COMMENT '第三列';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC first_table;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| first_column  | int(11)          | YES  |     | NULL    |                |
| second_column | varchar(100)     | YES  |     | abc     |                |
| third_column  | char(4)          | YES  |     | 1234    |                |
+---------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>
複製程式碼

通過檢視錶的結構可以看到該列已經新增成功了。

增加列到特定位置

預設的情況下列都是加到現有列的最後一列，我們也可以在新增列的時候指定它的位置，具體有兩種方式：

  1. 新增到第一列：

    ALTER TABLE 表名 ADD COLUMN 列名 列的型別 [列的屬性] FIRST;
複製程式碼

    讓我們把fourth_column插入到第一列：

    mysql> ALTER TABLE first_table ADD COLUMN fourth_column CHAR(4) DEFAULT '1234' COMMENT '第四列' FIRST;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC first_table;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| fourth_column | char(4)          | YES  |     | 1234    |                |
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| first_column  | int(11)          | YES  |     | NULL    |                |
| second_column | varchar(100)     | YES  |     | abc     |                |
| third_column  | char(4)          | YES  |     | 1234    |                |
+---------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql>
複製程式碼

    看到插入成功了。

  2. 新增到指定列的後邊：

    ALTER TABLE 表名 ADD COLUMN 列名 列的型別 [列的屬性] AFTER 指定列名;
複製程式碼

    再插入一個fifth_column到first_column後邊瞅瞅：

    mysql> ALTER TABLE first_table ADD COLUMN fifth_column CHAR(4) DEFAULT '1234' COMMENT '第五列' AFTER first_column;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC first_table;
+---------------+------------------+------+-----+---------+----------------+
複製程式碼

| Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+----------------+ | fourth_column | char(4) | YES | | 1234 | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | first_column | int(11) | YES | | NULL | | | fifth_column | char(4) | YES | | 1234 | | | second_column | varchar(100) | YES | | abc | | | third_column | char(4) | YES | | 1234 | | +---------------+------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)

mysql>
```
`fifth_column`列就被插到`first_column`列後邊了。
複製程式碼

刪除列

我們可以使用下邊的語句來刪除表中的列：

ALTER TABLE DROP COLUMN 列名;
複製程式碼

我們把剛才向first_table裡新增幾個列都刪掉試試：

mysql> ALTER TABLE first_table DROP COLUMN third_column;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE first_table DROP COLUMN fourth_column;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE first_table DROP COLUMN fifth_column;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC first_table;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| first_column  | int(11)          | YES  |     | NULL    |                |
| second_column | varchar(100)     | YES  |     | abc     |                |
+---------------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql>
複製程式碼

可以看到刪除成功了。

修改列資訊

修改列的資訊有下邊這兩種方式：

  1. 方式一：

    ALTER TABLE 表名 MODIFY 列名 新資料型別 [新屬性];
複製程式碼

    我們來修改一下first_table表的second_column列，把它修改為VARCHAR(2)以及具有NOT NULL約束條件：

    mysql> ALTER TABLE first_table MODIFY second_column VARCHAR(2) NOT NULL COMMENT '第二列';
ERROR 1406 (22001): Data too long for column 'second_column' at row 1
mysql>
複製程式碼

    咦，看到報了個錯，意思是second_column列裡存放的資料太大了，我們看看都存了哪些資料：

    mysql> SELECT * FROM first_table;
+----+--------------+---------------+
| id | first_column | second_column |
+----+--------------+---------------+
|  1 |            1 | aaa           |
|  2 |            2 | bbb           |
|  3 |            3 | ccc           |
+----+--------------+---------------+
3 rows in set (0.00 sec)

mysql>
複製程式碼

    原來存放的資料是'aaa'、'bbb'和'ccc'，它們都是包含3個字元的字串。現在我們要把second_column列的資料型別改為VARCHAR(2)，而VARCHAR(2)最多隻能存放兩個字元，所以才會報錯了！知道了錯誤原因後，我們把型別改為VARCHAR(3)試試：

    mysql> ALTER TABLE first_table MODIFY second_column VARCHAR(3) NOT NULL COMMENT '第二列';
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DESC first_table;
+---------------+------------------+------+-----+---------+----------------+
複製程式碼

| Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | first_column | int(11) | YES | | NULL | | | second_column | varchar(3) | NO | | NULL | | +---------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

mysql>
```
好了，這回就對了。這個過程也提醒我們：<span style="color:red">不能隨便修改列資訊，修改後的資料型別和屬性一定要相容表中現有的資料！</span>。
複製程式碼

  1. 方式二：

    ALTER TABLE 表名 CHANGE 舊列名 新列名 新資料型別 [新屬性];
複製程式碼

    可以看到這種修改方式需要我們填兩個列名，也就是說在修改資料型別和屬性的同時也可以修改列名！比如我們修改second_column的列名為second_column1：

    mysql> ALTER TABLE first_table CHANGE second_column second_column1 VARCHAR(3) NOT NULL COMMENT '第二列';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC first_table;
+----------------+------------------+------+-----+---------+----------------+
複製程式碼

| Field | Type | Null | Key | Default | Extra | +----------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | first_column | int(11) | YES | | NULL | | | second_column1 | varchar(3) | NO | | NULL | | +----------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

mysql>
```
我們只是把`second_column`的列名修改了一下而已，並沒有改動它的資料型別和屬性，所以直接把舊的資料型別和屬性抄過來就好了，可以看到結果名稱已經被修改了。
複製程式碼
修改列排列位置

如果我們覺得當前列的順序有問題的話，可以使用下邊這幾條語句進行修改：

  1. 將列設為表的第一列：

    ALTER TABLE 表名 MODIFY 列名 列的型別 列的屬性 FIRST;
複製程式碼

    先看一下現在表first_table的各個列的排列順序：

    mysql> DESC first_table;
+----------------+------------------+------+-----+---------+----------------+
複製程式碼

| Field | Type | Null | Key | Default | Extra | +----------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | first_column | int(11) | YES | | NULL | | | second_column1 | varchar(3) | NO | | NULL | | +----------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)

mysql>
```
可以看到，列的順序依次是： `id`、`first_column`、`second_column1`。現在我們想把`first_column`放在第一列可以這麼寫：
```
mysql>  ALTER TABLE first_table MODIFY first_column int(11)  COMMENT '第一列' FIRST;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC first_table;
+----------------+------------------+------+-----+---------+----------------+
複製程式碼

| Field | Type | Null | Key | Default | Extra | +----------------+------------------+------+-----+---------+----------------+ | first_column | int(11) | YES | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | second_column1 | varchar(3) | NO | | NULL | | +----------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

mysql>
```
看到`first_column`已經成為第一列了！
複製程式碼

  1. 將列放到指定列的後邊：

    ALTER TABLE 表名 MODIFY 列名 列的型別 列的屬性 after 指定列名;
複製程式碼

    比方說我們想把first_column放到second_column1後邊可以這麼寫：

    mysql> ALTER TABLE first_table MODIFY first_column int(11)  COMMENT '第一列' AFTER second_column1;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC first_table;
+----------------+------------------+------+-----+---------+----------------+
複製程式碼

| Field | Type | Null | Key | Default | Extra | +----------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | second_column1 | varchar(3) | NO | | NULL | | | first_column | int(11) | YES | | NULL | | +----------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

mysql>
```
複製程式碼

一條語句中包含多個修改操作

如果對同一個表有多個修改操作的話，我們可以把它們放到一條語句中執行，就像這樣：

ALTER TABLE 表名 操作1, 操作2, ..., 操作n;
複製程式碼

上邊我們在演示刪除列操作的時候用三條語句連著刪了third_column、fourth_column和fifth_column這三個列，其實這三條語句可以合併為一條：

ALTER TABLE first_table DROP COLUMN third_column, DROP COLUMN fourth_column, DROP COLUMN fifth_column;
複製程式碼

這樣人敲的命令也少了，伺服器也不用分多次執行效率也高了，何樂而不為呢？

總結

  1. MySQL伺服器可以包含若干資料庫，每個資料庫中可以包含若干個表。

  2. 資料庫相關操作如下：

    • 展示資料庫：SHOW DATABASES;
    • 建立資料庫：CREATE DATABASE 資料庫名;
    • 切換當前資料庫：USE 資料庫名
    • 刪除資料庫：DROP DATABASE 資料庫名

  3. 表結構的相關操作如下：

    • 建立表：

      CREATE TABLE 表名 (
    列名, 列的型別, [列的屬性] COMMENT '列的註釋',
    ... （若干個列的資訊）
) COMMENT '表的註釋';
複製程式碼

    • 刪除表：DROP TABLE 表名;

    • 修改表：

      • 修改表名：

        • 方式一：

          ALTER TABLE 舊錶名 RENAME TO 新表名;
複製程式碼

        • 方式二：

          RENAME TABLE 舊錶名1 TO 新表名1, 舊錶名2 TO 新表名2, ... 舊錶名n TO 新表名n;
複製程式碼

      • 新增列：

        ALTER TABLE 表名 ADD COLUMN 列名 列的型別 [列的屬性] [FIRST|AFTER 指定列名];
複製程式碼

      • 刪除列：

        ALTER TABLE DROP COLUMN 列名;
複製程式碼

      • 修改列資訊：

        • 方式一：

          ALTER TABLE 表名 MODIFY 列名 新資料型別 [新屬性] [FIRST|AFTER 指定列名];
複製程式碼

        • 方式二：

          ALTER TABLE 表名 CHANGE 舊列名 新列名 新資料型別 [新屬性] [FIRST|AFTER 指定列名];
複製程式碼

  4. 各種約束性條件

    • 預設值：在插入語句中沒有指定該列的值的情況下，使用預設值，宣告語法如下：

      列名 列的型別 DEFAULT 預設值
複製程式碼

    • 非空約束：宣告瞭該屬性的列不允許插入NULL值，宣告語法：

      列名 列的型別 NOT NULL
複製程式碼

    • 主鍵：唯一標識一條記錄，並且一個表中最多字能有一個主鍵，主鍵值不能為NULL，宣告語法：

      • 方式一：

        列名 列的型別 PRIMARY KEY
複製程式碼

      • 方式二：

        PRIMARY KEY (列名1, 列名2, ...)
複製程式碼

    • 唯一性約束：唯一標識一條記錄，一個表中可以有多個唯一性約束，並且值可以為NULL，宣告語法：

      • 方式一：

        列名 列的型別 UNIQUE [KEY]
複製程式碼

      • 方式二：

        UNIQUE [KEY] [約束名稱] (列名1, 列名2, ...)
複製程式碼

    • 外來鍵：表A的某個列或列組合的值依賴表B的某個列或列組合的值，則成表A為子表，表B為父表，表A的該列或者列組合稱為外來鍵，宣告外來鍵的方式如下：

      CONSTRAINT [外來鍵名稱] FOREIGN KEY(列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...);
複製程式碼

    • 自增：在插入語句沒有包含自增列的情況下，該列的值會遞增，宣告方式如下：

      列名 列的型別 AUTO_INCREMENT
複製程式碼

小冊

本系列專欄都是MySQL入門知識，想看進階知識可以到小冊中檢視：《MySQL是怎樣執行的：從根兒上理解MySQL》的連結 。小冊的內容主要是從小白的角度出發，用比較通俗的語言講解關於MySQL進階的一些核心概念，比如記錄、索引、頁面、表空間、查詢優化、事務和鎖等，總共的字數大約是三四十萬字，配有上百幅原創插圖。主要是想降低普通程式設計師學習MySQL進階的難度，讓學習曲線更平滑一點～

MySQL入門系列：資料庫和表的基本操作

相關文章