mysql——mysql高级SQL语句之数据库函数与存储过程

最新推荐文章于 2022-10-11 13:45:11 发布

提笔写春秋

最新推荐文章于 2022-10-11 13:45:11 发布

阅读量205

点赞数

分类专栏： mysql 文章标签：存储过程数学函数聚合函数字符串函数日期时间函数

本文链接：https://blog.csdn.net/m0_47161295/article/details/108840228

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

这里写目录标题

1. 数据库函数
1.1 数学函数
2.存储过程

1. 数据库函数

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

1.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…)	返回集合中最小的值

例如，MySQL 数学函数的使用方法，具体操作如下所示。

mysql>select abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
+---------+---------------------+----------+------------+-------------+
| abs(-1) | rand()	| mod(5,3) | power(2,3) | round(1.89) |
+---------+---------------------+----------+------------+-------------+
|	1 | 0.46176527949214474 |	2 |	8 |	2 |
+---------+---------------------+----------+------------+-------------+
1 row in set (0.00 sec)
mysql>select round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
+-----------------+-------------------+-----------+------------+-----------------------+
| round(1.8937,3) | truncate(1.235,2) | ceil(5.2) | floor(2.1) | least(1.89,3,6.1,2.1) |
+-----------------+-------------------+-----------+------------+-----------------------+
|	1.894 |	1.23 |	6 |	2 |	1.89 |
+-----------------+-------------------+-----------+------------+-----------------------+
1 row in set (0.00 sec)

1.2 聚合函数

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

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

例如，MySQL 聚合函数的使用方法，具体操作如下所示。


mysql>select sum(level) as sum_level from player;
+-----------+
| sum_level |
+-----------+
|	18793 |
+-----------+
1 row in set (0.00 sec)


mysql>select max(level) as max_level from player;
+-----------+
| max_level |
+-----------+
|	47 |
+-----------+
1 row in set (0.00 sec)


mysql>select min(level) as min_level from player;
+-----------+
| min_level |
+-----------+
|	1 |
+-----------+
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 字符串函数的使用方法，具体操作如下所示。

mysql>select length('bdqn'), trim(' yellow '), concat('bd', 'qn'), upper('abc'), right('hello', 3);
+----------------+------------------+--------------------+--------------+-------------------+
| length('bdqn') | trim(' yellow ') | concat('bd', 'qn') | upper('abc') | right('hello', 3) |
+----------------+------------------+--------------------+--------------+-------------------+
|	4 | yellow	| bdqn	| ABC	| llo
|
+----------------+------------------+--------------------+--------------+-------------------+
1 row in set (0.00 sec)


mysql>select repeat('kgc', 2), replace('hello', 'll', 'kgc'), strcmp(4, 5), substring('bjbdqn', 4, 2), reverse('hello');
+------------------+-------------------------------+--------------+---------------------------+------------------+
| repeat('kgc', 2) | replace('hello', 'll', 'kgc') | strcmp(4, 5) | substring('bjbdqn', 4, 2) | reverse('hello') |
+------------------+-------------------------------+--------------+---------------------------+------------------+
| kgckgc	| hekgco	|	-1 | dq
| olleh	|
+------------------+-------------------------------+--------------+---------------------------+------------------+
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 日期时间函数的使用方法，具体操作如下所示。

mysql>select	curdate(),	curtime(),	now(),	month('2020-02-09'),	week('2020-02-09'), hour('21:13:53');
+------------+-----------+---------------------+---------------------+--------------------+------------------+
| curdate()	|  curtime() | now()	| month('2020-02-09') | week('2020-02-09') | hour('21:13:53') |
+------------+-----------+---------------------+---------------------+--------------------+------------------+
| 2020-02-09 | 21:14:34    | 2020-02-09 21:14:34 |	2 |	6 |
21 |
+------------+-----------+---------------------+---------------------+--------------------+------------------+
1 row in set (0.00 sec)
mysql>select	minute('21:13:53'),	second('21:13:53'),	dayofweek('2020-02-09'), dayofmonth('2020-02-09'), dayofyear('2020-02-09');

+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
| minute('21:13:53') | second('21:13:53') | dayofweek('2020-02-09') | dayofmonth('2020-02-09') | dayofyear('2020-02-09') |
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
|	13 |	53 |	1 |
9 |	40 |
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
1 row in set (0.00 sec)

因为 MySQL 函数的数量比较多，存在很多使用频率不是很高的函数，所以本章仅列举了一些具有代表性、比较常用的函数。在实际的工作中，需要什么类型和功能的函数可以通过手册去查找，了解实际功能后再使用。

2.存储过程

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

2.1 存储过程简介

MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合。存储过程这个功能是从 5.0 版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 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 <存储过程名称>。
例如，通过存储过程查询 player 表的三条数据，存储过程是不带参数的，具体操作如下所示。

mysql> DELIMITER $$
mysql> CREATE PROCEDURE PlayerRole()
-> BEGIN
-> SELECT id,name,level from player limit 3;
-> END $$
Query OK, 0 rows affected (0.01 sec)


mysql> DELIMITER ;
mysql> call PlayerRole();
+----+--------------+-------+
| id | name	| level |
+----+--------------+-------+
|   1 | 修欧拉卡	|	10 |
|   2 | 起 风了	|	10 |
|   3 | 吊打低V	|	15 |
+----+--------------+-------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

通过存储过程查询 player 表中某一条记录，存储过程是带参数的，具体操作如下所示。

mysql>DELIMITER $$
mysql>CREATE PROCEDURE GetRole(IN inname VARCHAR(16))
->BEGIN
->SELECT id,name,level from player where name=inname;
->END $$
Query OK, 0 rows affected (0.00 sec)


mysql>DELIMITER ;
mysql>call GetRole('shirley');
+------+---------+-------+
| id	| name	| level |
+------+---------+-------+
| 2460 | shirley |	1 |
+------+---------+-------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)

2.3 修改存储过程

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

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

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

2.4 删除存储过程

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

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

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

mysql>DROP PROCEDURE PlayerRole; 
Query OK, 0 rows affected (0.00 sec) 
mysql>CALL PlayerRole;
ERROR 1305 (42000): PROCEDURE test.PlayerRole does not exist

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

提笔写春秋

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql——mysql高级SQL语句之数据库函数与存储过程

这里写目录标题1. 数据库函数1.1 数学函数1.2 聚合函数1.3字符串函数1.4日期时间函数2.存储过程2.1 存储过程简介2.2 创建存储过程2.3 修改存储过程2.4 删除存储过程1. 数据库函数MySQL 数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出。MySQL 内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数。1.1 数学函数数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。常用的数学函数如表
复制链接

扫一扫

专栏目录