运行环境: postgreSQL。

第四章:数据更新

创建 ProductIns 表

1
2
3
4
5
6
7
8
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));

数据的插入

负责插入数据的 SQL 是 INSERT

INSERT 基本语法

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

示例,向表中插入一行数据:

1
2
3
INSERT INTO ProductIns 
	(product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
	VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');

() 内的内容称为清单,列名称为列清单,VALUES 后称为值清单,其列书需保持一致。

大部分 RDBMS(除 Oracle),都支持多行 INSERT(multi row INSERT) 的写法:

1
2
3
4
5
INSERT INTO ProductIns 
	(product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
	VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
				 ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
				 ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

列清单的省略

进行全列 INSERT 时,可以省略列清单,即:

1
2
INSERT INTO ProductIns 
	VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

插入 NULL

当列约束条件没有指定 NOT NULL 约束时,可以向值清单对应列写入 NULL,如下指定 purchase_price 列插入 NULL:

1
2
3
INSERT INTO ProductIns 
	(product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
	VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');

插入默认值

只要在创建表时设定了列的默认值,既可在 INSERT 语句自动为列赋值。

  • 显式插入默认值:在值清单中指定 DEFAULT

    1
    2
    3
    
    INSERT INTO ProductIns 
    	(product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
    	VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
  • 隐式插入默认值:省略列清单中带有默认值的列,当省略不带默认值的列时,插入的值为 NULL。

    1
    2
    3
    4
    5
    
    INSERT INTO ProductIns 
    	(product_id, product_name, product_type, purchase_price, regist_date) 	VALUES ('0007', '擦菜板', '厨房用具', 790, '2009-04-28');
    
    INSERT INTO ProductIns 
    	(product_id, product_name, product_type, sale_price, regist_date) 			VALUES ('0008', '圆珠笔', '办公用品', 100, '2009-11-11');

从其它表中复制数据

创建 ProductCopy. 表

1
2
3
4
5
6
7
8
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 的表通过 INSERT … SELECT 复制到 ProductCopy 中,原表的数据不会发生变化,因此可以在需要进行数据备份时使用:

1
2
3
4
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;

以上的 SELECT 语句支持使用 WHERE 或者 GROUP BY 子句等,方便在关联表中存取数据,如下创建商品种类汇总表:

1
2
3
4
5
CREATE TABLE ProductType
	(product_type VARCHAR(32) 	NOT NULL,
   sum_sale_price INTEGER , 
   sum_purchase_price INTEGER ,
   PRIMARY KEY (product_type));

从 Product 表汇总数据到 ProductType 中:

1
2
3
4
5
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;

注意:以上语句使用 ORDER BY 子句并不会产生效果,即不能保证查询的顺序是插入的顺序。

数据的删除

负责插入数据的 SQL 是 DELETE

DELETE 基本语法

1
DELETE FROM <表名>;

示例,如下是删除 Product 表中全部数据行的 SQL :

1
DELETE FROM Product;

指定删除记录

删除部分数据记录的 搜索型 DELECT

1
DELETE FROM <表名> WHERE <条件>;

示例,删除销售单价(sale_price)大于等于 4000 的数据:

1
DELETE FROM Product WHERE sale_price >= 4000;

DELETE 只能使用 WHERE 语句,用来改变抽取数据形式的 GROUP BY , HAVING ,以及指定结果显示顺序的 ORDER BY,对于 DELETE 来说都是没有意义的。

TRUNCATE

大部分 RDBMS(Oracle, SQL, Server, PostgreSQL, MySQL, DB2)都实现了 TRUNCATE,意为舍弃,即只能删除表中全部数据,其相比 DELETE 能带来更快的处理速度。

1
TRUNCATE <表名>;

数据的更新

负责插入数据的 SQL 是 UPDATE

UPDATE 基本语法

1
UPDATE <表名> SET <列名> = <表达式>;

示例,将 regist_date 统一修改为 “2009-10-10”:

1
UPDATE Product SET regist_date = '2009-10-10';

指定条件的 UPDATE(搜索型 UPDATE)

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

示例,将商品种类( product_type )为厨房用具的记录的销售单价( sale_price )更新为原来的 10 倍:

1
2
UPDATE Product SET sale_price = sale_price * 10 
	WHERE product_type = '厨房用具';

使用 NULL 进行更新

将列更新为 NULL 的操作称为 NULL清空

1
UPDATE Product SET regist_date = NULL WHERE product_id = '0008';

多列更新

同时更新多列数据的语法有以下两种:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 所有 DBMS 支持
UPDATE Product 
 SET sale_price = sale_price * 10,
 		 purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';
 
-- PostgreSQL 和 DB2 支持
UPDATE Product
 SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)
 WHERE product_type = '厨房用具';

事务

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

创建事务

1
2
3
4
5
6
事务开始语句;
	DML 语句1;
	DML 语句2;
	DML 语句3;
	......
事务结束语句(COMMIT 或者 ROLLBACK;

标准 SQL 没有定义事务开始语句,以下为各个 DMBS 定义的事务开始语句语法:

1
2
3
4
5
6
7
8
--SQL Server, PostgreSQL
BEGIN TRANSACTION

--MySQL
START TRANSACTION

--Oracle, DB2

结束事务指令有如下两种:

  • COMMIT:提交事务包含的全部更新处理的结束指令。相当于文件处理的覆盖保存,一旦提交,即无法恢复事务开始前的状态。

image-20191122132909498

  • ROLLBACK:取消事务包含的全部更新处理的结束指令。相当于文件处理的放弃保存,一旦回滚,数据库就会恢复到事务开始之前的状态。

image-20191122133126208

事务处理何时开始通常分为两种情况:

  • A. 自动提交模式,即每条 SQL 就是一个事务,每一条语句都在开始语句和结束语句之中。
  • B. 直到用户执行 COMMIT 或者 ROLLBACK 为止算作一个事务。

默认自动提交的 DBMS 有 SQL Server, PostgreSQL 和 MySQL 等。默认 B 模式的有 Oracle 等。

ACID 特性

所有的 DBMS 都必须遵守 ACID 特性,以以下例子为例:

  • 原子性(Atomicity):指在事务结束时,其中所包含的更新处理要么全部执行(COMMIT),要么完全不执行(ROLLBACK)。
  • 一致性(Consistency)/完整性:指事务包含的处理要满足数据库提前设置的约束,如主键约束或者 NOT NULL 约束。事实上事务中的一致性侧重于 业务逻辑和规则,即 A 转账给 B 100 元,如果数据库最终没有达到 A 扣除 100元, B 增加 100 元的状态,即表示不符合一致性。

image-20191123140518764

  • 隔离性(Lsolation):保证不同事务之间互不干扰的特性,其保证了事务之间不会互相嵌套,即某个事务在结束之前,对其它事务都是不可见的。

  • 持久性(Durability)/耐久性:指事务(无论提交或者回滚)结束后,DBMS 能够保证该时间点的数据状态会被保存的特性,即使系统发生故障,数据库也能通过某种手段进行恢复。常见保证持久性的做法是将事务执行记录(日志)保存到硬盘等存储介质,当发生故障时,通过日志恢复到故障发生前的状态。