从0到1学数据库：数据操作与事务控制

点击上方“罗晓胜”，马上关注，您的支持对我帮助很大

上期文章

 

 

• 从0到1学数据库：where条件查询和Order By数据排序

• 从0到1学数据库：Function函数

 

/   前言   /

 

查询我们已经很熟悉，接下我们试试CRUD其他的，增加几条数据，修改一些数据，和删除一些数据，还有事务进行控制。

 

/   正文   /

 

要点

• INSERT语句
• UPDATE语句
• DELETE语句
• COMMIT命令
• ROLLBACK命令
• 管理锁

数据操作与事务控制

• 数据操作语言（DML：Data Manipulation Language） • 主要包括以下语句：– INSERT – UPDATE – DELETE – MERGE

• 事务是一组相关的DML语句的逻辑组合。事务控制主要包括下 列命令：– COMMIT – ROLLBACK – SAVEPOINT

插入数据INSERT语法结构

• 语法如下：

INSERT INTO 表名[(列名1[,列名2，…，列名n])] VALUES (值1[,值2，…，值n]);

– 一次只插入一行 – NULL的使用，连续的单引号（''）也可以表示空值。– 插入日期型数据 – 插入特殊字符 – 插入多行数据 – 按列的默认顺序列出各个列的值 – 在Insert子句中可以随意列出列名和他们的值 – 字符和日期型数据应该包含在单引号中

INSERT语句插入空值（NULL）

• 例 新增一条用户数据

INSERT INTO student VALUES(null,'阿拉善',23,3,88,'2020-01-01');

• 这里ID是自增，所以我们用null代替(有设置默认值的也是用null代替，系统会自动用默认值初始化)

– 符合SQL：1999标准 – 可以使用显示默认值控制默认值的使用 – 显示默认值可以在insert和update语句中使用

• 例 新增用户数据（班级未知or分数未知）

INSERT INTO student VALUES(null,'阿拉善',23,null,88,'2020-01-01');
INSERT INTO student VALUES(null,'阿拉善',23,3,null,'2020-01-01');

INSERT语句插入日期型数据

• 例 可选择年月日或者完整年月日时分秒插入数据，（时间通用格式 yyyy-MM-dd hh:mm:ss)

INSERT INTO student VALUES(null,'阿拉善',23,3,88,'2020-01-01');
INSERT INTO student VALUES(null,'阿拉善',23,3,88,'2020-01-01 01:01:01');
INSERT INTO student VALUES(null,'阿拉善',23,3,88,str_to_date('08/09/2008', '%m/%d/%Y'));
INSERT INTO student VALUES(null,'阿拉善',23,3,88,str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'));

INSERT语句插入特殊字符

使用“\”符对特殊符号转义 ""替换为"\" "'"替换为"''"

以下SQL插入数据会报错
INSERT INTO student VALUES(null,''阿拉善'',23,3,88,'2020-01-01');
INSERT INTO student VALUES(null,'\阿拉善\',23,3,88,'2020-01-01');

转义字符
INSERT INTO student VALUES(null,'\'阿拉善\'',23,3,88,'2020-01-01');
INSERT INTO student VALUES(null,'''阿拉善''',23,3,88,'2020-01-01');
INSERT INTO student VALUES(null,'\\阿拉善\\',23,3,88,'2020-01-01');

INSERT语句插入多行数据

• 语法

INSERT INTO表名[(列名1[,列名2，…，列名n])] 子查询 ;

• 例 将创建时间大于“2000-01-01”用户信息复制到 student表中。

INSERT INTO student SELECT * FROM student WHERE c_create_date<('2000-01-01');

不必书写values子句 INSERT子句中列的数量和类型必须和子查询中列的数量 和类型相匹配

修改数据UPDATE语法结构

• UPDATE语法结构

UPDATE 表名 SET 列名=表达式[,列名=表达式, [WHERE 条件表达式];]

• UPDATE简单修改

UPDATE student SET c_score=c_score+1 where id = 1;

• 嵌入子查询修改

UPDATE student SET c_score=c_score+(SELECT AVG(c_score) FROM student) WHERE where id = 1;

根据查询结果批量更新

将三班和一班同名的用户批量更新分数和年龄

UPDATE student t1,(select * from student where c_class = 3) t2
SET t1.c_score = t2.c_score,t1.age = t2.age 
where t1.name = t2.name and (t1.c_class = 1)

注：还可以对t2添加分组控制查询条数（保证t2在某一纬度只有一条数据）

修改数据(完整性错误)

• update student set id = 55 where id = 1;

• ORA-02291: 违反完整约束条件 (NEU.EMP_DEPT_FK) - 未找 到父项关键字

删除数据DELETE语法结构

• DELETE语法结构 DELETE [FROM] 表名 [WHERE 条件表达式]；

• DELETE删除数据 DELETE FROM student WHERE id=10;

• 例 删除所有名为"三班"的用户 DELETE FROM student WHERE c_class IN (SELECT id FROM t_class WHERE name ='三班') ;

• DELETE语句不能删除被其他表引用了的记录值，也即外键。

Merge存储引擎

现在有这么一个问题，怎样样统计所有的分表（假设按天分表）数据,所有的分表数据结构、类型相同。我们肯定能想到以下两种方案：第一种是最笨的方法，就是循环查询所有表数据（肯定不能采用）；第二种方法是，利用中间件，redis或者mycat，然后定时轮询，这样开销也很大。

mysql有很多引擎，如engine=innodb和engine=myisam，其实还有Merge存储引擎，可直接对多张表合并

假设有user_1、user_2、user_3三张表
CREATE TABLE `user_1` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`user_name` varchar(255) NULL ,
`user_sex` varchar(255) NULL ,
`user_birth_date` datetime NULL ,
PRIMARY KEY (`id`)
)ENGINE = MYISAM DEFAULT CHARSET=utf8mb4;
。。。
user_2、user_3

创建user_merge表
CREATE TABLE `user_merge` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`user_name` varchar(255) NULL ,
`user_sex` varchar(255) NULL ,
`user_birth_date` datetime NULL ,
PRIMARY KEY (`id`)
)ENGINE = MERGE UNION = (user_1,user_2,user_3);

