SQL 筆記：建立資料表與 SQL 的資料型態

建立 foods 資料表

建立資料表不等於建立資料，只是把表格的 欄 (column) 屬性定義建立好，實際每個 row 包含的資料(值)都還要之後 INSERT 進去。

以視覺化舉例來說，想建立一個如下的空表格：

我們定義的僅是屬性欄位和該屬性的規範：

所以要建立新表格並定義其包含屬性欄位 id、name、price、cost，
那語法可以這樣下：

CREATE TABLE `foods` (
  `id`    int(11)      unsigned NOT NULL AUTO_INCREMENT,
  `name`  varchar(20)           NOT NULL,
  `price` int(11)      unsigned NOT NULL,
  `cost`  int(11)      unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

根據上面可以看出，當我們要創建資料表內一個新的欄位時，有以下幾點需要設定與考慮：

• 欄位的命名
• 資料型態與長度
• 必填與預設值
• 哪一個欄位可作為主鍵（Primary Key，又稱 PK）
  每一張資料表都必須有 「主鍵」 (Primary Key)，大部分的狀況下，資料表的主鍵就會是他的 id，也就是每次新增資料都會遞增的流水編號。

資料表與欄位的命名

資料表的命名通常會是 複數名詞，用來記錄使用者的資料表會叫 users，而用來記錄訂單的則是 orders。資料表內的欄位名稱通常不會再提到自己是哪一張資料表，例如在 users 資料表中，姓名會直接叫 name，而不是 user_name，以免程式碼過於重複。

另外資料表中常常會出現需要參考另一張資料表的 「外鍵（Foreign key，又稱 FK）」，像我們的 orders 資料表中，就參考了 drinks 和 customers 兩張。外鍵的命名慣例上會是「資料表名稱(單數)_參考欄位」，像是 drink_id 或是 customer_id。

注意：設計時，時常會遇到的「單位」問題，像是訂單裡的數量是一杯、還是一壺？這時候我們可以將單位記錄在欄位的 註解 (comment)。

資料型態

常見的資料型態可以分成以下幾大類：文字、數字與日期。

文字

• varchar(n) : n 代表字元的數量
• text

text 可以記錄一大段文字，像是部落格系統儲存文章，或是第三方 API 回傳的 json 值。而其他狀況我們會使用 varchar ，像是使用者的名字、帳號，這邊要稍微思考一下 n 的值，像是手機號碼頂多就 20 個字，避免使用系統預設的值（也就是 255) 來避免儲存空間的浪費。

數字

• 整數： bigint, int, smallint, tinyint
• 近似浮點數：float, double
• 精確浮點數： decimal(m, d) m 代表數字的長度，而 d 是小數的位數，例如 12.999 可以用 decimal(5,3) 來代表。

根據開發者的欄位，會決定要使用範圍多大的數字，以及是否 unsigned，unsigned 代表數字不會有負值，而且資料的最大值範圍跟 signed 的會變大一倍，因此像是年齡、身高、距離就很適合使用。

舉例來說，tinyint 使用了 1 byte 去儲存資料， 1 byte 就是 8 bits，因此可以存放 28 = 256 個數字，所以數值範圍是 -128 ~ 127。

唔那為什麼正數比負數少了 1 呢？因為中間還有一個數字 0 的關係！如果是 unsigned ，就可以把所有的數值都拿去表示 0 以上的數字，因此範圍會增加一倍，變成 0 ~ 255。

至於浮點數的話就要特別注意！近似浮點數是使用近似值來計算浮點數，如果需要對這些數字做精確的「十進位 (decimal)」計算，像金錢欄位，請務必使用 decimal，其他則可以使用 float。

日期

• timestamp
• datetime
• date

timestamp 代表的是從 1970-01-01 00:00:00 UTC 到現在的秒數，所以他除了時間之外，也隱含了時區的資訊。timestamp 通常被用在記錄「資料建立的時間」、「資料變動的時間」，如果只是單純記錄，或時區不重要的資料欄位，就可以使用 date 或 datetime。

必填與預設值

若該欄位在新增資料時不能是空的，就請幫他加上 NOT NULL。若是希望在新增時可以給他預設值，則加上 DEFAULT（預設） ，像是記錄狀態的欄位通常會有預設值（帳號狀態預設為啟用）。