SQL基础:DDL、DML、DCL和TCL的区别与使用

简介: 本文详细解析了SQL语言的四大类别:数据定义语言(DDL)、数据操作语言(DML)、数据控制语言(DCL)和事务控制语言(TCL),涵盖每类语句的功能、语法、使用场景及示例。

💡 摘要:你是否曾对SQL语句的分类感到困惑?是否想知道CREATE、SELECT、INSERT这些语句有什么区别?是否想了解如何正确使用事务控制语句?

别担心,SQL语言按照功能被分为四大类别:数据定义语言(DDL)、数据操作语言(DML)、数据控制语言(DCL)和事务控制语言(TCL)。每种类型都有其特定的用途和语法规则。

本文将详细解析这四种SQL语言的区别、语法和使用场景,通过丰富的示例帮助你彻底理解它们的作用。无论你是数据库新手还是希望巩固基础的开发者,这篇文章都将为你提供清晰的指导和实用的技巧。

一、SQL语言分类概述

1. 四大类别简介

SQL语言分类

类别 全称 主要功能 常用语句
DDL Data Definition Language 定义和管理数据库对象 CREATE, ALTER, DROP, TRUNCATE
DML Data Manipulation Language 操作数据库中的数据 SELECT, INSERT, UPDATE, DELETE
DCL Data Control Language 控制数据库访问权限 GRANT, REVOKE
TCL Transaction Control Language 管理数据库事务 COMMIT, ROLLBACK, SAVEPOINT

2. 各类别的作用范围

操作对象对比

text

DDL → 数据库对象(表、视图、索引等)

DML → 数据记录(行级别的操作)  

DCL → 用户权限(访问控制)

TCL → 事务处理(操作序列)

二、DDL:数据定义语言

1. DDL概述

数据定义语言用于定义和管理数据库对象的结构,包括创建、修改、删除数据库对象。

特点

  • ✅ 自动提交(隐式提交)
  • ✅ 主要操作数据库结构
  • ✅ 通常由DBA使用
  • ✅ 执行后立即生效

2. 常用DDL语句

CREATE - 创建对象

sql

-- 创建数据库

CREATE DATABASE company;


-- 创建表

CREATE TABLE employees (

   id INT PRIMARY KEY AUTO_INCREMENT,

   name VARCHAR(50) NOT NULL,

   age INT,

   department VARCHAR(50),

   salary DECIMAL(10,2),

   created_date DATE DEFAULT CURRENT_DATE

);


-- 创建索引

CREATE INDEX idx_employees_department ON employees(department);


-- 创建视图

CREATE VIEW employee_summary AS

SELECT department, COUNT(*) as count, AVG(salary) as avg_salary

FROM employees

GROUP BY department;

ALTER - 修改对象

sql

-- 添加新列

ALTER TABLE employees ADD COLUMN email VARCHAR(100);


-- 修改列类型

ALTER TABLE employees MODIFY COLUMN name VARCHAR(100);


-- 删除列

ALTER TABLE employees DROP COLUMN age;


-- 添加外键约束

ALTER TABLE employees ADD CONSTRAINT fk_department

FOREIGN KEY (department) REFERENCES departments(name);


-- 重命名表

ALTER TABLE employees RENAME TO staff;

DROP - 删除对象

sql

-- 删除表

DROP TABLE employees;


-- 删除数据库

DROP DATABASE company;


-- 删除索引

DROP INDEX idx_employees_department;


-- 删除视图

DROP VIEW employee_summary;

TRUNCATE - 清空表

sql

-- 清空表数据(保留结构)

TRUNCATE TABLE employees;


-- TRUNCATE vs DELETE

-- • TRUNCATE是DDL,自动提交

-- • TRUNCATE更快,不写日志

-- • TRUNCATE重置自增计数器

3. DDL使用注意事项

重要考虑因素

sql

-- 1. 数据丢失风险

DROP TABLE employees; -- 立即删除,无法恢复


-- 2. 性能影响

ALTER TABLE huge_table ADD COLUMN new_column INT; -- 可能锁表


-- 3. 依赖关系

DROP TABLE departments; -- 如果有外键依赖会失败


