【数据库】四、数据库编程（SQL编程）

四、数据库编程

另一个大纲：

5.1存储过程
5.1.1存储过程基本概念
5.1.2创建存储过程
5.1.3存储过程体
5.1.4调用存储过程
5.1.5删除

5.2存储函数
5.2.1创建存储函数
5.2.2调用存储函数
5.2.3删除存储函数

目录

1.SQL编程基础

MySQL也在常量、变量、运算符、表达式、流程控制以及函数等方面进行了扩展。

主要介绍MySQL中使用的常量、变量的类型和用法，其中包含了常量、变量、运算符、表达式、begin-end语句块、条件控制语句、循环语句、重置命令结束标记等内容。

MySQL中提供给用户的常用的系统函数，包括数学函数、字符串函数、数据类型转换函数、条件控制函数、系统信息函数、日期和时间函数还有一些其他的常用函数。

1.1常量

常量也称为文字值或标量值，是指程序运行中值始终不变的量。

常量的格式取决于它所表示的的值的数据类型。

1. 字符串常量：是用单引号或者双引号引起来的字符序列。

   例如：“I love China”，‘Hello,world’。

2. 数值常量

   数值常量分为整型和实型。整型常量即十进制整数，实型常量分为小数表示和科学计数法。

   例如：3.14、1.234E-5表示1.234*10^-5。

3. 日期时间常量

   MySQL中，日期时间常量是符合规定格式的字符串。
   格式为：YYYY-MM-DD HH:MM:SS。

   例如：
   '17:45:20’表示时间常量
   '2020-05-09 17:47:00’表示日期时间常量。
   '2020-05-09’表示日期常量

   但要在合法范围内，如某年的2月31日，是非法的日期时间常量。

4. 布尔值常量

   布尔值是用来表示对错的。常量为：true和false。true的值为1，false的值为0。

5. NULL值

   当表达式运行结果不确定或没有值时，可以用NULL表示，它与任何类型都匹配。如果使用NULL作为运算量参与算术运算、逻辑运算时，运算结果依然是NULL。

1.2变量

变量就是在程序执行过程中值可以改变的量。变量用于临时存放数据，变量中的数据随着程序的运行而变化。

变量由变量名和变量值构成，变量名用于标识该变量，变量名不能与命令和函数名相同。变量的数据类型与常量一样，它确定了该变量存放值的格式及允许的运算。

MySQL中的变量3种分类

1. 系统变量
2. 用户变量（用户会话变量）
3. 局部变量

1.2.1系统变量

系统变量用于一些系统参数的设置。

系统变量在MySQL服务器启动时就被引入并初始化为默认值。

MySQL有一些特定的设置，当MySQL数据库服务器启动的时候，这些设置被读取来决定下一步骤。

例如，有些设置定义了数据如何被存储，有些设置则影响到处理速度，还有些与日期有关，这些设置就是系统变量。

大多数系统变量应用于其它SQL语句时，必须在名称前加两个@符号，而为了与其它SQL产品保持一致，某些特定的系统变量要省略掉这两个@符号，如CURRENT_DATE（系统日期）、CURRENT_TIME（系统时间）、CURRENT_USER（SQL用户的名字）等。

-- 获得现在使用的MySQL版本。
SELECT @@VERSION ;
-- 获得系统当前时间。
SELECT CURRENT_TIME;-- 查看当前使用的MySQL的版本信息和当前的系统日期。
SELECT @@VERSION AS '当前MySQL版本',CURRENT_DATE;

1.2.2用户变量（用户会话变量）

用户变量与连接有关。

以@开始，形式为@变量名。用户会话变量对大小写不敏感，命名中不可以用特殊符号，一般情况下，变量不要用中文命名。

特点：

1. 一个客户端定义的变量不能被其他客户端看到或使用。
2. 当客户端退出时，该客户端连接的所有用户变量将自动释放。当终端断开与服务器的连接，这些变量就会释放，不再占用服务器的内存。
3. 为了实现不同SQL语句中进行值的传递，可以把一些数值存储在自定义的用户变量中，不同的SQL语句都可以对它进行访问。
4. 在使用用户变量前必须先定义和初始化。如果变量没有初始化则它的值为NULL。

定义方法：

1. 使用set命令为用户对话变量定义并赋值：

