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

MySQL

1. 日期函数

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

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 字符串函数及其描述：

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 数学函数及其描述：

绝对值：

select abs(-32.4);

向上取整：

select ceiling(54.3);

向下取整：

select floor(54.3);

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

select format(12.34567,3);

产生随机数：

select rand();

4. 聚合函数

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

统计学生的个数：

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');