連接與基礎操作
# 登錄
mysql -h 127.0.0.1 -u root -p # 本機
mysql -h 192.168.1.10 -u root -p # 遠程
# 退出
mysql> EXIT; -- 或 QUIT, \q
# 查看版本/狀態
mysql> SELECT VERSION();
mysql> STATUS;
庫/表結構管理
| 操作 | 命令 | 示例 |
|---|
| 顯示所有庫 | SHOW DATABASES; | |
| 創建庫 | CREATE DATABASE db_name CHARSET utf8mb4; | CREATE DATABASE shop DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci; |
| 刪除庫 | DROP DATABASE db_name; | |
| 切換庫 | USE db_name; | |
| 顯示當前庫表 | SHOW TABLES; | |
| 查看表結構 | DESC table_name; / SHOW COLUMNS FROM table_name; | DESC users; |
| 查看建表語句 | SHOW CREATE TABLE table_name; | |
| 查看索引 | SHOW INDEX FROM table_name; | |
數據增刪改查 (CRUD)
-- 插入
INSERT INTO users(name, email) VALUES ('張三', 'zhang@example.com');
INSERT INTO users(name, email) VALUES ('李四', 'li@example.com'), ('王五', 'wang@example.com');
-- 查詢
SELECT id, name FROM users WHERE status = 1 ORDER BY id DESC LIMIT 10;
SELECT COUNT(*) AS total FROM users;
SELECT SUM(amount) AS total_sales FROM orders WHERE created_at > '2024-01-01';
-- 更新
UPDATE users SET status = 2 WHERE id = 100;
-- 刪除
DELETE FROM users WHERE last_login < '2023-01-01';
TRUNCATE TABLE logs; -- 快速清空表（不可回滾）
用戶與權限管理
-- 創建用戶 (5.7+)
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass123!';
-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON shop.* TO 'admin'@'10.0.1.%';
-- 查看權限
SHOW GRANTS FOR 'app'@'%';
-- 回收權限
REVOKE DELETE ON shop.* FROM 'app'@'%';
-- 修改密碼
ALTER USER 'app'@'%' IDENTIFIED BY 'NewPass456!';
-- 刪除用戶
DROP USER 'app'@'%';
表結構變更 (DDL)
-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
-- 修改列
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30) NOT NULL DEFAULT '';
-- 刪除列
ALTER TABLE users DROP COLUMN phone;
-- 添加索引
CREATE INDEX idx_email ON users(email);
ALTER TABLE users ADD INDEX idx_status (status);
-- 刪除索引
DROP INDEX idx_email ON users;
-- 修改表名
RENAME TABLE users TO members;
視圖操作
CREATE VIEW v_active_users AS SELECT id, name, email FROM users WHERE status = 1;
DROP VIEW v_active_users;
批量執行 SQL 文件
# 命令行導入
mysql -h 127.0.0.1 -u root -p db_name < schema.sql
# 客戶端內部
mysql> SOURCE /path/to/schema.sql;
常用聚合與分析函數
| 函數 | 說明 | 示例 |
|---|
| COUNT(*) | 行數 | SELECT COUNT(*) FROM orders |
| SUM(col) | 求和 | SELECT SUM(amount) FROM orders |
| AVG(col) | 平均值 | SELECT AVG(score) FROM exams |
| MAX/MIN(col) | 極值 | SELECT MAX(price) FROM products |
| GROUP BY | 分組聚合 | SELECT status, COUNT(*) FROM orders GROUP BY status |
| HAVING | 分組過濾 | SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5 |
進階查詢技巧
-- 模糊查詢
SELECT * FROM articles WHERE title LIKE '%MySQL%';
-- 多表連接
SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;
-- 子查詢
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE name = '電子產品');
-- 聯合查詢
SELECT name FROM users UNION SELECT name FROM admins;
優化與維護
-- 分析表統計信息
ANALYZE TABLE users;
-- 優化表（整理碎片、回收空間）
OPTIMIZE TABLE users;
-- 檢查表錯誤
CHECK TABLE users;
-- 修復表 (MyISAM)
REPAIR TABLE users;
掌握以上核心命令，覆蓋 95% 日常 MySQL 運維開發場景。建議收藏備查，實踐中構建肌肉記憶。
