MySQL 函数 第6章MySQL编程基础

1

2

3

4

课程封面

#池州学院红队Write up

##0x00课程名称：

1.数据库原理与应用

Principle and Application of Database

2.数据库原理与应用2022年春-池州学院

ChiZhou University: Database System （2022 Spring）

（课程名称排名不分先后：中文优先级大于英文，老师发给我的课件上的翻译大于我感觉的翻译）

池州学院：大数据与人工智能学院

刘老师的作业要求：提交时，一个题目、一个命令、一个截图

##0x01目标与要求：

本章主要学习MySQL的常量、变量、函数、表达式、关键词、注释等编程基础知识，自定义函数的创建与使用和程序基本控制结构。

了解：MySQL的脚本、注释语句；

重点：自定义函数创建与应用，程序三种基本结构；

难点：用MySQL编程解决实际问题

##0x02内容提纲：

1.编程基础知识

2.自定义函数

3.流程控制结构

##0x03：编程基础知识

##0x04注释、定界符与语句块

1.注释：不会被执行，用于解释说明，便于维护代码。

单行注释符号（--或#）放在行末，注释本行；

多行注释符号(/* …… */），须成对使用，可以注释多行；

2.定界符：

MySQL默认定界符为分号(;)

修改方法用delimiter命令（格式为delimiter 新的符号）

3.语句块：由若干条语句组成的程序代码单元，在逻辑上是一个整体，全部被执行或不会被执行。

语句块定义：

begin

语句序列（可包含多条语句）

and；

##0x05变量的定义及使用

1.变量三要素：变量名、变量类型和变量的值；

2.变量名不能与系统中的关键词或函数名同名；

3.每个变量是一个容器，用于保存数据；

4.变量分类（从作用域和生存期）：用户会话变量、局部变量和系统变量；

##0x06用户会话变量（@变量名）

每个用户拥有独立会话空间，用户会话变量互不干扰；

与服务器的连接断开则空间释放，会话变量生存期结束；

不用先定义，直接赋值即可使用；

赋值方法(用set 或select):

set 格式：set @变量名1=值1， @变量名2=值2 ；

select：

格式1：select @变量名1 : =值1, @变量名2：=值2；

格式2: select 值1 into @变量名1；

##0x07局部变量（变量名）

是指定义在某个局部范围内的变量，如语句块中定义的变量；

作用范围仅在定义的局部内；

必须先定义再赋值和使用；

定义方法：declare

格式： declare 变量名 1[，变量名2] 数据类型（n) default 值;

例定义局部变量myvar，数据类型为int，默认值为100。

declare myvar int default 100;

给myvar赋值为77

set myvar=77;

##0x08系统变量（@@变量名）

由MySQL自动创建的变量，前缀符号为“@@”；

分类：全局系统变量和用户系统变量；

全局系统变量影响服务器整体操作，初始值由数据库启动时自动初始化；

用户系统变量只影响各个用户连接，由数据库服务器针对每次连接创建的变量，并复制一份全局变量的值作为会话变量的初始值；

查看方法：

全局系统变量：show global variables;

用户系统变量：show session variables|show variables;

查看具体的变量：select @@系统变量名； 如select @@ verison

##0x09查看变量值

系统变量：

全局系统变量：show global variables;

用户系统变量：show session variables|show variables;

查看具体的变量：

select @@系统变量名；

如select @@ verison;

select @@global.变量名;

select @@session.变量名；

会话变量：select @变量名；

局部变量：select 变量名; （在语句块中使用）

##0x10运算符与表达式

MySQL表达式是由运算符将常量、变量、字段名和函数等组合连接而成的有意义的字符序列。一个表达式通常可以得到一个值。

常按运算符分类：算术表达式、关系表达式、逻辑表达式、位运算表达式；

运算符优先级；

内置函数；数学函数、字符串函数、时间日期函数

PPT(17-18):同一页面既成为“时间日期函数”和“系统信息函数”，可能是老师的隐写，当时用意我还没有体会到，sorry。

其他函数：

1.控制流函数

IF（expr,v1,v2）

翻译：如果表达式expr的值为真,则返回vl的值,否则返回v2的值

