MySQL SQL 语言全面指南：从基础到高级查询

作为关系型数据库的核心，SQL语言是与MySQL交互的基础。本文将全面解析MySQL中的SQL语言分类及其关键语法，帮助开发者掌握数据定义、操作和控制的精髓。

一、DDL（数据定义语言）

DDL用于定义和管理数据库对象的结构，是数据库设计的基石。

1. 数据库操作

-- 创建数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改数据库字符集
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 删除数据库
DROP DATABASE IF EXISTS mydb;

实践建议：始终指定字符集（推荐utf8mb4）和排序规则，避免后期出现乱码问题。

2. 表操作

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- 修改表结构
ALTER TABLE users 
ADD COLUMN age TINYINT UNSIGNED AFTER email,
MODIFY COLUMN username VARCHAR(60) NOT NULL;

-- 删除表
DROP TABLE IF EXISTS users;

实践建议：

主键推荐使用自增INT/BIGINT
为常用查询条件创建索引
表名使用复数形式(如users)

3. 索引管理

-- 创建索引
CREATE INDEX idx_name ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 删除索引
DROP INDEX idx_name ON users;

索引优化建议：

避免在频繁更新的列上创建过多索引
考虑使用复合索引而非单列索引
使用EXPLAIN分析查询是否使用了索引

4. 视图与触发器

-- 创建视图
CREATE VIEW active_users AS
SELECT id, username FROM users WHERE is_active = 1;

-- 创建触发器
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.email IS NULL THEN
        SET NEW.email = CONCAT(NEW.username, '@example.com');
    END IF;
END//
DELIMITER ;

二、DML（数据操作语言）

DML用于操作数据库中的数据记录，是日常开发中最常用的部分。

1. SELECT查询

-- 基础查询
SELECT id, username FROM users WHERE age > 18 ORDER BY created_at DESC LIMIT 10;

-- 条件查询
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500 
AND category_id IN (1, 3, 5)
AND name LIKE '%手机%';

-- 聚合查询
SELECT 
    department_id, 
    COUNT(*) as emp_count,
    AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

2. 数据修改操作

-- 插入数据
INSERT INTO users (username, email, age)
VALUES ('john_doe', 'john@example.com', 30),
       ('jane_smith', 'jane@example.com', 25);

-- 更新数据
UPDATE products 
SET price = price * 0.9, 
    updated_at = NOW()
WHERE stock > 100 AND created_at < '2023-01-01';

-- 删除数据
DELETE FROM logs 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
ORDER BY created_at
LIMIT 1000; -- 分批删除大量数据

批量操作建议：

大批量操作考虑分批次执行
使用事务保证数据一致性
高危操作前先备份数据

三、高级查询技巧

1. 连接查询

-- 内连接
SELECT o.order_id, u.username, o.amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- 左外连接
SELECT d.dept_name, COUNT(e.id) as emp_count
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id;

-- 自连接(查找同一部门的员工)
SELECT e1.name, e2.name, e1.department
FROM employees e1
JOIN employees e2 ON e1.department = e2.department
WHERE e1.id < e2.id;

2. 子查询

-- WHERE子句中的子查询
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- FROM子句中的子查询
SELECT dept_name, avg_salary
FROM (
    SELECT d.name as dept_name, AVG(e.salary) as avg_salary
    FROM departments d
    JOIN employees e ON d.id = e.dept_id
    GROUP BY d.id
) AS dept_stats
WHERE avg_salary > 10000;

-- EXISTS子查询
SELECT c.name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id 
    AND o.amount > 1000
);

3. 窗口函数(MySQL 8.0+)

-- 排名函数
SELECT 
    product_id,
    name,
    price,
    RANK() OVER (ORDER BY price DESC) as price_rank,
    DENSE_RANK() OVER (ORDER BY price DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY price DESC) as row_num
FROM products;

-- 分区计算
SELECT 
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department_id) as diff_from_avg
FROM employees;

四、DCL（数据控制语言）

DCL用于控制数据库访问权限和事务处理。

1. 权限管理

-- 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_user'@'%';

-- 撤销权限
REVOKE DELETE ON mydb.* FROM 'app_user'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

安全建议：

遵循最小权限原则
避免使用root账户进行应用连接
定期审计用户权限

2. 事务控制

-- 基本事务
START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;
-- 或 ROLLBACK;

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

事务最佳实践：

保持事务尽可能短小
避免在事务中进行网络调用
合理选择隔离级别

五、性能优化建议

查询优化：
- 使用EXPLAIN分析查询执行计划
- 避免SELECT *，只查询需要的列
- 合理使用索引覆盖查询

索引策略：

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'mydb';

-- 查找未使用的索引
SELECT * FROM sys.schema_unused_indexes;

分页优化：

-- 低效写法(偏移量大时性能差)
SELECT * FROM large_table LIMIT 10000, 20;

-- 优化写法(使用索引列过滤)
SELECT * FROM large_table 
WHERE id > 10000 
ORDER BY id 
LIMIT 20;

通过掌握这些SQL语言的核心概念和实用技巧，您将能够更高效地与MySQL数据库交互，构建性能优异的数据库应用。

MySQL SQL语言全指南：从DDL到高级查询