MySQL 常用 SQL 语句分类

SQL 语句分类表

SQL 语句	类型	用途
SELECT	DQL	查询表中的数据
INSERT	DML	插入新的数据记录到表中
UPDATE	DML	修改表中的数据记录
DELETE	DML	从表中删除数据记录
CREATE TABLE	DDL	创建新的表
ALTER TABLE	DDL	修改现有的表结构
DROP TABLE	DDL	删除表
CREATE DATABASE	DDL	创建新的数据库
DROP DATABASE	DDL	删除数据库
GRANT	DCL	给用户赋予权限
REVOKE	DCL	移除用户的权限
BEGIN TRANSACTION	TCL	开始一个新的事务
COMMIT	TCL	提交事务，保存所有修改
ROLLBACK	TCL	回滚事务，撤销所有修改
JOIN	DQL	在查询中关联两个或多个表
GROUP BY	DQL	在查询中按某列或条件分组数据
ORDER BY	DQL	对查询结果进行排序
WHERE	DQL	在查询中根据条件过滤数据
HAVING	DQL	在 GROUP BY 查询中根据条件过滤分组
CREATE INDEX	DDL	在表的一列或多列上创建索引
DROP INDEX	DDL	删除索引

SQL 语句类型详解

DQL 数据查询语言

DQL 专注于数据查询和检索操作，SELECT 是最基础的查询语句。通过 WHERE 子句过滤数据，使用 JOIN 关联多表，GROUP BY 进行分组统计，ORDER BY 排序结果集。

-- 查询所有员工的姓名和工资
SELECT name, salary FROM employees;

-- 查询工资大于5000的员工
SELECT name, salary FROM employees WHERE salary > 5000;

-- 关联查询员工所属部门
SELECT employees.name, departments.dept_name
FROM employees
JOIN departments ON employees.dept_id = departments.id;

-- 按部门分组统计每个部门的平均工资
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id;

-- 查询每个部门的平均工资并按平均工资降序排序
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
GROUP BY dept_id
ORDER BY avg_salary DESC;

最佳实践

• 使用别名（Alias）简化查询结果，如 SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.id;
• 利用索引优化 WHERE 和 JOIN 子句中的查询性能。
• 避免在 SELECT 语句中使用 SELECT *，明确指定需要的列，提高查询效率。

DML 数据操作语言

DML 用于对数据进行增删改操作。INSERT 添加新记录，UPDATE 更新已有数据，DELETE 删除符合条件的记录。这些操作会直接修改数据库中的实际数据。

-- 插入新员工记录
INSERT INTO employees (name, dept_id, salary) VALUES ('张三', 1, 6000);

-- 批量插入多条记录
INSERT INTO employees (name, dept_id, salary) VALUES
('李四', 2, 5500),
('王五', 1, 7000),
('赵六', 3, 5000);

-- 更新员工的工资
UPDATE employees SET salary = 6500 WHERE name = '张三';

-- 批量更新部门编号
UPDATE employees SET dept_id = 2 WHERE dept_id = 1;

-- 删除工资低于4000的员工记录
DELETE FROM employees WHERE salary < 4000;

最佳实践

• 在执行 UPDATE 或 DELETE 操作前，使用 SELECT 语句确认影响的记录范围。
• 使用事务（TCL）包裹多条 DML 语句，确保操作的原子性。
• 设置适当的约束（如外键）防止数据不一致。

DDL 数据定义语言

DDL 负责数据库对象的创建、修改和删除。包括数据库、表、索引等结构的定义。这些操作会改变数据库的结构而不是数据本身。

-- 创建新的数据库
CREATE DATABASE company_db;

-- 使用指定的数据库
USE company_db;

-- 创建员工表
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    dept_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 修改表结构，添加邮箱字段
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

-- 删除表中的邮箱字段
ALTER TABLE employees DROP COLUMN email;

-- 删除员工表
DROP TABLE employees;

-- 创建索引以优化查询
CREATE INDEX idx_salary ON employees(salary);

-- 删除索引
DROP INDEX idx_salary ON employees;

最佳实践

• 在创建表时，合理设计主键和外键，确保数据的完整性。
• 使用 IF EXISTS 或 IF NOT EXISTS 选项避免因对象已存在或不存在而导致的错误。
• 为频繁查询的列创建索引，提高查询性能，但避免过多索引导致写操作性能下降。

