＜7＞-MySQL内置函数
目录
一，日期函数
二，字符串函数
三，数学函数
四，其他函数
一，日期函数
用法：
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2025-05-28 |
+----------------+
1 row in set (0.00 sec)mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 10:38:44 |
+----------------+
1 row in set (0.00 sec)mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2025-05-28 10:39:01 |
+---------------------+
1 row in set (0.00 sec)mysql> select date_add('2017-10-28', interval 10 day);
+-----------------------------------------+
| date_add('2017-10-28', interval 10 day) |
+-----------------------------------------+
| 2017-11-07 |
+-----------------------------------------+
1 row in set (0.01 sec)mysql> select date_sub('2017-10-1', interval 10 day);
+----------------------------------------+
| date_sub('2017-10-1', interval 10 day) |
+----------------------------------------+
| 2017-09-21 |
+----------------------------------------+
1 row in set (0.00 sec)mysql> select datediff('2017-10-1', '2019-12-13');
+-------------------------------------+
| datediff('2017-10-1', '2019-12-13') |
+-------------------------------------+
| -803 |
+-------------------------------------+
1 row in set (0.00 sec)案例：
mysql> create table tmp(-> id int primary key auto_increment,-> birthday date-> );
Query OK, 0 rows affected (0.01 sec)mysql> insert into tmp(birthday) values(current_date());
Query OK, 1 row affected (0.01 sec)mysql> select * from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 2025-05-28 |
+----+------------+
1 row in set (0.00 sec)mysql> create table msg (-> id int primary key auto_increment,-> content varchar(30) not null,-> sendtime datetime-> );
Query OK, 0 rows affected (0.00 sec)mysql> insert into msg(content,sendtime) values('hello1', now());
Query OK, 1 row affected (0.00 sec)mysql> insert into msg(content,sendtime) values('hello2', now());
Query OK, 1 row affected (0.00 sec)mysql> select * from msg;
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello1 | 2025-05-28 11:02:16 |
| 2 | hello2 | 2025-05-28 11:02:21 |
+----+---------+---------------------+
2 rows in set (0.00 sec)mysql> select content,date(sendtime) from msg;
+---------+----------------+
| content | date(sendtime) |
+---------+----------------+
| hello1 | 2025-05-28 |
| hello2 | 2025-05-28 |
+---------+----------------+
2 rows in set (0.00 sec)mysql> select * from msg where date_add(sendtime, interval 2 minute) > now();
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello1 | 2025-05-28 11:02:16 |
| 2 | hello2 | 2025-05-28 11:02:21 |
+----+---------+---------------------+
2 rows 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)--要求显示score表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
mysql> select concat(name,'的语文分数是',chinese,'分，数学分数是',math,'分') as '分数' from score;
+------------------------------------------------------+
| 分数 |
+------------------------------------------------------+
| 唐一的语文分数是134分，数学分数是98分 |
| 孙二的语文分数是174分，数学分数是78分 |
| 张三的语文分数是140分，数学分数是98分 |
| 李四的语文分数是130分，数学分数是105分 |
| 王五的语文分数是110分，数学分数是115分 |
| 田七的语文分数是150分，数学分数是95分 |
+------------------------------------------------------+
6 rows in set (0.00 sec)--求学生表中学生姓名占用的字节数
mysql> select length(name), name from score;
+--------------+--------+
| length(name) | name |
+--------------+--------+
| 6 | 唐一 |
| 6 | 孙二 |
| 6 | 张三 |
| 6 | 李四 |
| 6 | 王五 |
| 6 | 田七 |
+--------------+--------+
6 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(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
| 100.2 |
+-------------+
1 row in set (0.00 sec)--向上取整
mysql> select ceiling(23.04);
+----------------+
| ceiling(23.04) |
+----------------+
| 24 |
+----------------+
1 row in set (0.00 sec)--向下取整
mysql> select floor(23.7);
+-------------+
| floor(23.7) |
+-------------+
| 23 |
+-------------+
1 row in set (0.00 sec)--保留2位小数位数（小数四舍五入)
mysql> select format(12.3456,2);
+-------------------+
| format(12.3456,2) |
+-------------------+
| 12.35 |
+-------------------+
1 row in set (0.00 sec)--产生随机数
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.5332775512088812 |
+--------------------+
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('admin');
+----------------------------------+
| md5('admin') |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.00 sec)--database()显示当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| scott |
+------------+
1 row in set (0.00 sec)--password()函数，MySQL数据库使用该函数对用户加密
mysql> select password('root');
+-------------------------------------------+
| password('root') |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)--ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
mysql> select ifnull('abc','123');
+---------------------+
| ifnull('abc','123') |
+---------------------+
| abc |
+---------------------+
1 row in set (0.00 sec)mysql> select ifnull(null,'123');
+--------------------+
| ifnull(null,'123') |
+--------------------+
| 123 |
+--------------------+
1 row in set (0.00 sec)