学习SQL【5】-数据更新
学习.png
数据的更新处理大体可以分为插入（INSERT）、删除（DELETE）和更新（UPDATE）三类，此外，还会给大家介绍数据库中用来管理数据更新的重要概念—事务。
一：数据的插入（INSERT语句）
1：要学习INSERT语句，我们得首先创建一个名为ProductIns的表。
创建表ProductIns的语句如下：
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));
如上所示，这里仅仅是创建了一个表，并没有插入数据。
INSERT语句的基本语法：
INSERT INTO <表名> （列名1, 列名2,...） VALUES (值1, 值2,...);
例，向表ProductIns中插入一行数据：
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0001', 'T衫', '衣服', 1000, 500, '2017-09-20');
将列名和值用逗号分开，分别括在()内，这种形式成为清单。上面的INSERT语句包含如下两个清单：
列清单：
(product_id, product_name, product_type, sale_price, purchase_price, regist_date)
值清单：
('0001', 'T衫', '衣服', 1000, 500, '2017-09-20')
原则上，执行一次INSERT 语句会插入一行数据。
当然很多RDBMS都支持一次插入多行数据，这样的功能称为多行INSERT。
例，多行INSERT(Oracle除外)：
INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2017-09-11'),
('0003', '运动T衫', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2017-09-20');
其次，多行INSERT的语法并不适用于所有的RDBMS，该语法适用于的DB2，SQL,SQL Server,PostgreSQL和MySQL，但不适用于Oracle.
2：列清单的省略
对表进行全列INSERT时，可以省略表名后的列清单。
INSERT INTO ProductIns VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2017-01-15');
3：插入NULL
INSERT语句中想要给某一列赋予NULL值时，可以直接在VALUES子句的值清单中写入NULL。
例，向purchase列插入中插入NULL：
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2017-09-20');
但是，想要插入NULL的列一定不能有NOT NULL约束。
4：插入默认值
我们还可以向表中插入默认值。可以通过在创建表的CREATE TABLE语句中设置DEFAULT约束来设置默认值。
我们可以通过“DEFAULT <默认值>”的形式来设定默认值。
例，创建表ProductIns的语句：
```SQL 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, -- 销售单价的默认值设定为0
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY(product_id));
默认值的使用方法通常有显示和隐式两种。
通过显示方式设定默认值，在VALUES子句中指定DEFAULT关键字：
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2017-04-28');
确认插入的数据行
SELECT * FROM ProductIns WHERE product_id = '0007';
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0007 | 擦菜板 | 厨房用具 | 0 | 790 | 2017-04-28
(1 行记录)
通过隐式方法设定默认值，在清单中省略相应的列和值：
INSERT INTO ProductIns (product_id, product_name, product_type, purchase_price, regist_date) VALUES ('0007', '擦菜板', '厨房用具', 790, '2017-09-28');
省略INSERT语句中的列名，就会自动设定为该列的默认值（没有默认值的会设定为NULL）。
5：从其他表中复制数据
要学习该方法，我们首先得创建一张表。
创建ProductCopy表的CREATE TABLE语句：
--用来插入数据的复制表
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));
下面我们就尝试一下将Product表中的数据插入到ProductCopy表中吧。
--将商品表中的数据复制到商品复制表中
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语句可以在关联的表之间传递数据。
下面我们尝试一下使用包含GROUP BY 子句的SELECT语句进行插入：
创建ProductType表的CREATE TABLE语句
--根据商品种类进行汇总的表
CREATE TABLE ProductType
( product_type VARCHAR(32) NOT NULL,
sun_sale_price INTEGER ,
sum_purchase_price INTEGER ,
PRIMARY KEY (product_type));
下面我们使用INSERT…SELECT语句，从Product表中选取数据插入到这张表中吧：
--插入其他表中数据合计值的INSERT...SELECT语句
INSERT INTO ProductType (product_type, sun_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM Product
GROUP BY product_type;
确认插入的数据：
SELECT * FROM ProductType;
执行结果
product_type | sun_sale_price | sum_purchase_price--------------+----------------+--------------------
衣服 | 5000 | 3300
办公用品 | 600 | 320
厨房用具 | 11180 | 8590
(3 行记录)
INSERT 语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何SQL语法（但使用ORDER BY子句不会产生任何效果）。
二：数据的删除（DELETE语句）
1：DROP TABLE语句和DELETE语句
删除数据的方法大体可以分为以下两种：
① DROP TABLE语句：可以将表完全删除
② DELETE语句：删除表中的全部数据，会留下表（容器）
2：DELETE语句的基本语法
保留数据表，仅删除全部数据行的DELETE语句：
DELETE FROM <表名>;
例如，清空Product表：
DELETE FROM Product;
DELETE语句删除的对象不是表或者列，而是记录（行）。
3：指定删除对象的DELETE语句
删除部分数据行的DELETE语句如下：
DELETE FROM <表名> WHERE <条件>;
例如，删除销售单价大于等于4000日元的数据：
DELETE FROM Product WHERE sale_price >= 4000;
确认删除后的结果:
SELECT * FROM Product;
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-09-11
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2017-09-20
0006 | 叉子 | 厨房用具 | 500 | | 2017-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2016-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2017-11-11
(6 行记录)
可以通过WHERE子句指定对象条件来删除部分数据。
与SELECT语句不同的是，DELETE语句中不能使用GROUP BY、HAVING和ORDER BY三类子句，而只能使用WHERE 子句。
三：数据的更新（UPDATE语句）
1：UPDATE语句的基本用法
改变表中数据的UPDATE语句：
UPDATE <表名> SET <列名> = <表达式>;
例如，将登记日期全部更新为“2017-10-10”：
UPDATE Product SET regist_date = '2017-10-10';
通过SELECT语句来确认一下更新内容：
--确认更新内容
SELECT * FROM Product;
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-10-10
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-10-10
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2017-10-10
0006 | 叉子 | 厨房用具 | 500 | | 2017-10-10
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2017-10-10
0008 | 圆珠笔 | 办公用品 | 100 | | 2017-10-10
(6 行记录)
2：指定条件的UPDATE语句
更新部分数据行的UPDATE语法如下：
UPDATE <表名> SET <列名> = <表达式> WHERE <条件>;
例如，将商品种类为厨房用具的记录的销售单价更新为原来的十倍：
UPDATE Product SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
确认更新内容:
SELECT * FROM Product ORDER BY product_id;
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-10-10
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-10-10
0004 | 菜刀 | 厨房用具 | 30000 | 2800 | 2017-10-10
0006 | 叉子 | 厨房用具 | 5000 | | 2017-10-10
0007 | 擦菜板 | 厨房用具 | 8800 | 790 | 2017-10-10
0008 | 圆珠笔 | 办公用品 | 100 | | 2017-10-10
(6 行记录)
3：使用NULL进行更新
使用UPDATE也可以将列更新为NULL（该更新俗称为NULL清空）。
例如，将商品编号为0008的数据（圆珠笔）的登记日期更新为NULL：
UPDATE Product SET regist_date = NULL
WHERE product_id = '0008';
确认更新内容:
SELECT * FROM Product ORDER BY product_id;
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-10-10
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-10-10
0004 | 菜刀 | 厨房用具 | 30000 | 2800 | 2017-10-10
0006 | 叉子 | 厨房用具 | 5000 | | 2017-10-10
0007 | 擦菜板 | 厨房用具 | 8800 | 790 | 2017-10-10
0008 | 圆珠笔 | 办公用品 | 100 | |
(6 行记录)
如上所示，圆珠笔的登记日期被更新为NULL。
使用UPDATE语句可以将值清空为NULL（但只限于未设置NOT NULL约束的列）。
4：多列更新
UPDATE语句的SET 子句支持同时将多个列作为更新对象。
使用逗号对列进行分隔：
UPDATE Product SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
确认更新内容:
SELECT * FROM Product ORDER BY product_id;
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-10-10
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-10-10
0004 | 菜刀 | 厨房用具 | 300000 | 1400 | 2017-10-10
0006 | 叉子 | 厨房用具 | 50000 | | 2017-10-10
0007 | 擦菜板 | 厨房用具 | 88000 | 395 | 2017-10-10
0008 | 圆珠笔 | 办公用品 | 100 | |
(6 行记录)
如上所示，厨房用具的销售单价更新为原来的10倍，进货单价更新为原来一半。
四：事务
1：什么是事务
在RDBMS中，事务是对表中数据进行更新的单位。
例如，更新商品信息的事务：
① 将叉子的销售单价降低1000元
PDATE Product SET sale_price = sale_price - 1000
WHERE product_name = '叉子';
② 将T衫的销售单价上浮1000元
UPDATE Product SET sale_price = sale_price + 1000
WHERE product_name = 'T衫';
上述两个操作一定要作为同一个处理单元执行。遇到这种需要在同一个处理单元中执行一系列更新操作的情况，一定要使用事务来进行处理。所以，事务是需要在同一个处理单元中执行一系列更新处理的集合。
2：创建事务
在RDBMS中创建事务的语法：
事务开始语句;
DML语句1;
DML语句2;
DML语句3;
...
事务结束语句（COMMIT或者ROLLBACK）;
使用事务开始语句和事务结束语句，将一系列的DML语句括起来，就实现了一个事务。
不同的DBMS的事务开始语句各不相同。
● SQL Server PostgreSQL
BEGIN TRANSACTION
● MySQL
START TRANSACTION
● Oracle、DB2
无
例如，更新商品信息的事务：
● SQL Server PostgreSQL
BEGIN TRANSACTION;
--将叉子的销售单价降低1000元
UPDATE Product SET sale_price = sale_price - 1000
WHERE product_name = '叉子';UPDATE 1
--将T衫的销售单价上浮1000元
UPDATE Product SET sale_price = sale_price + 1000
WHERE product_name = 'T衫';UPDATE 1
COMMIT;
● MySQL
START TRANSACTION;
--将叉子的销售单价降低1000元
UPDATE Product SET sale_price = sale_price - 1000
WHERE product_name = '叉子';UPDATE 1
--将T衫的销售单价上浮1000元
UPDATE Product SET sale_price = sale_price + 1000
WHERE product_name = 'T衫';UPDATE 1
COMMIT;
● Oracle、DB2
--将叉子的销售单价降低1000元
UPDATE Product SET sale_price = sale_price - 1000
WHERE product_name = '叉子';UPDATE 1
--将T衫的销售单价上浮1000元
UPDATE Product SET sale_price = sale_price + 1000
WHERE product_name = 'T衫';UPDATE 1
COMMIT;
● COMMIT—提交处理
COMMIT是提交事务包含的全部更新处理的结束指令，一旦提交，就无法恢复到事务开始前的状态了。所以在事务结束时一定要仔细确认。
● ROLLBACK—取消处理
ROLLBACK是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始前的状态。
事务回滚的例子（SQL Server PostgreSQL）：
BEGIN TRANSACTION;
--将叉子的销售单价降低1000元
UPDATE Product SET sale_price = sale_price - 1000
WHERE product_name = '叉子';UPDATE 1
--将T衫的销售单价上浮1000元
UPDATE Product SET sale_price = sale_price + 1000
WHERE product_name = 'T衫';UPDATE 1ROLLBACK;
3：ACID特性
DBMS的事务都遵循四种特性，将这四种特性的首字母结合起来统称为ACID特性。这是所有DBMS都必须遵守的规则。
● 原子性（Atomicity）
原子性是指在事务结束后，其中所包含的更新处理要么全部执行，要么完全不执行。
● 一致性（Consistency）
一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者NOT NULL约束等。
一致性也称为完整性。
● 隔离性（Isolation）
隔离性指的是保证不同事务之间互不干扰的特性。
● 持久性（Durability）
持久性也可以成为耐久性，指的是在事务结束后，DBMS能够保证该时间点的数据状态会被保存的特性。
保证持久性的方法根据实现的不同而不同，其中最常见的就是讲事务的执行记录保存到硬盘灯存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生前的状态。
每天学习一点点，每天进步一点点。
