Python-Basis-23th

周三，晴，记录生活分享点滴

参考博客1：https://www.cnblogs.com/wupeiqi/articles/5729934.html  题目

参考博客2：https://www.cnblogs.com/wupeiqi/articles/5748496.html  答案

测试题-下

按平均成绩从低到高 显示所有学生的“语文” “数学” “英语”的课程成绩

按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分

select sc.student_id,
    (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
    (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
    (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
    count(sc.course_id),
    avg(sc.num)
from score as sc
group by student_id desc

/*-------------------------------------------------------------------------------------------------*/

select 
    student_id,
    (select num from score as InnerTb where InnerTb.student_id=OuterTb.student_id and course_id=1) as yw,
    (select num from score as InnerTb where InnerTb.student_id=OuterTb.student_id and course_id=2) as wl,
    (select num from score as InnerTb where InnerTb.student_id=OuterTb.student_id and course_id=3) as ty,
    count(student_id),
    avg(num) as av
    
from score as OuterTb group by OuterTb.student_id order by av asc

查询各科成绩最高和最低的分

如以下形式显示：课程ID，最高分，最低分

select course_id, max(num) as max_num, min(num) as min_num from score group by course_id;

/*-------------------------------------------------------------------------------------------------*/

select course_id,max(num),min(num) from score group by course_id 

按各科平均成绩从低到高和及格率的百分数从高到低顺序

/* 思路：case when .. then */

select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;
    
/*-------------------------------------------------------------------------------------------------*/

select course_id, avg(num), count(course_id), sum(case when score.num > 60 then 1 else 0 end) / count(course_id) * 100 from score group by course_id

课程平均分从高到低显示（显示任课老师）

select avg(if(isnull(score.num),0,score.num)),teacher.tname from course
left join score on course.cid = score.course_id
left join teacher on course.teacher_id = teacher.tid
group by score.course_id
    
/*-------------------------------------------------------------------------------------------------*/

select avg(num),course_id from score group by course_id order by avg(num) desc
...

查询各科成绩前三名的记录(不考虑成绩并列情况)

select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join
(
    select sid,
    (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,
    (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 2,1) as second_num
    from score as s1
) as T
on score.sid =T.sid
where score.num <= T.first_num and score.num >= T.second_num

查询每门课程被选修的学生数

select course_id, count(1) from score group by course_id;

/*-------------------------------------------------------------------------------------------------*/

select course_id, count(student_id) from score group by course_id

查询出只选修了一门课程的全部学生的学号和姓名

select student.sid, student.sname, count(1) from score
left join student on score.student_id  = student.sid
group by course_id having count(1) = 1

/*-------------------------------------------------------------------------------------------------*/

select student_id, count(student_id) from score group by student_id having count(student_id) = 1

查询男生、女生的人数

select * from
(select count(1) as man from student where gender='男') as A ,
(select count(1) as feman from student where gender='女') as B

/*-------------------------------------------------------------------------------------------------*/

/* 横排列 */
select gender, count(gender) from student group by gender

/* 竖排列 select后面不加表 */ 
select 
(select count(1) from student where gender='男') as "男", 
(select count(1) from student where gender='女') as "女"

/* 竖排列 笛卡尔积运算 */ 
select * from
(select count(1) from student where gender='男') as "tb1", 
(select count(1) from student where gender='女') as "tb2"