Featured image of post 从零开始学MySQL(三)
数据库

从零开始学MySQL(三)

从零开始学MySQL,第三章 MySQL常用函数

第三章 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)，查询信管和大数据各有多少人

1

SELECT LEFT(class, 2), COUNT(*) FROM student GROUP BY LEFT(class, 2);

示例：查询名字有4个字的学生信息

1

SELECT * FROM student WHERE CHAR_LENGTH(name)=4;

示例：查询成绩能够被10整除的考试信息

1

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岁以上的学生信息

1

SELECT * FROM student WHERE TIMESTAMPDIFF(YEAR, birthday, NOW()) > 20;

示例：查询今天过生日的学生信息

1

SELECT * FROM student WHERE MONTH(birthday)=MONTH(NOW()) AND DATEOFMONTH(birthday)=DATEOFMONTH(NOW());

示例：查询本周过生日的学生信息

1
2

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

1

IF(条件, 表达式1, 表达式2)

如果条件满足，则使用表达式1，否则使用表达式2

示例：将学生成绩展示为及格和不及格

1

SELECT id, name, IF(score >= 60, '及格', '不及格') finalScore FROM scoreTable;

1.2 IFNULL

1

IFNULL(字段, 表达式)

如果字段值为空，则使用表达式，否则，使用字段值

示例：将未参加考试的学生成绩展示为缺考

1

SELECT id, name, IFNULL(score, '缺考') finalScore FROM scoreTable;

2. CASE … WHEN 语句

2.1 CASE WEHN

1

CASE WHEN 条件1 THEN 表达式1 [WHEN 条件2 THEN 表达式2 ...] ELSE 表达式n END

如果条件1满足，则使用表达式1；【如果条件2满足，则使用表达式2， … 】否则，使用表达式n。相当于Java中的多重if..else语句

示例：行转列，查看每人每科的分数

1
2
3
4
5
6
7
8
9

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

1

CASE 表达式 WHEN 值1 THEN 表达式1 [WHEN 值2 THEN 表达式2 ...] ELSE 表达式n END

如果表达式的执行结果为值1，则使用表达式1；【执行结果为值2，则使用表达式2， … 】否则，使用表达式n。相当于Java中的switch语句

示例：行转列，查看每人每科的分数

1
2
3
4
5
6
7
8
9

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;

练习：查询各班级人数，查询结果格式为 班级 男 女 其他

1
2
3
4
5
6
7

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. 数字格式化函数

1

FORMAT(X, D)

将数字X格式化，将X保留到小数点后D位，截断时要进行四舍五入

示例：

1

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;

第六节 综合练习

1. 求字符串'ABCdEfGHIJkLMn'的字符数

1

SELECT CHAR_LENGTH('ABCdEfGHIJkLMn');

2. 将'枫阿雨'和'带帅比'拼接成新的字符串

1

SELECT CONCAT('枫阿雨', '带帅比');

3. 求'2003-01-04'到现在总过有多少天

1

SELECT TIMESTAMPDIFF(DAY, '2003-01-04', NOW());

4. 如果字段score的值大于90，则展示为优秀，否则展示为良好

1

SELECT IF(score > 90, '优秀', '良好') score;
MySQL
Licensed under CC BY-NC-SA 4.0
© 2022 - 2023 枫阿雨's Blog
Built with Hugo
主题 Stack 由 Jimmy 设计