SQL语句案例
?
14)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
分析:
平均 avg---GROUP BY分组
从高到低--ORDER BY
所有学生的所有课程的成绩---行转列
所有学生----外联(所有)----RIGHT JOIN右联SELECT
?? ?s.sid,
?? ?s.sname ,
?? ?不能写sc.score,用sum
?? ?sum()
?? ??? ??? ?当cid等于01是语 ?文
?? ??? ??? ?(case when sc.cid='01' then sc.score end)语文,
?? ??? ??? ?(case when sc.cid='02' then sc.score end)数学,
?? ??? ??? ?(case when sc.cid='03' then sc.score end)英语,
?? ??? ??? ?平均值,取两位
?? ??? ??? ?ROUND(avg(sc.score),2)?
FROM
?? ?t_mysql_score sc
?? ?RIGHT JOIN t_mysql_student s ON sc.sid = s.sid?
GROUP BY
?? ?s.sid,
?? ?s.sname
?方法一case when
SELECT
s.sid,
s.sname ,
sum((case when sc.cid='01' then sc.score end))语文,
sum( (case when sc.cid='02' then sc.score end))数学,
sum((case when sc.cid='03' then sc.score end))英语,
ROUND(avg(sc.score),2)
FROM
t_mysql_score sc
RIGHT JOIN t_mysql_student s ON sc.sid = s.sid
GROUP BY
s.sid,
s.sname
方法二、if?
?如果cid等于01,取分数,如果没有就取0(类似于三元运算符)
?? ?sum(if(sc.cid='01',sc.score,0))语文,
?
SELECT
s.sid,
s.sname ,
sum(if(sc.cid='01',sc.score,0))语文,
sum(if(sc.cid='02',sc.score,0))数学,
sum(if(sc.cid='03',sc.score,0))英语,
ROUND(avg(sc.score),2)
FROM
t_mysql_score sc
RIGHT JOIN t_mysql_student s ON sc.sid = s.sid
GROUP BY
s.sid,
s.sname
?
13)检索" 01 "课程分数小于 60,按分数降序排列的学生信息
分析:
SELECT s.sid,
s.*,
sc.score
from
t_mysql_student s,
t_mysql_score sc
where s.sid=sc.sid
" 01 "课程
and sc.cid='01'
分数小于 60
and sc.score < 60
按分数降序
ORDER BY sc.score
SELECT
s.sid,
s.*,
sc.score
FROM
t_mysql_student s,
t_mysql_score sc
WHERE
s.sid = sc.sid
AND sc.cid = '01'
AND sc.score < 60
ORDER BY
sc.score desc
?
12)查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
分析:
两/平均成绩----数量----用聚合函数---要分组
不及格---比较---用筛选
有没有可能没有学学科,所有不用统计出来---内连接
SELECT s.sid,
sc.score,
avg(sc.score)
from
联表
t_mysql_student s,
t_mysql_score sc
where s.sid=sc.sid
B、筛选后再分组
and sc.score<60
分组
GROUP BY s.sid,
sc.score,
A、分组后筛选
having sc.score<60先A还是选B
选A,上面不能写sc.score导致会重复,导致语法错误
常识性语法:
凡是没有在分组中出现的,上面就不能有分了组只会出现一行数据
?
SELECT s.sid,
s.sname,
avg(sc.score) n
from
t_mysql_student s,
t_mysql_score sc
where s.sid=sc.sid
and sc.score<60
GROUP BY s.sid,
s.sname
11)查询没学过"张三"老师讲授的任一门课程的学生姓名
分析:
没学过--->找到学过张三老师的--->子查询找张三老师教了那些课程 ?
select cid from?
t_mysql_course c,
t_mysql_teacher t
where c.tid=t.tid
and t.tname='张三'学生只要没学过就查出来 ??
select * from t_mysql_score sc,
t_mysql_student s
where s.sid=sc.sid?
and sc.cid not in()有重复要分组
GROUP BY s.sid ,s.sname
SELECT
s.sid,
s.sname
FROM
t_mysql_score sc,
t_mysql_student s
WHERE
s.sid = sc.sid
AND sc.cid NOT IN ( SELECT cid FROM t_mysql_course c, t_mysql_teacher t WHERE c.tid = t.tid AND t.tname = '张三' )
GROUP BY
s.sid,
s.sname
?
?
10)查询没有学全所有课程的同学的信息
分析:
没有学全---一共三门,有可能学了2门1门
学全了有多少门:统计一共有多少门学科
①统计一共有多少门
②统计每个学生学了多少门
③做比较
统计---》聚合函数---》分组一共有多少学科
select count(*) from t_mysql_course
统计每个学生学了多少门---不管有没有学都有(每个)---外联
SELECT s.sid,s.sname ,count(sc.score) n from t_mysql_student s?
left join
t_mysql_score sc
on s.sid=sc.sid
GROUP BY s.sid,s.sname
做比较
HAVING n<统计一共有多少门
SELECT
s.sid,
s.sname,
count( sc.score ) n
FROM
t_mysql_student s
LEFT JOIN t_mysql_score sc ON s.sid = sc.sid
GROUP BY
s.sid,
s.sname
HAVING
n < (
SELECT
count(*)
FROM
t_mysql_course)
1)查询学过「张三」老师授课的同学的信息
分析:
需要的表:
t_mysql_course
t_mysql_student
t_mysql_teacher
t_mysql_score
s.*哪个学生,c.cname哪门课程,t.tname那个老师,sc.score哪个成绩--出现重复SELECT s.*, c.cname,t.tname,sc.score from
t_mysql_course c,
t_mysql_student s,
t_mysql_teacher t,
t_mysql_score sc
where
绑定关系(看表---老师可以绑定课程|分数可以绑定学生和课程)
t.tid=c.tid
and c.cid=sc.cid
and ?sc.sid=s.sid
学过「张三」老师
and t.tname='张三'
??
SELECT
s.*,
c.cname,
t.tname,
sc.score
FROM
t_mysql_course c,
t_mysql_student s,
t_mysql_teacher t,
t_mysql_score sc
WHERE
t.tid = c.tid
AND c.cid = sc.cid
AND sc.sid = s.sid
AND t.tname = '张三'
15)查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列分析:
left join 不能管有没有考试都算--外联
第一步:统计课程了
SELECT
?? ??? ?c.cid,
?? ??? ?c.cname,
?? ??? ?第二步最高分,最低分,平均分
?? ??? ?max(sc.score) 最高分,
?? ??? ?min(sc.score) 最低分,
?? ?ROUND(avg(sc.score),2) 平均分 ,
?? ?第三步统计优秀人数
?? ?if(sc.score>90,1,0)
?? ?第四步:统计优秀率--优秀人÷总人
?? ?sum(if(sc.score>=90,1,0))/(SELECT count(1) from t_mysql_student)
?? ?要100 取两位形式,
?? ?ROUND(X*100,2)
?? ?加%形式
?? ?concat(X,'%')
?? ?FROM
?? ??? ?t_mysql_score sc
?? ??? ?LEFT JOIN t_mysql_course c ON sc.cid = c.cid?
?? ?GROUP BY
?? ??? ?c.cid,
?? ??? ?c.cname
SELECT
c.cid,
c.cname,
count(sc.sid) 人数,
max(sc.score) 最高分,
min(sc.score) 最低分,
ROUND(avg(sc.score),2) 平均分 ,
CONCAT(ROUND(sum(if(sc.score>=90,1,0))/(SELECT count(1)
from t_mysql_student)*100,2),'%') 优秀率,
CONCAT(ROUND(sum(if(sc.score>=80 and sc.score<90,1,0))/(SELECT count(1)
from t_mysql_student)*100,2),'%') 优良率,
CONCAT(ROUND(sum(if(sc.score>=70 and sc.score<80,1,0))/(SELECT count(1)
from t_mysql_student)*100,2),'%') 中等率,
CONCAT(ROUND(sum(if(sc.score>=60,1,0))/(SELECT count(1)
from t_mysql_student)*100,2),'%') 及格率
FROM
t_mysql_score sc
LEFT JOIN t_mysql_course c ON sc.cid = c.cid
GROUP BY
c.cid,
c.cname
?
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!