set {@用户会话变量=表达式|常量} [,…N];-- eg.创建用户变量name并赋值为“王林”。
SET @name='王林';-- 还可以同时定义多个变量，中间用逗号隔开。
-- eg.创建用户变量user1并赋值为1，user2赋值为2，user3赋值为3。
SET @user1=1, @user2=2, @user3=3;-- 定义用户变量时变量值可以是一个表达式。
-- eg.创建用户变量user4，它的值为user3的值加1。
SET @user4=@user3+1;-- 输出展示select
set @stu_name ='Tom';
set @stu_score=99;
select @stu_name,@stu_score;

2. 使用select语句定义用户会话变量并赋值：

-- 语法1
select {@用户会话变量 := 表达式|常量} [,…N]-- 语法2
select {表达式|常量 into @用户会话变量}[,…N]

【注意】在语法1中，使用的赋值符号是:=，因为在select语句中，=是关系运算符，表示比较。

将前面的set语句改成select语法，分别为：

-- 语法1
select @stu_name:='Tom',@stu_score:=99;
select @stu_name, @stu_score;
-- 语法2
select 'Jerry' into @stu_nm;
select @stu_nm;

3. 用户会话变量在SQL语句中的应用

在数据库使用过程中，经常遇到检索出的结果是单个值。有时需要对这些值进行进一步的操作，此时，可以使用用户会话变量临时保存这些值，以便下一步的操作。

会话变量可以直接嵌入到select、insert、update、delete语句的表达式中。

例如：

set @stu_no='1902030102';
select * from student where stu_no=@stu_no;

在上述命令中，MySQL解析器通过用户会话变量前面的@来区别字段名与用户会话变量之间的区别。

-- 查询表student中姓名为“秦建兴”的Stu_no，并将该值保存在用户变量id_stu中。
SET @id_stu =(SELECT Stu_no FROM student WHERE Stu_name='秦建兴');
SELECT * FROM score WHERE stu_no=@id_stu;

1.2.3局部变量

局部变量是指定义在存诸函数、触发器、存储过程及事件等存储程序中的变量，这些变量的作用域范围仅限于所定义的存储程序中，批处理结束后，存储在局部变量中的信息将丢失。

说明：局部变量只能在BEGIN…END语句块中声明。

局部变量必须在存储过程的开头就声明，声明完后，可以在声明它的BEGIN…END语句块中使用该变量，其他语句块中不可以使用它。

1. 定义方法

局部变量必须用DECLARE命令定义后才可以使用，语法形式如下：

declare {变量名列表 数据类型 [default 默认值]}

变量的定义必须在存储过程的开头。

eg.:以下语句声明了两个整型变量和一个字符型变量：
declare n1,n2 int default 0;
declare s varchar(10);

2. 赋值方法

使用DECLARE命令声明并创建局部变量之后，如果想要设定局部变量的值，必须使用set命令或者select...into...语句。其语法格式如下：

set {变量名=表达式}[,…N]
-- 或者
select {字段名列表} into {变量名列表} [from {表名} where {条件}]-- declare语句专门用于定义局部变量，可以使用default来说明默认值declare age int default 0;
-- 局部变量的赋值方式一declare age int;set age=18;
-- 局部变量的赋值方式二declare age int;select StuAge into age from demo.student where StuNo='A001';

这段语句定义了name和tel两个局部变量，并从student表中检索学号为’180101011’的数据，为name和tel赋值：

declare name varchar(10);
declare tel varchar(11);
declare n int;
set n=0;
select stu_name,stu_tel into name,tel from student where stu_no='180101011';

局部变量与用户会话变量

在使用过程中，局部变量与用户会话变量要分清楚，以免犯错。

二者区别主要有以下两点：

（1）开头标志不同：用户会话变量是以@开头，而局部变量名不以@开头；
（2）作用域不同：用户会话变量的作用域是在本次会话期间一直有效，而局部变量是在其定义的存储过程或函数块中有效。

1.3运算符与表达式

运算符是操作数据的符号，能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。

表达式用来表示某个求值规则，它由运算符和配对的圆括号将常量、变量、函数等操作数以合理的形式组合而成。

每个表达式都产生唯一的值。表达式的类型由运算符的类型决定。从功能角度划分，MySQL中的运算符分为：

1. 算术运算符
2. 比较运算符
3. 逻辑运算符
4. 位操作运算符。

1.3.1算术运算符与算术表达式

