【MySQL】内置函数

1.1 日期函数的基本使用

• 获取当前日期与日期

mysql> select current_date(), current_time();+----------------+----------------+| current_date() | current_time() |+----------------+----------------+| 2023-08-08     | 12:40:57       |+----------------+----------------+1 row in set (0.00 sec)

• 获取时间戳

mysql> select current_timestamp();+---------------------+| current_timestamp() |+---------------------+| 2023-08-08 12:43:48 |+---------------------+1 row in set (0.00 sec)

• 获取当前日期时间

mysql> select now();+---------------------+| now()               |+---------------------+| 2023-08-08 12:44:51 |+---------------------+1 row in set (0.00 sec)

• 在日期的基础上加日期

mysql> select date_add('2020-01-01', interval 12 month) as res;+------------+| res        |+------------+| 2021-01-01 |+------------+1 row in set (0.01 sec)# 当前日期加上7天mysql> select date_add('2023-08-08', interval 7 day) as res;+------------+| res        |+------------+| 2023-08-15 |+------------+1 row in set (0.00 sec)

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

mysql> select date_sub('2023-08-08', interval 10 day);+-----------------------------------------+| date_sub('2023-08-08', interval 10 day) |+-----------------------------------------+| 2023-07-29  |+-----------------------------------------+1 row in set (0.00 sec)

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

mysql> select datediff('2022-6-15', '2022-1-4');+-----------------------------------+| datediff('2022-6-15', '2022-1-4') |+-----------------------------------+|   162 |+-----------------------------------+1 row in set (0.01 sec)

1.2 案例1

• 创建生日表

mysql> create table tmp(    -> id bigint primary key auto_increment,    -> birthday date not null    -> );Query OK, 0 rows affected (0.03 sec)

• 插入日期

mysql> insert into tmp (birthday) values ('1991-1-1');Query OK, 1 row affected (0.01 sec)mysql> insert into tmp (birthday) values ('1999-2-1');Query OK, 1 row affected (0.01 sec)# 可以通过函数插入，插入时间，mysql也会得到日期mysql> insert into tmp (birthday) values (current_date());Query OK, 1 row affected (0.00 sec)# 如果插入时间戳，mysql也会自动截取日期部分mysql> insert into tmp (birthday) values (current_timestamp());Query OK, 1 row affected, 1 warning (0.00 sec)# 方便观察可以带上date函数mysql> insert into tmp (birthday) values (date(current_timestamp()));Query OK, 1 row affected (0.01 sec)mysql> select * from tmp;+----+------------+| id | birthday   |+----+------------+|  1 | 1991-01-01 ||  2 | 1999-02-01 ||  3 | 2023-08-08 ||  4 | 2023-08-08 ||  5 | 2023-08-08 |+----+------------+5 rows in set (0.00 sec)

1.3 案例2

• 创建一个留言表

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

• 插入数据

mysql> insert into msg (content, sendtime) values ('评论1', now());Query OK, 1 row affected (0.00 sec)mysql> insert into msg (content, sendtime) values ('评论2', now());Query OK, 1 row affected (0.01 sec)mysql> select * from msg;+----+---------+---------------------+| id | content | sendtime            |+----+---------+---------------------+|  1 | 评论1   | 2023-08-08 13:29:51 ||  2 | 评论2   | 2023-08-08 13:29:56 |+----+---------+---------------------+2 rows in set (0.00 sec)

• 查询在5分钟内发布的评论

mysql> select content,sendtime from msg where sendtime > date_sub(now(),interval 5 minute);+---------+---------------------+| content | sendtime            |+---------+---------------------+| 评论2   | 2023-08-08 13:29:56 |+---------+---------------------+1 row in set (0.00 sec)

• 获取字符集

