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

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:
SELECT1 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

设置用户变量值:

SELECT1 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 步骤:定义、打开、取数据和关闭。

  1. 游标的定义,也叫游标声明:
    确定需查询的数据、指明数据的“查询要求”:declare 游标名 cursor for select 查询语句;
  2. 游标的打开: open 游标名;
    执行游标的“select查询语句”,将结果存放到服务器内存中
  3. 从游标中取数据
    fetch 游标名 into 变量1 [, 变量2, … ];
    取结果集中一条记录,存入into子句的1至多个变量中。
  4. 关闭游标:
    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');
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值