数据更新:练习题

本文内容

4.1 A 先生在自己的计算机(电脑)上,使用 CREATE TABLE 语句创建出了一张空的 Product(商品)表,并执行了如下的 SQL 语句向其中插入数据。

1
2
3
4
BEGIN TRANSACTION;
    INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2008-09-20');
    INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2008-09-11');
    INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);

紧接着,B 先生使用其他的计算机连接上该数据库,执行了如下 SELECT 语句。这时 B 先生能得到怎样的查询结果呢?

1
SELECT * FROM Product;

提示:如果可以使用DELETE语句,就可以对通过CREATE TABLE语句创建出的空表执行该操作了。

答:1 行也选取不出来。

» 解答

A 先生使用 BEGIN TRANSACTION 启动了事务处理,然后开始执行 INSERT 语句。因此,在 A 先生使用 COMMIT 确定该更新之前,B 先生等其他用户都无法看到 A 先生进行更新的结果。这就是基于 ACID 特性中的 I,也就是独立性(Isolation)的现象。当然,由于 A 先生在 COMMIT 之前能看到自己进行过的更新,因此如果 A 先生执行 SELECT * FROM Product; 的话,会得到 3 条记录。

顺便提一下,如果想要确认该现象,并不需要两个人。只需使用电脑打开两个窗口连接同一个数据库,一个人就能完成两个人的工作了。

4.2 如下所示,有一张包含 3 条记录的 Product 表。

商品编号 商品名称 商品种类 销售单价 进货单价 登记日期
0001 T 恤衫 衣服 1000 500 2009-09-20
0002 打孔器 办公用品 500 320 2009-09-11
0003 运动 T 恤 衣服 4000 2800

使用如下的 INSERT 语句复制这 3 行数据,应该就能够将表中的数据增加为 6 行。请说出该语句的执行结果。

1
INSERT INTO Product SELECT * FROM Product;

答:因为商品编号列违反了主键约束,所以会发生错误,1 行也插入不了。

» 解答

如果该 INSERT 能够正常执行的话,Product(商品)表的状态应该会像下面这样变为 6 行数据。

Product(商品)表

商品编号 商品名称 商品种类 销售单价 进货单价 登记日期
0001 T 恤衫 衣服 1000 500 2009-09-20
0002 打孔器 办公用品 500 320 2009-09-11
0003 运动 T 恤 衣服 4000 2800
0001 T 恤衫 衣服 1000 500 2009-09-20
0002 打孔器 办公用品 500 320 2009-09-11
0003 运动 T 恤 衣服 4000 2800

但是,显然上述记录违反了商品编号列的主键约束(不能存在主键重复的记录)。违反该约束带来的后果就是无法执行更新操作,这就是 ACID 特性中的 C—— 一致性(Consistency)。

4.3 以练习 4.2 中的 Product 表为基础,再创建另外一张包含利润列的新表 ProductMargin(商品利润)。

1
2
3
4
5
6
7
8
-- 商品利润表
CREATE TABLE ProductMargin
(product_id     CHAR(4)      NOT NULL,
 product_name   VARCHAR(100) NOT NULL,
 sale_price     INTEGER,
 purchase_price INTEGER,
 margin         INTEGER,
 PRIMARY KEY(product_id));

请写出向上述表中插入如下数据的 SQL 语句,其中的利润可以简单地通过对 Product 表中的数据进行计算(销售单价 - 进货单价)得出。

product_id product_name sale_price purchase_price margin
0001 T 恤衫 1000 500 500
0002 打孔器 500 320 180
0003 运动 T 恤 4000 2800 1200
1
2
3
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;

» 解答

Product(商品)表和 ProductMargin(商品利润)表中定义完全相同的列 product_id(商品编号)、product_name(商品名称)、sale_price(销售单价)、purchase_price(进货单价),可以通过 SELECT 语句直接从 Product(商品)表取出插入到 ProductMargin(商品利润)表中。只有 Product(商品)表中没有的 margin(利润)列的值需要根据 purchase_price 进货单价和 sale_price 销售单价进行计算。

4.4 对练习 4.3 中的 ProductMargin 表的数据进行如下更改。

  1. 将运动 T 恤的销售单价从 4000 日元下调至 3000 日元。

  2. 根据上述结果再次计算运动 T 恤的利润。

更改后的 ProductMargin 表如下所示。请写出能够实现该变更的 SQL 语句。

对练习 4.3 中的 ProductMargin 表的数据进行如下更改

  1. 将运动 T 恤的销售单价从 4000 日元下调至 3000 日元。

    1
    2
    3
    4
    
    -- 下调销售单价
    UPDATE ProductMargin
    SET sale_price = 3000
    WHERE product_id = '0003';
    
  2. 根据上述结果再次计算运动 T 恤的利润。

    1
    2
    3
    4
    
    -- 重新计算利润
    UPDATE ProductMargin
    SET margin = sale_price - purchase_price
    WHERE product_id = '0003';
    

请参阅#

(完)

comments powered by Disqus

本文内容