SQL复习03 | 数据更新

SQL复习系列目录

1. SQL复习01 | SELECT语句基础
2. SQL复习02 | 聚合与排序

---

1. 数据插入(INSERT)

1.1 单行INSERT

INSERT INTO <表名> (列1，列2，列3) VALUES (值1，值2，值2);

1.2 多行INSERT

INSERT INTO <表名> (列1, 列2, 列3)
VALUES
(值1, 值2, 值3),
(值4, 值5, 值6),
(值7, 值8, 值9);

• 在一条 INSERT 语句中，可以插入多行数据，每行都要使用 VALUES 子句指定。
• 在 VALUES 子句中，每个数据值之间要以逗号分隔，且每行数据都应该用括号括起来。
• 所插入的数据值的类型，应该与所插入的列的数据类型相同，并且应该按照列的顺序进行插入。

1.3 列清单的省略（不推荐）

• 对表进行全列INSERT时，可以省略表名后的列清单。

  INSERT INTO <表名>
  VALUES (值1, 值2, 值3);
  

• 插入值的顺序需要与表的列顺序一致

1.4 插入NULL的处理

INSERT INTO <表名> (列1, 列2, 列3)
VALUES (值1, NULL, 值3);

• 想插入NULL的列一定不能设置NOT NULL约束

1.5 插入默认值

• 可以通过在创建表的CREATE TABLE语句中设置DEFAULT约束来设定默认值

  CREATE TABLE <表名> (
     列1 数据类型,
     列2 数据类型 DEFAULT 默认值,
     列3 数据类型
  );
  

• 显式插入默认值（推荐使用，可读性强）

  INSERT INTO <表名> (列1, 列2, 列3)
  VALUES (值1, DEFAULT, 值3);
  

• 隐式插入默认值

  INSERT INTO <表名> (列1, 列3)
  VALUES (值1, 值3);
  

• 若省略了没有设定默认值的列，该列的值就会设定为NULL

1.6 从其他表复制数据

INSERT INTO <表名> (列1, 列2, 列3)
SELECT <列1, 列2, 列3>
FROM <表2>
WHERE <条件>
GROUP BY <聚合键>;

• 复制的数据应该与目标表中的列定义对应。如果选择的列和目标表中的列不一致，则可能会出现错误或截断数据。
• 可以使用WHERE、GROUP BY子句等任何SQL语法，但使用ORDER BY子句并不会产生任何效果，因为无法保证表内部记录的排列顺序

2. 数据删除(DELETE)

2.1 DROP TABLE语句

DROP TABLE <表名>;

• DROP TABLE 语句将从数据库中永久删除表及其所有数据

2.2 DELETE语句

DELETE FROM <表名> WHERE <某些条件>;

• 使用 DELETE FROM 语句将删除表中的数据
• 使用 DELETE FROM 语句删除后数据将无法恢复
• DELETE语句不能使用GROUP BY、HAVING、ORDER BY三类子句，只能使用WHERE子句

2.3 TRUNCATE语句

TRUNCATE <表名>;

• TRUNCATE只能删除表中的全部数据
• 注意：在ORACLE数据库中的TRUNCATE不能使用ROLLBACK。执行TRUNCATE的同时会默认执行COMMIT操作

3. 数据更新(UPDATE)

UPDATE基本语法：

UPDATE <表名>
SET <列名> = <表达式>, ...
WHERE <条件>;

使用NULL进行更新：

UPDATE <表名>
SET <列名> = NULL
WHERE <条件>;

• 只有未设置NOT NULL约束的列才可将值清空为NULL

多列更新：

UPDATE <表名>
SET <列名1> = <表达式1>,<列名2> = <表达式2>,<列名3> = <表达式3>
WHERE <条件>;

• 在更新多个列时，SET 子句中的列名和值必须一一对应
• 并且 WHERE 子句必须指定条件以确保只更新所需的记录行。

4. 事务(TRANSACTION)

事务是需要在同一处理单元中执行的一系列更新处理的集合

事务的开始语句：

-- SQLServer PostgreSQL
BEGIN TRANSACTION;

-- MySQL
START TRANSACTION;

-- Oracle DB2
无

4.1 提交 COMMIT

COMMIT是提交事务包含的全部的更新处理的结束指令，相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态了。

• 一旦误操作，只能重新建表、重新插入数据

4.2 回滚 ROLLBACK

ROLLBACK用于撤销上一次提交或保存点之后的所有更改，将事务回滚到之前的状态。

• 相当于文件处理中的放弃保存

4.3 事务处理何时开始

事务根据DBMS的不同而不同。

事务处理的两种模式：

• 模式A：每条SQL语句就是一个事务（自动提交模式）
• 模式B：直到用户执行COMMIT或者ROLLBACK为止算作一个事务

采用模式A的数据库：MySQL、SQL Server、PostgreSQL

采用模式B的数据库：Oracle

注意：如果在自动提交模式下执行了DELETE操作，即使回滚也无济于事

4.4 ACID特性

1. 原子性（Atomicity）：事务是一个原子性的操作单元，要么全部执行，要么全部回滚，不会发生部分执行的情况。也就是说，在一个事务中，若干操作语句要么全部成功，要么全部失败，不存在只执行了一部分的情况。
2. 一致性（Consistency）：事务执行的结果必须保证数据库从一个一致性状态变为另一个一致性状态。也就是说，事务在执行之前和之后，数据库的约束条件不会被破坏。
3. 隔离性（Isolation）：多个事务并发执行时，彼此之间是相互独立的，在数据库中每个事务必须感觉不到其他的事务在并发执行。也就是说，每个事务执行的结果，都和其他事务执行的结果是相互独立的。
4. 持久性（Durability）：事务一旦提交（或称为持久化），其结果就是永久性的，就算在此之后系统发生故障也不会丢失。即使系统崩溃，也可以通过日志文件来进行恢复，从而保证数据的持久性。