iT邦幫忙

2021 iThome 鐵人賽

DAY 18
0
Modern Web

網站一條龍 - 從架站到前端系列 第 18

[Day18] MySQL 的 CRUD 語法

上次我們介紹如何用 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 範例資料庫裏面再新增一個資料表
    https://ithelp.ithome.com.tw/upload/images/20210920/20140664ctnoPJFJwA.png

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`;
    

    https://ithelp.ithome.com.tw/upload/images/20210918/20140664a3YXwTT22Q.png
    當然也可以揀選自己要的欄位,也能做資料的篩選。以下腳本讀出尚未通過認證的 user_id 與 email

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

    https://ithelp.ithome.com.tw/upload/images/20210918/20140664fIQLLNzALI.png

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

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

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

    刪除完的資料就變
    https://ithelp.ithome.com.tw/upload/images/20210918/20140664iv1MERsL7H.png


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


上一篇
[Day17] 安裝 MySQL Server 與 MySQL Workbench
下一篇
[Day19] MySQL 的 JOIN
系列文
網站一條龍 - 從架站到前端33
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言