【MySQL】第十弹---编程必备：全面掌握日期、字符串、数学及其它关键函数

06-01 1794阅读

✨个人主页：熬夜学编程的小林

💗系列专栏：【C语言详解】【数据结构详解】【C++详解】【Linux系统编程】【MySQL】

1. 函数

1.1 日期函数

1.2 字符串函数

1.3 数学函数

1.4 其它函数

1. 函数

1.1 日期函数

获得年月日：

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-09-17     |
+----------------+
1 row in set (0.00 sec)

获得时分秒：

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 21:37:52       |
+----------------+
1 row in set (0.00 sec)

获得时间戳(转化为日期+时间)：

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-09-17 21:39:02 |
+---------------------+
1 row in set (0.00 sec)

获得当前时间(日期+时间)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-09-17 21:44:10 |
+---------------------+
1 row in set (0.00 sec)

在日期的基础上加日期：

# 加10天
mysql> select date_add('2022-01-1',interval 10 day);
+---------------------------------------+
| date_add('2022-01-1',interval 10 day) |
+---------------------------------------+
| 2022-01-11                            |
+---------------------------------------+
1 row in set (0.00 sec)
# 加10分钟
mysql> select date_add('2022-01-1',interval 10 minute);
+------------------------------------------+
| date_add('2022-01-1',interval 10 minute) |
+------------------------------------------+
| 2022-01-01 00:10:00                      |
+------------------------------------------+
1 row in set (0.00 sec)
# 加10秒
mysql> select date_add('2022-01-1',interval 10 second);
+------------------------------------------+
| date_add('2022-01-1',interval 10 second) |
+------------------------------------------+
| 2022-01-01 00:00:10                      |
+------------------------------------------+
1 row in set (0.00 sec)

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

# 减15天
mysql> select date_sub('2023-10-12',interval 15 day);
+----------------------------------------+
| date_sub('2023-10-12',interval 15 day) |
+----------------------------------------+
| 2023-09-27                             |
+----------------------------------------+
1 row in set (0.00 sec)
# 减15分钟
mysql> select date_sub('2023-10-12',interval 15 minute);
+-------------------------------------------+
| date_sub('2023-10-12',interval 15 minute) |
+-------------------------------------------+
| 2023-10-11 23:45:00                       |
+-------------------------------------------+
1 row in set (0.00 sec)
# 减15秒
mysql> select date_sub('2023-10-12',interval 15 second);
+-------------------------------------------+
| date_sub('2023-10-12',interval 15 second) |
+-------------------------------------------+
| 2023-10-11 23:59:45                       |
+-------------------------------------------+
1 row in set (0.00 sec)

计算两个日期之间相差多少天(前面 - 后面)：

mysql> select datediff('2023-10-15','2023-9-8');
+-----------------------------------+
| datediff('2023-10-15','2023-9-8') |
+-----------------------------------+
|                                37 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2023-9-8','2023-10-15');
+-----------------------------------+
| datediff('2023-9-8','2023-10-15') |
+-----------------------------------+
|                               -37 |
+-----------------------------------+
1 row in set (0.00 sec)

案例-1：

创建一张表，记录生日

mysql> create table tmp(
    -> id int primary key auto_increment,
    -> birthday date);
Query OK, 0 rows affected (0.03 sec)
mysql> desc tmp;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| birthday | date    | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

添加当前日期：

mysql> insert into tmp (birthday) values(current_date());
Query OK, 1 row affected (0.01 sec)
mysql> select * from tmp;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2024-09-17 |
+----+------------+
1 row in set (0.00 sec)

案例-2：

创建一个留言表

mysql> create table msg(
    -> id int primary key auto_increment,
    -> content varchar(30) not null,
    -> sendtime datetime);
Query OK, 0 rows affected (0.02 sec)
mysql> desc msg;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| content  | varchar(30) | NO   |     | NULL    |                |
| sendtime | datetime    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

插入数据

