1、?查詢Student表中的所有記錄的Sname、Ssex和Class列。select sname,ssex,class fromstudent;2、查詢教師所有的單位即不重復的Depart列。select distinct depart fromteacher;3、?查詢Student表的所有記錄。select * fromstudent;4、?查詢Score表中成績在60到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 like '95031' or ssex like '女';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(*) from student group byclass;10、?查詢Score表中的最高分的學生學號和課程號。(子查詢或者排序)select * from score order by degree desc limit 1;

下面這個使用的連接查詢select max(degree) fromscore #先寫出score的最高分select * from score where degree = (select max(degree) fromscore);select sno,cno from score where degree = (select max(degree) fromscore);11、?查詢每門課的平均成績。select cno,count(*),avg(degree) from score group by cno having count(*); 多條查詢12、查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數。select cno,avg(degree) from score where cno like '3-105' and cno like '3%';select * from score group bydegree;select cno,count(*),avg(degree) from score where cno like '3%' group by cno having count(*) >= 5;select cno,count(*),avg(degree) from score where cno like '3%' group by cno having count(*) >= 5;13、查詢分數大于70，小于90的Sno列。select sno,degree from score where degree>70 and degree<90;select group_concat(sno) from score where degree>70 and degree<90;14、查詢所有學生的Sname、Cno和Degree列。(多表查詢)

student.sname,course.cno,score.degreeselect student.sname,course.cno,score.degree fromstudent,course,score;select sname,cno,degree from student join score on student.sno =score.sno;15、查詢所有學生的Sno、Cname和Degree列。

student.sno,course.cname,score.degreeselect student.sno,course.cname,score.degree fromstudent,course,score;select sno,cname,degree from score join course on course.cno =score.cno;16、查詢所有學生的Sname、Cname和Degree列。

student.sname,course.cname,score.degree;select student.sname,course.cname,score.degree fromstudent,course,score;select student.sname,course.cname,score.degree from student,course,score where sname between '李軍' and '王麗';select student.sname,cname,degree from student join score on student.sno = score.sno join course on course.cno =score.cno;17、查詢“95033”班學生的平均分。select sno from student where class = '95033';select avg(degree) from score where sno in(select sno from student where class = '95033');18、查詢選修“3-105”課程的成績高于“109”號同學成績的所有同學的記錄。select degree from score where sno = '109' and cno = '3-105';select sno,degree from score where degree > (select degree from score where sno = '109' and cno = '3-105');19、查詢score中選學多門課程的同學中分數為非最高分成績的記錄。select cno from score where (cno = '3-245' and cno = '3-105') or (cno = '3-245' and cno = '6-166') or (cno = '3-245' and cno = '6-166'); 這是個錯誤的select * from Score a where Sno in (select Sno from Score group by Sno having count(*)>1 ) and Degree not in (select max(Degree) from Score b where b.Cno =a.Cno)select * from Score a where Sno in (select Sno from Score group by Sno having count(*)>1 ) and Degree not in (select max(Degree) from Score a where Sno in (select Sno from Score group by Sno having count(*)>1))20、查詢成績高于學號為“109”、課程號為“3-105”的成績的所有記錄。select degree from score where sno = '109' and cno = '3-105';select * from score where degree > (select degree from score where sno = '109' and cno = '3-105');21、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。select sbirthday from student where sno = '108';select sno,sname,sbirthday from student where sbirthday = (select sbirthday from student where sno = '108');22、查詢“張旭“教師任課的學生成績(姓名)。select tno from teacher where tname = '張旭'; #找出教師編號select cno from course where tno = (select tno from teacher where tname = '張旭'); #找出課程編號select sno,degree from score where cno = (select cno from course where tno = (select tno from teacher where tname = '張旭'));select student.sno,degree,sname from score join student on score.sno = student.sno where student.sno in (select sno from score where cno = (select cno from course where tno = (select tno from teacher where tname = '張旭')));23、查詢考計算機導論的學生成績select cno from course where cname = '計算機導論'; #找到課程編號3-105

