上次我們介紹如何用 MySQL Workbench GUI 操作資料庫，這次我們要來簡單介紹一下 CRUD 的基礎語法。

語法結構

• CREATE/INSERT

  • 建立新的資料庫

  CREATE DATABASE IF NOT EXISTS 資料庫名稱
  CHARACTER SET 編碼名稱
  COLLATE 比較規則名稱
  

  [CHARACTER SET] 代表編碼，[COLLATE] 代表比較字元的規則，如果不知道選什麼好，就用 UTF-8 系列的。

  • 建立新的資料表

  CREATE TABLE IF NOT EXISTS 資料表名稱(
      `欄位名稱1` 資料型態1 [其他欄位設定],
      `欄位名稱2` 資料型態2 [其他欄位設定],
      ...,
      PRIMARY KEY (`主鍵欄位`),
  ) ENGINE=資料庫引擎;
  

  MySQL 的資料型態請參考這篇文章。[其他欄位設定] 為選填，常用的有 NULL/NOT NULL（是否允許空值）、DEFAULT （預設值）。PRIMARY KEY 用來設定主鍵，通常是 ID 欄位

  • 新增資料列

  INSERT INTO 資料表名稱 (欄位1, 欄位2, ...)
  VALUES 
      (資料1-1, 資料1-2, ...),
      (資料2-1, 資料2-2, ...),
      ...
      (資料n-1, 資料n-2, ...);
  

  VALUES 的值順序與資料型態必須與資料表的設計符合。如果某個欄位是 auto incremental 則給 NULL，讓 MySQL 幫我們計算該有的值

• READ（SELECT）

  SELECT 
      欄位選擇
  FROM
      資料表名稱
  WHERE
      搜尋條件
  LIMIT
      限制選取數量;
  

  欄位選擇可以是多個欄位，用逗號（,）隔開，且不必照順序，唯一要注意的只有，它們必須是資料表所擁有的欄位。如果要選取所有欄位可以在欄位選擇只打一個星號（*）

  搜尋條件必須是一個回傳 TRUE/FALSE 的運算式；LIMIT 與 WHERE 可以忽略，預設取回所有資料

• UPDATE

  UPDATE 資料表名稱 
  SET 
      欄位名稱1 = 運算式1,
      欄位名稱2 = 運算式2,
      ...
  WHERE
      搜尋條件;
  

  使用 UPDATE 之前請一定要注意搜尋條件！沒有指定搜尋條件會更新整張資料表！！另外，運算式的部分也可以是固定數值或函數。

• DELETE

  DELETE FROM 資料表名稱
  WHERE 搜尋條件;
  

  與 UPDATE 一樣，使用 DELETE 之前請一定要注意搜尋條件！沒有指定搜尋條件會刪除整張資料表！！

範例

現在我們要來示範一下上面介紹的語法。但是以下不會包含 CREATE 的部分，建立資料庫、資料表筆者建議使用 GUI 就好，因為有許多編碼、資料庫引擎、資料型態的設定，要自己記住那些特定字串太麻煩了，直接讓 GUI 提供我們下拉選單設定比較好。

• 範例資料表
  筆者直接在內建的 world 範例資料庫裏面再新增一個資料表
  

Day20 更正
之前嘗鮮用了 MySQL 的型態 BIT(1)，結果到了 Day20 要用 Dapper 操作才發現，BIT(1) 傳回來的資料型態竟然是 ulong，無法隱含轉型為 bool，所以這邊改回安穩的舊式寫法 TINYINT(1)

• INSERT 新增資料

  INSERT INTO `user` 
  VALUES 
  (NULL, 'Alice','alice@test.mail', 0),
  (NULL, 'Bob','bob@test.mail', 0),
  (NULL, 'Cathy','cathy@test.mail', 0),
  (NULL, 'TestUser1','test1@test.mail', 0),
  (NULL, 'TestUser2','test2@test.mail', 0),
  (NULL, 'TestUser3','test3@test.mail', 0);
  

  這裡因為 user_id 是 auto incremental 所以填 NULL 上 MySQL 幫我們自動增加。

• UPDATE 更新資料
  現在假設我們要把三個有名字的 user 設為已經通過認證。這裡他們的 ID 剛好是 1~3 所以就用 ID 當搜尋條件，然後把 verified 欄位設為 1

  UPDATE `world`.`user`
  SET
      `verified` = 1
  WHERE 
      `user_id` > 0 AND `user_id` <= 3;
  

• SELECT 讀取資料
  接著我們來讀取資料，首先最簡單的讀出所有資料

  SELECT * FROM `user`;
  

  
  當然也可以揀選自己要的欄位，也能做資料的篩選。以下腳本讀出尚未通過認證的 user_id 與 email

  SELECT user_id, email FROM `user` WHERE verified = 0;
  

  

• DELETE 刪除資料
  最後，我們來刪除尚未通過認證的使用者資料。

  SET SQL_SAFE_UPDATES = 0;
  DELETE FROM `user` WHERE user_id;
  SET SQL_SAFE_UPDATES = 1;
  

  因為 MySQL 預設會開啟安全更新模式，在這個模式下只能用主鍵（Primary Key）來搜尋並刪除資料，這個限制大部分的時候都頗麻煩，所以使用 SET SQL_SAFE_UPDATES 這個指令來暫時關閉安全更新模式，執行完再關掉是比較常見的作法。

  刪除完的資料就變
  

今天的 CRUD 非常基礎，如果想要多學習更多關於 MySQL 的語法與觀念，可以參考這個網站。明天我們再來介紹一些比較稍微進階一點但是很實用的 MySQL 用法