Mysql事务

事务

事务：一个或者一组sql语句组成一个执行单元，每个sql语句相互依赖，这个执行单元要么全部执行，要么全不执行,如果某条语句失败，则整个单元将会回滚

事务的ACID
A:原子性(Atomicicity) 

C:一致性(Consistency)事务必须使数据库从一个一致性状态到另一个一致性状态，也就是执行前后都必须是一致性状态（转钱时总共的钱）

I:隔离性(Isolation)一个事务的执行不能被其他事务干扰。即一个事务内部的操作和使用的数据对并发的其他事务是隔离的，不能干扰。（往一个账号存钱的同时不能从账号取钱）
	隔离级别：
		read_unconmmitted：读取未提交事务的数据（造成脏读）
		read_committed：事务提交后其更新结果可以被其他事务看见（造成不可重复读，一般都更新）
		repeated_read：在一个事务中，对于同一份数据的读取结果相同。前提条件是不能增删，可以更新已有的数据值。（造成幻读，针对增删）
		serialization：牺牲并发性。解决以上所有问题
D:持久性(Durability)一个事务一旦提交，其对数据库的改变是永久性的

==Mysql默认的隔离级别是repeated_read==

事务分类
	隐形事务：insert、delete、SQL单句
	显示事务：有明显是开启和结束的标记（首先要设置自动提交功能为禁用）
	set autocommit=0;//只针对当前会话有效
show variables like 'autocommit';//查看变量

1.开启事务
set autocommit=0;
start transacation;//可选的，上一步默认开启

2.编写事务的sql语句（select、insert、update）           （修改、删除、创建表语句不是）
语句1;
语句2;
...

3.结束事务
commit;提交事务
rollback;回滚事务

查看隔离界别
select @@tx_isolation;

设置事务的隔离级别
set session transaction isolation level 隔离界别;//设置当前链接有效
set global transaction isolation level 隔离级别//全局有效


回滚时delete和truncate的区别
delete可以回滚，truncate不支持回滚

savepoint的使用
set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a;
delete from account where id=24;
rollback to a;
==回滚到a之后id=25删除，id=24回滚==

视图

语法
	create view 视图名 as 查询语句

视图的修改
create or replace view 视图名 as 查询语句
alter view 视图名 as 查询语句

删除视图
drop

查看视图
desc 视图
show create view 视图

视图的更新//更改视图的数据
视图里面insert、update等，在视图和源表一起更新

	不允许更新视图的情况
	1.包含以下关键字：distinct、group by、having、union或者union all
	2.视图是常量查询//select 1 math;
	3.select语句中包含子查询
	4.含有join
	5.from里面有一个不能更新的视图
	6.where子句的子查询引用from子句的表
		create or replace view myv
		as
		select last_name,email,salary
		from employees
		where employee_id in (
			select manager_id
			from employees
			where manager_id is not null;
		);

虚拟表，和普通表一样使用
mysql5.1版本新特性，通过普通表动态生成的数据

特点：
	不保存真实数据，在使用视图时动态生成，临时的，只保存sql逻辑

应用场景：
	多个地方用到同样的查询结果
	该查询结果使用的SQL语句复杂

create view v1
as
要查询的语句

示例：
create view v1
as
select stuname,majorname
from stuinfo s
inner join major m on m.`id`=s.`majorid`;


查询姓张的学生名和专业名
select * from v1 where stuname like '张%';

变量

系统变量：
	全局变量
	会话变量
自定义变量：
	用户变量
	局部变量
用户变量使用步骤：
	声明、赋值、使用（查看、比较、运算）
作用域：针对当前会话有效，同于会话变量的作用域

系统变量：
1.查看所有的系统变量
show global 【session】variables;

2.查看满足条件的部分系统变量
show global variables like '%char%';

3.查看指定的某个系统变量的值
select @@global.系统变量名

4.为某个系统变量赋值
set 系统变量名 =值;
set .@@global.系统变量名=值；
==如果是全局变量就加global，会话加session，默认session==

会话变量：针对当前会话
把上面的global换为session 或者不加去掉global，默认session

用户变量：操作符有：=或者:=
1.声明并初始化
set @用户变量名=值；
set @用户变量名:=值；
set @用户变量名:=值;
==使用set可以使用两种操作符，使用select只能使用:=  ==

2.赋值
通过上面的方式

通过select into
select 字段 into @变量名 from 表;

3.查看变量值
select @用户变量名;


局部变量：在定义的begin end中有效
1.声明：
declear 变量名 类型;
declear 变量名 类型 default 值;

2.赋值
set 局部变量名=值；
set 局部变量名:=值；
set @局部变量名:=值;

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

查看
select 局部变量名;

存储过程和函数

存储过程和函数
1.提高代码重用性
2.简化操作

存储过程：一组预先编译好的SQL语句的集合，理解为批处理语句
1.提高代码重用性
2.简化操作
3.减少编译次数和数据库服务器的连接次数，提高效率

存储过程和函数的区别：存储过程可以有多个返回，函数只能有一个

存储过程：可以有0个返回，也可以有多个返回，适合批量插入和更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果

