SQL基础之——数据的插入、删除及更新 + 事务

已于 2023-12-26 21:03:57 修改
阅读量1.4k 收藏 23
点赞数 21
文章标签： 数据库 sql mysql
于 2023-12-26 21:00:23 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_59427813/article/details/135024524
版权

1. 数据的插入(INSERT)

使用INSERT语句可以向表中插入数据(行)，原则上，INSERT语句每次执行一行数据的插入。

1.1 准备工作

首先创建一个名为base_category0的表

-- 建表语句
DROP TABLE IF EXISTS `base_category0`;
CREATE TABLE `base_category0`  (
  `id` bigint(20) NOT NULL ,
  `name` varchar(10) NOT NULL ,
  PRIMARY KEY (`id`)
)

该语句只是建立新表，并未插入数据。

1.2 INSERT语句的基本语法

-- 基本语法
INSERT INTO <表名>  (列1,列2,列3,……) VALUES (值1,值2,值3,……);

– (列1,列2,列3,……) 被称为列清单； (值1,值2,值3,……)被称为值清单

向表中base_category0中插入一行数据：

INSERT INTO base_category0 ( id , name ) VALUES (1,"图书、音像、电子书刊")；

插入结果
图1 插入结果

插入的列名与值数量与数据类型都应该对应，否则会插入失败。
此外，原则上执行一次INSERT语句会插入一行数据，因此，插入多行时通常需要循环执行相应次数的INSERT语句

1.3 多行INSERT

原则上运行一次INSERT语句只能插入一条数据，所以插入多条数据时语句如下：

INSERT INTO base_category0 (id , name) VALUES (1,"图书、音像、电子书刊");
INSERT INTO base_category0 (id , name) VALUES (2,"手机");
INSERT INTO base_category0 (id , name) VALUES (3,"家用电器");
INSERT INTO base_category0 (id , name) VALUES (4,"数码");
INSERT INTO base_category0 (id , name) VALUES (5,"家居家装");

为了减少语句的书写数量，可以将代码写成如下形式：

INSERT INTO base_category0 (id , name) VALUES (1,"图书、音像、电子书刊"),(2,"手机"),(3,"家用电器"),(4,"数码"),(5,"家居家装");

但是要注意：

1.要注意语句书写内容和插入的顺序是否正确，如果出错很难找到错误所在
2.多行INSERT的语法并不适用于所有RDBMS(不适用于Oracle)

1.4 列清单的省略

对一张表进行全列INSERT(插入所有列)时，可以省略表名后的列清单。

INSERT INTO base_category0 (id , name) VALUES (1,"图书、音像、电子书刊"),(2,"手机"),(3,"家用电器"),(4,"数码"),(5,"家居家装");

该语句可以改写成

INSERT INTO base_category0 VALUES (1,"图书、音像、电子书刊"),(2,"手机"),(3,"家用电器"),(4,"数码"),(5,"家居家装");

1.5 插入NULL

当想使用INSERT给某一列赋NULL值时，可以直接在值清单中写入NULL，但需要注意该列一定不能设置NOT NULL约束，否则会报错，插入失败(仅仅是当前希望通过INSERT插入的数据无法正常插入到表中，但之前已经插入的数据不会被破坏)。

1.6 插入默认值

我们还可以向表中插入默认值(初始值)，可以通过在创建表的CREATE TABLE语句中设置DEFAULT约束来设定默认值。

