MySQL学习笔记-4

变量
主要可以分为两大类：系统变量(全局变量&会话变量) ， 自定义变量(用户变量&局部变量)

• 1.1 系统变量

  • 作用域
    ① 全局变量作用域：服务器每次启动将为所有的全局变量赋初始值。 针对所有会话(连接有效)，但重启后会失效。(若需要永久修改该变量的值，则需要在配置文件中手动修改)
    ② 会话变量作用域：仅仅针对当前会话(连接)有效

  • 相关语法

    #查看所有/部分 全局/会话系统变量
    show global|[session] variables [like clause]  
    #查看某个指定的系统变量
    select @@global|[@@session].系统变量名; 
    
    #为某系统变量赋值
    set @@global.|[@@session.]系统变量名 = 设定值; 
    # 例如：set global.autocommit = 0;该设定将在所有会话中生效。
    #      set [session.]autocommit = 0;该设定将仅针对此次会话。
    或者
    set global|[session] 变量名 = 设定值;
    

• 1.2 自定义变量

  • 作用域
    ① 用户变量：同系统变量中的会话变量的作用域相同，仅对当前会话(连接)有效
    ② 局部变量：仅仅在定义它的begin — end 中有效(即存储过程体中) , 且必须为第一句。

  • 相关语法

    #创建自定义变量并赋值(可以只set而不赋值)
    set @def_name|def_part_name=值; 或者 
    set @def_name |def_part_name:=值; 或者 
    select @def_name| @def_part_name:=值;
    更新/赋值  
    select 字段 into @def_name | def_part_name from tab_name;
    #声明局部变量，赋值语法同上(注意 ‘@‘)
    declare 变量名 类型 [default default_value];
    #查看自定义变量
    select @def_name | def_part_name ;
    

存储过程&函数

• 2.1 概

  • 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数
    ① 提高了代码的重用性;
    ② 简化了应用开发人员的很多工作 ;
    ③ 减少数据在数据库和应用服务器之间的传输，提高了数据处理的效率。
  • 区别
    ① 函数必须有返回值且仅有一个，而存储过程可以没有;
    ② 存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的(无需标识)。
    ③ 应用场景
    ④ 存储过程，函数，视图三者的区别

• 2.2 存储过程相关语法

  #创建存储过程
  delimiter $  #指定分隔标识符为 $
  create procedure 存储过程名([参数类型 参数名 数据类型,...])
  #参数类型可以分为：IN，OUT,INOUT
  begin
       #存储过程体：
       #一组合法的SQL语句，每条SQL语句结尾必须添加分号   
  end $     
  #如果存储过程体只有一句话，begin,end 可以省略。
  
  #调用存储过程
  call 存储过程名(参数类型 参数名 数据类型) $
  select @def_name $  #@def_names是存储过程参数列表中标识为out或inout类型的参数
  delimiter ;  #回复分隔符为常规使用的分号
  #删除存储过程
  drop procedure [if exists ]pro_name;
  #查看存储过程的定义信息
  show create procedure pro_name;
  #查看当前数据库中的存储过程信息
  show procedure status [like 'pattern'|where expr];
  

  • 关于delimiter(手册13.1.17 P2241)

  The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

  ? delimiter 命令仅用于指定当前mysql客户端识别一条语句的标识，当指定 $ 为客户端的分隔标识后，存储过程体中的';'仍然会被服务器端所识别，因为服务器端仍然以分号作为一条sql语句的标识。分号与其他具体的存储过程体是作为一个整体直接传送到服务器端的，不会在客户端进行解析。

• 2.3 函数相关语法

  #创建函数
  delimiter $  #指定分隔标识符为 $
  create function fun_name([参数名 数据类型, ...]) return 返回类型
  begin
       #函数体：
       #一组合法的SQL语句，每条SQL语句结尾必须添加分号   
       return 值 ;
  end $     
  #如果函数体只有一句话，begin,end 可以省略。
  
  #调用存储过程
  select fun_name(参数列表) $
  select @def_name $  #@def_names是存储过程参数列表中标识为out或inout类型的参数
  
  delimiter ;  #回复分隔符为常规使用的分号
  #删除函数
  drop function [if exists ]fun_name;
  #查看函数的定义信息
  show create function fun_name;
  

流程控制结构

• 分支结构

  • if函数： if(判断表达式,成立时返回的值,不成立时返回的值);

  • if分支结构 (只能应用于begin_end结构中)

    if 条件1 then 语句1; 
    elseif 条件2 then 语句2;
    ...
    end if;
    

  • case分支结构

    #判断等值                           #判断范围
    case exp                           case 
    when value1 then 返回值1/语句1;      when exp1 then 返回值1/语句1; 
    when value2 then 返回值/语句2;       when exp2 then 返回值/语句2;
    ...                                ...
    else 返回值/语句;                    else 返回值/语句;
    end case;                          end case;
    /*满足when条件则执行完then语句后跳出case结构，若when条件皆不满足，则执行else语句，若皆不满足且else 语句省略 ，则返回NULL值。
    

    注意：在流控制函数中的case when 语句 与 存储过程/函数中的case when 语句 结束方式有所区别(前者为end case,后者为end ),详细参见手册12.5或
    13.6.5.1节
    

• 循环结构( 用于begin_end结构体中 )

  • 分类：while ,loop , repeat 循环控制：iterate , leave

  • label: while 条件 do  loop_list  end while label;  #先判断后循环
    label: loop loop_list end loop label; #死循环
    label: repeat loop_list until 终止条件 end repeat label #先循环后判断
    #循环控制语句用于loop_list 中： 
    if condition then iterate|leave label  end if ; end while|loop|repeat;