博客

链接如下：
学生成绩表传送门

查询最后一条语句：
select * from [表名] order by id desc limit 1;
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from students;
2、 查询教师所有的单位即不重复的Depart列。
select * from teachers group by depart;
3、 查询Student表的所有记录。
select * from students;
4、 查询Score表中成绩在60到80之间的所有记录。
select  *  from scores  where  degree between 60 and 80;
5、 查询Score表中成绩为85，86或88的记录。
select  *  from scores  where  degree in (85,86,88);
6、 查询Student表中“95031”班或性别为“女”的同学记录。
select  *  from students where class='95031' or ssex='女';
7、 以Class降序查询Student表的所有记录。
select  *  from students order by class desc;
8、 以Cno升序、Degree降序查询Score表的所有记录。
select  *  from scores order by cno asc,degree desc;
9、 查询“95031”班的学生人数。
select  count(sno) al  from students where class='95031';
10、查询Score表中的最高分的学生学号和课程号。
select sno,cno  from scores where degree=(select max(degree) from scores);
11、查询‘3-105’号课程的平均分。
select avg(degree) pinjun from scores where cno='3-105';
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(degree) FROM scores WHERE cno IN (SELECT cno FROM scores 
GROUP BY cno HAVING COUNT(cno)> 5 ) AND cno LIKE '3%';
13、查询最低分大于70，最高分小于90的Sno列。
select sno from scores  group by sno having min(degree)>70 and 
max(degree)<90;
14、查询所有学生的Sname、Cno和Degree列。
select cno,sname,degree from scores sc,students st  where sc.sno=st.sno;
15、查询所有学生的Sno、Cname和Degree列。
select sc.sno,sname,degree from scores sc,students st  where sc.sno=st.sno;
16、查询所有学生的Sname、Cname和Degree列。
select sc.sno,cname,degree from scores sc,students st,courses co  
where sc.sno=st.sno and sc.cno=co.cno;
17、查询“95033”班所选课程的平均分。
select avg(degree) from scores sc,students st where sc.sno=st.sno and 
st.class='95033';
18、假设使用如下命令建立了一个grade表：
现查询所有同学的Sno、Cno和rank列。
select sno,cno,degree,gr.rank from scores sc,grade gr where degree 
between low and upp;
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from scores where cno='3-105' and 
degree>(select degree from scores where sno='109' and cno='3-105');
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select * from Scores a where Degree <(select MAX(degree) from scores 
b where a.Cno=b.Cno) and Sno in(select Sno from Scores group by 
Sno having count(*)>1)
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from students,scores where students.sno=scores.sno
and scores.degree>(select degree from scores where
cno='3-105' and sno='109')
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT sno,sname,YEAR(sbirthday) FROM students WHERE YEAR(sbirthday) 
 = (SELECT YEAR(sbirthday) FROM students WHERE sno = '108';
23、查询“张旭“教师任课的学生成绩。
select degree from scores where cno=(select cno from courses where 
tno=(select tno from teachers where tname='张旭'));
24、查询选修某课程的同学人数多于5人的教师姓名。
select tname from teachers where tno=(select tno 
from courses where cno=(select cno from 
scores group by cno having count(*)>5));
25、查询95033班和95031班全体学生的记录。
select * from students where class='95033' or class='95031';
26、查询存在有85分以上成绩的课程Cno.
select  distinct cno from scores where degree >85;
27、查询出“计算机系“教师所教课程的成绩表。
select sc.degree from scores sc,courses co,teachers te where 
sc.cno=co.cno and co.tno=te.tno and te.depart like 
'计算%';
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select Tname,Prof from teachers a where Prof not in(select Prof 
from teachers b where a.Depart!=b.Depart)
29、查询选修编号为“3-105“课程且成绩高于选修编号为“3-245”的课程分数最大值的Cno、Sno和Degree,并按Degree从高到低次序排序。
select cno,sno,degree from scores where degree>=(select 
max(degree) from scores where cno='3-245') and cno='3-105'
order by degree;
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程分数最大值的Cno、Sno和Degree.
select Cno,Sno,Degree from Scores a where (select Degree from Scores b 
where Cno='3-105' and b.Sno=a.Sno)>(select Degree from Scores c where 
Cno='3-245' and c.Sno=a.Sno)
31、查询所有教师和同学的name、sex和birthday.
select distinct Sname as name,Ssex as sex,Sbirthday as birthday from 
students union
select distinct Tname as name,Tsex as sex,Tbirthady as birthday from teachers;
32、查询所有“女”教师和“女”同学的name、sex和birthday.
select distinct Sname as name,Ssex as sex,Sbirthday as birthday from 
students where Ssex='女' union
select distinct Tname as name,Tsex as sex,Tbirthady as birthday from 
teachers where Tsex='女'
33、查询成绩比该课程平均成绩低的同学的成绩表。
select Sno,Cno,Degree from Scores a where a.Degree<(select AVG(Degree) 
from Scores b where a.Cno=b.Cno);
34、查询所有任课教师的Tname和Depart.
select Tname,Depart from teachers where Tname in (select distinct 
Tname from teachers,courses,scores where Teachers.Tno=Courses.Tno 
and Courses.Cno=Scores.Cno);
35  查询所有未讲课的教师的Tname和Depart. 
select Tname,Depart from teachers where Tname not in (select distinct 
Tname from Teachers,courses,Scores where teachers.Tno=course.tno
and courses.cno=scores.Cno);
36、查询至少有2名男生的班号。
select class from students where ssex='男' group by 
class having count(*)>1;
37、查询Student表中不姓“王”的同学记录。
select sname from students where sname not like '王%';
38、查询Student表中每个学生的姓名和年龄。
select now(),sbirthday,timestampdiff(YEAR,sbirthday,now()) AS '年龄' 
from students;
39、查询Student表中最大和最小的Sbirthday日期值。
select max(Sbirthday) as 最大,min(Sbirthday) as 最小 from students;
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select  * from students order by class desc,sbirthday asc;
41、查询“男”教师及其所上的课程。
select tname,cname from teachers te,courses co 
where te.tsex='男' and te.tno=co.tno;
42、查询最高分同学的Sno、Cno和Degree列。
select sno,cno,Degree from scores where 
degree=(select max(degree)from scores)
43、查询和“李军”同性别的所有同学的Sname.
select sname from students where ssex=(select ssex from students
where sname='李军') and sname not in ('李军');
44、查询和“李军”同性别并同班的同学Sname.
select sname from students where ssex=(select ssex from students where  
sname='李军') 
and sname not in ('李军') and class=(select class from students where  
sname='李军')
45、查询所有选修“计算机导论”课程的“男”同学的成绩表
select sno,degree from scores where sno in (select sno from students
where Ssex='男') and cno in (select cno from ourses where 
cname='计算机导论')