【MySQL】 MySQL的内置函数——日期函数、字符串函数、数学函数、聚合函数、其他函数

MySQL

  

1. 日期函数

  在MySQL中，提供了多种时间函数供我们使用，其中包括用于查看时间的函数和计算日期的函数。以下是一些常用的MySQL时间函数及其描述：

函数名称	描述
current date()	当前日期
current time()	当前时间
current timestamp()	当前时间戳
date(datetime)	返回datetime参数的日期部分
date add(date, interval d value type)	在date中添加日期或时间，interval后的数值单位可以是：year minute secondday
date sub(date, interval d value type)	在date中减去日期或时间，interval后的数值单位可以是：year minute second day
datediff(datel, date2)	两个日期的差，单位是天
now()	当前日期时间

  

1.1 查看时间

获取当前的年月日：

select current_date();

  

获取当前的时分秒：

select current_time();

  
获取当前的时间戳：

  这里的时间戳相当于是年月日加上时分秒。

select current_timestamp();

  

1.2 对时间进行计算

在日期的基础上加日期：

  DATE_ADD() 是 MySQL 中的一个函数，用于在日期上添加指定的时间间隔。它的语法如下：

DATE_ADD(date, INTERVAL expr unit);

  date 是要操作的日期值。

  expr 是希望添加的时间间隔数值。

  unit 是时间间隔的单位，例如 DAY、MONTH、YEAR 等。

  

select date_add('2024-3-17',interval 10 day);

  

在日期的基础上减去时间：

  DATE_SUB() 是 MySQL 中的一个函数，用于从日期时间值中减去指定的时间间隔。它允许你根据需求在日期或日期时间字段中减去年、月、日、小时、分钟或秒钟等时间单位。以下是 DATE_SUB() 函数的基本语法：

DATE_SUB(date, INTERVAL expr unit);

  date 是要进行调整的日期或日期时间表达式。

  expr 是一个表示时间间隔大小的表达式，可以是一个整数或者一个与 date 类型相同的表达式。

  unit 是时间单位，如 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 等。

  

select date_sub('2024-3-17',interval 10 day);

  

计算两个日期之间相差多少天：

  DATEDIFF 用于计算两个日期之间的差异。以下是 DATEDIFF 函数的介绍：

DATEDIFF(end_date, start_date);

  end_date：表示要计算的时间段的结束日期。

  start_date：表示要计算的时间段的开始日期。

  

select datediff('2024-3-17','2023-3-17');

  

例子：创建一张留言表，用于记录用户发送信息的时间：

  我们使用 id 作为主键同时进行自增长，设置内容不为空，同时包含一个 datetime 类型的变量 sendtime 。

mysql> create table msg(
    -> id int primary key auto_increment,
    -> content varchar(30) not null,
    -> sendtime datetime
    -> );

  

打印表结构：

desc msg;

  

插入数据并且打印所有信息：

mysql> insert into msg(content,sendtime) values('hello',now());

mysql> insert into msg(content,sendtime) values('你好',now());

mysql> insert into msg(content,sendtime) values('你也好',now());

  

select * from msg;

  

打印所有留言信息，只显示日期，不显示时间：

select content ,date(sendtime) from msg;

  

打印所有留言信息，只显示时间，不显示日期：

select content ,time(sendtime) from msg;

  

只打印两分钟之内发布的信息：

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

            

2. 字符串函数

  MySQL 提供了许多字符串函数，用于在查询中处理和操作字符串数据。以下是一些常用的 MySQL 字符串函数及其描述：

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

  

2.1 字符串查找

创建一张表其中包括学生的 id 、姓名和语文数学成绩：

mysql> create table emp(
    -> id int primary key auto_increment,
    -> chinese int comment '语文成绩',
    -> math int comment '数学成绩',
    -> name varchar(20)
    -> );

  

插入学生的信息并且打印查看：

mysql> insert into emp(chinese,math,name) values(90,70,'张三');

mysql> insert into emp(chinese,math,name) values(87,70,'李四');

mysql> insert into emp(chinese,math,name) values(85,80,'王五');

mysql> select * from emp;

  

获取emp表的name列的字符集：

select charset(name) from emp;

  

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

  CONCAT函数用于连接两个或多个字符串值，并返回连接后的字符串。这个函数非常有用，特别是当我们需要将多个字段或值组合成一个字符串时。

CONCAT(str1, str2, ..., strN)

  str1, str2, …, strN：这是要连接的字符串。它们可以是字符串值、列名、其他CONCAT函数，或者返回字符串值的表达式。

  CONCAT函数返回一个字符串，该字符串是输入字符串参数的连接。

  

select concat(name,'的语文成绩为',chinese,'分，数学成绩为',math,'分') 
as '分数' from emp;

  

求学生表中学生姓名占用的字节数：

  因为这里的字符集编码为utf8，utf8中一个汉字占三个字节的大小， 在文段中，name都是两个文字，所以输出的都是6。

select length(name) ,name from emp;

  

2.2 字符串修改显示

将emp表中所有名字中有 三 的替换成 山：

select replace(name,'三','山'),name from emp;

  

截取emp表中name字段的第二个到第三个字符：

  SUBSTRING 函数用于从一个字符串中提取子字符串。

SUBSTRING(str, pos, len)

  str：需要提取子字符串的原始字符串。

  pos：开始提取的位置（基于1的索引）。

  len：可选参数，表示要提取的字符数。如果省略，将返回从pos开始到字符串末尾的所有字符。

  另一种形式：

SUBSTRING(str FROM pos FOR len)

  FROM pos：指定开始提取的位置。

  FOR len：指定要提取的字符数。

  

select substring(name,1,1),name from emp;

  

  以上对字符串的修改都不会影响到原来的字段信息，只是改变了显示的字符串，若想要更改字段中的字符串，请进行CURD操作。

  

以首字母小写的方式显示所有学生的姓名:

  LCASE（或LOWER）函数用于将字符串转换为小写。这个函数在处理大小写不敏感的查询或格式化输出时非常有用。

LCASE(str)

  或：

LOWER(str)

  其中 str 是要转换为小写的字符串

  

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

            

3. 数学函数

  MySQL 提供了丰富的数学函数，用于在查询中执行各种数学运算。以下是一些常用的 MySQL 数学函数及其描述：

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

  

绝对值：

select abs(-32.4);

  

向上取整：

select ceiling(54.3);

  

向下取整：

select floor(54.3);

  

保留2位小数位数（小数四舍五入)：

select format(12.34567,3);

  

产生随机数：

select rand();

            

4. 聚合函数

  MySQL 提供了多种聚合函数，用于对一组值执行计算并返回单个值。这些函数在数据分析和报告生成中特别有用，因为它们允许你快速总结大量数据。以下是一些常用的 MySQL 聚合函数及其描述：

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

  

统计学生的个数：

select count(name) from emp;

  

统计学生数学成绩的总分：

select sum(math) from emp;

  

计算学生的平局成绩：

select avg(chinese+math) 平均分 from emp;

  

打印数学成绩的最高分：

select max(math) from emp;

            

5. 其他函数

查询当前用户：

select user();

  

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

select md5('admin')

  

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

select database();

  

password()函数，MySQL数据库使用该函数对用户加密：

select password('root');

  

ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值：

select ifnull('abc', '123');