MySQL之触发器,事务,存储,备份
上节回顾
查询语句
select distinct * | 字段 from tabe
where 逻辑运算符 成员运算符 比较运算符 like between exists
group by 分组 为了统计 个数count max min avg sum
having 分组后进行过滤
order by 用于排序,默认升序asc desc
limit 第一个指定起始位置 第二个每一页的个数
子查询
子查询作为外层查询的条件
同一个需求 可以用子查询也可以用内连接
连表查询
笛卡尔积,
重复数据 显示指定字段
混乱 限制条件 (where 从表的外键 = 主表的主键)
内连接
本质上就是笛卡尔积查询.
仅显示匹配成功的记录
外连接
没有匹配也会显示
左外连接 左边要全部显示
右外连接 右边要全部显示
全外连接查询 无论是否匹配两边的记录都要全部显示 可以通过union 将左右合并 去除重复
union all 合并 但是不去除重复
必须保证字段个数相同才能合并
高级部分
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
事务的用户隔离级别:
数据库使用者可以控制数据库工作在哪个级别下,就可与防止不同的隔离性问题
read uncommitted --不做任何隔离,可能脏读,幻读
read committed----可以防止脏读,不能防止不可重复读,和幻读,
Repeatable read --可以防止脏读,不可重复读,不能防止幻读
Serializable--数据库运行在串行化实现,所有问题都没有,就是性能低
脏读: 没有提交进行读数据
幻读 是因为 别人在执行 插入 和删除
不可重复度 是因为 别人在做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 这样的符号 需要找其他符号来代替
