Mysql之增删改查案例【进阶篇】

一、表结构要求： 

        -- 1.学生表-t_student  (sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别)

        -- 2.教师表-t_teacher  (tid 教师编号,tname 教师名称)

        -- 3.课程表-t_course   (cid 课程编号,cname 课程名称,tid 教师名称)

        -- 4.成绩表-t_score     (sid 学生编号,cid 课程编号,score 成绩)

二、题目：

01）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null）

分析：

        count group by        选课总数

        sum                         课程总成绩
        外连接：左外连接（left join），从表与null值关联

select s.sid,s.sname,count(sc.cid) cn,sum(sc.score) sm
from t_student s left join t_score sc
on s.sid=sc.sid
group by s.sid,s.sname

---

02）查询「李」姓老师的数量

分析：t_teacher
        #count group by 分组
        # like'李%'

select count(t.tname) from t_teacher t 
group by t.tname having t.tname like '李%'

---

03）查询学过「张三」老师授课的同学的信息

分析：四表连接查询 (教师表==》课程表 ==》成绩表 ==》学生表)
        #多表联查：永远视为两表之间的连接查询

select s.* from t_teacher t,t_course c,t_score sc,t_student s
where t.tid=c.tid and c.cid=sc.cid and sc.sid=s.sid and t.tname='张三';

---

04）查询没有学全所有课程的同学的信息

分析：having  筛选
        #t_course/t_student
        #1、先找到学全了所有课程的学生
   select sid from t_score group by sid having count(cid)=(select count(cid) from t_course)
        #2、再排除学全的学生
        #计算总的课程数量：select count(cid) from t_course;

select * from t_student where sid not in(
select sid from t_score
group by sid having count(cid)=(select count(cid) from t_course));

---

05）查询没学过"张三"老师讲授的任一门课程的学生姓名

分析：t_teacher/t_course/t_score/t_student

select * from t_student where sid not in(
select sc.sid from t_teacher t,t_course c,t_score sc 
where t.tid=c.cid and t.tname='张三' and c.cid=sc.cid);

---

06）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

分析：t_score/t_student
        #group by 分组 having 过滤

        #score<60        不及格

        #having count(cid)>1        两门及其以上

select s.sid,s.sname,round(avg(sc.score),2) sc
from t_score sc,t_student s
where sc.sid=s.sid and sc.score<60
group by s.sid,s.sname
having count(sc.cid)>1;

---

07）检索" 01 "课程分数小于 60，按分数降序排列的学生信息

分析：order by score desc
        #连表查询    t_score/t_student

        cid='01'           课程为01

        score<60        分数小于 60

        order by score desc        降序

select s.*,sc.score from t_student s left join t_score sc
on s.sid=sc.sid where sc.cid='01' and sc.score<60
order by sc.score desc;

---

08）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

分析：avg 
        #1、order by avg(score) desc        平均成绩降序排序
        #2、所有课程的成绩（行列转换：if 或者 case when）
        select * from t_course;

select sc.sid,round(avg(sc.score),2) as '平均成绩',
sum(if(sc.cid='01',sc.score,0)) as '语文',
sum(if(sc.cid='02',sc.score,0)) as '数学',
sum(if(sc.cid='03',sc.score,0)) as '英语'
from t_score sc group by sc.sid
order by avg(sc.score) desc;

---

09）查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

concat：连接函数 

#选修人数：select cid,count(sid) from t_score1 group by cid 

select c.cid,c.cname,
max(sc.score) as '最高分',
min(sc.score) as '最低分',
round(avg(sc.score),2) as '平均分',
concat(round(sum(if(sc.score>=60,1,0))/count(s.sid)*100,2),'%') as '及格率',
concat(round(sum(if(sc.score>=70 and sc.score<=80,1,0))/count(s.sid)*100,2),'%') as '中等率',
concat(round(sum(if(sc.score>=80 and sc.score<=90,1,0))/count(s.sid)*100,2),'%') as '优良率',
concat(round(sum(if(sc.score>=90,1,0))/count(s.sid)*100,2),'%') as '优秀率'
from t_score sc,t_course c,t_student s 
where sc.cid=c.cid and sc.sid=s.sid
group by c.cid,c.cname