MySQL高级SQL语句--数据库函数、存储过程

一、数据库函数

MySQL 数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出。MySQL内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数。

1.1 数学函数

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

数学函数	描述
abs(x)	返回 x 的绝对值
rand( )	返回 0 到 1 的随机数（0<=x<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…)	返回集合中最小的值

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

mysql> select rand(),rand(),rand();
+---------------------+---------------------+--------------------+
| rand()              | rand()              | rand()             |
+---------------------+---------------------+--------------------+
| 0.43138670868369444 | 0.19065552129471258 | 0.6591175111561245 |
+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)

mysql> select mod(4,2),mod(11,2),mod(10,4);
+----------+-----------+-----------+
| mod(4,2) | mod(11,2) | mod(10,4) |
+----------+-----------+-----------+
|        0 |         1 |         2 |
+----------+-----------+-----------+
1 row in set (0.00 sec)

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

mysql> select round(3.1),round(3.9);
+------------+------------+
| round(3.1) | round(3.9) |
+------------+------------+
|          3 |          4 |
+------------+------------+
1 row in set (0.00 sec)

mysql> select round(3.1445,2),round(1.1564,2);
+-----------------+-----------------+
| round(3.1445,2) | round(1.1564,2) |
+-----------------+-----------------+
|            3.14 |            1.16 |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> select sqrt(4),sqrt(9),sqrt(10);
+---------+---------+--------------------+
| sqrt(4) | sqrt(9) | sqrt(10)           |
+---------+---------+--------------------+
|       2 |       3 | 3.1622776601683795 |
+---------+---------+--------------------+
1 row in set (0.00 sec)

mysql> select truncate(3.1415926,3);
+-----------------------+
| truncate(3.1415926,3) |
+-----------------------+
|                 3.141 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select ceil(3.1),ceil(3.9);
+-----------+-----------+
| ceil(3.1) | ceil(3.9) |
+-----------+-----------+
|         4 |         4 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> select floor(3.1),floor(3.9);
+------------+------------+
| floor(3.1) | floor(3.9) |
+------------+------------+
|          3 |          3 |
+------------+------------+
1 row in set (0.00 sec)

mysql> select greatest(11,22,33),least(11,22,33);
+--------------------+-----------------+
| greatest(11,22,33) | least(11,22,33) |
+--------------------+-----------------+
|                 33 |              11 |
+--------------------+-----------------+
1 row in set (0.00 sec)

1.2 聚合函数

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

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

mysql> select * from mytable;
+----+----------+-----+-------+
| id | name     | age | hobby |
+----+----------+-----+-------+
|  1 | zhangsan | 30  |     1 |
|  2 | lisi     | 40  |     2 |
|  3 | wangwu   | 30  |     1 |
|  4 | zhaoliu  | 40  |     2 |
|  5 | qiqi     | 50  |     2 |
|  6 | tom      | 27  |     3 |
|  7 | oooo     | 41  |     3 |
|  8 | oooooo   | 36  |     1 |
|  9 | jerry    | 44  |     5 |
+----+----------+-----+-------+
9 rows in set (0.00 sec)

mysql> select avg(age) from mytable;
+-------------------+
| avg(age)          |
+-------------------+
| 37.55555555555556 |
+-------------------+
1 row in set (0.00 sec)

mysql> select count(age) from mytable;
+------------+
| count(age) |
+------------+
|          9 |
+------------+
1 row in set (0.00 sec)

mysql> select min(age) from mytable;
+----------+
| min(age) |
+----------+
| 27       |
+----------+
1 row in set (0.00 sec)

mysql> select max(age) from mytable;
+----------+
| max(age) |
+----------+
| 50       |
+----------+
1 row in set (0.00 sec)

mysql> select sum(age) from mytable;
+----------+
| sum(age) |
+----------+
|      338 |
+----------+
1 row in set (0.00 sec)

聚合函数中最常用到的是 count()函数，用于统计表中的总记录数

1.3 字符串函数

常用函数不仅包括数学函数和聚合函数，还包含字符串函数，MySQL 为字符串的相关操作设计了丰富的字符串函数。
常用的字符串函数如表所示

字符串函数	描述
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 中的字符串 y
strcmp(x,y)	比较 x 和 y，返回的值可以为-1,0,1
substring(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
reverse(x)	将字符串 x 反转

mysql> select length('abcde'),length('fgh');
+-----------------+---------------+
| length('abcde') | length('fgh') |
+-----------------+---------------+
|               5 |             3 |
+-----------------+---------------+
1 row in set (0.00 sec)

mysql> select trim(' abc '),trim(' a b c ');  'trim只能过滤开头和结尾的空格'
+---------------+-----------------+
| trim(' abc ') | trim(' a b c ') |
+---------------+-----------------+
| abc           | a b c           |
+---------------+-----------------+
1 row in set (0.01 sec)

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

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

mysql> select left('abcdef',3),right('abcdef',3);
+------------------+-------------------+
| left('abcdef',3) | right('abcdef',3) |
+------------------+-------------------+
| abc              | def               |
+------------------+-------------------+
1 row in set (0.00 sec)

mysql> select repeat('abc',3);
+-----------------+
| repeat('abc',3) |
+-----------------+
| abcabcabc       |
+-----------------+
1 row in set (0.00 sec)

mysql> select concat('a',space(3),'b');
+--------------------------+
| concat('a',space(3),'b') |
+--------------------------+
| a   b                    |
+--------------------------+
1 row in set (0.00 sec)

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

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

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)

