MySQL函数与存储过程

数据库函数

数学函数

abs(x)：返回x的绝对值

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

rand()：返回0到1的随机数（0-0.9999…，1是取不到的）

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

mod(x,y)：返回x除以y以后的余数

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

-power(x,y)：返回x的y次方

mysql> select power(2,3),power(4,2);
+------------+------------+
| power(2,3) | power(4,2) |
+------------+------------+
|          8 |         16 |
+------------+------------+
1 row in set (0.00 sec)

round(x)：返回离x最近的整数（四舍五入，只看小数点后第一位）

mysql> select round(2.4),round(2.5);
+------------+------------+
| round(2.4) | round(2.5) |
+------------+------------+
|          2 |          3 |
+------------+------------+
1 row in set (0.00 sec)

round(x,y)：保留x的y位小数四舍五入后的值

mysql> select round(3.14159,1),round(3.14159,2);
+------------------+------------------+
| round(3.14159,1) | round(3.14159,2) |
+------------------+------------------+
|              3.1 |             3.14 |
+------------------+------------------+
1 row in set (0.00 sec)

sqrt(x)：返回x的平方根

mysql> select sqrt(9),sqrt(3);
+---------+--------------------+
| sqrt(9) | sqrt(3)            |
+---------+--------------------+
|       3 | 1.7320508075688772 |
+---------+--------------------+
1 row in set (0.00 sec)

truncate(x,y)：返回数字x截断为y位小数的值

mysql> select truncate(3.456,1),truncate(3.456,2);
+-------------------+-------------------+
| truncate(3.456,1) | truncate(3.456,2) |
+-------------------+-------------------+
|               3.4 |              3.45 |
+-------------------+-------------------+
1 row in set (0.00 sec)

ceil(x)：返回大于或等于x的最小整数（向上取整数）

mysql> select ceil(2.3),ceil(3.5);
+-----------+-----------+
| ceil(2.3) | ceil(3.5) |
+-----------+-----------+
|         3 |         4 |
+-----------+-----------+
1 row in set (0.00 sec)

floor(x)：返回小于或等于x的最大整数（向下取整数）

mysql> select floor(2.4),floor(1.9);
+------------+------------+
| floor(2.4) | floor(1.9) |
+------------+------------+
|          2 |          1 |
+------------+------------+
1 row in set (0.00 sec)

greatest(x1,x2…)：返回集合中最大的值

mysql> select greatest(11,55,39);
+--------------------+
| greatest(11,55,39) |
+--------------------+
|                 55 |
+--------------------+
1 row in set (0.00 sec)

least(x1,x2…)：返回集合中最小的值

mysql> select least(11,55,39);
+-----------------+
| least(11,55,39) |
+-----------------+
|              11 |
+-----------------+v
1 row in set (0.00 sec)

聚合函数

1、对表中数据记录进行集中概括而设计的一类函数
2、常用的聚合函数（只会产生一个值）

avg(字段名) 返回指定字段的平均值

mysql> select avg(age) from aa;
+----------+
| avg(age) |
+----------+
|       19 |
+----------+
1 row in set (0.00 sec)

count(字段名) 返回指定字段中非NULL值的个数

mysql> select * from aa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
| 1    | zhangsan | 20   |
| 2    | lisi     | 16   |
| 3    | wangwu   | 20   |
| 4    | chanwi   | 20   |
| 5    | zhaomi   | NULL |
+------+----------+------+
5 rows in set (0.00 sec)

mysql> select count(age) from aa;
+------------+
| count(age) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

min(字段名) 返回指定字段的最小值

mysql> select min(age) from aa;
+----------+
| min(age) |
+----------+
| 16       |
+----------+
1 row in set (0.04 sec)

max(字段名) 返回指定字段的最大值

mysql> select max(age) from aa;
+----------+
| max(age) |
+----------+
| 20       |
+----------+
1 row in set (0.00 sec)

sum(字段名) 返回指定字段的所有值之和

mysql> select sum(age) from aa;
+----------+
| sum(age) |
+----------+
|       76 |
+----------+
1 row in set (0.00 sec)

字符串函数

length(x)：返回字符串x的长度（空格也算）

mysql> select length('abc d');
+-----------------+
| length('abc d') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

trim()：返回去除指定格式的值（只能去除前后的空格）

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

concat(x,y)：将提供的参数x和y拼接成一个字符串

mysql> select concat('ab','cd');
+-------------------+
| concat('ab','cd') |
+-------------------+
| abcd              |
+-------------------+
1 row in set (0.00 sec) 

upper(x)：将字符串x的所有字母变成大写字母

mysql> select upper('abc');
+--------------+
| upper('abc') |
+--------------+
| ABC          |
+--------------+
1 row in set (0.00 sec)

lower(x)：将字符串x的所有字母变成小写字母

mysql> select lower('ABC');
+--------------+
| lower('ABC') |
+--------------+
| abc          |
+--------------+
1 row in set (0.00 sec)

left(x,y)：返回字符串x的前y个字符

mysql> select left('abcde',2);
+-----------------+
| left('abcde',2) |
+-----------------+
| ab              |
+-----------------+
1 row in set (0.00 sec)

right(x,y)：返回字符串x的后y个字符

mysql> select right('abcde',2);
+------------------+
| right('abcde',2) |
+------------------+
| de               |
+------------------+
1 row in set (0.00 sec)

repeat(x,y)：将字符串x重复y次

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

space(x)：返回x个空格（结合concat使用）

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

replace(x,y,z)：将字符串z替代字符串x中的字符串y

