以員工檔案表為例，完整演示從建表、欄位修改、資料增刪改查到進階查詢（排序、聚合、日期篩選、分頁）的標準化操作流程，適合初學者快速上手與開發人員規範參考。
DROP TABLE IF EXISTS `workers_info`;
CREATE TABLE `workers_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '員工編號',
`workername` VARCHAR(20) NOT NULL COMMENT '姓名',
`sex` ENUM('F','M','S') NOT NULL COMMENT '性別: F女 M男 S保密',
`salary` INT(11) NOT NULL DEFAULT 0 COMMENT '工資',
`email` VARCHAR(30) NOT NULL COMMENT '郵箱',
`employed_date` DATE NOT NULL COMMENT '入職時間',
`department` VARCHAR(30) NOT NULL COMMENT '部門',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='員工檔案表';規範亮點：反引號包裹識別符、顯式 NOT NULL、DEFAULT、COMMENT、ENUM 替代魔法數字、utf8mb4、InnoDB、表註釋。
ALTER TABLE workers_info ADD COLUMN `tel` VARCHAR(15) AFTER `salary`;
ALTER TABLE workers_info ADD COLUMN `qq` BIGINT COMMENT 'QQ號';ALTER TABLE workers_info MODIFY COLUMN `employed_date` DATE AFTER `qq`;ALTER TABLE workers_info CHANGE COLUMN `email` `mailbox` VARCHAR(30) NOT NULL COMMENT '郵箱';DESC workers_info;
-- 或
SHOW CREATE TABLE workers_info\G-- 單筆插入（指定欄位名，順序不拘）
INSERT INTO workers_info (workername, salary, tel, mailbox, department, qq, employed_date)
VALUES ('xing', 10000, '1598232123', 'xing@qq.com', 'yanfa', 736019646, '2012-12-21');
-- 批次插入
INSERT INTO workers_info (workername, salary, tel, mailbox, department, qq, employed_date)
VALUES
('alice', 12000, '13800001111', 'alice@qq.com', 'yanfa', 10001, '2020-01-15'),
('bob', 9000, '13900002222', 'bob@qq.com', 'sale', 10002, '2019-06-20'),
('charlie', 15000, '13700003333', 'charlie@qq.com', 'ops', 10003, '2021-03-10');-- 修改 id=1 的郵箱與電話
UPDATE workers_info
SET mailbox = 'haha@qq.com', tel = '1246543423'
WHERE id = 1;
-- 給研發部全員漲薪 10%
UPDATE workers_info
SET salary = ROUND(salary * 1.1)
WHERE department = 'yanfa';-- 全表查詢
SELECT * FROM workers_info;
-- 指定欄位
SELECT workername, employed_date FROM workers_info;
-- 條件篩選：2003 年前入職
SELECT * FROM workers_info
WHERE YEAR(employed_date) < 2003;
-- 排序 + 分頁：工資最高/最低
SELECT * FROM workers_info ORDER BY salary ASC LIMIT 1; -- 最低
SELECT * FROM workers_info ORDER BY salary DESC LIMIT 1; -- 最高
-- 聚合統計
SELECT AVG(salary) AS avg_salary FROM workers_info;
SELECT COUNT(*) AS male_count FROM workers_info WHERE sex = 'M';
SELECT COUNT(*) AS female_count FROM workers_info WHERE sex = 'F';
-- 入職時間排序取前 5
SELECT * FROM workers_info ORDER BY employed_date ASC LIMIT 5;| 場景 | SQL 範例 | 技巧點 |
|---|---|---|
| 分組統計各部門人數/平均薪資 | SELECT department, COUNT(*) cnt, AVG(salary) avg_sal FROM workers_info GROUP BY department; | GROUP BY + 聚合函數 |
| 篩選平均薪資 > 10000 的部門 | SELECT department, AVG(salary) avg_sal FROM workers_info GROUP BY department HAVING AVG(salary) > 10000; | HAVING 過濾聚合結果 |
| 模糊查詢姓名含「x」 | SELECT * FROM workers_info WHERE workername LIKE '%x%'; | LIKE + 萬用字元 |
| 多條件組合 | SELECT * FROM workers_info WHERE department='yanfa' AND salary > 10000 AND sex='M'; | AND/OR 邏輯組合 |
| 去重統計不同部門數 | SELECT COUNT(DISTINCT department) FROM workers_info; | DISTINCT |
| 分頁查詢 (第 2 頁，每頁 10 條) | SELECT * FROM workers_info ORDER BY id LIMIT 10 OFFSET 10; | LIMIT offset, rows 或 LIMIT rows OFFSET offset |
| 關聯查詢 (假設有部門表 depts) | SELECT w.workername, d.dept_name FROM workers_info w JOIN depts d ON w.department = d.dept_code; | JOIN 關聯 |
-- 刪除特定資料
DELETE FROM workers_info WHERE id = 5;
-- 清空表 (保留結構、重置自增)
TRUNCATE TABLE workers_info;
-- 刪除表 (結構+資料全刪)
DROP TABLE workers_info;id BIGINT UNSIGNED AUTO_INCREMENT、建立/更新時間 created_at/updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP、軟刪除 deleted TINYINT DEFAULT 0。idx_表_欄、外鍵 fk_表_關聯表。DECIMAL(12,2)、狀態 TINYINT UNSIGNED、長文本 TEXT/JSON、IP INT UNSIGNED (INET_ATON/INET_NTOA)。ANALYZE TABLE。SELECT *、大查詢必加 LIMIT、IN 值不超 500、避免函數索引失效 (WHERE DATE(created_at)=... 改 WHERE created_at >= ... AND created_at < ...)。扎實掌握建表規範與 CRUD 核心語法，是每位後端開發與 DBA 的基本功。建議將上述範例內化為肌肉記憶，並在 Code Review 中強制執行規範，從源頭減少線上 Schema 變更與效能事故。