1.4 日期时间函数

MySQL 也支持日期时间处理，提供了很多处理日期和时间的函数。
一些常用的日期时间函数如表所示

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

mysql> select curdate(),curtime(),now();
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2020-08-25 | 18:52:29  | 2020-08-25 18:52:29 |
+------------+-----------+---------------------+
1 row in set (0.00 sec)

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

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

mysql> select hour(now()),minute(now()),second(now());
+-------------+---------------+---------------+
| hour(now()) | minute(now()) | second(now()) |
+-------------+---------------+---------------+
|          18 |            55 |            17 |
+-------------+---------------+---------------+
1 row in set (0.00 sec)

mysql> select dayofweek(now()),dayofmonth(now()),dayofyear(now());
+------------------+-------------------+------------------+
| dayofweek(now()) | dayofmonth(now()) | dayofyear(now()) |
+------------------+-------------------+------------------+
|                3 |                25 |              238 |
+------------------+-------------------+------------------+
1 row in set (0.00 sec)

二、存储过程

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

2.1 存储过程简介

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

●存储过程在数据库中创建并保存，它不仅仅是SQL语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、在不同的应用程序或平台上执行相同的函数等等。如果了解一些编程语言，可能会发现存储过程更像是面向对象方法的模拟。

●存储过程的优点：

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

2.2 创建存储过程

使用 CREATE PROCEDURE 语句创建存储过程
●语法格式

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

●存储过程的名称应该尽量避免选取与MySQL内置的函数或者字段相同的名称，否则会发生错误。存储过程可以添加参数，具有自己的参数列表。参数包括参数名和其对应的类型。存在多个参数时，参数列表之间用逗号进行分隔。创建存储过程的时候可以不使用参数，但是括号要存在，也可以有一个或多个参数。

●MySQL的参数分为：输入参数、输出参数和输入/输出参数，分别用IN、OUT和INOUT 三个关键字表示。其中，输入参数可以传递给一个存储过程；输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

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

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

mysql> DELIMITER $$
  '省略存储过程其他步骤'
mysql> DELIMITER ;	'分号前有空格'

●要创建存储过程，必须要具有 CREATE ROUTINE 权限

●使用SHOW PROCEDURE STATUS命令查看数据库中存在哪些存储过程。如果要查看某个存储过程的具体信息，则可以使用SHOW CREATE PROCEDURE <存储过程名称>
例如，通过存储过程查询mytable表的三条数据，存储过程是不带参数的

mysql> delimiter $$
mysql> create procedure mytmp()
    -> begin
    -> select id,name,age from mytable limit 3;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call mytmp();
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 30  |
|  2 | lisi     | 40  |
|  3 | wangwu   | 30  |
+----+----------+-----+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

通过存储过程查询mytable表中某一条记录，存储过程是带参数的

mysql> delimiter $$
mysql> create procedure mycz(in myname varchar(20))
    -> begin
    -> select id,name,age from mytable where name=myname;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call mycz('lisi');
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | lisi | 40  |
+----+------+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure mycz;  '查看mycz存储过程的具体信息'
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                                                                                 | Create Procedure                                                                                                                       | character_set_client | collation_connection | Database Collation |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| mycz      | 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_SUBSTITUTION | CREATE DEFINER="root"@"localhost" PROCEDURE "mycz"(in myname varchar(20))
begin
select id,name,age from mytable where name=myname;
end | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> show procedure status where db='mydatabase';  '查看库中存在哪些存储过程'
+------------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db         | Name   | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mydatabase | mycz   | PROCEDURE | root@localhost | 2020-08-25 19:21:01 | 2020-08-25 19:21:01 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| mydatabase | myrole | PROCEDURE | root@localhost | 2020-08-25 19:07:28 | 2020-08-25 19:07:28 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| mydatabase | mytmp  | PROCEDURE | root@localhost | 2020-08-25 19:15:44 | 2020-08-25 19:15:44 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.00 sec)

2.3 修改存储过程

●存储过程在创建之后，随着开发业务的不断推进，业务需求难免有所调整，相应的存储 过程也会发生变动，这个时候就需要修改存储过程。存储过程的修改分为特征的修改和业务内容的修改。特征的修改可以使用 ALTER PROCEDURE 来实现

●存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储 过程

●语法结构

ALTER PROCEDURE <过程名> [ <特征> … ]

2.4 删除存储过程

●存储过程创建之时是存储到 MySQL 数据库中的，当程序不在调用这个存储过程时，也就意味这个存储过程被废弃了，废弃的存储过程需要从数据库中将其删除。使用 DROP PROCEDURE 语句即可删除存储过程
●语法格式

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

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

mysql> mysql> drop procedure mycz;
Query OK, 0 rows affected (0.01 sec)

mysql> call mycz('lisi');
ERROR 1305 (42000): PROCEDURE mydatabase.mycz does not exist

需要注意的是：存储过程名称后面没有参数列表，也没有括号。在删除之前，必须确认 该存储过程没有任何依赖关系，否则会导致与之关联的存储过程无法运行。