MYSQL 5.7 高级SQL语句（3）——数据库函数和存储过程

高级SQL语句

一、数据库函数1、数学函数2、聚合函数3、字符串函数4、日期时间函数二、存储过程1、概述2、简介3、优点4、创建存储过程（1）语法（2）参数（3）过程体（4）DELIMITER 命令5、查看存储过程6、修改存储过程（1）语法7、删除存储过程（1）语法
MySQL 数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效 的输出。MySQL 内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时 间函数。

一、数据库函数

1、数学函数

数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。 常用的数学函数如表所示

数学函数描述abs(x)返回 x 的绝对值rand()返回 0 到 1 的随机数mod(x,y)返回 x 除以 y 以后的余数power(x,y)返回 x 的 y 次方round(x)返回离 x 最近的整数round(x,y)保留 x 的 y 位小数四舍五入后的值sqrt(x)返回 x 的平方根truncate(x,y)返回数字 x 截断为 y 位小数的值ceil(x)返回大于或等于 x 的最小整数floor(x)返回小于或等于 x 的最大整数greatest(x1,x2…)返回集合中最大的值least(x1,x2…)返回集合中最小的值

-2的绝对值

mysql> select abs(-2);+---------+| abs(-2) |+---------+| 2 |+---------+1 row in set (0.00 sec)

0-1的随机数（0<=x<1）

mysql> select rand();+-------------------+| rand()|+-------------------+| 0.403306520919601 |+-------------------+1 row in set (0.00 sec)

可以搭配运算符

mysql> select rand()*100;+-------------------+| rand()*100|+-------------------+| 73.72450341817411 |+-------------------+1 row in set (0.00 sec)

5除以2的余数

mysql> select mod(5,2);+----------+| mod(5,2) |+----------+|1 |+----------+1 row in set (0.00 sec)

2的3次方

mysql> select power(2,3);+------------+| power(2,3) |+------------+|8 |+------------+1 row in set (0.00 sec)

离1.89最近的整数

mysql> select round(1.49);+-------------+| round(1.49) |+-------------+| 1 |+-------------+1 row in set (0.00 sec)mysql> select round(1.5);+------------+| round(1.5) |+------------+|2 |+------------+1 row in set (0.00 sec)

1.893保留小数点后2位，1.896保留小数点后2位，这里会四舍五入

mysql> select round(1.893,2);+----------------+| round(1.893,2) |+----------------+| 1.89 |+----------------+1 row in set (0.00 sec)mysql> select round(1.896,2);+----------------+| round(1.896,2) |+----------------+| 1.90 |+----------------+1 row in set (0.00 sec)

返回平方根

mysql> select sqrt(4);+---------+| sqrt(4) |+---------+| 2 |+---------+1 row in set (0.00 sec)mysql> select sqrt(5);+------------------+| sqrt(5)|+------------------+| 2.23606797749979 |+------------------+1 row in set (0.00 sec)

保留小数点后2位，但truncate函数不会四舍五入

mysql> select truncate(1.896,2);+-------------------+| truncate(1.896,2) |+-------------------+|1.89 |+-------------------+1 row in set (0.00 sec)

返回大于或等于5.2的最小整数

mysql> select ceil(5.2);+-----------+| ceil(5.2) |+-----------+| 6 |+-----------+1 row in set (0.01 sec)

返回小于或等于5.2的最大整数

mysql> select floor(5.2);+------------+| floor(5.2) |+------------+|5 |+------------+1 row in set (0.00 sec)

返回最大值

mysql> select greatest(1,2,3);+-----------------+| greatest(1,2,3) |+-----------------+| 3 |+-----------------+1 row in set (0.00 sec)

返回最小值

mysql> select least(1,2,3);+--------------+| least(1,2,3) |+--------------+|1 |+--------------+1 row in set (0.00 sec)

2、聚合函数

MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。

聚合函数描述avg()返回指定列的平均值count()返回指定列中非 NULL 值的个数min()返回指定列的最小值max()返回指定列的最大值sum(x)返回指定列的所有值之和

返回分数的总和

mysql> select sum(score) from info;+------------+| sum(score) |+------------+| 662.00 |+------------+1 row in set (0.00 sec)

返回分数字段的个数

mysql> select count(score) from info;+--------------+| count(score) |+--------------+|8 |+--------------+1 row in set (0.00 sec)

