MySQL函数

一、数据库函数

1. 数学函数

数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。 常用的数学函数如表所示

数学函数                     描述

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…)          返回集合中最小的值

select abs(-9); 返回-9的绝对值

select rand();返回随机数

select rand()*100; 返回随机数*100

select mod(10,3); 取余

select mod(10,4);

select power(3,4); 3的4次方

select power(2,4); 2的 4次方

select round （2.56）；判断离2.56最近的整数

select round(2.737,1); 2.737保留小数点后1位，四舍五入

select sqrt(8);返回8的平方根

select sqrt(4);返回4的平方根

select truncate(4.856,2);保留小数点后2位，但truncate函数不会四舍五入

select ceil(8.9);大于等于5.2的整数

select floor(7.0)返回小于等于7.0的最大整数

select greatest(2,6,4);返回2，6，4中的最大值

select least(2,6,4);返回2，6，4中的最小值

2. 聚合函数

MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。

可以对数据库内的记录求和、平均值、最大值、最小值的操作

select sum(score) from ba; 返回分数总和

select count(score) from ba; 进行分数的计数

select count(addr) from ba; 计数addr,null不会被计数

select avg(score) from ba;计算成绩的平均数

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 反转

select length(‘abcdefg’); 返回字符的长度

select trim(‘    mlb’); 去除指定格式，头部格式

select concat(‘Fuck’,’You’,’Bady’);将Fuck，You和Bady拼成一个字符

select concat(‘Fuck’,trim(‘     You),’Bady’);可以结合其他函数，如trim

select upper(‘Fuck You Bady’); 将字符串的所有字母变成大写

select lower('FUCK YOU BADY');将所有字母变成小写

select right(‘Fuck You Bady’,4)；返回字符串后三个字符

select concat(left('Fuck',1),right('fuck',3));把字符串的前一个字母和后三个字母拼起来

select repeat(‘fuck’,4)；重复输出fuck4次

select length(space(3));返回三个空格

select replace(‘Fuck You Bady’,’You’,’Me’);中间的You换成Me

select replace(‘accde’,’cc’,’bb’);

select strcmp(12,13);比较12，13，小于返回-1，等于返回0，大于返回1

select substring(‘abcdefghi’,4,4);返回字符串第4个字符开始的4个字符

select reverse(left(‘abcdefghi’,4));返回字符串前4个，然后反转输出

select left(reverse(‘abcdefghi’),4)；先将字符反转，再输出前4个字符；

4.日期时间函数

字符串函数           描述

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

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

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

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

week(x)                 返回日期 x 是年度第几个星期

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

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

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

dayofweek(x)        返回 x 是星期几，1 星期日，2 星期一

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

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

select curdate();返回年月日

select curtime();返回当前时间

select now();返回当前时间

select month(‘2021-10-29’);返回月份

select week(‘2021-10-29’); 返回当前日期是一年中的第几周

select hour(curtime());返回当前时间的小时

select minute(curtime());返回当前的分钟

select second(curtime());返回当前的秒

select dayofweek(curdate());返回本周的第几天，周日位第一天

select dayofmonht(curdate());当前日期是当月的几号

select dayofyear(curdate());当前日期是今年的第几天

二、存储过程

1.概述

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

2.简介

①存储过程是一组为了完成特定功能的SQL语句集合。

②存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高

3.开发

存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、 在不同的应用程序或平台上执行相同的函数等等。

4.存储过程的优点

①执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率

②SQL语句加上控制语句的集合，灵活性高

③在服务器端存储，客户端调用时，降低网络负载

④可多次重复被调用，可随时修改，不影响客户端调用

⑤可完成所有的数据库操作，也可控制数据库的信息访问权限

5.语法

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>

[过程参数[,…] ] 格式

<过程名>：尽量避免与内置的函数或字段重名

<过程体>：语句

[ IN | OUT | INOUT ] <参数名><类型>

6.操作

delimiter &&               #将语句的结束符号从分号;临时改为两个$$(可以自定义)

create procedure dic()  #创建存储过程，过程名为dic，不带参数

begin                           #过程体以关键字 BEGIN 开始

create table fa(id int(10),name char(10),score int(10));

insert into fa values(1,’zhangsan’,20),(2,’lisi’,30),(3,’wangwu’,40);

select * from fa;            #过程体语句

end &&                       #过程体以关键字 END 结束

delimiter ;                    #将语句的结束符号恢复为分号

调用存储过程

call dic();

①存储过程的主体都分,被称为过程体

②以BEGIN开始，以END结束，若只有一条sQL语句，则可以省略BEGIN-END

③以DELIMITER开始和结束

7.查看存储过程

show create procedure dic\G;

show procedure status like ‘dic’\G

7.存储过程的参数

①IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

②OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

③INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

即表示调用者向过程传入值，又表示过程向调用者传出值（只能是变量)

delimiter &&

create procedure proc(in inname varchar(40))

begin

select * from fa where name=inname;

end &&

delimiter ;

call proc('wangwu');

8.修改存储过程

alter procedure <过程名>[<特征>... ]

alter procedure getrole modifies sql data security invoker;

modifies sqldata:表明子程序包含写数据的语句

security:安全等级

invoker:当定义为invoker时，只要执行者有执行权限，就可以成功执行。

9.删除存储过程

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

drop procedure if exists dic;