MySQL数据库函数【数学函数、聚合函数、字符串函数、日期函数】和MySQL数据库储存过程

MySQL数据库函数与存储过程

一、数据库函数的认识

数据库函数是指当需要分析数据清单中的数值是否符合特定条件时，使用数据库工作表函数。
例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于 1,000 且小于 2,500 的行或记录的总数。 Microsoft Excel 共有 12 个 工作表函数用于对 存储在数据清单或 数据库中的数据进行 分析，这些函数的统一名称为 Dfunctions，也称为 D 函数，每个函数均有三个相同的 参数：database、field 和 criteria。这些 参数指向数据库函数所使用的 工作表 区域。其中 参数 database 为 工作表上包含数据清单的 区域。 参数 field 为需要汇总的列的 标志。 参数 criteria 为 工作表上包含指定条件的 区域。

二、数据库函数的作用

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

三、数据库函数的分类

1、数学函数

【1】 abs（x）返回绝对值

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

【2】rand（）返回0到1的随机数（取不到1）

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.8681239559018277 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6894331105886335 |
+--------------------+
1 row in set (0.00 sec)

【3】mod（x，y）返回x除以y得到的余数

mysql> select mod(7,2);
+----------+
| mod(7,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

【4】power（x，y）返回x的y次方

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

【5】round（x）返回四舍五入之后的整数

mysql> select round(7.1365);
+---------------+
| round(7.1365) |
+---------------+
|             7 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(7.5365);
+---------------+
| round(7.5365) |
+---------------+
|             8 |
+---------------+
1 row in set (0.00 sec)

【6】round（x，y）返回四舍五入小数点后保留y位的值

mysql> select round(7.5365,1);
+-----------------+
| round(7.5365,1) |
+-----------------+
|             7.5 |
+-----------------+
1 row in set (0.00 sec)

mysql> select round(7.5365,2);
+-----------------+
| round(7.5365,2) |
+-----------------+
|            7.54 |
+-----------------+
1 row in set (0.00 sec)

【7】sqrt（x）返回x的平方根

mysql> select sqrt(49);
+----------+
| sqrt(49) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

【8】truncate（x，y）返回x被截断y位的数

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

【9】ceil（x）返回大于或等于的x的最小整数（向上取整）

mysql> select ceil(4.1656);
+--------------+
| ceil(4.1656) |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

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

【10】floor（x）返回小于或等于的x最大整数（向下取整）

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

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

【11】greatest（x,y,z）求最大值

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

【12】least（x,y,z）求最小值

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

2、聚合函数

mysql> select * from student;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 60.00 |
|  2 | lisi     | 99.00 |
|  3 | wangwu   | 85.00 |
|  4 | zhaoliu  | 77.00 |
|  5 | tianqi   | 20.00 |
+----+----------+-------+
5 rows in set (0.00 sec)

【1】avg（字段）平均值

mysql> select avg(score) '平均成绩' from student;
+--------------+
| 平均成绩     |
+--------------+
|    68.200000 |
+--------------+
1 row in set (0.00 sec)

【2】count（字段）个数

mysql> select count(score) from student;
+--------------+
| count(score) |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

【3】min（字段）最小值

mysql> select min(score) as '最低成绩' from student;
+--------------+
| 最低成绩     |
+--------------+
|        20.00 |
+--------------+
1 row in set (0.00 sec)

【4】max（字段）最大值

mysql> select max(score) as '最高成绩' from student;
+--------------+
| 最高成绩     |
+--------------+
|        99.00 |
+--------------+
1 row in set (0.00 sec)

【5】sum（字段）求和

mysql> select sum(score) as '总成绩' from student;
+-----------+
| 总成绩    |
+-----------+
|    341.00 |
+-----------+
1 row in set (0.01 sec)

3、字符串函数

【1】length（x）字符串的长度

mysql> select length('zhansan');
+-------------------+
| length('zhansan') |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

【2】trim（）去除字符串两端的空格

mysql> select trim('  zhansan  ');
+---------------------+
| trim('  zhansan  ') |
+---------------------+
| zhansan             |
+---------------------+
1 row in set (0.00 sec)

【3】concat（x，x）字符串x与x相连

mysql> select concat('zhansan  ','lisi');
+----------------------------+
| concat('zhansan  ','lisi') |
+----------------------------+
| zhansan  lisi              |
+----------------------------+
1 row in set (0.00 sec)

【4】upper（x）转化字符串为大写

mysql> select upper(concat('zhansan  ','lisi'));
+-----------------------------------+
| upper(concat('zhansan  ','lisi')) |
+-----------------------------------+
| ZHANSAN  LISI                     |
+-----------------------------------+
1 row in set (0.00 sec)

【5】lower（x）转化字符串为小写

mysql> select lower(upper(concat('zhansan  ','lisi')));
+------------------------------------------+
| lower(upper(concat('zhansan  ','lisi'))) |
+------------------------------------------+
| zhansan  lisi                            |
+------------------------------------------+
1 row in set (0.00 sec)

【6】left（x，y）截取x字符串从左向右y个字符

mysql> select left('zhangsanlisi',8);
+------------------------+
| left('zhangsanlisi',8) |
+------------------------+
| zhangsan               |
+------------------------+
1 row in set (0.00 sec)

【7】right（x，y）从右往左截取y个字符串

mysql> select right('zhangsanlisi',4);
+-------------------------+
| right('zhangsanlisi',4) |
+-------------------------+
| lisi                    |
+-------------------------+
1 row in set (0.00 sec)

【8】repeat（x,y）重复x字符串y次

mysql> select repeat('zhangsanlisi  ',2);
+------------------------------+
| repeat('zhangsanlisi  ',2)   |
+------------------------------+
| zhangsanlisi  zhangsanlisi   |
+------------------------------+
1 row in set (0.00 sec)

【9】space（x）返回x个空格

mysql> select concat('zhangsan',space(2),'lisi');
+------------------------------------+
| concat('zhangsan',space(2),'lisi') |
+------------------------------------+
| zhangsan  lisi                     |
+------------------------------------+
1 row in set (0.00 sec)

【10】replace（x，y，z）将字符串x中的y代替为z

mysql> select replace('zhangsan','zhang','yang');
+------------------------------------+
| replace('zhangsan','zhang','yang') |
+------------------------------------+
| yangsan                            |
+------------------------------------+

【11】strcmp（x，y）比较x和y的第一个字符，返回-1则x小于y，返回0则x=y，返回1则x大于y。个位数比较

mysql> select strcmp(4,5);
+-------------+
| strcmp(4,5) |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

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

【12】substring（x，y，z）提取x字符串中从y个字符开始连续z个长度的字符串

mysql> select substring('zhangsan',6,3);
+---------------------------+
| substring('zhangsan',6,3) |
+---------------------------+
| san                       |
+---------------------------+
1 row in set (0.00 sec)

【13】reverse（x）对字符串x倒着输出（反转）

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

4、日期函数

【1】curdate（）当前时间年月日

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-08-27 |
+------------+
1 row in set (0.00 sec)

【2】curtime（）当前时间时分秒

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 09:50:19  |
+-----------+
1 row in set (0.00 sec)

【3】now（）当前时间年月日时分秒

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-08-27 09:50:29 |
+---------------------+
1 row in set (0.00 sec)

【4】month（x）返回x日期中的月份

mysql> select month('2020-08-27 09:50:29');
+------------------------------+
| month('2020-08-27 09:50:29') |
+------------------------------+
|                            8 |
+------------------------------+
1 row in set (0.00 sec)

【5】week（x）返回x日期中年度第几个星期

mysql> select week('2020-08-27 09:50:29');
+-----------------------------+
| week('2020-08-27 09:50:29') |
+-----------------------------+
|                          34 |
+-----------------------------+
1 row in set (0.00 sec)

【6】hour（x）返回时间x中的小时

mysql> select hour('2020-08-27 09:50:29');
+-----------------------------+
| hour('2020-08-27 09:50:29') |
+-----------------------------+
|                           9 |
+-----------------------------+
1 row in set (0.00 sec)

【7】minute（x）返回时间x中的分

mysql> select minute('2020-08-27 09:50:29');
+-------------------------------+
| minute('2020-08-27 09:50:29') |
+-------------------------------+
|                            50 |
+-------------------------------+
1 row in set (0.00 sec)

【8】second（x）返回时间x中的秒

mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
|            20 |
+---------------+
1 row in set (0.00 sec)

【9】dayofweek（x）返回x时间是周几=返回值-1

mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

【10】dayofmonth（x）返回这月第几天

mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
|                27 |
+-------------------+
1 row in set (0.00 sec)

【11】dayofyear（x）返回这个年的第几天

mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
|              240 |
+------------------+
1 row in set (0.00 sec)

四、数据库存储过程

【1】存储过程的定义

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，是利用SQL Server所提供的Transact-SQL语言所编写的程序。经编译后存储在数据库中。存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量 。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。

【2】存储过程的优点

1、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。执行一次之后，会将生成的二进制代码驻留缓冲区，提高执行效率
2、当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。控制语句加上SQL语句的集合，灵活性高。
3、存储过程可以重复使用,可减少数据库开发人员的工作量，可随时更改，不影响客户端调用。
4、安全性高,可设定只有某些用户才具有对指定存储过程的使用权，客户端调用时，降低网络负载。

【3】语法格式及相关参数

delimiter $$
create procedure 存储过程名 （in|out|inout 参数名 参数类型）
————————主体部分，过程体————————————
begin
//定义变量
declare 变量名 变量类型
//设置变量值
set 变量名=值
  sql 语句1;
  sql 语句2;
end$$
————————————————————————————
delimiter ;

输入参数：in
输出参数：out
输入输出参数：inout
【4】相关命令

//调用存储过程
call 存储过程名（实参）;
//查询存储过程
show procedure status where db='数据库名';
//删除存储过程
drop procedure 存储过程名;

【5】实例操作

（1）简单的过程调用范例

//编写
mysql> delimiter $$
mysql> create procedure myrule()
    -> begin
    -> select * from student;
    -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
//查看
mysql> show procedure status where db='school'\G;
*************************** 1. row ***************************
                  Db: school
                Name: myrule
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-08-27 10:16:32
             Created: 2020-08-27 10:16:32
       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)
