MySQL_05_自定义功能块

  • 自定义功能块
  • 概念
  • 常量
  • 变量
  • 系统变量
  • 用户自定义变量
  • BEGIN—END
  • IF
  • CASE
  • 循环


自定义功能块

概念

标识符：作为标识用的名称（每个对象都有一个）

//如：数据库名称、表名称、字段名等



标识符中的字符规定：

1）不加反引号（`）只能使用系统字符集中的字母、数字字符或“_”和“$”；

2）不加反引号（`）不能完全由数字字符构成；

3）字符用反引号（`）括起来，就允许使用任意字符



注释格式：

（1）注释单行：-- //两个减号，且减号后必须跟一个空格

（2）注释多行：#

（3）注释多行：/* */

常量

常量：程序运行中不会发生改变的量

常量类型

含义

字符串常量

指用单引号或双引号括起来的字符序列

数值常量

整数常量

小数常量

日期时间常量

特殊格式的字符串，需要单引号或者双引号括起来，分为时间常量和日期时间常量

布尔型

只包含两个值：TRUE和FALSE

二进制常量

由数字0和1组成 表示方法：前缀为“B”，后紧跟’二进制数’ （字符串必须用单引号括起来）

十六进制常量

由数字0到9和字母“A”到“F”组成（不区分大小写） 表示方法：前缀为“X”，后紧跟’十六进制数’ 前缀为“0X”，后紧跟一个十六进制数

NULL

可适用于各种字段类型 表示“值不确定”、“没有值”等含义

1）日期时间常量必须符合实际（如：不能设置‘1996-02-31’（2月不存在31），错误日期常量）

2）在SELECT语句中遇到TRUE或FALSE时会自动转换为字符串“0”或“1”

//二进制表达如：SELECT b’111101’,b’1’,b’11’; //显示“=”“笑脸”“心”

//十六进制中使用“0X”前缀时，后紧跟的数字不需要使用单引号括起来

//若将一个字符串或数字转换为十六进制格式的字符串，可以用HEX（）函数



变量

变量：程序运行中因其他因素导致改变而不能确定的量

变量

定义

包含

系统变量

由系统定义和维护 变量名前加“@@”

全局系统变量

会话系统变量

用户自定义变量

由用户定义和使用

用户会话变量（@开头）

局部变量（不以@开头）

1）全局系统变量：作用范围是所有服务

2）会话系统变量：作用范围是整个连接期间（默认是全局系统变量的拷贝）

//MySQL服务启动后没有客户机连接，则系统变量就是全局系统变量



标记全局系统变量：@@GLOBAL 变量名

标记会话系统变量：@@SESSION 变量名



列出所有的全局系统变量信息：

SHOW GLOBAL VARIABLES；



列出与当前会话相关的会话系统变量和全局系统变量信息：

SHOW [SESSION] VARIABLES；



查看系统变量的值：

1）SHOW命令：

SHOW GLOBAL VARIABLES LIKE ‘全局变量名’；

SHOW SESSION VARIABLES LIKE ‘会话变量名’；

SHOW VARIABLES LIKE ‘变量名’；

//可使用LIKE进行模糊查询（通配符（%或_）一起使用）

//查询全局系统变量时，三种方法都可查看

//查询会话系统变量，只能采用后两种方法查看



2）SELECT命令：

SELECT @@变量名；

//@@一般首先查询会话系统变量，若不存在，则查询全局系统变量



系统变量

设置系统变量的值：

1）修改MySQL源代码，对源代码重新编译（仅适用于MySQL高级用户）；

2）修改MySQL配置文件（需要重启MySQL服务）

3）使用“SET”命令重新设置系统变量的值；



设置全局系统变量：

1）格式1：SET @@ GLOBAL.变量名 = 变量值；

2）格式2：SET GLOBAL 变量名 = 变量值；

//部分系统全局变量名前的两个@@不能省略

//变量值可以是默认值（DEFAULT、ON、OFF）或具体取值

//只有root管理员才可设置全局系统变量的值



设置会话系统变量：

1）格式1：SET @@ SESSION.变量名 = 变量值；

2）格式2：SET SESSION 变量名 = 变量值；

3）格式3：SET @@ 变量名 = 变量值；

4）格式4：SET 变量名 = 变量值；

//部分会话系统变量名前的两个@@不能省略



静态变量：MySQL运行期间存在一些不能进行动态修改的特殊全局系统变量

1）只能通过修改源代码和配置文件对静态变量的值重新设置（不能使用“SET”语句进行设置）

比较

系统会话变量

用户会话变量

区别

以两个@@开头

以一个@开头

无需定义直接使用

需要用户进行定义

相同

变量名大小写不敏感


用户自定义变量

用户会话变量定义（一般情况下，用户会话变量的定义与赋值同时进行）：

1）SET语句

SET @变量名1 = 表达式[，@变量名N = 表达式]

//SET语句一次可以定义多个变量，不会产生结果集



2）SELECT语句

格式1：SELECT @变量名1：=表达式[，N]

格式2：SELECT 表达式1，表达式N INTO @变量1，@变量N

// 格式1在定义和赋值的同时会产生结果集，第二种则不会




局部变量： 用于语句块中，语句块执行完毕后就消失了

局部变量作用范围：运行程序中

局部变量变量必须定义在运行程序中（函数、触发器、存储过程以及事件）



DECLARE：用于定义局部变量的语句

1）定义格式：DECLARE 变量名 数据类型

2）定义时需同时指出变量名和数据类型

3）DECLARE写在BEGIN—END语句的开头（或调用局部变量语句前）

//其他类型变量不能使用DECLARE命令定义




局部变量使用场合：

（1）局部定义在存储程序的BEGIN—END语句之间；

1）此时局部变量必须使用DECLARE命令定义，且必须指定局部变量的数据类型

2）定义局部变量后，才可以使用SET或SELECT语句为其赋值



（2）局部变量作为存储过程或函数的参数使用；

1）此时不需要使用DECLARE命令定义，但需要指定参数的数据类型



（3）局部变量用于存储过程的SQL语句中



局部变量

用户会话变量

变量名没有@开头

变量名以@开头

使用DECLARE命令定义 定义时必须指定局部变量的数据类型 局部变量定义后，才可以使用SET 或SELECT语句为其赋值

定义变量时无需指定数据类型 直接使用SET或SELECT语句定义并进行赋值

若作为存储过程或函数的参数 在整个存储过程或函数内中有效 若定义在存储程序的BEGIN—END语句块中 只在定义的BEGIN—END语句块有效

在本次会话期间一直有效，直至关闭服务器连接

//若局部变量嵌入到SQL语句中，局部变量名不能与表字段名同名（因为局部变量名前没有“@“符号）



BEGIN—END

BEGIN—END语句块：多条MySQL表达式封包在一起以完成指定功能

[开始标签：]BEGIN
	[局部]变量的声明；
	错误触发条件的声明；
	游标的声明；
	错误处理程序的声明；
	业务逻辑代码；
END[结束标签]；



DELIMITER：用于定义命令结束标记的语句（默认为“；”）

1）定义格式：DELIMITER 结束标记符

2）使用DELIMITER时不需加“；”作为结束标记

//DELIMITER ； //设置“；”为结束标记



位运算符：对二进制数据进行操作

1）若不是二进制，会自动进行类型转换，运算结果为二进制数

2）使用SELECT语句显示二进制数时，会将其自动转换为十进制数显示

运算符

运算规则

&

按位与

|

按位或

^

按位异或

~

按位取反

>>

位右移

<<

位左移


IF

IF语句格式：

IF  条件表达式1  THEN  语句块1;
[ELSEIF  条件表达式2  THEN  语句块2]
[ELSEIF  条件表达式3  THEN  语句块3]
······
[ELSE  语句块N]
END IF；

1）END IF后必须以“；”结束

2）前面的ELSEIF中的ELSE和IF中不能空格



CASE

CASE语句格式：

CASE  表达式
WHEN  value1  THEN  语句块1；
WHEN  value2  THEN  语句块2；
······
ELSE  语句块N；
END CASE；

1）CASE语句用于实现比IF语句分支更为复杂的条件判断

2）CASE语句不同于高级程序语言（C、Java等），无需“break”语句跳出



查询中的CASE

程序中的CASE

CASE [字段名]

CASE 表达式

END

END CASE;

进行更为复杂的数据转换

只做表达式运算后的数据匹配


循环

跳出当前的循环语句：

LEAVE 循环标签；



跳出本次循环，继续下次循环

ITERATE 循环标签；



MySQL中常用的循环语句如下：

循环语句

语句格式

注意

WHILE语句

[循环标签：]WHILE 条件表达式 DO 循环体； END WHILE[循环标签]；

条件表达式为TRUE时，反复执行循环体，直到为FALSE

REPEAT语句

[循环标签：]REPEAT 循环体； UNTIL 条件表达式 END REPEAT[循环标签]；

条件表达式为FALES时，反复执行循环体，直到条件表达式为TRUE

LOOP语句

[循环标签：]LOOP 循环体； IF 条件表达式 THEN LEAVE [循环标签]； END IF； END LOOP；

LOOP循环本身没有停止循环的语句，所以通常使用LEAVE语句跳出LOOP循环