SQL 四大语言分类详解：DDL、DML、DCL、DQL_sqlite dml ddl

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 ASSELECT 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_studentsSELECT * FROM students WHERE age > 18;

UPDATE - 更新数据

-- 更新单列UPDATE students SET age = 16 WHERE name = \'张三\';-- 更新多列UPDATE students SET age = age + 1, class_id = 3WHERE id = 5;-- 使用子查询更新UPDATE studentsSET class_id = (SELECT id FROM classes WHERE name = \'高三\')WHERE age > 17;

DELETE - 删除数据

-- 删除特定记录DELETE FROM students WHERE id = 10;-- 删除所有记录DELETE FROM temp_students;-- 使用子查询删除DELETE FROM studentsWHERE 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_ageFROM studentsGROUP BY class_idHAVING COUNT(*) > 5;-- 连接查询SELECT s.name, c.class_nameFROM students sJOIN classes c ON s.class_id = c.id;-- 子查询SELECT name FROM studentsWHERE class_id IN (SELECT id FROM classes WHERE grade = \'高一\');-- 分页查询SELECT * FROM students LIMIT 10 OFFSET 20; -- MySQLSELECT * 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 Data Definition Language 定义数据结构 CREATE, ALTER, DROP, TRUNCATE 自动提交，不可回滚 DML Data Manipulation Language 操作数据记录 INSERT, UPDATE, DELETE, MERGE 需要显式提交，可回滚 DQL Data Query Language 查询数据 SELECT, WITH 不改变数据，只检索 DCL Data 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;