算术运算符包括加(+)、减(–)、乘(*)、除(/)、求余(%)和求商(div)。

如果在一个表达式中，出现多个算术运算符，则运算符的优先级顺序为：乘、除、取模运算为同一优先级，加、减运算符的优先级为次。

如果参与的运算量中，有一个值为NULL，则运算结果也是NULL。

-- 【例9-6】算术运算符的使用。
select 5/4,5.0/4,10%3--运算结果为：1   1.250000   1
select 25/(3*6), 25%(3*6)	--运算结果为：1   7-- 【例9-7】日期类型数据的加与减。
select '2020-05-15' + interval '22' day, '2030-05-15'-interval '22' day

日期（时间）型数据本身是一个数值，可以进行简单的算术运算。interval是关键字，后面的数字表示时间间隔

1.3.2比较算术运算符与比较表达式

运算符	含义	运算符	含义
=	等于	<=>	相等或者都等于空
>	大于	in	是否是某个集合内的值
<	小于	like	匹配模式
>=	大于或者等于	between…and…	是否在某个区间内
<=	小于或者等于	is null	是否都为空
<>、!=	不等于	regexp	正则表达式匹配模式

比较表达式用于Where子句及流程控制语句中，过滤复合搜索条件的行。

1.3.3逻辑算术运算符与逻辑表达式

运算符	含义
and 或 &&	逻辑与
or 或 ||	逻辑或
not 或！	逻辑非
xor	异或

1.3.4运算符的优先级

当一个复杂的表达式中包含多种运算符时，运算符的优先顺序将决定表达式的计算和比较顺序。在MySQL中，运算符的优先等级从高到低的顺序如下。

+(正)、–(负)、~(位求反)
*(乘)、/(除)、%(求余)
+(加)、–(减)
=、>、<、>=、<=、<>、!=、!>、!<比较运算符
^(位异或)、&(位与)、|(位或)
NOT
AND、BETWEEN…AND…、IN、LIKE、OR
=(赋值)

1.4 begin-end语句块

BEGIN…END可以定义SQL语句块，这些语句块作为一组语句执行，并且允许语句嵌套。

其基本语法结构如下：

BEGIN语句序列
END

局部变量的声明应该位于语句块开始的部分，在语句块内部声明的局部变量，作用域仅为语句块内部。

其中，开始标签与结束标签必须相同。

在MySQL中，单独使用begin-end语句没有意义，只有将begin-end放在存储过程、函数、触发器以及事件等内部才有意义。

1.5条件控制语句

条件控制语句分为两种，一种是if语句，另一种是case语句。

1.5.1 if语句

IF-THEN-ELSE语句用于控制程序根据不同的条件执行不同的操作。其基本语法格式如下：

IF 条件表达式1 THEN 语句序列1
[ELSEIF 条件表达式2 THEN 语句序列2]…
[ELSE 语句序列n]
END IF

IF…else语句可以嵌套使用，而且嵌套层数没有限制。

-- 判断输入的两个参数x和y的大小关系，并将结构放在变量result中。
IF x>y THENSET result=' x大于y ';
ELSEIF x=y THENSET result= ' x等于y ';
ELSESET result= ' x小于y ';
END IF;

下面这段代码定义了一个函数，函数用参数来表示要查询的课程代码，如果此门课程已经开设过，则返回这门课程的平均分，否则返回0。调用函数时，注意语法和参数格式。

delimiter $$
create function find_course(cno char(6)) returns float
reads sql data
begindeclare num int;declare avg_score float;select count(*) into num from score where Course_no=cno;if(num>0)thenselect avg(score) into avg_score from score where Course_no=cno;elseset avg_score=0;end if;
return avg_score;
end$$
delimiter ;

1.5.2 case语句

Case语句用于实现比if语句分支更为复杂的条件判断，其语法格式如下：

CASE 表达式WHEN 值1 THEN 语句块1WHEN 值2 THEN 语句块2…[ELSE 语句序列n]
END CASE

其中，测试表达式的值必须与测试值的数据类型相同，测试表达式可以是局部变量，也可以是表中的字段变量名，还可以是用运算符连接起来的表达式。

执行case表达式时，会按顺序将测试表达式的值与测试值逐个进行比较，只要发现一个相等，则执行相应的语句块，case语句执行结束。否则，将执行else中的语句，case表达式执行结束。