mysql> insert into msg (content,sendtime) values('hello mysql',now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into msg (content,sendtime) values('hello friend',now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from msg;
+----+--------------+---------------------+
| id | content      | sendtime            |
+----+--------------+---------------------+
|  1 | hello mysql  | 2024-09-17 21:50:49 |
|  2 | hello friend | 2024-09-17 21:50:56 |
+----+--------------+---------------------+
2 rows in set (0.00 sec)

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

mysql> select content,date(sendtime) from msg;
+--------------+----------------+
| content      | date(sendtime) |
+--------------+----------------+
| hello mysql  | 2024-09-17     |
| hello friend | 2024-09-17     |
+--------------+----------------+
2 rows in set (0.00 sec)

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

表中插入数据的时间 + 2分钟大于当前时间就是两分钟内发布的帖子。

# 直接查看两分钟内f发布的帖子，已经过了两分钟因此没有查到
mysql> select * from msg where date_add(sendtime,interval 2 minute) > now();
Empty set (0.00 sec)
# 再插入一条信息
mysql> insert into msg (content,sendtime) values('excuse me',now());
Query OK, 1 row affected (0.01 sec)
# 继续查两分钟内的信息
mysql> select * from msg where date_add(sendtime,interval 2 minute) > now();
+----+-----------+---------------------+
| id | content   | sendtime            |
+----+-----------+---------------------+
|  3 | excuse me | 2024-09-17 21:55:52 |
+----+-----------+---------------------+
1 row in set (0.00 sec)

1.2 字符串函数

获取字符串字符集

mysql> select charset('aaa');
+----------------+
| charset('aaa') |
+----------------+
| utf8           |
+----------------+
1 row in set (0.00 sec)

获取emp表的ename列的字符集

mysql> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
+----------------+
14 rows in set (0.00 sec)

连接字符串

mysql> select concat('abc','123',456);
+-------------------------+
| concat('abc','123',456) |
+-------------------------+
| abc123456               |
+-------------------------+
1 row in set (0.00 sec)

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

mysql> select concat(name,'的语文是',chinese,'分，数学是',math,'分') from exam_result;
+------------------------------------------------------------------+
| concat(name,'的语文是',chinese,'分，数学是',math,'分')           |
+------------------------------------------------------------------+
| 唐三藏的语文是134分，数学是98分                                  |
| 猪悟能的语文是176分，数学是98分                                  |
| 曹孟德的语文是140分，数学是90分                                  |
| 刘玄德的语文是110分，数学是115分                                 |
| 孙权的语文是140分，数学是73分                                    |
| 宋公明的语文是150分，数学是95分                                  |
+------------------------------------------------------------------+
6 rows in set (0.00 sec)

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

# 求的是字节大小，utf8编码下中文占三个字节
mysql> select name,length(name) from student;
+--------+--------------+
| name   | length(name) |
+--------+--------------+
| 张三   |            6 |
| 李四   |            6 |
| 王五   |            6 |
| 孙权   |            6 |
| 妲己   |            6 |
+--------+--------------+
5 rows in set (0.00 sec)

注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；

如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数

（与字符集编码有关）

求字符串占用字节数

# 英文字符和数字占一个字节，中文占3字节(utf8编码)
mysql> select length('abcd中');
+-------------------+
| length('abcd中')  |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

判断子串是否在主串中

在子串中返回起始下标(从1开始)，否则返回0.

mysql> select instr('abcdef123','123a') bool;
+------+
| bool |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
mysql> select instr('abcdef123','123') bool;
+------+
| bool |
+------+
|    7 |
+------+
1 row in set (0.00 sec)

转换成大小写

# 转换为大写
mysql> select ucase('abcdef') res;
+--------+
| res    |
+--------+
| ABCDEF |
+--------+
1 row in set (0.00 sec)
# 转换为小写
mysql> select lcase('ABCDEF') res;
+--------+
| res    |
+--------+
| abcdef |
+--------+
1 row in set (0.00 sec)

从左右获取字符串

# 从左边取3个字符
mysql> select left('abcdef',3);
+------------------+
| left('abcdef',3) |
+------------------+
| abc              |
+------------------+
1 row in set (0.00 sec)
# 从右边取3个字符
mysql> select right('abcdef',3);
+-------------------+
| right('abcdef',3) |
+-------------------+
| def               |
+-------------------+
1 row in set (0.00 sec)

将EMP表中所有名字中有S的替换成'上海'

mysql> select ename from emp;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.00 sec)
mysql> select replace(ename,'S','上海') from emp;
+-----------------------------+
| replace(ename,'S','上海')   |
+-----------------------------+
| 上海MITH                    |
| ALLEN                       |
| WARD                        |
| JONE上海                    |
| MARTIN                      |
| BLAKE                       |
| CLARK                       |
| 上海COTT                    |
| KING                        |
| TURNER                      |
| ADAM上海                    |
| JAME上海                    |
| FORD                        |
| MILLER                      |
+-----------------------------+
14 rows in set (0.00 sec)

截取EMP表中ename字段的第二个到第三个字符

# 原表数据
mysql> select ename from emp;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.00 sec)
# 截取后数据
mysql> select substring(ename,2,2) from emp;
+----------------------+
| substring(ename,2,2) |
+----------------------+
| MI                   |
| LL                   |
| AR                   |
| ON                   |
| AR                   |
| LA                   |
| LA                   |
| CO                   |
| IN                   |
| UR                   |
| DA                   |
| AM                   |
| OR                   |
| IL                   |
+----------------------+
14 rows in set (0.00 sec)

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

