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) 数据操作语言

功能：操作数据库中的数据记录

主要命令：

1. 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) 数据查询语言

功能：查询数据库中的数据

主要命令：

1. 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) 数据控制语言

功能：控制数据库访问权限和事务处理

主要命令：

1. 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;

五、四种语言对比总结

六、实际应用示例

场景：学生管理系统操作

-- 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;