mysql之TCL语言(事务)、视图、变量

目录

TCL语言(事务)

一、事务的ACID属性

二、事务的创建

三、数据隔离级别

视图

一、创建视图

二、视图的修改

三、删除视图

变量

一、系统变量

二、自定义变量

---

TCL语言(事务)

Transaction Control Language 事务控制语言

事务：

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

案例：转账

简意：sql语句都互相依赖，不能有的语句成功有的语句失败，要么都运行成功，要么都运行失败。

存储引擎：

1、在mysql中的数据用不同的技术存储在文件或内存中

2、通过SHOW ENGINES来查看mysql支持的存储引擎

3、在mysql中用的较多的有：innodb，myisam，memory等。其中innodb支持事务，而myisam、memory不支持。

一、事务的ACID属性

1、原子性(atomicity)指一个不可分割的工作单位，要么都发生，要么都不发生。

2、一致性(Consistency)事务必须使数据库从一个一致性状态变换到另一个一致性状态。

3、隔离性(Isolation)指一个事务的执行不能其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务间不能互相干扰。

4、持久性(Durability)指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

二、事务的创建

隐式事务：事务没有明显的开启和结束的标记

比如insert、update、delete语句

显式事务：事务具有明显的开启和结束标记

前提：必须禁用自动提交功能

关闭语句：SET autocommit=0;

SHOW VARIABLES LIKE’变量名’;查看变量状态

步骤：

1、开启事务SET autocommit=0;

START TRANSACTION;（可选的）

2、编写事务中的sql语句（select insert update delete）

3、结束事务

commit;提交事务 //将内存中的数据写入到硬盘

rollback;回滚事务//将内存中的数据不写入硬盘

三、数据隔离级别

同时运行多个事务且访问相同的数据时，如果没有隔离机制就会产生各种并发问题：

1、脏读：对于两个事务T1、T2，T1读取了已经T2更新但还没被提交的字段后，若T2回滚，T1读取的内容就是临时且无效的。

2、不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。

3、幻读：对于两个事务T1、T2，T1从表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再次读取同一个表，就会多出几行。

4种事务隔离级别

隔离级别	描述
READ UNCOMMITED (读未提交数据)	允许事务读取未被其他事务提交的变更 出现脏读、幻读、不可重复读
READ COMMITED (读已提交数据)	只允许事务读取已经被其他事务提交的变更 解决脏读
READATABLE READ (可重复读)	确保事务可以多次从一个字段中读取相同的值，在这个事务的持续期间禁止其他事务对这个字段进行更新 解决脏读和不可重复读
SERIALIZABLE(串行化)	确保事务可以从一个表中读取相同的行，再这个事务持续期间，禁止其他事务对该表执行行插入。可以解决所以并发问题，但是效率低下，需要等待该事务的完成，其他事务才能修改数据。

oracle支持READ COMMITED(默认) SERIALIZABLE。

mysql支持4种 REPEATABLE READ为默认。

查看默认隔离级别

SELECT @@TRANSACTION_ISOLATION; //8.0以后的版本

改变隔离级别：

设置当前隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;

设置全局隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

SAVEPOINT 节点名; //设置保存点

搭配ROLLBACK回滚到保存节点。

补充：DELETE和TRUNCATE在事务中使用时的区别：DELETE支持回滚，TRUNCATE不支持。

视图

含义：虚拟表，和普通的表一样使用

mysql5.1版本的新特性，是通过表动态生成的数据

应用场景：多个地方用到同样的查询结果，该查询结果使用的sql语句较复杂。可以将该复杂的语句封装到视图里面。

一、创建视图

语法：

CREATE VIEW 视图名

AS

查询语句;

作用：1、重用sql语句

2、简化复杂的sql操作，不必知道它的查询细节

3、保护数据，提高安全性

二、视图的修改

方式一：

CREAT OR REPLACE VIEW 视图名

AS

查询语句;

方式二：

语法：

ALTER VIEW 视图名

AS

查询语句;

三、删除视图

语法：

DROP VIEW 视图名1,视图名2,视图名3…;

四、查看视图（结构）

DESC 视图名;

或者SHOW CREATE VIEW 视图名;

五、视图的更新

视图可以使用

INSERT、UPDATE、DELETE进行增删改，且回改变原始表的数据，一般要为视图设定权限。

具备以下特点的视图不允许更新

1、包含以下关键字的sql语句：分组函数、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL

2、常量视图（就是只单纯查询一个常量）

例如:SELECT ‘John’ NAME;

3、select中包含子查询

4、JOIN

5、FROM一个不能更新的视图

6、WHERE子句的子查询引用了FROM子句中的表

视图与表的对比

视图 CREATE VIEW 只有sql逻辑占用物理空间 可以增删改查（但不推荐）

表 CREATE TABLE 保存了数据 可以增删改查

变量

分类：

系统变量：

全局变量

作用域：服务器每次启动将为所有全局变量赋初始值，针对所有的回话（连接）有效，但不能跨重启。

会话变量

作用域：仅仅针对于当前会话（连接）有效。

自定义变量：

用户变量

局部变量

一、系统变量

说明：由系统提供，不是用户定义，属于服务器层面

使用书法

1、查看所有的系统变量

SHOW GLOBAL|【SESSION】 VARIABLES; //其中填GLOBAL是全局变量，SESSION是会话变量可以不填也为会话变量。

2、查看满足条件的部分系统变量

SHOW GLOBAL|【SESSION】 VARIABLES LIKE ”;

3、查看指定的某个系统变量的值

SELECT @@GLOBAL|SESSION.系统变量名;

4、为某个系统变量赋值（赋值后跨连接有效）

方式一：

SET GLOBAL|SESSION 系统变量名=值;

方式二：

SET @@GLOBAL|SESSION.系统变量=值；

注意：如果是全局级别，则加GLOBAL，会话级别加SESSION如果不写默认为SESSION。

二、自定义变量

使用步骤：

声明

赋值

使用（查看、比较、运算等）

1、用户变量

作用域：针对于当前会话（连接）有效，同于会话变量的作用域。

应用在任何地方，也就是BEGIN END（后面讲到）

#1、声明并初始化

SET @用户变量名=值;

SET @用户变量名:=值; 也可以

SELECT @用户变量名:=值; 也行

#2、赋值（更新用户变量的值）

方式一：

按1的方式

方式二：通过SELECT INTO

 

SELECT 字段 INTO 变量名

FROM 表;

#3、使用（查看用户变量的值）

SELECT @用户变量名;

2、局部变量

作用域：仅仅在定义它的BEGIN END 中有效

应用在 BEGIN END中的第一句话

#1、声明

DECLARE 变量名 类型; //注意需要定义类型

加初始化赋值DECLARE 变量名 类型 DEFAULT 值;

#2、赋值

赋值与用户变量赋值方法一致，但是不用加@

除了SELECT @局部变量名:=值;

#3、使用

SELECT 局部变量名;