MySQL-----内置函数

---

前言

---

正文开始!!!

一、日期函数

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

• 获得年月日：

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-04-21     |
+----------------+
1 row in set (0.00 sec)

• 获得时分秒：

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

• 获得时间戳

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-04-21 12:53:03 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-04-21 14:25:06 |
+---------------------+
1 row in set (0.00 sec)

• 在日期的基础上加日期：

mysql> select date_add('1949-10-01',interval 10 day);
+----------------------------------------+
| date_add('1949-10-01',interval 10 day) |
+----------------------------------------+
| 1949-10-11                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('1949-10-01',interval 10 month);
+------------------------------------------+
| date_add('1949-10-01',interval 10 month) |
+------------------------------------------+
| 1950-08-01                               |
+------------------------------------------+
1 row in set (0.00 sec)

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

mysql> select current_time(),date_sub(current_time(),interval 10 year);
+----------------+-------------------------------------------+
| current_time() | date_sub(current_time(),interval 10 year) |
+----------------+-------------------------------------------+
| 14:21:13       | NULL                                      |
+----------------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select current_time(),date_sub(current_time(),interval 10 month);
+----------------+--------------------------------------------+
| current_time() | date_sub(current_time(),interval 10 month) |
+----------------+--------------------------------------------+
| 14:21:18       | NULL                                       |
+----------------+--------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select current_time(),date_sub(current_time(),interval 10 second);
+----------------+---------------------------------------------+
| current_time() | date_sub(current_time(),interval 10 second) |
+----------------+---------------------------------------------+
| 14:21:25       | 14:21:15                                    |
+----------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select current_time(),date_sub(current_time(),interval 10 minute);
+----------------+---------------------------------------------+
| current_time() | date_sub(current_time(),interval 10 minute) |
+----------------+---------------------------------------------+
| 14:21:30       | 14:11:30                                    |
+----------------+---------------------------------------------+
1 row in set (0.00 sec)

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

mysql> select datediff('1949-10-01',current_time());
+---------------------------------------+
| datediff('1949-10-01',current_time()) |
+---------------------------------------+
|                                -26865 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff(current_time(),'1949-10-01');
+---------------------------------------+
| datediff(current_time(),'1949-10-01') |
+---------------------------------------+
|                                 26865 |
+---------------------------------------+
1 row in set (0.00 sec)

案例1:

• 创建一张表，记录生日

