• Mysql中的视图、变量、存储过程、流程控制结构、函数和触发器


    一、视图

    2.1 、什么是视图

    ​ 视图mysql5.1版本出现的新特性,本身是一个虚拟表,它的数据来自于表,通过执行时动态生成。通俗的讲,视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。

    2.2、使用视图的好处

    1. 简化sql语句,用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。
    2. 保护基表的数据,提高了安全性,使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。

    2.3、创建视图

    CREATE [OR REPLACE]  VIEW  view_name 
    AS     select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
    

    如果在创建视图的时候制定了“WITH CHECK OPTION”,那么更新数据时不能插入或更新不符合视图限制条件(where语句)的记录。

    LOCAL : 只要满足本视图的条件就可以更新。

    CASCADED(默认值) : 必须满足所有针对该视图的所有视图的条件才可以更新。

    2.4、修改视图

    ​ 修改方式一:

    CREATE [OR REPLACE]  VIEW  view_name 
    AS     select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
    

    方式二:

    alter view  view_name 
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
    

    2.3、查看视图

    ​ 查看视图的方式和查看表的方式差不多

    desc view_name
    show create view view_name
    show tables;-- 不仅可以显示当前数据库下的表还可以显示出视图,没有show views
    

    2.4、删除视图

    drop view view_name1,view_name2
    

    注意:视图一般是用于查询,而不是更新,具有以下特点的视图都不允许更新

    • 包含分组函数、group by、distinct、having、union、join
    • 视图字段来自字段表达式或常数,不可执行INSERT和UPDATE,但允许执行DELETE
    • where后的子查询用到了from中的表
    • 不可更新的视图导出的视图

    2.5视图与表的比较

    关键字 是否使用物理空间 作用于
    视图 view 只保留sql逻辑,占用空间小 一般用于查询
    table 保存实际的数据 可用于增删改查

    二、mysql中的变量

    mysql中的变量按作用范围可以分为以下几个类型:局部变量,会话变量,全局变量。按是否是用户自定义的又可以分为系统变量和用户自定义变量。

    2.1、系统变量

    ​ 系统变量就是由系统提供的变量,不用用户自定义。

    1. 查看系统变量

      -- 查看所有系统变量
      show [global | session ] variables [like]
      -- 查看指定的系统变量
      select @@[global | session].变量名  -- 类似与对象.属性
      
      -- 给系统变量赋值
      set [global | session] 变量名 = 值
      set @@[global | session].变量名=值;
      
    • global表示全局变量,服务器层面上的,必须拥有super权限才能为系统变量赋值,作用域为整个服务器,也就是针对于所有连接(会话)有效
    • session表示会话变量,作用域为当前的会话(连接)

    注意:local是session的近义词,session可以使用local代替,在不指定session或者global的情况下,默认是session

    注意:无论是global变量还是session变量,在mysql服务重启之后,数据库的配置又会重新初始化,一切按照my.ini的配置进行初始化。global和session的配置都会失效了

    2.2、用户自定义变量

    1. 局部变量

      ​ 局部变量一般用在sql语句块中,比如存储过程的begin/end,只在当前begin/end代码块中有效,且只能放在begin end中,而且只能放在第一句。在该语句块执行完毕后,局部变量就消失了。declare语句专门用于定义局部变量,可以使用default来说明默认值。

      声明方式:declare 变量名 类型 【default 值】;

      赋值方式:

      set 变量名 = 值;
      set 变量名: = 值;
      select xx into 变量名 from 表;
      
      CREATE PROCEDURE myprocedure()
      BEGIN
      declare age int default 0;
      set age = 5;
      select age;
      end
      
    2. 用户变量(一次会话有效)

      MySQL中用户变量不用事前申明,在用的时候直接用“@变量名”使用就可以了。用户变量 在客户端链接到数据库实例整个过程中用户变量都是有效的。可以在begin end里面,也可以放在外面。

      注意:无论使用还是赋值用户变量都需要在前面记得加上@

      赋值方式:

      set @变量名=值;
      set @变量名:=值;
      select @变量名:=值;
      select xx into @变量名 from 表;
      select @num:=字段名 from 表名;
      

      注意上面两种赋值符号,使用set时可以用“=”或“:=”,但是使用select时必须用“:=赋值”

      使用方式:

      select @变量名;
      

    三、存储过程

    ​ 存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。

    存储过程和函数的区别在于函数必须有返回值,而存储过程没有

    • 存储函数:是一个有返回值的存储过程

    • 存储过程:是一个没有返回值的存储函数

    3.2、创建存储过程

    CREATE PROCEDURE procedure_name (参数模式 参数名 参数类型)
    begin
    	 SQL语句
    end
    -- 参数类型有三种
    in(默认):表示输入的参数
    out:表示输出的参数
    inout:可以输入又可以输出
    

    1.参数模式:in、out、inout,其中in可以省略

    2.存储过程体的每一条sql语句都需要用分号结尾

    在使用命令行写存储过程时,由于过程中的每一条sql都需要以‘;’结尾mysql会误以为是整个过程的结束,我们可以通过delimiter 重新定义一个分隔符

    delimiter $
    
    create procedure pro_test1()
    begin
    	select 'Hello Mysql' ;
    end$
    

    DELIMITER:

    ​ 该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器,该段命令是否已经结束了,mysql是否可以执行了。默认情况下,delimiter是分号;。在命令行客户端中,如果有一行命令以分号结束,那么回车后,mysql将会执行该命令。

    调用存储过程:

    call procedure_name(参数)
    调用in模式的参数:call sp1(‘值’);
    调用out模式的参数:set @name; call sp1(@name);select @name;
    调用inout模式的参数:set @name=值; call sp1(@name); select @name;
    

    查看存储过程:

    -- 查询某个存储过程的定义
    show create procedure 存储过程名;
    -- 查询db_name数据库中的所有的存储过程
    select name from mysql.proc where db='db_name';
    

    删除存储过程

    DROP PROCEDURE  [IF EXISTS] procedure_name ;
    

    四、流程控制结构

    4.1、分支结构

    1. if函数

      -- if函数实现简单的单双分支,可以放在表达式的任何位置
      if(条件,值1,值2)
      
    2. if条件判断

      实现多分支条件判断

      语法:

      -- id条件判断语句只能放在 begin end 语句中
      if 条件1 then 语句1;
      elseif 条件2 then 语句2;
      ...
      else 语句n;
      end if;
      
      -- 例子
      create procedure procedure1()
      begin
        declare  height  int  default  175; 
        declare  description  varchar(50);
        
        if  height >= 180  then
          set description = '身材高挑';
        elseif height >= 170 and height < 180  then
          set description = '标准身材';
        else
          set description = '一般身材';
        end if;
        
        select description ;
      end
      
    3. case结构实现多分支条件判断

      case结构可以放在任何位置,如果放在begin end 外面,作为表达式结合着其他语句使用如果放在begin end 里面,一般作为独立的语句使用

      方式一:

      case 表达式或字段
      when 值1 then 语句1;
      when 值2 then 语句2;
      ..
      else 语句n;
      end [case];
      
      -- 例子
      create procedure procedure2(day int)
      begin
        declare result varchar(20);
        case day
          when  1 then 
            set result = '星期一';
          when  2 then 
            set result = '星期二';
      	  when  3 then 
            set result = '星期三';
          when  4 then 
            set result = '星期四';
          when  5 then 
            set result = '星期五';
          when  6 then 
            set result = '星期六';
      		when  7 then 
            set result = '星期七';
      		else
      			set result = '输入错误';
        end case;
        
        select concat('今天是 :',result) as dayofweek ;
      end
      

      方式二:

      case 
      when 条件1 then 语句1;
      when 条件2 then 语句2;
      ..
      else 语句n;
      end [case];
      
      -- 例子
      create procedure procedure3(month int)
      begin
        declare result varchar(20);
        case 
          when month >= 1 and month <=3 then 
            set result = '第一季度';
          when month >= 4 and month <=6 then 
            set result = '第二季度';
          when month >= 7 and month <=9 then 
            set result = '第三季度';
          when month >= 10 and month <=12 then 
            set result = '第四季度';
        end case;
        
        select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ; 
      end
      

      4.2、循环结构

      1. while循环

        while循环是满足条件就继续执行

        语法:
        
        [名称:] while 循环条件 do
        	循环体
        end while [名称];
        
        -- 例子:
        -- 从0加到n
        create procedure procedure4(n int)
        begin
          declare total int default 0;
          declare num int default 1;
          name1:while num<=n do
            set total = total + num;
        	set num = num + 1;
          end while name1;
          select total;
        end
        
      2. repeat循环

        repeat是有条件的循环控制语句, 当满足条件的时候退出循环 。

        【名称:】repeat
        		循环体
        until 结束条件 
        end repeat 【名称】;
        
        -- 例子从1加到n
        create procedure procedure5(n int)
        begin
          declare total int default 0;
          
          repeat 
            set total = total + n;
            set n = n - 1;
            until n=0  
          end repeat;
          select total ;
        end
        -- 知道until满足就退出循环
        
        
      3. loop循环

        loop 实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用 LEAVE 语句实现,具体语法如下:

          【名称:】loop
          循环体
          end loop 【名称】;
    
          -- 例子从0加到n
          CREATE PROCEDURE procedure6(n int)
          BEGIN
            declare total int default 0;  
            ins: LOOP 
              IF n <= 0 then
              /* 注意这里使用了 leave来跳出循环,类似于break,
              iterate可以用来结束本次循环开启下一次循环,类似于	continue,且使用了leave或者iterate都必须指定循	环的名称
             */
                leave ins;
              END IF;  
              set total = total + n;
              set n = n - 1;
            END LOOP ins;  
            select total;
          END
    
      循环控制语句
    
      - leave:类似于break,用于跳出所在的循环
      - iterate:类似于continue,用于结束本次循环,继续下一次
    
      循环语句的总结:
    
      - 三种循环只能放在begin end中,都能实现循环结构
      - 这三种循环都可以省略名称,但如果循环中添加了循环控制语句(leave或   iterate)则必须添加名称
      - loop 一般用于实现简单的死循环,while 先判断后执行repeat 先执行后判断,无条件至少执行一次。
    

    五、存储函数

    ​ 存储函数相对于存储过程最大的一个不同点就是,存储函数必须有返回值,另外函数的参数类型只能是in,不能是inout或者out.

    CREATE FUNCTION function_name(参数模式 参数名 参数类型) --参数模式是in可以省略
    -- 声明返回类型,这历史returns,不是return
    RETURNS type 
    BEGIN
    	sql语句
    	return 返回值;
    END;
    
    -- 例子,将输入的数乘2并返回
    create FUNCTION fun1(num int)
    returns int
    BEGIN
    	DECLARE cnum int default 0;
    	set cnum = num*2;
    	return cnum;
    end
    
    -- 使用函数
    select fun1(5);
    

    六、触发器

    6.1、简介

    ​ 触发器是与表有关的数据库对象,指在 insert/update/delete 之前或之后,触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。触发事件的操作和触发器里的SQL语句是一个事务操作,具有原子性,要么全部执行,要么都不执行;

    使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容

    触发器类型 NEW 和 OLD的使用
    INSERT 型触发器 NEW 表示将要或者已经新增的数据
    UPDATE 型触发器 OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
    DELETE 型触发器 OLD 表示将要或者已经删除的数据

    6.2创建触发器

    create trigger trigger_name 
    
    before/after insert/update/delete
    
    on tbl_name 
    
    [ for each row ]  -- 行级触发器
    
    begin
    
    	trigger_stmt ;
    
    end;
    

    before/after :触发器触发的时间

    insert/update/delete:触发器触发的条件

    根据出发时间和触发条件的组合(笛卡尔积)可以看出一种有6中触发器。

    for each row:行级触发器,针对每一行的改动执行一次

    在 InnoDB 数据库中,其上建立的表是事务性表,也就是事务安全的。这时,若SQL语句或触发器执行失败,MySQL 会回滚事务,有:
    ①如果 BEFORE 触发器执行失败,SQL 无法正确执行。
    ②SQL 执行失败时,AFTER 型触发器不会触发。
    ③AFTER 类型的触发器执行失败,SQL 会回滚。

    注意:触发器使用的表需要支持事务(就是InnoDB),创建触发器的表,不能再sql语句(就是BEGIN...END)中再次操作该张表。

    -- 在emp表中插入数据后执行触发器
    create trigger emp_logs_insert_trigger
    after insert 
    on emp 
    for each row 
    begin
      insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));	
    end 
    

    6.3、删除触发器

    -- 如果不指定schema_name,就默认当前的数据库
    drop trigger [schema_name.]trigger_name
    

    6.4、查看触发器

    -- 可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
    show triggers;
    

    参考链接:https://www.jianshu.com/p/3ffdf1397ce1

  • 相关阅读:
    .NET WinForm下StatusStrip控件如何设置分隔线及部分子控件右对齐
    winform 弹出窗体指定位置
    命名空间“System.Web”中不存在类型或命名空间名称“Optimization”(是否缺少程序集引用?)
    SQL之case when then用法详解
    VS2015编译错误:调用的目标发生了异常--->此实现不是Windows平台FLPS验证的加密算法的一部分。
    .NET Core 控制台中文乱码问题!
    IOC 依赖注入 Unity
    《重构:改善代码的既有设计》读书笔记
    .NET中的堆(Heap)和栈(Stack)的本质
    浅谈 Sql Server 游标
  • 原文地址:https://www.cnblogs.com/myblogstart/p/13704654.html
Copyright © 2020-2023  润新知