DCL 数据控制语言

DCL 管理数据库的访问权限。通过 GRANT 授予权限，REVOKE 收回权限，实现细粒度的访问控制。

-- 创建新用户
CREATE USER 'john_doe'@'localhost' IDENTIFIED BY 'secure_password';

-- 授予用户对特定数据库的所有权限
GRANT ALL PRIVILEGES ON company_db.* TO 'john_doe'@'localhost';

-- 授予用户仅查询权限
GRANT SELECT ON company_db.employees TO 'john_doe'@'localhost';

-- 撤销用户的查询权限
REVOKE SELECT ON company_db.employees FROM 'john_doe'@'localhost';

-- 刷新权限，使更改立即生效
FLUSH PRIVILEGES;

最佳实践

• 遵循最小权限原则，仅授予用户完成其任务所需的最少权限。
• 定期审查用户权限，确保不必要的权限被及时撤销。
• 使用角色（Role）管理权限，简化权限的分配和管理。

TCL 事务控制语言

TCL 确保数据库事务的完整性。BEGIN TRANSACTION 开启事务，COMMIT 提交更改，ROLLBACK 在发生错误时回滚事务。事务具有原子性、一致性、隔离性和持久性特征。

-- 开始事务
START TRANSACTION;

-- 插入新员工
INSERT INTO employees (name, dept_id, salary) VALUES ('孙七', 2, 6200);

-- 更新部门预算
UPDATE departments SET budget = budget - 6200 WHERE id = 2;

-- 提交事务
COMMIT;

-- 如果发生错误，回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT point_name;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT point_name;

最佳实践

• 在涉及多步操作的场景中使用事务，确保数据的一致性。
• 处理事务时，捕获可能的错误，确保在出错时能够正确回滚。
• 设置适当的事务隔离级别，防止并发操作导致的数据问题。

综合示例

以下是一个综合示例，展示如何使用多种 SQL 语句完成一个完整的数据库操作流程。

-- 创建数据库
CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

-- 创建部门表
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    budget DECIMAL(15, 2)
);

-- 创建员工表
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    dept_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 插入部门数据
INSERT INTO departments (dept_name, budget) VALUES
('研发部', 1000000),
('市场部', 500000),
('人事部', 300000);

-- 插入员工数据
INSERT INTO employees (name, dept_id, salary) VALUES
('张三', 1, 6000),
('李四', 2, 5500),
('王五', 1, 7000),
('赵六', 3, 5000);

-- 查询所有员工及其所属部门
SELECT e.name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
ORDER BY e.salary DESC;

-- 更新某员工的工资
UPDATE employees SET salary = 7500 WHERE name = '王五';

-- 删除工资低于5000的员工
DELETE FROM employees WHERE salary < 5000;

-- 创建索引优化查询
CREATE INDEX idx_dept_id ON employees(dept_id);

-- 授予用户查询权限
GRANT SELECT ON company_db.employees TO 'john_doe'@'localhost';

-- 开始事务进行批量更新
BEGIN TRANSACTION;
UPDATE departments SET budget = budget - 6000 WHERE id = 1;
UPDATE employees SET salary = salary + 500 WHERE dept_id = 1;
COMMIT;

解释：

1. 创建名为 company_db 的数据库，并使用该数据库。
2. 创建 departments 和 employees 两张表，并设置外键关联。
3. 插入初始数据到两个表中。
4. 使用 JOIN 查询员工及其所属部门，并按工资降序排列。
5. 更新特定员工的工资，并删除工资低于 5000 的员工。
6. 创建索引以优化基于部门 ID 的查询。
7. 授予用户 john_doe 对 employees 表的查询权限。
8. 使用事务批量更新部门预算和员工工资，确保操作的原子性。

最佳实践

• 使用外键约束维护数据的参照完整性。
• 利用索引提升常用查询的性能，但避免过多索引影响写操作效率。
• 在进行批量更新或删除操作时，使用事务确保操作的完整性和一致性。

---

通过以上分类和示例，希望能帮助您更好地理解和使用 MySQL 常用的 SQL 语句。在实际应用中，多加练习和参考官方文档，将进一步提升您的数据库操作能力。