MySQL的函数及存储过程
- 一、MySQL数据库函数
- 1.1数学函数
- 1.2聚合函数
- 1.3字符串函数
- 1.4日期时间函数
- 二、MySQL数据库存储过程
- 2.1存储过程的优点
- 2.2创建存储过程
- 2.3查看存储过程
- 2.4存储过程的参数
一、MySQL数据库函数
概述:用于数据库中进行一系列的算是操作,主要有数学函数、聚合函数、字符串函数、日期时间函数
1.1数学函数
abs(x) : #返回x的绝对值
rand() : #返回0到1的随机数
mod(x,y) : #返回x除以y的余数
power(x,y) : #返回x的y次方
round(x) : #返回离x最近的整数
rount(x,y) : #保留x的y位小数四舍五入后的值
sqrt(x) : #返回x的平方跟
truncate(x,y) : #返回数字x截取为y为小数的值
ceil(x) : #返回大于或等于x的最小整数
floor(x) : #返回小于或者等于x的最大整数
qreatest(x1,x2...) : #返回集合中最大的值
least(x1,x2...) : #返回集合中最小的值
- 函数abs()
mysql> select abs(-1);
+---------+
| abs(-1) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
mysql> select abs(0);
+--------+
| abs(0) |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)
- 函数rand()
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.7838600853307732 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.4494626339985641 |
+--------------------+
1 row in set (0.00 sec)
- 函数mod(x,y)
mysql> select mod(5,2);
+----------+
| mod(5,2) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
- 函数power(x,y)
mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
| 8 |
+------------+
1 row in set (0.00 sec)
- 函数round(x)
mysql> select round(1.49);
+-------------+
| round(1.49) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select round(1.59);
+-------------+
| round(1.59) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql> select round(1.53);
+-------------+
| round(1.53) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
- 函数rount(x,y)
mysql> select round(1.555,2);
+----------------+
| round(1.555,2) |
+----------------+
| 1.56 |
+----------------+
1 row in set (0.00 sec)
mysql> select round(1.5,2);
+--------------+
| round(1.5,2) |
+--------------+
| 1.50 |
+--------------+
1 row in set (0.00 sec)
- 函数sqrt(x)
mysql> select sqrt(4);
+---------+
| sqrt(4) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
- 函数truncate(x,y)
mysql> select truncate(1.555,2); #截断小数点后两位
+-------------------+
| truncate(1.555,2) |
+-------------------+
| 1.55 |
+-------------------+
1 row in set (0.00 sec)
- 函数ceil(x)
mysql> select ceil(5.2);
+-----------+
| ceil(5.2) |
+-----------+
| 6 |
+-----------+
1 row in set (0.00 sec)
mysql> select ceil(5.0);
+-----------+
| ceil(5.0) |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
- 函数floor(x)
mysql> select floor(5.2);
+------------+
| floor(5.2) |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(5.0);
+------------+
| floor(5.0) |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
- 函数qreatest(x1,x2…)
mysql> select greatest(1,2,3);
+-----------------+
| greatest(1,2,3) |
+-----------------+
| 3 |
+-----------------+
1 row in set (0.00 sec)
- 函数least(x1,x2…)
mysql> select least(1,2,3);
+--------------+
| least(1,2,3) |
+--------------+
| 1 |
+--------------+
1 row in set (0.01 sec)
1.2聚合函数
avg() : #返回指定列的平均值
count() : #返回指定列中非null值的个数
min() : #返回指定列的最小值
max() : #返回指定列的最大值
sum() : #返回指定列的所有值之和
- 函数avg()
mysql> select * from whd;
+------+-----------+------+-------+------+------+
| id | name | age | score | addr | sss |
+------+-----------+------+-------+------+------+
| 1 | zhangshan | 18 | 80 | null | NULL |
| 2 | lisi | 20 | 60 | | NULL |
| 3 | wangwu | 25 | 60 | ai | NULL |
| 4 | wangmazi | 22 | 90 | ai | NULL |
| 5 | xuyi | 18 | 90 | ai | NULL |
+------+-----------+------+-------+------+------+
5 rows in set (0.00 sec)
mysql> select avg(score) from whd;
+------------+
| avg(score) |
+------------+
| 76.0000 |
+------------+
1 row in set (0.00 sec)
- 函数count()
mysql> select count(id) from whd;
+-----------+
| count(id) |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
- 函数min ()
mysql> select min(score) from whd;
+------------+
| min(score) |
+------------+
| 60 |
+------------+
1 row in set (0.00 sec)
- 函数max()
mysql> select max(score) from whd;
+------------+
| max(score) |
+------------+
| 90 |
+------------+
1 row in set (0.00 sec)
- 函数sum()
mysql> select sum(score) from whd;
+------------+
| sum(score) |
+------------+
| 380 |
+------------+
1 row in set (0.00 sec)
1.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个字符
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反转
repeat(x,y) : #将字符串x重复y次
- 函数length(x)
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.00 sec)
- 函数trim()
mysql> select trim(' abcd '); #去除头尾空格
+-----------------------+
| trim(' abcd ') |
+-----------------------+
| abcd |
+-----------------------+
1 row in set (0.00 sec)
mysql> select length(trim(' abc '));
+-------------------------+
| length(trim(' abc ')) |
+-------------------------+
| 3 |
+-------------------------+
1 row in set (0.00 sec)
- 函数concat(x,y)
mysql> select concat('abc','df');
+--------------------+
| concat('abc','df') |
+--------------------+
| abcdf |
+--------------------+
1 row in set (0.00 sec)
mysql> select concat('abc ',' df');
+----------------------+
| concat('abc ',' df') |
+----------------------+
| abc df |
+----------------------+
1 row in set (0.00 sec)
mysql> select concat('abc ',trim(' df'));
+----------------------------+
| concat('abc ',trim(' df')) |
+----------------------------+
| abc df |
+----------------------------+
1 row in set (0.00 sec)
mysql> select concat(left('abcdefg',3),right('12345',3));
+--------------------------------------------+
| concat(left('abcdefg',3),right('12345',3)) |
+--------------------------------------------+
| abc345 |
+--------------------------------------------+
1 row in set (0.00 sec)
- 函数upper(x)
mysql> select upper('aBc');
+--------------+
| upper('aBc') |
+--------------+
| ABC |
+--------------+
1 row in set (0.00 sec)
- 函数lower(x)
mysql> select lower('aBc');
+--------------+
| lower('aBc') |
+--------------+
| abc |
+--------------+
1 row in set (0.00 sec)
- 函数left(x,y)
mysql> select left('abcdefg',3);
+-------------------+
| left('abcdefg',3) |
+-------------------+
| abc |
+-------------------+
1 row in set (0.01 sec)
- 函数right(x,y)
mysql> select right('abcdefg',3);
+--------------------+
| right('abcdefg',3) |
+--------------------+
| efg |
+--------------------+
1 row in set (0.00 sec)
- 函数space(x)
mysql> select length(space(5));
+------------------+
| length(space(5)) |
+------------------+
| 5 |
+------------------+
1 row in set (0.00 sec)
- 函数replace(x,y,z)
mysql> select replace('hello','ll','bb');
+----------------------------+
| replace('hello','ll','bb') |
+----------------------------+
| hebbo |
+----------------------------+
1 row in set (0.00 sec)
- 函数strcmp(x,y)
mysql> select strcmp(5,6);
+-------------+
| strcmp(5,6) |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)
mysql> select strcmp(6,6);
+-------------+
| strcmp(6,6) |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> select strcmp(7,6);
+-------------+
| strcmp(7,6) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
- 函数substring(x,y,z)
mysql> select substring('abcdefg',3,4);
+--------------------------+
| substring('abcdefg',3,4) |
+--------------------------+
| cdef |
+--------------------------+
1 row in set (0.00 sec)
- 函数reverse(x)
mysql> select reverse('abcd');
+-----------------+
| reverse('abcd') |
+-----------------+
| dcba |
+-----------------+
1 row in set (0.00 sec)
mysql> select reverse('1234');
+-----------------+
| reverse('1234') |
+-----------------+
| 4321 |
+-----------------+
1 row in set (0.00 sec)
- 函数repeat(x)
mysql> select repeat('abc',3);
+-----------------+
| repeat('abc',3) |
+-----------------+
| abcabcabc |
+-----------------+
1 row in set (0.00 sec)
1.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中的字符串y
dayofmonth(x) : #计算日志x是本月的第几天
dayofyear(x) : #计算日期x是本年的第几天
- 函数curdate()
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-07-17 |
+------------+
1 row in set (0.00 sec)
- 函数curtime()
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 20:25:25 |
+-----------+
1 row in set (0.00 sec)
- 函数now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-07-17 20:25:30 |
+---------------------+
1 row in set (0.00 sec)
- 函数month(x)
mysql> select month(curdate());
+------------------+
| month(curdate()) |
+------------------+
| 7 |
+------------------+
1 row in set (0.00 sec)
mysql> select month('2021-07-08');
+---------------------+
| month('2021-07-08') |
+---------------------+
| 7 |
+---------------------+
1 row in set (0.00 sec)
- 函数week(x)
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
| 28 |
+-------------+
1 row in set (0.00 sec)
- 函数hour(x)
mysql> select hour(curtime());
+-----------------+
| hour(curtime()) |
+-----------------+
| 20 |
+-----------------+
1 row in set (0.00 sec)
- 函数minute(x)
mysql> select minute(curtime());
+-------------------+
| minute(curtime()) |
+-------------------+
| 30 |
+-------------------+
1 row in set (0.00 sec)
- 函数second(x)
mysql> select second(curtime());
+-------------------+
| second(curtime()) |
+-------------------+
| 49 |
+-------------------+
1 row in set (0.00 sec)
- 函数dayofweek(x)
mysql> select dayofweek(curdate());
+----------------------+
| dayofweek(curdate()) |
+----------------------+
| 7 |
+----------------------+
1 row in set (0.00 sec)
- 函数dayofmonth(x)
mysql> select dayofmonth(curdate());
+-----------------------+
| dayofmonth(curdate()) |
+-----------------------+
| 17 |
+-----------------------+
1 row in set (0.00 sec)
- 函数dayofyear(x)
mysql> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
| 198 |
+----------------------+
1 row in set (0.00 sec)
二、MySQL数据库存储过程
概述:MySQL数据库存储过程相当于shell脚本里的函数,是一组为了完成特定功能的SQL语句的集合,它是将一组SQL语句写好并取名存储在数据库服务器中,需要使用时调用即可,存储过程在执行上比传统SQL速度更快、执行效率更高
2.1存储过程的优点
- 执行成功后,会将生成的二进制代码存于缓冲区,提高执行效率
- SQL语句加控制语句的结合,灵活性高
- 在服务端存储,客户端调用时,降低网络负载
- 可多次重复调用,可随时修改
- 可完成所有数据库操作,也可控制数据库的信息访问权限
2.2创建存储过程
delimiter ## #设置语句的结束符号为#,默认为分号
create procedure proc() #设置存储过程,取名,不带参数
-> begin #表示语句开始
-> select * from whd; #过程中的SQL语句
-> end ## #表示语句的结束
delimiter ; #将语句的结束符号改回分号
call proc; #调用存储过程
mysql> delimiter ##
mysql> create procedure www()
-> begin
-> select * from test;
-> end ##
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call www;
+------+----------+
| id | name |
+------+----------+
| 1 | nanjign |
| 2 | hangzou |
| 3 | shanghai |
+------+----------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
2.3查看存储过程
show create procedure 数据库.存储过程名 \G #查看某个存储过程的信息
show procedure status like '%存储过程名%' \G #查看指定存储过程信息
show procedure status like '%%' \G #查看所有存储过程
mysql> show create procedure proc \G
*************************** 1. row ***************************
Procedure: proc
sql_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_SUBSTITUTION
Create Procedure: CREATE DEFINER="root"@"localhost" PROCEDURE "proc"()
begin
select * from whd;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql> show procedure status like '%proc%' \G
*************************** 1. row ***************************
Db: whd
Name: proc
Type: PROCEDURE
Definer: root@localhost
Modified: 2021-07-17 20:53:53
Created: 2021-07-17 20:53:53
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
2.4存储过程的参数
in 输入参数:表示调用者想过程传入值(可以是常量或变量)
out 输出参数:表示过程向调用者传出值(只能是变量,可以返回多个值)
input 输入输出参数:包含in和out,值只能是变量
mysql> delimiter ##
mysql> create procedure proc1(in inname varchar(40))
-> begin
-> select * from whd where name=inname;
-> end ##
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call proc1('lisi');
+------+------+------+-------+------+------+
| id | name | age | score | addr | sss |
+------+------+------+-------+------+------+
| 2 | lisi | 20 | 60 | | NULL |
+------+------+------+-------+------+------+
1 row in set (0.00 sec)
- 删除存储过程
drop procedure if exists 存储过程名;