🗃️ 一、数据库操作

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