写点什么

MySQL 系列教程之(十二)扩展了解 MySQL 的存储过程,视图,触发器

用户头像
若尘
关注
发布于: 46 分钟前
MySQL 系列教程之(十二)扩展了解 MySQL 的存储过程,视图,触发器

存储过程

Mysql 储存过程是一组为了完成特定功能的 SQL 语句集,经过编译之后存储在数据库中,在需要时直接调用存储过程就像脚本语言中函数定义一样


-- 定义存储过程\d //create procedure p1()beginset @i=10;while @i<90 doinsert 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 方式来完成,但是会不会出现 limit 9000000,10,这样做也没毛病此时还可以借助存储过程和游标来实现,在存储过程中去定义并使用游标来获取指定的数据

MySQL 的触发器

提前定义好一个或一组操作,在指定的 sql 操作前或后来触发指定的 sql 执行举例:定义一个 update 语句,在向某个表中执行 insert 添加语句时来触发执行,就可以使用触发器触发器就是 javascript 中的事件一样


格式:1、触发器的定义:


 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;
复制代码


触发器 Demo 注意:如果触发器中 sql 有语法错误,那么整个操作都会报错


-- 创建一个删除的触发器,在users表中删除数据之前,往del_users表中添加一个数据
-- 1,复制当前的一个表结构create table del_users like users;
-- 2,创建 触发器 注意在创建删除触发器时,只能在删除之前才能获取到old(之前的)数据\d //create trigger deluser before delete on users for each rowbegininsert into del_users values(old.uid,old.uname,old.email,old.phone);end;//\d ;
-- 3 删除users表中的数据去实验
复制代码


用触发器来实现数据的统计


-- 1.创建一个表, users_count 里面有一个 num的字段 初始值为0或者是你当前users表中的count
-- 2,给users表创建一个触发器-- 当给users表中执行insert添加数据之后,就让users_count里面num+1,-- 当users表中的数据删除时,就让users_count里面num-1,-- 想要统计users表中的数据总数时,直接查看 users_count
复制代码

Mysql 中的视图

视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。


视图仅仅是用来查看存储在别处的数据的一种设施或方法。视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时,视图将返回改变过的数据。


因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图,可能会发现性能下降得很厉害。


视图的作用:1.重用 SQL 语句。2.简化复杂的 SQL 操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。3.使用表的组成部分而不是整个表。4.保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。5.更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。注意:视图不能索引,也不能有关联的触发器或默认值。


创建视图:mysql> create view v_t1 as select * from t1 where id>4 and id<11;Query OK, 0 rows affected (0.00 sec)
view视图的帮助信息:mysql> ? viewALTER VIEWCREATE VIEWDROP VIEW
查看当前库中所有的视图show tables; --可以查看到所有的表和视图show table status where comment='view'; --只查看当前库中的所有视图
删除视图v_t1:mysql> drop view v_t1;
复制代码

mysql 数据库备份与恢复

配置 mysql 的 bin log 日志在 windows 中找到 my.ini 配置文件,在 mysqld 的配置项配置 server_id=123456log_bin = mysql-binbinlog_format = ROW


ubuntu:sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf


server-id = 123456log_bin = /var/log/mysql/mysql-bin


配置完成后重启 mysql 服务进入 mysql 中


-- 重置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");
-- 查询select * from user;+----+-----------+-----+| id | name | age |+----+-----------+-----+| 1 | wangbo | 24 || 2 | guohui | 22 || 3 | zhangheng | 27 |+----+-----------+-----+

-- 现在进行数据备份mysqldump -uroot -p -B -F -R -x --master-data=2 ops >F:\mysql-5.7.27-winx64\bf\ops.sql
-----------------参数说明:-B:指定数据库-F:刷新日志-R:备份存储过程等-x:锁表--master-data:在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息


-- 再添加新的数据insert into user values(4,"liupeng","21"),(5,"xiaoda","31"),(6,"fuaiai","26");
-- 查询数据mysql> select * from user;+----+-----------+-----+| id | name | age |+----+-----------+-----+| 1 | wangbo | 24 || 2 | guohui | 22 || 3 | zhangheng | 27 || 4 | liupeng | 21 || 5 | xiaoda | 31 || 6 | fuaiai | 26 |+----+-----------+-----+


-- 此时误操作,删除了test数据库drop database ops;
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || python4 || sys || wx |+--------------------+


1,先讲binlog文件导出-- 将binlog文件导出sql文件,并vim编辑它删除其中的drop语句mysqlbinlog F:\mysql-5.7.27-winx64\data\mysql-bin.000002>>F:\mysql-5.7.27-winx64\bf\002bin.sql


2,-- 删除里面的drop语句vim 002bin.sql
3,-- 导入备份的数据文件,mysql -uroot -p < F:\mysql-5.7.27-winx64\bf\ops.sql
4,-- 再导入删除 drop语句后的 binlog日志文件mysql -uroot -p ops < F:\mysql-5.7.27-winx64\bf\002bin.sql
复制代码


最后,欢迎大家关注我的个人微信公众号 『小小猿若尘』,获取更多 IT 技术、干货知识、热点资讯。同时,我在公众号中分享了精心整理的一些视频资料(包括 Python 全栈教程、AI 教程、前端、数据库等),大家回复相应关键词即可获取网盘视频链接,感谢大家的关注😊



发布于: 46 分钟前阅读数: 4
用户头像

若尘

关注

还未添加个人签名 2021.01.11 加入

还未添加个人简介

评论

发布
暂无评论
MySQL 系列教程之(十二)扩展了解 MySQL 的存储过程,视图,触发器