mysql> select replace('abcde','a','z');
+--------------------------+
| replace('abcde','a','z') |
+--------------------------+
| zbcde                    |
+--------------------------+
1 row in set (0.00 sec)

strcmp(x,y)：比较x和y，返回的值可以为-1 <，0 =，1 >

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

substring(x,y,z)：获取从字符串x中的第y个位置开始长度为z的字符串
格式：substring(完整字符串,起始位置,长度)； #起始位置从1开始

mysql> select substring('abcde',2,2);
+------------------------+
| substring('abcde',2,2) |
+------------------------+
| bc                     |
+------------------------+
1 row in set (0.00 sec)

reverse(x)：将字符串x反转

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

日期时间函数

curdate()：返回当前时间的年月日

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

curtime()：返回当前时间的时分秒

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 00:12:15  |
+-----------+
1 row in set (0.04 sec)

now()：返回当前时间的日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-12-28 00:12:25 |
+---------------------+
1 row in set (0.00 sec)

month(x)：返回日期x中的月份值

mysql> select month(curdate());
+------------------+
| month(curdate()) |
+------------------+
|               12 |
+------------------+
1 row in set (0.00 sec)

hour(x)：返回x中的小时值

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

minute(x)：返回x中的分钟值

ysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            16 |
+---------------+
1 row in set (0.00 sec)

second(x)：返回x中的秒钟值

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

dayofweek(x)：返回x是星期几，1星期日，2星期一，3星期二…

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

mysql> select dayofweek('2020-12-28');
+-------------------------+
| dayofweek('2020-12-28') |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)

dayofmonth(x)：计算日期x是本月的第几天

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

mysql> select dayofmonth('2020-12-28');
+--------------------------+
| dayofmonth('2020-12-28') |
+--------------------------+
|                       28 |
+--------------------------+
1 row in set (0.00 sec)

dayofyear(x)：计算日期x是本年的第几天

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

mysql> select dayofyear('2020-12-28');
+-------------------------+
| dayofyear('2020-12-28') |
+-------------------------+
|                     363 |
+-------------------------+
1 row in set (0.00 sec)

MySQL存储过程

存储过程简介

1、从 5.0 版本才开始支持

2、是一组为了完成特定功能的SQL语句集合（封装）

3、比传统SQL速度更快、执行效率更高

4、存储过程的优点
（1）执行一次后，会将生成的二进制代码驻留缓冲区（便于下次执行），提高执行效率
（2）SQL语句加上控制语句的集合，灵活性高
（3）在服务器端存储，客户端调用时，降低网络负载
（4）可多次重复被调用，可随时修改，不影响客户端调用
（5）可完成所有的数据库操作，也可控制数据库的信息访问权限

5、为什么要用存储过程？
（1）减轻网络负载；
（2）增加安全性

创建存储过程

1、使用create procedure语句创建存储过程
2、参数分为
输入参数：in
输出参数：out
输入/输出参数：inout
3、存储过程的主体部分，被称为过程体；以begin开始，以end$$结束
4、具体格式

delimiter $$
create procedure 存储过程名（in 参数名 参数类型）
begin
#定义变量
declare 变量名 变量类型
#变量赋值
set 变量名 = 值
  sql 语句1；
  sql 语句2；
  ...
end$$
delimiter ；（有空格）

举例：

mysql> delimiter $$
mysql> create procedure aa ()
    -> begin
    -> select * from aa;
    -> end $$
Query OK, 0 rows affected (0.04 sec)

调用存储过程

call 存储过程名(实际参数);
举例：

mysql> delimiter ;
mysql> call aa;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
| 1    | zhangsan | 20   |
| 2    | lisi     | 16   |
| 3    | wangwu   | 20   |
| 4    | chanwi   | 20   |
| 5    | zhaomi   | NULL |
+------+----------+------+
5 rows in set (0.00 sec)

查询存储过程

show procedure status where db=‘数据库’；

修改存储过程

存储过程的修改分为特征的修改和业务内容的修改。
特征的修改语法结构如下：
alter procedure 存储过程名 [ <特征> … ]

删除存储过程

删除存储过程的语法：
drop {procedure|function|if exits} <过程名>
举例：

mysql> drop procedure aa;
Query OK, 0 rows affected (0.04 sec)

mysql> call aa;
ERROR 1305 (42000): PROCEDURE ttt.aa does not exist

传递参数过程

栗子1：

mysql> delimiter $$
mysql> create procedure aa(in inname varchar(16)) 
    -> begin
    -> select * from aa where name=inname; 
    -> end $$
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> call aa('zhangsan');
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
| 1    | zhangsan | 20   |
+------+----------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

栗子2：

mysql> set @num1=1;@num2=2,@num3=3;
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure b(in num1 int,out num2 int,inout num3 int)
    -> begin
    -> select num1,num2,num3;
    -> set num1=10,num2=20,num3=30;
    -> select num1,num2,num3;
    -> end $$
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> call b(@num1,@num2,@num3);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|    1 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)

+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
|   10 |   20 |   30 |
+------+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

总结：in和inout参数会将全局变量的值传入存储过程中，而out参数不会将全局变量的值传入存储过程中。在存储过程使用中，参数值in，out，inout都会发生改变。

栗子3：

mysql> select @num1,@num2,@num3;
+-------+-------+-------+
| @num1 | @num2 | @num3 |
+-------+-------+-------+
|     1 |    20 |    30 |
+-------+-------+-------+
1 row in set (0.00 sec)

总结：
1、调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量。

2、in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须为变量。