-- 4. 最佳实践:先备份后操作

CREATE TABLE employees_backup AS SELECT * FROM employees;

三、DML:数据操作语言

1. DML概述

数据操作语言用于对数据库中的数据进行增删改查操作。

特点

  • ✅ 需要显式提交或回滚
  • ✅ 操作数据记录(行级别)
  • ✅ 最常用的SQL类别
  • ✅ 支持事务控制

2. 常用DML语句

SELECT - 数据查询

sql

-- 基础查询

SELECT * FROM employees;


-- 条件查询

SELECT name, salary FROM employees WHERE salary > 5000;


-- 排序查询

SELECT * FROM employees ORDER BY salary DESC;


-- 分组统计

SELECT department, COUNT(*), AVG(salary)

FROM employees

GROUP BY department;


-- 多表连接

SELECT e.name, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.id;

INSERT - 插入数据

sql

-- 插入单条数据

INSERT INTO employees (name, department, salary)

VALUES ('张三', '技术部', 8000);


-- 插入多条数据

INSERT INTO employees (name, department, salary) VALUES

('李四', '市场部', 6000),

('王五', '技术部', 7500),

('赵六', '财务部', 9000);


-- 插入查询结果

INSERT INTO managers (name, department)

SELECT name, department FROM employees WHERE salary > 10000;

UPDATE - 更新数据

sql

-- 更新特定记录

UPDATE employees SET salary = 8500 WHERE name = '张三';


-- 批量更新

UPDATE employees SET salary = salary * 1.1

WHERE department = '技术部';


-- 使用子查询更新

UPDATE employees

SET salary = (SELECT AVG(salary) FROM employees)

WHERE salary < 5000;

DELETE - 删除数据

sql

-- 删除特定记录

DELETE FROM employees WHERE name = '张三';


-- 删除所有记录(可回滚)

DELETE FROM employees;


-- 使用子查询删除

DELETE FROM employees

WHERE department IN (

   SELECT department_name FROM departments WHERE location = '北京'

);

3. DML性能优化

高效DML操作技巧

sql

-- 1. 批量操作代替单条操作

-- 不好:循环执行1000次INSERT

-- 好:一次INSERT多个VALUES


-- 2. 使用WHERE条件限制范围

UPDATE large_table SET status = 1; -- 避免全表更新

UPDATE large_table SET status = 1 WHERE date = '2023-01-01';


-- 3. 索引优化

-- 确保WHERE条件中的字段有索引

四、DCL:数据控制语言

1. DCL概述

数据控制语言用于管理数据库用户的权限和访问控制。

特点

  • ✅ 控制数据安全性
  • ✅ 管理用户权限
  • ✅ 通常由DBA使用
  • ✅ 支持角色管理

2. 常用DCL语句

GRANT - 授予权限

sql

-- 授予SELECT权限

GRANT SELECT ON employees TO user1;


-- 授予多个权限

GRANT SELECT, INSERT, UPDATE ON employees TO user2;


-- 授予所有权限

GRANT ALL PRIVILEGES ON employees TO user3;


-- 授予权限并允许传递

GRANT SELECT ON employees TO user4 WITH GRANT OPTION;


-- 授予数据库所有表的权限

GRANT SELECT ON company.* TO user5;

REVOKE - 撤销权限

sql

-- 撤销SELECT权限

REVOKE SELECT ON employees FROM user1;


-- 撤销多个权限

REVOKE INSERT, UPDATE ON employees FROM user2;


-- 撤销所有权限

REVOKE ALL PRIVILEGES ON employees FROM user3;


-- 撤销GRANT OPTION

REVOKE GRANT OPTION ON employees FROM user4;

3. 权限管理实践

完整的权限管理示例

sql

-- 创建用户

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'password';

CREATE USER 'editor_user'@'%' IDENTIFIED BY 'password';


-- 授予只读权限

GRANT SELECT ON company.* TO 'readonly_user'@'localhost';


-- 授予编辑权限

GRANT SELECT, INSERT, UPDATE, DELETE ON company.employees TO 'editor_user'@'%';