截取姓名的第一个字符，将第一个字符转换为小写，再拼接上从第二个字符开始的姓名。

mysql> select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;
+--------------------------------------------------------+
| concat(lcase(substring(ename,1,1)),substring(ename,2)) |
+--------------------------------------------------------+
| sMITH   |
| aLLEN   |
| wARD    |
| jONES   |
| mARTIN  |
| bLAKE   |
| cLARK   |
| sCOTT   |
| kING    |
| tURNER  |
| aDAMS   |
| jAMES   |
| fORD    |
| mILLER  |
+--------------------------------------------------------+
14 rows in set (0.00 sec)

去除前后空格

ltrim() 去除前空格，rtrim() 去除后空格，trim() 去除前后空格。

# 去除前空格
mysql> select ltrim('   nihao           ') res;
+------------------+
| res              |
+------------------+
| nihao            |
+------------------+
1 row in set (0.00 sec)
# 去除后空格
mysql> select rtrim('   nihao           ') res;
+----------+
| res      |
+----------+
|    nihao |
+----------+
1 row in set (0.00 sec)
# 去除后空格
mysql> select trim('   nihao           ') res;
+-------+
| res   |
+-------+
| nihao |
+-------+
1 row in set (0.00 sec)

1.3 数学函数

绝对值

mysql> select abs(100);
+----------+
| abs(100) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)
mysql> select abs(-100);
+-----------+
| abs(-100) |
+-----------+
|       100 |
+-----------+
1 row in set (0.00 sec)

向上取整(有小数则数值变大)

mysql> select ceiling(3.0);
+--------------+
| ceiling(3.0) |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(3.1);
+--------------+
| ceiling(3.1) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(-3.1);
+---------------+
| ceiling(-3.1) |
+---------------+
|            -3 |
+---------------+
1 row in set (0.00 sec)
mysql> select ceiling(-3.9);
+---------------+
| ceiling(-3.9) |
+---------------+
|            -3 |
+---------------+
1 row in set (0.00 sec)

向下取整(有小数则数值变大)