select sno,degree from score where cno = (select cno from course where cname = '計算機導論');24、查詢李誠老師教的課程名稱select tno from teacher where tname = '李誠'; ##找到教師編號select cname from course where tno = (select tno from teacher where tname = '李誠');25、教高等數學的老師是哪個系的select tno from course where cname = '高等數學';select depart from teacher where tno = (select tno from course where cname = '高等數學');26、查詢選修某課程的同學人數多于5人的教師姓名。select cno,count(*) from score group by cno having count(*)>=5; #找出課程編號select tno from course where cno = (select cno from score group by cno having count(*)>=5);select tname from teacher where tno = (select tno from course where cno = (select cno from score group by cno having count(*)>=5));27、查詢95033班和95031班全體學生的記錄。select * from student group by class having count(*);select * from student order by class desc;28、查詢成績表中存在有85分以上成績的課程Cno.select cno,degree from score where degree>85;29、查詢出“計算機系“教師所教課程的成績表。select tno,tname from teacher where depart = '計算機系'#查出教師編號select cno from course where tno in (select tno from teacher where depart = '計算機系'); #查出課程編號select sno,cno,degree from score where cno in (select cno from course where tno in (select tno from teacher where depart = '計算機系'));30、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學的 Cno、Sno和Degree.select max(degree) from score where cno = '3-245'; #先把選修編號為3-245課程的同學的最高成績查詢出來select cno,sno,degree from score where cno = '3-105' and degree > (select max(degree) from score where cno = '3-245');31、查詢所有教師和同學的name、sex和birthday.select tname as name,tsex as sex,tbirthday as birthday fromteacherunion

select sname,ssex,sbirthday fromstudent;32、查詢所有“女”教師和“女”同學的name、sex和birthday.select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex = '女'

union

select sname,ssex,sbirthday from student where ssex = '女';33、查詢所有成績比3-105課程平均成績低的同學的成績表。select avg(degree) from score where cno = '3-105';select degree from score where degree < (select avg(degree) from score where cno = '3-105');34、查詢所有任課教師的Tname和Depart.select tname,depart fromteacher;35、查詢所有未講課的教師的Tname和Depart.select tno from course group bytno; #找出有課的老師的編號select tname,depart from teacher where not exists(select tno from course group bytno);36、查詢至少有2名男生的班號。#####################################################3

select ssex,class from student where ssex = '男' group byclass;select class from student where exists ((select ssex,class from student where ssex = '男' group by class) * 2);37、查詢Student表中不姓“王”的同學記錄。select sname from student where sname like '王%'

select sname from student where sname not in (select sname from student where sname like '王%');38、查詢Student表中每個學生的姓名和年齡。selectsname,select floor(datediff(curdate(),@birthday)/365.2422)39、查詢Student表中最大和最小的Sbirthday日期值。select max(sbirthday) as '最大日期' , min(sbirthday) as '最小日期' fromstudent;update student set sbirthday = '1995-07-11' where sno = '108';update student set sbirthday = '1820-05-01' where sno = '105';40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。select * from student order by class desc,sbirthday desc;41、查詢“男”教師及其所上的課程。select tno from teacher where tsex = '男';select cname from course where tno in (select tno from teacher where tsex = '男');42、查詢最高分同學的Sno、Cno和Degree列。select max(degree) fromscoreselect score.sno,cno,degree fromstudentjoinscoreon student.sno =score.snowhere degree = (select max(degree) fromscore);43、查詢和“李軍”同性別的所有同學的Sname.select ssex from student where sname = '李軍';select sname from student where ssex = (select ssex from student where sname = '李軍');44、查詢和“李軍”同性別并同班的同學Sname.select class from student where sname = '李軍';select sname from student where ssex = (select ssex from student where sname = '李軍') and class = (select class from student where sname = '李軍');45、查詢所有選修“計算機導論”課程的“男”同學的成績表。select cno from course where cname = '計算機導論'; #根據課程表找到課程編號select sno from score where cno = (select cno from course where cname = '計算機導論'); #根據課程編號找到成績表里面的學生編號select sname from student where sno in (select sno from score where cno = (select cno from course where cname = '計算機導論')) and ssex = '男';