示例：IF(50

IFNULL(v1,v2)

翻译：如果表达式expr的值不是NULL,则返回vl,否则返回v2

示例：IFNULL(1/0, 4)的值为4

2.数据类型转换函数

CAST(Value AS type)

翻译：将Value的数据类型转换为type类型

示例：CAST(3.14 AS CHAR(4))的值为字符串'3.14'

CONVERT(Value,type)

示例：CONVERT('3.14',FLOAT)的值为3.14 (数值)

相同点：都是进行数据类型转换，实现的功能基本等同

补充（事实上就是re，CTF中常用，机器学习中的LSTM也常用，升华：温故而知新，可以为师矣）：

不同点：两者的语法不同，cast(value as type) 、 convert(value,type)

所支持的类型如下：

二进制，同带binary前缀的效果 : BINARY

字符型，可带参数 : CHAR()

日期 : DATE

时间: TIME

日期时间型 : DATETIME

浮点数 : DECIMAL

整数 : SIGNED

无符号整数 : UNSIGNED

##0x11：自定义函数

1.创建自定义函数

创建函数必须具有create routine权限(alter routine和execute权限被自动授予给创建者）;

创建一个spuser用户并对它赋予create procedure 权限

grant create routine on tempdb.* to “spuser”@'localhost' identified by '123456';

Tips&Flag:spuser是什么意思？如何拆解这个单词，是sp - user吗？sp英文全称是什么？音标你能写出来吗？代表了什么用户组？有哪些权限？

MySQL创建自定义函数时，需要打开数据库，但可以创建非当前数据库的自定义函数（数据库名.函数名）;

安全参数默认没有开启

log_bin_trust_function_creators 默认为0，是不允许function的同步；

>show variables like '%fun%' ;

>set global log_bin_trust_function_creators=1;

语法格式：

create function func_name([[in | out | inout]func_parameter type[,…]])

returns return_type

[characteristic…]

begin

function_body_statements;

return[return_values];

end;

例：定义一个求长方形面积的函数

delimiter //咱就是说，俺叫“定界符”

create function func_area(long1 int,wide1 int)

returns int

begin

return long1 * wide1;

end //

delimiter ; //你到底有没有记住我呜呜呜，我叫“定界符”，艺名“迪丽米特儿”，英 /d??l?m?t?(r)/ 美 /d??l?m?t?r/

函数定义说明（一） ：

[in | out | inout] func_parameter type :函数参数及类型列表。in表示输入参数， out表示输出参数，inout表示输入输出参数，func_parameter表示参数名， type参数类型。

returns return_type：函数返回值类型。

begin…end: 函数体起止符。由实现任务的语句序列组成，函数体内要有“return return_values；”语句，表示函数返回值表达式。

函数定义说明（二）：

characteristics：用于指定函数的特征参数，由以下一种或几种选项组合而成：

language sql 默认为sql

|[not] deterministic 对同样的输入参数是否会产生相同的结果

|{ contains sql|no sql|reads sql data|modifies sql data }

CONTAINS SQL表??程序不包含读或者写数据的语句

NO SQL 表??程序不包含sql

READS SQL DATA 表??程序包含读数据的语句，但是不包含写数据的语句

MODIFIES SQL DATA 表??程序包含写数据的语句。

|sql security {definer|invoker } definer表示只有定义者才能执行；

invoker表示拥有权的调用者才可以执行，默认为definer；

|comment ‘string‘ 注释信息，用来描述存储函数；

例：定义函数实现给定一个学号从student表中读取此学生的性别；

delimiter &&

create function func_sno(s_no varchar(11))

returns char(2)

READS SQL DATA

begin

return (select sex from student

where studentno =s_no);

end &&

delimiter ;

2.调用自定义函数

在MySQL系统中，因为函数和数据库相关，如果要调用函数时，需要打开相应的数据库或指定数据库名称。存储函数的调用与MySQL内部函数的调用方式相同，用select实现。

示例：

select func_sno('19126113307');

select func_area(4,5);

3.管理自定义函数

3.1查看函数

方法一：查看当前数据库中所有的自定义函数

show function status;

show function status like ‘表达式’;

方法二：查看指定数据库（如teaching）中的所有的自定义函数。可使用SQL语句。

mysql> select name from MySQL.proc

where db = 'teaching' and type='function' ;

方法三：可以查看指定函数名的详细信息。

mysql>show create function 函数名;

方法四：通过函数保存数据库information_schema的表routines。

mysql> select * from information_schema.routines

where routine_name='func_course';

3.2修改函数

alter function 函数名 [characteristic …]; 或者删除重新创建；

例：修改存储函数func_course的定义。将读写权限改为reads sql data，并加上注释信息“find function name”

mysql> alter function func_course

reads sql data

comment ‘find function name’;

3.3删除函数

##0x12 MySQL流程控制语句

概览：

1.IF语句

2.CASE语句

3.WHILE语句

4.LOOP语句

5.repeat语句

6.iterate和leave

MySQL语言编程，可以通过流程控制语句实现程序的顺序、选择和循环三种基本程序结构，解决复杂问题如存储过程、函数和触发器等的编程。

##0x13 IF语句，根据条件不同执行不同的语句。

语法：

if condition then

…

[elseif condition then]

…

[else]

…

End if

例：创建函数exam_if，判断输入参数的值是否为10，如果是则输出1；是否为20，如果是则输出2，当以上条件都不满足时输出3。然后调用函数exam_if

mysql> delimiter //

mysql>

create function exam_if(x int)

returns int

begin

if x=10 then set x=1;

elseif x=20 then set x=2;

else set x=3;

end if;

return x;

end //

mysql> delimiter ;

mysql> select exam_if(77);

##0x14：CASE语句，多分支语句。

格式1：

case 表达式

when value then …

[when value then…]

[else…]

end

格式2：

case

when 条件1 then …

[when 条件2 then…]

[else…]

end

例：创建函数exam_if，判断输入参数的值是否为10，如果是则输出1；是否为20，如果是则输出2，当以上条件都不满足时输出3。然后调用函数exam_if

mysql>delimiter //

mysql>

create function exam_case1(x int)

returns int

begin

case x

when 10 then set x=1;

when 20 then set x=2;

else set x=3;

end case;

return x;

end //

调用函数：select exam_case(17);

##0x15 条件判断函数if()、ifnull()

直接调用MySQL 函数，主要用在SQL语句中进行条件判断；

（1）if()函数。if(condition,v1,v2)函数中condition为条件表达式，当condition的值为true时，函数返回v1的值，否则返回v2的值。

例：从表student中查询学号studentno，入学成绩entrance。成绩大于等于800分，显示“pass!”，否则，显示“bye.”，输出前5条记录。

mysql>select studentno, entrance,if(entrance

=800,'pass','bye! ')

from student limit 5;

（2）ifnull()函数。ifnull(expression, alt_value)函数中，如果expression的值为null，则该函数alt_value的值；否则该函数返回expression的值。

例：

SELECT IFNULL(NULL, "RUNOOB");

SELECT IFNULL("Hello", "RUNOOB");

##0x16 while循环语句

while循环语句执行时首先判断condition条件是否为真，如果是则执行循环体，否则退出循环。

语法：

[begin label: ]while condition do

…

end while [begin label];

例：定义函数exam_while，应用while语句求1到100项的和。

mysql> delimiter //

mysql> create function exam_while(n int)

returns int

begin

declare sum int default 0;

declare m int default 1;

while m< = n do

set sum=sum+m;

set m=m+1;

end while;

return sum;

end //

函数调用：

mysql> delimiter ;

mysql> select exam_while(100);

##0x17 loop循环语句

loop循环语句是没有退出条件语句的循环，一般通过leave语句退出循环体。

语法：

[begin label: ] loop

…

end loop [begin label];

例：定义函数exam_loop，应用loop语句求1~100之和。通过leave语句退出循环并输出结果。

mysql> delimiter //

mysql> create function exam_loop(n int) returns int

begin

declare sum int default 0;

set n=1;

loop_label:loop

set sum=sum+n;

set n=n+1;

if n>100 then

leave loop_label;

end if;

end loop;

return sum;

end //

函数调用：

mysql> delimiter ;

mysql> select exam_loop(100);

##0x18 repeat循环语句

先执行一次循环体，之后判断condition条件是否为真，若为真则退出循环，否则继续执行循环。

语法：

[begin label: ] repeat

…

until condition

end repeat [begin label];

例：求解12+22+32+……+i2

delimiter //

create function sum_i ( ) returns int

begin

declare I int default 1;

declare s int default 0;

repeat

set s=s+(i*10+2);

set i=i+1;

until s>1000

end repeat;

return i-1 ;

end //

delimiter ;

delimiter //

create function sum_ia(x int ) returns int

begin

declare I int default 1;

declare s int default 0;

repeat

set s=s+(i*10+2);

set i=i+1;

until i=x

end repeat;

return s;

end //

delimiter ;

##0x19 leave与iterate语句

语法 ：leave|iterate ;

可以出现在loop、repeat和while语句内；

相同点：都可以中止循环;

区别：leave语句是结束循环; iterate语句是结束本次循环，转向下一次循环。

delimiter //

create function exam_iterate2(n int) returns int

begin

declare sum char(20) default 0;

declare s int default 0;

add_num: while true do

set s=s+1;

if (s%2=0) then

set sum=sum+s;

else

iterate add_num;

end if;

if (s=n) then

leave add_num;

end if;

end while add_num;

return sum;

end //

delimiter ;

本章小结：

1.变量三要素及三种常用变量；

2.自定义函数定义及调用；

3.程序控制流语句（分支与循环）；

##FLAG{你能看到这里，那就是我们的缘分，首先，刘老师2022年4月23日下午15时40分下课时讲到了运算符与表达式，PPT定位在17-18页，后面的内容是我闲暇之余对着老师这一章的课件手敲的，可能会有些错漏，只要你留言，我会查阅资料再进行勘误，我们是不是冠军，不重要，你能不能看懂隐写，才重要，欢迎报考池州学院。这是明着写的隐写，对NLP浅学一下其实就差不多}

#FLAG{大风泱泱，大潮滂滂，五千年文脉涵养了巍巍中华。}

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!