SQL基础——数据更新_更新产品表的价格使用sql语句

前言

系统学习SQL的笔记，用于记录学习过程。

思维导图

数据的插入（INSERT语句的使用方法）

什么是INSERT

用创建表CREATE TABLE语句创建出来的表，可以任务是一个空空如也的箱子。只有把数据装入到这个箱子后，它才能够称为数据库。用来装入数据的SQL语句就是INSERT语句。

INSERT语句的基本语法

语法4.1 INSERT语句

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

先创建ProductIns表用于后序的语句练习

代码示例4.2 创建 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,
    purchase_price INTEGER,
    regist_date DATE,
PRIMARY KEY ( product_id ));
1
2
3
4
5
6
7
8

使用INSERT语句

代码示例4.3 向表中插入一行数据

INSERT INTO ProductIns ( product_id, product_name, product_type, sale_price, purchase_price ,regist_date )
VALUES
    ( '0001', '体恤衫', '衣服', 1000, 500, '2009-09-20' );
1
2
3

由于product_id列和product_name列是字符型，所以插入的数据需要像’0001’用单引号括起来，日期型的regist_date也需要用单引号括起来。
将列名和值用逗号隔开，分别括在()内，这种形式成为清单。在代码示例4.3中的INSERT包含如下两个清单。
列清单→(product_id, product_name, product_type, sale_price, purchase_price, regist_date)
值清单→(‘0001’, ‘T恤衫’, ‘衣服’, 1000, 500,‘2009-09-20’)

注意：表名后面的列清单和VALUES子句中的值清单的列数必须保持一致，列数不一致时会出错。

一般执行一次INSERT语句会插入一条数据。但是有很多RDBMS都支持一次插入多行数据。

代码示例4.4 INSERT插入多行数据

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

注意：该语法适用于DB2、SQL、SQL Server、PostgreSQL和MySQL，但不适用于Oracle。Oracle有特定的多行插入语法。

列清单的省略

对表进行全列INSERT时，可以省略表名后的列清单，这时VALUES子句的值会默认按照从左到右的顺序赋给每一列。

代码示例4.5 省略列清单

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

插入NULL

INSERT语句想要给某一列赋予NULL值时，可以直接在VALUES子句的值清单中写入NULL。

插入默认值

可以向表中插入默认值，首先需要在创建表的时候设置DEFAULT约束来设定默认值。
如在代码示例4.2 中的sale_price列默认值设置为0.如果在创建表的同时设置了默认值，就可以再INSERT语句中自动为列赋值了。
默认值的使用方法通常由显示和隐式两种。

通过显示方法插入默认值

在VALUES子句中制定DEFAULT关键字

代码示例4.5 通过显示方式设定默认值

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

这样一来，RDBMS就会在插入记录是自动把默认值赋值给对应的列。

通过隐式方法插入默认值

插入默认值时也可以不使用DEFAULT关键字，只要在列清单和VALUES中省略设定的默认值的列就可以了。

代码示例4.6 通过隐式方法设定默认值

INSERT INTO ProductIns ( product_id, product_name, product_type,  purchase_price, regist_date )
VALUES
    ( '0008', '擦菜板', '厨房用具', 790, '2009-04-28' );
1
2
3

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

从他表中复制数据

要插入数据，除了使用VALUES子句制定具体的数据之外，还可以从其他表中复制数据。

先创建一张表

代码示例4.7 创建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 DEFAULT 0,
    purchase_price INTEGER,
    regist_date DATE,
PRIMARY KEY ( product_id ));
1
2
3
4
5
6
7
8
9

ProductCopy（商品复制）表的结构与之前使用的ProductIns（商品）表完全一样，只是更改了一下表名而已。
接下来将ProductIns表中的数据插入到ProductCopy表中。

代码示例4.8 INSERT…SELECT语句

-- 将商品表中的数据复制到商品复制表中
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
    ProductIns;
1
2
3
4
5
6
7
8
9
10

这样就将ProductIns表中的所有数据插入到了ProductCopy表中。，INSERT … SELECT
语句可以在需要进行数据备份时使用

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

数据的删除（DELETE语句的使用方法）

DROP TABLE语句与DELETE语句

① DROP TABLE语句可以将表完全删除
② DELETE语句会留下表（容器），而删除表中的全部数据

DELETE语句的基本语法

