資料庫級操作
| 操作 | SQL | 說明 |
|---|
| 顯示所有庫 | SHOW DATABASES [LIKE 'pattern']; | 支援 % _ 通配符 |
| 建立資料庫 | CREATE DATABASE db_name [CHARSET utf8mb4 COLLATE utf8mb4_general_ci]; | 建議顯式指定字元集 |
| 刪除資料庫 | DROP DATABASE [IF EXISTS] db_name; | 謹慎執行，連表帶資料全刪 |
| 切換資料庫 | USE db_name; | 會話級生效 |
命令列工具對應
# 建庫
mysqladmin -uroot -p create db_name
# 刪庫
mysqladmin -uroot -p drop db_name
資料表級操作
| 操作 | SQL |
|---|
| 顯示所有表 | SHOW TABLES [FROM db_name] [LIKE 'pattern']; |
| 檢視表結構 | DESCRIBE tbl_name; 或 SHOW COLUMNS FROM tbl_name; |
| 建立表 | CREATE TABLE tbl_name (col1 TYPE, col2 TYPE, ...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
| 刪除表 | DROP TABLE [IF EXISTS] tbl_name; |
| 重命名表 | RENAME TABLE old_name TO new_name; |
| 複製表結構 | CREATE TABLE new_tbl LIKE old_tbl; |
| 複製表結構+資料 | CREATE TABLE new_tbl AS SELECT * FROM old_tbl; |
資料操作 (DML)
| 操作 | SQL |
|---|
| 插入單行 | INSERT INTO tbl (col1,col2) VALUES (val1,val2); |
| 批次插入 | INSERT INTO tbl (col1,col2) VALUES (v1,v2), (v3,v4), (v5,v6); |
| 查詢 | SELECT col1,col2 FROM tbl WHERE condition ORDER BY col1 LIMIT 10; |
| 更新 | UPDATE tbl SET col1=val1 WHERE condition; |
| 刪除 | DELETE FROM tbl WHERE condition; |
| 截斷表 | TRUNCATE TABLE tbl_name; (DDL、不可回滾、重置自增 ID) |
索引與約束
-- 建立使用者
CREATE USER 'user'@'host' IDENTIFIED BY 'password';
-- 授權
GRANT SELECT,INSERT,UPDATE ON db.* TO 'user'@'host';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
-- 回收權限
REVOKE INSERT ON db.* FROM 'user'@'host';
-- 刷新生效
FLUSH PRIVILEGES;
-- 修改密碼 (5.7+)
ALTER USER 'user'@'host' IDENTIFIED BY 'newpass';
-- 刪除使用者
DROP USER 'user'@'host';
實用進階查詢
- 分頁：
SELECT * FROM tbl ORDER BY id LIMIT 10 OFFSET 20; (或 LIMIT 20,10)
- 去重：
SELECT DISTINCT col FROM tbl;
- 聚合：
SELECT COUNT(*), SUM(col), AVG(col), MAX(col), MIN(col) FROM tbl GROUP BY col HAVING COUNT(*) > 1;
- 連接查詢：
INNER/LEFT/RIGHT JOIN + ON 條件
- 子查詢：
WHERE col IN (SELECT col FROM ...) / EXISTS (SELECT 1 FROM ...)
- 合併結果：
UNION ALL (不去重、快) / UNION (去重)
備份/還原常用指令
