第三章 MySQL
常用函数
第一节 常用数学函数
函数 | 说明 | 示例 |
---|---|---|
ABS(X) | 返回X的绝对值 | SELECT ABS(-8); |
FLOOR(X) | 返回不大于X的最大整数 | SELECT FLOOR(1.3); |
CEIL(X) | 返回不小于X的最小整数 | SELECT CEIL(1.3); |
TRUNCATE(X, D) | 返回值X保留到小数点后D位的值,截断时不进行四舍五入 | SELECT TRUNCATE(1.2328, 3); |
ROUND(X) | 返回离X最近的整数,截断时要进行四舍五入 | SELECT ROUND(1.8); |
ROUND(X, D) | 返回X小数点后D位的值,截断时要进行四舍五入 | SELECT ROUND(1.2323, 3); |
RAND() | 返回0-1的随机数 | SELECT RAND(); |
MOD(N, M) | 返回N除以M以后的余数 | SELECT MOD(2, 9); |
第二节 常用字符串函数
函数 | 说明 | 示例 |
---|---|---|
CHAR_LENGTH(str) | 计算字符串字符个数 | SELECT CHAR_LENGTH('枫阿雨'); --3 |
LENGTH(str) | 返回值位字符串str的长度,单位为字节 | SELECT LENGTH('枫阿雨'); --9 |
CONCAT(s1, s2, ...) | 将多个字符串拼接在一起,其中任意一个为NULL则返回值为NULL | SELECT CONCAT('枫', '阿', '雨'); --枫阿雨 |
LOWER(str) LCASE(str) |
将字符串中的字母全部转换成小写 | SELECT LOWER('JAVA'); SELECT LCASE('JAVA'); --java |
UPPER(str) UCASE(str) |
将字符串中的字母全部转换成大写 | SELECT UPPER('java'); SELECT LCASE('java'); --JAVA |
LEFT(s, n) | 返回字符串s从最左边开始的n个字符 | SELECT LEFT('枫阿雨带帅比', 3); --枫阿雨 |
RIGHT(s, N) | 返回字符串s从最右边开始的n个字符 | SELECT RIGHT('枫阿雨带帅比', 3); --带帅比 |
LTRIM(str) | 返回字符串s,其左边的所有空格被删除 | SELECT LTRIM('Java'); --Java |
RTRIM(str) | 返回字符串s,其右边的所有空格被删除 | SELECT RTRIM('Java '); --Java |
TRIM(str) | 返回字符串str删除了两边空格之后的字符串 | SELECT TRIM(' Java '); --Java |
REPLACE(s, s1, s2) | 返回一个字符串,用字符串s2替代字符串s中的所有字符串s1 | SELECT REPLACE('疯阿雨', '疯', '枫'); --枫阿雨 |
SUBSTRING(s, n, len) | 从字符串s中返回一个第n个字符开始 长度为len的字符串 | SELECT SUBSTRING('枫阿雨带帅比', 2, 2); --阿雨 |
示例:假设表中有信管和大数据四个班(class),查询信管和大数据各有多少人
SELECT LEFT(class, 2), COUNT(*) FROM student GROUP BY LEFT(class, 2);
示例:查询名字有4个字的学生信息
SELECT * FROM student WHERE CHAR_LENGTH(name)=4;
示例:查询成绩能够被10整除的考试信息
SELECT * FROM student WHERE MOD(score, 10)=0;
第三节 日期和时间函数
函数 | 说明 | 示例 |
---|---|---|
CURDATE() CURRENT_DATE() |
返回当前日期:YY-MM-dd |
SELECT CURDATE(); |
CURTIME() CURRENT_TIME() |
返回当前时间:HH:mm:ss |
SELECT CURTIME; |
NOW() CURRENT_TIMESTAMP() SYSDATE(); |
返回当前日期和时间:YY-MM-dd HH:mm:ss |
SELECT NOW(); |
YEAR(d) | 返回日期 d 中的年份值 | SELECT YEAR(NOW()); |
MONTH(d) | 返回日期 d 中的月份值,范围是1~12 | SELECT MONTH(NOW()); |
WEEKDAY(d) | 返回日期 d 是星期几 | SELECT WEEKDAY(NOW()); |
DAYOFMONTH(d) | 返回给定日期 d 是当月的第几天 | SELECT DAYOFMONTH(NOW()); |
HOUR(d) | 返回日期 d 的小时数 | SELECT HOUR(NOW()); |
MINUTE(d) | 返回日期 d 的分钟数 | SELECT MINUTE(NOW()); |
SECOND(d) | 返回日期 d 的秒数 | SELECT SECOND(NOW()); |
ADDDATE(d, n) | 返回起始日期 d 加上 n 天的日期 | SELECT ADDDATE(NOW(), 3); |
TIMESTAMPDIFF(INTERVAL expr type, d1, d2) | 返回给定日期 d1 和 d2 的时间差 | SELECT TIMESTAMPDIFF(YEAR, '2003-1-4', '2022-1-22'); |
DATE_FORMAT(d, f) | 返回给定日期格式的字符串 | SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); |
示例:查询年龄在20岁以上的学生信息
SELECT * FROM student WHERE TIMESTAMPDIFF(YEAR, birthday, NOW()) > 20;
示例:查询今天过生日的学生信息
SELECT * FROM student WHERE MONTH(birthday)=MONTH(NOW()) AND DATEOFMONTH(birthday)=DATEOFMONTH(NOW());
示例:查询本周过生日的学生信息
SELECT * FROM student WHERE RIGHT(birthday, 5) > RIGHT(DATE_FORMAT(ADDDATE(NOW(), -DAYOFWEEK(NOW())), '%Y-%m-%d'), 5) AND RIGHT(birthday, 5) <=
RIGHT(DATE_FORMAT(ADDDATE(NOW(),7-DAYOFMONTH(NOW())), '%Y-%m-%d'), 5);
第四节 条件判断函数
1. IF 函数
1.1 IF
IF(条件, 表达式1, 表达式2)
如果条件满足,则使用表达式1,否则使用表达式2
示例:将学生成绩展示为及格和不及格
SELECT id, name, IF(score >= 60, '及格', '不及格') finalScore FROM scoreTable;
1.2 IFNULL
IFNULL(字段, 表达式)
如果字段值为空,则使用表达式,否则,使用字段值
示例:将未参加考试的学生成绩展示为缺考
SELECT id, name, IFNULL(score, '缺考') finalScore FROM scoreTable;
2. CASE ... WHEN 语句
2.1 CASE WEHN
CASE WHEN 条件1 THEN 表达式1 [WHEN 条件2 THEN 表达式2 ...] ELSE 表达式n END
如果条件1满足,则使用表达式1;【如果条件2满足,则使用表达式2, ... 】否则,使用表达式n。相当于Java中的多重if..else语句
示例:行转列,查看每人每科的分数
SELECT
name,
course,
MAX(CASE WHEN (course = 'Java') THEN score ELSE 0 END) JavaScore,
MAX(CASE WHEN (course = 'Python') THEN score ELSE 0 END) PythonScore,
MAX(CASE WHEN (course = 'C/Cpp') THEN score ELSE 0 END) C/CppScore,
MAX(CASE WHEN (course = 'Golang') THEN score ELSE 0 END) GolangScore
FROM scoreTable
GROUP BY name;
2.2 CASE ... WHEN
CASE 表达式 WHEN 值1 THEN 表达式1 [WHEN 值2 THEN 表达式2 ...] ELSE 表达式n END
如果表达式的执行结果为值1,则使用表达式1;【执行结果为值2,则使用表达式2, ... 】否则,使用表达式n。相当于Java中的switch语句
示例:行转列,查看每人每科的分数
SELECT
name,
course,
MAX(CASE course WHEN 'Java' THEN score ELSE 0 END) JavaScore,
MAX(CASE course WHEN 'Python' THEN score ELSE 0 END) PythonScore,
MAX(CASE course WHEN 'C/Cpp' THEN score ELSE 0 END) C/CppScore,
MAX(CASE course WHEN 'Golang' THEN score ELSE 0 END) GolangScore
FROM scoreTable
GROUP BY name;
练习:查询各班级人数,查询结果格式为 班级 男 女 其他
SELECT
class,
SUM(CASE sex WHEN '男' THEN sex ELSE 0 END) '男',
SUM(CASE sex WHEN '女' THEN sex ELSE 0 END) '女',
SUM(CASE sex WHEN '其他' THEN sex ELSE 0 END) '其他'
FROM scoreTable
GROUP BY class;
第五节 其他函数
1. 数字格式化函数
FORMAT(X, D)
将数字X格式化,将X保留到小数点后D位,截断时要进行四舍五入
示例:
SELECT FORMAT(1.2353, 2);
2. 系统信息函数
函数 | 说明 | 示例 |
---|---|---|
VERSION() | 获取服务器的版本号 | SELECT VERSION(); |
CONNECTION_ID() | 获取服务器的连接数 | SELECT CONNECTION_ID(); |
DATEBASE() SCHEMA() |
获取当前数据库名 | SELECT DATEBASE(); SELECT SCHEMA(); |
USER() SYSTEM_USER() SESSION_USER() |
获取当前用户名 | SELECT USER(); SELECT SYSTEM_USER(); SELECT SESSION_USER(); |
CURRENT_USER() CURRENT_USER |
获取当前用户名 | SELECT CURRENT_USER; |