欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL视图,函数,触发器,存储过程

程序员文章站 2023-11-28 09:24:04
1. 视图 视图是一个虚拟表,它的本质是根据SQL语句获取动态的数据集,并为其命名,用户使用时只需使用【名称】即可获取结果集,可以将该结果集当做表来使用。 使用视图我们可以把查询过程中的临时表摘出来,用视图去实现,这样以后再想操作该临时表的数据时就无需重写复杂的sql了,直接去视图中查找即可, 但视 ......

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中代码属于强类型语言, 变量需要先 声明 变量名 和 变量类型.

MySQL视图,函数,触发器,存储过程1.简单示例

 

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))
2.传参数(in)
MySQL视图,函数,触发器,存储过程
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)
3.参数 out

为什么有结果集又有out伪造的返回值?

MySQL视图,函数,触发器,存储过程
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 ;
view code
MySQL视图,函数,触发器,存储过程
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 ;
4.事务
MySQL视图,函数,触发器,存储过程
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 ;
5.游标-实现循环语句

游标性能比较差,一般很少用,使用场景是:针对每一行都需要专门的处理计算的时候可能会用到,但是一般update+ 循环也能解决 如:update b set num=id+num;

MySQL视图,函数,触发器,存储过程
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)
6. 动态执行sql(防sql注入)