返回分数的最小值

mysql> select min(score) from info;+------------+| min(score) |+------------+|70.00 |+------------+1 row in set (0.00 sec)

返回分数的最大值

mysql> select max(score) from info;+------------+| max(score) |+------------+|98.00 |+------------+1 row in set (0.00 sec)

返回分数的平均值

mysql> select avg(score) from info;+------------+| avg(score) |+------------+|82.750000 |+------------+1 row in set (0.00 sec)

3、字符串函数

字符串函数描述length(x)返回字符串 x 的长度trim()返回去除指定格式的值concat(x,y)将提供的参数 x 和 y 拼接成一个字符串upper(x)将字符串 x 的所有字母变成大写字母lower(x)将字符串 x 的所有字母变成小写字母left(x,y)返回字符串 x 的前 y 个字符right(x,y)返回字符串 x 的后 y 个字符repeat(x,y)将字符串 x 重复 y 次space(x)返回 x 个空格replace(x,y,z)将字符串 z 替代字符串 x 中的字符串 ystrcmp(x,y)比较 x 和 y，返回的值可以为-1,0,1substring(x,y,z)获取从字符串 x 中的第 y 个位置开始长 度为 z 的字符串reverse(x)将字符串 x 反转

返回abcd的长度，空格也算一个字符

mysql> select length("abcd");+----------------+| length("abcd") |+----------------+|4 |+----------------+1 row in set (0.00 sec)mysql> select length("ab cd");+-----------------+| length("ab cd") |+-----------------+| 5 |+-----------------+1 row in set (0.01 sec)

去掉格式只输出字符串，对比不用trim函数的显示

mysql> select trim(" sheng");+------------------+| trim(" sheng") |+------------------+| sheng|+------------------+1 row in set (0.00 sec)mysql> select " sheng";+----------+| sheng|+----------+|sheng |+----------+1 row in set (0.00 sec)

把abc和def拼接起来

mysql> select concat("abc","def");+---------------------+| concat("abc","def") |+---------------------+| abcdef|+---------------------+1 row in set (0.01 sec)mysql> select concat("abc"," def");+----------------------+| concat("abc"," def") |+----------------------+| abc def|+----------------------+1 row in set (0.00 sec)

还可以结合其他函数，如trim

mysql> select concat("abc",trim(" def"));+----------------------------+| concat("abc",trim(" def")) |+----------------------------+| abcdef |+----------------------------+1 row in set (0.00 sec)

把abc转换为大写字母

mysql> select upper("abc");+--------------+| upper("abc") |+--------------+| ABC|+--------------+1 row in set (0.00 sec)

把ABC转换为小写字母

mysql> select lower("ABC");+--------------+| lower("ABC") |+--------------+| abc|+--------------+1 row in set (0.01 sec)

返回字符串的前3个字母

mysql> select left("abcdefg",3);+-------------------+| left("abcdefg",3) |+-------------------+| abc |+-------------------+1 row in set (0.00 sec)

返回字符串的最后3个字母

mysql> select right("abcdefg",3);+--------------------+| right("abcdefg",3) |+--------------------+| efg|+--------------------+1 row in set (0.00 sec)

把字符串的前3个字母和后3个字母拼接起来

mysql> select concat(left("abcdefg",3),right("abcdefg",3));+----------------------------------------------+| concat(left("abcdefg",3),right("abcdefg",3)) |+----------------------------------------------+| abcefg |+----------------------------------------------+1 row in set (0.00 sec)

重复字符串2次

mysql> select repeat("abc",2);+-----------------+| repeat("abc",2) |+-----------------+| abcabc|+-----------------+1 row in set (0.00 sec)

返回3个空格，显示无法看出几个空格，这里用length函数显示长度

mysql> select length(space(3));+------------------+| length(space(3)) |+------------------+|3 |+------------------+1 row in set (0.00 sec)

用aa替换hello中的ll

mysql> select replace("hello","ll","aa");+----------------------------+| replace("hello","ll","aa") |+----------------------------+| heaao|+----------------------------+1 row in set (0.00 sec)

比较17和18，小于返回-1，等于返回0，大于返回1，只会返回这3个值，它是比较第一位数字

