1.表結構的操作

在MySQL中，表就相當于文件夾中的具體的文件，這個文件里面存的就是有效數據 。這些有效數據在邏輯上就是一個表格。

例如用戶管理中，不同列代表不同用戶屬性（如用戶ID、電話、公司、住址等），每一行表示一個用戶，讀取一行，再根據每列確定用戶信息。

下面將從表的常見操作出發，逐漸延伸至創建表的列所需要了解的約束和數據類型。

（1）增加表

必須use database之后才可以增加表，每個表必須存在數據庫中。

create table tb (
id int, # 類型在后，命名在前，和C/C++的風格是相反的
name varchar(30) # 不同列屬性有不同類型，還要跟上所需的約束
#最后一個列之后不加逗號
) [charset=gbk collate=gbk_bin engine=MyISAM];

其中charset、collate、engine的設置如果不顯式指定的話，就會優先使用database創建時設置的，如果沒有再使用系統默認的， 所以我們可以在database創建時設置該庫下的所有table存儲方式。

（2）查看庫中所有的表

首先我們肯定想知道增加表后當前數據庫存的所有表有哪些

show tables;

（3）查看表每個列的約束

其次我們想要知道某個表的具體列的具體屬性，每個列所遵循的屬性也叫做約束

desc db; # 顯示列的屬性，而不是內容

下面顯示的就是列的各種屬性（約束）

（4）刪除整張表

drop table tb;

（5）刪除某個具體的列

有的時候我們并不想刪除整張表，而是某個列，本質上這是對table的修改，所以會用到alter語句

alter table tb drop id;

（6）增加某個具體的列

既然能夠刪除某個具體的列，自然也能增加某個具體的列，我們也能指定增加的列在現有的某個列之后

 alter table tb add age int [before name]; # before可以換成after，增加的列要包含所需的約束

（7）修改某個具體的列，但不對列明做修改

alter table tb modify id varchar(10);

修改某個具體的列需要重新設置列的所有屬性（約束），會對指定名稱的列完全覆蓋，這里演示比較簡單，后面帶入約束后要記得加上，否則會導致錯誤。
但這樣只能修改除列名以外的其它屬性，要修改列名需要用到change

（8）修改某個具體的列，要對列明做修改

如果不是為了修改列的其它約束，單單修改名字的話可以用到change

alter table tb change id num varchar(10); # 要跟完整約束

（9）修改表的名字

除了修改某一個具體的列的名字，我們還可以修改整張表的名字

alter table tb rename [to] tb_1;

（10）修改表的存儲引擎

alter table tb engine=Innodb;

（11）修改表的字符集、校驗集

alter table tb convert to character set utf8mb4 collate utf8mb4_general_ci; # 不支持等號簡寫

靠前的相對而言更重要，修改存儲引擎和字符校驗集的情況極少。

2.數據類型（Type）

可以看到數據類型是被分為四類的，后面會分別講述每個類型的作用以及適用的場景。

（1）數值類型

a.int家族

int家族在MySQL這里也是有對應類型的，其中tinyint大小對應char，smallint對應short，bigint對應long long，也新增了一個3字節的mediumint，整體而言是很容易記住的。

數據庫是數據安全體系中最后一道防線，因為用戶的數據在經過數據庫的檢查后會被直接保存起來了，這是發現數據異常的最后一個機會。因此數據庫要解決的一個關鍵問題就是這個數據符不符合規范？能不能存進去？這就是數據庫約束存在的根本原因。

因此數據庫對數據類型有著更嚴格的限制，所以在MySQL中不存在C/C++中的截斷、隱式類型轉換等操作，只要發現存入的數值不在應有范圍內，會直接放棄插入該整行并向用戶報錯。（默認均為有符號類型）

b.bit類型

bit[(M)] : 位字段類型。其中M表示每個值的bit位數，范圍從1～64。如果M被忽略，默認為1。

寬泛上說，bit類型也屬于整形家族，它的特點和char一樣，在顯示的時候都是以ASCII碼對應的值顯示的。

c.float（小數類型）

float[(m, d)] [unsigned] : m指定數的最大顯示長度（不包含小數點），d指定小數位數，也就是說整數部分的位數最大是m - d。該類型占用空間4個字節

例如，float(6, 3)就是指小數位最多3位，因此對應整數位最多3位，該類型范圍是-999.999 ～ 999.999。 如果插入999.9978會被四舍五入至999.998，但注意對于有的版本插入-1000或1000會被四舍五入，有的就會嚴格報錯，我們不要插入這種有爭議的數值。

