【MySQL数据库设计与应用（五）】MySQL 编程基础

最新推荐文章于 2022-06-17 17:47:58 发布

-出发-

最新推荐文章于 2022-06-17 17:47:58 发布

阅读量1.4k

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/happyjacob/article/details/105874666

版权

MySQL 专栏收录该内容

8 篇文章 2 订阅

订阅专栏

文章目录

1 常量和变量

1.1 常量

常量有 5 种，分别是字符串型、数值型、日期时间型、布尔值、NULL

（1）字符串常量：
用成对单引号或双引号括起来的字符序列。MySQL 推荐用单引号表示（区别于其它语言的字符串）。

（2）数值型常量

二进制常量如：b’1011’ 、B’1011’
十进制常量，直接书写，如：3，-5，+3.14，123.7E-2
（123.7E-2 表示 123.7×10 -2 ，其中 E 前须有数字，E 后须为整数）
十六进制常量，需要使用前缀和单引号表示：
只加前缀：0x41、0x4d7953514c
结合前缀和单引号：x’41’ 、X’41’、x’4d7953514c’

（3）日期时间常量
用单引号括起来，是有特殊格式的字符串。格式： ‘ 年-月-日 [ 时:分:秒 [.微秒 ] ] ‘
如：‘2019-1-24 10:20:30.3000’

年份取值为 1000-9999，月份取值 1-12，日期取值 1-31
方括号 “[ ]” 表示可选项，具体值中不能出现方括号字符
省略 “时:分:秒[.微秒]” 只表示日期，可以精确到微秒级别
给 TimeStamp 列字段或变量赋值时会根据所处时区自动转换，而且会忽略微秒部分，并要求年份只能取值 1970-2037
“年-月-日” 之间的分隔符 “-” 可以换为 “\”、“@”、“%” 等

（4）布尔值和 NULL 常量

布尔值只能表示 True 或 False。其中 False 对应数值 0，True 对应数值 1。
NULL 用于表示“未知”、“待定” 、“没有值”、“无数据”等意义，是一种无类型的值，但不同于 0 或空字符串的含义。

1.2 变量

MySQL 中有 2 类变量。

用户变量：先定义才能使用
系统变量：MySQL 已经定义好了，用户直接使用即可

用户变量根据使用范围分会话用户变量和局部变量。

客户端与 MySQL 服务建立连接后，可以定义会话用户变量。在用户断开连接之前，会话用户变量一直起作用，但也只有该用户的本次连接可用；连接断开时会自动释放会话用户变量。
局部变量只在一段封装的存储程序内部起作用。在存储程序调用结束释放局部变量。

会话用户变量
定义

# 形式1：
SET @var1 = 值1 [, @var2 := 值2, … ]
# 形式2：
SELECT 值1 INTO @var1 [ , 值2 INTO @var2, … ]
# 形式3：
SELECT @var1 := 值1 [ , @var2 := 值2, … ]

需要注意的是以下几点：

变量名 @var1、@var2 等必须以 1 个“@”开头，可以由当前字符集的文字、数字、“. ” 、“_ ”和“$”等字符组成。
当变量名中需要包含了一些特殊符号（如空格、#等）时，可以用双引号、单引号或反引号（也叫勾号）“`”将整个变量名括起来。
“:=”是赋值运算符，会产生结果集，在存储函数中不能用来定义会话用户变量。
值 1、值 2、… 可以是表达式或常量值。
会话用户变量的赋值或设置，其形式与定义时完全相同。
调用存储过程或存储函数时的实际参数如果是第一次写成“@var”形式，其实质也是定义会话用户变量。

全局系统变量
定义

DECLARE var1 [, var2] ... 数据类型 [DEFAULT 默认值]

作用：定义 1~多个指定类型的局部变量，并设置初始值。

值得注意的是以下几点：

局部变量的名称不可以用“@”开头。
缺省“[DEFAULT 默认值]” ，则初始值为 NULL。
“默认值”可以是表达式或常量。
DECLARE 只能用于 BEGIN…END 的开头部分定义局部变量，
作用范围：只能在该 BEGIN…END 中使用。
存储函数和存储过程的形式参数也属于局部变量。

查看系统变量

SHOW [ GLOBAL | SESSION | LOCAL] VARIABLES [LIKE 模式字符串]

其中：

