mysql8学习笔记⑤数据库的聚合函数

-- 统计课程表的总课程数

 

select count(*)

from imc_course;

 

统计讲师个数

select count(course_id)

                                ,count(distinct user_id)

from imc_course;

 

-- 统计出所有不同难度课程的学习人数

select level_name,sum(study_cnt)

from imc_course a

join imc_level b on a.level_id = b.level_id

group by level_name;

-- 统计出每门课程的平均学习人数

 

select sum(study_cnt)/count(study_cnt),avg(study_cnt)

from imc_course;

 

-- 统计出每门课程的平均学习人数

 

 select sum(study_cnt)/count(study_cnt),avg(study_cnt)

 from imc_course;

 

 select class_name,avg(study_cnt)

 from imc_course a

 join imc_class b on a.class_id = b.class_id

 group by class_name;

-- 利用课程评价表中的评分，更新课程表中课程的评分

 

select course_id

                ,avg(content_score) as avg_content

                ,avg(level_score) as avg_level

                ,avg(logic_score) as avg_logic

                ,avg(score) as avg_score

from imc_classvalue

group by course_id;

 

-- 查询出学习人数最多的课程

 

select title,study_cnt

from imc_course

where study_cnt = (select max(study_cnt) from imc_course)

-- 查询出每门课程的学习人数并按学习人数从高到低排列

select title,study_cnt

from imc_course

group by title

order by study_cnt desc

 

 

-- 分页返回课程ID和课程名称，每页返回10行记录

 

select course_id,title

from imc_course

order by course_id asc

limit 20,10

 

 

-- 定义一个包含课程ID，课程名称，课程分类，课程方向以及课程难度的视图

 

create view vm_course

as

select a.course_id,a.title,b.class_name,c.type_name,d.level_name

from imc_course a

join imc_class b on b.class_id = a.class_id

join imc_type c on c.type_id = a.type_id

join imc_level d on d.level_id = a.level_id

-- 查询出学习人数最多的课程

 

select title,study_cnt

from imc_course where study_cnt = (select max(study_cnt) from imc_course);