MySQL--高级

视图

什么是视图

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

为什么要是用视图

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

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

所以视图有两大作用：

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

使用方法

创建视图语法
```
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 | 三班    |
+------+------+--------+

注意：

当原表发生变化时，视图也会进行相应的变化
当修改视图时，原表也会进行变化，所以一般不要对视图进行修改

触发器

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

这其中有两个关键因素：

时间点
- 事件发生前 before
- 事件发生后 after
事件
- 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执行失败将导致数据错乱!

例如转账操作,

从原有账户减去转账金额
给目标账户加上转账金额

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

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

事物的特点

原子性：指的是这个事务中的sql语句是一个整体，不能进行拆分，要么全部成功，要么全部失败
一致性：事务执行结束后，表的关联关系一定是正确的，不会发生数据错乱
隔离性：事务之间相互隔离，数据不会相互影响，即使操作了同一个表，本质就是加了互斥锁，根据锁的粒度不同分为几个隔离级别
- 事务的用户隔离级别：
  
  事务的使用者可以控制数据库工作在哪个级别下，就可以防止不同的隔离性问题
  - read uncommitted：可读取未提交，不做任何隔离，不可防止脏读、不可重复读和幻读
  - read committed：可读已提交，可防止脏读，不防止不可重复读和幻读
  - repeatable read：可重复读，可防止脏读和不可重复度，不防止幻读
  - Serializable：序列化，将数据库运行串行化，可防止所有问题
- 补充
  - 脏读：也就是当数据库的一个事务A正在使用一个数据但还没有提交，另外一个事务B也访问到了这个数据，还使用了这个数据，这就会导致事务B使用了事务A没有提交之前的数据
  - 不可重复度：在一个事务A中多次操作一个数据，在这两次或多次访问这个数据的中间，事务B也操作此数据，并使其值发生了改变，这就导致同一个事务A在两次操作这个数据的时候值不一样，这就是不可重复读
  - 幻读：是指事务不独立执行产生的一种现象。事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。这样就会导致当A本来执行的结果包含B执行的结果，这两个本来是不相关的，对于A来说就相当于产生了“幻觉”
持久性：事务执行成功后，数据将永久保存，无法恢复

使用方法

语法

# 开启事务
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语句，逻辑处理

三种数据处理方式

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

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

缺点：增加了人力成本，沟通成本，降低了整体开发效率
应用程序既要处理业务逻辑，还要自己编写sql语句

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

缺点：网络传输增加了，sql语句的编写非常繁琐，易出错
通过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 ;

相关阅读:
Linux 升级make (gmake)
C库函数-calloc（）
redis若干命令中文翻译
 centos7 安装xinetd,telnet
vim 显示行号
 重启redis
TS 过滤 .meta文件
 TS 判断为空
 TS 聚合查询读取MongoDB
windows 编译libuv库.txt
原文地址：https://www.cnblogs.com/Hades123/p/11215673.html

MySQL--高级

视图

什么是视图

为什么要是用视图

使用方法

案例

注意：

触发器

使用方法

案例

事务 *****

什么是事务

为什么需要事务

事物的特点

使用方法

修改隔离级别

存储过程

三种数据处理方式

使用存储过程

函数

内置函数

自定义函数

备份与恢复

备份

恢复

流程控制

case语句

定义变量

while循环

loop循环

repeat循环

案例

事务 `*****`