mysql学习之查询练习

——mysql查询练习

1、查询student表的所有记录

select * from student;

2、查询student表的所有记录的sname,ssex,class列

  select sname,ssex,class from student;

3、查询教师所有的单位即不重复的depart列

   select distinct depart from teacher;

4、查询score表中成绩在90-95之间的所有记录

  select * from score where degree between 90 and 95;   (包括90和95)
    select * from score where degree > 90 and degree < 95;

5、查询score表中成绩为90、91或92的记录

select * from score where degree in(90,91,92);

6、查询student表中95031或女的同学记录

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

7、以class降序查询student表中的所有记录

   select * from student order by class desc;
    desc 降序 asc 升序

8、以cno升序，degree降序查询score表的所有记录

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

9、查询95031班的学生人数

  select count(*) from student where class='95031';

10、查询score表中最高分的学生学号和课程号（子查询或排序）

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

11、查询每门课的平均成绩

 select avg(degree) from score where cno='3-105';   
    select cno,avg(degree) from score group by cno;   

group by 分组

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

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

13、查询分数大于90小于94的sno列

 select sno,degree from score where degree>90 and degree<94;

14、查询所有学生sname,cno和degree列

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

15、查询所有学生的sno,cname和degree列

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

16、查询所有学生的sname,cname,degree

  select sname,cname,degree from student,course,score 
    where student.sno=score.sno and course.cno=score.cno;

17、查询95031班学生每门课的平均分

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

18、查询选修3-105课程成绩高于105号同学3-105成绩的所有同学的记录

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

19、查询和学号为101，105的同学同年出生的所有学生的sno,sname,sbirthday

  select sno,sname,sbirthday from student where year(sbirthday) 
    in(select year(sbirthday) from student where sno in(101,105));

20、查询李教师任课的学生成绩

select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张'));