MySQL 基礎語法練習（四）：DDL、DML、查詢、函數、事務綜合實戰

MySQL 基礎語法實戰練習（四）：從建表到事務的完整流程

本文通過一個 employee 表的完整生命周期，串聯 DDL、DML、查詢、函數、事務等核心操作，適合初學者跟敲練習。

1. 創建表（指定字符集）

DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  gender VARCHAR(6),
  birthday DATE,
  entry_date DATE,
  job VARCHAR(30),
  salary FLOAT(5,1),
  resume TEXT
) CHARSET=utf8;

2. 插入數據

INSERT INTO employee (id,name,gender,birthday,entry_date,job,salary,resume)
VALUES (1,'jack','male','2011-10-08','2011-12-31','software',5000.1,'hello');

3. 修改表結構

-- 增加列
ALTER TABLE employee ADD image BLOB;
-- 修改列長度與默認值
ALTER TABLE employee MODIFY job VARCHAR(60) DEFAULT 'teacher';
-- 刪除列
ALTER TABLE employee DROP gender;
-- 重命名表
RENAME TABLE employee TO user;
-- 修改表字符集
ALTER TABLE user CHARACTER SET utf8;
-- 重命名列
ALTER TABLE user CHANGE COLUMN name username VARCHAR(20);

4. 客戶端編碼設置（解決中文亂碼）

SET character_set_client = gbk;
SET character_set_results = gbk;
-- 或一鍵設置
SET NAMES gbk;

5. 插入中文測試數據

INSERT INTO user (username,id,birthday,entry_date,job,salary,resume)
VALUES ('傑克',2,'2011-10-08','2011-12-31','software',5000.1,'你好');
INSERT INTO user VALUES (3,'馬利','2011-10-08','2011-12-31','software',5000.1,'你好',NULL);
INSERT INTO user VALUES (4,'馬利','2011-10-08','2011-12-31','software',5000.1,NULL,NULL);
INSERT INTO user (id,username,birthday,entry_date,job,salary,image)
VALUES (5,'馬利','2011-10-08','2011-12-31','software',5000.1,NULL);

6. DML 更新與刪除

-- 全表更新
UPDATE user SET salary = 6000;
-- 條件更新
UPDATE user SET salary = 7000 WHERE username = '馬利';
-- 相對更新
UPDATE user SET salary = salary + 1000 WHERE username = 'jack';
-- 條件刪除
DELETE FROM user WHERE username = 'jack';
-- 全表刪除
DELETE FROM user;
-- TRUNCATE（重置自增、速度快）
TRUNCATE TABLE user;

7. 基礎查詢

SELECT * FROM student;
SELECT id,name,math,chinese,english FROM student;
SELECT name,english FROM student;
-- 去重
SELECT DISTINCT english FROM student;
SELECT DISTINCT name,english FROM student;
-- 列運算與別名
SELECT name, math+10 AS math_bonus FROM student;
SELECT name, math+chinese+english AS total FROM student;
SELECT name, math+chinese+english AS total FROM student;

8. 條件查詢

SELECT * FROM student WHERE name = '張小明';
SELECT * FROM student WHERE english > 90;
SELECT name, chinese+math+english AS total FROM student WHERE chinese+math+english > 200;
SELECT * FROM student WHERE english BETWEEN 80 AND 90;
SELECT * FROM student WHERE math IN (89,90,91);
SELECT * FROM student WHERE name LIKE '李%';
SELECT * FROM student WHERE name LIKE '%李%';
SELECT * FROM student WHERE name LIKE '__李';
SELECT * FROM student WHERE math IS NOT NULL;

9. 排序

SELECT * FROM student ORDER BY math ASC;
SELECT * FROM student ORDER BY math DESC;
SELECT name, math+chinese+english AS total FROM student ORDER BY total DESC;
SELECT name, math+chinese+english AS total FROM student WHERE name LIKE '李%' ORDER BY total DESC;

10. 聚合函數

SELECT COUNT(*) AS total FROM student;
SELECT COUNT(*) FROM student WHERE math > 80;
SELECT COUNT(*) FROM student WHERE math+chinese+english > 250;
SELECT SUM(math) FROM student;
SELECT SUM(math) AS math_sum, SUM(chinese) AS chinese_sum, SUM(english) AS english_sum FROM student;
SELECT SUM(math)+SUM(chinese)+SUM(english) AS class_total FROM student;
SELECT SUM(math)/COUNT(math) AS math_avg FROM student;
SELECT SUM(math)/COUNT(*) AS math_avg2 FROM student;

11. 關鍵區別總結

12. 排序與聚合注意點

• ORDER BY 必須放最後，NULL 排最前。
• COUNT(col) 不統計 NULL；COUNT(*) 統計行數。
• SUM(col) 非數值列返回 0。
• AVG/MAX/MIN 同理忽略 NULL。

總結

跟敲完本練習，即可掌握 MySQL 核心 DDL/DML/查詢/聚合/事務/編碼處理全流程。建議在本地庫重複練習 3 遍，形成肌肉記憶。