mysql> select floor(3.9);
+------------+
| floor(3.9) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(3.1);
+------------+
| floor(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(-3.1);
+-------------+
| floor(-3.1) |
+-------------+
|          -4 |
+-------------+
1 row in set (0.00 sec)

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

mysql> select format(12.34125,2);
+--------------------+
| format(12.34125,2) |
+--------------------+
| 12.34              |
+--------------------+
1 row in set (0.00 sec)
# 5及以上则进1
mysql> select format(12.34525,2);
+--------------------+
| format(12.34525,2) |
+--------------------+
| 12.35              |
+--------------------+
1 row in set (0.00 sec)

产生随机数 [0.0，1.0)，左边可取，右边不可取。

mysql> select rand();
+-------------------+
| rand()            |
+-------------------+
| 0.586795278440039 |
+-------------------+
1 row in set (0.00 sec)
# 产生1-10之间的数
mysql> select rand()*10;
+-------------------+
| rand()*10         |
+-------------------+
| 8.999853217042846 |
+-------------------+
1 row in set (0.00 sec)
# 产生1-10的整数，使用format保留0位小数
mysql> select format(rand()*10,0);
+---------------------+
| format(rand()*10,0) |
+---------------------+
| 7                   |
+---------------------+
1 row in set (0.00 sec)

1.4 其它函数

user() 查询当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

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

可以使用对密码进行加密转换。

mysql> select md5('abcd123');
+----------------------------------+
| md5('abcd123')                   |
+----------------------------------+
| 79cfeb94595de33b3326c06ab1c7dbda |
+----------------------------------+
1 row in set (0.00 sec)

创建用户表

mysql> create table user1
    -> (id int primary key auto_increment,
    -> password char(32));
Query OK, 0 rows affected (0.02 sec)

插入密码

mysql> insert into user1 (password) values(123456);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user1;
+----+----------+
| id | password |
+----+----------+
|  1 | 123456   |
+----+----------+
1 row in set (0.00 sec)

通过上下键找不到插入密码的那行，因为密码会被mysql自动隐藏，但是如果直接能够使用select查看密码不安全，我们可以使用md5进行加密转换。

加密

mysql> insert into user1 (password) values(md5(123456));
Query OK, 1 row affected (0.01 sec)
mysql> select * from user1;
+----+----------------------------------+
| id | password                         |
+----+----------------------------------+
|  1 | 123456                           |
|  2 | e10adc3949ba59abbe56e057f20f883e |
+----+----------------------------------+
2 rows in set (0.00 sec)
mysql> delete from user1 where id = 2;
Query OK, 1 row affected (0.01 sec)
mysql> insert into user1 (password) values(md5(123));
Query OK, 1 row affected (0.00 sec)
mysql> select * from user1;
+----+----------------------------------+
| id | password                         |
+----+----------------------------------+
|  1 | 123456                           |
|  3 | 202cb962ac59075b964b07152d234b70 |
+----+----------------------------------+
2 rows in set (0.00 sec)
mysql> select id from user1 where password = '123';
Empty set (0.00 sec)
mysql> select id from user1 where password = md5('123');
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.01 sec)

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

mysql> select password('12');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

对 '12' 字符串进行加密，会报不满足密码要求，此时需要查看密码的最低长度，该字符串的长度大于等于最低长度才能正确使用。

补充

# 查看密码最低长度
show variables like '%password%';
show variables like 'validate_password%';
# 设置密码最低长度
set global validate_password_legnth=4;

查看密码最低长度

mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 4     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

因为密码长度最少为4，因此对'12'进行加密会报错。

正确方式

mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select password('12345');
+-------------------------------------------+
| password('12345')                         |
+-------------------------------------------+
| *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

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

类似于C语言三目表达式，第一个值不为空返回第一个值，为空返回第二个值。

# 第一个值为空返回第二个值
mysql> select ifnull(null,12);
+-----------------+
| ifnull(null,12) |
+-----------------+
|              12 |
+-----------------+
1 row in set (0.00 sec)
# 第一个值不为空返回第一个值
mysql> select ifnull(12,10);
+---------------+
| ifnull(12,10) |
+---------------+
|            12 |
+---------------+
1 row in set (0.00 sec)
mysql> select ifnull(12,null);
+-----------------+
| ifnull(12,null) |
+-----------------+
|              12 |
+-----------------+
1 row in set (0.00 sec)

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。