【数据库】SQL分类

SQL 语言的详细分类和介绍，涵盖核心功能、语句用途、实际示例及数据库差异：

---

1. DQL（数据查询语言，Data Query Language）

定义与用途

• 用于从数据库中检索数据，不修改数据本身。
• 支持复杂的逻辑过滤、排序、聚合和多表关联查询。

核心语句

• SELECT：唯一的核心语句，但可通过子句扩展功能：

  SELECT [DISTINCT] 列名 
  FROM 表名 
  [WHERE 条件] 
  [GROUP BY 分组列] 
  [HAVING 分组过滤条件] 
  [ORDER BY 排序列] 
  [LIMIT/OFFSET 分页];
  

示例

-- 查询年龄大于 18 的用户，按年龄降序排列，返回前 10 条
SELECT name, age 
FROM users 
WHERE age > 18 
ORDER BY age DESC 
LIMIT 10;

-- 多表关联查询（JOIN）
SELECT u.name, o.order_id 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'paid';

注意事项

• SELECT * 可能导致性能问题，建议明确指定列。
• 聚合函数（如 SUM, COUNT）需结合 GROUP BY 使用。
• 不同数据库分页语法不同：
  • MySQL: LIMIT 10 OFFSET 20
  • SQL Server: OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

---

2. DML（数据操作语言，Data Manipulation Language）

定义与用途

• 对数据库中的数据进行增、删、改操作。
• 直接影响数据内容，但可通过事务回滚。

核心语句

语句	用途	示例
INSERT	插入新数据	INSERT INTO users (name, age) VALUES ('Alice', 25);
UPDATE	更新现有数据	UPDATE users SET age = 26 WHERE name = 'Alice';
DELETE	删除数据	DELETE FROM users WHERE age < 18;
MERGE	合并数据（插入或更新）	MERGE INTO target USING source ON (...) WHEN MATCHED THEN UPDATE ...

注意事项

• DELETE 不带 WHERE 会清空整个表（危险操作！）。
• MERGE（UPSERT）的兼容性：
  • Oracle/SQL Server: 支持 MERGE
  • MySQL: 使用 INSERT ... ON DUPLICATE KEY UPDATE
  • PostgreSQL: 使用 INSERT ... ON CONFLICT DO UPDATE

---

3. DDL（数据定义语言，Data Definition Language）

定义与用途

• 定义或修改数据库结构（表、索引、视图等）。
• 通常自动提交（不可回滚，取决于数据库）。

核心语句

语句	用途	示例
CREATE	创建新对象（表、索引等）	CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
ALTER	修改对象结构	ALTER TABLE users ADD COLUMN email VARCHAR(100);
DROP	删除对象	DROP TABLE users;
TRUNCATE	清空表数据	TRUNCATE TABLE logs;
RENAME	重命名对象	ALTER TABLE users RENAME TO customers; （MySQL）

注意事项

• TRUNCATE vs DELETE：
  • TRUNCATE：DDL 操作，清空表并重置自增列，不可回滚，速度快。
  • DELETE：DML 操作，逐行删除，可回滚，触发触发器。
• 不同数据库的 ALTER 语法差异：
  • 添加列：MySQL 使用 ADD COLUMN，SQL Server 可省略 COLUMN。

---

4. DCL（数据控制语言，Data Control Language）

定义与用途

• 管理数据库访问权限和安全性。
• 控制用户或角色对数据库对象的操作权限。

核心语句

语句	用途	示例
GRANT	授予权限	GRANT SELECT, INSERT ON users TO user1;
REVOKE	撤销权限	REVOKE DELETE ON orders FROM user2;
DENY	显式拒绝权限（SQL Server）	DENY UPDATE ON products TO guest;

权限类型

• 对象级权限：SELECT, INSERT, UPDATE, DELETE, EXECUTE 等。
• 数据库级权限：CREATE TABLE, BACKUP DATABASE 等。

示例

-- 创建角色并授权（PostgreSQL）
CREATE ROLE analyst;
GRANT SELECT ON sales_data TO analyst;
GRANT analyst TO user3;

---

5. TCL（事务控制语言，Transaction Control Language）

定义与用途

• 管理数据库事务的原子性、一致性、隔离性、持久性（ACID）。
• 确保多个操作要么全部成功，要么全部失败。

核心语句

语句	用途	示例
COMMIT	提交事务，永久保存更改	COMMIT;
ROLLBACK	回滚事务，撤销未提交的更改	ROLLBACK;
SAVEPOINT	设置事务保存点，部分回滚	SAVEPOINT sp1; ROLLBACK TO sp1;
SET TRANSACTION	设置事务属性（隔离级别等）	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

事务示例

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE user = 'Bob';
-- 检查余额是否合法
IF (SELECT balance FROM accounts WHERE user = 'Alice') >= 0 THEN
  COMMIT;
ELSE
  ROLLBACK;
END IF;

数据库差异

• MySQL：默认自动提交，可通过 SET autocommit = 0 关闭。
• PostgreSQL：需显式使用 BEGIN 和 COMMIT。
• Oracle：事务隐式开始，需显式提交或回滚。

---

6. 扩展与补充

存储过程与触发器

• 通常归类为 PL/SQL（Oracle） 或 T-SQL（SQL Server）。
• 示例（MySQL 存储过程）：

  DELIMITER //
  CREATE PROCEDURE GetUser(IN userId INT)
  BEGIN
    SELECT * FROM users WHERE id = userId;
  END //
  DELIMITER ;
  

实用命令

命令	用途	示例	数据库支持
EXPLAIN	分析查询执行计划	EXPLAIN SELECT * FROM users;	MySQL, PostgreSQL
SHOW TABLES	显示所有表	SHOW TABLES;	MySQL
VACUUM	清理数据库空间（PostgreSQL）	VACUUM FULL;	PostgreSQL

---

总结对比

分类	操作对象	是否持久化	是否可回滚	典型场景
DQL	数据	否	无需回滚	数据分析、报表生成
DML	数据	是（需提交）	可回滚（事务内）	用户注册、订单更新
DDL	结构	立即生效	通常不可回滚	创建新表、修改字段类型
DCL	权限	立即生效	部分数据库可回滚	用户权限分配
TCL	事务	依赖提交	事务内可回滚	转账、批量数据处理