首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >学习SQL【5】-数据更新

学习SQL【5】-数据更新

作者头像
爱吃西瓜的番茄酱
发布2018-04-04 11:28:16
2.7K0
发布2018-04-04 11:28:16
举报

数据的更新处理大体可以分为插入(INSERT)、删除(DELETE)和更新(UPDATE)三类,此外,还会给大家介绍数据库中用来管理数据更新的重要概念—事务。

一:数据的插入(INSERT语句)

1:要学习INSERT语句,我们得首先创建一个名为ProductIns的表。 创建表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语句的基本语法:

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

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

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

将列名和值用逗号分开,分别括在()内,这种形式成为清单。上面的INSERT语句包含如下两个清单: 列清单:

 (product_id, product_name, product_type, sale_price, purchase_price, regist_date)

值清单:

 ('0001', 'T衫', '衣服', 1000, 500, '2017-09-20')

原则上,执行一次INSERT 语句会插入一行数据。

当然很多RDBMS都支持一次插入多行数据,这样的功能称为多行INSERT。 例,多行INSERT(Oracle除外):

INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2017-09-11'),
                               ('0003', '运动T衫', '衣服', 4000, 2800, NULL),
                               ('0004', '菜刀', '厨房用具', 3000, 2800, '2017-09-20');

其次,多行INSERT的语法并不适用于所有的RDBMS,该语法适用于的DB2,SQL,SQL Server,PostgreSQL和MySQL,但不适用于Oracle.

2:列清单的省略 对表进行全列INSERT时,可以省略表名后的列清单。

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

3:插入NULL INSERT语句中想要给某一列赋予NULL值时,可以直接在VALUES子句的值清单中写入NULL。 例,向purchase列插入中插入NULL:

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

但是,想要插入NULL的列一定不能有NOT NULL约束。

4:插入默认值 我们还可以向表中插入默认值。可以通过在创建表的CREATE TABLE语句中设置DEFAULT约束来设置默认值。 我们可以通过“DEFAULT <默认值>”的形式来设定默认值。 例,创建表ProductIns的语句:

```SQL 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));

默认值的使用方法通常有显示和隐式两种。

通过显示方式设定默认值,在VALUES子句中指定DEFAULT关键字:

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

确认插入的数据行

SELECT * FROM ProductIns WHERE product_id = '0007';

执行结果:

 product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
 0007       | 擦菜板       | 厨房用具     |          0 |            790 | 2017-04-28
(1 行记录)

通过隐式方法设定默认值,在清单中省略相应的列和值:

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

省略INSERT语句中的列名,就会自动设定为该列的默认值(没有默认值的会设定为NULL)。

5:从其他表中复制数据 要学习该方法,我们首先得创建一张表。 创建ProductCopy表的CREATE TABLE语句:

--用来插入数据的复制表
 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;

使用INSERT…SELECT语句可以在关联的表之间传递数据。 下面我们尝试一下使用包含GROUP BY 子句的SELECT语句进行插入: 创建ProductType表的CREATE TABLE语句

--根据商品种类进行汇总的表
 CREATE TABLE ProductType
 ( product_type  VARCHAR(32)  NOT NULL,
   sun_sale_price  INTEGER  ,
   sum_purchase_price  INTEGER  ,   PRIMARY KEY (product_type));

下面我们使用INSERT…SELECT语句,从Product表中选取数据插入到这张表中吧:

--插入其他表中数据合计值的INSERT...SELECT语句
 INSERT INTO ProductType (product_type, sun_sale_price,     sum_purchase_price) SELECT product_type, SUM(sale_price), SUM(purchase_price)   FROM Product  GROUP BY product_type;

确认插入的数据:

SELECT * FROM ProductType;

执行结果

 product_type | sun_sale_price | sum_purchase_price--------------+----------------+--------------------
 衣服         |           5000 |               3300
 办公用品     |            600 |                320
 厨房用具     |          11180 |               8590
(3 行记录)

INSERT 语句的SELECT语句中,可以使用WHERE子句或者GROUP BY子句等任何SQL语法(但使用ORDER BY子句不会产生任何效果)。

二:数据的删除(DELETE语句)

1:DROP TABLE语句和DELETE语句 删除数据的方法大体可以分为以下两种:

① DROP TABLE语句:可以将表完全删除
② DELETE语句:删除表中的全部数据,会留下表(容器)

2:DELETE语句的基本语法 保留数据表,仅删除全部数据行的DELETE语句:

DELETE FROM <表名>;

例如,清空Product表:

DELETE FROM  Product;

DELETE语句删除的对象不是表或者列,而是记录(行)。

3:指定删除对象的DELETE语句 删除部分数据行的DELETE语句如下:

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

例如,删除销售单价大于等于4000日元的数据:

DELETE FROM Product  WHERE sale_price >= 4000;

确认删除后的结果:

SELECT * FROM Product;

执行结果:

 product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
 0001       | T衫          | 衣服         |       1000 |            500 | 2017-09-20
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2017-09-11
 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2017-09-20
 0006       | 叉子         | 厨房用具     |        500 |                | 2017-09-20
 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2016-04-28
 0008       | 圆珠笔       | 办公用品     |        100 |                | 2017-11-11
(6 行记录)

可以通过WHERE子句指定对象条件来删除部分数据。 与SELECT语句不同的是,DELETE语句中不能使用GROUP BY、HAVING和ORDER BY三类子句,而只能使用WHERE 子句。

三:数据的更新(UPDATE语句)

1:UPDATE语句的基本用法 改变表中数据的UPDATE语句:

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

例如,将登记日期全部更新为“2017-10-10”:

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

通过SELECT语句来确认一下更新内容:

--确认更新内容
 SELECT * FROM Product;

执行结果:

 product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
 0001       | T衫          | 衣服         |       1000 |            500 | 2017-10-10
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2017-10-10
 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2017-10-10
 0006       | 叉子         | 厨房用具     |        500 |                | 2017-10-10
 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2017-10-10
 0008       | 圆珠笔       | 办公用品     |        100 |                | 2017-10-10
(6 行记录)

2:指定条件的UPDATE语句 更新部分数据行的UPDATE语法如下:

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

例如,将商品种类为厨房用具的记录的销售单价更新为原来的十倍:

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

确认更新内容:

SELECT * FROM Product ORDER BY product_id;

执行结果:

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
 0001       | T衫          | 衣服         |       1000 |            500 | 2017-10-10
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2017-10-10
 0004       | 菜刀         | 厨房用具     |      30000 |           2800 | 2017-10-10
 0006       | 叉子         | 厨房用具     |       5000 |                | 2017-10-10
 0007       | 擦菜板       | 厨房用具     |       8800 |            790 | 2017-10-10
 0008       | 圆珠笔       | 办公用品     |        100 |                | 2017-10-10
(6 行记录)

3:使用NULL进行更新 使用UPDATE也可以将列更新为NULL(该更新俗称为NULL清空)。 例如,将商品编号为0008的数据(圆珠笔)的登记日期更新为NULL:

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

确认更新内容:

SELECT * FROM Product ORDER BY product_id;

执行结果:

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
 0001       | T衫          | 衣服         |       1000 |            500 | 2017-10-10
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2017-10-10
 0004       | 菜刀         | 厨房用具     |      30000 |           2800 | 2017-10-10
 0006       | 叉子         | 厨房用具     |       5000 |                | 2017-10-10
 0007       | 擦菜板       | 厨房用具     |       8800 |            790 | 2017-10-10
 0008       | 圆珠笔       | 办公用品     |        100 |                |
(6 行记录)

如上所示,圆珠笔的登记日期被更新为NULL。 使用UPDATE语句可以将值清空为NULL(但只限于未设置NOT NULL约束的列)。

4:多列更新 UPDATE语句的SET 子句支持同时将多个列作为更新对象。 使用逗号对列进行分隔:

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

确认更新内容:

SELECT * FROM Product ORDER BY product_id;

执行结果:

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
 0001       | T衫          | 衣服         |       1000 |            500 | 2017-10-10
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2017-10-10
 0004       | 菜刀         | 厨房用具     |     300000 |           1400 | 2017-10-10
 0006       | 叉子         | 厨房用具     |      50000 |                | 2017-10-10
 0007       | 擦菜板       | 厨房用具     |      88000 |            395 | 2017-10-10
 0008       | 圆珠笔       | 办公用品     |        100 |                |
(6 行记录)

如上所示,厨房用具的销售单价更新为原来的10倍,进货单价更新为原来一半。

四:事务

1:什么是事务 在RDBMS中,事务是对表中数据进行更新的单位。 例如,更新商品信息的事务: ① 将叉子的销售单价降低1000元

PDATE Product   SET sale_price = sale_price - 1000
  WHERE product_name = '叉子';

② 将T衫的销售单价上浮1000元

UPDATE Product   SET sale_price = sale_price + 1000
  WHERE product_name = 'T衫';

上述两个操作一定要作为同一个处理单元执行。遇到这种需要在同一个处理单元中执行一系列更新操作的情况,一定要使用事务来进行处理。所以,事务是需要在同一个处理单元中执行一系列更新处理的集合。

2:创建事务 在RDBMS中创建事务的语法:

事务开始语句;

     DML语句1;
     DML语句2;
     DML语句3;
...
事务结束语句(COMMIT或者ROLLBACK);

使用事务开始语句和事务结束语句,将一系列的DML语句括起来,就实现了一个事务。

不同的DBMS的事务开始语句各不相同。 ● SQL Server PostgreSQL

BEGIN TRANSACTION

● MySQL

START TRANSACTION

● Oracle、DB2 无

例如,更新商品信息的事务: ● SQL Server PostgreSQL

BEGIN TRANSACTION;

 --将叉子的销售单价降低1000元
 UPDATE Product   SET sale_price = sale_price - 1000
  WHERE product_name = '叉子';UPDATE 1
 --将T衫的销售单价上浮1000元
 UPDATE Product   SET sale_price = sale_price + 1000
  WHERE product_name = 'T衫';UPDATE 1
 COMMIT;

● MySQL

START TRANSACTION;

 --将叉子的销售单价降低1000元
 UPDATE Product   SET sale_price = sale_price - 1000
  WHERE product_name = '叉子';UPDATE 1
 --将T衫的销售单价上浮1000元
 UPDATE Product   SET sale_price = sale_price + 1000
  WHERE product_name = 'T衫';UPDATE 1
 COMMIT;

● Oracle、DB2

 --将叉子的销售单价降低1000元
 UPDATE Product   SET sale_price = sale_price - 1000
  WHERE product_name = '叉子';UPDATE 1
 --将T衫的销售单价上浮1000元
 UPDATE Product   SET sale_price = sale_price + 1000
  WHERE product_name = 'T衫';UPDATE 1
 COMMIT;

● COMMIT—提交处理 COMMIT是提交事务包含的全部更新处理的结束指令,一旦提交,就无法恢复到事务开始前的状态了。所以在事务结束时一定要仔细确认。

● ROLLBACK—取消处理 ROLLBACK是取消事务包含的全部更新处理的结束指令,相当于文件处理中的放弃保存。一旦回滚,数据库就会恢复到事务开始前的状态。 事务回滚的例子(SQL Server PostgreSQL):

BEGIN TRANSACTION;

 --将叉子的销售单价降低1000元
 UPDATE Product   SET sale_price = sale_price - 1000
  WHERE product_name = '叉子';UPDATE 1
 --将T衫的销售单价上浮1000元
 UPDATE Product   SET sale_price = sale_price + 1000
  WHERE product_name = 'T衫';UPDATE 1ROLLBACK;

3:ACID特性 DBMS的事务都遵循四种特性,将这四种特性的首字母结合起来统称为ACID特性。这是所有DBMS都必须遵守的规则。 ● 原子性(Atomicity) 原子性是指在事务结束后,其中所包含的更新处理要么全部执行,要么完全不执行。

● 一致性(Consistency) 一致性指的是事务中包含的处理要满足数据库提前设置的约束,如主键约束或者NOT NULL约束等。 一致性也称为完整性。

● 隔离性(Isolation) 隔离性指的是保证不同事务之间互不干扰的特性。

● 持久性(Durability) 持久性也可以成为耐久性,指的是在事务结束后,DBMS能够保证该时间点的数据状态会被保存的特性。 保证持久性的方法根据实现的不同而不同,其中最常见的就是讲事务的执行记录保存到硬盘灯存储介质中(该执行记录称为日志)。当发生故障时,可以通过日志恢复到故障发生前的状态。

每天学习一点点,每天进步一点点。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2017-12-05,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 小白客 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档