MYSQL5.7高级SQL语句（3）-数据库函数和存储过程

1、MYSQL5.7SQL3数据库函数和存储过程MySQL 数据库函数提供了能够实现各种功能的法，使我们在查询记录时能够更效 的输出。MySQL 内建了很多函数，常的包括数学函数、聚合函数、字符串函数和期时 间函数。、数据库函数数据库内存储的记录，经常要进系列的算术操作，所以 MySQL 持很多数学函数。 常的数学函数如表所数学函数abs(x)描述返回 0 到 1 的随机数返回 x 除以 y 以后的余数返回 x 的 y 次mod(x,y)返回离 x 最近的整数保留 x 的 y 位数四舍五后的值返回 x 的平根truncate(x,y)ceil(x)floor(x)greatest(x1,x2)le

2、ast(x1,x2)返回集合中最的值-2的绝对值| abs(-2) |-+|2 |-+1 row in set (0.00 sec)0-1的随机数（0=x select sum(score) from info;- +| sum(score) |- +|662.00 |- +1 row in set (0.00 sec)返回分数字段的个数| count(score) |- +|8 |- +1 row in set (0.00 sec)返回分数的最值| min(score) |- +|70.00 |- +1 row in set (0.00 sec)返回分数的最值mysql select max

3、(score) from info;- +| max(score) |1 row in set (0.00 sec)返回分数的平均值| 82.750000 |1 row in set (0.00 sec)3字符串函数length(x)trim()将提供的参数 x 和 y 拼接成个字符串将字符串 x 的所有字母变成写字母将字符串 x 的所有字母变成写字母返回字符串 x 的前 y 个字符返回字符串 x 的后 y 个字符将字符串 x 重复 y 次lower(x)left(x,y)repeat(x,y)space(x)返回 x 个空格replace(x,y,z)strcmp(x,y)substring

4、(x,y,z)reverse(x)将字符串 z 替代字符串 x 中的字符串 y较 x 和 y，返回的值可以为-1,0,1获取从字符串 x 中的第 y 个位置开始长 度为 z 的字符串将字符串 x 反转返回abcd的长度，空格也算个字符| length(abcd) |- +|4 |- +1 row in set (0.00 sec)| length(ab cd) |- +|5 |- +1 row in set (0.01 sec)去掉格式只输出字符串，对不trim函数的显|1 row in set (0.00 sec)把abc和def拼接起来| concat(abc, def) |- +| ab

5、c def|- +1 row in set (0.00 sec)还可以结合其他函数，如trim| abcdef|- +1 row in set (0.00 sec)把abc转换为写字母| upper(abc) |- +| ABC|- +1 row in set (0.00 sec)把ABC转换为写字母| lower(ABC) |- +|- +1 row in set (0.01 sec)返回字符串的前3个字母|1 row in set (0.00 sec)返回字符串的最后3个字母|1 row in set (0.00 sec)把字符串的前3个字母和后3个字母拼接起来+|-1 row in se

