一、表结构要求:
-- 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)查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
分析(需要用到子查询):
1、t_score/t_student
2、先找出学过课程1和课程2的学生
3、课程01的成绩>课程02的成绩,是否是同一个人?(t1.sid=t2.sid限定 就是同一个人)t1:别名 (以 t1&t2 为临时数据源)
(select sid,score as s1 from t_score where cid='01') t1 ==》找出课程1的学生id和成绩
select s.*,temp.s1,temp.s2 from(
select t1.sid,t1.s1,t2.s2 from
(select sid,score as s1 from t_score where cid='01') t1,
(select sid,score as s2 from t_score where cid='02') t2
where t1.sid=t2.sid and t1.s1>t2.s2) temp,t_student1 s
where temp.sid=s.sid
02)查询同时存在" 01 "课程和" 02 "课程的情况
分析:
1.首先查找学过课程01和02的所有学生
select * from t_score where cid='01';t1.sid=t2.sid ==> 代表既学过课程1又学过课程2的学生
select * from
(select sid,score as s1 from t_score where cid='01') t1,
(select sid,score as s2 from t_score where cid='02') t2
where t1.sid=t2.sid;
03)查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null)
分析:1、连接查询(外连接)
2、首先查找学过课程01和02的所有学生
3、存在" 01 "课程但可能不存在" 02 "课程,故 t1 为主表
1)内连接:inner join 只有连接条件完全成立才会显示查询结果
2)外连接:左外连接(left join)和右外连接(right join)
特点:连接条件完全成立则显示结果;连接条件不成立,主表记录永远保存,从表与null值关联
select * from
(select sid,score from t_score where cid='01') t1 left join
(select sid,score from t_score where cid='02') t2 on t1.sid=t2.sid;
04)查询不存在" 01 "课程但存在" 02 "课程的情况
分析:
1、首先查找所有学过课程01的学生编号,在取相反值,not in2、再到成绩表中使用 not in 排除学过01课程得学生,最后拼接条件 cid='02'
select * from t_score where sid not in
(select sid from t_score where cid='01') and cid='02';
05)查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
分析:聚合函数
四舍五入 round(5,2)总共5位数,取2位小数
#1、t_score/t_student
#2、平均成绩avg>=60
#3、分组 group by || 过滤 having avg(score)>=60
select s.sid,s.sname,round(avg(sc.score),2)
from t_student s,t_score sc
where s.sid=sc.sid
group by s.sid,s.sname
having avg(sc.score)>=60;
06)查询在t_score表存在成绩的学生信息
分析:判断成绩表中是否存在有成绩的学生编号(内连接)
1、有可能一个学生参加了多场考试,故要去重
distinct:去重
select distinct s.* from t_score sc,t_student s where sc.sid=s.sid;