【MySQL】06.内置函数
1. 聚合函数
-- 统计表中的人数
-- 使用 * 做统计，不受 NULL 影响
mysql> select count(*) 人数 from exam_result;
+--------+
| 人数 |
+--------+
| 5 |
+--------+
1 row in set (0.01 sec)-- 使用表达式做统计
mysql> select count(name) 人数 from exam_result;
+--------+
| 人数 |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)-- 统计数学成绩总分
mysql> select sum(math) 数学总分 from exam_result;
+--------------+
| 数学总分 |
+--------------+
| 458 |
+--------------+
1 row in set (0.00 sec)-- 统计平均总分
mysql> select avg(math + chinese + english) 平均分 from exam_result;
+-----------+
| 平均分 |
+-----------+
| 320.8 |
+-----------+
1 row in set (0.00 sec)-- 返回最高总分
mysql> select max(math + chinese + english) 最高总分 from exam_result;
+--------------+
| 最高总分 |
+--------------+
| 364 |
+--------------+
1 row in set (0.00 sec)-- 返回 > 70 分以上的数学最低分
mysql> select min(math) 数学低分 from exam_result where math > 70;
+--------------+
| 数学低分 |
+--------------+
| 78 |
+--------------+
1 row in set (0.00 sec)-- having 结构
mysql> select math + chinese + english .总分 from exam_result having 总分 > 320;
+--------+
| 总分 |
+--------+
| 329 |
| 364 |
| 321 |
+--------+
3 rows in set (0.00 sec)
2. 日期函数
• 获得年月日
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2025-05-23 |
+----------------+
1 row in set (0.00 sec)
• 获得时分秒
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 20:09:47 |
+----------------+
1 row in set (0.00 sec)
• 获得时间戳
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2025-05-23 20:10:43 |
+---------------------+
1 row in set (0.00 sec)
• 在日期的基础上加上时间
mysql> select date_add(date(current_timestamp()),interval 20 day);
+-----------------------------------------------------+
| date_add(date(current_timestamp()),interval 20 day) |
+-----------------------------------------------------+
| 2025-06-12 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
• 在日期的基础上减去时间
mysql> select date_sub(date(current_timestamp()),interval 20 day);
+-----------------------------------------------------+
| date_sub(date(current_timestamp()),interval 20 day) |
+-----------------------------------------------------+
| 2025-05-03 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
• 计算两个日期之间相差多少天
mysql> select datediff(date(current_timestamp()),'2020-4-26');
+-------------------------------------------------+
| datediff(date(current_timestamp()),'2020-4-26') |
+-------------------------------------------------+
| 1853 |
+-------------------------------------------------+
1 row in set (0.00 sec)
• 当前时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2025-05-23 20:20:10 |
+---------------------+
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.03 sec)mysql> insert into msg(content,sendtime) values('hello',now()),('world',now());
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> insert into msg(content,sendtime) values('send',now()),('you',now());
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from msg;
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello | 2025-05-23 20:26:45 |
| 2 | world | 2025-05-23 20:26:45 |
| 3 | send | 2025-05-23 20:27:17 |
| 4 | you | 2025-05-23 20:27:17 |
+----+---------+---------------------+
4 rows in set (0.00 sec)mysql> select * from msg where date_add(sendtime,interval 3 minute) > now();
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 3 | send | 2025-05-23 20:27:17 |
| 4 | you | 2025-05-23 20:27:17 |
+----+---------+---------------------+
2 rows in set (0.00 sec)
3. 字符串函数
• 获取ms表的content列的字符集
mysql> select distinct charset(content) from msg;
+------------------+
| charset(content) |
+------------------+
| utf8mb4 |
+------------------+
1 row in set (0.00 sec)
• 字符串拼接
mysql> select concat(id,'发送的文本内容是：',content,' ,发送的时间是：',sendtime) 文本内容 from msg;
+-----------------------------------------------------------------------------+
| 文本内容 |
+-----------------------------------------------------------------------------+
| 1发送的文本内容是：hello ,发送的时间是：2025-05-23 20:26:45 |
| 2发送的文本内容是：world ,发送的时间是：2025-05-23 20:26:45 |
| 3发送的文本内容是：send ,发送的时间是：2025-05-23 20:27:17 |
| 4发送的文本内容是：you ,发送的时间是：2025-05-23 20:27:17 |
+-----------------------------------------------------------------------------+
4 rows in set (0.00 sec)
• 字串出现的位置
mysql> select instr('I am a happy day','hap');
+---------------------------------+
| instr('I am a happy day','hap') |
+---------------------------------+
| 8 |
+---------------------------------+
1 row in set (0.00 sec)
• 大小写转化
mysql> select ucase('I am a happy day');
+---------------------------+
| ucase('I am a happy day') |
+---------------------------+
| I AM A HAPPY DAY |
+---------------------------+
1 row in set (0.00 sec)mysql> select lcase('I AM A HAPPY DAY');
+---------------------------+
| lcase('I AM A HAPPY DAY') |
+---------------------------+
| i am a happy day |
+---------------------------+
1 row in set (0.00 sec)
• 截取子串
mysql> select left('I AM A HAPPY DAY',6);
+----------------------------+
| left('I AM A HAPPY DAY',6) |
+----------------------------+
| I AM A |
+----------------------------+
1 row in set (0.00 sec)
• 计算字符串字节数
mysql> select length('I AM A HAPPY DAY');
+----------------------------+
| length('I AM A HAPPY DAY') |
+----------------------------+
| 16 |
+----------------------------+
1 row in set (0.00 sec)
• 字符串替换
mysql> select replace('I AM A HAPPY DAY','HAPPY','999990');
+----------------------------------------------+
| replace('I AM A HAPPY DAY','HAPPY','999990') |
+----------------------------------------------+
| I AM A 999990 DAY |
+----------------------------------------------+
1 row in set (0.00 sec)
• 空格去除
mysql> select trim(' I AM A HAPPY DAY ');
+-----------------------------------+
| trim(' I AM A HAPPY DAY ') |
+-----------------------------------+
| I AM A HAPPY DAY |
+-----------------------------------+
1 row in set (0.00 sec)
4. 数学函数
• 绝对值
mysql> select abs(-10.89);
+-------------+
| abs(-10.89) |
+-------------+
| 10.89 |
+-------------+
1 row in set (0.00 sec)• 进制转化
mysql> select bin(19);
+---------+
| bin(19) |
+---------+
| 10011 |
+---------+
1 row in set (0.00 sec)mysql> select hex(19);
+---------+
| hex(19) |
+---------+
| 13 |
+---------+
1 row in set (0.00 sec)mysql> select conv(19,10,8);
+---------------+
| conv(19,10,8) |
+---------------+
| 23 |
+---------------+
1 row in set (0.00 sec)
• 取整
mysql> select ceiling(8.4);
+--------------+
| ceiling(8.4) |
+--------------+
| 9 |
+--------------+
1 row in set (0.00 sec)mysql> select floor(8.4);
+------------+
| floor(8.4) |
+------------+
| 8 |
+------------+
1 row in set (0.00 sec)
5. 其他函数
-- user() 查询当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
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() |
+------------+
| func |
+------------+
1 row in set (0.00 sec)-- password()函数，MySQL数据库使用该函数对用户加密
mysql> select password('root');
+-------------------------------------------+
| password('root') |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+-- ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
mysql> select ifnull(null,'000');
+--------------------+
| ifnull(null,'000') |
+--------------------+
| 000 |
+--------------------+
1 row in set (0.00 sec)mysql> select ifnull('hello','000');
+-----------------------+
| ifnull('hello','000') |
+-----------------------+
| hello |
+-----------------------+
1 row in set (0.00 sec)