mysql> select charset('123');+----------------+| charset('123') |+----------------+| utf8           |+----------------+1 row in set (0.00 sec)mysql> select charset(123);+--------------+| charset(123) |+--------------+| binary       |+--------------+1 row in set (0.00 sec)# 获取sendtime的字符集mysql> select charset(sendtime) from msg;+-------------------+| charset(sendtime) |+-------------------+| binary            || binary            |+-------------------+2 rows in set (0.00 sec)

• 字符串拼接

mysql> select concat('aa', 'bc', '123');+---------------------------+| concat('aa', 'bc', '123') |+---------------------------+| aabc123                   |+---------------------------+1 row in set (0.00 sec)

• instr(string,substring)返回substring在string中的位置，不存在返回0

mysql> select instr('abcdef', 'bc');+-----------------------+| instr('abcdef', 'bc') |+-----------------------+|                     2 |+-----------------------+1 row in set (0.00 sec)mysql> select instr('abcdef', 'bcf');+------------------------+| instr('abcdef', 'bcf') |+------------------------+|                      0 |+------------------------+1 row in set (0.00 sec)

• 将字符串转成大写和小写

mysql> select ucase('AaBbCc123');+--------------------+| ucase('AaBbCc123') |+--------------------+| AABBCC123          |+--------------------+1 row in set (0.00 sec)mysql> select lcase('AaBbCc123');+--------------------+| lcase('AaBbCc123') |+--------------------+| aabbcc123          |+--------------------+1 row in set (0.00 sec)

• 提取步长个字符

left(string2,length)从string2中，向左截取length个字符

mysql> select left('abc', 2);+----------------+| left('abc', 2) |+----------------+| ab             |+----------------+1 row in set (0.00 sec)

right(string2,length)从string2中，向右截取length个字符

mysql> select right('abc', 2);+-----------------+| right('abc', 2) |+-----------------+| bc              |+-----------------+1 row in set (0.00 sec)

• 求字符串长度

mysql> select length('abcdef');+------------------+| length('abcdef') |+------------------+|                6 |+------------------+1 row in set (0.00 sec)

看一张成绩表：

mysql> select * from exam_result;+----+--------+---------+------+---------+| id | name   | chinese | math | english |+----+--------+---------+------+---------+|  1 | 张三   |      67 |   98 |      56 ||  2 | 李四   |      87 |   78 |      77 ||  3 | 王五   |      88 |   98 |      90 ||  4 | 赵六   |      82 |   84 |      67 ||  5 | 田七   |      55 |   85 |      45 ||  6 | 孙八   |      70 |   73 |      78 ||  7 | 周九   |      75 |   65 |      30 |+----+--------+---------+------+---------+7 rows in set (0.00 sec)

• 以“XXX的语文是XXX分，数学XXX分，英语XXX分”的方式显示

mysql> select concat('姓名: ', name, ' 语文: ', chinese, ' 数学: ', math, ' 英语: ', english) msg from exam_result;+-------------------------------------------------+| msg                 |+-------------------------------------------------+| 姓名: 张三 语文: 67 数学: 98 英语: 56           || 姓名: 李四 语文: 87 数学: 78 英语: 77           || 姓名: 王五 语文: 88 数学: 98 英语: 90           || 姓名: 赵六 语文: 82 数学: 84 英语: 67           || 姓名: 田七 语文: 55 数学: 85 英语: 45           || 姓名: 孙八 语文: 70 数学: 73 英语: 78           || 姓名: 周九 语文: 75 数学: 65 英语: 30           |+-------------------------------------------------+7 rows in set (0.00 sec)

• 将学生表中所有名字中的‘张’的替换成’帅’

mysql> select replace(name, '张', '帅') from exam_result;+-----------------------------+| replace(name, '张', '帅')   |+-----------------------------+| 帅三                        || 李四                        || 王五                        || 赵六                        || 田七                        || 孙八                        || 周九                        |+-----------------------------+7 rows in set (0.00 sec)mysql> select name  from exam_result;+--------+| name   |+--------+| 张三   || 李四   || 王五   || 赵六   || 田七   || 孙八   || 周九   |+--------+7 rows in set (0.00 sec)

