文章目录
一: 数据库函数
- 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> select abs(-1),abs(-3.14); +---------+------------+ | abs(-1) | abs(-3.14) | +---------+------------+ | 1 | 3.14 | +---------+------------+ 1 row in set (0.00 sec) //取随机数 mysql> select rand(); +---------------------+ | rand() | +---------------------+ | 0.22567010598785253 | +---------------------+ 1 row in set (0.00 sec) mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.9746294514971128 | +--------------------+ 1 row in set (0.00 sec) //取余数 mysql> select mod(10,3); +-----------+ | mod(10,3) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) //x的y次方 mysql> select power(3,2); +------------+ | power(3,2) | +------------+ | 9 | +------------+ 1 row in set (0.00 sec) //保留x 的y 位小数四舍五入后的值,round 返回整数 整数后面一位四舍五入(小数点位) mysql> select round(1.49); +-------------+ | round(1.49) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select round(1.50); +-------------+ | round(1.50) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) mysql> select round(3.147,2); +----------------+ | round(3.147,2) | +----------------+ | 3.15 | +----------------+ 1 row in set (0.00 sec) //sqrt(x)返回 x 的平方根 mysql> select sqrt(9); +---------+ | sqrt(9) | +---------+ | 3 | +---------+ 1 row in set (0.00 sec) mysql> select sqrt(10); +--------------------+ | sqrt(10) | +--------------------+ | 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(1.9); +-----------+ | ceil(1.9) | +-----------+ | 2 | +-----------+ 1 row in set (0.01 sec) //向下取整 mysql> select floor(1.9); +------------+ | floor(1.9) | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) //取最大值,最小值 mysql> select greatest(10,20,30),least(10,20,30); +--------------------+-----------------+ | greatest(10,20,30) | least(10,20,30) | +--------------------+-----------------+ | 30 | 10 | +--------------------+-----------------+ 1 row in set (0.00 sec) //rand mod power sqart btruncate ceil floor 常用
1.2: 聚合函数
-
MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的,这些函数被称作聚合函数。常见的聚合函数如下表 所示。
聚合函数 描述 avg() 返回指定列的平均值 count() 返回指定列中非 NULL 值的个数 min() 返回指定列的最小值 max() 返回指定列的最大值 sum(x) 返回指定列的所有值之和 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)
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('abcd'); +----------------+ | length('abcd') | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) mysql> select length('abcd'); +----------------+ | length('abcd') | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) //trim 不能去除空格,只能过滤首位两端的空格 mysql> select trim(' abcd'); +---------------+ | trim(' abcd') | +---------------+ | abcd | +---------------+ 1 row in set (0.00 sec) mysql> select trim(' ab cd'); +----------------+ | trim(' ab cd') | +----------------+ | ab cd | +----------------+ 1 row in set (0.00 sec) //concat(x,y)将提供的参数 x 和 y 拼接成一个字符串 mysql> select concat('abc','bdqn'); +----------------------+ | concat('abc','bdqn') | +----------------------+ | abcbdqn | +----------------------+ 1 row in set (0.00 sec) mysql> select concat('abc',trim(' bdqn')); +-----------------------------+ | concat('abc',trim(' bdqn')) | +-----------------------------+ | abcbdqn | +-----------------------------+ 1 row in set (0.00 sec) 可以做函数内嵌,这里不能使用两个select语句 //upper转换为大写,lower转换为小写 mysql> select upper('abc'),lower('ABC'); +--------------+--------------+ | upper('abc') | lower('ABC') | +--------------+--------------+ | ABC | abc | +--------------+--------------+ 1 row in set (0.00 sec) //left right 是截取从开头到第几个字符向左/向右截取 mysql> select left('abcdefg',3); +-------------------+ | left('abcdefg',3) | +-------------------+ | abc | +-------------------+ 1 row in set (0.00 sec) mysql> select right('abcdefg',3); +--------------------+ | right('abcdefg',3) | +--------------------+ | efg | +--------------------+ 1 row in set (0.00 sec) mysql> select concat(left('abcdefg',3),right('abcdefg',3)); +----------------------------------------------+ | concat(left('abcdefg',3),right('abcdefg',3)) | +----------------------------------------------+ | abcefg | +----------------------------------------------+ 1 row in set (0.00 sec) //重复字符 mysql> select repeat('abd',2); +-----------------+ | repeat('abd',2) | +-----------------+ | abdabd | +-----------------+ 1 row in set (0.00 sec) //space('3')返回三个空格 mysql> select concat('a',space('3'),'b'); +----------------------------+ | concat('a',space('3'),'b') | +----------------------------+ | a b | +----------------------------+ 1 row in set (0.00 sec) //replace (x,y,z) 把z的内容替换x字符串的y,也就是在x中找y,找到的字符用z替换 mysql> select replace('hello','ll','kgc'); "hello的ll,换成kgc" +-----------------------------+ | replace('hello','ll','kgc') | +-----------------------------+ | hekgco | +-----------------------------+ 1 row in set (0.00 sec) //x<y -1 ; x>y ;x=y 0 mysql> select strcmp(7,7); +-------------+ | strcmp(7,7) | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) mysql> select strcmp(7,6); +-------------+ | strcmp(7,6) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select strcmp(5,6); +-------------+ | strcmp(5,6) | +-------------+ | -1 | +-------------+ 1 row in set (0.00 sec) mysql> select strcmp(12,3); +--------------+ | strcmp(12,3) | +--------------+ | -1 | +--------------+ 1 row in set (0.00 sec) mysql> select strcmp(12,11); +---------------+ | strcmp(12,11) | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) "只比较第一个字符,十位一样的话,比第二位,位数不一样,依次比" //从第三个数开始,往后数4个输出 mysql> select substring('abcdefg',3,4); +--------------------------+ | substring('abcdefg',3,4) | +--------------------------+ | cdef | +--------------------------+ "substring(完整字符串,起始位置,长度) //reverse 倒过来显示 mysql> select reverse('abc'); +----------------+ | reverse('abc') | +----------------+ | cba | +----------------+ 1 row in set (0.00 sec) mysql> select upper(reverse ('hello')); +--------------------------+ | upper(reverse ('hello')) | +--------------------------+ | OLLEH | +--------------------------+ 1 row in set (0.00 sec)
1.4: 日期时间函数
-
MySQL 也支持日期时间处理,提供了很多处理日期和时间的函数。一些常用的日期时间函数如下表所示。
表 5-10 日期时间函数
字符串函数 描述 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(); //查看现在日期
+------------+
| curdate() |
+------------+
| 2020-08-25 |
+------------+
1 row in set (0.00 sec)
mysql> select curtime(); //查看现在时间
+-----------+
| curtime() |
+-----------+
| 19:28:59 |
+-----------+
1 row in set (0.00 sec)
mysql> select now(); //查看现在包括日期和时间
+---------------------+
| now() |
+---------------------+
| 2020-08-25 19:29:27 |
+---------------------+
1 row in set (0.00 sec)
mysql> select month('2020-08-25'); //查看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()) |
+-----------------+
| 19 |
+-----------------+
1 row in set (0.00 sec)
mysql> select minute('19:29:27'); //查看19:29:27中的分
+--------------------+
| minute('19:29:27') |
+--------------------+
| 29 |
+--------------------+
1 row in set (0.01 sec)
mysql> select second('19:29:27'); //查看19:29:27中的秒
+--------------------+
| second('19:29:27') |
+--------------------+
| 27 |
+--------------------+
1 row in set (0.00 sec)
mysql> select dayofweek('2020-08-25'); //计算2020-08-25是本周的第几天
+-------------------------+
| dayofweek('2020-08-25') |
+-------------------------+
| 3 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select dayofmonth('2020-08-25'); //计算2020-08-25是本月的第几天
+--------------------------+
| dayofmonth('2020-08-25') |
+--------------------------+
| 25 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select dayofyear('2020-08-25'); //计算2020-08-25是本年的第几天
+-------------------------+
| dayofyear('2020-08-25') |
+-------------------------+
| 238 |
+-------------------------+
1 row in set (0.00 sec)
1.5: 存储过程
- 在实际的数据库应用中,有些数据库操作可能会非常复杂,可能会需要多条 SQL 语句一起去处理才能够完成,这时候就可以使用存储过程, 轻松而高效的去完成这个需求。
- MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合。存储过程这个功能是从 5.0 版本才开始支持的,它可以加快数据库的处理速度,增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一个指定的名称存储起来,这个过程经编译和优化后存储在数据库服务器中。当需要使用该存 储过程时,只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译,然后再去执行,跟存储过程一对比,明显存储过程在执行上速度更快,效率更高。
- 存储过程在数据库中创建并保存,它不仅仅是 SQL 语句的集合,还可以加入一些特殊的控制结构,也可以控制数据的访问方式。存储过程的应用范围很广,例如封装特定的功能、 在不同的应用程序或平台上执行相同的函数等等。如果了解一些编程语言,可能会发现存储过程更像是面向对象方法的模拟。
1.5.1: 存储过程的优点
- 存储过程执行一次后,生成的二进制代码就驻留在缓冲区,之后如果再次调用的话,将 直接调用二进制代码,使得存储过程的执行效率和性能得到大幅提升。
- 存储过程是 SQL 语句加上控制语句的集合,有很强的灵活性,可以完成复杂的运算。
- 存储过程存储在服务器端,客户端调用时,直接在服务器端执行,客户端只是传输的调 用语句,从而可以降低网络负载。
- 存储过程被创建后,可以多次重复调用,它将多条 SQL 封装到了一起,可随时针对 SQL语句进行修改,不影响调用它的客户端.
- 存储过程可以完成所有的数据库操作,也可以通过编程的方式控制数据库的信息访问权 限。
1.5.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 ; //分号前有空格
-
//格式 #定义存储过程 delimiter $$ create procedure存储过程名(in 参数名 参数类型) begin #定义变量 declare变量名变量类型 #变量赋值 set 变量名 = 值 sql语句1; sql语句2; .. sql语句n; end $$ delimiter ; #调用存储过程 call存储过程名(实际参数); #查询存储过程 show procedure status where db='数据库"; #删除存储过程 drop procedure存储过程名;
1.5.3: 实操创建存储过程
-
创建简单存储过程
//环境 mysql> select * from accp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 85.00 | | 5 | zhaoliu | 98 | 0 | 98.00 | | 6 | tianqi | 100 | 10 | 77.00 | | 8 | NULL | 22 | 2 | 78.00 | +----+----------+-----+-------+-------+ mysql> delimiter $$ mysql> create procedure myrole() //创建存储过程,名字为myrole -> begin -> select name,score from accp; -> end $$ Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call myrole(); +----------+-------+ | name | score | +----------+-------+ | wangwu | 88.00 | | lisi | 90.00 | | wangwu | 88.00 | | zhangsan | 85.00 | | zhaoliu | 98.00 | | tianqi | 77.00 | | NULL | 78.00 | +----------+-------+ 7 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> show procedure status where db='school'; //查看存储过程状态 +--------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +--------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | school | myrole | PROCEDURE | root@localhost | 2020-08-25 20:00:28 | 2020-08-25 20:00:28 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci | +--------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec)
-
创建中阶存储过程(传参)
mysql> delimiter $$ mysql> create procedure myschool(in my_name varchar(10)) -> begin -> select name score from accp where name=my_name; -> end$$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> show procedure status where db='school'; +--------+----------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +--------+----------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | school | myrole | PROCEDURE | root@localhost | 2020-08-25 20:00:28 | 2020-08-25 20:00:28 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci | | school | myschool | PROCEDURE | root@localhost | 2020-08-25 20:07:41 | 2020-08-25 20:07:41 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci | +--------+----------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 2 rows in set (0.00 sec) mysql> call myschool('zhangsan'); //调用myschool存储过程,并传参zhangsan +----------+ | score | +----------+ | zhangsan | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
-
创建高阶存储过程(变量)
mysql> delimiter $$ mysql> create procedure myupdate(in my_score decimal(5,2)) -> begin -> declare hob int(3); -> if my_score >= 80 then -> set hob = 1; -> else -> set hob = 2; -> end if; -> update accp set score=my_score,hobby=hob where name='zhangsan'; -> end$$ Query OK, 0 rows affected (0.06 sec) //修改名为张三的分数和hobby,修改zhangsan的hobby=2,score=75 mysql> delimiter ; mysql> call myupdate(75); Query OK, 1 row affected (0.06 sec) //75<80 hob=2=hobby score=my_score=75 mysql> select * from accp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 2 | 75.00 | | 5 | zhaoliu | 98 | 0 | 98.00 | | 6 | tianqi | 100 | 10 | 77.00 | | 8 | NULL | 22 | 2 | 78.00 | +----+----------+-----+-------+-------+ 7 rows in set (0.00 sec) mysql> call myupdate(85); Query OK, 1 row affected (0.00 sec) //85>80 hob=1=hobby score=my_score=85 修改zhangsan的hobby=1,score=85 mysql> select * from accp; +----+----------+-----+-------+-------+ | id | name | age | hobby | score | +----+----------+-----+-------+-------+ | 1 | wangwu | 18 | 2 | 88.00 | | 2 | lisi | 28 | 1 | 90.00 | | 4 | wangwu | 30 | 3 | 88.00 | | 3 | zhangsan | 20 | 1 | 85.00 | | 5 | zhaoliu | 98 | 0 | 98.00 | | 6 | tianqi | 100 | 10 | 77.00 | | 8 | NULL | 22 | 2 | 78.00 | +----+----------+-----+-------+-------+ 7 rows in set (0.00 sec)
1.5.4: 修改存储过程
-
存储过程在创建之后,随着开发业务的不断推进,业务需求难免有所调整,相应的存储 过程也会发生变动,这个时候就需要修改存储过程。存储过程的修改分为特征的修改和业务 内容的修改。特征的修改可以使用 ALTER PROCEDURE 来实现,其语法结构如下所示。
ALTER PROCEDURE <过程名> [ <特征> … ]
-
存储过程内容的修改方法是通过删除原有存储过程,之后再以相同的名称创建新的存储过程。
1.5.5: 删除存储过程
-
存储过程创建之时是存储到 MySQL 数据库中的,当程序不在调用这个存储过程时,也就意味这个存储过程被废弃了,废弃的存储过程需要从数据库中将其删除。使用 DROP PROCEDURE 语句即可删除存储过程,其语法格式具体如下。
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
-
从以上语法结构可以看出,在删除时存储过程的名字是放到最后的,前面可以添加 IF EXISTS 这个关键字,其主要作用是防止因删除不存在的存储过程而引发的错误。删除存储过程的具体操作如下所示。
mysql> drop procedure myupdate; Query OK, 0 rows affected (0.00 sec) mysql> call myupdate; ERROR 1305 (42000): PROCEDURE school.myupdate does not exist
-
需要注意的是:存储过程名称后面没有参数列表,也没有括号。在删除之前,必须确认该存储过程没有任何依赖关系,否则会导致与之关联的存储过程无法运行。