在case表达式中，若同时有多个测试值与测试表达式的值相同，则只返回第一个与测试表达式值相同的when子句后的结果。

-- 【例9-13】显示“成绩”表中的数据，并使用case语句将课程号替换为课程名。
delimiter $$
create function get_course_name(c_no char(10)) returns varchar(16)
no sql
begindeclare name varchar(16);case c_nowhen '010001' then set name='大学计算机基础';when '010002' then set name='数据结构';when '010003' then set name='数据库原理';when '010004' then set name='操作系统原理';else set name='非计算机专业课';end case;return name;
end$$
delimiter ;

1.6循环语句

MySQL中提供了三种循环语句：

1. while
2. repeat
3. loop

用iterate语句及leave语句用来循环的内部控制。

1.6.1 while语句

利用循环语句while可以有条件地重复执行一个SQL语句或语句块。其语法格式如下：

[循环标签:]while 条件表达式 do循环体;
end while[循环标签];-- 【例】使用WHILE语句创建一个执行5次的循环。
DECLARE a INT DEFAULT 5;
WHILE  a > 0 DOSET a = a-1;
END WHILE;

一些例子

-- 【例9-14】用while语句计算2的n次方。
delimiter $$
create function pow_2(n int) returns int
no sql
begindeclare my_result int default 1;declare start int default 0;
while start<n doset my_result=my_result*2;set start=start+1;
end while;return my_result;
end$$
delimiter ;-- 【例9-15】改写例9-14，要求使用iterate和leave语句。
delimiter $$
create function pow1_2(n int) returns int
no sql
begindeclare my_result int default 1;time_num:while true doset my_result=my_result*2;set n=n-1;if n>0 theniterate time_num;end if;leave time_num;end while time_num;return my_result;
end$$
delimeter ;

1.6.2 repeat语句

repeat语句与while语句正好相反，当条件当假时，反复执行循环体，直到条件为真，退出循环。语法如下：

[循环标签:]repeat循环体
until 条件表达式
end repeat[循环标签];-- 【例】用REPEAT语句创建一个执行5次的循环。
DECLARE a INT DEFAULT 5;
REPEATa = a-1;
UNTIL a < 1 
END REPEAT;-- 【例9-16】用repeat改写例9-13。
delimiter $$
create function pow2_2(n int) returns int
no sql
begin
declare my_result int default 1;
declare start int default 0;
repeatset my_result=my_result*2;set start= start+1;
until start=n
end repeat;
return my_result;
end$$
delimiter ;

1.6.3 loop语句

loop语句是一种特殊的循环，本身没有停止循环的语句，所以，loop语句必须借助leave语句跳出循环，语法格式如下：

[循环标签:]loop循环体if 条件表达式 thenleave [循环标签];end if;
end loop;-- 【例】使用LOOP语句创建一个执行5次的循环。
SET a = 5;
label1: LOOPSET a = a-1;IF a<1 THENLEAVE label1;END IF;
END LOOP label1;-- 【例9-17】使用loop语句实现2的n次方的功能。
delimiter $$
create function pow3_2(n int) returns int
no sql
begin
declare my_result int default 1;
declare start int default 0;
time_2:loopset my_result=my_result*2;set start= start+1;
if(start=n) thenleave time_2;
end if;
end loop;
return my_result;
end$$
delimiter ;

1.7重置命令结束标记

在语句块中，通常存在多条语句，语法规定，有很多语句结束时需要结束标记，而这种结束标记与整个语句块结束标记如果相同，势必造成冲突，使得语句块被拆分。

解决方法是设置一个不同的结束标记，在写语句块之前设置好，在语句块结束后，重新设置回来。语法记为：

delimiter 命令结束标记这里的命令结束标记被使得较多的是：$$、;;、//。

例如在窗口中输入：

delimiter //
select * from student where stu_name like '张_' //

此时语句的结束标记变为//，而接下来的执行语句delimiter ；可以将结束标志重新置为;。

delimiter ;
select * from student where stu_name like '张_' ;

1.8注释

注释是程序代码中不被执行的文本字符串，用于对代码进行说明或进行整段的部分语句。

MySQL支持以下3中注释方式：

1. #：从#到行尾都是注释内容。
2. --：从–到行尾都是注释内容，注意–后面必须有一个空格。
3. /* */：/* 和 */之间的所有内容都是注释，通常用于多行注释。

2.系统函数