学习SQL【5】-数据更新

数据的更新处理大体可以分为插入(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能够保证该时间点的数据状态会被保存的特性。 保证持久性的方法根据实现的不同而不同,其中最常见的就是讲事务的执行记录保存到硬盘灯存储介质中(该执行记录称为日志)。当发生故障时,可以通过日志恢复到故障发生前的状态。

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

原文发布于微信公众号 - 小白客(youcoding)

原文发表时间:2017-12-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏吴生的专栏

MySQL的索引是什么?怎么优化?

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,...

39313
来自专栏撸码那些事

MySQL——索引基础

本篇文章,我们将从索引基础开始,介绍什么是索引以及索引的几种类型,然后学习如何创建索引以及索引设计的基本原则。

1003
来自专栏杨建荣的学习笔记

当主键碰到NULL(r6笔记第64天)

主键和Null看似没有多大的关系,因为一般的主键设置都是not null,但是把两者结合起来,会有很多意想不到的情况,说是意想不到是因为结果不在预期范围,但是如...

3527
来自专栏Pythonista

mysql索引提高查询速度

  在web开发中,业务模版,业务逻辑(包括缓存、连接池)和数据库这三个部分,数据库在其中负责执行SQL查询并返回查询结果,是影响网站速度最重要的性能瓶颈。本文...

1283
来自专栏张狗蛋的技术之路

MySQL探秘(七):InnoDB行锁算法

 在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

1711
来自专栏各种机器学习基础算法

如何编写高性能sql语句

一、什么是执行计划? 1)执行计划 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句...

3206
来自专栏微信公众号:Java团长

MySQL的索引是什么?怎么优化?

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,...

911
来自专栏撸码那些事

MySQL——索引基础

本篇文章,我们将从索引基础开始,介绍什么是索引以及索引的几种类型,然后学习如何创建索引以及索引设计的基本原则。

853
来自专栏跟着阿笨一起玩NET

SQL Server通过整理索引碎片和重建索引提高速度

本文章转载:http://database.51cto.com/art/201108/282408.htm

3461
来自专栏杨建荣的学习笔记

MySQL和Oracle中唯一性索引的差别(r12笔记第83天)

今天在修复MySQL数据的时候,发现一个看起来“奇怪”的问题。 有一个表里存在一个唯一性索引,这个索引包含3个列,这个唯一性索引的意义就是通过这3个列...

3546

扫码关注云+社区

领取腾讯云代金券