oa 触发器导出流程html,数据库视图，触发器，事务，存储过程，函数，备份与恢复...

高级部分

1.视图

什么是视图,

​ 本质上是一个虚拟的表,即看得见但是不实际存在

​ 为什么需要虚拟表????

使用场景

场景1:

​ 我们希望某些查询语句只能查看到某个表中的一部分记录 ,就可以使用视图

场景2:

​ 简化sql语句的编写

使用方法:

语法:

create [or replace] view view_name as 查询语句;

or replace 如果视图已经存在了 就替换里面的查询语句;

# 修改视图

alter view view_name as 新的语句;

# 删除视图

drop view view_name;

# 查看

desc view_name;

show create view view_name;

# 限制可以查看的记录

create table salarys(id int,name char(10),money float);

insert into salarys values(1,"张三丰",50000),(2,"张无忌",40000);

# 创建视图 限制只能查看张无忌的工资

create view zwj_view as select *from salarys where 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;

注意: 修改视图 也会引起原表的变化,我们不要这么做,视图仅用于查询

2.触发器

​ 触发器,是一段与某个表相关的sql语句,会在某个时间点,满足某个条件后自动触发执行

​ 其中两个关键因素:

​ 时间点

​ 事件发生前 before | 事件发生后 after

​ 事件

​ update delete insert

​ 再触发器自动的包含两个对象

​ old update，delete中可用

​ new update,insert 中可用

​ 用来干什么:

​ 可以用于:当表的数据被修改时,自动记录一些数据,执行一些sql语句

​ 语法:

create trigger t_name t_time t_event on table_name for each row

begin

#sql语句。。。。。：

end

案例：

#准备数据

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') #0代表执行失败

);

#错误日志表

CREATE TABLE errlog (

id INT PRIMARY KEY auto_increment,

err_cmd CHAR (64),

err_time datetime

);

#需求： 当插入cmd表 的时候 如果执行状态时失败的 那么将信息插入到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.sub_time);

end if;

end|

# 在还原之前的结束符

delimiter ;

# 创建一个触发器 叫cmd_insert

# 触发器会在 插入数据到cmd表后执行

# 当插入的记录的success为no时 自动插入记录到errlog中

# 错误原因 遇到分号自动提交了 , 需要重定义 行结束符

delimiter |

# 删除触发器

drop trigger cmd_insert;

#查看 所有触发器

show triggers;

# 查看某个触发器的语句

show create trigger t_name;

3.事务 非常重要

什么是事务

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

事务的特点:

​ 原子性,指的是这个事务中的sql语句是一个整体,不能拆分,要么都执行,要么全都失败

​ 一致性, 事务执行结束后,表的关联关系一定是正确的,不会发送数据错乱

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

​ 持久性,事务执行成功后数据将永久保存,无法恢复

事务的应用场景:

​ 转账操作 1.把转出账户的前扣掉,2.在给转入账户的余额 做增加操作

​ money(name,money)

​ update money set money = money - 100 where name = "李寻欢";

​ update money set money = money + 100 where name = "步惊云";

注意: 在官方提供的cmd的mysql客户端下 事务是默认就开启,会将一条sql语句作为一个事务 会自动提交

语法:

#开启事务

start transaction

#sql 语句......

#sql 语句......

rollback #回滚操作 即撤销没有提交之前的所有操作

#sql 语句......

commit #提交事务 一旦提交就持久化了

CREATE TABLE `account` (

`name` char(10),

`money` float

);

start transaction;

update account set money = money - 100 where name = "一只穿云箭";

update account set money = money + 100 where name = "千军万马";

commit;

# 何时应该回滚 当一个事务执行过程中出现了异常时

# 何时提交 当事务中所有语句都执行成功时

# 保存点 可以在rollback指定回滚到某一个savepoint ,也就是回滚一部分

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 保存点名称

read committed

幻读 是因为 别人在执行 插入 和删除

不可重复度 是因为 比人在做update

修改隔离级别:

修改全局的

set global transaction isolation level read committed;

或者:

set @@tx_isolation = "asasasasas-read";

修改局部

set session transaction isolation level read committed;

@@系统内置变量

@表示用户自定义的变量

4.存储过程 优先掌握

什么是存储过程 ,

​ 是任意的sql语句的组合,被放到某一个存储过程中,类似于一个函数,有一个函数,有参数,还是函数体

用来干什么:

​ 其中可以包含任何的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 参数的类型 in输入 out输出 inout即可输出也可输入

p_name 参数的名字

p_data_type 参数的数据类型 如 int float

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

#案例:

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(100,10,@res);

# 删除

drop procedure 名称;

# 查看

show create procedure 名称;

# 查看全部 db02库下的所有过程

select name from mysql.proc where db = 'day41' and type = 'PROCEDURE';

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 ;

5.函数

自定义函数

create function func_name(参数 类型)

函数体

returns 返回值的类

return 返回值

delimiter |

create function add2(a int,b int)

returns int

return a + b|

delimiter ;

#查看创建语句

show create function name;

#查看所有函数的状态

show function status;

#查看某个库下所有函数

select name from mysql.proc where db = "库名称" and type = "FUNCTION";

# 删除

drop function name;

6.备份与恢复

# 备份

mysqldump.exe

mysqldump -u用户名 -p密码 数据库 表名1 表名2 .... > 文件路径....

# 注意 第一个表示数据库 后面全都是表名

mysqldump -uroot -p day41 student >

#备份多个数据库

mysqldump -uroot -p111 --databases day41 day40 > x3x.sql

#指定 --databases 后导出的文件包含 创建库的语句 而上面的方式不包含

#备份所有数据

mysqldump -uroot -p111 --all-databases > all.sql

#自动备份

linux crontab 指令可以定时执行某一个指令

# 恢复数据:

没有登录mysql

mysql < 文件的路径

已经登录了MySQL

source 文件路径

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

7.流程控制

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 ;

正则匹配

语法:

select *from table where 字段名 regexp "表达式!";

create table info(name char(20));

insert into info values("jack sbaro"),("jack rose"),("jerry sbaro"),("sbaro jerry"),("jerry");

# 注意: 不能使用类似 \w 这样的符号 需要找其他符号来代替