DROP TABLE IF EXISTS `base_category0`;
CREATE TABLE `base_category0`  (
  `id` bigint(20) NOT NULL ,
  `name` varchar(10) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

如果在创建表的同时设定了默认值，就可以在INSERT语句中自动为列赋值了，默认值的使用方法通常有显式和隐式两种。

1.6.1 通过显示方法插入默认值

在VALUE子句中指定DEFAULT关键字。

INSERT INTO base_category0 (id , name) VALUES (1 , DEFAULT);

1.6.2 通过隐式方法插入默认值

在列清单与值清单中省略设定了默认值的列。

INSERT INTO base_category0 (id) VALUES (1);
-- 省略了列清单中的name与值清单中的对应值

通常建议使用显式方法赋默认值，因为这样可以一目了然地知道哪一列使用了默认值
如果省略了没有设定默认值的列，那么该列的值就会被设为NULL，如果该列被设置了NOT NULL约束，那么该INSERT语句就会出错。

1.7 从其他表复制数据

插入数据的方法除了使用VALUES子句指定具体数据之外，还可以从其他表中复制数据。
在base_category1中有如下数据：
base_category1表数据
图2 base_category1表数据

INSERT INTO base_category0 (id ,`name`)
SELECT *
FROM base_category1;

运行结果
图3 运行成功结果
插入结果
图4 插入内容展示

base_category1表中数据不会改变，所以该功能可以在需要进行数据备份时使用。

从其他表中复制数据使用的SELECT语句也可以使用WHERE条件以子句及GROUP BY子句等任何SQL语法，但使用ORDER BY子句不会产生任何效果。

2. 数据的删除

数据的删除方法大体可以分为以下三种

1.DROP TABLE语句可以将表完全删除
2.TRUNCATE TABLE语句可以将表中数据清空但保留表结构
3.DELETE语句可以删除表中数据

2.1 DROP TABLE语句

DROP TABLE语句是完全删除整张表，因此删除之后再想插入数据就必须使用CREATE TABLE语句重新创建一张表。

DROP TABLE base_category0;

2.2 TRUNCATE TABLE语句

TRUNCATE TABLE语句是将表中数据清空，仅仅删除表的所有记录，表的结构、索引、触发器、约束等将被保留，后续仍然可以使用该表。

TRUNCATE TABLE base_category0;

2.3 DELETE语句

2.3.1 基本语法：

-- 保留数据表，仅删除全部数据行
DELETE FROM base_category0;

如果语句中忘了写FROM，或者写了多余的列名都会出错。

DELETE删除的对象是行，并非列或者表，所以"DELETE * FROM <表名>;" 这种写法也是错误的。

2.3.2 指定删除对象的delete语句(搜索型DELETE)

想要删除部分数据行时，使用DELETE语句可以像SELECT一样，利用WHERE语句指定删除条件(搜索型DELETE)。

-- 基本语法
DELETE FROM <表名>
WHERE <条件> ;

删除order_info表中所有消费金额在5000以下的用户：

DELETE FROM order_info
WHERE final_total_amount < 5000 ;

与SELECT语句不同的是，DELETE语句不能使用GROUP BY、HAVING和ORDER BY三类子句，只能使用WHERE子句。原因很简单，GORUP BY和HAVING是用于从表中选取数据时用来改变抽取数据形式的，而ORDER BY是用来改变指定取得结果顺序的，因此这三类子句在删除表中数据时起不到什么作用。

注：TRUNCATE不能用于条件筛选删除，只能清空表中数据。

3. 数据的更新(UPDATE)

3.1 UPDATE语句的基本用法

使用INSERT语句向表中插入数据后，需要更改数据时，不需要将之前的数据删除后再重新插入，此时使用UPDATE语句就可以改变表中数据了。

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

-- 基本语法
UPDATE <表名>
SET <列名> = <表达式> ;

例：将activity_info表中的activity_type全部修改成’0518’

UPDATE activity_info
SET activity_type = '0518' ;

修改前
图5 修改前
修改后
图6 修改后

3.2 指定条件的UPDATE语句(搜索型UPDATE)

更新数据也可以像SELECT语句一样用WHERE子句指定更新对象，这种UPDATE语句被称为搜索型UPDATE。

-- 基本语法
UPDATE <表名>
SET <列名> = <表达式>
WHERE <条件> ;

例：将order_info表中总消费金额小于5000的用户的消费金额变为原来的2倍。

UPDATE order_info
SET final_total_amount = final_total_amount * 2
WHERE final_total_amount < 5000 ;

修改前
图7 修改前

修改后
图8 修改后

SET子句中赋值表达式的右边不仅可以是单纯的值，还可以是包含列的表达式

3.3 使用NULL进行更新

使用UPDATE也可以将列更新为NULL(俗称为NULL清空)

例：将order_info表中总消费金额在30000以上的用户消费金额设置为NULL

UPDATE order_info
SET final_total_amount = NULL
WHERE final_total_amount > 30000 ;

赋值结果
图9 赋空值后结果

只有未设置NOT NULL约束和主键约束的列才可以请空为NULL，否则就会出错。

3.4 多列更新

UPDATE的SET子句支持同时将多个列作为更新对象，如果想将order_info表中总消费金额小于5000的用户的消费金额变为原来的2倍的同时，将这些用户的地址设为空值，此时可以将需求拆分成两个语句执行：

UPDATE order_info
SET final_total_amount = final_total_amount * 2
WHERE final_total_amount < 5000 ;

UPDATE order_info
SET delivery_address = NULL
WHERE final_total_amount < 5000 ;

虽然这样也能正确更新数据，但执行两次语句增加了SQL语句的书写量，同时浪费了资源。其实可以将语句合并成一条语句处理，如：

-- 方法一：使用逗号对列进行分隔排列
UPDATE order_info
SET final_total_amount = final_total_amount * 2 ,
	delivery_address = NULL
WHERE final_total_amount < 5000 ;

-- 方法二：将列用()括起来写成清单形式
UPDATE order_info
SET (final_total_amount , delivery_address) = (final_total_amount * 2 , NULL)
WHERE final_total_amount < 5000 ;

实际应用中通常会使用方法一。

当然SET子句中的列还可以是三列或更多。

4.事务

在RDBMS中，事务是对表中数据进行更新的单位，简单来讲，事务就是需要在同一个处理单元中执行的一系列更新处理的集合。

4.1 事务的重要性

现在需要把华为Mate60 Pro的价格上调200元，还需要将运动鞋的价格提高50元，此时只需要使用UPDATE来更新即可，如果只处理了Mate60 Pro的价格而未处理运动鞋的价格，数据就会与想要的结果不一致。

此时就需要将两个物品的价格改动作为一个处理单元去执行，一定要用一个事务来处理。

4.2 创建事务

-- 事务的语法：
事务开始语句;

    DML语句1;
    DML语句2;
    DML语句3;

事务结束语句(COMMIT或者ROLLBACK)

使用事务开始语句和事物结束语句将一系列DML语句(INSERT/UPDATE/DELETE语句)括起来，就实现了一个事务处理。

此时需要特别注意的是事务的开始语句。实际上标准的SQL并没有定义事务的开始语句，而是由各个DBMS自己定义的。

MySQL：START TRANSACTION
SQL Server、PostgreSQL：BEGIN TRANSACTION

-- 以MySQL举例：把华为Mate60 Pro的价格上调200元，还需要将运动鞋的价格提高50元
BEGIN TRANSACTION;
	-- 把华为Mate60 Pro的价格上调200元
	UPDATE Product
	   SET sale_price = sale_price + 200
	 WHERE product_name = "华为Mate60 Pro" ;
	-- 将运动鞋的价格提高50元
	UPDATE Product
	   SET sale_price = sale_price + 50
	 WHERE product_name = "运动鞋" ;
COMMIT;

Oracle和DB2并没有定义特定的开始语句。

4.3 事物的区分方法

其实标准SQL中规定了一种悄悄开始事务处理的方法，上面提到各个DBMS都有各自的事务开始语句，但实际上，几乎所有数据库的事务都无需开始指令。这是因为大部分情况下，事务在数据库连接建立时就已经悄悄开始了，并不需要用户发出明确的事务开始指令。

通常区分各个事务有两种方式：

1.每条SQL语句都是一个事务。（自动提交模式）
2.用户发出commit或rollback指令才算一个事务。（非自动模式）

4.4 结束事务的指令

4.4.1 COMMIT——提交处理

COMMIT是提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存，一旦提交就无法恢复到事务开始前的状态了。因此在提交之前一定要确认好是否真的需要这些更新。

COMMIT流程是直线进行的：
事务开始语句 → 执行更新语句(DML) → 执行COMMIT

为了防止造成数据无法恢复的严重后果，在业务中一般建议进行数据备份之后再进行更新操作(尤其是在执行DELETE语句的COMMIT时)。

4.4.2 ROLLBACK——取消处理

ROLLBACK是取消事务包含的全部更新处理的结束指令。相当于文件处理中的放弃保存。一旦回滚，数据就会恢复到事务开始前的状态。通常情况下回滚不会像提交那样造成大规模的数据损失。

ROLLBACK流程是掉头回到起点
事务开始语句 → 执行更新语句(DML) → 执行ROLLBACK
结束后的状态和事务开始语句执行前相同。

4.5 ACID特性

DBMS的事务都遵循四种特性，四种特性的英文首字母组合起来即ACID特性。

4.5.1 原子性(Atomicity)

原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行。如之前的例子：把华为Mate60 Pro的价格上调200元，同时将运动鞋的价格提高50元，绝对不可能出现华为Mate60 Pro价格变动但运动鞋价格不变的情况。该事物的结束状态，要么是两者都执行了(COMMIT)，要么是两者都未执行(ROLLBACK)。

4.5.2 一致性(Consistency)

一致性又称完整性，指的是事务一旦执行要么回滚要么保存。

例如正常操作的事务满足数据库提前设置的约束，如主键约束与NOT NULL约束等，这些事务会成功执行并保存修改后的数据。
某些不符合这些约束的事务将执行失败并回滚到事务执行之前的状态。

4.5.3 隔离性(Isolation)

隔离性是为了保证事物之间互不干扰的特性。该特性保证了事务之间不会交叉执行，不会嵌套。

此外，在某个事务中进行的更改在该事务结束之前对其他事务而言是不可见的。

因此即便某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新加的记录的。

4.5.4 持久性(Durability)

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

如果不能保持持久性，即使是正常提交结束的事务，一旦发生了系统故障，也会导致数据丢失，一切都需要从头再来。

保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中(该执行记录被称为日志)。当发生故障时可以通过日志恢复到故障发生前的状态。

秘制小汉堡aa
关注
  • 21
    点赞
  • 踩
  • 23
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
c#及SQL server数据库——简单的图书管理系统
06-26
使用SQL语句进行数据的CRUD（创建、读取、更新、删除）操作，如INSERT插入新书，SELECT查询图书信息，UPDATE修改图书状态，DELETE删除已注销的图书。 在用户登录注册方面，系统需要验证用户名和密码的正确性。这...
iOS基础——数据操作之Sqlite3、FMDB
03-16
2. **执行SQL语句**：使用`sqlite3_exec()`函数执行SQL命令，如创建表、插入数据等。 3. **预编译语句**：对于多次执行的SQL语句，可以使用`sqlite3_prepare_v2()`预编译，提高效率。 4. **绑定参数**：预编译后的...
实验2 表数据的插入、修改和删除.pdf
03-22
。
SQL-删除数据
weixin_45789169的博客
12-03 2万+
SQL删除数据
SQL INSERT INTO 语句
最新发布
李长安的博客
04-24 768
13、如果调试一个程序让你很苦恼，千万不要放弃，成功永远在拐角之后，除非你走到拐角，否则你永远不知道你离他多远，所以，请记住，坚持不懈，直到成功。1、作为一个真正的程序员，首先应该尊重编程，热爱你所写下的程序，他是你的伙伴，而不是工具。2、程序员可以让步，却不可以退缩，可以羞涩，却不可以软弱，总之，程序员必须是勇敢的。5、没有情调，不懂浪漫，也许这是程序员的一面，但拥有朴实无华的爱是他们的另一面。3、编程是一种单调的生活，因此程序员比普通人需要更多的关怀，更多的友情。
sql 删除数据
3369
03-24 2423
sql 删除数据droptruncatedelete drop drop table Student_back 这样就删除表了，这样的删除不仅会删除表中的数据，还包括表结构、字段、视图、索引、触发器和依赖的约束等等。此方法慎用！！！ truncate truncate table Student_back 这样只是删除表中的所有数据，会保留表结构、字段、约束、索引等等，但是不能加 where 条...
Sql删除重复行
学无止境
07-18 7486
alter table 表 add nid int identity(1,1) delete 表where nid not in(select min(nid) from 表 group by 其他字段） alter table 表 drop column nid
sql delete语句
mengtianqq的博客
03-21 5158
delete语句用于删除数据库的表 delete语句结构：delete from 表名 where 字段1=值1 delete from person where  name="liuliu"//删除表中的名字为liuliu的行  
SQL 更新和删除数据
Fe_cow的博客
02-17 3786
SQL 更新和删除数据 一、更新数据： 更新(修改)表中的数据，可以使用UPDATE语句。 有两种使用UPDATE的方式： 1.更新表中的特定行。 2.更新表中的所有行。 注意：不要省略WHERE子句 再使用UPDATE时一定要细心。因为稍不注意，就会更新表中的所有行。 提示：UPDATE与安全 再客户端/服务器的DBMS中，使用UPDATE语句可能需要特殊的安全权限。再使用...
JSP源码——BBS论坛系统(jsp+sql).zip
10-06
【JSP源码——BBS论坛系统(jsp+sql)】是一个基于Java Server Pages（JSP）技术构建的在线讨论平台。在这个系统中，开发者利用JSP与SQL数据库的交互来实现用户注册、登录、发帖、回帖、浏览主题等功能，为用户提供了...
数据分析核心知识点总结 —— SQL.pdf
03-09
在数据分析中，SQL扮演着至关重要的角色，它能够帮助我们对数据进行增、删、改、查等操作，从而进行有效的数据处理和分析。以下是对SQL核心知识点的详细说明： **一、SQL增删改语句** 1. **新增数据** - **使用`...
SQL for Palm——移动设备编程项目.pdf
09-19
1. **SQL基础**：首先，读者会了解到SQL的基本语法，包括如何创建数据库和表，插入、更新和删除数据，以及查询数据。比如，`CREATE DATABASE`用于创建数据库，`CREATE TABLE`定义表结构，`INSERT INTO`插入记录，`...
Springboot 整合mybatis框架实现增删改查及事务处理——SpringBoot学习
写Bug的小白的博客
04-11 1569
一、POM 文件引入 JAR   在 pom.xml 中引入 Mybatis 以及测试包 <!-- mybatis 依赖 --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</art...
数据库入门(SQL SEVER)之SQL语句删除单行数据，所有行数据，表和数据库
热门推荐
qq_52241267的博客
03-30 3万+
1 删除单行数据 2 删除所有行数据 3 删除表和数据库 一:在该页面上直接单击右键选择删除 1.2选择 数据库->SSMSTest->表，右键单击直接删除整个表，连带着数据也删除了。不过要注意表有没有外键约束，有的话不能删除主表。欲删主表，先删子表。 二:通过SQL语句实现删除 2.1删除单行数据 SQL语句delete的语法如下： DELETE FROM 表名称 WHERE 列名称 = 值 例如： delete from student where sno = '00.
SQL删除表中的行DELETE 与 TRUNCATE 争锋
boyinglove的专栏
03-01 500
<br />      在没有WHERE子句时使用DELETE语句可以从表中删除所有的行，但Transact-SQLde TRUNCATE TABLE 语句执行此任务时会更快并更有效。TRUNCATE TABLE 语句的句法如下：<br />      TRUNCATE TABLE <table name><br />      如果使用以下DELETE语句：<br />      DELETE FROM employee<br />      DBMS 将一次一行地处理 EMPLOYEE 表，在想、从表中
【SQL】11 SQL DELETE 语句
Twinkle
07-26 788
DELETE 语句用于删除表中的记录。 SQL DELETE 语句 DELETE 语句用于删除表中的行。 SQL DELETE 语法 DELETE FROM table_name WHERE some_column=some_value;   请注意 SQL DELETE 语句中的 WHERE 子句！ WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHE...
SQL关于删除的三个语句：DROP、TRUNCATE、 DELETE 的区别
weixin_42713690的博客
07-21 526
DROP: DROP test; 删除表test，并释放空间，将test删除的一干二净。 TRUNCATE: TRUNCATE test; 删除表test里的内容，并释放空间，但不删除表的定义，表的结构还在。 DELETE: 1、删除指定数据 DELETE FROM test WHERE age=30 AND country='US'; 删除表test中年龄等于30的且国家为US...
sql更改某列数据_数据分析-第四关-1122
weixin_39645003的博客
12-09 471
第一部分：视图WHAT:表中存放的是实际数据，视图中存放的是 sql查询语句，使用视图时，会运行视图里的sql查询语句创建出一张临时表。HOW：如何创建视图：create view 视图名称（视图列名1，....）as WHY: 频繁使用庞大的查询语句；原表数据更新，视图也会同步更新；视图不需要保存数据，节省内存空间。注意事项：避免在视图的基础上再次创建视图，多重视图会降低sql的性能和效率；...
用一条insert语句来插入多行数据
allway2的博客
07-15 3962
MySQL\PostgreSQL: INSERT INTO contacts(first_name, last_name, email, phone) VALUES ('John','Doe','john.doe@example.com',NULL), ('Lily','Bush','lily.bush@example.com','(408-234-2764)'); ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值