跳到主要内容 MySQL 内置函数详解:聚合、日期、字符串及数学运算 | 极客日志
SQL
MySQL 内置函数详解:聚合、日期、字符串及数学运算 本文详细介绍了 MySQL 中的常用内置函数,涵盖聚合函数(如 SUM、AVG、COUNT)、日期函数(如 NOW、DATE_ADD)、字符串函数(如 CONCAT、SUBSTRING)、数学函数(如 ABS、ROUND)以及其他系统函数。通过具体 SQL 示例展示了函数的用法、参数说明及注意事项,帮助开发者高效处理数据计算与格式转换。
MySQL 内置函数详解
MySQL 提供了丰富的内置函数,用于处理数据、执行计算和转换格式等操作。本文介绍 MySQL 中常用的一些函数。
一、聚合函数
函数名 作用 示例 结果 SUM(col) 求和 SUM(amount) 所有 amount 的总和 AVG(col) 平均值 AVG(age) 平均年龄 COUNT(col) 计数(忽略 NULL) COUNT(id) 行数 COUNT(*) 计数(包含 NULL) COUNT(*) 总行数 MAX(col) 最大值 MAX(score) 最高分数 MIN(col) 最小值 MIN(price) 最低价格
测试表结构
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号' ,
name VARCHAR (20 ) NOT NULL ,
qq VARCHAR (20 )
);
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR (20 ) NOT NULL COMMENT '同学姓名' ,
chinese FLOAT DEFAULT 0.0 COMMENT '语文成绩' ,
math FLOAT DEFAULT 0.0 COMMENT '数学成绩' ,
english FLOAT DEFAULT 0.0 COMMENT
);
微信扫一扫,关注极客日志 微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
相关免费在线工具 SQL 美化和格式化 在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
SQL 转 CSV/JSON/XML 解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL 转 CSV/JSON/XML在线工具,online
CSV 工具包 CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
Base64 字符串编码/解码 将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
Base64 文件转换器 将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
Markdown 转 HTML 将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML 转 Markdown 互为补充。 在线工具,Markdown 转 HTML在线工具,online
'英语成绩'
SELECT COUNT (* ) FROM students;
SELECT COUNT (qq) FROM students;
对比上表可以看到 COUNT 函数,对于 NULL 值,不做统计。
SELECT COUNT (math) FROM exam_result;
对比上表可以看到 COUNT 函数,对于重复值,不做统计。
SELECT COUNT (math) FROM exam_result WHERE math < 60 ;
SELECT AVG (chinese + math + english) AS 平均总分 FROM exam_result;
SELECT MAX (english) FROM exam_result;
SELECT MIN (math) FROM exam_result WHERE math > 70 ;
二、日期函数 SELECT DATE_ADD(CURRENT_DATE (), INTERVAL 10 DAY );
INTERVAL 后可以根据需要使用不同单位(年、月、日、分、秒)
SELECT DATE_SUB(CURRENT_DATE (), INTERVAL 10 DAY );
SELECT DATEDIFF(CURRENT_DATE (), '1949-10-01' );
CREATE TABLE msg (
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR (30 ) NOT NULL ,
sendtime DATETIME
);
INSERT INTO msg(content, sendtime) VALUES ('hello1' , NOW());
INSERT INTO msg(content, sendtime) VALUES ('hello2' , NOW());
SELECT * FROM msg;
显示所有留言信息,发布日期只显示日期,不用显示时间:
SELECT content, DATE (sendtime) FROM msg;
SELECT * FROM msg WHERE TIMESTAMPDIFF(MINUTE , sendtime, NOW()) <= 1 ;
三、字符串函数 SELECT CONCAT(name, '的语文分:' , chinese, ', 数学分:' , math, ', 英语分:' , english) FROM exam_result LIMIT 1 ;
SELECT INSTR('Hello World' , 'World' );
在 string 中查找字符串 substring 出现的位置,找到返回下标(从 1 开始),未找到返回 0。
SELECT LEFT ('Hello World' , 5 );
SELECT RIGHT ('Hello World' , 5 );
LENGTH() 函数在 MySQL 中计算的是字符串的字节长度,而不是字符个数,当前所使用的字符集汉字占三个字节。
SELECT REPLACE('Hello World' , 'World' , 'MySQL' );
在 substring 中查找 string,并将其替换为 str。若未找到则不做处理。
SELECT SUBSTRING ('Hello World' , 7 , 5 );
从字符串 string 的 pos 处开始,向后截取 len 个字符。
TRIM:去除字符串两端空格
LTRIM:去除字符串最左边的空格
RTRIM:去除字符串右边的空格
SELECT TRIM (' Hello ' ), LTRIM(' Hello' ), RTRIM('Hello ' );
在保存用户信息数据时,一般先对数据执行去除空格操作。由于网络传输过程可能引入不可见空字符,若直接存储含此类字符的数据,后续用户登录时,比如输入密码因存在空格匹配不上,会引发登录失败问题,且排查难度极大。所以,要先过滤掉字符串中的空格,再将处理后的数据存入数据库,以此规避因隐性空格导致的登录故障。
四、数学函数 可以看到在对小数进行二进制转换时,会将小数进行向下取整后再操作。
将数字 N,从 from_base 进制转换成 to_base 进制。
SELECT FORMAT(1234.567 , 2 );
将 N 保留 D 位小数,处理小数部分遵循四舍五入,若小数部分不够就补 0。
生成的数是从 0.0 ~ 1.0,若想要生成指定范围的我们就直接乘以 10^n 即可实现。
五、其他函数 获取当前连接到 MySQL 服务器的用户信息,返回结果的格式为 用户名@主机名。
在实际开发中,密码通常不会以明文形式直接存储在数据库中,而 MD5 哈希算法是常用的密码加密方案之一。其核心作用是将原始密码通过加密计算转换为一段固定长度(32 位)的哈希字符串,从而避免明文密码在存储或传输过程中泄露的风险。
这种加密方式缺点较多,实际生产中建议使用更安全的加密方案。
当 val1 为 NULL 时返回 val2,否则返回 val1 本身。
SELECT IFNULL(NULL , 'default' );