MySQL的查询练习

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL的查询练习，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6026字，纯文字阅读大概需要9分钟。

内容图文

student表

 teacher表

 course表

 score表

 对这四个表进行一些练习。

1：查询student表中所有记录。

      select *from student;

2：查询student表中name/sex/classid这些列。

      select  name,sex,classid from student;

3：查询教师中不重复的单位department。

      select distinct department from teacher;

      distinct 列(一个或多个) 获取不重复的记录

4：查询score表中成绩再60至80之间的记录。

      select *from score where degree between 60 and 80;

5：查询score表中成绩为85，86，88的记录。

     select *from score where degree in (86,85,88);

6：查询student表中为95031班的或性别为女生的。

      select *from student where classid=95031 or sex='女';

7：以classid列降序查询student表的所有记录。

     select *from student order by desc classid ;

8：以degree降序courseid升序输出score表中所有记录。

    select *from score order by courseid asc, degree desc;

9：查询95031班级的人数。

     select count(id) from student where classid=95031;

10：查询score表中成绩最高者的记录。

       select *from score where degree=(select max(degree) from score);

      或： select id,courseid,degree order by degree desc limit 0,1;

11：查询每门课的平均数。

    select courseid,avg(degree) from score group by courseid;

12：查询score表中至少2名学生选修以3开头的课程的平均分数。

      select courseid, avg(degree) as average from score group by courseid having count(courseid)>1 and courseid like '3%';

13：查询score中分数大于70小于90的id列。

        select  *from score where degree between 70 and 90;

14：查询学生的name列和degree列和classid列(多表查询)。

        select name,degree,classid from student,score where student.id=score.id

15：查询学生的课程id课程名和degree列(多表查询)。

         select coursename,degree from score,course where course.courseid=score.courseid;

16：查询学生的姓名成绩和对应课程名。(三表查询)。

       select  name,degree,coursename from student,course,score where score.id=student.id and score.courseid=course.courseid;

17：查询95031班学生每门课的平均成绩(子查询)。

        select avg(degree) from score where id in(select id from student where classid=95031) group by courseid;

18：查询选修3105课程高于109好同学3105课程成绩的同学记录。

         select *from score where courseid=3105 and degree>(select degree from score where id=109 and courseid=3105);

19：查询成绩高于学号109课程号为3105的同学的记录。

       select *from score where  degree>(select degree from score where id=109 and courseid=3105);

20：查询学号与101和108的同学同年出生的学生的记录。

       select name, brithday,classid from student where year(brithday) in (select year(brithday) from student where id=108 or id=101);

21：查询张旭老师教的课程学生的成绩。

        select *from score where courseid in (select courseid from course where teacherid=(select id from teacher where name='张旭'));

22：查询选修课多余5人的老师的记录。

       select *from teacher where id in (select teacherid from course where courseid in (select courseid from score group by courseid having count(courseid)>5));

23：查询95033和95031班级同学的记录

       select *from student where classid in (95033,95031);

24：查询88分以上成绩的课程name。

       select coursename from course where courseid in(select courseid from score where degree>88);

25：查询计算机系老师所教课程同学的成绩。

       select *from score where courseid in(select courseid from course where teacherid in(select id from teacher where department='计算机系'));

26：查询计算机系与电子工程系不同职称的教师的记录。

        select *from teacher where department='计算机系'and professional  not in (select professional from  teacher where department='电子工程系' )
    -> union
    -> select *from teacher where department='电子工程系'and professional  not in (select professional from  teacher where department='计算机系' );

27：查询选修编号为3105的课程成绩至少高于部分选修3245课程学生的成绩的记录。

        select *from score where courseid=3105  and degree> any(select degree from score where courseid=3245);

28：查询选修编号为3105的课程成绩高于所有选修3245课程学生的成绩的记录。

        select *from score where courseid=3105  and degree> all(select degree from score where courseid=3245);

29：查询所有教师和学生的name ,sex ,brithday。

         select name,sex,brithday from student
    -> union
    -> select name,sex,brithday from teacher;

30：查询女教师和女同学的name sex brithday。

      select name,sex,brithday from student where sex='女'
    -> union
    ->  select name,sex,brithday from teacher where sex='女';

31：查询成绩比该课程平均分低的同学的成绩。

        select *from score a where degree<(select avg(degree) from score b where a.courseid=b.courseid);

32：查询任课老师的name和department。

         select *from teacher where id in(select teacherid from course where courseid in(select courseid from score group by courseid));

33：查询班级中至少有2名男生的班级。

       select classid from student where sex='男' group by classid having count(classid)>1;

34：查询班级中不姓王的同学。

         select *from student where name not like '王%';

35：查询所有学生的姓名和年龄。

        select name,year(now())-year(brithday) as age from student;

36：查询学生中年龄最大和年龄最小的数据。

        select max(year(now())-year(brithday)),min(year(now())-year(brithday)) from student;

37：以班号和年龄从大到小顺序查询student表中所有记录。

       select *from student order by classid,year(now())-year(brithday);

38：查询男教师所上的课程。

       select coursename from course where teacherid in (select  id from teacher where sex='男')；

39：查询最高分同学的信息。

      select *from score where degree =(select max(degree) from score);

40：查询和季军同性别的所有同学。

      select *from student where sex=(select sex from student where name='季军');

41：查询和季军同性别并同班的同学的信息。

        select *from student where sex=(select sex from student where name='季军') and classid=(select classid from student where name='季军');

42：查询所有选修计算机导论课程的男同学的成绩。

       select *from score where courseid=(select courseid from course where coursename='计算机导论') and id in(select id from student where sex='男' );

内容总结

以上是互联网集市为您收集整理的MySQL的查询练习全部内容，希望文章能够帮你解决MySQL的查询练习所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端