連線與基礎
# 登入
mysql -h localhost -u root -p
# 修改密碼
mysqladmin -u root -p'old' password 'new'
# 退出
QUIT; -- 或 EXIT; \q
DDL：庫表結構
SHOW DATABASES;
CREATE DATABASE `demo` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE demo;
SHOW TABLES;
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
status TINYINT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DESCRIBE users;
SHOW CREATE TABLE users\G
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ADD INDEX idx_status (status);
ALTER TABLE users DROP INDEX idx_status;
DML：增刪改查
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice');
INSERT INTO users (email, name) VALUES ('c@d.com', 'Bob'), ('e@f.com', 'Carol');
SELECT * FROM users;
SELECT id, name FROM users WHERE email LIKE '%@b.com' LIMIT 10;
SELECT * FROM users ORDER BY created_at DESC;
UPDATE users SET name = 'Alice Smith' WHERE id = 1;
DELETE FROM users WHERE id = 2;
TRUNCATE TABLE users;
連線查詢
-- 內連接
SELECT u.id, u.name, o.amount
FROM users u INNER JOIN orders o ON u.id = u.id;
-- 左連接
SELECT u.id, u.name, o.amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id;
-- 右連接
SELECT u.id, u.name, o.amount
FROM users u RIGHT JOIN orders o ON u.id = o.user_id;
索引策略
| 類型 | 建立語法 | 適用場景 |
|---|
| 主鍵 | PRIMARY KEY (id) | 唯一標識、聚簇索引 |
| 唯一 | UNIQUE KEY uk_email (email) | 業務唯一約束 |
| 普通 | INDEX idx_status (status) | WHERE/JOIN/ORDER BY/GROUP BY |
| 全文 | FULLTEXT INDEX ft_content (content) | MATCH AGAINST 自然語言搜索 |
| 組合 | INDEX idx_status_created (status, created_at) | 最左前綴原則 |
常用函數速查
| 分類 | 函數 |
|---|
| 字符串 | CONCAT, SUBSTRING, REPLACE, TRIM, UPPER, LOWER, LENGTH, INSTR, LOCATE, LEFT, RIGHT |
| 數學 | ABS, CEIL, FLOOR, ROUND, TRUNCATE, MOD, POWER, SQRT, RAND, SIGN |
| 日期 | NOW, CURDATE, CURTIME, DATE_ADD, DATE_SUB, DATEDIFF, DATE_FORMAT, STR_TO_DATE, DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, DAYNAME, DAYOFWEEK, WEEK, QUARTER |
| 流程控制 | IF, CASE, IFNULL, COALESCE, NULLIF |
| 聚合 | COUNT, SUM, AVG, MAX, MIN, GROUP_CONCAT, STDDEV, VARIANCE |
| 加密 | MD5, SHA1, SHA2, AES_ENCRYPT, AES_DECRYPT, PASSWORD |
效能優化 20 條
- 開啟 Query Cache（5.7 已移除，改用 Redis）。
EXPLAIN 分析執行計劃，關注 type、key、rows、Extra。- 單行查詢
LIMIT 1。
- 搜索欄位建立索引（
title、tags）。
- JOIN 條件欄位**類型、長度、校對規則完全一致**並建索引。
- 隨機查詢：應用層
mt_rand + LIMIT offset, 1，避免 ORDER BY RAND()。
- 避免
SELECT *，顯式列出欄位。
- 每表必有
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY。
- 枚舉值固定用
ENUM（性別、狀態、部門）。
- IP 存
INT UNSIGNED，用 INET_ATON/INET_NTOA 或 ip2long/long2ip。
- 大量刪除/插入分批，
usleep 間隔減輕鎖衝突。
- 選擇引擎：讀多寫少
MyISAM（已淘汰），事務/高併發 InnoDB。
CHAR 固定長度，VARCHAR 可變長度，超長用 TEXT。- 浮點
FLOAT/DOUBLE 精度丟失，金額用 DECIMAL(10,2)。
- 索引建在
WHERE/JOIN 欄位，高選擇性優先。
- 唯一業務鍵用
UNIQUE。
- 分區表處理億級數據。
- 讀寫分離 + 主從同步。
- 慢查詢日誌 +
pt-query-digest 定期分析。
- 監控：
SHOW GLOBAL STATUS、SHOW ENGINE INNODB STATUS。
存儲過程/函數
DELIMITER //
CREATE PROCEDURE get_version(OUT p_ver VARCHAR(20))
BEGIN SELECT VERSION() INTO p_ver; END//
DELIMITER ;
CALL get_version(@v); SELECT @v;
DELIMITER //
CREATE FUNCTION greet(p_name VARCHAR(20)) RETURNS VARCHAR(50)
BEGIN RETURN CONCAT('Hello, ', p_name); END//
DELIMITER ;
SELECT greet('World');
-- 控制流
IF condition THEN ... ELSEIF ... ELSE ... END IF;
CASE var WHEN 1 THEN ... WHEN 2 THEN ... ELSE ... END CASE;
WHILE v < 10 DO ... END WHILE;
REPEAT ... UNTIL v >= 10 END REPEAT;
LOOP ... LEAVE label; ITERATE label; ... END LOOP;
權限管理
GRANT SELECT, INSERT, UPDATE, DELETE ON demo.* TO 'app'@'%' IDENTIFIED BY 'Pass#2024';
SHOW GRANTS FOR 'app'@'%';
REVOKE UPDATE ON demo.* FROM 'app'@'%';
DROP USER 'app'@'%';
備份還原
# 邏輯備份（單事務一致性）
mysqldump -u root -p --single-transaction --master-data=2 demo > demo.sql
# 還原
mysql -u root -p demo < demo.sql
MySQL 幫助系統
HELP CONTENTS;
HELP 'CREATE TABLE';
HELP 'DATA TYPES';
