表结构
- 学生表student(id,name)
- 课程表course(id,name)
- 成绩表student_course(sid,cid,score)
先建表：
create table student(
id int not null auto_increment,
name char(10) not null,
primary key(id)
);
create table course(
id int not null auto_increment,
name char(10) not null,
primary key(id)
);
create table student_course(
sid int not null,
cid int not null,
score int not null,
foreign key (sid) references student(id),
foreign key (cid) references course(id)
);
|
再构造数据：
insert into student(name) values ('张三'),('李四');
insert into course(name) values('语文'),('数学');
insert into student_course values(1,1,80),(1,2,90),(2,1,90),(2,2,70);
|
问题
1、查询选了课程的人数
select count(distinct cid) from student_scourse;
|
2、查询每门课程被选修的学生数
select cid, count(sid) from student_course group by cid;
|
3、查询至少选修了两门课的学生的学号以及选课的数量
select sid, count(cid) as res
from student_course group by sid having res>=2;
|
4、查询去重后的学生的信息
select id,name from student group by id,name;
select distinct(id),name from student;
|
以上两种写法等价
5、查询student表中重名的学生，结果包含id和name，按name,id升序
select id,name from student where name in(
select name from student group by name having(count(name)>1)
)
order by name,id;
|
最重要的工作就是解决重名问题。按名字分组， 看哪一组行数大于1，就说明这一组代表的所有人，都是重名的。
6、在student_course表中查询平均分不及格的学生，列出学生id和平均分
select sid, avg(score)
from student_course group by sid having(avg(score)<60);
|
where后面不能跟聚集函数；having后面可以。
7、查询两门课程以上不及格的学生的学号及其平均成绩
select sid,avg(score)
from student_course where score<60 group by sid having count(cid)>2;
|
8、查询各科成绩最高和最低的分
select cid,max(score),min(score) from student_course group by cid;
|
9、查询平均成绩大于60分的学生的学号和平均成绩
select sid,avg(score) as res
from student_course group by sid having res>60;
|
10、在student_course表中查询每门课成绩都不低于80的学生id
select sid from student_course where sid not in (
select sid from student_course where score < 80 group by sid
);
|
反向思想，先找出有课程低于80分的学生，这些学生去重后，其补集就是目标要找的学生。
group by候面不能跟where，where后面能跟group by。
11、查询课程编号为0002的总成绩
select sum(score) from student_course where sid = 0002;
|
12、查询每个学生的总成绩，结果列出学生姓名和总成绩
select name, sum(score)
from student, student_course
where student.id=student_course.sid group by student.id;
|
如果使用上面的sql会过滤掉没有成绩的人。需要考虑有的学生缺考，没有成绩。更保险的做法应该是使用 左外连接。
select name,sum(score)
from student left join student_course
on student.id=student_course.sid
group by sid;
|
13、查询id为1的课程里，得分最高的学生的信息
select * from student where id=(
select sid from student_course where cid=1 order by score desc limit 0,1
);
|
14、查询id为1的课程里，得分第三高的学生的信息
select * from student where id=(
select sid from student_course where cid=1 group by score order by score desc limit 2,1
);
|
要group by分组来去重，因为要考虑到这么一种情况：想找第三大的时间，但有多个人并列在第二大。
我们要以分数大小为依据；如果只排序一下就取，那是以人数为依据的，第三个人不一定是第三大。
15、总成绩最高的学生，结果列出学生id和总成绩
select sid,sum(score) as res
from student_course group by sid order by res limit 0,1;
|
16、在student_course表查询各科成绩最高的学生，结果列出学生id、课程id和对应的成绩
select sid,cid,score from student_course group by cid order by score limit 0,1;
select sid,cid,max(score) from student_course group by cid;
|
上面两条语句作用相同，但都是不恰当的。因为 使用了group by的查询字段只能是group by中的字段或者聚集函数或者是每个分组内均相同的字段。 虽然不会报错，但是sid是无效的，如果去掉sid的话只能查出每门课程的最高分，不包含学生id。 本题的正确解法是使用相关嵌套查询：
select * from student_course as x where score>=
(select max(score) from student_course where cid=x.cid);
|