GRANT SELECT, INSERT, UPDATE ON company.departments TO 'editor_user'@'%';


-- 创建角色并授权

CREATE ROLE 'data_analyst';

GRANT SELECT ON company.* TO 'data_analyst';

GRANT EXECUTE ON PROCEDURE company.* TO 'data_analyst';


-- 将角色授予用户

GRANT 'data_analyst' TO 'analyst_user'@'localhost';


-- 查看权限

SHOW GRANTS FOR 'readonly_user'@'localhost';

五、TCL:事务控制语言

1. TCL概述

事务控制语言用于管理数据库事务,确保数据的一致性。

事务特性(ACID)

  • 原子性(Atomicity):事务全部完成或全部不完成
  • 一致性(Consistency):事务前后数据库状态一致
  • 隔离性(Isolation):事务之间相互隔离
  • 持久性(Durability):事务提交后结果永久保存

2. 常用TCL语句

事务管理

sql

-- 开始事务(MySQL中通常使用START TRANSACTION)

START TRANSACTION;


-- 或者设置自动提交为OFF

SET AUTOCOMMIT = 0;


-- 执行一系列DML操作

INSERT INTO orders (customer_id, amount) VALUES (1, 100.00);

UPDATE accounts SET balance = balance - 100 WHERE customer_id = 1;


-- 提交事务

COMMIT;


-- 或者回滚事务

ROLLBACK;

SAVEPOINT - 保存点

sql

START TRANSACTION;


-- 执行一些操作

INSERT INTO log_entries (message) VALUES ('开始处理订单');

SAVEPOINT step1;


UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101;

SAVEPOINT step2;


INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 101, 1);

SAVEPOINT step3;


-- 如果某个步骤失败,可以回滚到保存点

ROLLBACK TO SAVEPOINT step2;


-- 或者提交整个事务

COMMIT;

3. 事务实践示例

银行转账事务

sql

-- 开始事务

START TRANSACTION;


-- 记录转账日志

INSERT INTO transfer_log (from_account, to_account, amount, transfer_time)

VALUES (1001, 1002, 500.00, NOW());


-- 扣减转出账户余额

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1001;


-- 增加转入账户余额  

UPDATE accounts SET balance = balance + 500 WHERE account_id = 1002;


-- 检查余额是否充足(应用程序逻辑)

-- 如果余额不足,执行ROLLBACK


-- 提交事务

COMMIT;

错误处理事务

sql

-- 使用存储过程处理事务

DELIMITER //


CREATE PROCEDURE safe_transfer(

   IN from_acc INT,

   IN to_acc INT,

   IN amount DECIMAL(10,2)

)

BEGIN

   DECLARE EXIT HANDLER FOR SQLEXCEPTION

   BEGIN

       ROLLBACK;

       SELECT '转账失败:发生错误' AS result;

   END;


   START TRANSACTION;

   

   -- 检查余额

   IF (SELECT balance FROM accounts WHERE account_id = from_acc) < amount THEN

       ROLLBACK;

       SELECT '转账失败:余额不足' AS result;

   ELSE

       -- 执行转账

       UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;

       UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;

       INSERT INTO transfer_log (from_account, to_account, amount)

       VALUES (from_acc, to_acc, amount);

       

       COMMIT;

       SELECT '转账成功' AS result;

   END IF;

END //


DELIMITER ;

六、四种类别的综合使用

1. 完整业务流程示例

员工管理系统操作

sql

-- DDL:创建表结构

CREATE TABLE departments (

   id INT PRIMARY KEY AUTO_INCREMENT,

   name VARCHAR(50) NOT NULL,

   manager_id INT

);


CREATE TABLE employees (

   id INT PRIMARY KEY AUTO_INCREMENT,

   name VARCHAR(100) NOT NULL,

   department_id INT,

   salary DECIMAL(10,2),

   FOREIGN KEY (department_id) REFERENCES departments(id)

);


-- DML:插入初始数据

INSERT INTO departments (name) VALUES

('技术部'), ('市场部'), ('财务部');


INSERT INTO employees (name, department_id, salary) VALUES

('张三', 1, 8000),

('李四', 2, 7000),

