数据库圣经--update delete 截断表(TRUNCATE)

update

语法

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

• UPDATE table_name：指定要更新的表名。
• SET column = expr [, column = expr ...]：设置要更新的列以及对应的新值表达式，多个列的更新用逗号分隔。
• [WHERE ...]：可选的条件子句，用于指定要更新的行，若省略则更新表中所有行。
• [ORDER BY ...]：可选，用于指定更新行的顺序（部分数据库支持）。
• [LIMIT ...]：可选，用于限制更新的行数（部分数据库支持）。

注：

（1）对符合条件的结果进行列值更新

（2）要先查找，找到之后再进行修改

MySQL 有三种查询表结构得方法

1.使用desc

desc table_name;

2.使用show

show columns from 表名;

3.使用describe 

describe 表名;

示例：

 将总成绩倒数前三的3位同学的数学成绩加上30分

update student set ascores = ascores + 30 order by  ascores Asc limit 3; 

博主一开始写得是

update student set ascores = ascores + 30 order by  ascores Asc limit (0,3);

这条语句的是错误的，在于 LIMIT 不能使用括号包裹参数，且 UPDATE 语句中 LIMIT 不支持 offset, count 这种逗号分隔的偏移量语法。

update student set ascores = ascores + 30 order by  ascores Asc limit 3 offset 0;

也不支持这个，但是确实是对的

值为0的更新操作：

# 更新操作
update exam set chinese = chinese * 2;
# 查看结果
select * from exam;

加入说我要修改的是这四个人的语文成绩，但是执行结果是

这时候外面看到有一条语句没有被执行到是语文成绩为0 的这位同学

语文成绩原来为 0 的记录因未发生实际改变，未被实际修改，故出现匹配 4 条记录但仅修改 3 条记录的情况。

注：

（1）以原值的基础上做变更时，不能使用math+=30这样的语法

（2）不加where条件时，会导致全表数据被更新，谨慎操作

delete

语法：

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

DELETE 和 DROP 都可以操作表数据，但作用和场景完全不同，核心区别如下：

1. DELETE：删除表中的数据行（保留表结构）

• 作用：仅删除表中满足条件的记录（行），表本身的结构（列、约束、索引等）会保留。
• 语法：

-- 删除表中所有数据（保留表结构）
DELETE FROM 表名;

-- 删除满足条件的数据（推荐，避免误删全表）
DELETE FROM 表名 WHERE 条件;

• 特点：
  • 可以通过 WHERE 子句精确筛选要删除的行。
  • 操作可以被 ROLLBACK（回滚）（如果在事务中）。
  • 执行后会记录日志，删除大量数据时效率较低。

2. DROP：删除整个表（包括结构和数据）

• 作用：直接删除整个表，包括表的结构、所有数据、索引、约束等，表将不复存在。
• 语法：

DROP TABLE 表名;

• 特点：
  • 会彻底删除表，无法只删除部分数据。
  • 操作通常无法回滚（除非数据库有特殊配置），风险极高。
  • 执行速度快，但一旦删除无法恢复（除非有备份）。

Delete注意事项：

        执行Delete时不加条件会删除整张表的数据，记得加where 限制条件

1. DELETE 操作的风险

• 若执行 DELETE 时不加 WHERE 条件，会清空整张表的数据，属于非常危险的操作。

2. 数据恢复机制

• 执行的 SQL 会被记录到日志中，可通过重放日志中的操作来恢复被误删的数据。

3. 生产环境的替代方案

• 生产环境一般不直接使用 DELETE 操作，而是采用逻辑删除的方式：
  • 在表中添加 deleteState 字段（0 表示正常，1 表示已删除）。
  • 通过 UPDATE 操作更新 deleteState 字段来实现 “删除” 功能，数据实质上仍保存在数据库中。

生产环境的替代方案

我们以一个 users 表为例来演示逻辑删除的实现：

步骤 1：创建带逻辑删除字段的表

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    age INT,
    deleteState TINYINT DEFAULT 0  -- 逻辑删除字段，0正常，1已删除
);

步骤 2：插入测试数据

INSERT INTO users (username, age) VALUES 
('张三', 25),
('李四', 30),
('王五', 28);

步骤 3：执行 “逻辑删除”（更新 deleteState 字段）

比如要 “删除” 用户李四，执行：

UPDATE users 
SET deleteState = 1 
WHERE username = '李四';

步骤 4：查询正常数据（过滤已删除记录）

