🗃? 一、數據庫操作

CREATE DATABASE db_name;
USE db_name;
DROP DATABASE db_name;
SHOW DATABASES;

🔗 官方文檔

---

📊 二、表操作

表創建示例

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

表關系圖示 (Mermaid)

常用命令

ALTER TABLE users ADD COLUMN age INT;
DROP TABLE users;
TRUNCATE TABLE logs;
DESCRIBE users;

🔗 官方文檔

---

💾 三、CRUD 操作

1. 數據插入

INSERT INTO users (name, email) 
VALUES ('張三', 'zhangsan@example.com');

2. 數據查詢

SELECT * FROM users WHERE age > 18;
SELECT name, email FROM users ORDER BY created_at DESC;
SELECT COUNT(*) AS total FROM orders;

3. JOIN 操作圖示 (Mermaid)

4. 更新與刪除

UPDATE users SET age = 25 WHERE id = 101;
DELETE FROM logs WHERE created_at < '2023-01-01';

🔗 SELECT 文檔

---

🔐 四、事務管理

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 或 ROLLBACK

事務流程 (Mermaid)

🔗 事務文檔

---

📑 五、索引優化

CREATE INDEX idx_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

B+樹索引結構 (Mermaid)

🔗 索引文檔

---

🛠? 六、高級功能

1. 存儲過程

DELIMITER //
CREATE PROCEDURE GetUser(IN uid INT)
BEGINSELECT * FROM users WHERE id = uid;
END //
DELIMITER ;
CALL GetUser(101);

2. 視圖

CREATE VIEW active_users AS
SELECT id, name FROM users WHERE is_active = 1;

3. 觸發器

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW SET NEW.updated_at = NOW();

---

📚 七、學習資源

1. MySQL 8.0 官方文檔
2. MySQL Tutorial
3. SQL Fiddle 在線練習
4. DB Fiddle MySQL 沙盒

---

💡 性能優化提示

1. 使用 EXPLAIN 分析查詢
2. 避免 SELECT *
3. 為 WHERE/JOIN 字段創建索引
4. 用 LIMIT 分頁代替全表掃描
5. 定期執行 OPTIMIZE TABLE