SQL基础教程笔记：第四章 数据更新
数据的插入（INSERT语句的使用方法）
什么是INSERT
-- 创建ProductIns表的CREATE TABLE语句
CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
如前所述，这里仅仅是创建出了一个表，并没有插入数据。接下来，我们就向 ProductIns 表中插入数据。
INSERT语句的基本语法
-- 向表中插入一行数据
INSERT INTO ProductIns (product_id, product_name, product_type,
sale_price, purchase_price, regist_date) VALUES ('0001', 'T恤衫',
'衣服', 1000, 500, '2009-09-20');
由于 product_id 列（商品编号）和product_name列（商品名称）是字符型，所以插入的数据需要像 '0001' 这样用单引号括起来。日期型的regist_date（登记日期）列也是如此.
将列名和值用逗号隔开，分别括在（）内，这种形式称为清单。
当然，表名后面的列清单和 VALUES 子句中的值清单的列数必须保持一致(但是使用默认值时列数无需完全一致),列数不一致时会出错，无法插入数据。
此外，原则上，执行一次 INSERT 语句会插入一行数据。因此，插入多行时，通常需要循环执行相应次数的 INSERT 语句。
多行INSERT
-- 通常的INSERT
INSERT INTO ProductIns VALUES ('0002', '打孔器',
'办公用品', 500, 320, '2009-09-11');
INSERT INTO ProductIns VALUES ('0003', '运动T恤',
'衣服', 4000, 2800, NULL);
INSERT INTO ProductIns VALUES ('0004', '菜刀',
'厨房用具', 3000, 2800, '2009-09-20');
-- 多行INSERT （Oracle以外）
INSERT INTO ProductIns VALUES ('0002', '打孔器',
'办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤',
'衣服', 4000, 2800, NULL),
('0004', '菜刀',
'厨房用具', 3000, 2800, '2009-09-20');
该语法很容易理解，并且减少了书写语句的数量，非常方便。但是，使用该语法时请注意以下几点。
- 首先， INSERT语句的书写内容及插入的数据是否正确。若不正确会发生INSERT错误，但是由于是多行插入，和特定的单一行插入相比，想要找出到底是哪行哪个地方出错了，就变得十分困难。
- 其次，多行 INSERT 的语法并不适用于所有的 RDBMS。该语法适用于 DB2、SQL、SQL Server、 PostgreSQL 和 MySQL，但不适用于 Oracle。
列清单的省略
对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列。
-- 包含列清单
INSERT INTO ProductIns (product_id, product_name, product_type,
sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅',
'厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO ProductIns VALUES ('0005', '高压锅', '厨房用具',
6800, 5000, '2009-01-15');
插入NULL
INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。
-- 向purchase_price列中插入NULL
INSERT INTO ProductIns (product_id, product_name, product_type,
sale_price, purchase_price, regist_date) VALUES ('0006', '叉子',
'厨房用具', 500, NULL, '2009-09-20');
但是，想要插入 NULL 的列一定不能设置 NOT NULL 约束。向设置了 NOT NULL 约束的列中插入 NULL 时， INSERT 语句会出错，导致数据插入失败.插入失败指的是希望通过 INSERT 语句插入的数据无法正常插入到表中，但之前已经插入的数据并不会被破坏。不仅是INSERT， DELETE 和UPDATE等更新语句也一样，SQL语句执行失败时都不会对表中数据造成影响。
插入默认值
我们还可以向表中插入默认值（初始值）。可以通过在创建表的CREATE TABLE 语句中设置 DEFAULT 约束来设定默认值。我们可以通过“DEFAULT < 默认值 >”的形式来设定默认值。如果在创建表的同时设定了默认值，就可以在 INSERT 语句中自动为列赋值了。默认值的使用方法通常有显式和隐式两种。
通过显式方法插入默认值：
-- 通过显式方法设定默认值
INSERT INTO ProductIns (product_id, product_name, product_type,
sale_price, purchase_price, regist_date) VALUES ('0007',
'擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
这样一来， RDBMS 就会在插入记录时自动把默认值赋给对应的列。
通过隐式方法插入默认值:
插入默认值时也可以不使用 DEFAULT 关键字，只要在列清单和VALUES 中省略设定了默认值的列就可以了。
那么在实际使用中哪种方法更好呢？笔者建议大家使用显式的方法。因为这样可以一目了然地知道sale_price列使用了默认值， SQL语句的含义也更加容易理解。
说到省略列名，还有一点要说明一下。如果省略了没有设定默认值的列，该列的值就会被设定为NULL。因此，如果省略的是设置了 NOTNULL 约束的列，INSERT 语句就会出错（代码清单 4-8）。请大家一定要注意。
省略INSERT语句中的列名，就会自动设定为该列的默认值（没有默认值时会设定为NULL）。
从其他表中复制数据
要插入数据，除了使用 VALUES 子句指定具体的数据之外，还可以从其他表中复制数据。下面我们就来学习如何从一张表中选取数据，复制到另外一张表中。
-- 用来插入数据的商品复制表
CREATE TABLE ProductCopy
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
-- 将商品表中的数据复制到商品复制表中
INSERT INTO ProductCopy (product_id, product_name, product_type,
sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price,
purchase_price, regist_date
FROM Product;
执行该 INSERT … SELECT 语句时，如果原来 Product 表中有8 行数据，那么ProductCopy 表中也会插入完全相同的 8 行数据。当然，Product 表中的原有数据不会发生改变。因此， INSERT … SELECT语句可以在需要进行数据备份时使用）。
INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何SQL语法（但使用ORDER BY子句并不会产生任何效果,因为无法保证表内部记录的排列顺序）.
-- 根据商品种类进行汇总的表 ；
CREATE TABLE ProductType
(product_type VARCHAR(32) NOT NULL,
sum_sale_price INTEGER ,
sum_purchase_price INTEGER ,
PRIMARY KEY (product_type));
INSERT INTO ProductType (product_type, sum_sale_price,
sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM Product
GROUP BY product_type;
数据的删除（DELETE语句的使用方法）
DROP TABLE语句和DELETE语句
删除数据的方法大体可以分为以下两种。
- DROP TABLE 语句会完全删除整张表，因此删除之后再想插入数据，就必须使用 CREATE TABLE 语句重新创建一张表。
- DELETE 语句在删除数据（行）的同时会保留数据表，因此可以通过 INSERT 语句再次向表中插入数据。
DELETE语句的基本语法
-- 清空Product表
DELETE FROM Product;
如果语句中忘了写 FROM，而是写成了“DELETE < 表名 >”，或者写了多余的列名，都会出错，无法正常执行，请大家特别注意。
- 前者无法正常执行的原因是删除对象不是表，而是表中的数据行（记录）。这样想的话就很容易理解了吧。
- 后者错误的原因也是如此。因为 DELETE 语句的对象是行而不是列，所以 DELETE 语句无法只删除部分列的数据。因此，在 DELETE 语句中指定列名是错误的。当然，使用星号的写法（DELETE * FROM Product ；）也是不对的，同样会出错。
DELETE语句的删除对象并不是表或者列，而是记录（行）。
指定删除对象的DELETE语句（搜索型DELETE）
想要删除部分数据行时，可以像 SELECT 语句那样使用 WHERE子句指定删除条件。 这种指定了删除对象的 DELETE 语句称为搜索型DELETE。
-- 删除销售单价（sale_price）大于等于4000日元的数据
DELETE FROM Product
WHERE sale_price >= 4000;
与 SELECT 语句不同的是， DELETE 语句中不能使用 GROUP BY、HAVING 和 ORDER BY 三类子句，而只能使用WHERE 子句。原因很简单，GROUP BY 和 HAVING 是从表中选取数据时用来改变抽取数据形式的，而 ORDER BY 是用来指定取得结果显示顺序的。因此，在删除表中数据时它们都起不到什么作用。
数据的更新（UPDATE语句的使用方法）
UPDATE语句的基本语法
使用 INSERT 语句向表中插入数据之后，有时却想要再更改数据，例如“将商品销售单价登记错了”等的时候。这时并不需要把数据删除之后再重新插入，使用 UPDATE语句就可以改变表中的数据了。和INSERT语句、DELETE 语句一样， UPDATE语句也属于 DML语句。通过执行该语句，可以改变表中的数据。其基本语法如下所示。
将更新对象的列和更新后的值都记述在 SET 子句中。
UPDATE Product
SET regist_date = '2009-10-10';
此时，连登记日期原本为 NULL 的数据行（运动 T 恤）的值也更新为 2009-10-10 了。
指定条件的UPDATE语句（搜索型UPDATE）
更新数据时也可以像DELETE 语句那样使用 WHERE 子句，这种指定更新对象的 UPDATE 语句称为搜索型 UPDATE 语句。
UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
SET 子句中赋值表达式的右边不仅可以是单纯的值，还可以是包含列的表达式。
使用NULL进行更新
使用 UPDATE 也可以将列更新为 NULL（该更新俗称为 NULL 清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。
UPDATE Product
SET regist_date = NULL
WHERE product_id = '0008';
和 INSERT 语句一样， UPDATE 语句也可以将 NULL 作为一个值来使用。但是，只有未设置 NOT NULL 约束和主键约束的列才可以清空为NULL。如果将设置了上述约束的列更新为 NULL，就会出错，这点与INSERT 语句相同.
多列更新
UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。
-- 能够正确执行的繁琐的UPDATE语句
-- 一条UPDATE语句只更新一列
UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
UPDATE Product
SET purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
虽然这样也能够正确地更新数据，但执行两次 UPDATE 语句不但有些浪费，而且增加了 SQL 语句的书写量。其实，我们可以将其合并为一条 UPDATE 语句来处理。合并的方法有两种，
- 方法1：
-- 使用逗号对列进行分隔排列
UPDATE Product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
- 方法2：
-- 将列用()括起来的清单形式
UPDATE Product
SET (sale_price, purchase_price) = (sale_price * 10,
purchase_price / 2)
WHERE product_type = '厨房用具';
需要注意的是第一种方法——使用逗号将列进行分隔排列，这一方法在所有的 DBMS 中都可以使用。但是第二种方法——将列清单化，这一方法在某些 DBMS 中是无法使用的(可以在 PostgreSQL 和 DB2 中使用)。因此，实际应用中通常都会使用第一种方法。
事务
什么是事务
在RDBMS中，事务（transaction）是对表中数据进行更新的单位。简单来讲，事务就是需要在同一个处理单元中执行的一系列更新处理的集合。
一个事务中包含多少个更新处理或者包含哪些处理，在DBMS中并没有固定的标准，而是根据用户的要求决定的（例如，运动T恤和T恤衫的销售单价需要同时更新这样的要求， DBMS是无法了解的）。
创建事务
使用事务开始语句和事务结束语句，将一系列 DML 语句（INSERT/UPDATE/DELETE 语句）括起来，就实现了一个事务处理。这时需要特别注意的是事务的开始语句。实际上，在标准 SQL 中并没有定义事务的开始语句(与之相对，事务结束语句只有COMMIT和ROLLBACK两种，在所有的RDBMS中都是通用的)，而是由各个DBMS自己来定义的。比较有代表性的语法如下所示。
-- postgresql
BEGIN TRANSACTION;
-- mysql
/*START TRANSACTION*/
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
COMMIT;
如上所示，各个 DBMS 事务的开始语句都不尽相同，其中 Oracle 和DB2 并没有定义特定的开始语句。可能大家觉得这样的设计很巧妙，其实是因为标准 SQL 中规定了一种悄悄开始事务处理的方法(《标准SQL手册修订第4版》中的记述 ：希望大家注意事务默认开始的时间点。没有“BEGIN TRANSACTION”这样 明确的开始标志。)。因此，即使是经验丰富的工程师也经常会忽略事务处理开始的时间点。反之，事务的结束需要用户明确地给出指示。结束事务的指令有如下两种。
COMMIT——提交处理:
COMMIT是提交事务包含的全部更新处理的结束指令（图 4-3），相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态了。因此，在提交之前一定要确认是否真的需要进行这些更新。
万一由于误操作提交了包含错误更新的事务，就只能回到重新建表、重新插入数据这样繁琐的老路上了。由于可能会造成数据无法恢复的后果，请大家一定要注意（特别是在执行 DELETE 语句的 COMMIT 时尤其要小心）。
虽然我们可以不清楚事务开始的时间点，但是在事务结束时一定要仔细进行确认。
ROLLBACK——取消处理:
ROLLBACK 是取消事务包含的全部更新处理的结束指令（图 4-4），相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始之前的状态（代码清单 4-22）。通常回滚并不会像提交那样造成大规模的数据损失。
-- postgresql
BEGIN TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
ROLLBACK;
上述事务处理执行之后，表中的数据不会发生任何改变。这是因为执行最后一行的 ROLLBACK 之后，所有的处理都被取消了。因此，回滚执行起来就无需像提交时那样小心翼翼了（即使是想要提交的情况，也只需要重新执行事务处理就可以了）。
ACID特性
DBMS 的事务都遵循四种特性，将这四种特性的首字母结合起来统称为 ACID 特性。这是所有 DBMS 都必须遵守的规则。
- 原子性（Atomicity），原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。该事务的结束状态，要么是两者都执行了（COMMIT），要么是两者都未执行（ROLLBACK）。从事务中途停止的角度去考虑，就能比较容易理解原子性的重要性了。由于用户在一个事务中定义了两条 UPDATE 语句， DBMS 肯定不会只执行其中一条，否则就会对业务处理造成影响。
- 一致性（Consistency），一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者NOT NULL约束等。例如，设置了NOT NULL 约束的列是不能更新为 NULL 的，试图插入违反主键约束的记录就会出错，无法执行。对事务来说，这些不合法的 SQL 会被回滚。也就是说，这些 SQL 处理会被取消，不会执行。一致性也称为完整性（图 4-5）。
- 隔离性（Isolation），隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。因此，即使某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新添加的记录的。
- 持久性（Durability），持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后， DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。如果不能保证持久性，即使是正常提交结束的事务，一旦发生了系统故障，也会导致数据丢失，一切都需要从头再来。保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生前的状态。
练习题
4.1 A 先生在自己的计算机（电脑）上，使用 CREATE TABLE 语句创建出了一张空的 Product（商品）表，并执行了如下的 SQL 语句向其中插入数据。
紧接着，B 先生使用其他的计算机连接上该数据库，执行了如下 SELECT语句。这时 B 先生能得到怎样的查询结果呢？
1 行也选取不出来。
解答
A 先生使用 BEGIN TRANSACTION 启动了事务处理，然后开始执行 INSERT语句。因此，在 A 先生使用 COMMIT 确定该更新之前， B 先生等其他用户都无法看到 A 先生进行更新的结果。这就是基于 ACID 特性中的 I，也就是独立性（Isolation）的现象。当然，由于 A 先生在 COMMIT 之前能看到自己进行过的更新，因此如果 A先生执行 SELECT * FROM Product; 的话，会得到 3 条记录。顺便提一下，如果想要确认该现象，并不需要两个人。只需使用电脑打开两个窗口连接同一个数据库，一个人就能完成两个人的工作了。
因为商品编号列违反了主键约束，所以会发生错误， 1 行也插入不了。
解答
如果该 INSERT 能够正常执行的话， Product（商品）表的状态应该会像下面这样变为 6 行数据。
但是，显然上述记录违反了商品编号列的主键约束（不能存在主键重复的记录）。违反该约束带来的后果就是无法执行更新操作，这就是 ACID 特性中的 C—— 一致性（Consistency）。
INSERT INTO ProductMargin (product_id, product_name, sale_price,
purchase_price, margin)
SELECT product_id, product_name, sale_price, purchase_price,
sale_price - purchase_price
FROM Product;
- 1,
-- 下调销售单价
UPDATE ProductMargin
SET sale_price = 3000
WHERE product_id = '0003';
- 2,
-- 重新计算利润
UPDATE ProductMargin
SET margin = sale_price - purchase_price
WHERE product_id = '0003';
