MySQL数据库进阶

1.事务

1.1 事务的概念

事务：事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

1.2 事务的特点

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3. 隔离性（Isolation）
   事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

1.3 事务的创建

• 隐式事务：事务没有明显的开始和结束的标记（比如：insert，delete、update语句）
• 显示事务：
  事务必须有明显的开启和结束的标记
  前提：必须设置自动提交功能为禁用（set autocommit=0）

事务的步骤

# 步骤一：开启事务
set autocommit = 0;
start transaction; # 可选
# 步骤二 编写事务的SQL语句（select、delete、update、insert）
语句1
语句2
。。。
# 步骤三结束事务
commit; #提交事务
rollback;#回滚事务

1.4 数据库的隔离级别

1.4.1 什么是脏读、幻读和不可重复读

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

• 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
• 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
• 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插 入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行

1.4.2 脏读、幻读、不可重复读的区别

不可重复读和脏读的区别是，脏读读取到的是一个未提交的数据，而不可重复读读取到的是前一个事务提交的数据。
而不可重复读在一些情况也并不影响数据的正确性，比如需要多次查询的数据也是要以最后一次查询到的数据为主。
幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

1.4.3 事务的隔离性

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.

Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ

每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别.
查看当前的隔离级别:SELECT @@tx_isolation;
设置当前 mySQL 连接的隔离级别: set transaction isolation level read committed;
设置数据库系统的全局的隔离级别: set global transaction isolation level read committed;

1.4.4 设置保存点

savepoint：节点；设置保存点。

set autocommit  = 0；
start transaction；
delete from account where id = 25；
savepoint a #设置保存点
delete from account where id= 28；
rollback to a； 意思在于设置保存点之后的信息可以恢复。

2. 视图

2.1 概念

一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

2.2 创建视图

语法

create view 视图名 as 查询语句

表准备

insert into ss (name,math) VALUES
('张三',60),
('李四',70),
('王五',80)

create view v1 as SELECT * from ss
select * from v1 where math>70

视图的好处

1. 重用sql语句
2. 简化复杂的sql操作，不必知道它的查询细节
3. 保护数据，提高安全性

2.3修改视图

语法

# 方式一
create or replace view 视图名 as sql语句
# 方式二
alter view 视图名 as 查询语句

测试

CREATE or REPLACE view  v1 as select name from ss
select * from v1
alter view v1 as select id from ss
select * from v1

2.4 删除视图

语法

drop view 视图名,视图名

2.5 查看视图

语法

desc 视图名
show create view 视图名

2.6更新视图

如果视图进行了更新，视图对应的原来表中的数据也会更新
视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的（插入、删除、update）。

• 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

3. 变量

3.1变量的分类

1. 系统变量

• 全局变量
• 会话变量

2. 自定义变量

• 局部变量
• 用户变量

3.2 系统变量

变量由系统提供，不是用户定义，属于服务器方面
全局变量作用域：服务器每次启动将会为所有的全局变量赋初始值，针对所有的会话（连接）有效，但不能跨重启
会话变量作用域：仅仅对于当前对话（连接）有效
使用语法
注意如果是全局变量需要加global，如果是局部变量需要加session，如果不写默认为session

1. 查看所有的系统变量

show session VARIABLES
show GLOBAL variables 

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

show GLOBAL variables like '%a%'

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

select @@global .autocommit
select @@session .binlog_format

4. 为某个系统变量赋值

方式一:

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

方式二

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

3.3自定义变量

变量是用户自定义的，不是系统的
自定义变量分为：局部变量和用户变量

3.3.1 用户变量

作用域：针对于当前会话（连接）有效，同于会话变量的作用域（可以放在任何地方）

常用方法

1. 赋值操作符： =或 :=

• 声明并初始化

set @用户变量名=值
set @用户变量名 :=值
select @用户变量名 :=值

• 赋值

方式一：通过set或者select
set @用户变量名=值
set @用户变量名 :=值
select @用户变量名 :=值
方式二： 通过select into 
select 字段 into 变量名 from 表

• 查看变量的值

select @用户变量名

• 查看变量的值

select @用户变量名

举例
用户变量查看ss表中的总行数

set @count =(select count(*) from ss)
select @count

3.3.2 局部变量

作用域：仅仅在定义他的begin end中有效
使用地方：begin end的第一级

• 声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 default 值

• 赋值

方式一：通过set或者select
set 局部变量名=值
set 局部变量名 :=值
select @局部变量名 :=值
方式二： 通过select into 
select 字段 into 局部变量名 from 表

·- 使用

select 局部变量名

3.3.3 用户变量和局部变量的对比

	作用域	定义和使用位置	语法
用户变量	当前会话	会话中的任何地方	必须加@符号，不用限定类型
局部变量	begin end中	只能在begin end中且为第一句话	一般不用加@符号，需要限定类型

4.存储过程

4.1 存储过程相关的概念

一组预先编译好的SQL语句的集合，可以理解为成批处理的语句
好处

1. 提高代码的重用性
2. 简化操作
3. 减少编译次数并且减少和数据库服务器的连接次数，提高了效率

4.2 创建语法

create procedure 存储过程名(参数列表)
begin
	存储过程体（一组合法的SQL语句）
end 

/**
注意
1. 参数列表包含三部分：参数模式 参数名 参数类型
举例 in stuname  varchar(20)
参数模式：
in ： 该参数可以作为输入，也就是该参数需要调用方法传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入，有可以作为输出，也就是该参数即需要传入值，也可以返回值

2. 如果存储过程体中仅仅只有一句话 begin end可以省略
存储过程体中的每条SQL语句必须加分号
存储过程的结尾可以使用 delimiter 重新设置
语法
delimiter 结束标记
**/

