《SQL基础教程》学习笔记Ch4

4数据更新

4-1数据的插入（INSERT语句）

--创建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 INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
VALUES ('0001', 'T恤衫', '衣服', 100, 50, '2009-09-20');

当values后的数目与列数不相等时会报错
原则上，执行一次INSERT语句会插入一行数据

多行INSERT

--通常的INSERT
INSERT INTO ProductIns 
VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO ProductIns 
VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO ProductIns 
VALUES ('0004', '菜刀', '厨房用品', 3000, 2800, '2009-09-20');
--多行INSERT（Oracle除外）
INSERT INTO ProductIns VALUES
('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')，
('0003', '运动T恤', '衣服', 4000, 2800, NULL)，
('0004', '菜刀', '厨房用品', 3000, 2800, '2009-09-20');

列清单的忽略

-- 包含列清单
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO ProductIns VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

插入数据可以不用加上列名称

插入NULL

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
VALUES ('0006', '叉子', '厨房用具', 6800, 5000, NULL);

需要注意的是，插入NULL的列一定不能设置NOT NULL约束

插入默认值

--创建ProductIns表的CREATE TABLE语句
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));

• 通过显式方法插入默认值

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES ('0007', '擦菜板', DEFAULT, 790, '2009-04-28');

• 通过隐式方法插入默认值

INSERT INTO ProductIns (product_id, product_name, product_type, purchase_price, regist_price)
VALUES ('0007', '擦菜板', '厨房用具', 790, '2009-04-28');

省略sale_price列，值也省略

• 未设定默认值的情况

-- 省略purchase_price列（无约束）：会赋予"NULL"
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, regist_price)
VALUES ('0008', '圆珠笔', '办公用品', 100, '2009-11-11');

-- 省略product_name列（设置了NOT NULL约束）：错误！
INSERT INTO ProductIns (product_id, product_type, sale_price, purchase_price, regist_date)
VALUES('0009', '办公用品', 1000, 500, '2009-12-12');

省略INSERT语句的列名，就会自动设定为该列的默认值（没有默认值时会设定为NULL）

从其他表中复制数据

-- 用来插入数据的商品复制表
-- 创建ProductCopy表的语句
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;

--创建ProductType表的CREATE TABLE语句
CREATE TABLE ProductType
(product_type   VARCHAR(32)   NOT NULL,
 sum_sale_price   INTEGER  ,
 sum_purchase_price  INTEGER  , 
 PRIMARY KEY  (product_type));
--插入Product表中数据合计值的INSERT...SELECT语句
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;
--确认插入的数据行
SELECT * FROM ProductType;

INSERT语句中的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何SQL语法（但使用ORDRE BY子句并不会产生任何效果）

4-3数据的删除（DELETE语句）

DROP DELETE语句和DELETE语句

删除数据的两种方法：
1.DROP DELETE语句可以将表完全删除；
2.DELETE语句会留下表，而删除表中的全部数据。

DELETE语句的基本语法

--保留数据表，仅删除全部数据行的DELETE语句
--清空Product表
DELETE FROM Product;

DELETE语句的删除对象并不是表或者列，而是记录行。

指定删除对象的DELETE语句（搜索型DELETE）

--删除sale_price大于等于4000日元的数据
DELETE FROM Product
 WHERE sale_price >= 400;

DELETE语句中只能使用WHERE子句，不能使用GROUP BY,HAVING,OREDR BY三类子句

TRUNCATE语句

--删除表中全部数据
TRUNCATE <表名>;

TRUNCATE（舍弃）只能删除表中全部数据，而不能通过WHERE子句指定条件来删除数据

4-3数据的更新（UPDATE语句）

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

4-1.png

执行语句后，regist_date全部更新为2009-10-10

指定条件的UPDATE语句

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

使用NULL进行更新

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

多列更新

假设我们需要将sale_price更新为原来的10倍，同时将purchase_price更新为原来的一半

-能够正确执行但略显麻烦的UPDATE语句
UPDATE Product
   SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';

UPDATE Product
   SET purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';

上面的语句可以进行合并：

--方式1 使用逗号对列进行分隔排列
UPDATE Product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';

--方式2 将列用()括起来的形式
UPDATE Product
   SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2 )
 WHERE product_type = '厨房用具';

方式1可以在所有DBMS中使用，方式2只能在PostgreSQL和DB2中使用

4-4事务

事务：需要在同一个处理单元中执行的一系列更新处理的集合

创建事务

例如，我们需要两条更新处理：将运动T恤的销售单价降低1000日元；将T恤衫的销售单价上浮1000日元

BEGIN TRANSACTION;
  --将运动T恤的销售单价降低1000日元
  UPDATE Product
     SET sale_price = sale_price - 1000;
   WHERE product_name = '运动T恤';

  --将T恤衫的销售单价上浮1000日元
  UPDATE Product
     SET sale_price = sale_price +1000;
   WHERE product_name = 'T恤衫';
COMMIT;

COMMIT是提交事务包含的全部更新处理的结束指令，一旦提交，就无法回到事务开始之前的状态
ROLLBACK是取消事务包含的全部更新处理的结束指令，一旦回滚，数据库就会恢复到事务开始之前的状态

BEGIN TRANSACTION;
  --将运动T恤的销售单价降低1000日元
  UPDATE Product
     SET sale_price = sale_price - 1000;
   WHERE product_name = '运动T恤';

  --将T恤衫的销售单价上浮1000日元
  UPDATE Product
     SET sale_price = sale_price +1000;
   WHERE product_name = 'T恤衫';
ROLLBACK;

ACID特性

DBMS的事务都遵守四种特性：

原子性：在事务结束时，其中包含的更新处理要么全部执行，要么完全不执行
一致性：事务中包含的处理要满足数据库提前设置的约束
隔离性：保证不同事务之间互不干扰的特性
持久性：在事务结束后，DBMS能够保证该时间点的数据状态会被保存的特性