注意select配合replace并不会修改原始数据库。

• 截取学生表中name字段的第二个到第三个字符

mysql> select name,substring(name,2,2) from exam_result;+-----------+---------------------+| name      | substring(name,2,2) |+-----------+---------------------+| 张三      | 三                  || 李四      | 四                  || 王五      | 五                  || 赵六      | 六                  || 田七      | 七                  || 孙八      | 八                  || 周九      | 九                  || 曹孟德    | 孟德                |+-----------+---------------------+8 rows in set (0.00 sec)

• 去除字符串左右空格

mysql> select ltrim ('  你好  ');+----------------------+| ltrim ('  你好  ')   |+----------------------+| 你好                 |+----------------------+1 row in set (0.01 sec) mysql> select rtrim ('  你好  ');+----------------------+| rtrim ('  你好  ')   |+----------------------+|   你好               |+----------------------+1 row in set (0.01 sec) mysql> select trim('  你好  ');+--------------------+| trim('  你好  ')   |+--------------------+| 你好               |+--------------------+1 row in set (0.00 sec)

• 基本用法

# 取绝对值mysql> select abs(-12);+----------+| abs(-12) |+----------+|       12 |+----------+1 row in set (0.00 sec)# 十进制转二进制mysql> select bin(10);+---------+| bin(10) |+---------+| 1010    |+---------+1 row in set (0.00 sec)# 十进制转16进制mysql> select hex(10);+---------+| hex(10) |+---------+| A       |+---------+1 row in set (0.00 sec)# 任意进制转换（十进制转三进制）mysql> select conv(10,10,3);+---------------+| conv(10,10,3) |+---------------+| 101           |+---------------+1 row in set (0.00 sec)# 保留n位小数mysql> select format(1.23456, 3);+--------------------+| format(1.23456, 3) |+--------------------+| 1.235              |+--------------------+1 row in set (0.00 sec)# 取模mysql> select mod(10, 3);+------------+| mod(10, 3) |+------------+|          1 |+------------+1 row in set (0.00 sec)# 生成随机数（rand范围0~1）mysql> select rand() * 100;+-------------------+| rand() * 100      |+-------------------+| 66.49860438564662 |+-------------------+1 row in set (0.00 sec)

• 查询当前用户

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

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

创建表：

mysql> create table user(     -> id int unsigned primary key auto_increment,    -> name varchar(20) not null,     -> password char(32) not null    -> );Query OK, 0 rows affected (0.04 sec)

插入密码

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

在数据库不允许出现明文密码。

需要加密：

mysql> insert into user (name,password) values ('李四',md5('123'));Query OK, 1 row affected (0.00 sec)mysql> select * from user;+----+--------+----------------------------------+| id | name   | password                         |+----+--------+----------------------------------+|  1 | 张三   | 123456                           ||  2 | 李四   | 202cb962ac59075b964b07152d234b70 |+----+--------+----------------------------------+2 rows in set (0.00 sec)

我们进行查找也要通过摘要查找。

mysql> select name,password from user where name='李四' and password=md5('123');+--------+----------------------------------+| name   | password                         |+--------+----------------------------------+| 李四   | 202cb962ac59075b964b07152d234b70 |+--------+----------------------------------+1 row in set (0.00 sec)

• 也可以使用password函数对密码进行加密。

mysql> select password('123');+-------------------------------------------+| password('123')                           |+-------------------------------------------+| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |+-------------------------------------------+1 row in set, 1 warning (0.00 sec)

• 是null返回第二个，不是null返回第一个

mysql> select ifnull(null, 123);+-------------------+| ifnull(null, 123) |+-------------------+|               123 |+-------------------+1 row in set (0.00 sec)mysql> select ifnull(222, 123);+------------------+| ifnull(222, 123) |+------------------+|              222 |+------------------+1 row in set (0.00 sec)

来源地址：https://blog.csdn.net/qq_66314292/article/details/132164381