学生表信息
- 查询选修了MySQL的学生姓名
//法1:
select name from stu where id in (
select stu_id from stu_kecheng where kecheng_id=(
select id from kecheng where kecheng_name = 'Mysql'
)
);
//法2:
select stu.name from stu
inner join stu_kecheng as sk on sk.stu_id=stu.id
inner join kecheng as kc on kc.id = sk.kecheng_id
where kc.kecheng_name = 'Mysql';
- 查询 张三 同学选修的课程
//法1:
select kecheng_name from kecheng where id in (
select kecheng_id from stu_kecheng where stu_id = (
select id from stu where name = '张三'
)
);
//法2:
select kc.kecheng_name from stu
inner join stu_kecheng as sk on sk.stu_id=stu.id
inner join kecheng as kc on kc.id = sk.kecheng_id
where stu.name = '张三';
- 查询了只选修了一门课程的学生学号和姓名
select id,name from stu where id in(
select stu_id from stu_kecheng group by stu_id haveing count(*) = 1
);
- 查询了只选修了3门课程的学生学号和姓名
select id,name from stu where id in(
select stu_id from stu_kecheng group by stu_id haveing count(*) >= 3
);
- 查询选修了所有课程的学生信息
select * from stu where id in(
select stu_id from stu_kecheng group by stu_id having count(*) = (
select count(*) as c from kecheng
)
);
- 查询选修课程的学生人数
select count(*) from (
select count (*) from stu_kecheng group by stu_id
) as t1;
- 查询所学课程至少有一门跟张三所学课程相同的学生信息
select * from stu where id in (
//和张三选修课程相同的stu_id
select stu_id from stu_kecheng where kecheng_id in(
//张三的课程id
select kecheng_id from stu_kecheng where stu_id=(
select id from stu where name = '张三'
)
)
);
- 查询两门及两门以上不及格同学的平均分
select avg(score) from stu_kecheng where stu_id in(
//找出2门及以上不及格同学的id
select stu_id from stu_kecheng where score <= 60 group by stu_id having count(*)>=2
)
group by stu_id;
网友评论