MySQL--高级

目录

• 视图
  • 什么是视图
  • 为什么要是用视图
  • 使用方法
  • 案例
  • 注意：
• 触发器
  • 使用方法
  • 案例
• 事务 *****
  • 什么是事务
  • 为什么需要事务
  • 事物的特点
  • 使用方法
  • 修改隔离级别
• 存储过程
  • 三种数据处理方式
  • 使用存储过程
• 函数
  • 内置函数
  • 自定义函数
• 备份与恢复
  • 备份
  • 恢复
• 流程控制
  • case语句
  • 定义变量
  • while循环
  • loop循环
  • repeat循环
  • 案例

视图

什么是视图

视图就是一张虚拟的表, 通过一张表或多表查询构成的

为什么要是用视图

当我们查询一些表时， 我们的sql语句非常复杂， 如果每一次都要写一遍的话，无疑会疯的，所以我们可以通过视图来避免这样的操作

又或者我们只想向别人展示部分数据而不是全部，也可以通过视图实现，例如工资表，员工只能看到自己的那部分，而老板可以看到所有人的

所以视图有两大作用：

1. 避免重复编写相同的sql语句
2. 进行数据隔离

使用方法

• 创建视图语法

  create [or repalce] view view_name as sql查询语句 
  

  or replace 表示当视图存在时，替换原来的视图

• 视图的使用方法

  # 查看视图
  1. desc view_name;  # 查看数据结构
  2. show create view view_name;  # 查看创建语句
  
  # 修改视图
  alter view view_name as 新的sql语句;
  
  # 删除视图
  drop view view_name;
  

案例

# 简化sql编写
create table student(
  s_id int(3),
  name varchar(20), 
  math float,
  chinese float 
);
insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75);

create table stu_info(
  s_id int(3),
  class varchar(50),
  addr varchar(100)
);
insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江');
# 查询班级和学员的对应关系做成一个视图  方便后续的查询 
create view class_info as select student.s_id,name,class from student join stu_info on student.s_id = stu_info.s_id;

select *from class_info;
+------+------+--------+
| s_id | name | class  |
+------+------+--------+
|    1 | tom  | 二班    |
|    2 | jack | 二班    |
|    3 | rose | 三班    |
+------+------+--------+

注意：

1. 当原表发生变化时，视图也会进行相应的变化

2. 当修改视图时，原表也会进行变化，所以一般不要对视图进行修改

触发器

触发器是一段与某个表相关的sql语句，会在某一个时间点，满足某个条件后自动触发执行，可以用来自动记录一些东西，比如：当某个表被修改时，自动记录一些数据，执行sql语句

这其中有两个关键因素：

1. 时间点

   • 事件发生前 before

   • 事件发生后 after

2. 事件

   • update
   • insert
   • delete

   触发器自动包含两个对象

   • old 旧的数据，update、delete中可用
   • new 新的数据，update、insert中可用

使用方法

• 创建触发器语法

  create trigger t_name t_time t_event on table_name for each row
  begin
  	sql……;
  end |
  

• 补充：delimiter是对结束符进行重载，主要是因为在触发器中的sql语句要添加;，但是在终端中会默认;为结束了，所以我们需要进行结束符重载

• 触发器的使用方法

  # 删除触发器
  drop trigger t_name;
  
  # 查看所有触发器
  show triggers;
  
  # 查看某个触发器的语句
  show create trigger t_name;
  

案例

# 准备数据
create table cmd(
    id int primary key auto_increment,
    user char(32),
    priv char(10),
    cmd char(64),
    sub_time datetime,
    success enum("yes","no")
);

# 错误日志表
create table errlog(
    id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
);

# 需求：当插入cmd表的时候，如果执行状态为失败，那么将信息插入到errlog中

# 1. 创建一个触发器叫cmd_insert
# 2. 触发器会在插入数据到cmd表后执行
# 3. 当插入的记录success为no时，自动插入到errlog表中

delimiter |   # 重载结束符
create trigger cmd_insert after insert on cmd for each row
begin
if new.success = "no" then
	insert into errlog values(null,new.cmd,new.sun_time);
end if;
end |
delimiter ;  # 还原结束符

事务 *****

什么是事务

