7.1 常量
常量:固定数据值、字面量、字面值、标量值
作用:参与运算或给变量赋值
种类:5种,包括:字符串、日期时间、数值、布尔、NULL。
其中:
字符串常量:用成对单引号“‘”或双引号“””括起来的字符序列。
数值型常量:二进制、十进制与十六进制。
- 二进制常量如:b’1011’、B’1011’。
- 十进制常量,最常用,直接书写,不需要定界符,如:3,-5,+3.14,123.7E-2
- 十六进制常量,需要使用前缀和单引号表示:0x41、x’41’、X’41’
日期时间型:用单引号括起来,是有特殊格式的字符串。
格式:年-月-日[ 时:分:秒[.微秒] ]
• 年份取值为1000-9999,月份取值1-12,日期取值1-31
• 方括号“[ ]” 表示可选项,具体值中不能出现方括号字符
• 省略“时:分:秒[.微秒]”只表示日期,可以精确到微秒级别
• 给TimeStamp列字段或变量赋值时会根据所处时区自动转换,而且会忽略微秒部分,并要求年份只能取值1970-2037。
• “年-月-日”之间的分隔符“-”可以换为“\”、“@”、“%”等
布尔型:只能表示True或False,1为True,0为False
NULL型:表示“未知”、“待定”、“没有值”、“无数据”等意义,是一种无类型的值 ,但不同于0或空字符串的含义。
7.2 变量
变量分为用户变量和系统变量。
用户变量:需要先定义再使用,定义变量的名称、类型和初始值。
系统变量:系统所定义的变量,用户可以访问或修改,但是不能定义。
7.2.1 用户变量
用户变量又分为:会话用户变量和局部变量。
客户端与MySQL服务建立连接后,可以定义会话用户变量。
在用户断开连接之前,会话用户变量一直起作用,但也只有该用户的本次连接可用;连接断开时会自动释放会话用户变量。
局部变量只在一段封装的存储程序内部起作用。在存储程序调用结束释放局部变量。
7.2.1.1 用户变量定义
会话用户变量定义:
SET @var1 = 值1 [, @var2 := 值2, … ]
SELECT 值1 INTO @var1[, 值2 INTO @var2, … ]
SELECT @var1 := 值1 [ , @var2 := 值2, … ]
需要注意以下几点:
- 变量名@var1、@var2等必须以1个“@”开头,可以由当前字符集的文字、数字、“.”、“_”和“$”等字符组成。
当变量名中需要包含了一些特殊符号(如空格、#等)时,可以用双引号、单引号或反引号(也叫勾号 ` 将整个变量名括起来。 :=
是赋值运算符,会产生结果集,在存储函数中不能用来定义会话用户变量。- 值1、值2、… 可以是表达式或常量值。
- 会话用户变量的赋值或设置,其形式与定义时完全相同。
————————————————————
局部变量定义:
DECLARE var1 [, var2] ... 数据类型[DEFAULT 默认值]
需要注意以下几点:
- 局部变量的名称不可以用“@”开头。
- 缺省“[DEFAULT 默认值]”,则初始值为NULL。
- “默认值”可以是表达式或常量。
- DECLARE只能用于BEGIN…END的开头部分定义局部变量,
作用范围:只能在该BEGIN…END中使用。 - 存储函数和存储过程的形式参数也属于局部变量。
7.2.2 系统变量
系统变量是MySQL用来跟踪和记录系统运行状态的变量,用户不能定义,但是可以访问与修改,从而感知或影响系统。
被分为全局系统变量和会话系统变量,都由MySQL系统定义并维护。
全局系统变量:
- 启动时MySQL根据系统配置定义全局系统变量并初始化为默认值(约393个全局系统变量)。
- 生存期从MySQL服务启动时起,直到MySQL服务停止或重启为止。
- 作用范围是整个系统,其值改变会影响整个系统。
会话系统变量:
- 生存期从客户端连接MySQL服务开始,到连接断开为止。
- 连接开始时根据该瞬间的全局系统变量名称、类型和值复制创建专属于本连接的系统会话变量(约393个)。
此外,还会创建专属于本连接但不同于全局系统变量的其它变量(约12个系统会话变量不同于全局系统变量)。 - 会话系统变量的作用范围仅仅局限于本连接。
- 用户更改专属于本连接的会话系统变量的值,只会影响到本连接的运行状态。
7.2.2.1 系统变量查看
SHOW [ GLOBAL | SESSION | LOCAL] VARIABLES [LIKE 模式字符串]
- GLOBAL限定查看全局系统变量,否则默认查看会话系统变量。
- “[LIKE 模式字符串]”查看特定系统变量,否则查看所有系统变量。
- 模式字符串中用“%”通配符表示0~多个字符。
• 系统变量名多数都以2个“@”开头
• 用户会话变量必须以1个“@”开头
• 局部变量则不能以“@”开头
7.2.3 设置变量
设置所有类型的变量值:
SET [ GLOBAL | [ SESSION | LOACAL| @@ | @ ]变量名1 = 值1
| @@global. | @@ [session. | @@local. | @@ | @]变量名1 = 值1
设置用户变量值:
SELECT 值1 INTO [@]变量名1[ , 值2 INTO [@]变量名2, … ]
SELECT [@ ] 变量名1 := 值1[, @ ] 变量名2 := 值2, … ]
需要特别指出的是以下几点:
- 全局系统变量需要用GLOBAL或@@global.特别指出。
- LOCAL和SESSION是同义词。
- 会话系统变量需要用SESSION、LOCAL、@@session.、@@local.
或@@前缀。 - 会话用户变量需要用“@”字符。
- 整个可选项[@@ global.|@@session. | @@local. |@@|@] 都缺省则改变局部变量的值。
- 可以用DEFAULT代替相应的“值”来恢复系统变量的默认值。
7.3 运算符与表达式
数据运算是计算机的基础功能。
表达式是由常量、变量、运算符和函数构成的运算式。
灵活构建并求解表达式,是数据查询、数据计算和变量赋值的基础,更是判断选择和迭代循环的关键。
7.3.1 运算符种类
MySQL中运算符包括算术、比较、逻辑与位运算符四种。
算术运算符
符号 | 功能 |
---|---|
+ | 正号 或 加法运算 |
- | 负号 或 减法运算 |
* | 乘法 |
/ | 除 |
%、MOD | 模运算,取整除后的余数 |
DIV | 整除,取商的整数部分 |
注意:
- 表达式中不能省略任何一个运算符
- +和-还可以表示取原数和取相反数运算
- +和-还可以用来计算日期,如:‘2019-06-19 22:55:02’ + interval 22 day
#结果为:‘2019-07-11 22:55:02’ - 算术表达式中的字符串会自动转换为数字进行运算,不能被转换的转换为0
比较运算符
符号 | 功能 |
---|---|
> | 大于 |
< | 小于 |
= | 等于 |
>= | 大于等于 |
<= | 小于等于 |
!=<> | 不等于 |
<=> | 相等或都为NULL |
- 比较运算符的结果是True、1、0、False、NULL
- NULL参加的比较运算,除了“<=>”和 IS [NOT] NULL外,结果都是NULL。
- 字符串比较时(除非用BINARY关键字),默认不区分大小写,还要先截掉字符串尾部所有的空格字符,再进行比较。
- [NOT] BETWEEN、 [NOT] IN、[NOT] LIKE、[NOT] REGEXP、IS [NOT] NULL 也被称为比较运算符。
逻辑运算符
符号 | 功能 |
---|---|
!、NOT | 非,否定,判断指定条件是否不成立 |
&& 、AND | 逻辑与,判断是否两个条件都成立 |
|| 、OR | 逻辑或,判断是否有条件成立 |
XOR | 逻辑异或,判断是否仅有一个条件成立 |
判断选值运算符case也对多个条件进行运算,并根据条件的测试结果获取
不同的结果,如下:
case
when 条件1 then 表达式1
when 条件2 then 表达式2 …
else 表达式n
end
位运算符
符号 | 功能 | 举例 |
---|---|---|
~ | 位非,逐位取反 | ~(b’1011’) #结果为 (b’0100’) |
& | 按位与,相应位都为1,结果位才为1 | b’1011’ & b’1100’ #结果为(b’1000’) |
| | 按位或,相应位至少1个为1,结果位为1 | b’1011’ | b’1001’ #结果为(b’1011’) |
^ | 按位异或,相应位只能有1个为1,结果位才为1 | b’1011’ ^ b’1101’ #结果为6(b’0110’) |
>> | 位右移 | b’1011’ >>2 #结果为2 (b’0010’) |
<< | 位左移 | b’1011’ <<2 #结果为44(b’101100’) |
7.3.2 运算符优先级
运算符 | 优先级 |
---|---|
INTERVAL | 1 |
BINARY、 COLLATE | 2 |
!(逻辑非) | 3 |
+(正)、–(负)、~(按位取反) | 4 |
*(乘)、/(除)、DIV(整除)、%或MOD(求余、模运算) | 6 |
+(加)、–(减) | 7 |
<<(位左移)、 >>(位右移) | 8 |
&(按位与) | 9 |
| 按位或 | 10 |
=、>、<、>=、<=、<>、!= 、<=>、IS、LIKE、REGEXP、IN | 11 |
ALL、ANY、SOME、BETWEEN、CASE、WHEN、THEN、ELSE | 12 |
NOT(逻辑非) | 13 |
AND、&&(逻辑与) | 14 |
XOR(异或) | 15 |
OR、|| (逻辑或) | 16 |
=、:=(赋值) | 17 |
相关:
- 只需要1个数据就可以运算的是一元运算符
- 关键字和一元运算符 !、+、-、~ 优先级最高
- 一般来说,各类运算符的优先次序如下:
算术运算 > 位运算 > 比较运算 > 逻辑运算 - 构建表达式时,为了保证准确性,应尽量使用小括号“( )”保证自
己的设计意图与系统的实际求解顺序相一致,而不是依赖于优先级。
7.4 条件选择
条件结构分为:
如何实现?
IF条件语句
IF 条件1 THEN S1;
[ ELSEIF 条件2 THEN S2; ]
...
[ ELSEIF 条件n THEN Sn; ]
[ELSE Sn+1;]
END IF;
CASE语句
CASE
WHEN 条件1 THEN S1;
[ WHEN 条件2 THEN S2; ]
...
[ WHEN 条件n THEN Sn; ]
[ELSE Sn+1;]
END CASE;
简化CASE表达式
CASE 表达式
WHEN 值1 THEN S1;
[ WHEN 值2 THEN S2; ]
...
[ WHEN 值n THEN Sn; ]
[ ELSE Sn+1; ]
END CASE;
单项选择实例:
mysql> use db1
Database changed
mysql> drop function if exists week_day1 $$
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> set names gbk $$
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL log_bin_trust_function_creators = 1 $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create function week_day1(x tinyint)
-> returns char(3)
-> begin
-> declare wd char(3) default '工作日';
-> if x>5 then
-> set wd='双休日';
-> end if;
->/* 可改用CASE语句如下实现:
CASE
WHEN x > 5 THEN SET wd = '双休日’;
ELSE SET wd = '工作日';
END CASE;
*/
-> return wd;
-> end $$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter $$
mysql> select week_day1(2),week_day1(6) $$
+--------------+--------------+
| week_day1(2) | week_day1(6) |
+--------------+--------------+
| 工作日 | 双休日 |
+--------------+--------------+
1 row in set (0.00 sec)
7.5 循环结构
循环结构:也叫重复结构,用来表达有条件、有规律、周而复始的数据处理功能。
循环体:需要规律性地重复执行的部分
循环条件:决定循环体是否执行、会执行几遍、何时停止的表达式,
一般是比较表达式和逻辑表达式。
3种循环语句:WHILE、REPEAT和LOOP
7.5.1 WHILE循环
语法格式:
[循环语句标签:] while 循环条件 do
循环语句 ;
# 可包含 leave[循环语句标签] 和 iterate语句
end while [循环语句标签] ;
功能:当循环条件成立时,一遍又一遍反复执行循环体,
直到条件不成立为止。
需要注意的是以下几点:
- while之前的 “[循环语句标签:]”和end while后的“[循环语句标签]”一致。
- end while后以“;”结束。
- “循环语句 ;”可以是1条或多条MySQL语句,可包含leave和iterate语句。
iterate
语句作用:
提前结束当前这一次循环体的执行。
此时还在循环中,若循环条件还成立,会再次从头执行循环体,
直到条件不成立时循环语句才正常结束。leave
语句用于在循环条件仍然成立时就结束循环语句,
此时已经跳出循环外,提前终结本循环。
循环实例:判断是否是素数
mysql> delimiter $$
mysql> create function IsPrime(n int) returns int
-> begin
-> declare i int default 2;
-> declare y int default 1;
-> test:while i<n do
-> if n%i=0 then
-> set y=0;
-> leave test;
-> end if;
-> set i=i+1;
-> end while test;
-> return y;
-> end $$
Query OK, 0 rows affected (0.01 sec)
mysql> select IsPrime(2),IsPrime(5),IsPrime(9) $$
+------------+------------+------------+
| IsPrime(2) | IsPrime(5) | IsPrime(9) |
+------------+------------+------------+
| 1 | 1 | 0 |
+------------+------------+------------+
1 row in set (0.00 sec)
7.5.2 LOOP循环
语法格式:
[循环语句标签:] LOOP
循环语句 ; #可包含 iterate语句
IF 结束条件 THEN
LEAVE [循环语句标签] ;
END IF
END LOOP [循环语句标签] ;
功能:当结束条件不成立时,一遍又一遍反复执行循环体,
直到结束条件成立为止。
- LOOP之前的 “[循环语句标签:]”和END LOOP 之后的“[循环语
句标签]”必须一致。 - END LOOP后必须以“;”结束。
- “循环语句 ;”可以是1条或多条MySQL语句,可包含iterate语句。
- LOOP循环本身没有结束机制,必须使用LEAVE方可终止循环,而
LEAVE一般配合IF语句使用。
循环实例:LOOP素数检查
mysql> delimiter $$
mysql> create function IsPrime1(n int) returns int
-> begin
-> declare i int default 2;
-> declare y int default 1;
-> if n=2 then return y;
-> end if;
-> test:LOOP
-> if n%i =0 then set y=0;
-> leave test;
-> end if;
-> set i=i+1;
-> if i>=n then leave test;
-> end if;
-> END LOOP test;
-> return y;
-> end $$
Query OK, 0 rows affected (0.01 sec)
7.5.3 REPEAT循环
语法格式:
[循环语句标签:] REPEAT
循环语句 ; # 可包含ITERATE和LEAVE 语句
UNTIL 结束条件
END REPEAT [循环语句标签] ;
功能:
当结束条件不成立时,一遍又一遍反复执行循环体,
直到结束条件成立为止。
- REPEAT之前的可选项“[循环语句标签:]”和END REPEAT之后的“[循环语句标签]”必须一致。
- END REPEAT后必须以“;”结束。
- “循环语句 ;”可以是1条或多条MySQL语句,可包含ITERATE和LEAVE语句。
- REPEAT循环本身就带有结束循环的机制,LEAVE语句不是必须的。
循环实例:REPEAT判断素数
mysql> delimiter $$
mysql> create function IsPrime2(n int) returns int
-> begin
-> declare i int default 2;declare y int default 1;if n=2 then return y;end if;
-> test:REPEAT
-> if n%i=0 then set y=0;leave test;end if;set i=i+1;
-> UNTIL i>=n
-> END REPEAT test;return y;
-> end $$
Query OK, 0 rows affected (0.01 sec)
mysql> select IsPrime2(7) $$
+-------------+
| IsPrime2(7) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
7.6 循环+游标
循环是有条件、有规律地、反复处理数据。需要不断反复处理的部分就是循环体,周而复始的关键就是循环条件。
• 用循环逐一处理多个记录的思路:
先明确单条记录要如何处理,将其作为循环体。
接着把“所有记录都处理完毕”作为重复的先决条件。
然后选用合适的循环语句实现。
• 逐一地处理查询到的记录,需要用到游标
游标(Cursor)使用4步骤:定义、打开、取数据和关闭。
①游标的定义,也叫游标声明:
确定需查询的数据、指明数据的“查询要求”:
declare 游标名 cursor for select查询语句;
②游标的打开:
open 游标名;执行游标的“select查询语句”,将结果存放到服务器内存中
③从游标中取数据
fetch 游标名 into 变量1 [, 变量2, … ];取结果集中一条记录,存入into子句的1至多个变量中。
④关闭游标:
close 游标名;释放查询结果占用的内存空间。
注:若游标未关闭,在其所属begin-end末尾也会自动关闭。
使用游标的注意事项:
• 执行1次fetch命令只能取到1条记录
• 1次select查询往往都有多条记录,要执行多次fetch。
如何用循环来控制fetch的执行次数呢?
方法1:根据结果集的条数确定。
方法2:用fetch取数据,取完了、无法再取时就会出错并停止fetch。
使用实例:
mysql> delimiter $$
mysql> create function getCredits(no char(12) charset gbk) returns int
-> begin
-> declare s,m,score,credit,n int;
-> declare re cursor for
-> select a.score,b.credit
-> from choose a,course b
-> where a.course_id=b.course_id and student_id=no;
-> select count(*) into n
-> from choose a,course b
-> where a.course_id=b.course_id and student_id=no;
-> set s=0,m=1;
-> open re;
-> while m<=n do
-> fetch re into score,credit;
-> set m=m+1;
-> if score>=60 then set s=s+credit;
-> end if;
-> end while;
-> close re;
-> return s;
-> end $$