連線 MySQL
mysql -u username -p
# 輸入密碼後進入 mysql> 提示符
資料庫級操作
-- 建庫(指定編碼)
CREATE DATABASE ceshi CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 刪庫
DROP DATABASE ceshi;
-- 切換庫
USE ceshi;
-- 查看建庫語句
SHOW CREATE DATABASE ceshi;
資料表級操作
建表範例
CREATE TABLE students (
sid INT(10) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
course VARCHAR(255),
score INT(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
AUTO_INCREMENT:自增主鍵。
ENGINE=InnoDB:支援事務、外鍵、行鎖。
CHARSET=utf8mb4:完整 Unicode 支援(含 Emoji)。
修改表結構
-- 修改表預設編碼
ALTER TABLE students DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 新增欄位
ALTER TABLE students ADD COLUMN email VARCHAR(100) AFTER name;
-- 修改欄位型別/屬性
ALTER TABLE students MODIFY COLUMN score DECIMAL(5,2);
-- 刪除欄位
ALTER TABLE students DROP COLUMN email;
-- 重命名表
RENAME TABLE students TO stu_info;
刪表
DROP TABLE students;
資料操作 (DML)
插入資料
-- 單條
INSERT INTO students (name, course, score) VALUES ('張三', '數學', 95);
-- 多條(高效批次)
INSERT INTO students (name, course, score) VALUES
('李四', '英文', 88),
('王五', '物理', 92),
('趙六', '化學', 76);
-- 從另一表複製資料
INSERT INTO students (name, course, score)
SELECT name, course, score FROM old_students WHERE score > 60;
查詢資料
-- 全欄位
SELECT * FROM students;
-- 指定欄位、別名、排序
SELECT sid AS id, name AS student_name, score
FROM students
ORDER BY score DESC; -- DESC 降序,ASC 升序(預設)
-- 條件查詢
SELECT * FROM students WHERE score >= 90;
SELECT * FROM students WHERE course IN ('數學', '物理');
SELECT * FROM students WHERE name LIKE '張%'; -- % 任意長度,_ 單字元
-- 分組聚合
SELECT course, COUNT(*) AS cnt, AVG(score) AS avg_score
FROM students
GROUP BY course
HAVING AVG(score) > 80; -- HAVING 過濾聚合後結果
-- 分頁
SELECT * FROM students ORDER BY score DESC LIMIT 10 OFFSET 20; -- 第 3 頁,每頁 10 筆
更新資料
UPDATE students SET score = 100 WHERE name = '張三';
-- ⚠️ 沒有 WHERE 會更新全表!
刪除資料
DELETE FROM students WHERE score < 60;
-- ⚠️ 沒有 WHERE 會清空全表!
-- TRUNCATE TABLE students; -- 更快,重置自增 ID,不可回滾
進階查詢技巧
UNION / UNION ALL
SELECT name FROM class_a WHERE score > 90
UNION -- 去重
SELECT name FROM class_b WHERE score > 90;
SELECT name FROM class_a WHERE score > 90
UNION ALL -- 不去重,效能更好
SELECT name FROM class_b WHERE score > 90;
子查詢
-- WHERE 子查詢
SELECT * FROM students WHERE course = (SELECT course FROM courses WHERE difficulty = 'hard');
-- FROM 子查詢(衍生表)
SELECT t.name, t.avg_score FROM (
SELECT name, AVG(score) AS avg_score FROM students GROUP BY name
) t WHERE t.avg_score > 85;
常用函數速查
| 分類 | 函數 | 範例 |
| 字串 | CONCAT, SUBSTRING, REPLACE, TRIM, UPPER/LOWER | CONCAT('Hello', ' ', 'World') |
| 日期時間 | NOW(), CURDATE(), DATE_FORMAT(), DATEDIFF() | DATE_FORMAT(NOW(), '%Y-%m-%d') |
| 數學 | ROUND, CEIL, FLOOR, RAND, ABS | ROUND(3.14159, 2) |
| 流程控制 | IF, CASE, COALESCE, NULLIF | IF(score>=60, 'Pass', 'Fail') |
| 聚合 | COUNT, SUM, AVG, MAX, MIN, GROUP_CONCAT | GROUP_CONCAT(name SEPARATOR ',') |
新手必知十大避坑指南
- **永遠帶 WHERE 執行 UPDATE/DELETE**。
- **主鍵用 BIGINT AUTO_INCREMENT**,預留足夠空間。
- **字串欄位預設 NOT NULL DEFAULT ''**,避免 NULL 處理麻煩。
- **時間戳用 TIMESTAMP/DATETIME(3)**,毫秒級精度。
- **金額用 DECIMAL(10,2)**,嚴禁 FLOAT/DOUBLE。
- **索引建在 WHERE/JOIN/ORDER BY 欄位**,但不濫建。
- **EXPLAIN 分析慢 SQL**,關注
type、key、rows。
- **備份先測試恢復**,未測試的備份等於沒備份。
- **嚴格 SQL_MODE**:
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION。
- **監控慢查詢、連線數、緩存命中率**,建立基線告警。