存储过程

创建语法
create procedure 存储过程名（参数列表）
begin
	存储过程体（一组合法的SQL语句）
end
注意
1.参数列表包含 参数模式、参数名、参数类型
举例：in stuname varchar(20)

参数模式：
in：参数作为输入
out：参数作为输出（返回值）
inout：既可以输入又可以输出

2.存储过程只有一句话，beginend可以省略
存储过程体每条SQL语句必须加封号
存储过程结尾使用（delimter 特殊标志 ）重新设置
==特殊标志是自己设置，不能和封号一样，用作存储过程的结束。在call和end的时候必须使用设置的结尾，在select的时候也用==

调用语句
call 存储过程名（参数列表）;

1.空参数列表

插入到admin表中5条记录
select * from admin;

delimter $

create procedure myp()
begin
	insert into admin(username,`passwd`)
	values('join1','0000'),('join2','0000'),('join3','0000'),('join4','0000');
end $

call myp()$

2.创建带in模式的
根据女神名查询对应的男生信息
create procedure myp(in beautyname varchar(20))
begin  
	select bo.*
	from boys bo
	right join beauty b bo.id=b.boyfriend_id
	where b.name=beautyname;
end$

调用
call myp('柳岩') $


创建存储过程实现，用户登录是否成功
create procedure mm(in username varchar(20),in passwd varchar(20))
begin
	declear result int default 0;
	select count(*) into result
	from admin
	where admin.username=username
	and admin.passwd=passwd;
end$

调用
call mm('张飞',‘888’)；

3.带out模式的存储过程

根据女神们，返回对应的男生名
create procedure myp(in beautyname varchar(20),out boyname varchar(20))
begin 
	select bo.boyname into boyname
	from boys bo
	inner join beauty b on bo.id=b.boyfriend_id
	where b.name=beautyname;
end $

call myp('xiaozhao',@bname)$
select @bname$


根据女神名，返回对应的男神名和男生魅力值

create procedure myp(in beautyname varchar(20),out boyname varchar(20),out usercp int)
begin
	select bo.boyname,bo.usercp into boyname,usercp
	from boys bo
	inner join beauty b on bo.id=b.boyfriend_id
	where b.name=beautyname;
end

call myp('xiapzhap',@name,@cp)$
select @name,@cp$

4.inout模式
create procedure myp(inout a int ,inout b int)
begin
	set a=a*2;
	set b=b*2;
end$

在inout模式中，如果输入输出靠一个值，要提前设置变量
set @m=10$
set @n=20$

call(@m,@n)$

select @m,@n$

存储过程删除
drop procedure 存储过程名;//不支持删除多个


查看存储过程的信息
show create procedure 名字;

函数

创建语法
create function 函数名(参数列表) returns 返回类型
begin
	函数体
end


参数列表分为参数名和参数类型

函数体肯定有return，否则报错

return 值

调用语法
select 函数名(参数列表)

1.无参又返回
返回公司员工个数
create function my() returns int
begin
    declear c int default 0;
	select count(*)
	from employees;
	return c;
end$

select my()$

2.有参数又返回
根据员工名返回工资
create function my(empName varcahr(20)) returns double
begin
	set @sal=0;
	select salary into @sal
	from employees
	where last_name=empName;
	return @sal;
end$

select my('king')$//若是名字是king的有两个值，则返回失败


查看函数
show create function 函数名;

删除函数
drop function 函数名;

流程控制

分支结构
1.if函数

功能：实现简单双分支
语法：
if（表达式1，表达式2，表达式3）
如果表达1 成立，则if返回表达式2 的值，否则返回3


2.case结构
类似switch，用于等值判断
语法：
	case 变量|表达式|字段
	when 要判断的值 then 返回值或者语句;
	when 要判断的值 then 返回值或者语句;
	...
	else 要返回的值或者语句
	end case;
类似多重if，用于区间判断
语法：
	case
	when 要判断的条件 then 返回值或者语句;
	when 要判断的条件 then 返回值或语句;
	...
	else 要返回的值或者语句;
	end case;

==作为表达式结束只有end，返回值是值。可以用在任何地方
作为独立语句使用，只能放在begin end中，结束为end case，返回值是语句。==

创建存储过程，根据传入的成绩显示等级
create procedure test(in score int)
begin  
	case 
	when score>=90 then select 'a';
	when score>=80 then select 'b';
	when score>=70 then select 'c';
	else select 'd';
	end case;
end

3.if结构

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else n;
end if;

用在begin end中

create function test (score int) return char
begin
	if score>=90 and score<=100 then return 'a';
	elseif score >=80 then return 'b';
	else return 'c';
	end if;
end

循环结构
分类：while loop repeat

iterate类似于continue
leave类似break


1.while 
语法：
【名字：】 while 循环条件 do
	循环体
end while【标签】;
相当于普通while

2.loop
语法：
【标签：】 loop
	循环体
end loop【标签】;
相当于死循环

3.repeat
语法：
【标签：】 repeat
	循环体
until 结束循环的条件
end repeat【标签】

相当于do while