然后select 查询user_merge即可
select * from user_merge

事务概率

• 概念：事务（Transaction）也称工作单元，是一个或多个SQL 语句所组成的序列，这些SQL操作作为一个完整的工作单元， 要么全部执行，要么全部不执行。通过事务的使用，能够使一 系列相关操作关联起来，防止出现数据不一致现象。

• 事务的组成：在ORACLE数据库中，事务由以下语句组成：

– 一组相关的DML语句，修改的数据在该组语句中保持一致

– 一个 DDL语句或者一个 DCL语句或者一个TCL语句

事务特征

• 可用四个字母的缩写表示：即ACID

• 原子性（Atomicity） – 事务就像一个独立的工作单元。原子性保证要么所有的操 作都成功，要不全都失败。如果所有的动作都成功了，我 们就说这个事务成功了，不然就是失败的，然后回滚。

• 一致性（Consistency） – 一旦事务完成了（不管是成功的，还是失败的），整个系 统处于操作规则的统一状态，也就是说，数据不会损坏。

• 隔离性（Isolation） – 事务的隔离性是指数据库中一个事务的执行不能被其它事 务干扰。所以，事务应该隔离起来，目的为了防止同时的 读和写操作。这就需要事务与锁同时使用。

• 持久性（Durability） – 事务的持久性也称为永久性（Permanence），指事务一旦 提交，则其对数据库中数据的改变就是永久的。

事务控制

• 事务的控制分为：显式控制及隐式控制。

• 显式控制：– 显式提交：Commit – 显式回滚：Rollback

• 隐式控制：– 隐式提交：当下列任意一种情况发生时，会发生隐式提交 a. 执行一个DDL 语句 b. 执行一个DCL 语句

– 隐式回滚：当下列任意一种情况发生时，会发生隐式回滚 a. 客户端连接到服务器端异常中断 b. 系统崩溃

事务的主要命令

• 事务控制的命令主要有以下三个：

– 事务提交：COMMIT – 事务回滚：ROLLBACK – 设立保存点：SAVEPOINT（作为辅助命令使用）

• 设置保存点语法：– SAVEPOINT 保存点名称；——定义保存点 – ROLLBACK TO保存点名称; ——回滚到已定义保存点

事务的开始和结束

• 开始：事务开始于上一个事务结束后执行的第一个DML语句 • 结束：事务结束于下面的任一种情况的发生：

– 执行了COMMIT 或者ROLLBACK命令 – 隐式提交（单个的DDL或DCL语句）或自动提交 – 用户退出 – 系统崩溃

事务自动提交

背景知识：mysql事务支持的引擎是InnoDB 默认情况下autocommit的值为1

当autocommit为开启状态时，即使没有手动start transaction开启事务，mysql默认也会将用户的操作当做事务即时提交

• 设置格式：

SET AUTOCOMMIT [ON|OFF];

事务控制示例

DELETE FROM test ; ROLLBACK; ——撤消DELETE操作 INSERT INTO test VALUES(’A’); SAVEPOINT insert_a; ——定义insert_a保存点 INSERT INTO test VALUES(’B’); SAVEPOINT insert_b; ——定义insert_b保存点 INSERT INTO test VALUES(’C’); ROLLBACK TO insert_b; ——撤消操作到insert_b保存点 DELETE FROM test WHERE test_str = ‘A’; COMMIT; ——将所有修改写入数据库 ROLLBACK; ——所有操作已经COMMIT提交，不能 回滚

事务执行Commit或Rollback前的数据状态

– 数据变化前的状态可以被恢复 – 当前会话可以使用SELECT语句来验证 DML操作后的结果 – 其它会话不能查看由当前用户的DML操作结果 – 受影响记录被锁定，也就是其它用户不能改变受影响记录 中的数据

• Commit后的状态 – 在数据库中数据变化成为永久性的 – 先前的数据状态永久性的消失 – 所有用户/会话都可以查询提交后的结果 – 锁定的记录被释放，可以有效地被其他用户操作 – 所有的存储节点被清除

