MySQL视图、存储过程、 变量、 函数、事务
1.视图
含义：虚拟表，但是和普通表的一样使用
MySQL5.1之后出现特性，是通过动态生产的数据
语法：
create view 视图名称
as
查询语句创建语法 是否实际占用物理空间 使用
表 create table 保持了数据 增删改查
视图 create view 只保存了SQL逻辑 主要是查（可以修改数据，不能添加数据，不能删除数据）删除视图
语法：drop view 视图名
drop view v2;修改视图的内容：
方式1：
create or replace view 视图名
as
查询语句
方式2：
alter view 视图名
as
查询语句查询表结构
desc employess;
查询视图结构
desc v1;注意：
试图不允许更新的情况
包含分组函数、group by、having、去重
2.存储过程
存储过程
类似于Java中方法
含义：一组预先变化的SQL语句集合，理解成批处理语句
好处：
1、提高SQL复用性
2、简化操作
3、减少了编译次数并且减少和数据库服务器的连接次数，提高了效率
创建语法：
create procedure 存储过程名（[参数列表]）
begin
存储过程体（一组SQL语句）
end
用[ ]表示可以不写
注意：
1. 参数列表：参数模型 参数名 参数类型
参数模型：
in:该参数可以作为输入，也就是该参数在调用 存储过程时需要穿入
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入或输出，也就是该参数需要传入，又可以返回
2. 如果存储过程中仅仅只有一句语句，begin和end可以省略不写
3. 存储过程体中每条SQL语句结尾要求都要加上分号
delimiter 重新设置（结束标记）案例：求两个数的和(两个int --> in 一个out类型参数)
create procedure pro1_sum(in a int,in b int,out c int)
begin
-- c是输出结果 set:给变量赋值
set c = a + b;
end;存储过程的使用
调用存储过程：
call 存储过程名()；
调用带参数的存储过程：
call 存储过程名(参数)；
注意：
如果参数中in类型，参数可以是数值，可以是变量
对于out或inout类型，参数必须是变量
MySQL中的变量必须以@开头
例如：call pro_sum(@变量名，@变量名)案例：
@result 就是一个变量，用来接收存储过程中返回值
call pro1_sum(10,20,@result);
selecct @result;
call pro1_sum(@a,@b,@c);
select @c;删除存储过程
drop procedure pro1_sum;案例：查询员工表的平均工资
无in 无out
create procedure pro2_avg()
begin
select round(avg(salary),2) as '平均工资' from employees;
end;
#调用存储过程
call pro2_avg();案例：传递一个员工编号，输出这个员工信息
有in 无out
create procedure pro3(in empId int)
begin
select * from employees where employee_id = empId;
end;
#调用存储过程
call pro3(123);
#删除存储过程
drop procedure pro3;案例：查询出公司的总计工资
无in 有out
create procedure pro4(out totalMoney double)
begin
给变量赋值 set
select 值 into 变量名
select sum(salary) into totalMoney from employees;
end;
#调用存储过程
call pro4(@temp);
select @temp;案例：分页存储过程
8个参数（6个输入、2个输出）
limit （当前页码-1）*每页条目数，每页条目数
6个输入：
表名 tableName
显示字段名 fieldList
排序 orderStr
查询条件 whereStr
每页条目数 pageSize
当前页码 pageNum
2个输出
总记录数 totalSize
总页数 totalPage
create procedure pro_fenye(
in tableName varchar(20),
in fieldList text,
in orderStr text,
in whereStr text,
in pageSize int ,
in pageNum int,
out totalSize int,
out totalPage int
)
BEGIN
-- 使用临时变量存储拼接SQL，然后再去执行SQL语句获取结果
-- 求总记录数 totalSize
-- select count(*) into @totalSize from 表名 where 条件
set @sql = CONCAT_WS(' ','select count(*) into @totalSize from',
tableName,whereStr);
prepare count_sql from @sql;-- 准备执行SQL语句
execute count_sql;-- 执行SQL语句
deallocate prepare count_sql;
-- 给变量赋值
set totalSize = @totalSize;
-- 求总页数： totalSize 和 pageSize
if(pageSize<=0)THEN
set pageSize = 5;-- 默认显示5条数据
end if;
set totalPage = ceil(totalSize/pageSize);
-- 对页码进判断是否符合要求
if(pageNum<1)then
set pageNum = 1;
elseif(pageNum > totalPage)then
set pageNum = totalPage;
end if;
-- 拼接分页SQL语句
set @fenyeSql = CONCAT_WS(' ','select',fieldList,'from',
tableName,whereStr,orderStr,'limit',
(pageNum - 1)*pageSize,',',pageSize);
-- 执行SQL
prepare fenyeSql from @fenyeSql;
execute fenyeSql;
deallocate prepare fenyeSql;-- 解除执行SQL
select @fenyeSql from dual;
END;3.变量
mysql变量
- 系统变量就是系统已经提前定义好了的变量
一般都有特殊的含义，
set names --> 会话变量(当次会话连接才有效果)
全局变量 --> 一直生效的
查看系统变量
show variables;
select @@变量名 调用系统变量
select @@hostname;- 用户变量
系统为了区别系统变量和自定义变量，规定用户自定义变量必须使用一个@符号
变量的定义：
set @变量名 = 值；
select @变量名 := 值；
select 值 into @变量名；
注意：用户变量可以不声明定义，直接使用，不过默认为NULL值
用户变量会话级变量，仅在当前连接有效
例如:
declare @b int default 100; declare是设默认值
set @a = 100;
select @a := 200;
select 300 into @a;
下面不是查询语句，而是赋值语句
select salary,last_name into @sal,@name from employees where employee_id = 100;
select @sal,@name;- 局部变量
由于局部变量也是用户自定义的，所有可以认为局部变量也是用户变量，但是局部变量不需要使用@
局部变量一般定义在SQL语句块中，比如存储过程
定义：使用delcare声明局部变量，其中后面可以加上default，给一个默认值
示例：
declare a int;
declare b int default 100;
设置变量名 = 值；
set 变量名 = 值;
获取变量的值
select 变量名；
create procedure my01()
begin
declare a int default 100;
declare b int;
set b = 20;
select a;
select b;
end;
call my01();4.函数
定义：函数存储着一系列SQL语句，调用函数就是一次性执行这些SQL语句，所有函数可以降低语句重复性
存储过程和函数的区别：
函数是只有一个返回值的，不允许返回一个结果集
存储过程没有返回值函数创建：
create function 函数名([参数列表]) returns 数据类型
begin
SQL语句
return 值;
end;
参数列表： 变量名 数据类型
函数调用：
select 函数名(实参)
存储过程：call 存储过程名
删除函数
drop function 函数名;案例：
求两个数的和
create function fun_sum(a double,b double)
returns double
begin
return a+b;
end;
调用函数
select fun_sum(1.2,9.9);案例：定义一个函数，求总页数totalPage
totalPage = ceil(总记录数/每页条目数)
create function fun_totalpage(totalSize int,pageSize int)
returns int
begin
return CEIL(totalSize / pageSize);
end;
select fun_totalpage(100,20) as '总页数';
删除函数
drop function fun_totalpage;
使用变量
create function fun_totalpage(totalSize int,pageSize int)
returns int
begin
创建一个局部变量
declare num int; 没有赋值，值为null
if(totalSize mod pageSize = 0)then
set num = totalSize div pageSize; -- 整除
else
set num = totalSize div pageSize+1;
end if;
return num;
end;
调用
select fun_totalPage(16,3);案例：日期格式化
create function fun_format(d datetime)
returns varchar(20)
begin
return date_format(d,'%Y年-%m月-%d日');
end;
select fun_format(now());5.事务
1、事务是什么？
一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务（例如：银行账户转账业务）
一个完整的业务需要批量的DML（insert、update、delete）语句共同联合完成
事务只和DML语句相关，或者DML语句才有事务
事务处理可以维护数据库的完整性，它保证了成批的SQL语句操作要么全部执行，要么全部不执行
2、事务想要做到什么效果？
（1）可靠性：数据库要保证当insert或update的时候抛出异常或者数据库宕机了的时候保证数据前后一致，
（2）并发处理：当多个请求过来（很多人同时操作一张表的数据），其中一个请求对数据修改操作的时候会有影响
为了避免脏读、幻读、不可重复读，所以需要对事务之间的读写操作进行隔离。隔离有四种。案例：转账操作理解事务
关于银行的转账业务，一个完整业务，最小单元不可分割的
创建银行账户表
create table tb_account(
aNo int PRIMARY key auto_increment,
balance decimal(10,2)
);
insert into tb_account(balance) values(1000);
select * from tb_account;
转账操作
1 --->2 转500
update tb_account
set balance = balance - 500
where aNo = 1;
------------问题-----------------------
update tb_account
set balance = balance+500
where aNo = 2;
以上两条DML语句必须同时成功或者同时失败。
最小单元不可再分
当第一条DML语句执行成功后，并不能将底层数据库中的第一个账户的数据进行修改
只是将操作记录一下，这个记录是在内存中完成的，当第二条DML语句执行成功后
和底层数据库文件的数据完成数据同步
若第二条DML语句执行失败，则清空所有的历史操作记录
要完成以上的功能必须借助事务
使用事务完成转账
start transaction; 手动开启事务
update tb_account
set balance = balance - 500
where aNo = 1;
-----------问题----------------
update tb_account
set balance = balance + 500
where aNo = 2;
commit; ----提交事务，同步到数据库文件中事务的四大特性(ACID)
1.原子性A：事务是最小单元，不可再分
一个事务中的所有操作，要么全部成功，要么全部不成功，不会在中间某个环节结束
事务在执行过程中若发生了错误，则会被回滚rollback到事务的开始前的状态，就像没有执行一样
2.一致性C：事务要求所有DML语句操作的时候，必须保证同时成功或同时失败
在事务开始之前和事务结束之后，数据库完整性没有被破坏，例如转账，两个用户转账之前余额总额为2000，转账之后应该是2000，不能凭空多或者少
3.隔离性I：事务A和事务B之间具有隔离性
数据运允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行的时候由于交叉执行而导致数据不一致。
事务隔离级别不同，导致结果也不一样
在MySQL中只有使用innoDB存储引擎才支持事务
事务隔离级别：
-- 未提交读 read uncommitted
-- 已提交读 read committed
-- 可重复读 repeatable read
-- 可串行读 serializable 某一个用户访问一张表时，其他用户都不能访问
4.持久性D：是事务的保证，事务结束的标志（内存数据持久到硬盘中）
一旦commit或rollback，事务处理结束后，对数据的修改是永久的同步到数据库文件中和事务相关的概念TCL
提交：commit
回滚：rollback事务什么时候开启，事务什么时候结束
开始标志：
任何一条DML语句（insert、update、delete）执行，标志事务的开启
结束标志：
commit提交：成功的结束，将所有DML语句操作历史记录和底层磁盘数据完成同步
rollback回滚：失败的结束，将所有DML语句操作的记录全部清空
对于之前学DML语句的时候，并没有用到事务任何的相关的知识，
因为DML语句执行会自动开启事务，然后执行完毕后会自动结束事务事务的隔离级别
-- 设置事务隔离级别
set session transaction isolation level read uncommitted;
-- 开启手动提交
set autocommit = 0;
-- 开启事务
start transaction;
-- DML语句
-- 结束事务
rollback;1、读未提交 read uncommitted
事务A和事务B，事务A未提交的数据，事务B可以读取到的
这里的事务B读取到的数据就是“脏数据”
这种隔离级别是最低的，mysql数据库默认的隔离级别要高于该级别#脏读
开启两个cmd，来模拟事务A和事务B
事务A：
登录：mysql -u root -p密码
设置隔离级别：
set session transaction isolation level read uncommitted;
查看当前的事物隔离级别
select @@tx_isolation;
设置手动提交
set autocommit = 0;
开启事务
start transaction;
添加一条数据
insert into 表 values(值);
事务B
登录：mysql -u root -p密码
设置隔离级别：
set session transaction isolation level read uncommitted;
查看当前的事物隔离级别
select @@tx_isolation;
查询表的数据
select * from 表; -- 能看到事务A添加的数据(未提交)
事务A：
rollback; -- 事务回滚，回到开启事务之前的状态
事务B：
select * from 表;2、读已提交 read committed
事务A和事务B，事务A提交的数据，事务B才能读到
这种隔离级别是高于读未提交
3、读可重复 repeatable read 【mysql默认的隔离级别】
事务A和事务B，事务A提交之后的数据，事务B读取不到，事务B是可重复读取数据#幻读
事务A：
登录：mysql -u root -p密码
设置隔离级别：
set session transaction isolation level read committed;
查看当前的事物隔离级别
select @@tx_isolation;
事务B
登录：mysql -u root -p密码
设置隔离级别：
set session transaction isolation level read committed;
查看当前的事物隔离级别
select @@tx_isolation;
查看表的数据
select * from 表 -- 只有原来的数据
事务A
设置手动提交
set autocommit = 0;
开启事务
start transaction;
添加一条数据
insert into 表 values(值);
提交事务
commit;
事务B
查看表的数据
select * from 表 -- 只有原来的数据4、串行化 serializable
事务A和事务B，事务A在操作数据库时，事务B只能排队等待
这种隔离级别很少使用，用户体验差，吞吐量太低
这种级别可以避免“幻读”，每一次读取的都是数据库真实存在的数据
脏读：
1、在事务A执行过程中，事务A对数据进行了修改，事务B读取了事务A修改后的数据
2、由于某些原因，事务A并没有完成提交，发生了rollback操作，则事务B读取到的数据就是脏读数据
这种读取到另一个事务未提交的数据的现象就是脏读（Dirty Read）
不可重复读：
事务B读取了两次数据，在这两次读取的过程中事务A修改了数据，导致事务B在两次读取出来的数据不一致
这种在同一个事务中，前后两次读取的数据不一致的现象就是不可重复读
幻读：
事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后一次读取到前一次查询没有看到内容数据
幻读和不可重复读有些类似，但是幻读强调是集合的增减，而不是单条数据的更新（修改操作）设置隔离级别
设置隔离级别
set session transaction isolation level 隔离级别;
查看当前的事物隔离级别
select @@tx_isolation支持事物的存储引擎只有InnoDB
为什么需要事物
一个用户提交了一个订单，那么这条数据包含了两个信息：用户信息和购买的商品信息，需要我们分别存储到用户表和商品表，如果不采用事物，可能会出现，商品信息插入成功，而用户信息没有插入成功，这个时候就会出现无主商品；用户付钱了，却得不到商品。如果采用了事物就可以保证用户信息和商品信息都必须插入成功，该事务才算成功。
事物保留点
把tb_01表删除
drop table tb_01;
create table tb_01(
id int primary key,
name varchar(20),
pwd varchar(20)
)
select * from tb_01;
设置手动提交
set autocommit = 0;
开启事务
start transaction;
插入一条数据
insert into tb_01 values(1,'admin','123');
创建一个保留点
savepoint s1;
插入一条数据
insert into tb_01 values(2,'abc','456');
查询数据 查到两条数据
select * from tb_01;
回到保留点
rollback to s1;
查询数据 查到一条数据
select * from tb_01;
回滚
rollback;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