當我們使用unsigned時，該數據類型范圍直接砍半，如float(6, 3) unsigned直接變為0 ～ 999.999，所以unsigned在這里更多是作為約束使用的。

d.decimal（小數類型）

decimal(m, d) [unsigned] : 同理，m指定數的總長度，d表示小數點的位數

float表示的精度大約是7位，當超過7位時就會出現精度丟失，原因在于無法用二進制指數在有限精度下準確表示任意十進制的數值。 decimal精度支持整數最大位數m為65，支持小數最大位數d是30。因此，需要高精度存儲時優先使用decimal。

（2）文本、二進制類型

a.char

char(L): 固定長度字符串，L是可以存儲的長度，單位為字符，最大長度值可以為255

當我們想要存別人的姓名、地址時就可以用這個類型，看上去是char，實際上對應C++中的string，真正的char在MySQL這里是bit(8)，我們要注意不要搞混了。

還有一個區別點，MySQL中的字符是嚴格意義上根據顯示效果定義的，而非根據底層存儲定義的，也就是說一個漢字也叫一個字符，就算它實際存了3個字節，一個字母也是一個符號。

b.varchar

varchar(L): 可變長度字符串，L表示字符長度，最大長度65535個字節 （注意不是字符，因此受到編碼選擇的影響）

它的功能和char基本沒區別，都是存儲字符串，但它在底層實現和空間占用策略和char不一樣。變長的含義是在不超過自定義范圍的情況下，用多少，開辟多少，相應時間效率會低一些；相比而言char定長的意義是，直接開辟好對應的空間，時間效率相對較高。

因此在選用char和varchar時要明確自己的需求，比如填地址其實更適合varchar，varchar可以開得很大而無需擔心空間問題，它只需要關心最大長度的地址能存進去就行了；性別就適合char(1)，因為一個人必須有一個性別且要么是男，要么是女。

要實現varchar需要有個開銷，需要有個變量存儲當前的size，就像當初我們vector的實現那樣，這個開銷根據我們的實際占用在1 ～ 3字節之間，并且這個開銷會算在總的65535字節中。

當表的編碼是utf8時，varchar(n)的參數n最大值是65532（除去size開銷） / 3 = 21844（utf中，一個字符占用3個字節，變長存儲）
如果編碼是gbk，varchar(n)的參數n最大是65532 / 2 = 32766（gbk中，一個字符占用2字節，變長存儲）。
但由于變長存儲和size開銷不定，所以上述計算僅為參考，一般我們不會觸碰到varchar的極限，如果要存的字符串真的很大，建議使用text類型。

（3）日期和時間類型

a.date

date: 日期格式為 ‘yyyy-mm-dd’ ，該類型占用三字節

在插入過程中需要嚴格按照格式來寫

b.datetime

datetime: 時間日期格式 ‘yyyy-mm-dd HH:ii:ss’ ，其中每個數字表示范圍從 1000到 9999，該類型占用八字節

c.timestamp

timestamp: 時間戳，這個值是從1970年1月1日開始到當前時間總共的s（或ms）數。

但是需要注意的是，timestamp底層是按照時間戳的值在存，占用4字節。但顯示時是按照yyyy-mm-dd HH:ii:ss格式顯示，和datetime完全一致。

但這就引出了一個問題，timestamp和datetime在表層有什么區別？

d.datetime和timestamp的區別

datetime本質上是為了提供一種精準存儲時間的類型，我們可以用這個類型存放任意時間，比如某個人坐上飛機的時間，這個時間和當前時間沒什么關系，看的是我們關心的事件發生的時刻。

而timestamp形象地說是提供了一種標記，這個類型不需要插任何值，會自動補全。當我們插入一行時這個變量自動初始化為當前時間，當我們更新這一行時同樣會更新timestamp，因此timestamp很適合用來做“最后修改時間”的類型。

（4）String類型

a.enum

enum: enum( ‘選項1’, ‘選項2’, ‘選項3’, … ); # 在定義數據類型時就要確定選項，后續的插入等只能在選項中選擇

存儲的數據就是enum中的一個。在底層，存儲的實際上是當前選項對應的數字（下標），內容只存一份，通過映射來一一匹配每個選項，因此enum的選項上限是65535。

b.set

set: set( ‘選項值1’, ‘選項值2’, ‘選項值3’, … ) # 相比較enum而言可以多選

同樣，底層上存儲的是我們選項的下標，我們用unordered_map來理解就行，本質上就是一種映射，set最多允許同時選64個選項。

c.find_in_set

既然有選擇，那必然也有篩選，很多時候我們希望通過用戶不同的選擇來篩選特定用戶，這就要講到一個內置函數find_in_set

