MySQL数据库函数与MySQL数据库存储过程！

一、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 存储过程名;