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会保证该时点的数据状态得以保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。如果不能保证持久性，即使是正常提交结束的事务，一旦发生了系统故障，就会导致数据丢失，一切都需要从头再来的后果。保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生的状态。