事务就是一系列sql语句的组合，是一个整体

为什么需要事务

很多时候一个数据操作,不是一个sql语句就完成的,可能有很多个sql语句,如果部分sql执行成功而部分sql执行失败将导致数据错乱!

例如转账操作,

1. 从原有账户减去转账金额

2. 给目标账户加上转账金额

若中间突然断电了或系统崩溃了,钱就不翼而飞了!

注意：在官方提供的cmd的客户端下，事务是默认开启的，会将一条sql语句作为一个事务

事物的特点

1. 原子性：指的是这个事务中的sql语句是一个整体，不能进行拆分，要么全部成功，要么全部失败

2. 一致性：事务执行结束后，表的关联关系一定是正确的，不会发生数据错乱

3. 隔离性：事务之间相互隔离，数据不会相互影响，即使操作了同一个表，本质就是加了互斥锁，根据锁的粒度不同分为几个隔离级别

   • 事务的用户隔离级别：

     事务的使用者可以控制数据库工作在哪个级别下，就可以防止不同的隔离性问题

     • read uncommitted：可读取未提交，不做任何隔离，不可防止脏读、不可重复读和幻读
     • read committed：可读已提交，可防止脏读，不防止不可重复读和幻读
     • repeatable read：可重复读，可防止脏读和不可重复度，不防止幻读
     • Serializable：序列化，将数据库运行串行化，可防止所有问题

   • 补充

     • 脏读：也就是当数据库的一个事务A正在使用一个数据但还没有提交，另外一个事务B也访问到了这个数据，还使用了这个数据，这就会导致事务B使用了事务A没有提交之前的数据
     • 不可重复度：在一个事务A中多次操作一个数据，在这两次或多次访问这个数据的中间，事务B也操作此数据，并使其值发生了改变，这就导致同一个事务A在两次操作这个数据的时候值不一样，这就是不可重复读
     • 幻读：是指事务不独立执行产生的一种现象。事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。这样就会导致当A本来执行的结果包含B执行的结果，这两个本来是不相关的，对于A来说就相当于产生了“幻觉”

4. 持久性：事务执行成功后，数据将永久保存，无法恢复

使用方法

• 语法

  # 开启事务
  start transaction;
  sql...语句
  sql...语句
  rollback   # 回滚操作
  sql...语句
  commit;   # 提交事务，一旦提交就持久化了
  
  

• 案例

  create table account(
      name char(10),
      money float
  );
  insert into account values('一只穿云箭',1000);
  insert into account values('千军万马',1000);
  
  
  # 案例一：转账操作
  delimiter |  # 重载结束符
  start transaction;
  update account set money = money - 100 where name = "一只穿云箭";
  update account set money = money + 100 where name = "千军万马";
  commit;
  delimiter ;
  
  # 当一个事务执行过程中出现了异常，就需要进行回滚
  # 当事务中的语句全部执行成功了，就进行提交
  
  
  # 案例二：保存点
  delimiter |
  start transaction;
  update account set money = money - 100 where name = "一只穿云箭";
  savepoint a;
  update account set money = money - 100 where name = "一只穿云箭";
  savepoint b;
  update account set money = money - 100 where name = "一只穿云箭";
  savepoint c;
  
  select * from account
  
  rollback to 保存点名字
  
  delimiter ;
  

修改隔离级别

修改全局的  
 set global transaction isolation level read committed;
 或者:
 set @@tx_isolation = "asasasasas-read";
 修改局部
 set session transaction isolation level read committed;
 

 @@系统内置变量
 @表示用户自定义的变量

存储过程

存储过程就是任意的sql语句的组合，被放到某一个存储过程中，类似于一个函数，有参数，有函数体，存储过程可以包含任何sql语句，逻辑处理

三种数据处理方式

1. 应用程序只关注业务逻辑，所有与数据逻辑封装到mysql中

   优点：减少了网络传输，加快了速度

   缺点：增加了人力成本，沟通成本，降低了整体开发效率

2. 应用程序既要处理业务逻辑，还要自己编写sql语句

   优点：降低了人力成本，沟通成本

   缺点：网络传输增加了，sql语句的编写非常繁琐，易出错

