数据库 DDL / DML / DCL 详解

数据库 DDL / DML / DCL 详解在关系型数据库中SQL 语句按功能可分为三大类DDL、DML和DCL。理解它们的区别、用法及适用场景是数据库开发和运维的基础。一、DDLData Definition Language数据定义语言作用用于定义、修改或删除数据库对象的结构如数据库、表、索引、视图、存储过程等。DDL 语句执行后通常自动提交事务无法回滚。常用命令命令说明示例CREATE创建数据库对象CREATE DATABASE db_name;CREATE TABLE users (id INT, name VARCHAR(50));ALTER修改已有对象结构ALTER TABLE users ADD COLUMN age INT;ALTER TABLE users DROP COLUMN age;DROP删除对象结构和数据DROP TABLE users;DROP DATABASE db_name;TRUNCATE清空表中所有数据保留结构TRUNCATE TABLE users;RENAME重命名对象RENAME TABLE users TO user_list;使用场景系统初始化创建数据库、表、索引、主外键等。版本迭代中的结构变更增加字段、修改字段类型、添加约束。清理测试环境删除表或清空表数据TRUNCATE比DELETE更快但不可回滚。数据库重构重命名表或列调整存储位置等。注意事项DDL 操作通常不可撤销部分数据库如 PostgreSQL 支持事务性 DDL但 MySQL InnoDB 中 DDL 多数会隐式提交。执行DROP或TRUNCATE前务必确认数据已备份。二、DMLData Manipulation Language数据操作语言作用用于对表中的数据进行增、删、改、查操作。DML 语句可以显式提交或回滚事务控制。常用命令命令说明示例SELECT查询数据SELECT * FROM users WHERE id 1;INSERT插入新行INSERT INTO users (id, name) VALUES (1, Alice);UPDATE修改现有数据UPDATE users SET name Bob WHERE id 1;DELETE删除行可带条件DELETE FROM users WHERE id 1;MERGE/REPLACE合并/替换数据部分数据库支持MERGE INTO ...使用场景日常业务操作用户注册INSERT、登录验证SELECT、修改个人信息UPDATE、删除订单DELETE。数据导入导出批量 INSERT 或使用 SELECT INTO。报表统计复杂查询、聚合分析、多表关联。数据清洗UPDATE 修正错误数据DELETE 删除无效数据。事务控制搭配START TRANSACTION; -- 开启事务 UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; COMMIT; -- 提交 -- 或 ROLLBACK; -- 回滚注意事项无条件的UPDATE或DELETE会影响整张表务必加上WHERE子句。大量数据操作时建议分批处理并合理使用事务避免锁表过长。三、DCLData Control Language数据控制语言作用用于管理数据库用户权限和访问控制。DCL 语句同样支持事务提交/回滚。常用命令命令说明示例GRANT授予权限GRANT SELECT, INSERT ON db_name.* TO userlocalhost;REVOKE撤销权限REVOKE INSERT ON db_name.* FROM userlocalhost;DENYSQL Server拒绝权限优先级高于 GRANTDENY DELETE ON table_name TO user;CREATE USER/DROP USER管理用户账号CREATE USER john% IDENTIFIED BY password;使用场景多租户系统为不同应用或租户创建独立数据库账号只授予必要权限如只读、读写。安全加固禁止普通用户执行 DDL 或 DROP 操作撤销过期员工权限。运维授权给监控账号授予SELECT和SHOW STATUS权限不给修改权限。开发/测试环境为开发人员分配 schema 级别的增删改查权限限制其访问生产库。权限层级全局级*.*所有数据库的所有对象数据库级db_name.*表级db_name.table_name列级SELECT(col1, col2) ON ...存储过程级EXECUTE ON PROCEDURE ...示例-- 创建只读用户 CREATE USER readonly192.168.1.% IDENTIFIED BY pass123; GRANT SELECT ON mydb.* TO readonly192.168.1.%; -- 授予所有权限谨慎 GRANT ALL PRIVILEGES ON mydb.* TO adminlocalhost WITH GRANT OPTION; -- 撤销权限 REVOKE DELETE ON mydb.orders FROM app_user%; -- 刷新权限MySQL 需要 FLUSH PRIVILEGES;对比总结特性DDLDMLDCL操作对象数据库结构表、库等表内数据记录用户权限是否可回滚多数数据库自动提交难回滚可事务控制COMMIT/ROLLBACK可事务控制取决于实现常用命令CREATE, ALTER, DROP, TRUNCATESELECT, INSERT, UPDATE, DELETEGRANT, REVOKE使用频率较低变更管理时最高业务操作低权限分配时影响范围整个对象结构部分或全部记录用户会话级别综合示例完整业务流程-- DCL创建用户并授予连接权限DBA执行 CREATE USER app_user% IDENTIFIED BY password; GRANT SELECT, INSERT, UPDATE ON mydb.* TO app_user%; -- DDL创建表DBA或开发执行 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100), quantity INT ); -- DML插入数据应用执行 INSERT INTO orders (product_name, quantity) VALUES (Laptop, 2); -- DML查询数据 SELECT * FROM orders WHERE quantity 0; -- DML更新数据 UPDATE orders SET quantity 3 WHERE id 1; -- DML删除数据 DELETE FROM orders WHERE id 1; -- DDL清空表保留结构用于测试重置 TRUNCATE TABLE orders; -- DDL删除表下线功能时 DROP TABLE orders; -- DCL回收权限用户离职时 REVOKE INSERT ON mydb.orders FROM app_user%;实际运维场景举例上线新功能DDL DML先用ALTER TABLE增加新列DDL再用UPDATE为旧数据填充默认值DML数据库迁移DCL DDL DML创建新用户并授权DCL在新库执行建表脚本DDL导出旧库数据并INSERT到新库DML紧急故障处理DML DCL发现恶意删除数据用ROLLBACK回滚未提交事务DML 事务立即撤销可疑用户权限REVOKEDCL在实际工作中通常由 DBA 或运维人员负责 DDL 和 DCL开发人员负责 DML但全栈工程师和运维实施人员都应熟悉所有三类命令。