1.CASE WHEN 用法
对查询的结果进行条件筛选后赋值
基础语法:
CASE WHEN 条件1 THEN 赋值1
WHEN 条件2 THEN 赋值2
WHEN 条件3 THEN 赋值3
ELSE 赋值4 END
实际运用举例:
1.1 如计算一个班每个同学语文成绩的优良差,可以这么写
CASE WHEN 语文>=90 THEN 优
WHEN 语文>=60 THEN 良
ELSE差 END
1.2 要计算出上半个学期,每个同学第一次和最后一次语文成绩的差的和
SUM是对TEHN的值进行计算,case和sum结合还可以实现分段统计
SUM(CASE WHEN 时间在上半学期 THEN (最后-开始) ELSE NULL END)
1.3 计算出上半个学期,每个学生的缺考次数
COUNT是对受影响的行的计算,比SUM的运行速度快
COUNT(CASE WHEN 分数为空 TEHN 1 ELSE NULL END)
以上仅作为参考,实际操作都可以通过其他办法去实现
2.CONVERT用法
转为日期
SELECT CONVERT(NOW(), DATE);
- 转为日期加时间
SELECT CONVERT(NOW(), DATETIME); - 转为时间
SELECT CONVERT(NOW(), TIME); - 转为数字为字符串
SELECT CONVERT(150, CHAR); - SIGNED运算后四舍五入取值
SELECT CONVERT(5/10, SIGNED); 结果为1
SELECT CONVERT(6.4, SIGNED);结果为6
3.三种日期相减方法
SELECT TIMESTAMPDIFF(DAY, ‘2020-01-01’,CURDATE());
- 结束时间-开始时间
SELECT to_days(CURDATE()) - to_days(‘2020-01-01’);
SELECT DATEDIFF(CURDATE(),‘2020-01-01’)
4.参数化sql的值
目的是为了减少重复更改常用条件的值,需要一起执行下面的操作
set @a=‘’;
set @b=‘’;
SELECT * FROM 表 where 条件1=@a and 条件2=@b
5.对查询的结果值进行运算
求学生分数的平均值,最大值,最小值,总和,四舍五入,受影响的行数
SELECT AVG(分数),MAX(分数),MIN(分数),SUM(分数),ROUND(分数,2) ,COUNT(1) FROM 表
6.GROUP BY的用法
group by 的意思为分组汇总
求学生的分数的平均值
SELECT AVG(分数) FROM 成绩表 GROUP BY 学生id
注意:除运算的列,如果要查询其他字段,GROUP BY字段要跟着查询的字段
如:
SELECT 语文,英语,学号id FROM 成绩表 GROUP BY 语文,英语,学号id
进阶:
如果要查询一个学生一个学期所有语文的总和,并列出来,可以用GROUP_CONCAT,默认以,分割
SELECT SUM(语文),GROUP_CONCAT(语文) FROM 成绩表 GROUP BY 学号id
如果想把每个学生这个学期的语文从小到大排序展示,可以这么写
SELECT SUM(语文),GROUP_CONCAT(语文 order by 语文 SEPARATOR ‘+’) FROM 成绩表 GROUP BY 学号id
如果想知道期末学生的语文成绩和学号,可
SELECT GROUP_CONCAT(concat_ws(‘:’, 学号id,语文) ) FROM 成绩表 GROUP BY 学号id
7.is null 和 is not null,<> 用法
查询没有成绩的人数,字段不为空也不为空字符串,is not null不为空,<>不等于
SELECT COUNT(1) FROM 成绩表 WHERE SCORE IS NULL AND SCORE <> ’ ’
查询有成绩的人数
SELECT COUNT(1) FROM 成绩表 WHERE SCORE IS NOT NULL
8.IFNULL(),IF()
IFNULL(expression_1,expression_2),如果expression_1不为空就返回expression_1,否则expression_2
如,IFNULL(0,1)返回的是0,因为第一个不为空
IFNULL(NULL,1),返回的是1,因为第一个为空,就返回第二个值,目的是对查询的字段为空的做处理
IF(expr,0,1),如果expr表达式为真返回0,如果为假返回1