MySQL内置函数

日期函数

获取年月日：

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

获得时分秒：

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 01:10:26       |
+----------------+
1 row in set (0.00 sec)

获取时间戳：

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-02-01 01:11:08 |
+---------------------+
1 row in set (0.00 sec)

在日期的基础上加日期：

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

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

mysql> select date_sub('2013-10-01', interval 10 day);
+-----------------------------------------+
| date_sub('2013-10-01', interval 10 day) |
+-----------------------------------------+
| 2013-09-21                              |
+-----------------------------------------+
1 row in set (0.00 sec)

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

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

案例：

创建一张表，记录生日：

mysql> create table birthday (-> id int unsigned primary key auto_increment,-> birthday date-> );
Query OK, 0 rows affected (0.02 sec)

添加当前日期：

mysql> insert into birthday (birthday) values (current_date());
Query OK, 1 row affected (0.00 sec)mysql> select * from birthday;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2023-02-01 |
+----+------------+
1 row in set (0.00 sec)

案例：
创建一个留言表：

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

插入数据：

mysql> insert into msg (content, sendtime) values ('hello world', now());
Query OK, 1 row affected (0.01 sec)mysql> insert into msg (content, sendtime) values ('hello mysql', now());
Query OK, 1 row affected (0.01 sec)mysql> select * from msg;
+----+-------------+---------------------+
| id | content     | sendtime            |
+----+-------------+---------------------+
|  1 | hello world | 2023-02-01 01:21:23 |
|  2 | hello mysql | 2023-02-01 01:21:30 |
+----+-------------+---------------------+
2 rows in set (0.00 sec)

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

mysql> select content, date(sendtime) from msg;
+-------------+----------------+
| content     | date(sendtime) |
+-------------+----------------+
| hello world | 2023-02-01     |
| hello mysql | 2023-02-01     |
+-------------+----------------+
2 rows in set (0.00 sec)

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

mysql> select * from msg;
+----+-------------+---------------------+
| id | content     | sendtime            |
+----+-------------+---------------------+
|  1 | hello world | 2023-02-01 01:21:23 |
|  2 | hello mysql | 2023-02-01 01:21:30 |
|  3 | hello Linux | 2023-02-01 01:24:34 |
+----+-------------+---------------------+
3 rows in set (0.00 sec)mysql> select content, sendtime from msg where date_add(sendtime, interval 2 minute) > now();
+-------------+---------------------+
| content     | sendtime            |
+-------------+---------------------+
| hello Linux | 2023-02-01 01:24:34 |
+-------------+---------------------+
1 row in set (0.00 sec)

---

字符串函数

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

mysql> select charset(ename) from emp limit 1;
+----------------+
| charset(ename) |
+----------------+
| utf8           |
+----------------+
1 row in set (0.00 sec)

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

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

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

mysql> select name, length(name) from students;
+----------+--------------+
| name     | length(name) |
+----------+--------------+
| Curry    |            5 |
| Durant   |            6 |
| Bryant   |            6 |
| Mitchell |            8 |
+----------+--------------+
4 rows in set (0.00 sec)

将EMP表中所有名字中有S的替换成’数据库’

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

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

mysql> select substring(ename, 2, 2), ename from emp;
+------------------------+--------+
| substring(ename, 2, 2) | ename  |
+------------------------+--------+
| MI                     | SMITH  |
| LL                     | ALLEN  |
| AR                     | WARD   |
| ON                     | JONES  |
| AR                     | MARTIN |
| LA                     | BLAKE  |
| LA                     | CLARK  |
| CO                     | SCOTT  |
| IN                     | KING   |
| UR                     | TURNER |
| DA                     | ADAMS  |
| AM                     | JAMES  |
| OR                     | FORD   |
| IL                     | MILLER |
+------------------------+--------+
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)

---

数学函数

绝对值：

mysql> select abs(-200);
+-----------+
| abs(-200) |
+-----------+
|       200 |
+-----------+
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.1);
+---------------+
| ceiling(-8.1) |
+---------------+
|            -8 |
+---------------+
1 row in set (0.00 sec)

向下取整：

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

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

mysql> select format(12.456, 1);
+-------------------+
| format(12.456, 1) |
+-------------------+
| 12.5              |
+-------------------+
1 row in set (0.00 sec)

十进制转二进制：

mysql> select bin(12);
+---------+
| bin(12) |
+---------+
| 1100    |
+---------+
1 row in set (0.00 sec)

转化成十六进制：

mysql> select hex(10);
+---------+
| hex(10) |
+---------+
| A       |
+---------+
1 row in set (0.00 sec)

随机数：

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

---

其他函数

user() 查询当前用户：

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

md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串，相当于对内容进行加密：

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

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

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

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

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

---