//调用
mysql> call myrule ();
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 60.00 |
|  2 | lisi     | 99.00 |
|  3 | wangwu   | 85.00 |
|  4 | zhaoliu  | 77.00 |
|  5 | tianqi   | 20.00 |
+----+----------+-------+
5 rows in set (0.00 sec)
//删除
mysql> drop procedure myrule;
Query OK, 0 rows affected (0.00 sec)

（2）传入参数过程调用范例

//编写
mysql> delimiter $$
mysql> create procedure myrule(in my_name char(10))
    -> begin
    -> select * from student where name=my_name;
    -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
//查看
mysql> show procedure status where db='school'\G;
*************************** 1. row ***************************
                  Db: school
                Name: myrule
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-08-27 10:29:47
             Created: 2020-08-27 10:29:47
       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)
//调用
mysql> call myrule('lisi');
+----+------+-------+
| id | name | score |
+----+------+-------+
|  2 | lisi | 99.00 |
+----+------+-------+
1 row in set (0.00 sec)

（3）配合条件语句过程调用

//编写
mysql>  delimiter $$
mysql> create procedure myupdate(in my_score decimal(5,2))
    -> begin
    -> declare my_name char(10);
    -> if my_score >= 90 then
    -> set my_name = 'wangyangli';
    -> else
    -> set my_name = 'zhugeliang';
    -> end if;
    -> update student set score=my_score,name=my_name where id=2;
    -> end $$
mysql> delimiter ;
//调用
mysql> call myupdate(91);
Query OK, 1 row affected (0.00 sec)
//验证
mysql> select * from student;
+----+------------+-------+
| id | name       | score |
+----+------------+-------+
|  1 | zhangsan   | 60.00 |
|  2 | wangyangli | 91.00 |
|  3 | wangwu     | 85.00 |
|  4 | zhaoliu    | 77.00 |
|  5 | tianqi     | 20.00 |
+----+------------+-------+
5 rows in set (0.00 sec)