mysql> select strcmp(17,18);+---------------+| strcmp(17,18) |+---------------+|-1 |+---------------+1 row in set (0.00 sec)mysql> select strcmp(18,18);+---------------+| strcmp(18,18) |+---------------+| 0 |+---------------+1 row in set (0.00 sec)mysql> select strcmp(19,18);+---------------+| strcmp(19,18) |+---------------+| 1 |+---------------+1 row in set (0.00 sec)mysql> select strcmp(27,7);+--------------+| strcmp(27,7) |+--------------+| -1 |+--------------+1 row in set (0.00 sec)

返回从字符串中第三个字符开始的4个字符

mysql> select substring("abcdefg",3,4);+--------------------------+| substring("abcdefg",3,4) |+--------------------------+| cdef |+--------------------------+1 row in set (0.00 sec)

字符串反转显示

mysql> select reverse("gfedcba");+--------------------+| reverse("gfedcba") |+--------------------+| abcdefg|+--------------------+1 row in set (0.00 sec)

返回字符串的前3个字符，然后反转输出

mysql> select reverse(left("gfedcba",3));+----------------------------+| reverse(left("gfedcba",3)) |+----------------------------+| efg|+----------------------------+1 row in set (0.00 sec)

先将字符串反转，再输出前3个字符

mysql> select left(reverse("gfedcba"),3);+----------------------------+| left(reverse("gfedcba"),3) |+----------------------------+| abc|+----------------------------+1 row in set (0.00 sec)

4、日期时间函数

字符串函数描述curdate()返回当前时间的年月日curtime()返回当前时间的时分秒now()返回当前时间的日期和时间month(x)返回日期 x 中的月份值week(x)返回日期 x 是年度第几个星期hour(x)返回 x 中的小时值minute(x)返回 x 中的分钟值second(x)返回 x 中的秒钟值dayofweek(x)返回 x 是星期几，1 星期日，2 星期一replace(x,y,z)将字符串 z 替代字符串 x 中的字符串 ydayofmonth(x)计算日期 x 是本月的第几天dayofyear(x)计算日期 x 是本年的第几天

返回年月日

mysql> select curdate();+------------+| curdate()|+------------+| 2020-08-25 |+------------+1 row in set (0.00 sec)

返回当前时间

mysql> select curtime();+-----------+| curtime() |+-----------+| 15:57:33|+-----------+1 row in set (0.00 sec)

返回当前完整时间

mysql> select now();+---------------------+| now() |+---------------------+| 2020-08-25 15:57:45 |+---------------------+1 row in set (0.00 sec)

返回月份

mysql> select month("2020-08-25");+---------------------+| month("2020-08-25") |+---------------------+| 8 |+---------------------+1 row in set (0.00 sec)

返回当前日期是一年中的第几周

mysql> select week("2020-08-25");+--------------------+| week("2020-08-25") |+--------------------+| 34 |+--------------------+1 row in set (0.00 sec)

返回当前时间的小时

mysql> select hour(curtime());+-----------------+| hour(curtime()) |+-----------------+|16 |+-----------------+1 row in set (0.00 sec)

返回当前时间的分钟

mysql> select minute(curtime());+-------------------+| minute(curtime()) |+-------------------+| 3 |+-------------------+1 row in set (0.00 sec)

返回当前时间的秒

mysql> select second(curtime());+-------------------+| second(curtime()) |+-------------------+|56 |+-------------------+1 row in set (0.00 sec)

当前是星期几

mysql> select dayofweek(curdate());+----------------------+| dayofweek(curdate()) |+----------------------+|3 |+----------------------+1 row in set (0.00 sec)

当前日期是本月的第几天

mysql> select dayofmonth(curdate());+-----------------------+| dayofmonth(curdate()) |+-----------------------+|25 |+-----------------------+1 row in set (0.00 sec)

当前日期是今年的第几天

mysql> select dayofyear(curdate());+----------------------+| dayofyear(curdate()) |+----------------------+|238 |+----------------------+1 row in set (0.00 sec)

二、存储过程

1、概述

前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程， 轻松而高效的去完成这个需求，有点类似shell脚本里的函数

2、简介

MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合。存储过程这个功能是从 5.0 版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中 的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一 个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高。 存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。

3、优点