GLOBAL 限定查看全局系统变量，否则默认查看会话系统变量。
“[LIKE 模式字符串]”查看特定系统变量，否则查看所有系统变量。
模式字符串中用“%”通配符表示 0~多个字符。

需要特别注意的是：

系统变量名多数都以 2 个“@”开头
用户会话变量必须以 1 个“@”开头
局部变量则不能以“@”开头

SET设置变量值
设置所有类型的变量值：

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 代替相应的 “ 值 ”

2 条件结构 If、case

用 SHOW STATUS 语句可以查看存储过程和函数的状态，其基本的语法结构如下:

SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’ ]

删除存储过程和函数可以使用 DROP 语句，其语法结构如下:

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] Sp_name

IF 条件1 THEN S1;
[ ELSEIF 条件2 THEN S2; ]
...
[ ELSEIF 条件n THEN Sn; ]
[ELSE Sn+1;]
END IF;

去除了全部任选项就实现单项选择；
仅仅保留最后一个可选项 “ELSE Sn+1;” 则可以实现二选一。
多项选择时需要加上相应的：“ELSEIF 对应条件 THEN 对应选项; ”，并决定是否选用任选项：“ELSE Sn+1; ”。

CASE
WHEN 条件 1 THEN S1;
[ WHEN 条件 2 THEN S2; ]
...
[ WHEN 条件 n THEN Sn; ]
[ELSE Sn+1;]
END CASE;

去除了全部任选项就实现单项选择；
仅仅保留最后一个可选项 “ELSE Sn+1;” 则可以实现二选一。
多项选择时需要加上相应的：“WHEN 对应条件 THEN 对应选项; ”，并决定是否选用任选项：“ELSE Sn+1; ”。

example:
【例】设计存储函数 week_day1，根据参数值（1-7）输出“工作日”或者“双休日”。

delimiter $$			# 将结束符改为$$

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 $$

delimiter ;			# 将结束符改为;

select week_day1(2), week_day1(6);	# 调用函数

【例】设计存储函数 week_day_name(x) 根据整数输出“星期一”、“星期二” …或者“参数错”。

create function week_day_name(x tinyint)
	returns char(3)
begin
	declare wd char(3);
	case x
		when 7 then set wd = '星期日';
		when 6 then set wd = '星期六';
		when 5 then set wd = '星期五';
		when 4 then set wd = '星期四';
		when 3 then set wd = '星期三';
		when 2 then set wd = '星期二';
		when 1 then set wd = '星期一';
		else set wd = '参数错';
	end case;
	return wd;
end $$

# 或者

create function week_day_name(x tinyint)
	returns char(3)
begin
	declare wd char(3);
	case
		when x = 7 then set wd = '星期日';
		when x = 6 then set wd = '星期六';
		when x = 5 then set wd = '星期五';
		when x = 4 then set wd = '星期四';
		when x = 3 then set wd = '星期三';
		when x = 2 then set wd = '星期二';
		when x = 1 then set wd = '星期一';
		else set wd = '参数错';
	end case;
	return wd;
end $$

# 或者

create function week_day_name(x tinyint)
	returns char(3)
begin
	declare wd char(3);
	if x = 7 then set wd = '星期日';
	elseif x = 6 then set wd = '星期六';
	elseif x = 5 then set wd = '星期五';
	elseif x = 4 then set wd = '星期四';
	elseif x = 3 then set wd = '星期三';
	elseif x = 2 then set wd = '星期二';
	elseif x = 1 then set wd = '星期一';
	else set wd = '参数错';
	end if;
	return wd;
end $$

3 循环结构 while、loop 与 repeat

有 3 种循环语句：WHILE 、REPEAT 和 LOOP

3.1 while

语法格式：

[循环语句标签:] while 循环条件 do
循环语句 ;
# 可包含 leave[循环语句标签] 和 iterate语句
end while [循环语句标签] ;

功能：当循环条件成立时，一遍又一遍反复执行循环体，直到条件不成立为止。

需要注意的是以下几点：

while 之前的 “[循环语句标签:]”和 end while 后的“[循环语句标签]”一致。
end while 后以“;”结束。
“循环语句 ;”可以是 1 条或多条 MySQL 语句，可包含 leave 和 iterate 语句。
iterate 语句作用：
提前结束当前这一次循环体的执行。
此时还在循环中，若循环条件还成立，会再次从头执行循环体，直到条件不成立时循环语句才正常结束。
leave 语句用于在循环条件仍然成立时就结束循环语句，此时已经跳出循环外，提前终结本循环。