6、t (0.00 sec)重复字符串2次| repeat(abc,2) |- +| abcabc|- +1 row in set (0.00 sec)返回3个空格，显法看出个空格，这length函数显长度| length(space(3) |- +|3 |- +1 row in set (0.00 sec)aa替换hello中的llmysql select replace(hello,ll,aa);- +| replace(hello,ll,aa) |- +1 row in set (0.00 sec)较17和18，于返回-1，等于返回0，于返回1，只会返回这3个值，它是较第位数字| strcm

7、p(27,7) |- +|-1 |- +1 row in set (0.00 sec)返回从字符串中第三个字符开始的4个字符mysql select substring(abcdefg,3,4);- +| cdef|- +1 row in set (0.00 sec)字符串反转显| abcdefg|- +1 row in set (0.00 sec)返回字符串的前3个字符，然后反转输出|- +1 row in set (0.00 sec)先将字符串反转，再输出前3个字符mysql select left(reverse(gfedcba),3);- +|- +1 row in set (0.00

8、 sec)4字符串函数描述now()返回当前时间的期和时间返回期 x 中的份值week(x)hour(x)minute(x)second(x)dayofweek(x)replace(x,y,z)dayofmonth(x)dayofyear(x)返回 x 是星期，1 星期，2 星期将字符串 z 替代字符串 x 中的字符串 y计算期 x 是本的第天计算期 x 是本年的第天返回年| curdate() |- +1 row in set (0.00 sec)返回当前时间| curtime() |- +| 15:57:33 |- +1 row in set (0.00 sec)返回当前完整时间|- +|

9、 2020-08-25 15:57:45 |- +1 row in set (0.00 sec)返回份|8 |1 row in set (0.00 sec)返回当前期是年中的第周|34 |1 row in set (0.00 sec)返回当前时间的时- +|- +1 row in set (0.00 sec)返回当前时间的分钟|3 |- +1 row in set (0.00 sec)返回当前时间的秒|56 |- +1 row in set (0.00 sec)当前是星期mysql select dayofweek(curdate();- +|1 row in set (0.00 sec)当前

10、期是本的第天mysqlselect dayofmonth(curdate();- +|- +1 row in set (0.00 sec)当前期是今年的第天|1 row in set (0.00 sec)、存储过程前学习的 MySQL 相关知识都是针对个表或个表的单条 SQL 语句，使这样的SQL 语句虽然可以完成户的需求，但在实际的数据库应中，有些数据库操作可能会常复杂，可能会需要多条 SQL 语句起去处理才能够完成，这时候就可以使存储过程， 轻松效的去完成这个需求，有点类似shell脚本的函数MySQL 数据库存储过程是组为了完成特定功能的 SQL 语句的集合。存储过程这个功能是从 5.0

11、 版本才开始持的，它可以加快数据库的处理速度，增强数据库在实际应中 的灵活性。存储过程在使过程中是将常或者复杂的作预先使 SQL 语句写好并 个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使该存储过程时，只需要调它即可。操作数据库的传统SQL 语句在执时需要先编译，然后再去执，跟存储过程对，明显存储过程在执上速度更快，效率更。 存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加些特殊的控制结构，也可以控制数据的访问式。存储过程执次后，成的进制代码就驻留在缓冲区，之后如果再次调的话，将直接调进制代码，使得存储过程的执效率和性能得到幅提升。存储过程是

12、SQL 语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。存储过程存储在服务器端，客户端调时，直接在服务器端执，客户端只是传输的调 语句，从可以降低络负载。存储过程被创建后，可以多次重复调，它将多条 SQL 封装到了起，可随时针对 SQL 语句进修改，不影响调它的客户端。存储过程可以完成所有的数据库操作，也可以通过编程的式控制数据库的信息访问权 限使 CREATE PROCEDURE 语句创建存储过程CREATE PROCEDURE ( , ) , 格式 IN | OUT | INOUT 存储过程的名称应该尽量避免选取与 MySQL 内置的函数或者字段相同的名称，否则会 发错误。存储

13、过程可以添加参数，具有的参数列表。参数包括参数名和其对应的类型。 存在多个参数时，参数列表之间逗号进分隔。创建存储过程的时候可以不使参数，但是括号要存在，也可以有个或多个参数。 MySQL 的参数分为：输参数、输出参数和输/输出参数，分别 IN、OUT 和 INOUT 三个关键字表。其中，输参数可以传递给个存储过程；输出参数于存储过程需要返 回个操作结果的情形，输/输出参数既可以充当输参数也可以充当输出参数。存储过程的主体部分，被称为过程体，包含了在调时必须执的 SQL 语句。 这个部分以关键字 BEGIN 开始，以关键字 END 结束。若过程体中只有条 SQL 语句，则 可以省略 BEGIN

14、-END 标志 在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的，过程体中由多条 SQL 语句构成，每条 SQL 后都是分号结尾，那么 MySQL 服务器在处理时遇到第条 SQL 语句就会结束整个过程， 不再去处理后的 SQL 语句。为了解决这个问题，在创建存储过程时，使 DELIMITER 命令$;$才会执语句- -+| 11 | nannan | 76.00 |2 |- -+9 rows in set (0.00 sec)例1：创建存储过程scoreinfo，功能是查询info表中的前三条记录，显id，姓名和分数mysql delimiter $Query , 0

15、 rows affected (0.01 sec)调存储过程callmysql delimiter ;| id | name| score |Query , 0 rows affected (0.00 sec)使SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程使SHOW CREATE PROCEDURE 查看某个存储过程的具体信息sql_mode: ,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATCreate Procedure: CREATErootlocalhost PROCEDUREscoreinfo()character_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)No query specified存储过程的修改分为特征的修改和业务内容的修改。特征的修改可以使 ALTER PROCEDURE 来实现存储过程内容的修改法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程使 DROP PROCEDURE 语句即可删除存储过程DRO