MySQL视图,函数,触发器,存储过程
1. 视图
视图是一个虚拟表,它的本质是根据sql语句获取动态的数据集,并为其命名,用户使用时只需使用【名称】即可获取结果集,可以将该结果集当做表来使用。
使用视图我们可以把查询过程中的临时表摘出来,用视图去实现,这样以后再想操作该临时表的数据时就无需重写复杂的sql了,直接去视图中查找即可,
但视图有明显地效率问题,并且视图是存放在数据库中的,如果我们程序中使用的sql过分依赖数据库中的视图,即强耦合,那就意味着扩展sql极为不便,
因此不推荐使用. 而且工作中一般不方便,因为是虚拟表 不方便共用,如果需要修改,可能设计到与dba的沟通,很麻烦
1 -- 使用视图 2 select .. from v1 3 select asd from v1 4 -- 某个查询语句设置别名,日后方便使用 5 6 - 创建 7 create view 视图名称 as sql 8 9 ps: 虚拟的,临时表 无法插入操作 10 11 - 修改 12 alter view 视图名称 as sql 13 14 - 删除 15 drop view 视图名称;
2. 触发器
定制用户对表进行【增、删、改】操作时前后的行为,注意:没有查询
工作中一般也很少用到,因为自己在代码中就能设计操作前后的行为
insert into tb (....) delimiter // -- 修改结束标记 create trigger t1 before insert on student for each row -- 创建insert操作前的触发器 begin -- 触发器具体内容 insert into teacher(tname) values(new.sname); insert into teacher(tname) values(new.sname); insert into teacher(tname) values(new.sname); insert into teacher(tname) values(new.sname); end // delimiter ; -- 恢复默认的语句结束标记 ------------------------------------------------ insert into student(gender,class_id,sname) values('女',1,'涛'),('女',1,'根'); -- new,代指新数据 可以在触发器中点语法使用 -- old,代指老数据 删除的那一行记录被old引用
3.函数
因为在sql语句执行中调用函数会比较耗时,而且对索引的那一列使用了函数,则无法命中索引了。
所以工作中对响应速度要求高,一般不会不使用函数处理结果集。而是在架构级别或者程序级别处理结果集。
内置的函数很多,详情参看官方文档
-- 内置函数: 执行函数 select curdate(); blog id title ctime 1 asdf 2019-11 2 asdf 2019-11 3 asdf 2019-10 4 asdf 2019-10 select ctime,count(1) from blog group ctime select date_format(ctime, "%y-%m"),count(1) from blog group date_format(ctime, "%y-%m") 2019-11 2 2019-10 2 date_format 时间格式化函数,较常用 -- 自定义函数(必须有返回值): delimiter \\ create function f1( i1 int, i2 int) returns int begin declare num int default 0; set num = i1 + i2; return(num); end \\ delimiter ; select f1(1,100);
4. 存储过程
包含了一系列可执行的sql语句,存储过程存放于mysql中,通过调用它的名字可以执行其内部的一堆sql,可以让程序与sql解耦合,且执行通过一个名字减少数据传输。mysql 5.5版本以后才有的功能
开发岗位一般也比较少使用。主要会是dba使用。
用mysql的三种方式:
方式一:
mysql: 存储过程
程序:调用存储过程
方式二:
mysql:。。
程序:sql语句
方式三:
mysql:。。
程序:类和对象(sql语句)
mysql中代码属于强类型语言, 变量需要先 声明 变量名 和 变量类型.
delimiter // create procedure p2( in n1 int, in n2 int ) begin ----- 获取大于传参数字的id行 select * from student where sid > n1; end // delimiter ; ---------------- 命令行 call p2(12,2) --------------- pymsql cursor.callproc('p2',(12,2))
delimiter // create procedure p3( in n1 int, inout n2 int ) begin set n2 = 123123; select * from student where sid > n1; end // delimiter ; ------------------- set @v1 = 10; call p2(12,@v1) select @v1; set @_p3_0 = 12 ser @_p3_1 = 2 call p3(@_p3_0,@_p3_1) select @_p3_0,@_p3_1 ------------------------ pymysql cursor.callproc('p3',(12,2)) r1 = cursor.fetchall() print(r1) cursor.execute('select @_p3_0,@_p3_1') # @_p3_0 是底层创建好的名字 r2 = cursor.fetchall() # 去除out值 print(r2)
为什么有结果集又有out伪造的返回值?
delimiter // create procedure p3( in n1 int, out n2 int -- 用于标识存储过程的执行结果 一般用 tinyint 1,2等来表示相应的执行结果,方便程序获取后知道执行结果 ) begin insert into vv(..) insert into vv(..) insert into vv(..) insert into vv(..) insert into vv(..) insert into vv(..) end // delimiter ;
delimiter // create procedure p4( out status int ) begin -- 伪代码描述 1. 声明如果出现异常则执行{ set status = 1; rollback; } 开始事务 -- 由秦兵账户减去100 -- 方少伟账户加90 -- 张根账户加10 commit; 结束 set status = 2; end // delimiter ; =============================== delimiter \\ create procedure p5( out p_return_code tinyint ) begin declare exit handler for sqlexception begin -- error set p_return_code = 1; rollback; end; start transaction; delete from tb1; insert into tb2(name)values('seven'); commit; -- success set p_return_code = 2; end\\ delimiter ;
delimiter // create procedure p6() begin declare row_id int; -- 自定义变量1 declare row_num int; -- 自定义变量2 declare done int default false; -- 默认为false 表述循环未执行完 declare temp int; -- 声明游标 declare my_cursor cursor for select id,num from a; declare continue handler for not found set done = true; -- 开始循环 open my_cursor; xxoo: loop fetch my_cursor into row_id,row_num; if done then -- 需要自己判断是否循环结束 leave xxoo; -- 结束循环 end if; set temp = row_id + row_num; insert into b(number) values(temp); end loop xxoo; close my_cursor; end // delimter ;
游标性能比较差,一般很少用,使用场景是:针对每一行都需要专门的处理计算的时候可能会用到,但是一般update+ 循环也能解决 如:update b set num=id+num;
delimiter // create procedure p7( in tpl varchar(255), in arg int ) begin set @xo = arg; prepare prod from 'select * from student where sid > ?'; -- 1. 预检测某个东西 sql语句合法性 execute prod using @xo; -- 2. sql =格式化 tpl + arg deallocate prepare prod; -- 3. 执行sql语句 end // delimter ; --------------------------- call p7("select * from tb where id > ?",9)