Excel-mysql-存储过程、触发器和视图

发表于 | 更新于 | 分类于 | 阅读次数：

复制表结构:create table del_user like users
复制表数据:insert into users select * from del_users

####Mysql储存过程
`是一组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中(类似于函数)

1
2
3
4
5
6
7
8
9
10
11
-- 定义存储过程
\d //
create procedure p1()
begin
set @i=10;
while @i<90 do
insert into users values(null,concat('user:',@i),concat('user:',@i,'@qq.com'),concat('137013730',@i));
set @i=@i+1;
end while;
end;
//

执行储存：call p1
查看存储具体信息：show create procedure p1\G
删除触发器: drop procedure p1
应用场景:
分页显示,那么通常情况下是使用limit方式来完成,还可以借助存储过程和游标来实现,在存储过程中去定义并使用游标来获取指定的数据

####MySQL的触发器
含义:提前定义好一个或一组操作,在指定的sql操作前或后来触发指定的sql执行

1
2
3
4
5
6
7
8
9
10
11
12
13
定义：
 CREATE TRIGGER trigger_name trigger_time trigger_event
   ON tbl_name FOR EACH ROW trigger_stmt
说明：
# trigger_name：触发器名称
# trigger_time:触发时间，可取值：BEFORE或AFTER
# trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
# tb1_name：指定在哪个表上
# trigger_stmt：触发处理SQL语句。
-- 查看所有的 触发器
show triggers\G;
-- 删除触发器
drop trigger trigger_name;

1
2
3
4
5
6
7
8
9
触发器举例:
create table del_users like users;
\d //
create trigger deluser before delete on users for each row
begin
insert into del_users values(old.uid,old.uname,old.email,old.phone);
end;
//
\d ;

触发器注意事项:
1.在触发器中只想语句,要确保sql语句能正确执行
2.在创建一个insert类型触发器时,在触发器中无法使用add来获取原来的数据

####mysql视图
定义:视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
特点:
1.视图仅仅是用来查看存储在别处的数据的一种设施
2.视图本身不包含数据
3.在添加或更改这些表中的数据时，视图将返回改变过的数据
视图的作用:
1.重用SQL语句
2.简化复杂的SQL操作
3.使用表的组成部分而不是整个表
4.保护数据
5.更改数据格式和表示

1
2
3
4
5
6
7
8
视图操作:
创建视图
mysql> create view v_t1 as select * from t1 where id>4 and id<11;
查看当前库中所有的视图
show tables; --可以查看到所有的表和视图
show table status where comment='view'; --只查看当前库中的所有视图
删除视图
drop view v_t1;

####mysql数据库备份以及恢复
1.配置mysql的bin log日志
在windows中找到 my.ini 配置文件,在mysqld的配置项配置
server_id=123456
log_bin = mysql-bin
binlog_format = ROW
2.配置完成后重启mysql服务
3.进入mysql中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
	重置binlog日志 reset master;
	查看当前的所有日志 show binary logs
	创建数据库 create database ops
	选择并打开库 use ops;
 	创建表
create table user(
    id int not null auto_increment,
    name char(20) not null,
    age int not null,
    primary key(id)
)engine=InnoDB;
    添加数据
insert into user values(1,"wangbo","24"),(2,"guohui","22"),(3,"zhangheng","27");
	数据备份
mysqldump -uroot -p -B -F -R -x --master-data=2 ops >F:\mysql-5.7.25\bf\ops.sql
    再添加新的数据
insert into user values(4,"liupeng","21"),(5,"xiaoda","31"),(6,"fuaiai","26");
	此时误操作，删除了test数据库
drop database ops;
	binlog文件导出,将binlog文件导出sql文件，删除其中的drop语句
mysqlbinlog -d ops F:\mysql-5.7.25\data\mysql-bin.000002> F:\mysql-5.7.25\bf\002bin.sql
	导入备份的数据文件,
mysql -uroot -p < F:\mysql-5.7.25\bf\ops.sql 
	再导入删除 drop语句后的 binlog日志文件
mysql -uroot -p ops < F:\mysql-5.7.25\bf\002bin.sql