Database changed
mysql> create table if not exists birth(
    -> d date not null,
    -> t timestamp
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc birth;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d     | date      | NO   |     | NULL              |                             |
| t     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> insert into birth (d) values ('1949/10/01');
Query OK, 1 row affected (0.00 sec)

mysql> insert into birth (d) values (current_date());
Query OK, 1 row affected (0.00 sec)

mysql> select * from birth;
+------------+---------------------+
| d          | t                   |
+------------+---------------------+
| 1949-10-01 | 2023-04-21 14:13:16 |
| 2023-04-21 | 2023-04-21 14:13:27 |
+------------+---------------------+
2 rows in set (0.01 sec)

mysql> alter table birth change t t datetime;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc birth;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | NO   |     | NULL    |       |
| t     | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into birth (d,t) values (current_date(),current_timestamp());
Query OK, 1 row affected (0.00 sec)

mysql> select * from birth;
+------------+---------------------+
| d          | t                   |
+------------+---------------------+
| 1949-10-01 | 2023-04-21 14:13:16 |
| 2023-04-21 | 2023-04-21 14:13:27 |
| 2023-04-21 | 2023-04-21 14:15:50 |
+------------+---------------------+
3 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-04-21 14:16:12 |
+---------------------+
1 row in set (0.00 sec)

案例2

• 创建一个留言表

mysql> create table if not exists msg(
    -> id int primary key auto_increment,
    -> content varchar(30) not null,
    -> sendtime datetime
    -> );
Query OK, 0 rows affected (0.01 sec)

# 插入数据
mysql> insert into msg (content,sendtime) values ('我是要成为海贼王的男人!!!',now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into msg (content,sendtime) values ('顶楼上',now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into msg (content,sendtime) values ('一定可以!!!',now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into msg (content,sendtime) values ('one pieces',now());
Query OK, 1 row affected (0.00 sec)

# 显示所有留言信息，发布日期显示日期
mysql> select * from msg;
+----+--------------------------------------+---------------------+
| id | content                              | sendtime            |
+----+--------------------------------------+---------------------+
|  1 | 我是要成为海贼王的男人!!!            | 2023-04-21 14:28:11 |
|  2 | 顶楼上                               | 2023-04-21 14:28:33 |
|  3 | 一定可以!!!                          | 2023-04-21 14:28:40 |
|  4 | one pieces                           | 2023-04-21 14:28:48 |
+----+--------------------------------------+---------------------+
4 rows in set (0.00 sec)

# 请查询在2分钟内发布的帖子
mysql> insert into msg (content,sendtime) values ('顶楼上',now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into msg (content,sendtime) values ('顶楼上111',now());
Query OK, 1 row affected (0.00 sec)

# 评论的时间+2min>now()
mysql> select * from msg where date_add(sendtime,interval 2 minute)>now();
+----+--------------+---------------------+
| id | content      | sendtime            |
+----+--------------+---------------------+
|  5 | 顶楼上       | 2023-04-21 14:31:58 |
|  6 | 顶楼上111    | 2023-04-21 14:32:06 |
+----+--------------+---------------------+
2 rows in set (0.00 sec)

# now()-2min<评论的时间
mysql> select * from msg where date_sub(now(),interval 2 minute)<sendtime;
+----+--------------+---------------------+
| id | content      | sendtime            |
+----+--------------+---------------------+
|  5 | 顶楼上       | 2023-04-21 14:31:58 |
|  6 | 顶楼上111    | 2023-04-21 14:32:06 |
+----+--------------+---------------------+
2 rows in set (0.00 sec)

二、字符串函数

函数名称	描述
charset(str)	返回字符串字符集
concat(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_string,replace_str)	在str中用replace_str替换search_str
strcmp(string1,string2)	逐字符比较两字符串的大小
substring(string,position [,length])	从string的position开始,取length个字符,如果不带length默认提取到字符串结尾
ltrim(string) rtrim(string) trim(string)	去除前空格或后空格

案例:

• 获取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 charset(deptno) from emp;
+-----------------+
| charset(deptno) |
+-----------------+
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
| binary          |
+-----------------+
14 rows in set (0.00 sec)

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

mysql> select concat(name,'的语文成绩是:',chinese,'分,数学成绩是:',math,'分,英语成绩是:',english,'分') 分数 from exam_reesult;
+-------------------------------------------------------------------------------+
| 分数                                                                          |
+-------------------------------------------------------------------------------+
| 赖益烨的语文成绩是: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.01 sec)

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

mysql> select name,length(name) from exam_result;
+-----------+--------------+
| name      | length(name) |
+-----------+--------------+
| 赖益烨    |            9 |
| 郭雨妍    |            9 |
| 马师傅    |            9 |
| 洋妈      |            6 |
| 李观洋    |            9 |
| 郭昊      |            6 |
| 张照洋    |            9 |
+-----------+--------------+
7 rows in set (0.00 sec)

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

注意:length函数返回字符串长度,以字节为单位.如果是多字节字符则计算多个字节数;如果是单字节字符则算作一个字节.比如:字母,数字算作一个字节,中文表示多个字节数(与字符集编码有关).

• 将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.01 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)) 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)

三、数学函数

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

• 绝对值

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

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

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

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

• 向上取整

mysql> select ceiling(8.1);
+--------------+
| ceiling(8.1) |
+--------------+
|            9 |
+--------------+
1 row in set (0.00 sec)

mysql> select ceiling(8.9);
+--------------+
| ceiling(8.9) |
+--------------+
|            9 |
+--------------+
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(1.9);
+------------+
| floor(1.9) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(1.1);
+------------+
| floor(1.1) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

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

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

• 保留2位小数位数(小数四舍五入)

mysql> select format(12.236,2);
+------------------+
| format(12.236,2) |
+------------------+
| 12.24            |
+------------------+
1 row in set (0.00 sec)

mysql> select format(12.231,2);
+------------------+
| format(12.231,2) |
+------------------+
| 12.23            |
+------------------+
1 row in set (0.00 sec)

mysql> select format(-12.231,2);
+-------------------+
| format(-12.231,2) |
+-------------------+
| -12.23            |
+-------------------+
1 row in set (0.00 sec)

mysql> select format(-12.236,2);
+-------------------+
| format(-12.236,2) |
+-------------------+
| -12.24            |
+-------------------+
1 row in set (0.00 sec)

• 产生随机数

mysql> select format(rand(),2);
+------------------+
| format(rand(),2) |
+------------------+
| 0.61             |
+------------------+
1 row in set (0.00 sec)

mysql> select format(rand()*100,0);
+----------------------+
| format(rand()*100,0) |
+----------------------+
| 83                   |
+----------------------+
1 row in set (0.00 sec)

四、其它函数

• user() 查询当前用户

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

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

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

mysql> select md5('hulu');
+----------------------------------+
| md5('hulu')                      |
+----------------------------------+
| a016e7e3817eec54180fba6a64ae6616 |
+----------------------------------+
1 row in set (0.00 sec)

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

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

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

mysql> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

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

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

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

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

---

总结

(本章完!)