【MySQL】内置函数

思维导图

学习目标

       这一篇博客学习MySQL中一些内置函数。

一、日期函数

       一般地，日期是年月日，时间是时分秒。

函数名称	描述
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回datetime参数的日期部分
date_add(date, interval d_value_type)	在date中添加日期或时间 interval 后的数值单位可以是：year，minute，second，day
date_sub(date, interval d_value_type)	在date中减去日期或时间 interval 后的数值单位可以是：year，minute，second，day
datediff(date1, date2)	两个日期的差（左 - 右），单位是天
now()	当前日期时间

1.1 函数举例 

current_date()   获取当前日期

                  

current_time()   获取当前时间

                  

current_timestamp() 

       获取当前的时间戳，并转化为：年月日 + 时分秒 

             

date(datetime)  获取日期时间中的年月日

       可将传入上述的current系列函数，也可以直接传入一个日期时间

date_add(date, interval d_value_type)  进行日期的相加

       进行日期时间加上年/月/日/小时/分钟/秒数

date_sub(date, interval d_value_type)  进行日期的相减

       进行日期时间减去年/月/日/小时/分钟/秒数

datediff(date1, date2)  进行两个日期的差值计算（左 - 右）

now()  显示当前时间（个人觉得和时间戳差不多）

                   

1.2 一些基本案例

1.2.1 案例1：创建一张表，记录生日

create table tmp( id int primary key, birthday date not null ); // 创建一张生日表
insert into tmp values(1, '2010-01-01'), (2, '2024-05-21'), (3, current_date); // 添加时间
select * from tmp; // 查看表中的数据

 

1.2.2 案例2：创建一个留言表

create table msg( id int primary key auto_increment, content varchar(30) not null, sendtime datetime ); // 创建一张留言表
// 插入数据
insert into msg(content, sendtime) values('hello1', now());
insert into msg(content, sendtime) values('hello2', now());
// 查看留言表
select * from msg;

• 插入数据 

• 显示所有留言消息，发布日期只显示日期，不用显示时间

select content, date(sendtime) from msg;

• 请查询在2分钟内发布的帖子

select * from msg where date_add(sendtime, interval 2 minute) > now();

二、字符串函数

函数名称	描述
charset(string)	返回字符串字符集
concat(string, string,[......])	连接字符串
instr(string, substring)	返回substring在string中出现的位置，没有则返回0
ucase(string)	转换为大写
lcase(string)	转换为小写
left(string, length)	从string中的左边起取length个字符
right(string, length)	从string中的右边起取length个字符
length(string)	string的长度
replace(string, search_str, replace_str)	在string中用replace_str替换search_str
strcmp(string1, string2)	逐字符比较两个字符串的大小
substring(string, postion, length)	从string的postion开始，取length个字符
ltrim(string)   rtrim(string)   trim(string)	取出前空格或后空格

2.1 函数举例 

charset(string)   返回字符串的字符集

           

concat(string, string, [......])   连接字符串

    

instr(string, substring)   

       返回substring在string中的位置，没有则返回0

ucase(string)  将小写字母转化为大写字母

lcase(string)  将小写字母转换为大写字母

left(string, length)   将string从左边截取length长度的字符串

right(string, length)   将string从右边截取length长度的字符串

length(string)   计算字符串的字节大小

       因为在字符串中，汉字的字节大小为3个字节，而字母的大小为1个字节。

replace(string, search_string, replace_string)   

       将string中的search_string替换为replace_string

strcmp(string1, string2)  逐字符进行比较string1和string2

       左边字符串与右边字符串，如果左边大，则返回1,；如果一样大，则返回0,；如果右边大。则返回-1

substring(string, postion, length) 

       截取string的postion位置向后的length长度的字符串

ltrim(string)   rtrim(string)   trim(string)

       取出前空格，或者取出后空格， 或者取出前空格和后空格

2.2 一些案例

• 要求显示exam_result表中的信息，显示格式为：“XXX的语文是XXX分，数学是XXX分，英语是XXX分”

select concat(name, '的语文:', chinese, ',数学:', math, ',英语:', english) as '分数' from student;

• 以首字母小写的方式显示所有员工的姓名

select concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) from emp;

三、数学函数

函数名称	描述
abs(number)	绝对值函数
bin(decimal_number)	十进制转换为二进制
hex(decimal_number)	十进制转换为十六进制
conv(number, from_base, to_base)	进制转换
ceiling(number)	向上取整
floor(number)	向下取整
format(number, decimal_places)	格式化，保留小数位数
rand()	返回随机浮点数，范围为[0.0, 1.0)
mod(number, denominator)	取模，求余

3.1 函数举例

abs(number)   绝对值函数

                         

bin(decimal_number)   十进制转换为二进制

                           

hex(decimal_number)   十进制转换为十六进制

                          

conv(number, from_base, to_base)   进制转换

       想转换为几进制就转换为几进制

                 

ceiling(number)   向上取整 

                      

floor(number)   向下取整

                          

format(number, decimal_places)   格式化，保留小数位数

rand()  返回随机数，范围为[0.0, 1.0)

                           

mod(number, denominator)  求模，取余

                         

3.2 补充一些知识点

3.2.1 向上取整，向下取整，0向取整

3.2.2 负数取模

       取模的定义：如果a和d是两个自然数，可以证明存在两个唯一的整数q和r，满足 a = q * d + r，q 为整数，且0 <= |r| <= |d|。其中，q被称为商，r被称为余数。

       在不同的语言中，同一个计算表达式，负数”取模"的结果是不同的（比如，-10 % 3 在C语言中的结果是-1，在python中的结果为2），我们可以称之为正余数和符余数。 

余数取决于商，由于每种语言的取整方式不同，导致不同语言的取模结果是不同的，

四、其他函数

函数名称	描述
user()	查询当前用户
md5(string)	对一个字符串进行md5摘要，摘要后得到一个32位字符串
database()	显示当前正在使用的数据库
password()	MySQL数据库使用该函数对用户加密
ifnull(val1, val2)	如果val1为null，返回val2，否则返回val1的值

4.1 函数举例

user()   查询当前用户

                   

md5(string)   加密使用

       对一个字符串进行md5摘要，摘要后得到一个32位字符串

        

database()  显示当前正在使用的数据库

                    

password()  MySQL使用该函数对用户加密

ifnull(val1, val2)   类似于三目运算符

       如果val1为null，返回val2，否则返回val1的值