一、视图
create VIEW 视图名称 AS sql语句
- 例如
create view login_view as select * from login
视图是虚拟的表
1.优点:
- 简化sql
- 安全(不暴露字段)
- 降低耦合
2.缺点
- 不能提高性能
- 不适合分布式和大数据的项目
3. 适合oa和erp
4. 视图的curd(真实的数据表数据会跟着变动)
insert into login_view (id,name) VALUES(1,'uuu');
insert into login_view (id,name) VALUES(2,'1212');
5.限制加入视图的数据(语句加入with check option)
create view login_view2 as select * from login where id > 2 with check option;
insert into login_view2 (id,name) values (0,'uuu');
6.不能新增或修改的视图
- 有聚合函数
- 包含子查询
- JOIN查询
- 常量视图(不常用)
二、触发器
触发器类似于开发使用的框架的事件
create trigger [触发器名称] [触发器执行事件] ON [表] for each row [函数或者某个动作]
- 触发器的执行事件:before,after
- 执行的动作点: insert, update, delete
- 函数:begin end;
- 动作: update, insert
create trigger
login_trigger_before
before insert on
login
for each row
begin
update test_trigger set count = count+1 where id = 1;
end;
一般使用程序逻辑代替,不在sql语句做这种逻辑
三、存储过程
- 可以理解为一段sql语句的集合(方法),实现业务逻辑;是事先编译好的并存储在数据库中的(所以效率会高一些);Mysql的函数
create procedure 存储过程名称(参数列表)
begin
declare 游标名 cursor for 查询语句；
declare [变量名称][类型]default;
open游标名 -- 打开
fetch 游标名 into xxx,
业务处理
代码;业务逻辑区域
end;
调用: call 存储过程名称(参数列表)
删除: drop procedure 存储过程名称
create procedure login_procedure(in id int, out ret varchar(2))
begin
declare id int default 0;
declare blag int default 1;
declare name varchar(10) default "";
declare shop_cursor cursor for select shop_name,uid from shop;
select shop_name into ret from shop where uid = id;
-- 异常处理:注意游标在读取数据的时候,不断读取
declare continue handler for not found set blag = 0;
open shop_cursor;
read_loop:loop
fetch shop_cursor into name,id;
if blag = 0 then
leave read_loop;
end if;
if id = 63 then
set ret = name;
end if;
end loop read_loop;
set ret = "ttt";
set ret = y;
end;
call login_procedure(87,@ret);
select @ret;
- 参数类型:
- IN : 只是给存储过程传递参数
- OUT : 表示只是用来输出(可以直接set设置)
- INOUT : 既可以传递也可以输出
- 数据类型
- 数值类型:int,float,double,decimal
- 时间类型:timestamp,date,datetime
- 字符串:char, varchar, text
- 流程控制语句
- if 的语句格式为:
if 条件表达式 then 语句
[elseif 条件表达式 then 语句] ...
[else 语句]
end if
- case的语法格式
case 表达式
when 值 then 语句
when 值 then 语句
...
[else 语句]
end case
case
when 表达式 then 语句
when 表达式 then 语句
...
[else 语句]
end case
- loop循环语法格式
[标号:]loop
循环语句
end loop [标号]
- 声明语句结束符,可以自定义:
DELIMITER [符合]
delimiter $$
$$
- 优点:
- 执行速度快,每个sql语句都需要编译再运行,但是存储过程都是预先编译好的,可直接运行(并不能显著提高速度)
- 减少网络流量.我们传输一个存储过程比我们传输大量的SQL语句的开销小得多.
- 提高系统安全性.因为存储过程可以使用权限控制,而且参数化的存储过程可以有效地防止SQL注入.
- 耦合性降低.当我们的表结构发生了调整之后,我们可以修改相应的存储过程,应用程序需要改动的地方就小了.
- 缺点(最大的问题就是移植性差)
- 移植性差,因为存储过程是和数据库绑定的,如果我们要更换数据库之类的操作,可能很多地方需要改动.
- 修改不方便.因为对于存储过程而言,我们并不能特别有效的调试,它的一些bug可能发现的更.
- 优势不明显和赘余功能.对于小型web应用来说,如果我们使用语句缓存,发现编译SQL的开销并不大,存储过程却需要检查权限一类的开销,这些赘余功能也会在一定程度上拖累性能.
- 存储过程适用的应用:OA,ERP
四、物化视图(mysql中并不存在)
- 如果数据有几千万的时候
select count(*),sum(),avg() from table_name;会很慢,可以用辅助索引或者物化视图处理. - 创建一个实体的表 物化视图表
记录:查询的结果
查询 物化视图表==>聚合结果集
数据:实时性一致性
对数据实时性要求高:触发器 INSERT UPDATE DELETE 的时候提供触发器的方法,通过触发器对物化视图表进行修改
对数据实时性要求低:定时对存储过程
