SQL 数据删除与事务控制

SQL 数据删除与事务控制学习笔记

在 SQL 中，数据删除和事务控制是数据库管理的重要部分。

1. 数据删除操作

（1）DELETE：删除表中的数据

• 语法：

  DELETE FROM 表名 WHERE 条件;

• 特点：
  • 可以删除满足条件的行。
  • 删除的数据可以通过 ROLLBACK 回滚。
• 示例：

  -- 删除姓名为小白的员工
  DELETE FROM A WHERE empno = 3;
  
  -- 回滚删除操作
  ROLLBACK;

（2）TRUNCATE：清空表中的数据

• 语法：

  TRUNCATE TABLE 表名;

• 特点：
  • 清空表中的所有数据。
  • 清空的数据无法通过 ROLLBACK 回滚。
• 示例：

  -- 清空表 A 的数据
  TRUNCATE TABLE A;

（3）DROP：删除表

• 语法：

  DROP TABLE 表名;

• 特点：
  • 删除表及其所有数据。
  • 删除的表无法通过 ROLLBACK 回滚。
• 示例：

  -- 删除表 A
  DROP TABLE A;

2. 事务控制

事务是数据库操作的基本单位，具有四大特性（ACID）：

1. 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败。
2. 一致性（Consistency）：事务执行前后，数据库的状态必须保持一致。
3. 隔离性（Isolation）：多个事务并发执行时，彼此之间互不干扰。
4. 持久性（Durability）：事务提交后，对数据库的修改是永久性的。

（1）事务的基本操作

• COMMIT：提交事务，保存对数据库的修改。
• ROLLBACK：回滚事务，撤销对数据库的修改。
• SAVEPOINT：设置保存点，用于部分回滚。

示例：

-- 设置保存点
SAVEPOINT a7566;

-- 删除员工编号为 7566 的记录
DELETE FROM A WHERE empno = 7566;

-- 设置保存点
SAVEPOINT a7521;

-- 删除员工编号为 7521 的记录
DELETE FROM A WHERE empno = 7521;

-- 回滚到保存点 a7566
ROLLBACK TO SAVEPOINT a7566;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

（2）事务锁

事务锁用于控制并发访问，防止多个用户同时修改同一数据。

当出现多用户（session）并发访问数据库的同一张表，进行操作，会产生不必要误会，会破坏对方操作，比如一个用户删除 EMPNO = 7521，另一个用户查找 EMPNO = 7521

示例：

-- 为表 A 加锁
SELECT * FROM A FOR UPDATE;

-- 释放锁（通过提交或回滚）
COMMIT;
ROLLBACK;

3. 事务的四大特性（ACID）

（1）原子性（Atomicity）

• 事务中的所有操作要么全部成功，要么全部失败。
• 示例：银行转账操作必须同时成功或失败。

（2）一致性（Consistency）

• 事务执行前后，数据库的状态必须保持一致。
• 示例：转账前后，账户总金额不变。

（3）隔离性（Isolation）

• 多个事务并发执行时，彼此之间互不干扰。
• 示例：用户 A 和用户 B 同时操作同一张表，不会互相影响。

（4）持久性（Durability）

• 事务提交后，对数据库的修改是永久性的。
• 示例：转账成功后，即使系统崩溃，转账结果也不会丢失。

---

4. 综合示例

（1）银行转账案例

-- 创建银行表
CREATE TABLE t_bank (
    ename VARCHAR2(10),
    sal NUMBER
);

-- 插入测试数据
INSERT INTO t_bank VALUES ('A', 1000);
INSERT INTO t_bank VALUES ('B', 1000);
COMMIT;

-- 转账操作
BEGIN
    -- A 给 B 转账 100 元
    UPDATE t_bank SET sal = sal - 100 WHERE ename = 'A';
    UPDATE t_bank SET sal = sal + 100 WHERE ename = 'B';
    
    -- 提交事务
    COMMIT;
EXCEPTION
    -- 回滚事务
    WHEN OTHERS THEN
        ROLLBACK;
END;

（2）事务锁示例

-- 会话 1：为表 A 加锁
SELECT * FROM A FOR UPDATE;

-- 会话 2：尝试删除表 A 中的数据（会被阻塞）
DELETE FROM A WHERE empno = 7566;

-- 会话 1：提交事务，释放锁
COMMIT;

（3） 验证事物原子性

savepoint a7566; --事务保存点
DELETE FROM a WHERE EMPNO = 7566;
savepoint a7521;--事务保存点
DELETE FROM a WHERE EMPNO = 7521;
savepoint a7564;--事务保存点
DELETE FROM a WHERE EMPNO = 7654;
savepoint a0000;

--回滚到保存点
rollback to savepoint a7566;
--回滚
rollback; --针对DML语句
--提交
commit;   --针对DML语句  

--查询A 
SELECT * FROM A 

5. 总结

1. 数据删除操作：DELETE、TRUNCATE 和 DROP 的使用方法及区别。
2. 事务控制：COMMIT、ROLLBACK 和 SAVEPOINT 的使用方法。
3. 事务的四大特性：原子性、一致性、隔离性和持久性。
4. 事务锁：使用事务锁控制并发访问。