mysql基本练习（二）

水深无声 2023-06-25 06:28 58阅读 0赞

①查询所有班级的全部信息
    select classNo,className,classNum,grade,institute
    from class
    ② 查询1999年出生的“女”同学基本信息。
    select studentno,studentname,birthday
    from(select * from student where sex='女') as a where year(birthday)=1999 
    把女生给查询出来然后用一个变量a 来承载这个条件，可以建成一张表，然后接着从这张表中找出满足1999年的人
    ③查询平均分在80分以上的每个同学的选课门数、平均分和最高分，且学号是'1500001','1500002','1500003' 和 平均分数是 70 - 80
    select studentno,count(*)门数,avg(score)平均分,max(score)最高分
    from score
    group by studentNo
    having studentno in('1500001','1500002','1500003') and avg(score) between 70 and 80
    ④呵呵哒课堂上的作业
    思路：首先查询出'001','002','003'的表为c，接着在定义的a,b,c中查找出满足大于80到90的学生
    select a.studentno,studentname,b.courseno,b.score
    from student a,score b,(select * from course  where courseno in ('001','002','003'))c
    where b.score between 80 and 90 and a.studentNo=b.studentNo and c.courseNo=b.courseNo
    ⑤呵呵哒课堂上的作业
    select a.studentno,studentname,b.courseno,b.score,c.coursename
    from student a,score b,course c
    where b.score between 80 and 90 and c.courseno in ('001','002','003') and a.studentNo=b.studentNo and c.courseNo=b.courseNo
    ⑥为表示同时选修“001”和“002”课程的选择条件，基于成绩表Score构造一个查询表c，查找出选修了编号为“002”课程的所有同学
    select a.studentno,studentname,b.courseno,b.score,c.courseno,c.score
    from student a,score b,(select * from score where courseno='002')c
    where b.courseno='001'
    and a.studentno=b.studentno
    and a.studentno=c.studentno
    ⑦要求按学号排序输出，需要排序语句ORDER BY
    本查询语句为：
    select a.studentno,studentname,b.courseno,b.score,c.courseno,c.score
    from student a,score b,(select * from score where courseno='002')c
    where b.courseno='001'
    and a.studentno=b.studentNo
    and a.studentno=c.studentno
    order by a.studentno
    ⑧要求按学号排序输出，需要排序语句ORDER BY
    本查询语句为：
    （1）
    SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
    FROM Student a, Score b, Score c
    WHERE a.studentNo=b.studentNo      
    AND a.studentNo=c.studentNo     
    AND b.courseNo='001'      
    AND c.courseNo='002'          
    ORDER BY a.studentNo
    （2）
    SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
    FROM Student a, Score b, 
                 (SELECT * FROM Score WHERE courseNo='002') c
    WHERE b.courseNo='001' 
          AND a.studentNo=b.studentNo    
          AND a.studentNo=c.studentNo    
    ORDER BY a.studentNo
    ⑧
    tudentname,b.courseno,b.score,c.courseno,c.score
    from student a,score b,score c
    where b.courseno='001' and c.courseno='002' and c.score between 70 and 80
    and a.studentno=b.studentno and b.studentno=c.studentNo
    order by a.studentno
    ⑨ 在学生表Student中查找与“李宏冰”同学在同一个班的同学姓名、班级编号和出生日期。
    （1）
    select a.studentname,a.classno,a.birthday
    from student a,student b
    where b.studentname='李宏冰' and a.classno=b.classno
    （2）
    select a.studentname,a.classno,a.birthday
    from student a,(select * from student where studentname='李宏冰')b
    where a.classno=b.classno
    ⑩查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。
    select classname,institute,studentno,studentname
    from class a,student b
    where a.classno=b.classno and grade =2015
    order by classname
    ⑾
    select classname,institute,studentno,studentname
    from class a left join student b
    on a.classno=b.classno
    where grade=2015
    order by classname,studentno
    (12)
    
       SELECT className, institute, studentNo, studentName
       FROM Class a left OUTER JOIN Student b 
                     ON a.classNo=b.classNo
       WHERE grade=2015
       ORDER BY className, studentNo
       (13)
       
       SELECT className, institute, studentNo, studentName
       FROM Class a right OUTER JOIN Student b 
                     ON a.classNo=b.classNo
       WHERE grade=2015
       ORDER BY className, studentNo
       (14)
       
       
       SELECT className, institute, studentNo, studentName
       FROM Class a full OUTER JOIN Student b 
                     ON a.classNo=b.classNo
       WHERE grade=2015
       ORDER BY className, studentNo
    3.41使用IN的子查询
    本例子的查询的含义是：在学生表student中，将学号出现在成绩表Score中（表明该学生选修过课程）的学生姓名查询出来
    select studentname
    from student
    where student.studentno in(select score.studentno from score)