MySQL查询语句练习1

查询student表中的所有记录的sname,ssex,class列
select SNAME,SSEX,CLASS from student;
查询教师所有的单位即不重复的Depart列
select distinct DEPART from teacher;
查询Student表的所有记录
select * from student;
查询Score表中成绩在60到80之间的所有记录
select * from score where degree between 60 and 80;
查询Score表中成绩为85，86或88的记录
select * from score where degree=85 or degree=86 or degree=88;
查询Student表中“95031”班或性别为“女”的同学记录
select * from student where class='95031' and ssex='女';
以Class降序查询Student表的所有记录
select * from student order by class desc;
以Cno升序、Degree降序查询Score表的所有记录
select * from score order by degree desc , cno;
查询“95031”班的学生人数
select count(*) as '人数' from student where class='95031';
查询Score表中的最高分的学生学号和课程号
select * from score order by degree desc limit 1;
查询‘3-105’号课程的平均分
select avg(degree) from score where CNO='3-105';
查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
select cno,avg(degree) from score where cno like '3%' group by cno having count(sno)>5;
查询最低分大于70，最高分小于90的Sno列
select sno from score group by sno having min(degree)>70 and max(degree)<90;