一:视图
01:介绍
视图是一个虚拟表(非真实存在),是跑到内存中的表,真实表是硬盘上的表,怎么就得到了虚拟表,就是你查询的结果,只不过之
前我们查询出来的虚拟表,从内存中取出来显示在屏幕上,内存中就没有了这些表的数据,但是下次我要是想用这个虚拟表呢,没办法,
只能重新查一次,每次都要重新查。其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结
果集,可以将该结果集当做表来使用。如果我们想查询一些有关联的表,比如我们前面的老师学生班级什么的表,我可能需要几个表联合
查询的结果,但是这几张表在硬盘上是单独存的,所以我们需要通过查询的手段,将这些表在内存中拼成一个虚拟表,然后是不是我们再
基于虚拟表在进行进一步的查询,然后我们如果以后想重新再查一下这些关系数据,还需要对硬盘上这些表进行再次的重新加载到内容,
联合成虚拟表,然后再筛选等等的操作,意味着咱们每次都在写重复的sql语句,那有没有好的方法啊,其实很简单,我们把重复用的这些
sql逻辑封装起来,然后下次使用的时候直接调用这个封装好的操作就可以了,这个封装起来的操作就类似我们下面要说的视图为什么要用
视图:使用视图我们可以把查询过程中的临时表摘出来,保存下来,用视图去实现,这样以后再想操作该临时表的数据时就无需重写复杂
的sql了,直接去视图中查找即可,但视图有明显地效率问题,并且视图是存放在数据库中的,如果我们程序中使用的sql过分依赖数据库中
的视图,即强耦合,那就意味着扩展sql极为不便,因此并不推荐使用
二:触发器:(注意:触发器不能进行 查操作 )
触发器不是由用户去触发的,而是程序事先设定好的,由程序自动触发的。触发器里面可以进行 增/删/改(触发器无法由用户直接调用,而由对表的【增/删/改】操作被动引发的。)
01:创建触发器:
001:插入数据前触发触发器
create trigger tri_before_insert_表1 before insert on 表1 for each row begin ......#begin和end 里面写触发器要做的事情,而且要注意代码缩进,给触发器起名字的时候, 最好像tri_befores_insert_表1 有表示意义,看名字就知道是给哪个表设置的触发器。 end
002:插入数据后触发触发器
create trigger tri_after_insert_表1 after insert on 表1 for each row begin ...#需要触发器做的事情 end
003:删除数据前触发触发器
create trigger tri_before_delete_表1 before delete on 表1 for each row begin ......#需要触发器做的事情 end
004:更新数据前触发触发器
create trigger tri_before_update_表1 after update on 表1 for each row begin ...触发器需要做的事情 end
005:更新数据后触发触发器
create trigger tri_after_update_表1 after update on 表1 for each row begin .... 触发器需要做的事情 end
02:使用案例: #准备表
CREATE TABLE cmd ( #这是一张指令信息表,你在系统里面执行的任何的系统命令都在表里面写一条记录 id INT PRIMARY KEY auto_increment, #id USER CHAR (32), #用户 priv CHAR (10), #权限 cmd CHAR (64), #指令 sub_time datetime, #提交时间 success enum ('yes', 'no') #是否执行成功,0代表执行失败 ); CREATE TABLE errlog ( #指令执行错误的信息统计表,专门提取上面cmd表的错误记录 id INT PRIMARY KEY auto_increment, #id err_cmd CHAR (64), #错误指令 err_time datetime #错误命令的提交时间 );
#现在的需求是:不管正确或者错误的cmd,都需要往cmd表里面插入,然后,如果是错误的记录,还需要往errlog表里面插入一条记录 #若果没有触发器,我们会怎么实现,我们完全可以通过咱们的应用程序来做,根据cmd表里面的success这个字段是哪个值(yes成功,no表示失败), 在给cmd插入记录的时候,判断一下这个值是yes或者no,来判断一下成功或者失败,如果失败了,直接给errlog来插入一条记录 #但是mysql说,你的应用程序可以省事儿了,你只需要往cmd表里面插入数据就行了,没必要你自己来判断了,可以使用触发器来实现,可以判断你插 入的这条记录的success这个字段对应的值,然后自动来触发触发器,进行errlog表的数据插入
#创建触发器 delimiter // (或者写$$,其他符号也行,但是不要写mysql不能认识的,知道一下就行了),delimiter 是告诉mysql,遇到这句话的时候,
就将sql语句的结束符分号改成delimiter后面的// CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW #在你cmd表插入一条记录之后触发的。 BEGIN #每次给cmd插入一条记录的时候,都会被mysql封装成一个对象,叫做NEW,里面的字段都是这个NEW的属性 IF NEW.success = 'no' THEN #mysql里面是可以写这种判断的,等值判断只有一个等号,然后写then INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号,并且注意,我们必须用delimiter来包裹,
不然,mysql一看到分号,就认为你的sql结束了,
所以会报错 END IF ; #然后写end if,必须加分号 END// #只有遇到//这个完成的sql才算结束 delimiter ; #然后将mysql的结束符改回为分号
#往表cmd中插入记录,触发触发器,根据IF的条件决定是否插入错误日志
mysql> insert into cmd( -> user, -> priv, -> cmd, -> sub_time, -> success -> ) -> values -> ("1","0755","ls-1 /etc",now(),"yes"), -> ("2","0755","cat /etc/passwd",now(),"no"), -> ("3","0755","useradd xxx",now(),"no"), -> ("4","0755","ps aux",now(),"yes"); Query OK, 4 rows affected (0.16 sec) Records: 4 Duplicates: 0 Warnings: 0
查询错误日志,发现有两条
mysql> select * from errlog; +----+-----------------+---------------------+ | id | err_cmd | err_time | +----+-----------------+---------------------+ | 1 | cat /etc/passwd | 2018-12-11 16:54:42 | | 2 | useradd xxx | 2018-12-11 16:54:42 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec)
03:删除触发器:
drop trigger tri_after_insert_cmd(触发器名字)
三:事件
事物的四大特性:(原子性,一致性,持久性,隔离性)
01:原子性(Atomicity):事务开始执行,要么成功的话一起成功,要么失败的话全部都失败。只要有一个失败则全部都失败。
(正确版的原子性)事务的原子性例子:
mysql> create table user( -> id int primary key auto_increment, -> name char(32), -> balance int -> ); Query OK, 0 rows affected (0.19 sec) mysql> insert into user(name,balance) -> values -> ("wsb",1000), -> ("chao",1000), -> ("ysb",1000); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 #原子操作 mysql> start transaction; #开启事务 Query OK, 0 rows affected (0.00 sec) mysql> update user set balance=910 where name="wsb"; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1090 where name="ysb"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #这些操作都只是在内存中操作,还未写入文件,先看下他们的数据; mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 900 | | 2 | chao | 1010 | | 3 | ysb | 1090 | +----+------+---------+ 3 rows in set (0.00 sec) 然后退出(quit) 再进入查看,user表的信息还是未被改变,说明还未被写入磁盘中()还未用commit mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 1000 | | 2 | chao | 1000 | | 3 | ysb | 1000 | +----+------+---------+ 3 rows in set (0.00 sec) 02:commit 写入磁盘 过程: create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('chao',1000), ('ysb',1000); #原子操作 start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='chao'; #中介拿走10元 update user set balance=1090 where name='ysb'; #卖家拿到90元 commit; #只要不进行commit操作,就没有保存下来,没有刷到硬盘上 现在用commit命令执行写入磁盘中。(现在需要把前面的表重新创建,所以还是先drop再新建表吧) 显示commit后的结果:这个是从磁盘读取的数据,说明已经将数据写入磁盘中了。 mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 900 | | 2 | chao | 1010 | | 3 | ysb | 1090 | +----+------+---------+ 3 rows in set (0.00 sec) 错误版的需要回滚的的原子性(例子说明): create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('chao',1000), ('ysb',1000); #开启事务: mysql> start transaction; #开启事务 Query OK, 0 rows affected (0.00 sec) mysql> update user set balance=900 where name='wsb'; #买支付100元 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1010 where name='chao'; #中介拿走10元 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> uppdate user11 set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到,并没有user11这个用户信息 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL serve sb'' at line 1 查看事务出现异常后各用户的信息:(可以看到前面两项执行了的语句,数据还是改了并未出现回滚,因此我们需要自行设置回滚) mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 900 | | 2 | chao | 1010 | | 3 | ysb | 1000 | +----+------+---------+ 3 rows in set (0.00 sec) 上面的只要写了执行 commit; 操作,数据就写入到磁盘中了,再进行回滚rollback就不行了,就算quit出来再进去库里面进行查询,结果还是 修改后的数据,就算地三条语句没有执行,前面两条数据执行后已经被commit 写入磁盘中了。 就像这样: mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 900 | #数据正常被改了,因为执行了commit; | 2 | chao | 1010 | #数据正常被改了,因为执行了commit; | 3 | ysb | 1000 | #数据没有被改(报错语句),但是也被写入磁盘中了。 +----+------+---------+ 3 rows in set (0.00 sec)
03:需要解决问题:(就是我需要判断我在修改数据的时候判断语句的正确性,如果全部语句正确则执行commit; 如果有其中一条不正确,则所有的
执行语句都不执行成功,并将数据回滚到初始化。
001:出现异常就立即回滚:
start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='chao'; #中介拿走10元 uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到 rollback; #如果上面三个sql语句出现了异常,就直接rollback,数据就直接回到原来的状态了。但是执行了commit之后,rollback这个操作就没法回滚了 #我们要做的是检测这几个sql语句是否异常,没有异常直接commit,有异常就rollback,但是现在单纯的只是开启了事务,但是还没有说如何检测异常,我们先来一个存储过程来捕获异常, commit;
002:通过存储过程来捕获异常回滚
#通过存储过程来捕获异常:(写存储过程的是,注意每一行都不要缩进!!! 02:一致性(Consistency):事务开始前和结束后,数据库的完整性没有被破坏掉. 03:持久性(Isolation):事务完成后,事务对数据库的所有更新将被保存在本地不能回滚。 04:隔离型(Durability):同一时间只能允许一个事务对请求数据,不同的事务之间不会彼此干扰
四:存储过程
1:什么叫存储过程:存储过程包括了一系列的sql语句,存储过程放入mysql中,通过调用它的名字可以执行其内部的
一系列的sql。(存储过程其实就是一堆的sql的集合体) 优点: 01:用于替代程序写的sql语句,实现了程序与sql解耦 02:基于网络传输,传输别名的数据量小,而直接传sql语句的话数据量大 缺点: 01:扩展不方便
2:程序与数据库结合使用的三种方式:
01:mysql:存储过程 程序: 调用存储过程 02:mysql: 程序:纯sql语句 03:mysql: 程序类和对象,即orm(本质还是纯sql语句)