4 SQL 数据更新
4 数据更新
4-1 数据的插入（INSERT语句的使用方法）
通过create table语句创建出来的表，可以将其比作一个空空如也的箱子。只有把数据装入到这个箱子后，它才能称为数据库。用来装入数据的SQL就是INSERT（插入）。
|
什么是INSERT |
|
CREATE TABLE shohinIns ( shohin_id CHAR(4) NOT null, shohin_mei VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32) NOT NULL, hanbai_tanka INTEGER DEFAULT 0, shiire_tanka INTEGER, totokubi DATE, PRIMARY KEY (shohin_id) ); |
|
INSERT语句的基本语法 |
|
-- INSERT INTO <表名> (列1，列2， 列3， 。。。) VALUES (值1，值2，值3，。。。); 包括：列清单和值清单（两者数量必须保持一致）
INSERT INTO shohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, totokubi) VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20'); |
列名和值用逗号隔开，分别括在（）内，这种形式称为清单。
|
法则4-1 |
|
原则上，执行一次INSERT语句会插入一行数据。（其实很多RDBMS都支持一次INSERT多行数据，这样的功能称为“多行INSERT（multi row INSERT）”，一般不建议使用）。 |
对表进行全列INSERT时，可以省略表名后的列清单。这时VALUES子句的值会默认按照从左到右的顺序赋给每一列。
|
列清单的省略 |
|
INSERT INTO shohinIns VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'); |
INSERT语句中想给某一列赋予NULL值时，可以直接在VALUES子句的值清单中写入NULL。
但是，想要插入NULL的列一定不能设置NOT NULL约束。向设置了NOT NULL约束的列中插入NULL时，INSERT语句会出错，数据插入失败。
插入失败指的是希望通过INSERT语句插入的数据无法正常插入到表中，但之前已经插入的数据并不会被破坏。
|
插入NULL |
|
INSERT INTO shohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, totokubi) VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'); |
通过显式方法插入默认值（初始值），默认值的设定，可以通过在创建表的CREATE TABLE语句中设置DEFAULT约束来实现。
在VALUES子句中指定DEFAULT关键字（显示方式插入默认值）；
通过隐式方式插入默认值，插入默认值时也可以不使用DEFUALUT关键字（隐式方式插入默认值，即值什么都不写）；
那么，在实际使用中哪种方法更好呢？笔者建议大家使用显示的方法。
说的省略列名，还有一定要说明以下。如果省略了没有设定默认值的列的话，该列的值就会被设定为NULL。因此，如果省略的是设置了NOT NULL约束的列的话，INSERT语句就会出错。
|
插入默认值 |
|
INSERT INTO shohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, totokubi) VALUES ('0007', '擦菜饭', '厨房用具', DEFAULT, 790, '2009-04-28');
SELECT * FROM shohinIns WHERE shohin_id = '0007';
INSERT INTO shohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, totokubi) VALUES ('0008', '圆珠笔', '办公用品', 100, '2009-11-11');
INSERT INTO shohinIns (shohin_id, shohin_bunrui, hanbai_tanka, shiire_tanka, totokubi) VALUES ('0009', '办公用品', 1000, 500, '2009-12-12'); |
|
法则4-2 |
|
省略INSERT语句中的列名，就会自动设定为该列的默认值（没有默认值时会设定为NULL）。 |
插入数据的方法，除了使用VALUSE子句指定具体的数据之外，还可以从其他表中复制数据。
|
从其他表中复制数据 |
|
-- 用来插入数据的商品复制表 CREATE TABLE shohinCopy ( shohin_id CHAR(4) NOT null, shohin_mei VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32) NOT NULL, hanbai_tanka INTEGER, shiire_tanka INTEGER, torokubi DATE, PRIMARY KEY (shohin_id) );
-- 将商品表中的数据复制到商品复制表中 INSERT INTO shohinCopy (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi FROM shohin;
-- 用来汇总商品种类的表 CREATE TABLE shohinBunrui ( shohin_bunrui VARCHAR(32) NOT NULL, sum_hanbai_tanka INTEGER, sum_shiire_tanka INTEGER, PRIMARY KEY (shohin_bunrui) );
INSERT INTO shohinBunrui (shohin_bunrui, sum_hanbai_tanka, sum_shiire_tanka) SELECT shohin_bunrui, SUM(hanbai_tanka), SUM(shiire_tanka) FROM shohin GROUP BY shohin_bunrui;
SELECT * FROM shohinBunrui; |
|
法则4-3 |
|
INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY 子句等任何SQL语法（但使用ORDER BY子句并不会产生任何效果） |
4-2 数据的删除（DELETE语句的使用方法）
删除数据的方法，大体可以分为以下两种 ：
第一， DROP TABLE语句可以将表完全删除，因此删除之后再想插入数据，就必须使用CREATE TABLE语句重新创建一张表。
第二， DELETE语句会留下表（容器），而删除表中的全部数据，因此只需要通过INSERT语句就可以再次向表中插入数据。
只能删除表中全部数据的TERUNCASE语句 TRUNCASE <表名>;
与delete不同的是，truncate只能删除表中的全部数据，而不能通过where子句指定条件来删除部分数据。也正是因为它不能具体地控制删除对象，所以其处理速度比delete要快的多。
|
DELETE语句的基本语法 |
|
-- DELETE FROM <表名>; DELETE FROM shohin; |
由于DELETE语句的对象是行而不是列，所以DELETE语句无法只删除部分列的数据。因此，在DELETE语句中指定列名是错误的。当然，使用星号的写法也是不对的（DELETE * FROM shohin;），同样会出错。
|
法则4-4 |
|
DELECT语句的删除对象并不是表或者列，而是记录（行）。 |
想要删除部分数据行时，可以像SELECT语句那样使用WHERE子句指定删除条件。这种指定了删除对象的DELETE语句称为搜索型DELETE.
|
指定删除对象的DELETE语句（搜索型DELECT） |
|
-- DELETE FROM <表名> WHERE <条件>；
DELETE FROM shohin WHERE hanbai_tanka >= 4000; |
与SELECT语句不同的是，DELETE语句中不能使用GROUP BY、HAVING和ORDER BY三类子句，而只能使用WHERE子句。原因很简单，GROUP BY和HAVING是从表中选取数据时用来改变抽取数据形式的，而ORDER BY是用来指定取得结果显示顺序的。因此，在删除表中数据时它们都起不到什么作用。
|
法则4-5 |
|
可以通过WHERE子句指定对象条件来删除部分数据。 |
4-3 数据的更新（UPDATE语句的使用方法）
|
UPDATE语句的基本语法 |
|
-- UPDATE <表名> SET <列名> = <表达式>;
UPDATE shohin SET torokubi = '2009-10-10';
SELECT * FROM shohin ORDER BY shohin_id; |
将更新对象的列和更新后的值都记述在SET子句中。
更新数据时也可以像DELETE语句那样使用WHERE子句。这种指定更新对象的UPDATE语句称为搜索型UPDATE语句。
|
指定条件的UPDATE语句（搜索型UPDATE） |
|
-- UPDATE <表名> SET <列名> = <表达式> WHERE <条件>;
UPDATE shohin SET hanbai_tanka = hanbai_tanka * 10 WHERE shohin_bunrui = '厨房用具';
SELECT * FROM shohin ORDER BY shohin_id; |
使用UPDATE也可以将列更新为NULL（该更新俗称为NULL清空）。
|
使用NULL进行更新 |
|
UPDATE shohin SET torokubi = NULL WHERE shohin_id = '0008';
SELECT * FROM shohin ORDER BY shohin_id; |
和INSERT语句一样，UPDATE语句也可以将NULL作为一个值来使用。
但是，只有未设置NOT NULL约束和主键约束的列才可以清空为NULL。如果将设置了上述约束的列更新为NULL，就会出错，这点与INSERT语句相同。
|
法则4-6 |
|
使用UPDATE语句可以将值清空为NULL（但只限于未设置NOT NULL约束的列）。 |
UPDATE语句的SET子句支持同时将多个列作为更新对象。
|
多列更新 |
|
-- 使用逗号将列分隔排列 UPDATE shohin SET hanbai_tanka = hanbai_tanka * 10 WHERE shohin_bunrui = '厨房用具';
-- 将列用（）括起来的列表形式 UPDATE shohin SET shiire_tanka = shiire_tanka / 2 WHERE shohin_bunrui = '厨房用具';
-- 建议使用这一种，使用逗号将列进行分隔排序的方法，这是通用的 UPDATE shohin SET hanbai_tanka = hanbai_tanka * 10, shiire_tanka = shiire_tanka / 2 WHERE shohin_bunrui = '厨房用具'; -- 部分dbms不支持下面这种写法 UPDATE shohin SET (hanbai_tanka, shiire_tanka) = (hanbai_tanka * 10, shiire_tanka / 2) WHERE shohin_bunrui = '厨房用具'; |
4-4 事务
在RDBMS中，事务代表了对表中数据进行更新的单位。简单来讲，事务就是需要在同一个处理单元中执行的一系列更新处理的集合。
|
4-7 |
|
事务是需要在同一个处理单元中执行的一系列更新处理的集合。 |
一个事务中包含多少个更新处理或者包含哪些处理，在DBMS中并没有固定的标准。
使用事务开始语句和事务结束语句，将一系列DML语句（INSERT/UPDATE/DELETE语句）括起来，就实现了一个事务处理。
|
创建事务 |
|
/* 事务开始语句; DML语句1; DML语句2; DML语句3; 事务结束语句(COMMIT或者ROLLBACK);
SQL server / PostgreSQL BEGIN TRANSACTION
mysql START TRANSACTION
oracle / DB2 无 */
-- SQL server postgresql BEGIN TRANSACTION; UPDATE shohin SET hanbai_tanka = hanbai_tanka - 1000 WHERE shohin_mei = '运动T恤'; UPDATE shohin SET hanbai_tanka = hanbai_tanka + 1000 WHERE shohin_mei = 'T恤衫'; COMMIT;
-- mysql START TRANSACTION; UPDATE shohin SET hanbai_tanka = hanbai_tanka - 1000 WHERE shohin_mei = '运动T恤'; UPDATE shohin SET hanbai_tanka = hanbai_tanka + 1000 WHERE shohin_mei = 'T恤衫'; COMMIT;
-- oracle db2 UPDATE shohin SET hanbai_tanka = hanbai_tanka - 1000 WHERE shohin_mei = '运动T恤'; UPDATE shohin SET hanbai_tanka = hanbai_tanka + 1000 WHERE shohin_mei = 'T恤衫'; COMMIT;
/* 各个dbms事务的开始语句都不尽相同。其中Oracle和DB2并没有定义特定的开始语句。 可能大家觉得这样设计很巧妙，其实是因为标准sql中规定了 一种悄悄开始事务处理的方法。因此，即使经验丰富的工程师也经常忽略事务处理开始的时点。 */
-- SQL server postgresql BEGIN TRANSACTION; UPDATE shohin SET hanbai_tanka = hanbai_tanka - 1000 WHERE shohin_mei = '运动T恤'; UPDATE shohin SET hanbai_tanka = hanbai_tanka + 1000 WHERE shohin_mei = 'T恤衫'; ROLLBACK; |
COMMIT是提交事务包含的全部更新处理的结束指令。相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态了。因此，在提交之前一定要确认是否真的需要进行这些更新。
万一由于误操作提交了包含错误更新的事务，就只能重新回到重新建表、重新插入数据这样繁琐的老路上了。由于可能会造成无法恢复的后果，请大家一定要注意（特别是在执行DELETE语句的COMMIT时尤其小心）。
ROLLBACK是取消事务包含的全部更新处理的结束命令。相当于文件处理中的放弃保存。一旦回滚，数据库就会回复到事务开始之前的状态。通常回滚并不会像提交那样造成大规模的数据损失。
|
法则4-8 |
|
虽然我们可以不清楚事务开始的时点，但是在事务结束时一定要仔细进行确认。 |
DBMS的事务都遵循四种标准规格的约定。将这四种特定的首字母结合起来统称为ACID特性。这些约定是所有DBMS都必须遵守的规则。
|
ACID特性 |
|
原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行的特性。也就是要么占有一切要么一无所有。从事务中途停止的角度去考虑，就能比较容易理解原子性的重要性了。由于用户在一个事务中定义了两条UPDATE语句，DBMS肯定不会只执行其中一条，否则就会对业务处理造成影响。
一致性指的是事务中包含的处理，要满足数据库提前设置的约束，如主键约束或者not null约束等。对事务来说，这些不合法的SQL会被回滚。也就是说这些SQL处理会被取消，不会执行。一致性也称完整性。
隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。因此，即使某个事务向表中添加了记录，在没有提交之前，其他事务是看不到新添加的记录的。
持久性也可以称为耐久性，指的是事务（不论是提交还是回滚）一旦结束，dbms会保证该时点的数据状态得以保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。如果不能保证持久性，即使是正常提交结束的事务，一旦发生了系统故障，就会导致数据丢失，一切都需要从头再来的后果。保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生的状态。 |
