Mysql 函数参考和扩展：Mysql 常用函数和基础查询、 Mysql 官网
Mysql 语法执行顺序如下，一定要清楚！！！运算符相关，可前往 Mysql 基础语法和执行顺序扩展。
(8) select (9) distinct (11)<columns_name list> (1) from <left_table> (3) <join_type> join <right_table> (2) on <join_condition> (4) where <where_condition> (5) group by <group_by columns_name list> (6) with <rollup> (7) having <having_condition> (10) order by <order_by columns_name list> (12) limit <[offset，] rows> ;
1. 数据准备
这里有一张一年级一班的成绩得分表。
create table sql_test1.student_subject_scroe ( student_id varchar(255) comment '学生编号', subject varchar(255) comment '课程名称', score int comment '分数' ); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('01', 'english', 89); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('01', 'math', null); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('01', 'china', 97); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('02', 'english', 87); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('02', 'math', 53); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('02', 'china', 96); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('03', 'english', 87); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('03', 'math', 53); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('03', 'china', 96); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('04', 'english', 84); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('04', 'math', 52); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('04', 'china', 96); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('05', 'english', 74); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('05', 'math', 47); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('05', 'china', 92); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('06', 'english', 73); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('06', 'math', 40); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('06', 'china', 90); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('07', 'english', 73); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('07', 'math', 40); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('07', 'china', 90); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('08', 'english', 73); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('08', 'math', 40); insert into sql_test1.student_subject_scroe (student_id, subject, score) values ('08', 'china', 90);
2. 汇总查询
输入的是一组数据的集合，输出的是单个值。
常用的聚合函数如下：
- count([distinct] expr)：返回expr的记录数。
- sum(expr)：返回expr的汇总值。
- avg(expr)：返回expr的平均值。
- std(expr)：返回expr的标准差。
- max(expr)：返回expr的最大值。
- min(expr)：返回expr的最小值。
- group_concat([distinct] expr …)：返回一串字符串。
统计表数据总量、学生数、有效数据数量和考试科目。
# 统计一年级一班成绩得分表，总记录数、学生人数、有效得分记录数和考试科目 select count(*) total_records, count(distinct student_id) s_cnt, count(score) valid_cnt, group_concat(distinct subject order by subject separator '、') subjects from sql_test1.student_subject_scroe; +---------------+-------+-----------+------------------------+ | total_records | s_cnt | valid_cnt | subjects | +---------------+-------+-----------+------------------------+ | 24 | 8 | 23 | china、english、math | +---------------+-------+-----------+------------------------+
count(*)：返回表中数据总量；count(1)：与COUNT(*)效果相同，因为它只是在每一行中都返回一个非空的值；count(字段)：返回字段非空值的行数；
count(*)会统计值为 NULL 的行，而count(字段)不会统计此列为 NULL 值的行。
执行效率顺序：
count(*)=count(1)>count(字段)
只适用于数值类型的函数有：avg()、sum()、std()；
# 查看一年级一班语文平均分，avg = sum/count select avg(score) china_avg_score, sum(score) / count(distinct student_id) china_avg_score2, std(score) std_score from sql_test1.student_subject_scroe where subject = 'china'; +-----------------+------------------+--------------------+ | china_avg_score | china_avg_score2 | std_score | +-----------------+------------------+--------------------+ | 93.3750 | 93.3750 | 2.9553976043842236 | +-----------------+------------------+--------------------+
3. 分组查询
SELECT中出现的非汇总聚合的字段必须声明在GROUP BY中。
查看一年级一班各学科数据详情。
select subject, count(score) valid_cnt, avg(score) avg_score, sum(score) / count(score) avg_score2, std(score) std_score, min(score) min_score, max(score) max_score, group_concat(score order by score desc separator '、') score_str from sql_test1.student_subject_scroe group by subject; +---------+-----------+-----------+------------+--------------------+-----------+-----------+---------------------------------------+ | subject | valid_cnt | avg_score | avg_score2 | std_score | min_score | max_score | score_str | +---------+-----------+-----------+------------+--------------------+-----------+-----------+---------------------------------------+ | china | 8 | 93.3750 | 93.3750 | 2.9553976043842236 | 90 | 97 | 97、96、96、96、92、90、90、90 | | english | 8 | 80.0000 | 80.0000 | 6.8738635424337655 | 73 | 89 | 89、87、87、84、74、73、73、73 | | math | 7 | 46.4286 | 46.4286 | 5.876275371772324 | 40 | 53 | 53、53、52、47、40、40、40 | +---------+-----------+-----------+------------+--------------------+-----------+-----------+---------------------------------------+
查看一年级一班学科平均分低于60的学科数据详情
select subject, count(score) valid_cnt, avg(score) avg_score, sum(score) / count(score) avg_score2, std(score) std_score, min(score) min_score, max(score) max_score, group_concat(score order by score desc separator '、') score_str from sql_test1.student_subject_scroe where score is not null group by subject having avg(score) < 60; +---------+-----------+-----------+------------+-------------------+-----------+-----------+----------------------------------+ | subject | valid_cnt | avg_score | avg_score2 | std_score | min_score | max_score | score_str | +---------+-----------+-----------+------------+-------------------+-----------+-----------+----------------------------------+ | math | 7 | 46.4286 | 46.4286 | 5.876275371772324 | 40 | 53 | 53、53、52、47、40、40、40 | +---------+-----------+-----------+------------+-------------------+-----------+-----------+----------------------------------+
WHERE与HAVING的区别：
WHERE用于在执行查询之前对行进行筛选，而HAVING用于对查询结果进行分组后的筛选。WHERE可以应用于单个表或多个表的连接查询，而HAVING必须与GROUP BY一起使用。WHERE可以使用各种条件表达式进行筛选，而HAVING可以使用聚合函数和条件表达式对分组后的结果进行筛选。
