MySQL学习笔记三：数据的插入、删除、更新与事务

最新推荐文章于 2022-06-29 10:17:57 发布

小粉桥反手王

最新推荐文章于 2022-06-29 10:17:57 发布

阅读量801

点赞数

分类专栏： SQL学习笔记

本文链接：https://blog.csdn.net/hzk427/article/details/105393228

版权

SQL学习笔记专栏收录该内容

6 篇文章 5 订阅

订阅专栏

插入默认值

重新创建一个表格productins，为sale_price列设置约束default 0，那么在插入数据时有两种方法可以插入默认值0：

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 VALUES ('0001', 'T恤' ,'衣服', default, 500, '2009-09-20');
-- 显式插入默认值
INSERT INTO productins(product_id,product_name,product_type,purchase_price,regist_date)
 VALUES ('0008', '圆珠笔', '办公用品', 100, '2009-11-11');
-- 隐式插入默认值

在这里插入图片描述

从其他表中复制数据

假设productins表格的数据要从之前的product表中插入，比如插入编号为0003的商品数据：

insert into productins select * from product where product_id='0003';

在这里插入图片描述
该insert语句中的select语句也可以使用where、group by、having等子句，但是order by不行。下面创建一个根据商品种类的进货与出售价格表producttype，其中的数据来源于product：

CREATE TABLE producttype
(product_type    VARCHAR(32)  NOT NULL,
 sum_sale_price      INTEGER ,
 sum_purchase_price  INTEGER ,
 PRIMARY KEY (product_type));
 
insert into producttype select product_type,sum(sale_price),
sum(purchase_price) from product group by product_type;

在这里插入图片描述
注意，不能从自己的表中复制内容然后往自己的表后面插入，因为这会违反主键约束。

数据的删除：delete from…

删除表格的所有数据（保留表头）用delete from或者truncate：

DELETE FROM producttype;
truncate producttype;-- 也可以删除表格内的所有数据

更新删除数据时，可能会遇到Error Code:1175的错误，原因就是未使用主键作为where条件，可以使用下列语句更改SAFE UPDATE MODE：

SET SQL_SAFE_UPDATES=0;

删除指定对象加where语句：

delete from product where sale_price>=4000;
select * from product;

在这里插入图片描述
“运动T恤”和“高压锅”被删除了。

数据的更新：update…set…

set子句中不仅可以是单纯的常数值，也可以是列的表达式。修改一列所有值：

update product set regist_date='2019-10-10';
-- regist_date一列都变成了'2019-10-10'

指定条件的update语句：

update product set sale_price = sale_price * 0.1 where product_type='厨房用具';
SELECT * FROM product;
-- 厨房用具的卖价被缩小了10倍

在这里插入图片描述
null清空（只有未设置not null约束和主键约束的列才可以）：

update product set regist_date = null where product_id='0008';
-- id为8的商品注册日期被改为null
SELECT * FROM product;

在这里插入图片描述
多列更新：

update product set sale_price=sale_price*10,purchase_price=
purchase_price*0.1 where product_type='厨房用具';
-- 厨房用具的卖价被扩大了10倍，买价被缩小了10倍
SELECT * FROM product;

在这里插入图片描述

事务

事务（transaction）就是需要在同一个处理单元中执行的一系列更新处理的集合。比如我需要删除一条数据并且更新一条数据，那么这两条处理就可以称为一个事务。mysql里的事务开始语句为start transaction，结束确认处理语句为commit（或者结束取消处理语句rollback），但是经常情况下可省略，是因为mysql默认每条语句就是一个事务，即自动提交模式，比如将运动T恤价格下调1000元这一事务已经默认包含在开始语句和结束语句之中：

START TRANSACTION;
-- 将运动 T 恤的销售单价降低1000元
UPDATE Product SET sale_price=sale_price-1000 WHERE product_name='运动T 恤';
COMMIT;-- 或者rollback取消处理

自动提交的情况需要特别注意的是DELETE语句。如果不是自动提交，即使使用 DELETE语句删除了数据表，也可以通过ROLLBACK命令取消该事务的处理，恢复表中的数据。但这仅限于明示开始事务，或者关闭自动提交的情况。如果不小心在自动提交模式下执行了DELETE操作，再怎么ROLLBACK也无济于事了。因此在进行数据的更新删除等操作时，最好先明示start transaction，确认无误后再commit。