语法4-2　保留数据表，仅删除全部数据行的DELETE语句

 DELETE FROM <表名>;
1

代码示例4.9 清空Product表

DELETE FROM Product;
1

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

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

想要删除部分数据行时，可以像SELECT语句那样使用WHERE子句制定删除条件。这种指定了删除对象的DELETE语句成为搜索型DELETE。

语法4.3 删除部分数据行的搜索型DELETE

DELETE 
FROM
    <表名> 
WHERE
    <条件>;
1
2
3
4
5

以Product表为例进行DELETE语句的操作。

代码示例4.10 删除销售单价(sale_price）大于等于4000日元的数据

DELETE FROM Product
where sale_price >=4000;
1
2

注意：可以通过WHERE子句指定对象条件来删除部分数据。

删除和舍弃

标准SQL中用来从表中删除数据的只有DELETE语句。但是，很多数据库产品中还存在另外一种被称为TRUNCATE的语句。这些产品主要包括Oracle、SQL Server、PostgreSQL、MySQL和DB2。

TRUNCATE
是舍弃的意思，具体的使用方法如下所示。

语法4.A 只能删除表中全部数据的TRUNCATE语句

TRUNCATE<表名>;
1

与DELETE之间的区别是，TRUNCATE智能删除表中的全部数据，如果通过使用WHERE子句来指定条件进行删除部分数据。正是由于它不能具体的控制删除对象，所以他的处理速度比DELETE要快的多，因此要删除全部数据行时，使用TRUNCATE可以缩短执行时间。

但是，产品不同需要注意的地方也不尽相同。例如在Oracle中，把TRUNCATE定义为DDL，而不是DML。使用TRUNCATE时，请大家仔细阅读使用手册，多加注意。便利的工具往往还是会存在一些不足之处的。

数据的更新（UPDATE语句的使用方法）

使用UPDATE语句可以更改（更新）表中的数据。

UPDATE语句的基本语法

和INSERT语句、DELETE语句一样，UPDATE语句也属于DML语句。通过执行该语句，可以改变表中的数据。

语法4.4 改变表中的数据的UPDATE语句

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

代码示例 4.11 将登记日期全部更新为"2022-12-17"

UPDATE Product
SET regist_date ='2022-12-17';
1
2

查看更新结果

select * FROM Product;
1

执行结果

指定条件的UPDATE语句（搜索型UPDATE）

更新数据时也可以像DELETE语句那样使用WHERE语句子句，这种指定更新对象的UPDATE语句成为搜索型UPDATE语句。

语法4.5 更新部分数据行的搜索型UPDATE

UPDATE <表名> 
SET <列名> = <表达式> 
WHERE
    <条件>;
1
2
3
4

代码示例 4.12 将商品种类为厨房用具的记录的销售单价更新为原来的10倍

UPDATE Product 
SET sale_price = sale_price * 10 
WHERE
    product_type = '厨房用具';
1
2
3
4

更新之后的结果

使用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 = '厨房用具';
    
1
2
3
4
5
6
7

方式②代码示例4.14

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

事务

事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理。

事务就是需要在用一个处理单元中执行一系列更新处理的集合。

创建事务

如果想在DBMS中创建事务，课按照如下语法结构编写SQL语句。

语法4.6 事务的语法

事务开始语句;
DML语句①;
DML语句②;
DML语句③;
...
事务结束语句（ COMMIT或者ROLLBACK）;
1
2
3
4
5
6

使用事务开始语句和事务结束语句，将一系列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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

注意：各个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;
1
2
3
4
5
6
7
8
9
10
11

上述事务处理执行之后，表中的数据不会发生任何改变。这是因为执行最后一行的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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

ACID特性

DBMS的事务都遵循四种特性，将这四种特性的首字母结合起来统称为ACID特性。这是所有DBMS都必须遵守的规则。

原子性（Atomicity）

原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。

一致性（Consistency）

一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者NOT NULL约束等。例如，设置了NOT NULL约束的列是不能更新为NULL的，试图插入违反主键约束的记录就会出错，无法执行。对事务来说，这些不合法的SQL会被回滚。也就是说，这些SQL处理会被取消，不会执行。

隔离性（Isolation）

隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。因此，即使某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新添加的记录的。

持久性（Durability）

持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后，DBMS能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。

如果博主的文章对您有所帮助，可以评论、点赞、收藏，支持一下博主!!!