【例】请设计函数 IsPrime，判断给定正整数 n 是不是素数。

create function IsPrime(n int)
	returns int
begin
	declare i int default 2;
	declare yn int default 1;
	test: while i < n do
		if n % i = 0 then
			set yn = 0;
			leave test;
		end if;
		set i = i + 1;
	end while test;
	return yn;
end $$

3.2 loop

语法格式：

[循环语句标签:] LOOP
循环语句 ; 				# 可包含iterate 语句
IF 结束条件 THEN
LEAVE [ 循环语句标签] ;
END IF
END LOOP [循环语句标签] ;

注意事项:

LOOP 之前的 “[循环语句标签:]”和 END LOOP 之后的“[循环语句标签]”必须一致。
END LOOP 后必须以“;”结束。
“循环语句 ;”可以是 1 条或多条 MySQL 语句，可包含 iterate 语句。
LOOP 循环本身没有结束机制，必须使用 LEAVE 方可终止循环，而 LEAVE 一般配合 IF 语句使用。

【例】利用 loop 循环设计函数 IsPrime2 判断素数。Loop 循环是先执行循环体，再判断条件，对于特殊的素数 2，做了单列处理。

delimiter $$

create function IsPrime2(n int)
	returns int
begin
	declare i int default 2;
	declare yn int default 1;
	if n = 2 then
		return yn;
	end if;
	test:loop
		if n%i = 0 then
			set yn = 0;
			leave test;
		end if;
		set i = i + 1;
		if i >= n then
			leave test;
		end if;
	end loop test;
	return yn;
end $$

delimiter ;

select IsPrime2(5),IsPrime2(10), IsPrime2(19);

3.3 repeat

语法格式：

[循环语句标签:] REPEAT
循环语句 ; # 可包含ITERATE 和LEAVE 语句
UNTIL 结束条件
END REPEAT [循环语句标签] ;

注意事项

REPEAT 之前的可选项“[循环语句标签:]”和 END REPEAT 之后的“[循环语句标签]”必须一致。
END REPEAT 后必须以“;”结束。
“循环语句 ;”可以是 1 条或多条 MySQL 语句，可包含 ITERATE 和 LEAVE 语句。
REPEAT 循环本身就带有结束循环的机制，LEAVE 语句不是必须的。

【例】请用 REPEAT 循环设计函数 IsPrime3，判断给定正整数 n 是不是素数。

delimiter $$

create function IsPrime3(n int)
	returns int
begin
	declare i int default 2;
	declare yn int default 1;
	if n = 2 then
		return yn;
	end if;
	test:repeat
		if n%i = 0 then
			set yn = 0;
			leave test;
		end if;
		set i = i + 1;
		until i >= n
	end repeat test;
	return yn;
end $$

delimiter ;

select IsPrime3(2),IsPrime3(3), IsPrime3(9);

4 循环操作数据库实例

游标（Cursor）使用 4 步骤：定义、打开、取数据和关闭。

游标的定义，也叫游标声明：
确定需查询的数据、指明数据的“查询要求”：declare 游标名 cursor for select 查询语句;
游标的打开： open 游标名;
执行游标的“select查询语句”，将结果存放到服务器内存中
从游标中取数据
fetch 游标名 into 变量1 [, 变量2, … ];
取结果集中一条记录，存入into子句的1至多个变量中。
关闭游标：
close 游标名;
释放查询结果占用的内存空间。
注：若游标未关闭，在其所属begin-end末尾也会自动关闭。

【例】结合游标和循环，请查询给定学号的学生已经取得的总学分。

delimiter $$

create function getCredits( no char(12) charset gbk )
	returns int
begin
	declare s, m, score, credit, n int;
	declare rs 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 rs;
	while m <= n do
		fetch rs into score, credit;
		if score >= 60 then
			set s = s + credit;
		end if ;
		set m = m + 1;
	end while ;
	close rs;
	return s ;
end $$

delimiter ;

select getCredits('M20177001'), getCredits('M20177002');

#验证

select
Student_id, a.Course_id, score, credit
from choose a left join course b
on a.Course_id = b.Course_id
where Student_id in('M20177001', 'M20177002');