前言
雖然平時開發中經常接觸MySQL，但大多數的資料庫操作都是通過ORM對映實現的，自己並沒有從底層接觸SQL。
本文繼 初級程式設計師的SQL拾遺-①（增刪改查）
主要內容包括：建庫、建表、欄位、約束、查詢等
實際開發過程中，與增刪改查相比，建表、刪表、約束之類的這些低頻操作更加不重要，因為資料庫大部分時間還是在增刪改查（準確的說是查）。
所以本文的內容更不需要記，只需理解即可。
得益於強大的視覺化工具Navicat（或者PHPMyAdmin），我們可以用滑鼠完成建表，並轉換成SQL語句。
可以說，如果會用Navicat，SQL語句就不需要記住了（因為可以用軟體生成）。
一、玩轉Navicat
這個軟體有30試用版，也能找到和諧版，可自行搜尋。
開啟軟體連線到MySQL服務後，左側會顯示當前MySQL的所有資料庫：
新建資料庫（圖形化）
在左側任意資料庫上右鍵->新建資料庫，輸入資訊即可。
(在此處需要科普一下，為什麼要用utf8mb4？
因為MySQL內建的utf8是閹割版，用3字元表示
而真正的UTF-8是1~4可變字元，換言之MySQL的utf8並不能表示所有UTF-8字元
而utf8mb4才是真正4字元的UTF-8
這是MySQL的一個坑)
可以看到表test1已經顯示出來了，
這個操作用命令列怎麼實現呢？
首先需要了解一下什麼叫做“查詢”
查詢（Query）
SQL終端輸入的一組可以完成特定功能的SQL語句稱為一個查詢。
注意此處的Query並不是增刪改查的查（SELECT），也絕不僅限於查。
可以理解成，所有SQL語句的執行過程都是查詢過程。
也就是粗暴的簡化成，查詢 == 執行SQL語句
Query不僅包括了CRUD，還包括各種庫操作、表操作。
接下來，在查詢中用SQL建一個名為test2的資料庫。
新建資料庫 （命令列）
首先在Navicat中新建查詢（也就是新建一個輸入SQL語句的功能）
輸入SQL程式碼後執行
// 建立資料庫
CREATE DATABASE 資料庫名
// 示例：建立名為test2的資料庫
CREATE DATABASE test2 重新整理後就會出現新增的資料庫。
雙擊新建的庫後，圖示變成綠色，文章後面的內容都可以通過在此資料庫的Query中操作。
同理，刪除資料庫的語句是：
// 刪除資料庫
DROP DATABASE 資料庫名
// 刪除名為test2的資料庫
DROP DATABASE test2演示Navicat生成SQL語句
這是軟體最神奇之處。
首先新建表，然後任意編輯表內欄位。
編輯完成後不需要儲存，此時點選SQL預覽。
此時就可以發現，軟體已經自動為我們生成了SQL語句：
這對於初學者學習SQL非常有幫助，有利於在大腦中快速建立某種功能和SQL程式碼的關聯。
到此如果看明白並熟練使用Navicat，後面的內容就不用看了（不是）。
二、常見表操作
建立（CREATE）
現在我們可以拿出剛才Navicat生成的SQL程式碼來看看：
// 軟體生成的SQL語句
CREATE TABLE `test2`.`無標題` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NULL,
`sex` int(1) NULL,
`phone` varchar(255) NULL,
PRIMARY KEY (`id`)
);我們可以歸納出，建立表的寫法是：
// 建立資料表
CREATE TABLE `資料庫名`.`資料表名` (
`欄位1` 欄位1的屬性,
`欄位2` 欄位2的屬性,
......
`欄位n` 欄位n的屬性,
)
注意此處類似單引號的符號並不是單引號'，而是TAB上方的那個鍵。
助記：
- CREATE指令適用於一切表示新增的功能，如新增資料庫、資料表、索引等
- 庫的操作物件是DATABASE，而表的操作物件是TABLE
- 新建資料表時需要寫上表的欄位和屬性，而新建資料庫不需要
用這種方法建立一個名為table1的資料表，接下來準備看如何刪除它。
刪除（DROP）和清空（ TRUNCATE）
SQL的寫法具有很強的規律性，我們就是要把這種規律找出來以便理解和記憶。
// 刪除某個資料表
DETELE TABLE 資料表名
// 舉例：刪除table1資料表
DETELE TABLE table1助記：
- 和CREATE用法相同，DROP適用於一切表示刪除的功能，如刪除資料庫、表、索引
- 庫的操作物件是DATABASE，標的操作物件是TABLE
- 對於資料庫和資料表，刪除的語句結構都是 DELETE + 操作物件 + 物件名
對於表有一種特殊的操作，可以通俗的理解成清空表，即只刪除資料不刪除結構：
// 情況某個資料表的資料，但不改變其結構
TRUNCATE TABLE table_name
// 舉例：清空table1
TRUNCATE TABLE table1助記：
- 清空資料只可以對資料表使用
- 情況語句結構和刪除語句只有指令不同，其他相同
小結
此處類比資料庫的建立、刪除，總結了資料表的建立、刪除、清空操作。
三、欄位操作
前置條件：建一張名為table2的表，任意設定一些欄位，類似如下：
軟體自動生成了如下的SQL語句，如果讀者懶得手動加欄位，可以直接執行即可獲得和本文一樣的環境。
// 軟體生成的SQL語句
CREATE TABLE `test2`.`無標題` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) NULL COMMENT '姓名',
`age` int(11) NULL COMMENT '年齡',
`number` varchar(11) NULL COMMENT '學號',
PRIMARY KEY (`id`)
);所以我們又能總結出新增一個欄位的寫法：
// 最精簡的欄位寫法
`欄位名` 型別(長度) 是否為空約束,
// 帶註釋的欄位寫法
`欄位名` 型別(長度) 是否為空約束 COMMENT '註釋內容',這裡一定要睜大眼睛，欄位名是TAB上方的符號，而註釋是單引號！
現在已經知道欄位的寫法，那麼欄位型別共有多少種呢？
實際上很多，圖片裡只是一小部分。
但最常用的仍然是int、varchar、date、timestrap，
至於其他型別，需要用到的時候，先用navicat新增一個，再看看生成的SQL即可。
這就叫授人以漁（確信）。
約束
“師爺，您給翻譯翻譯，什麼叫約束？”
“約束就是約束啊。”
約束本質是對欄位取值範圍的限定，
如果沒有約束，欄位可以取其型別中的任何值
但有時不允許欄位出現NULL或者重複值等情況，這時就需要約束了。
常見約束包括：
- NOT NULL 不允許允許欄位為空
- UNIQUE 不允許重複的值（例如學生的學號）
- DEFAULT 預設值
- 主鍵約束
- 外來鍵約束
非空、唯一值、預設值很好理解。
在Navicat中點選設計表，新增兩個欄位，分別設定非空和預設值：
自動生成了以下SQL語句：
// 自動生成的SQL語句
ALTER TABLE `test2`.`table2`
ADD COLUMN `test_null` varchar(255) NOT NULL AFTER `number`,
ADD COLUMN `test_default` varchar(255) NULL DEFAULT 123123123 AFTER `test_null`;這樣就可以歸納出這些約束的寫法：
- 非空約束使用
NOT NULL，否則寫NULL - 預設值使用
DEFAULT 預設值 - 唯一值Navicat沒有直接設定的選項，實際上使用
UNIQUE
接下來有一個問題：多種約束同時寫的時候，有沒有順序要求？
來做個測試，同時設定非空和預設值，生成的語句，可以看到null在前，default在後：
// 自動生成的SQL語句
ALTER TABLE `test2`.`table2`
ADD COLUMN `test_default` varchar(255) NOT NULL DEFAULT 123123123 AFTER `name`;接下來調換位置，在Query中執行：
// 調換約束的順序
ALTER TABLE `test2`.`table2`
ADD COLUMN `test_default` varchar(255) DEFAULT 123123123 NOT NULL AFTER `name`;是可以成功的，SQL開發者沒那麼傻，肯定會想到這一點。
小結
我們可以調整上文提到的一般欄位的寫法：
// 一般情況下欄位寫法，其中約束可以改變順序
`欄位名` 型別(長度) 約束1 約數2 ... COMMENT '備註內容',接下來說很重要的主鍵和外來鍵
主鍵（PRIMARY KEY）
對於一張表來說，主鍵是一個記錄（一行資料）在這張表的唯一標識。
即從業務上來說，後端可以通過主鍵來獲取一個唯一確定的物件。
SQL規定主鍵必須有以下要求：
- 主鍵必須是唯一值（不需要寫出
UNIQUE，宣告主鍵後自帶UNIQUE效果） - 主鍵必須是非空（需要在欄位上寫出
NOT NULL）
此外，在實際開發過程中，通常有以下約定：
- 主鍵的列名是
id - 主鍵是int型別
- 主鍵是自增的（需要寫出
AUTO_INCREMENT）
我們又回到第一次建表時生成的SQL語句，可以歸納出主鍵的寫法：
// 軟體生成的SQL語句
CREATE TABLE `test2`.`無標題` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NULL,
`sex` int(1) NULL,
`phone` varchar(255) NULL,
PRIMARY KEY (`id`)
);// 歸納主鍵定義的寫法
CREATE TABLE `資料庫名`.`資料表名` (
`id` int(11) NOT NULL AUTO_INCREMENT,
......
PRIMARY KEY (`id`)
);所以，要點如下（助記）：
- 欄位名為id，需要新增非空、自增約束
- 在所有欄位的最後宣告主鍵是id
- 注意引號的格式
擴充套件： 聯合主鍵
通常來說主鍵只有一列，比如ID，那麼這一列就是唯一的，
例如，資料表中已經有了ID為666的學生，此時插入一個ID為666的學生、或者將另一個學生的ID改為666，就會報錯。這就是唯一性。
聯合主鍵涉及到兩個以上欄位，只有這兩個列上的資料都相同，才會報錯。
比如，設定聯合主鍵name和number，也就是說只有姓名和學號同時重複才會被判定為重複資料。
假設資料庫有一個學生姓名為張三，學號123456
此時，如果插入新學生，姓名為張三，學號777777，可以插入
再插入學生，姓名為李四，學號123456，可以插入
但如果插入姓名為張三，學號為123456，資料庫就會報錯：主鍵重複。
外來鍵
外來鍵僅出現在多表查詢時，實質上是在當前資料表中存放的關聯資料表中的唯一索引，大多數情況下是主鍵。
簡化一下，外來鍵是在當前表中存放的，其他表的主鍵。
如果說，主鍵的目的是在單表查詢中準確的根據ID獲取唯一物件，
那麼外來鍵就是在多表查詢中準確的獲取到與之關聯的唯一物件。
例如學生和班級是多對一，每個學生屬於一個班級，那麼就在學生表中增加一個班級ID的欄位，這就是外來鍵。
在查詢學生時，就可以通過學生的班級ID欄位來獲取它所在的班級。
接下來是研究外來鍵的寫法，首先建立班級表”
// 建立班級
CREATE TABLE `klass` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NULL,
PRIMARY KEY (`id`)
);然後建立學生表，並加入外來鍵klass_id指向klass 表的id欄位。
先正常建立欄位：
然後在外來鍵頁面，增加一個外來鍵：
刪除時和更新時有一個下拉選單，有四個選項，分別是級聯、無操作、限制、置空。
簡單解釋一下：
級聯指的是當班級被刪除時，一併刪除關聯的學生。
無操作會在刪除班級時，學生外來鍵不變，此時通過學生查班級會報錯
限制指的是班級中有學生時，它不能被刪除
置空指的是刪除班級時，班級中原有的學生的klass_id欄位被改為NULL
現在隨意選擇一個即可，檢視SQL：
// 軟體生成的SQL語句
CREATE TABLE `test2`.`無標題` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NULL,
`number` int(11) NULL,
`sex` int(1) NULL,
`klass_id` int(11) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `klass_id` FOREIGN KEY (`klass_id`) REFERENCES `test2`.`klass` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);// 外來鍵的寫法
CONSTRAINT `klass_id` FOREIGN KEY (`klass_id`) REFERENCES `test2`.`klass` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
// 歸納
CONSTRAINT `外來鍵名` FOREIGN KEY (`本表中的欄位`) REFERENCES `資料庫名`.`關聯表名` (`關聯表欄位`) ON DELETE 刪除操作模式 ON UPDATE 更新操作模式
實際開發中外來鍵約束常用的作用是：
當使用限制模式時，某個記錄被外來鍵關聯時，它不可以被刪除。
例如，當班級中有學生時，這個班級是不可以被刪除的。
小結
主鍵約束和外來鍵約束是超高頻使用的功能，理解它們的含義很重要。
四、總結
和上一篇文章不同，本文的內容幾乎完全不用記，一是因為都是低頻操作，二是因為已經有很智慧的生成工具，我們不是DBA，不會天天和建表刪表打交道。
筆者認為更重要的是講述如何生成SQL，掌握這項操作後，無論是什麼沒見過語句，都可以靈活面對了。