上述函數實現了在集合’a, b, c’中尋找不同元素，其中找到了返回下標（從1開始），沒找到返回0。根據實例我們也能發現只能找單個元素，即只能匹配一個選項，第一個例子函數直接把’a, b’當成了一個整體選項，在只有a、b、c三個選項的集合中找，自然是找不到的。

一般實際使用為：

select * from friends where find_in_set('游泳', hobby);

這樣的話就可以找到行中hobby含有游泳的人了。

3.約束

其實通過上述講解我們就應該意識到，數據類型本身就是一種約束。要求我們傳入int，那我們只能存入int，要求float范圍不能超，我們就不能存入超范圍的數，這種約束能夠更快幫助我們發現存儲數據中的錯誤，并及時向上反饋。由于約束的存在，MySQL能保證只要數據成功存入，那么它就一定符合規范，即約束很好地回答了“這個數據該不該存進去，符不符合規范”這個問題。

除了數據類型，我們還需要知道其它種類的約束，它們共同作用組成了MySQL數據安全的一道防線。

（1）空屬性（Null）

創建語句：name varchar(20) not null # 也可寫作null，表示允許為空

數據類型也是一種約束，not null和null也是一種約束，約束條件放一起，因此not null直接跟在上一個約束后面是符合邏輯的。

空屬性存在的實際意義是防止一些必要信息的漏填和缺失，如某個人的電話號碼，學生的期末成績等。加上not null之后就必須插入數據且也要符合其它約束，否則整行插入失敗。默認情況下是null，即不顯式指定時not null允許不插入數據。

除了實際意義，空屬性也具有一定的語法意義，例如，1+null=null，即任何空值均不能參與任何運算。也就是說只要學生的成績欄有一個人的是null，那么就沒辦法計算它們的總值、平均值等，所以空屬性也是在一定程度上避免觸發這種語法特性而導致錯誤。

（2）默認值（Default）

默認值是指在用戶不指定存入數據的情況下默認存入的值，用戶可以在創建表結構時自定義默認值（要符合規則），因此也可理解為缺省值。

score mediumint unsigned not null default 0

可以說，只要設置缺省值，表格中該列就一定不為空了嗎？換句話說，上面實例中not null能否去掉？

如果我們不插入值，該字段會默認使用缺省值，但當我們顯式插入值時，就會使用我們指定的值，要是我們指定插入null呢？ 所以默認值不能完全實現not null的效果，如果我們希望一個字段不為空，我們依然需要加上not null

（3）列描述（Comment）

age int not null comment '姓名'

可以理解為對這一列的注釋，沒有強制限制，但從另一種角度上說，這也是對用戶的限制，明確該字段是什么含義同樣可以有效降低錯誤數據的存儲概率。

但是，這個comment在哪呢？使用desc只能看到如Null和Default等屬性，comment并不在里面。

在之前就提過show create可以查看相應的創建語句，在這里同樣適用，我們可以通過show create table方式查看comment約束。

（4）zerofill

我們來看下面這個例子，創建表結構時在數據類型后面緊挨著zerofill，導致顯示的時候前端用0填充了。

我們可以看到一共填充了9個0，加上有效位數一共是10位數，10位數正是int類型所顯示的最大數所需的位數。

zerofill是一種格式化輸出的設置，它不會對我們存儲的有效數據做出約束，但在顯示時會根據我們的有效數據，在高位補齊0以保證顯示位數一致。

通過下面的例子就能明白其用法了。

我們能夠自由選擇填充的位數，并且能夠在desc里面看到它，默認情況下都是int(10)，有的系統在默認情況下desc會顯示int(10)，有的則不會。取10是因為int的最大值需要用10位來表示。同時，如果顯示位數低于當前數據位數，就會忽略限制，完整的顯示。

對于float而言也存在zerofill，但注意它會保證小數位和預期一致，整數位少1，因為它算上了標點符號，格式化輸出總共6個符號

zerofill在特定情況下會使用，如金額會統一顯示到小數點后兩位，id號的顯示。zerofill只能用于int家族和float家族（數值類型），對于char、varchar、時間、String類型無效。

并且有一個細節需要注意，當我們為int/float添加zerofill約束時，會自動變為無符號類型

（5）鍵（Key）

key是MySQL的一個重要約束，它也是MySQL存儲引擎實現的一個重要條件。 鍵有不同種類，我們可以用鍵值對來輔助理解鍵，下面將詳細介紹。

a.主鍵（primary key）

一張表只能有一個字段被設為主鍵，主鍵對應列的元素不能為空，且每個元素保持唯一性。