3. 通过ORM框架，对象关系映射，可以自动生成sql语句并执行

   优点：不需要再编写sql语句，提升开发速度

   缺点：不够灵活，应用程序开发者和数据库完全隔离了，可能导致仅关注上层开发，而不清楚底层原理

使用存储过程

• 语法

# 创建一个存储过程
create procedure p_name(p_type p_name p_date_type)
begin
sql.......
end

'''
p_type      参数的类型
                1. in  输入
                2. out 输出
                3. inout 输入输出
p_name      参数的名字
p_date_type 参数的数据类型
'''

# 注意：out参数必须是一个变量，不能是值


# 调用
call p_name(p_name p_date_type);

# 删除
drop procedure p_name;

# 查看
show create procedure p_name;

# 查看全部db02库下的存储过程
select name from mysql.proc where db = "库名" and type = "procedure";

• 案例

  # 案例一
  delimiter |
  create procedure add1(in a float,in b float,out c float)
  begin
  set c = a + b;
  end|
  delimiter ;
  
  set @res = 0;
  call add1(12.4,3,@res);
  select @res;
  
  
  # 案例二
  delimiter |
  create procedure transfer2(in aid int,in bid int,in m float,out res int)
  begin 
  	declare exit handler for sqlexception
  	begin
  		# 异常处理代码
  		set res = 99;
  		rollback;
  	end;
  	
  	start transaction;
  	update account set money = money - m where id = aid;
  	update account set money = moneys + m where id = bid;
  	commit;
  	set res = 1;
  end|
  delimiter ;
  

函数

函数和python中一样

内置函数

• 时间相关

  

• 字符串相关

  

• 数字相关

  

• 其他函数

  

自定义函数

• 语法

# 创建函数
create function func_name(参数 类型)
函数体
returns 返回值的类
return 返回值 

# 查看函数创建语句
show create functiojn func_name;

# 查看所有函数状态
show function status;

# 查看db02库下的函数
select name from mysql.proc where db = "库名" and type = "function";

# 删除函数
drop function func_name;

• 案例

  delimiter | 
  create function add2(a int,b int)
  returns int
  return a + b|
  delimiter ;
  
  # 执行函数
  select 函数名(参数1,参数2……);
  

备份与恢复

备份

# 备份某个库里的表
mysqldump -u用户名 -p密码 数据库 表名1 表名2…… > 文件路径

# 备份库
mysqldump -u用户名 -p密码 --databases 数据库1 数据库2…… > 文件路径

# 备份所有数据
mysqldump -u用户名 -p密码 --all-databases > 文件路径

恢复

# 没有登录mysql
mysql < 文件路径

# 已经登陆mysql
source 文件路径

注意: 如果导出的sql中没有包含选择数据库的语句 需要手动加上 

流程控制

• 语法

  # number one
  if 条件 then
  语句;
  end if;
  
  # number two 
  # if elseif
  if 条件 then
  语句1;
  elseif 条件 then
  语句2;
  else 语句3;
  end if;
  

case语句

大体意思与Swtich一样的 你给我一个值 我对它进行选择 然后执行匹配上的语句

• 语法

  create procedure caseTest(in type int)
  begin
  case type 
  when 1  then select "type = 1";
  when 2  then select "type = 2";
  else select "type = other";
  end case;
  end
  

定义变量

• 语法：declare 变量名 类型 default 值

while循环

# 循环输出10次hello mysql
create procedure showHello()
begin 
declare i int default 0;
while  i < 10 do
select "hello mysql";
set i  = i + 1;
end while;
end

loop循环

没有条件 需要自己定义结束语句

# 输出十次hello mysql;
create procedure showloop()
begin 
declare i int default 0;
aloop: LOOP
select "hello loop";
set i = i + 1;
if i > 9 then leave aloop;
end if;
end loop aloop;
end

repeat循环

类似do while

# 输出10次hello repeat
create procedure showRepeat()
begin
declare i int default 0;
repeat
select "hello repeat";
set i = i + 1;
until i > 9
end repeat;
end

案例

delimiter | 
create procedure showjishu()
begin
declare i int default 0;
aloop: loop
set i = i + 1;
if i >= 101 then leave aloop; end if;
if i % 2 = 0 then iterate aloop; end if;
select i;
end loop aloop;
end|
delimiter ;