• Rollback后的数据状态 – 使用ROLLBACK 可以放弃所有悬而未决的变化。¯数据变化是可以撤销的 ¯先前的数据状态被恢复 ¯锁定的记录被释放 ¯所有的存储节点被清除

事务的读一致性

• 读一致性保证了不同会话在同一时间查看数据时，数据一致。

• 在两个不同级别上提供读一致性：语句级读一致性和 事务级一致性。

• 事务级一致性，当一个会话正在修改数据时，其它的会话将看 不到该会话未提交的修改。

• 语句级读一致性，保证单个查询所返回的数据与该查询开始时 刻相一致。所以一个查询从不会看到在查询执行过程中提交的 其它事务所作的任何修改。

锁

锁-并发事务

• 并发事务

– 并发事务：如果多个事务同时访问某一个资源，比如同时 修改表中的某一行。

并发事务常见问题

– 并发事务可能出现如下问题:

• 1.脏读取:就是读取了未提交的数据，如B事务读取了A 事务未提交的数据 • 2.不可重复读取:就是一个事务在读取一个数据时，数据 不同，可能是另一个事务修改了数据。• 3.更新丢失:就是两个事务同时更新了一条数据，先更新 的就会丢失数据。• 4.幻想读:事务A在读取数据的时候，数据发生了变化。

锁的概念

• 锁用来在多用户并发访问和操作数据库时保证数据的一致性。锁由系统自动管理。如一个DML操作，默认的机制 是在DML操作涉及到的行上加锁（行级别），但不会在更高的 级别（表级别）上加更严格的锁，比如只改某行的数据不会锁 住整个表。这提供了很好的并发性，因为整个表没有锁定，只 是某些行被锁定了，其他用户可以修改其他行数据。• 查询不需要任何锁。• 锁的生命周期，锁在被相关的操作申请并持有后，会一直保持 到事务的结束。事务结束后，锁才会被释放。• 锁的内部维护机制是采用排队机制（enqueue），一个对象的 排它锁被持有后，该对象相同级别的锁被其他事务申请时候， 所有等待该锁的事务都在一个等待队列中排队，其他事务处于 等待状态。直到该锁被释放，等待的事务才重新竞争使用该资 源。

• 锁的模式 – 排它锁模式（Exclusive）排它锁在被释放之前，会阻止其 锁住的资源被其他任何事务共享。– 共享模式（Share）

锁的分类

• 两种类型的锁，DML锁和DDL锁。• DML锁，也称数据锁，用于在数据被多个不同的用户改变时， 保证数据的完整性。• DDL锁，也称为数据字典锁，执行DDL语句时，DDL语句涉及 到的对象获得DDL锁。由于被持有的时间很短，因此很少看到 冲突的DDL锁，并且以nowait方式被请求。

3种DDL锁类型：

– 排它的DDL锁，很多对象的创建、修改和删除定义时候都 需要获得该锁。比如执行Create Table、Drop Table等时会 获得表上的排它DDL锁。– 共享的DDL锁。在执行Grant、Create Procedure等命令时， 会获得命令相关操作对象的共享DDL锁。– Breakable Parse Lock，用来在共享SQL区校验语句。

锁的使用

• 实现加锁机制主要有两种方式：– 1.自动加锁 – 2.手动加锁

• 自动加锁 – 用户在执行INSERT，UPDATE，DELETE，DCL， DDL语句时，ORACLE会自动加锁。– 在一个事务中，ORACLE会自动锁定执行以上语句的 数据库对象，只有事务结束（提交或者回滚），资源 才能被释放。– 默认情况下，更新数据时ORACLE会自动提供排他锁， 使不同用户在更新表中同一数据时不会发生冲突。l 例 模拟两个会话，同时修改员工编号198的员工信息

• 手动加锁 – 除了自动加锁之外，也可以使用手动加锁。– 语法常见的有两种：• 1.表加锁语法 LOCK TABLE 表名 IN 锁类型 MODE [NOWAIT] 锁类型: Exclusive,Share • 2.行加锁语法 SELECT…FROM…FOR UPDATE [NOWAIT] NOWAIT选项是当申请加锁不成功时，就不再等待

使用锁的常见问题

• 如使用锁的方式不当，可能会产生锁冲突，不适当的使用方式 包括：– 不必要的高级别的锁 – 长时间运行的事务 – 没有提交的事务 – 其他产品产生了高级别的锁

• 死锁：当两个或者两个以上的用户彼此等待被对方锁定的资源 时，就有可能产生死锁。

锁的例子(死锁)

• 会话A在时间T1修改资源X，未进行提交 • 会话B在时间T2修改资源Y，未进行提交 • 会话A在时间T3修改资源Y，未进行提交 • 会话B在时间T4修改资源X，死锁产生

 

/   总结   /

 

本文主要讲了增加、修改、删除数据这些操作，作为一个合格的程序员，CRUD的熟练是基础标准

 

往期推荐：

如何入门做软件开发

为什么我不推荐入行程序员

做全栈开发很难吗

关注我的公众号，学习技术或投稿

长按上图，识别图中二维码即可关注