代码示例4.10 删除销售单价(sale_price)大于等于4000日元的数据
DELETE FROM Product where sale_price >=4000;
注意:可以通过WHERE子句指定对象条件来删除部分数据。
删除和舍弃
标准SQL中用来从表中删除数据的只有DELETE语句。但是,很多数据库产品中还存在另外一种被称为TRUNCATE的语句。这些产品主要包括Oracle、SQL Server、PostgreSQL、MySQL和DB2。
TRUNCATE
是舍弃的意思,具体的使用方法如下所示。
语法4.A 只能删除表中全部数据的TRUNCATE语句
TRUNCATE<表名>;
与DELETE之间的区别是,TRUNCATE智能删除表中的全部数据,如果通过使用WHERE子句来指定条件进行删除部分数据。正是由于它不能具体的控制删除对象,所以他的处理速度比DELETE要快的多,因此要删除全部数据行时,使用TRUNCATE可以缩短执行时间。
但是,产品不同需要注意的地方也不尽相同。例如在Oracle中,把TRUNCATE定义为DDL,而不是DML。使用TRUNCATE时,请大家仔细阅读使用手册,多加注意。便利的工具往往还是会存在一些不足之处的。
数据的更新(UPDATE语句的使用方法)
使用UPDATE语句可以更改(更新)表中的数据。
UPDATE语句的基本语法
和INSERT语句、DELETE语句一样,UPDATE语句也属于DML语句。通过执行该语句,可以改变表中的数据。
语法4.4 改变表中的数据的UPDATE语句
UPDATE <表名> SET <列名> = <表达式>;
代码示例 4.11 将登记日期全部更新为"2022-12-17"
UPDATE Product SET regist_date ='2022-12-17';
查看更新结果
select * FROM Product;
执行结果
指定条件的UPDATE语句(搜索型UPDATE)
更新数据时也可以像DELETE语句那样使用WHERE语句子句,这种指定更新对象的UPDATE语句成为搜索型UPDATE语句。
语法4.5 更新部分数据行的搜索型UPDATE
UPDATE <表名> SET <列名> = <表达式> WHERE <条件>;
代码示例 4.12 将商品种类为厨房用具的记录的销售单价更新为原来的10倍
UPDATE Product SET sale_price = sale_price * 10 WHERE product_type = '厨房用具';
更新之后的结果
使用NULL进行更新
使用UPDATE也可以将列更新为NULL(俗称为NULL清空),只需要将赋值表达式右边的值直接写成NULL即可。
多列更新
UPDATE语句的SET子句支持同时将多个列作为更新对象。例如我们刚刚将销售单价(sale_price)更新为原来的10倍,如果想同时将进货单价(purchase_price)更新为原来的一半。
将看似需要两条UPDATE语句合并为一条UPDATE语句
方式①代码示例4.13
-- 使用逗号对列进行分隔排列 UPDATE Product SET sale_price = sale_price * 10, purchase_price = purchase_price / 2 WHERE product_type = '厨房用具';
事务
事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务,可以对数据库中的数据更新处理的提交和取消进行管理。
事务就是需要在用一个处理单元中执行一系列更新处理的集合。
创建事务
如果想在DBMS中创建事务,课按照如下语法结构编写SQL语句。
语法4.6 事务的语法
事务开始语句; DML语句①; DML语句②; DML语句③; ... 事务结束语句( COMMIT或者ROLLBACK);
使用事务开始语句和事务结束语句,将一系列DML语句(INSERT/UPDATE/DELETE语句)括起来,就实现一个事务处理。
代码示例4.15 更新商品信息的事物
SQL Server PostgreSQL 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; MySQL START 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; Oracle DB2 - - 将运动T恤的销售单价降低1000日元 UPDATE Product SET sale_price = sal e_price - 1000 WHERE product_name = ' 运动T恤';- - 将T恤衫的销售单价上浮1000日元 UPDATE Product SET sale_price = sal e_price + 1000 WHERE product_name = 'T恤衫'; COMMIT;
注意:各个DBMS事务的开始语句都不尽相同,其中Oracle和DB2并没有定义特定的开始语句。
COMMIT——提交处理
COMMIT是提交事物包含的全部更新处理的借宿指令,相当于文件处理中的覆盖保存。一旦提交,就无法恢复到事务开始前的状态了。
注意:虽然我们可以不清楚事务开始的时间点,但是在事务结束时一定要仔细进行确认。
代码示例4.16 事务回滚的例子(MYSQl)
--mysql START 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;
上述事务处理执行之后,表中的数据不会发生任何改变。这是因为执行最后一行的ROLLBACK
之后,所有的处理都被取消了.
事务何时开始
事务并没有标准的开始指令存在,而是根据DBMS不同而不同。
实际上,几乎所有的数据库产品的事务都无需事务开启的指令。这是因为绝大多数情况下,事务在数据库建立连接的时候已经悄悄开始了,并不需要用户再明确发出开始指令。例如:使用Oracle时,数据库连接建立之后,第一条SQL语句执行的同时,事务已经悄悄开始了。
像这样不使用指令而悄悄开始事务的情况下,如果区分各个事物呢?通常由两种情况。
每条SQL语句就是一个事务(自动提交模式)
直到用户执行COMMIT或者ROLLBACK为止算作一个事务。
自动提交模式
通常的DBMS都可以选择其中任意一种模式。默认使用自动提交模式的DBMS有SQL Server、PostgreSQL和MySQL等 DML语句如下所示,每一条语句都括在事务的开始语句和结束语句之中。
---mysql START TRANSACTION; -- 将运动T恤的销售单价降低1000日元 UPDATE Product SET sale_price = sale_price - 1000 WHERE product_name = '运动T恤'; COMMIT; START TRANSACTION; -- 将T恤衫的销售单价上浮1000日元 UPDATE Product SET sale_price = sale_price + 1000 WHERE product_name = 'T恤衫'; COMMIT;
ACID特性
DBMS的事务都遵循四种特性,将这四种特性的首字母结合起来统称为ACID特性。这是所有DBMS都必须遵守的规则。
原子性(Atomicity)
原子性是指在事务结束时,其中所包含的更新处理要么全部执行,要么完全不执行,也就是要么占有一切要么一无所有。
一致性(Consistency)
一致性指的是事务中包含的处理要满足数据库提前设置的约束,如主键约束或者NOT NULL约束等。例如,设置了NOT NULL约束的列是不能更新为NULL的,试图插入违反主键约束的记录就会出错,无法执行。对事务来说,这些不合法的SQL会被回滚。也就是说,这些SQL处理会被取消,不会执行。
隔离性(Isolation)
隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外,在某个事务中进行的更改,在该事务结束之前,对其他事务而言是不可见的。因此,即使某个事务向表中添加了记录,在没有提交之前,其他事务也是看不到新添加的记录的。
持久性(Durability)
持久性也可以称为耐久性,指的是在事务(不论是提交还是回滚)结束后,DBMS能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失,数据库也一定能通过某种手段进行恢复。