4.2.1. 空参的存储过程

delimiter $
CREATE procedure my()
BEGIN
insert into ss (name,math) VALUES
('1',11),
('2',22),
('3',33),
('4',44);
ENd $

call my()

4.2.2带in模式的存储过程

# 根据姓名和属性成绩查找用户信息
CREATE PROCEDURE m(in name varchar(20),in math VARCHAR(20))
BEGIN
   
  select * from ss where ss.name=name and ss.math=math;
END
call m('1',1)

4.2.3带out模式的存储过程

CREATE procedure ss(in id int ,out math1 int)
BEGIN
	select ss.math into math1 from ss where ss.id=id;
END

call ss(1,@math1)
select @math1

4.2.4 带inout模式的存储过程

CREATE PROCEDURE x(INOUT a int , inout b int)
BEGIN
     set a=a+a;
	set b=b+b;
END
set @m=1
set @n=2
call x(@m,@n)
select @m,@n

4.3 存储过程的删除

语法

drop  PROCEDURE 存储过程名 一次只能删除一个

4.4 存储过程的查看

show create PROCEDURE 存储过程名

5. 函数

5.1相关的概念

5.1.1含义

一组预先编译好的SQL语句的集合，可以理解为成批处理的语句

5.1.2优点

1. 提高代码的重用性
2. 简化操作
3. 减少编译次数并且减少和数据库服务器的连接次数，提高了效率

5.1.3函数和存储过程的区别

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

5.2 创建语法

create function 函数名（参数列表） returns 返回值类型
begin
	函数体
end
/**
注意：
1. 参数列表包含两部分 参数名 参数类型
2. 函数体肯定有return语句，没有return语句会报错，如果return语句没有放在最后也不会报错但不建议
3. 函数体中只有一句话可以省略begin end
4. 使用delimiter语句设置结束标志
**/

5.2.1 无参数类型

有表中数据的个数

CREATE FUNCTION f1() RETURNS int
BEGIN
 DECLARE cnt int DEFAULT 0;
  select count(*) into cnt from ss;
  return cnt;
end 

SELECT f1()

5.2.2 有参数类型的

根据id查询用户姓名

CREATE FUNCTION f2(id int) RETURNS VARCHAR(20)
BEGIN
   DECLARE name VARCHAR(20);
		select ss.name into name from ss where ss.id=id; 
    RETURN name;
END
select f2(1)

5.2.3 查看函数

show CREATE FUNCTION f2

5.2.4 删除函数

drop function f2;

5.3 流程控制结构

5.3.1 流程结构的分类

1. 顺序结构：程序从上往下一次执行
2. 分支结构：程序从两条或多条程序中选择一条去执行
3. 循环结构：程序在满足一定条件的基础上，重复执行一段代码

5.3.2 分支结构

5.3.2.1. if函数

• 功能：实现简单的双分支
• 语法：if(表达式1，表达式2，表达式3)
• 执行顺序：如果表达式1 成立，则if函数返回表达式2的值，否则返回表达式3 的值
• 应用：任何地方

5.3.2.2. case结构

• 情况一：类似于java中的switch语句，一般用于等值判断
  语法：

case 变量|表达式|字段
when 要判断的值 then 返回值1 或语句1;
when 要判断的值 then 返回值2 或语句2;
...
else 要返回的值n或语句N
end case;

• 情况二：类似于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
   具体举例
   根据成绩判断等级

CREATE PROCEDURE ju(in score int)
BEGIN
		case
		WHEN score>90 THEN select 'a';
		WHEN score>80 THEN select 'b';
		WHEN score>70 THEN select 'c';
		WHEN score>60 THEN select 'd';
		ELSE SELECT 'e';
    END case;
END
call ju(99)

5.3.2.1 if结构

• 功能：实现多重分支
• 语法

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

• 具体举例

CREATE FUNCTION text_if(score int) RETURNS char
BEGIN
   if(score>90) THEN RETURN 'a';
	 elseif(score>80) then RETURN 'b';
   elseif(score>60) then RETURN 'c';
    else RETURN 'd';
		end if;
END
select text_if(91)

5.3.3 循环结构

5.3.3.1分类

while、loop、repeat

5.3.3.2 循环控制

iterate类似于continue，继续，结束本次循环进行下一次循环
leave类似于break，跳出，结束当前所在的循环

5.3.3.3 语法

1. while

[标签] while 循环条件 do
		循环体
end while [标签];

2. loop

[标签] loop
		循环体
end loop [标签];
可以用了模拟简单的死循环

3. repeat
   语法

[标签] repeat
	循环体;
until 结束循环的条件
end repeat [标签];

样例

CREATE PROCEDURE while_text(in cnt int)
BEGIN
    DECLARE i int DEFAULT 1;
 while i<=cnt DO
		INSERT into ss(name,math) VALUES (CONCAT('while',i),i);
		set i=i+1;
  end WHILE;
END
call while_text(2)
select * from ss

leave使用

CREATE PROCEDURE text_leave(in cnt int)
BEGIN
      DECLARE i int DEFAULT 1; 
        a: WHILE i<=cnt DO
					INSERT into ss(name,math) VALUES (CONCAT('do',i),i);
					set i=i+1;
						if i>=3 then LEAVE a;
							end IF ;
				end while a;      
END

call text_leave(30)

select * from ss