MYSQL数据库函数和存储过程

一、数据库函数

1、数学函数

数学函数	描述
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…)	返回集合中最小的值

2、聚合函数

聚合函数	描述
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

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 个字符
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 反转

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 是本年的第几天

二、存储过程

1、概述
学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程， 轻松而高效的去完成这个需求

2、简介
MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合。存储过程这个功能是从 5.0 版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中 的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一 个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高。 存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。

3、优点
1：存储过程执行一次后，生成的二进制代码就驻留在缓冲区，之后如果再次调用的话，将直接调用二进制代码，使得存储过程的执行效率和性能得到大幅提升。
2：存储过程是 SQL 语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调 用语句，从而可以降低网络负载。
3：存储过程被创建后，可以多次重复调用，它将多条 SQL 封装到了一起，可随时针对 SQL 语句进行修改，不影响调用它的客户端。
4：存储过程可以完成所有的数据库操作，也可以通过编程的方式控制数据库的信息访问权限

4、创建存储过程
使用 CREATE PROCEDURE 语句创建存储过程
（1）语法

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体> [过程参数[,…] ] 格式 [ IN | OUT | INOUT ] <参数名><类型>

（2）参数
存储过程的名称应该尽量避免选取与 MySQL 内置的函数或者字段相同的名称，否则会 发生错误。存储过程可以添加参数，具有自己的参数列表。 MySQL 的参数分为：输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字表示。其中，输入参数可以传递给一个存储过程；输出参数用于存储过程需要返 回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

（3）DELIMITER 命令
在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的，过程体中由多条 SQL 语句构成，每条 SQL 后面都是分号结尾，那么 MySQL 服务器在处理时遇到第一条 SQL 语句就会结束整个过程， 不再去处理后面的 SQL 语句。为了解决这个问题，在创建存储过程时，使用 DELIMITER 命令

5、查看存储过程
使用SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程
使用SHOW CREATE PROCEDURE <存储过程名称>查看某个存储过程的具体信息

6、修改存储过程
存储过程的修改分为特征的修改和业务内容的修改。特征的修改可以使用 ALTER PROCEDURE 来实现

7、删除存储过程
使用 DROP PROCEDURE 语句即可删除存储过程

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
从以上语法结构可以看出，在删除时存储过程的名字是放到最后的，前面可以添加 IF EXISTS 这个关键字，其主要作用是防止因删除不存在的存储过程而引发的错误