[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 范例资料库里面再新增一个资料表

Day20 更正
之前尝鲜用了 MySQL 的型态 BIT(1)，结果到了 Day20 要用 Dapper 操作才发现，BIT(1) 传回来的资料型态竟然是 ulong，无法隐含转型为 bool，所以这边改回安稳的旧式写法 TINYINT(1)

INSERT 新增资料

INSERT INTO `user` 
VALUES 
(NULL, 'Alice','[email protected]', 0),
(NULL, 'Bob','[email protected]', 0),
(NULL, 'Cathy','[email protected]', 0),
(NULL, 'TestUser1','[email protected]', 0),
(NULL, 'TestUser2','[email protected]', 0),
(NULL, 'TestUser3','[email protected]', 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 这个指令来暂时关闭安全更新模式，执行完再关掉是比较常见的作法。

删除完的资料就变