MySQL查询综合练习

学生表信息

1. 查询选修了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';

2. 查询 张三 同学选修的课程

//法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 = '张三';

3. 查询了只选修了一门课程的学生学号和姓名

select id,name from stu where id in(
  select stu_id from stu_kecheng group by stu_id haveing count(*) = 1
);

4. 查询了只选修了3门课程的学生学号和姓名

select id,name from stu where id in(
  select stu_id from stu_kecheng group by stu_id haveing count(*) >= 3
);

5. 查询选修了所有课程的学生信息

select * from stu where id in(
  select stu_id from stu_kecheng group by stu_id having count(*) = (
   select count(*) as c from kecheng
)
);

6. 查询选修课程的学生人数

select count(*) from (
select count (*) from stu_kecheng group by stu_id
) as t1;

7. 查询所学课程至少有一门跟张三所学课程相同的学生信息

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 = '张三'
)
)
);

8. 查询两门及两门以上不及格同学的平均分

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;