美文网首页程序员
MySQL查询综合练习

MySQL查询综合练习

作者: kangyiii | 来源:发表于2017-03-01 17:00 被阅读0次
    学生表信息
    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';
    
    1. 查询 张三 同学选修的课程
    //法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 = '张三';
    
    1. 查询了只选修了一门课程的学生学号和姓名
    select id,name from stu where id in(
      select stu_id from stu_kecheng group by stu_id haveing count(*) = 1
    );
    
    1. 查询了只选修了3门课程的学生学号和姓名
    select id,name from stu where id in(
      select stu_id from stu_kecheng group by stu_id haveing count(*) >= 3
    );
    
    1. 查询选修了所有课程的学生信息
    select * from stu where id in(
      select stu_id from stu_kecheng group by stu_id having count(*) = (
       select count(*) as c from kecheng
    )
    );
    
    1. 查询选修课程的学生人数
    select count(*) from (
    select count (*) from stu_kecheng group by stu_id
    ) as t1;
    
    1. 查询所学课程至少有一门跟张三所学课程相同的学生信息
    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 = '张三'
    )
    )
    );
    
    1. 查询两门及两门以上不及格同学的平均分
    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;
    

    相关文章

      网友评论

        本文标题:MySQL查询综合练习

        本文链接:https://www.haomeiwen.com/subject/krffgttx.html