('王五', 1, 9000);


-- DCL:设置权限

CREATE USER 'hr_manager'@'localhost' IDENTIFIED BY 'securepass';

GRANT SELECT, INSERT, UPDATE ON company.employees TO 'hr_manager'@'localhost';

GRANT SELECT ON company.departments TO 'hr_manager'@'localhost';


-- TCL:事务操作

START TRANSACTION;


-- 调整薪资

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1;


-- 记录薪资调整日志

INSERT INTO salary_adjustments (department_id, adjust_rate, adjust_date)

VALUES (1, 0.1, NOW());


COMMIT;

2. 日常操作场景

常见操作流程

sql

-- 1. 查询数据(DML)

SELECT * FROM employees WHERE department_id = 1;


-- 2. 插入新数据(DML)

INSERT INTO employees (name, department_id, salary)

VALUES ('赵六', 3, 8500);


-- 3. 更新数据(DML)

UPDATE employees SET salary = 9200 WHERE name = '王五';


-- 4. 创建备份(DDL)

CREATE TABLE employees_backup AS SELECT * FROM employees;


-- 5. 权限管理(DCL)

GRANT SELECT ON employees_backup TO 'report_user'@'localhost';


-- 6. 事务控制(TCL)

START TRANSACTION;

DELETE FROM employees WHERE name = '赵六';

ROLLBACK; -- 取消删除

七、最佳实践与常见问题

1. 使用建议

各类别的最佳实践

sql

-- DDL最佳实践

-- • 在生产环境谨慎使用DDL

-- • 先在测试环境测试DDL变更

-- • 使用版本控制管理DDL脚本


-- DML最佳实践  

-- • 使用WHERE条件避免全表操作

-- • 批量操作提高性能

-- • 使用事务保证数据一致性


-- DCL最佳实践

-- • 遵循最小权限原则

-- • 定期审计用户权限

-- • 使用角色管理权限


-- TCL最佳实践

-- • 保持事务简短

-- • 避免在事务中进行用户交互

-- • 正确处理事务异常

2. 常见问题解答

Q: DDL语句为什么自动提交?

A: 因为DDL操作涉及数据库结构变更,需要立即生效,无法回滚。

Q: DML和TCL有什么关系?

A: DML操作通常需要在事务中执行,TCL用于控制这些操作的提交或回滚。

Q: 什么时候需要使用保存点?

A: 在复杂事务中,当需要部分回滚而不是全部回滚时使用保存点。

Q: GRANT和REVOKE可以回滚吗?

A: 不可以,权限管理操作是即时生效的,不在事务控制范围内。

八、总结回顾

1. 核心区别总结

四种类别对比表

特性 DDL DML DCL TCL
操作对象 数据库对象 数据记录 用户权限 事务
提交方式 自动提交 手动提交 立即生效 手动控制
主要语句 CREATE, ALTER SELECT, INSERT GRANT, REVOKE COMMIT, ROLLBACK
使用频率 较低 很高 较低 中等
使用者 DBA/开发者 所有用户 DBA 开发者

2. 学习建议

掌握路径推荐

  1. 先掌握DML:SELECT、INSERT、UPDATE、DELETE
  2. 学习TCL:事务管理,保证数据一致性
  3. 了解DDL:数据库对象创建和管理
  4. 熟悉DCL:权限管理和安全控制

实践建议

  • 🎯 多写多练,实际操作是最好的学习方式
  • 🎯 理解每个语句的执行效果和影响范围
  • 🎯 学习如何优化SQL语句性能
  • 🎯 掌握错误处理和异常情况处理

通过系统学习这四类SQL语言,你将能够全面掌握数据库操作,写出高效、安全的SQL语句,为成为数据库专家打下坚实基础。

相关文章
|
17天前
|
人工智能 JavaScript 前端开发
实战使用 Qwen3-coder 低代码开发 HTML 个人网站
阿里巴巴开源的Qwen3-coder模型,凭借强大性能和低代码能力,助力用户快速搭建个人网站。本文详解环境配置、提示词设计与部署流程,适合编程新手快速上手,掌握AI辅助开发技能。
1139 8