🎇一、聚合函数
提供 student 表
1️⃣count 函数
count 表示返回行的总数
（1）基本语法
SELECT COUNT(*|列名) FROM 表名
WHERE 条件;
（2）基本练习
统计一个班级共有多少学生？
SELECT COUNT(*) FROM student;
统计数学成绩大于 90 的学生有多少个？
SELECT COUNT(*) FROM student
WHERE math > 90;
统计总分大于 250 的人数有多少？
SELECT COUNT(*) FROM student
WHERE (math + english + chinese) > 250
（3）注意细节
count(*) 和 count(列) 的区别：count(*) 返回满足条件的记录的行数count(列): 统计满足条件的某列有多少个，但是会排除为 null 的情况
2️⃣sum 函数
sum函数返回满足where条件的行的和，一般使用于数值列
（1）基本语法
SELECT SUM(列名) FROM 表名
WHERE 条件;
（2）基本练习
统计一个班级数学总成绩
SELECT SUM(math) FROM student;
统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math),SUM(english),SUM(chinese) FROM student;
统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student;
统计一个班级语文成绩平均分
SELECT SUM(chinese)/ COUNT(*) FROM student;
（3）注意细节
sum 函数仅对数值起作用，对多行求和需用逗号隔开
3️⃣avg 函数
avg函数返回满足where条件的一列的平均值
（1）基本语法
SELECT AVG(列名) FROM 表名
WHERE 条件;
（2）基本练习
求一个班级数学平均分？
SELECT AVG(math) FROM student;
求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;
4️⃣max/min 函数
max/min 函数返回满足 where 条件的一列的最大/最小值
（1）基本语法
SELECT MAX(列名) FROM 表名
WHERE 条件;
SELECT MIN(列名) FROM 表名
WHERE 条件;
（2）基本练习
求班级最高分和最低分
SELECT MAX(math + english + chinese), MIN(math + english + chinese) FROM student;
求出班级数学最高分和最低分
SELECT MAX(math), MIN(math) FROM student;
🎆二、字符串相关函数
1️⃣字符串常用函数一览表
| 函数用法 | 说明 |
|---|---|
| CHARSET(str) | 返回字串字符集 |
| CONCAT (string2 [,… ]) | 连接字串, 将多个列拼接成一列 |
| INSTR (string ,substring ) | 返回 substring 在 string 中出现的位置,没有返回 0 |
| UCASE (string2) | 转换成大写 |
| LCASE (string2) | 转换成小写 |
| LEFT (string2 ,length) | 从 string2 中的左边起取 length 个字符 |
| RIGHT (string2 ,length) | 从 string2 中的右边起取 length 个字符 |
| LENGTH (string) | string 长度[按照字节] |
| REPLACE (str ,search_str ,replace_str ) | 在 str 中用 replace_str 替换 search_str |
| STRCMP (string1 ,string2) | 逐字符比较两字串大小 |
| SUBSTRING (str , position [,length ]) | 从 str 的 position 开始【从 1 开始计算】,取 length 个字符 |
| LTRIM (string) | 去除前端空格 |
| RTRIM (string) | 去除后端空格 |
| TRIM(string) | 去除前后两端空格 |
2️⃣案例演示
以 emp 表为例
文章来源：https://www.toymoban.com/news/detail-803196.html
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, '工作是', job) FROM emp;
-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('jack','c') FROM DUAL;
-- UCASE (string2) 转换成大写
SELECT UCASE(ename) FROM emp;
-- LCASE (string2) 转换成小写
SELECT LCASE(ename) FROM emp;
-- LEFT (string2 ,length)从 string2 中的左边起取 length 个字符
SELECT LEFT(ename,2) FROM emp;
-- RIGHT (string2 ,length) 从 string2 中的右边起取 length 个字符
SELECT RIGHT(ename,2) FROM emp;
-- LENGTH (string)string 长度[按照字节]
SELECT LENGTH(ename) FROM emp;
SELECT LENGTH('邱崇源') FROM emp;-- 9个字节
-- REPLACE (str ,search_str ,replace_str )在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job, 'MANAGER', '经理') FROM emp;
-- STRCMP (string1 ,string2) 逐字符比较两字串大小
SELECT STRCMP('abc','bbc') FROM emp;
-- SUBSTRING (str , position [,length ])
-- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符
-- 从 ename 列的第一个位置开始取出 2 个字符
SELECT SUBSTRING(ename,1,2) FROM emp;
-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM(' 邱崇源') FROM DUAL;
SELECT RTRIM('邱崇源 ') FROM DUAL;
SELECT TRIM(' 邱崇源 ') FROM DUAL;
✨三、数学相关函数
1️⃣数学常用函数一览表
| 函数用法 | 说明 |
|---|---|
| ABS(num) | 绝对值 |
| BIN (decimal_number) | 十进制转二进制 |
| CEILING (number2) | 向上取整, 得到比 num2 大的最小整数 |
| CONV(number2,from_base,to_base) | 进制转换 |
| FLOOR (number2) | 向下取整,得到比 num2 小的最大整数 |
| FORMAT (number,decimal_places) | 保留小数位数(四舍五入) |
| HEX (DecimalNumber) | 转十六进制 |
| LEAST (number , number2 [,…]) | 求最小值 |
| MOD (numerator ,denominator) | 求余 |
| RAND([seed]) | 返回随机数 其范围为 0 ≤ v ≤ 1.0 |
2️⃣案例演示
-- 演示数学相关函数
-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL;
-- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL;
-- CEILING (number2 ) 向上取整, 得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;
-- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的 8, 转成 2 进制输出
SELECT CONV(8, 10, 2) FROM DUAL;
-- 下面的含义是 8 是 16 进制的 8, 转成 2 进制输出
SELECT CONV(16, 16, 10) FROM DUAL;
-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(1.1) FROM DUAL;
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL;
-- HEX (DecimalNumber ) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL;
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL;
-- RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 说明
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
🎄四、时间日期相关函数
1️⃣时间日期常用函数一览表
| 函数用法 | 说明 |
|---|---|
| CURRENT_DATE ( ) | 当前日期 |
| CURRENT_TIME ( ) | 当前时间 |
| CURRENT_TIMESTAMP ( ) | 当前时间戳 |
| DATE_ADD(send_time, INTERVAL d_value d_type) | 在date2中加上一个日期或时间 |
| DATE_SUB(send_time, INTERVAL d_value d_type) | 在date2中减去一个日期或时间 |
| DATEDIFF(date1,date2) | 两个日期差（结果是天） |
| TIMEDIFF(date1,date2) | 两个时间差（结果是多少小时多少分钟多少秒） |
| NOW() | 当前时间 |
| YEAR/Month/DAY (datetime) | 取时间的年月日 |
| unix_timestamp() | 返回的是 1970-1-1 到现在的秒数 |
| FROM_UNIXTIME() | 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期 |
2️⃣案例演示
建立mes表文章来源地址https://www.toymoban.com/news/detail-803196.html
CREATE TABLE mes (
id INT,
content VARCHAR(30),
send_time DATETIME);
INSERT INTO mes VALUES(1,'界面打开',CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2,'鸡腿肉',NOW());
INSERT INTO mes VALUES(3,'圣诞狗狗',NOW());
-- 日期时间相关函数
-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME ( )当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.
SELECT id,content,DATE(send_time) FROM mes;
-- 请查询在 10 分钟内发布的新闻, 思路一定要梳理一下.
SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL;
-- 请用 mysql 的 sql 语句求出你活了多少天?
SELECT DATEDIFF(NOW(),'2000-05-24') FROM DUAL;
-- 如果你能活 80 岁，求出你还能活多少天.
SELECT DATEDIFF('2080-05-24',NOW()) FROM DUAL;
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;
-- YEAR|Month|DAY| (datetime)
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
-- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换
SELECT FROM_UNIXTIME(1672307500, '%Y-%m-%d %H:%i:%s');
🍱五、加密和系统函数
1️⃣加密和系统函数一览表
| 函数用法 | 说明 |
|---|---|
| USER() | 查询用户 |
| DATABASE() | 查询当前使用数据库名称 |
| MD5(str) | 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密 |
| PASSWORD(str) | 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密 |
2️⃣案例演示
-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP
SELECT USER() FROM DUAL; -- 用户@IP 地址
-- DATABASE()查询当前使用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密
-- root 密码是 qcy -> 加密 md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('qcy') FROM DUAL;
SELECT LENGTH(MD5('qcy')); -- 32
-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密
SELECT PASSWORD('qcy') FROM DUAL;
🎋六、流程控制函数
1️⃣流程控制函数一览表
| 函数用法 | 说明 |
|---|---|
| IF(expr1,expr2,expr3) | 如果 expr1 为 True ,则返回 expr2 否则返回 expr3 |
| IFNULL(expr1,expr2) | 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2 |
| SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.] | 如果 expr1 为 TRUE,则返回 expr2,如果 expr3 为 TRUE, 返回 expr4, 否则返回 expr5 |
2️⃣案例演示
# 演示流程控制语句
# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2
SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果 expr1 为 TRUE,则返回 expr2,如果 expr3 为 TRUE, 返回 expr4, 否则返回 expr5
SELECT CASE
WHEN TRUE THEN 'jack' -- jack
WHEN FALSE THEN 'tom' ELSE 'mary' END
-- 1. 查询 emp 表, 如果 comm 是 null , 则显示 0.0
-- 说明，判断是否为 null 要使用 is null, 判断不为空 使用 is not null
SELECT ename,IFNULL(comm,0.0) FROM emp;
-- 2. 如果 emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS 'job' FROM emp;
到了这里，关于零基础学MySQL（五）-- 详细讲解数据库中的常用函数的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