需要查询未被 “删除” 的用户时，添加 deleteState = 0 的条件：

SELECT * FROM users WHERE deleteState = 0;

此时结果只有张三和王五，李四因 deleteState=1 被过滤，但数据仍在表中。

步骤 5：恢复 “删除” 的记录（可选）

如果要恢复李四的记录，只需将 deleteState 改回 0：

UPDATE users 
SET deleteState = 0 
WHERE username = '李四';

截断表

1. 核心定义

截断表 是一种在数据库管理系统中使用的 SQL 命令（通常是 TRUNCATE TABLE），它的作用是快速删除一个表中的所有行（记录），但保留表的结构。

您可以把它想象成：

• 有一个装满文件的文件柜（表）。
• 截断 这个操作相当于把整个文件柜的抽屉清空，但文件柜本身还在。
• 之后您还可以继续往这个文件柜里放新的文件。

与之相对的是 删除 操作：

• 删除 是使用 DELETE FROM table_name 命令，它更像是一张一张地把文件拿出来扔掉。

2. 主要特点

速度快

这是截断表最显著的优势。原因在于：

• 最小化日志记录：TRUNCATE 通常只将整个数据页的释放操作记录到日志中，而不是像 DELETE 那样逐行记录每一行的删除。这使得日志量非常小，速度极快。
• 不触发触发器：TRUNCATE 操作不会激活定义在表上的 DELETE 触发器。
• 直接释放空间：它立即释放表数据和索引所占用的磁盘空间给操作系统。

重置标识列

如果表中有自增列（如 SQL Server 的 IDENTITY 或 MySQL 的 AUTO_INCREMENT），TRUNCATE 会将该计数器的值重置为初始值（通常是 1）。下次插入新数据时，会从 1 开始编号。

无法回滚（在大多数情况下）

虽然 TRUNCATE 也是一个事务性操作，并且在某些数据库（如 SQL Server）中可以被包含在事务中并回滚，但在很多数据库的默认设置下，它一旦执行就无法回滚。相比之下，DELETE 操作总是可以在事务中回滚。

注意：这个特性与具体数据库有关。例如，在 Oracle 和 PostgreSQL 中，TRUNCATE 可以被回滚。但在 MySQL 的 InnoDB 引擎中，如果配置为 innodb_file_per_table，则 TRUNCATE 实际上类似于 DROP 和重新 CREATE 表，在事务外执行也无法回滚。因此，请务必谨慎使用！

无法带条件

TRUNCATE TABLE 后面只能跟表名，不能使用 WHERE 子句。它要么清空整个表，要么不清空。而 DELETE 可以使用 WHERE 子句来指定删除哪些行。

4. 语法示例

语法非常简单：

TRUNCATE TABLE 表名;

TRUNCATE [TABLE] tbl_name

例如，要清空一个名为 Employees 的表：

TRUNCATE TABLE Employees;

执行这条语句后，Employees 表就变成一个空表，但其所有列名、数据类型、索引、约束等结构都完好无损。

5. 使用场景与注意事项

使用场景：

• 清空测试数据：在开发或测试环境中，需要快速清空表并重新开始。
• 清空临时表：清空用于中间计算的临时表。
• 清空日志表或归档表：当需要定期清理历史数据，且不再需要这些数据时。

注意事项（非常重要！）：

• 数据丢失：这是一个破坏性操作，数据一旦被截断，几乎无法恢复。执行前务必确认。
• 权限要求：通常需要较高的权限（如 ALTER 权限），而 DELETE 通常只需要 DELETE 权限。
• 外键约束：如果表被其他表通过外键引用，在某些数据库系统中可能无法直接截断，需要先处理外键约束。

2、示例

# 准备测试表
CREATE TABLE t_truncate (
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(20)
     );
# 插⼊测试数据
INSERT INTO t_truncate (name) VALUES ('A'), ('B'), ('C');
# 查看测试表
select * from t_truncate;

# 查看建表结构，AUTO_INCREMENT= 4
show create table t_truncate;

# 截断表，注意受影响的⾏数是0
truncate table t_truncate;
# 查看表中的数据
select * from t_truncate;

# 再次查看表结构，AUTO_INCREMENT=2
show create table t_truncate;

3、Truncate注意事项

（1）只能对整表操作，不能像DELETE⼀样针对部分数据

（2）不对数据操作所以比DELETE更快，TRUNCATE在删除数据的时候，不经过真正的事物，所以无法回滚

（3）会重置AUTO_INCREMENT项