存储过程执行一次后，生成的二进制代码就驻留在缓冲区，之后如果再次调用的话，将
直接调用二进制代码，使得存储过程的执行效率和性能得到大幅提升。存储过程是 SQL 语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。
存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调 用语句，从而可以降低网络负载。存储过程被创建后，可以多次重复调用，它将多条 SQL 封装到了一起，可随时针对 SQL 语句进行修改，不影响调用它的客户端。存储过程可以完成所有的数据库操作，也可以通过编程的方式控制数据库的信息访问权 限

4、创建存储过程

使用 CREATE PROCEDURE 语句创建存储过程

（1）语法

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体> [过程参数[,…] ] 格式 [ IN | OUT | INOUT ] <参数名><类型>

（2）参数

存储过程的名称应该尽量避免选取与 MySQL 内置的函数或者字段相同的名称，否则会 发生错误。存储过程可以添加参数，具有自己的参数列表。
参数包括参数名和其对应的类型。 存在多个参数时，参数列表之间用逗号进行分隔。创建存储过程的时候可以不使用参数，但是括号要存在，也可以有一个或多个参数。 MySQL 的参数分为：输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字表示。其中，输入参数可以传递给一个存储过程；输出参数用于存储过程需要返 回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

（3）过程体

存储过程的主体部分，被称为过程体，包含了在调用时必须执行的 SQL 语句。 这个部分以关键字 BEGIN 开始，以关键字 END 结束。若过程体中只有一条 SQL 语句，则 可以省略 BEGIN-END 标志

（4）DELIMITER 命令

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的，过程体中由多条 SQL 语句构成，每条 SQL 后面都是分号结尾，那么 MySQL 服务器在处理时遇到第一条 SQL 语句就会结束整个过程， 不再去处理后面的 SQL 语句。为了解决这个问题，在创建存储过程时，使用 DELIMITER 命令

改变结束标志$$，原来的;就不起作用了，需要输入$$才会执行语句

mysql> delimiter $$mysql> mysql> select * from info;-> $$+----+-----------+-------+----------+-------+| id | name| score | address| hobby |+----+-----------+-------+----------+-------+|1 | shidapeng | 90.00 | nanjing| 2 ||2 | shangzhen | 80.00 | beijing| 1 ||3 | tangyan | 98.00 | shanghai | 1 ||6 | chengu| 88.00 | nanjing| 1 ||7 | caicai| 70.00 | hangzhou | 1 ||8 | zhaokun | 80.00 | hangzhou | 2 ||9 | xiawenjie | 80.00 | hangzhou | 1 || 10 | zhaobin |NULL | shanghai | 1 || 11 | nannan| 76.00 || 2 |+----+-----------+-------+----------+-------+9 rows in set (0.00 sec)

例1：创建存储过程scoreinfo，功能是查询info表中的前三条记录，显示id，姓名和分数

mysql> delimiter $$mysql> create procedure scoreinfo()-> begin-> select id,name,score from info limit 3;-> end $$Query OK, 0 rows affected (0.01 sec)

调用存储过程用call

mysql> delimiter ;mysql> call scoreinfo();+----+-----------+-------+| id | name| score |+----+-----------+-------+|1 | shidapeng | 90.00 ||2 | shangzhen | 80.00 ||3 | tangyan | 98.00 |+----+-----------+-------+3 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

5、查看存储过程

使用SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程使用SHOW CREATE PROCEDURE <存储过程名称>查看某个存储过程的具体信息

mysql> show create procedure scoreinfo \G;*************************** 1. row *************************** Procedure: scoreinfosql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONCreate Procedure: CREATE DEFINER="root"@"localhost" PROCEDURE "scoreinfo"()beginselect id,name,score from info limit 3;endcharacter_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)ERROR: No query specified

6、修改存储过程

存储过程的修改分为特征的修改和业务内容的修改。特征的修改可以使用 ALTER PROCEDURE 来实现

（1）语法

ALTER PROCEDURE <过程名> [ <特征> … ]存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程

7、删除存储过程

使用 DROP PROCEDURE 语句即可删除存储过程

（1）语法

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>从以上语法结构可以看出，在删除时存储过程的名字是放到最后的，前面可以添加 IF EXISTS 这个关键字，其主要作用是防止因删除不存在的存储过程而引发的错误

mysql> drop procedure scoreinfo;Query OK, 0 rows affected (0.01 sec)mysql> call scoreinfo();ERROR 1305 (42000): PROCEDURE school.scoreinfo does not exist