SQL 四大语言分类详解：DDL、DML、DCL、DQL
SQL（结构化查询语言）通常被分为四种主要类型，每种类型负责不同的数据库操作。下面我将详细介绍这四类SQL语言的语法和用途。
一、DDL (Data Definition Language) 数据定义语言
功能：定义和管理数据库对象结构（表、视图、索引等）
主要命令：
CREATE - 创建数据库对象
-- 创建数据库
CREATE DATABASE school;
-- 创建表
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT CHECK (age > 0),
class_id INT,
FOREIGN KEY (class_id) REFERENCES classes(id)
);
-- 创建索引
CREATE INDEX idx_name ON students(name);
-- 创建视图
CREATE VIEW student_view AS
SELECT id, name FROM students WHERE age > 10;
ALTER - 修改数据库对象
-- 添加列
ALTER TABLE students ADD COLUMN gender CHAR(1);
-- 修改列类型
ALTER TABLE students MODIFY COLUMN name VARCHAR(100);
-- 删除列
ALTER TABLE students DROP COLUMN gender;
DROP - 删除数据库对象
DROP TABLE IF EXISTS temp_students;
DROP VIEW student_view;
TRUNCATE - 清空表数据（保留结构）
TRUNCATE TABLE log_data;
二、DML (Data Manipulation Language) 数据操作语言
功能：操作数据库中的数据记录
主要命令：
INSERT - 插入数据
-- 插入单条记录
INSERT INTO students (name, age, class_id)
VALUES ('张三', 15, 1);
-- 插入多条记录
INSERT INTO students (name, age, class_id)
VALUES ('李四', 16, 1),
('王五', 14, 2);
-- 从其他表插入数据
INSERT INTO graduate_students
SELECT * FROM students WHERE age > 18;
UPDATE - 更新数据
-- 更新单列
UPDATE students SET age = 16 WHERE name = '张三';
-- 更新多列
UPDATE students
SET age = age + 1, class_id = 3
WHERE id = 5;
-- 使用子查询更新
UPDATE students
SET class_id = (SELECT id FROM classes WHERE name = '高三')
WHERE age > 17;
DELETE - 删除数据
-- 删除特定记录
DELETE FROM students WHERE id = 10;
-- 删除所有记录
DELETE FROM temp_students;
-- 使用子查询删除
DELETE FROM students
WHERE class_id IN (SELECT id FROM classes WHERE grade = '毕业班');
MERGE - 合并操作（UPSERT）
-- MySQL语法
INSERT INTO students (id, name, age)
VALUES (1, '张三', 15)
ON DUPLICATE KEY UPDATE age = 16;
-- PostgreSQL语法
INSERT INTO students (id, name, age)
VALUES (1, '张三', 15)
ON CONFLICT (id) DO UPDATE SET age = 16;
三、DQL (Data Query Language) 数据查询语言
功能：查询数据库中的数据
主要命令：
SELECT - 查询数据
-- 基本查询
SELECT * FROM students;
-- 条件查询
SELECT name, age FROM students WHERE age > 15;
-- 排序
SELECT * FROM students ORDER BY age DESC, name ASC;
-- 分组聚合
SELECT class_id, COUNT(*) as student_count, AVG(age) as avg_age
FROM students
GROUP BY class_id
HAVING COUNT(*) > 5;
-- 连接查询
SELECT s.name, c.class_name
FROM students s
JOIN classes c ON s.class_id = c.id;
-- 子查询
SELECT name FROM students
WHERE class_id IN (SELECT id FROM classes WHERE grade = '高一');
-- 分页查询
SELECT * FROM students LIMIT 10 OFFSET 20; -- MySQL
SELECT * FROM students OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- SQL标准
WITH (CTE) - 公用表表达式
WITH top_students AS (
SELECT * FROM students ORDER BY score DESC LIMIT 10
)
SELECT * FROM top_students WHERE gender = 'F';
四、DCL (Data Control Language) 数据控制语言
功能：控制数据库访问权限和事务处理
主要命令：
GRANT - 授予权限
-- 授予SELECT权限
GRANT SELECT ON students TO user1;
-- 授予所有权限
GRANT ALL PRIVILEGES ON database.* TO 'admin'@'localhost';
-- 授予特定列权限
GRANT SELECT (name, age), UPDATE (age) ON students TO teacher_role;
REVOKE - 撤销权限
-- 撤销权限
REVOKE INSERT ON students FROM user2;
-- 撤销所有权限
REVOKE ALL PRIVILEGES ON database.* FROM 'old_admin'@'localhost';
COMMIT - 提交事务
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
ROLLBACK - 回滚事务
BEGIN TRANSACTION;
DELETE FROM orders WHERE status = 'pending';
-- 发现错误
ROLLBACK;
SAVEPOINT - 设置保存点
BEGIN TRANSACTION;
INSERT INTO log (message) VALUES ('Operation started');
SAVEPOINT sp1;
UPDATE data SET value = 10 WHERE id = 1;
-- 部分回滚
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
五、四种语言对比总结
类别全称主要功能常用命令特点DDLData Definition Language定义数据结构CREATE, ALTER, DROP, TRUNCATE自动提交，不可回滚DMLData Manipulation Language操作数据记录INSERT, UPDATE, DELETE, MERGE需要显式提交，可回滚DQLData Query Language查询数据SELECT, WITH不改变数据，只检索DCLData Control Language权限控制GRANT, REVOKE, COMMIT, ROLLBACK管理访问和事务
六、实际应用示例
场景：学生管理系统操作
-- DDL: 创建表结构
CREATE TABLE classes (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
grade VARCHAR(20)
) ENGINE=InnoDB;
-- DML: 插入班级数据
INSERT INTO classes (name, grade) VALUES
('一班', '高一'), ('二班', '高一'), ('三班', '高二');
-- DQL: 查询班级信息
SELECT * FROM classes WHERE grade = '高一';
-- DML: 更新班级信息
UPDATE classes SET grade = '高三' WHERE name = '三班';
-- DCL: 创建用户并授权
CREATE USER 'teacher'@'%' IDENTIFIED BY 'password';
GRANT SELECT, UPDATE ON school.students TO 'teacher'@'%';
GRANT SELECT ON school.classes TO 'teacher'@'%';
-- DDL: 添加索引提高查询性能
CREATE INDEX idx_class_grade ON classes(grade);
-- 事务处理示例 (DCL)
BEGIN TRANSACTION;
-- DML: 转班操作
UPDATE students SET class_id = 2 WHERE id = 101;
UPDATE class_stats SET student_count = student_count - 1 WHERE class_id = 1;
UPDATE class_stats SET student_count = student_count + 1 WHERE class_id = 2;
COMMIT;