Mysql学习笔记(查询语句练习题1)

表格详情：

student表：

teacher表：

course表：

score表：

 

Mysql查询语句练习：

1.查询Student表中的所有记录的Sname、Ssex和Class列

select sname,ssex,class from student;

 

2.查询教师所有的单位即不重复的Depart列

select distinct(depart) from teacher;

 

3.查询Student表的所有记录

select * from student;

 

4.查询Score表中成绩在60到80之间的所有记录

select * from score where degree between 60 and 80;

select * from score where degree > 60 and degree < 80;

 

5.查询Score表中成绩为85，86或88的记录

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

 

6.查询Student表中“95031”班或性别为“女”的同学记录

select * from student where class="95031" or ssex="女";

 

7.以Class降序查询Student表的所有记录

select * from student order by class desc;

 

8.以Cno升序、Degree降序查询Score表的所有记录

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

 

9.查询“95031”班的学生人数

select count(sno) from student where class = "95031";

 

10.查询Score表中的最高分的学生学号和课程号,子查询

select sno,cno from score where degree = (select max(degree) from score);

select sno,cno from score order by degree desc limit 0,1;

 

11.查询‘3-105’号课程的平均分

select cno,avg(degree) from score where cno="3-105";

 

12.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数

select cno,avg(degree) from score where cno like '3%' group by cno having count(cno) >= 5;

 

13.查询每个学生最低分大于70，最高分小于90的Sno列

select sno from score group by sno having min(degree)>70 and max(degree)<90;

 

14.查询所有学生的Sname、Cno和Degree列

select A.sname,B.cno,B.degree from student A,score B where A.sno=B.sno;

 

15.查询所有学生的Sno、Cname和Degree列

select A.sno,B.cname,A.degree from score A,course B where A.cno=B.cno;

 

16.查询所有学生的Sname、Cname和Degree列

select A.sname,B.cname,C.degree from student A join (course B,score C) on A.sno=C.sno and B.cno=C.cno;

 

17.查询“95033”班所选课程的平均分、课程号

select cno,avg(degree) from score where sno in (select sno from student where class="95033") group by cno;

select A.cno,avg(A.degree) from score A join student B on A.sno=B.sno and B.class="95033" group by A.cno;

 

18.假设使用如下命令建立了一个grade表

现查询所有同学的Sno、Cno和rank列

select A.sno,A.cno,B.rank from score A,grade B where A.degree between B.low and B.upp;

 

19.查询选修“3-105”课程且成绩高于“109”号同学成绩的所有同学的记录

select * from score where cno="3-105" and degree > (select degree from score where sno="109" and class="3-105");

select A.* from score as A,score as B where A.cno="3-105" and A.degree>B.degree and B.sno="109" and B.cno="3-105";

 

20.查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

select * from score where degree<(select max(degree) from score) group by sno having count(sno)>1;

 

21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

select * from score where degree >(select degree from score where sno="109" and cno="3-105");

 

22.查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列

select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday) from student where sno="107");