使用時直接在字段后面指定即可

id int(10) zerofill primary key comment '學號'

primary key能夠完全達到not null的功能，所以指定主鍵后沒必要再指定空屬性了。

同時，主鍵可以直接追加（前提需保證已有數據滿足主鍵要求），用法如下圖：

也能直接刪除主鍵

MySQL還支持復合主鍵，即多個字段一起作為一個鍵，在創建表結構的時候需要單起一行指定，也可以用上面的方法添加。 復合主鍵的條件自然是均不為空和整體唯一。

b.自增長（auto_increment）

自增長必須綁定在主鍵或者唯一鍵之后

下面是它的用法：

可以看到，指定了自增長后我們就不需要插入數據了，它會自動加上數據。其中用show create可以看到AUTO_INCREMENT=2字樣，在此之前只插入了一個數據，因此這個字段指的是下一個數據默認插入值。

我們插入后查看相關屬性和內容

自增長每次自動加1，因此自增長字段必須是一個整數，且一張表最多有一個自增長字段。

除此之外，自增長允許自定義插入，也就是說我們可以指定插入一個具體的值，自增長會根據我們插入的值自動加1作為下一個自動插入數據。

最后值得分享一個內置函數last_insert_id()，它可以獲取上次自增長插入的值。

c.唯一鍵（unique）

name varchar(20) unique comment '姓名'

唯一鍵如其名字那樣，是為了保證唯一性而存在的。主鍵具有唯一性和不可空，唯一性具有不為空數據的唯一性和可空。主鍵一張表只能有一個，唯一鍵一張表可以有多個。

唯一鍵存在的意義是為了在主鍵之外對其它列施加唯一約束。例如，我們用身份證號作為主鍵，員工的姓名、電話作為唯一鍵，它和主鍵并不沖突。 但是，又有一個新的問題誕生了，為什么我們不用員工的電話作為主鍵呢？如何判斷一個字段更適合主鍵還是更適合唯一鍵呢？

我們選取主鍵時盡量選擇和業務無關的量，這樣當業務有修改時，對主鍵就不會有什么影響。 上述例子中身份證顯然是最無關的量，甚至可以說一個人一輩子都不會改變身份證號，有非常強的穩定性，而一個人的電話卻很可能因為工作等原因換掉，所以不適合主鍵。

d.外鍵（foreign key）

外鍵是跨表的一個約束。先舉個例子，班主任手上有兩張表，一張是學生的基本信息，如姓名、學號、電話、地址、家長電話等。還有一張表是多次考試學生的成績表，這張表的列由學生、第幾次考試、各科成績組成。這兩張表在一定程度上是有聯系的，信息表中的學生名字和成績表中的應該一致，不應在成績表中出現信息表沒有的，信息表中不應出現成績表沒有的。這種約束關系需要外鍵來維護。

外鍵涉及兩張表，這兩張表具有從屬關系，可以理解為一張表掛靠在另一張表上，是另一張表的延伸。我們可以根據兩張表的特性來判斷誰是主表，誰是從表。主表中相關的列（上述例子中是學生名字）必須是唯一的（primary key或者unique），外鍵是加在從表上的。因此，我們可以判斷信息表是主表（學生信息只有一份），成績表是從表（一個學生有多次考試，對應就有多行信息，不滿足列中元素的唯一性）。

第一個判斷是從唯一性出發，如果滿足有不滿足唯一性的只能是從表。但有時兩張表都滿足唯一性，比如學生成績表只統計一次成績呢？因此，還有一個判斷的點，即外鍵是在從表中添加，而主表不會被修改，因此可以觀察有哪些表是非常穩定，不能輕易被修改的，這種多半是主表。

# 成績表
create table scores (
name varchar(20),
times int,
score int,
foreign key (name) references info(name)
);

這樣就能將外鍵綁定到references的表對應的列了。

之后主從表關系建成。主表可以任意添加從表沒有的信息，我們可以將主表理解為一位負責人的老師，老師可以不斷增加自己的學生。從表只能添加主表中有的信息，主表中沒有的從表無法添加，相當于只有老師承認了才能成為他的學生。當主表想要刪除信息時，必須先保證從表中沒有這個信息，相當于老師不會隨便拋棄自己的學生，只要認領了學生，就要負責到底。

只要滿足上述要求，就能對數據進行操作。一般來說，“外鍵”功能也會在應用層進行維護，有的公司甚至不會使用MySQL的外鍵，全權交由應用層，因為外鍵的功能本應由用戶自己管理，MySQL只是提供一個兜底的選擇。