MySQL笔记之TCL语言、视图、变量、存储过程和函数、流程控制结构

TCL 

transcation control language 事务控制语言

事务：

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

事务的ACID属性

1.原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2.一致性（Consistency）

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

3.隔离性（Isolation）

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

4.持久性（Durability）

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

事务的创建：

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

比如insert、update、delete语句

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

前提：必须先设置自动提交功能为禁用

set autocommit = 0;

步骤1.开启事务

set autocommit = 0;

start transcation;可选的 

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

语句1；

语句2；

......

步骤三：结束事务

commit；提交事务

rollback；回滚事务

savepoint 回滚点名；设置回滚点

set autocommit=0;

start transaction;

delete from account where id = 25;

savepoint a;

delete from account where id = 28;

rollback to a;#回滚到保存点

并发事务

1.事务的并发问题是如何发生的？

多个事务同时操作同一数据库的相同数据时

并发问题有哪些？

脏读：一个事务读取了其他事务还没提交的数据，读到的是其他事务更新的数据

不可重复读：一个事务多次读取结果不一样

幻读：一个事务读取了其他事务还没提交的数据，知识读到其他事务插入的数据

如何解决并发问题？

通过设置隔离级别来解决并发问题

事务的隔离级别：

*

视图

一、视图的创建

create view 视图名 as 查询语句；

二、视图的修改

方式一：

create or replace view 视图名 as 查询语句；

方式二：

alter view 视图名 as 查询语句；

三、删除视图

drop view 视图名，...；

四、查看视图

desc 视图名；

或者 show create view 视图名；

五、视图的使用

视图一般用于查询的，而不是用于更新的，对视图的增删改（insert、delete、update），也会体现在原表上，所以有的时候给视图增加只读的权限。

以下视图不能更新：

（1）具有以下关键字的sql语句:分组函数、distinct、group by、havin、union或者union all

（2）常量视图

（3）select中包含子查询

（4）join

（5）from 不能更新的视图

（6）where子句的子查询用到from子句中的表

视图和表的对比：

delete和truncate在事务中区别：

delete支持回滚而truncate不支持回滚

测试：

变量

1.系统变量：全局变量、会话变量

2.自定义变量：用户变量、局部变量

一、系统变量：

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

使用语法：

1.查看所有的系统变量

show global|session variables;不写的话是session

2.查看满足条件的部分变量

show global|session variables like '%char%';

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

select @@global|session.系统变量值

4.为某个系统变量赋值

方式一：

set  global|session 系统变量名=值；

方式二：

set @@global|session.系统变量名=值；

全局变量

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

会话变量

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

自定义变量

说明：变量是用户自定义的，不是由系统定义的

使用步骤：

声明、赋值、使用（查看，比较，运算等）

用户变量

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

（1）声明并初始化

赋值操作符=或:=

set @用户变量名=值；

set @用户变量名:=值；

select @用户变量名:=值；

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

方式一：通过set或者select

set @用户变量名=值；

set @用户变量名:=值；

select @用户变量名:=值； 

方式二：通过select into

select 字段 into变量名 from 表；

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

select @用户变量名；

局部变量

作用域：仅仅在定义它的begin end中有效

应用在begin end中的第一句话

（1）声明

declare 变量名 类型；

declare 变量名 类型 default 值；

（2）

2）赋值

方式一：通过set或者select

set 局部变量名=值；

set 局部变量名:=值；

select @局部变量名:=值； 

方式二：通过select into

select 字段 into 局部变量名 from 表；

（3）使用

select 局部变量名；

上面的2会报错，因为不在begin end中。

存储过程和函数

类似于java中的方法

存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

好处：

1.提高代码的重用性

2.简化操作

3.减少编译次数，并且减少了和数据库服务器的连接次数，提高了效率

一、创建语法

create procedure 存储过程名（参数列表）

begin 

        存储过程体（一组合法的SQL语句）

end

注意：

1.参数列表包含三部分

参数模式  参数名  参数类型

举例： IN stuName varchar(20)

参数模式：

IN：该参数可以作为输入，也就是改参数需要调用方传入值

OUT：该参数可以作为输出，也就是改参数可以作为返回值

INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2.如果存储过程体仅仅只有一句话，begin end 可以省略

存储过程的每条语句的结尾要求必须加分号

存储过程的结尾可以使用delimiter重新设置

语法：

delimiter 结束标记

二、调用语法

call 存储过程名（实参列表）；

案例：

1.空参列表

2.创建带in模式参数的存储过程

调用那里是call myp4...

3.创建out模式的存储过程

4.带inout模式参数的存储过程

测试：

三、删除存储过程

语法：drop procedure 存储过程名

drop procedure p1;

一次只能删除一个

四、查看存储过程的信息

show create procedure myp2;

不可以用desc

测试：

函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

好处：

1.提高代码的重用性

2.简化操作

3.减少编译次数，并且减少了和数据库服务器的连接次数，提高了效率

函数与存储过程的区别：

存储过程：可以有0个返回也可以有多个返回，适合做批量插入，批量更新

函数：有且仅有一个返回，适合做处理数据后返回一个结果

一、创建语法

create function 函数名（参数列表） returns 返回类型

begin

        函数体

end

二、调用语法

1.无参有返回

2.有参有返回

三、查看函数

show create function myf3;

四、删除函数

drop function myf3;

流程控制结构

一、分支结构

1.if函数

功能：实现简单的双分支

语法：

IF（表达式1，表达式2，表达式3）

执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值

应用：任何地方

2.case结构

情况1：类似于java中的switch结构，一般用于等值判断

语法：

 case 变量|表达式|字段

when 要判断的值 then 返回的值1或语句1；

when 要判断的值 then 返回的值2或语句2；

...

else 要返回的值n；

end

情况2：类似于java中的多重if判断，一般用于实现区间判断

语法：

 case 

when 要判断的条件1  then 返回的值1或语句1；

when 要判断的条件2  then 返回的值2或语句2；

...

else 要返回的值n或语句n；

end case；

特点：

（1）可以作为表达式，嵌套在其他语句中使用，可以放在任何地方使用，begin end中或者begin end外面

可以作为独立的语句使用，只能放在begin end中

（2）如果when 中的条件或者语句成立，那么执行then后面的语句，并且结束case，如果都不满足，则执行else中的语句或值

（3）else可以省略，如果else省略了，并且所有的when条件都不满足，则返回null

3.if结构

功能：实现多重分支

语法：

if 结构

功能：实现多分支

语法：

if 条件1 then 语句1；

elseif 条件2 then 语句2；

......

[else 语句n];

end if;

应用在begin end中

二、循环结构

分类： while 、loop、repeat

循环控制：

iterate类似于continue，继续，结束本次循环，继续下一次

leave 类似于 break，跳出，结束当前所在的循环

1.while

语法

【标签：】while 循环条件 do 

    循环体；

end while 【标签】;

2.loop

语法：

【标签：】loop 

    循环体；

end loop 【标签】;

可以用来模拟简单的死循环

3.repeat

语法：

【标签：】repeat

    循环体；

until 结束循环条件

end repeat【标签】;

1.没有添加循环控制语句

2.添加leave语句

3.添加iterate语句

测试：

三、顺序结构

从上往下依次执行