MySQL表的增刪改查（基礎）

1. CRUD

CRUD 即增加(Create)、查詢(Retrieve)、更新(Update)、刪除(Delete)四個單詞的首字母縮寫。

2. 新增（Create）

語法（[ ]內的東西表示可以有，也可以沒有）

INSERT [INTO] table_name
[(column [, column] …)]
VALUES (value_list) [, (value_list)]…
value_list: value, [, value]…

案例：

– 建立一張學生表

DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT,
sn INT comment ‘學號’,
name VARCHAR(20) comment ‘姓名’,
qq_mail VARCHAR(20) comment ‘QQ郵箱’
);

2.1 單行資料 + 全列插入

– 插入兩條記錄，value_list 數量必須和定義表的列的數量及順序一致
INSERT INTO student VALUES (100, 10000, 安琪拉’, NULL);
INSERT INTO student VALUES (101, 10001, ‘老夫子’, ‘11111’);

2.2 多行資料 + 指定列插入

– 插入兩條記錄，value_list 數量必須和指定列數量及順序一致
INSERT INTO student (id, sn, name) VALUES
(102, 20001, ‘小喬’),
(103, 20002, ‘不知火舞’);

大小寫均可，文章中所有的大寫均表示關鍵字，後面執行的時候懶得在敲一遍就直接複製了

3. 查詢（Retrieve）

語法：

SELECT
[DISTINCT] {* | {column [, column] …}
[FROM table_name]
[WHERE …]
[ORDER BY column [ASC | DESC], …]
LIMIT …

案例：

– 建立考試成績表

DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);

– 插入測試資料

INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,‘娜可露露’, 67, 98, 56),
(2,‘蘭陵王’, 87.5, 78, 77),
(3,‘貂蟬’, 88, 98.5, 90),
(4,‘曹操’, 82, 84, 67),
(5,‘高漸離’, 55.5, 85, 45),
(6,‘李白’, 70, 73, 78.5),
(7,‘鍾無豔’, 75, 65, 30);

3.1 全列查詢

通常不建議使用 * 進行全列查詢
1.查詢的列越多，意味著要傳輸的資料量越大；
2.可能會影響到索引的使用。（索引下一篇將會提到）
SELECT * FROM exam_result;

3.2 指定列查詢

指定列的順序不需要按照表定義的順序來

SELECT id,name,english FROM exam_result;

3.3 查詢欄位為表示式

– 表示式不包含欄位
SELECT id, name, 10 FROM exam_result;
– 表示式包含一個欄位
SELECT id, name, english + 10 FROM exam_result;
– 表示式包含多個欄位
SELECT id, name, chinese + math + english FROM exam_result;

3.4 別名

為查詢結果中的列指定別名，表示返回的結果集中，以別名作為該列的名稱，語法:
SELECT column [AS] alias_name […] FROM table_name;
– 結果集中，表頭的列名=別名
SELECT id, name, chinese + math + english 總分 FROM exam_result;

3.5 去重：DISTINCT

使用DISTINCT關鍵字對某列資料進行去重:
– 98 分重複了
SELECT math FROM exam_result;

– 去重結果
SELECT DISTINCT math FROM exam_result;

3.6 排序：ORDER BY
語法：
– ASC 為升序（從小到大）
– DESC 為降序（從大到小）
– 預設為 ASC
SELECT … FROM table_name [WHERE …]
ORDER BY column [ASC|DESC], […];

1. 沒有 ORDER BY 子句的查詢，返回的順序是未定義的，永遠不要依賴這個順序。
2. NULL 資料排序，視為比任何值都小，升序出現在最上面，降序出現在最下面。
   – 查詢同學姓名和 qq_mail，按 qq_mail 排序顯示
   SELECT name, qq_mail FROM student ORDER BY qq_mail;
   SELECT name, qq_mail FROM student ORDER BY qq_mail DESC;
3. 使用表示式及別名排序
   – 查詢同學及總分，由高到低
   SELECT name, chinese + english + math FROM exam_result ORDER BY chinese + english + math DESC;
   SELECT name, chinese + english + math total FROM exam_result ORDER BY total DESC;
4. 可以對多個欄位進行排序，排序優先順序隨書寫順序
   – 查詢同學各門成績，依次按 數學降序，英語升序，語文升序的方式顯示
   SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english, chinese;

此處演示最後兩個

3.7 條件查詢：WHERE

比較運算子：

大於，大於等於等不做說明

• =：等於，NULL 不安全，例如 NULL = NULL 的結果是 NULL
• <=>：等於，NULL 安全，例如 NULL <=> NULL 的結果是 TRUE(1)
• !=,<>：不等於
• BETWEEN a0 AND a1：範圍匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1) 注意：左右都是閉區間，與Java不同，Java一般都是左閉右開。
• IN (option, …)：如果是 option 中的任意一個，返回 TRUE(1)
• IS NULL：是 NULL
• IS NOT NULL ：不是 NULL
• LIKE：模糊匹配。% 表示任意多個（包括 0 個）任意字元；_ 表示任意一個字元

邏輯運算子：

AND：多個條件必須都為 TRUE(1)，結果才是 TRUE(1)
OR：任意一個條件為 TRUE(1), 結果為 TRUE(1)
NOT：條件為 TRUE(1)，結果為 FALSE(0)
注：

• WHERE條件可以使用表示式，但不能使用別名。

• AND的優先順序高於OR，在同時使用時，需要使用小括號()包裹優先執行的部分

• 範圍查詢:

BETWEEN … AND

查詢語文成績在 [80, 90] 分的同學及語文成績
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

IN

– 查詢數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

- 模糊查詢：LIKE

• – % 匹配任意多個（包括 0 個）字元
• 如若在插入兩行資料分別為孫悟空和孫權
  SELECT name FROM exam_result WHERE name LIKE ‘孫%’;-- 匹配到孫悟空、孫權
  – _ 匹配嚴格的一個任意字元
  SELECT name FROM exam_result WHERE name LIKE ‘孫_’;-- 匹配到孫權（只顯示第一行資料，按英文字母排序孫權排在孫悟空之前）

3.8 分頁查詢：LIMIT

語法：
– 起始下標為 0
– 從 0 開始，篩選 n 條結果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n;
– 從 s 開始，篩選 n 條結果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT s, n;
– 從 s 開始，篩選 n 條結果，比第二種用法更明確，建議使用
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET s;

4. 修改（Update）

語法：
UPDATE table_name SET column = expr [, column = expr …]
[WHERE …] [ORDER BY …] [LIMIT …]

案例：
– 將鍾無豔同學的數學成績變更為 80 分
UPDATE exam_result SET math = 80 WHERE name = ‘鍾無豔’;
– 將所有同學的語文成績更新為原來的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

5. 刪除（Delete）

語法：
DELETE FROM table_name [WHERE …] [ORDER BY …] [LIMIT …]

案例：
– 刪除孫悟空同學的考試成績
DELETE FROM exam_result WHERE name = ‘孫悟空’;

程式碼全都是文章中的，我也是敲在文章中然後copy到資料庫的，自己操作吧！