1、表關(guān)系

????????

班級表：class	?	?	?	學(xué)生表：student	?	?	?
cid	caption	grade_id	?	sid	sname	gender	class_id
1	一年一班	1	?	1	喬丹	女	1
2	二年一班	2	?	2	艾弗森	女	1
3	三年二班	3	?	3	科比	男	2
?	?	?	?	?	?	?	?
老師表：teacher	?	?	?	課程表：course	?	?	?
tid	tname	?	?	cid	cname	teacher_id	?
1	張三	?	?	1	生物	1	?
2	李四	?	?	2	體育	1	?
3	王五	?	?	3	物理	2	?
?	?	?	?	?	?	?	?
成績表：score	?	?	?	?	年級表：class_grade	?	?
sid	student_id	course_id	score	?	gid	gname	?
1	1	1	60	?	1	一年級	?
2	1	2	59	?	2	二年級	?
3	2	2	99	?	3	三年級	?
?	?	?	?	?	?	?	?
班級任職表：teach2cls	?	?	?	?	?	?	?
tcid	tid	cid	?	?	?	?	?
1	1	1	?	?	?	?	?
2	1	2	?	?	?	?	?
3	2	1	?	?	?	?	?
4	3	2	?	?	?	?	?

二、題目要求

# 題目：1、自行創(chuàng)建測試數(shù)據(jù)；2、查詢學(xué)生總?cè)藬?shù)；3、查詢“生物”課程和“物理”課程成績都及格的學(xué)生id和姓名；4、查詢每個年級的班級數(shù)，取出班級數(shù)最多的前三個年級；5、查詢平均成績最高和最低的學(xué)生的id和姓名以及平均成績；6、查詢每個年級的學(xué)生人數(shù)；7、查詢每位學(xué)生的學(xué)號，姓名，選課數(shù)，平均成績；8、查詢學(xué)生編號為“2”的學(xué)生的姓名、該學(xué)生成績最高的課程名、成績最低的課程名及分?jǐn)?shù)；9、查詢姓“李”的老師的個數(shù)和所帶班級數(shù)；10、查詢班級數(shù)小于5的年級id和年級名；11、查詢班級信息，包括班級id、班級名稱、年級、年級級別(12為低年級，34為中年級，56為高年級)，示例結(jié)果如下；班級id 班級名稱 年級 年級級別1 一年一班 一年級 低12、查詢學(xué)過“張三”老師2門課以上的同學(xué)的學(xué)號、姓名；13、查詢教授課程超過2門的老師的id和姓名；14、查詢學(xué)過編號“1”課程和編號“2”課程的同學(xué)的學(xué)號、姓名；15、查詢沒有帶過高年級的老師id和姓名；16、查詢學(xué)過“張三”老師所教的所有課的同學(xué)的學(xué)號、姓名；17、查詢帶過超過2個班級的老師的id和姓名；18、查詢課程編號“2”的成績比課程編號“1”課程低的所有同學(xué)的學(xué)號、姓名；19、查詢所帶班級數(shù)最多的老師id和姓名；20、查詢有課程成績小于60分的同學(xué)的學(xué)號、姓名；21、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號、姓名；22、查詢至少有一門課與學(xué)號為“1”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號和姓名；23、查詢至少學(xué)過學(xué)號為“1”同學(xué)所選課程中任意一門課的其他同學(xué)學(xué)號和姓名；24、查詢和“2”號同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)號和姓名；25、刪除學(xué)習(xí)“張三”老師課的score表記錄；26、向score表中插入一些記錄，這些記錄要求符合以下條件：①沒有上過編號“2”課程的同學(xué)學(xué)號；②插入“2”號課程的平均成績；27、按平均成績從低到高顯示所有學(xué)生的“語文”、“數(shù)學(xué)”、“英語”三門的課程成績，按如下形式顯示： 學(xué)生ID,語文,數(shù)學(xué),英語,有效課程數(shù),有效平均分；28、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；29、按各科平均成績從低到高和及格率的百分?jǐn)?shù)從高到低順序；30、課程平均分從高到低顯示（顯示任課老師）；31、查詢各科成績前三名的記錄(不考慮成績并列情況)32、查詢每門課程被選修的學(xué)生數(shù)；33、查詢選修了2門以上課程的全部學(xué)生的學(xué)號和姓名；34、查詢男生、女生的人數(shù)，按倒序排列；35、查詢姓“張”的學(xué)生名單；36、查詢同名同姓學(xué)生名單，并統(tǒng)計同名人數(shù)；37、查詢每門課程的平均成績，結(jié)果按平均成績升序排列，平均成績相同時，按課程號降序排列；38、查詢課程名稱為“數(shù)學(xué)”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)；39、查詢課程編號為“3”且課程成績在80分以上的學(xué)生的學(xué)號和姓名；40、求選修了課程的學(xué)生人數(shù)41、查詢選修“王五”老師所授課程的學(xué)生中，成績最高和最低的學(xué)生姓名及其成績；42、查詢各個課程及相應(yīng)的選修人數(shù)；43、查詢不同課程但成績相同的學(xué)生的學(xué)號、課程號、學(xué)生成績；44、查詢每門課程成績最好的前兩名學(xué)生id和姓名；45、檢索至少選修兩門課程的學(xué)生學(xué)號；46、查詢沒有學(xué)生選修的課程的課程號和課程名；47、查詢沒帶過任何班級的老師id和姓名；48、查詢有兩門以上課程超過80分的學(xué)生id及其平均成績；49、檢索“3”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號；50、刪除編號為“2”的同學(xué)的“1”課程的成績；51、查詢同時選修了物理課和生物課的學(xué)生id和姓名； View Code

三、解答

1、表與表之間的關(guān)系

學(xué)生表：student? ? 多對一? ? ?班級表：class

班級表：class? ? ? ? 多對一? ? ?年級表：class_grade

課程表：course? ? ?多對一? ? ?老師表：teacher

成績表：score　　多對一　 學(xué)生表：student

成績表：score　　多對一? ? ?課程表：course

teacher2cls：

老師表：teacher　多對多? ? ? 班級表：class

2、建立表格

一、建立表格 先建立被關(guān)聯(lián)的表，再建立關(guān)聯(lián)表0、班級表：class 多對一 年級表：class_grade建立年級表--------class_grade----------create table class_grade( gid int primary key auto_increment, gname varchar(20) not null unique );1、學(xué)生表：student 多對一 班級表：class建立班級表-----------class------------create table class( cid int primary key auto_increment, caption varchar(20) not null, grade_id int not null, foreign key(grade_id) references class_grade(gid) on delete cascade on update cascade );建立學(xué)生表-------student-------------create table student( sid int primary key auto_increment, sname varchar(20) not null, gender enum('male','female') not null default 'male', class_id int not null, foreign key (class_id) references class(cid) on delete cascade on update cascade );3、課程表：course 多對一 老師表：teacher建立老師表---------teacher-----------create table teacher( tid int not null primary key auto_increment, tname varchar(20) not null );建立課程表---------course------------create table course( cid int primary key auto_increment, cname varchar(20) not null, teacher_id int not null, foreign key(teacher_id) references teacher(tid) on delete cascade on update cascade );4、成績表：score　　多對一　 學(xué)生表：student 5、成績表：score　　多對一 課程表：course 學(xué)生表：student 課程表：course建立成績表--------score-----------create table score( sid int primary key auto_increment, student_id int not null, foreign key(student_id) references student(sid) on delete cascade on update cascade, course_id int not null, foreign key(course_id) references course(cid) on delete cascade on update cascade, unique(student_id,course_id), score int not null );6、teacher2cls：老師表：teacher　多對多 班級表：class建立班級任職表------teacher2cls-------create table teach2cls( tcid int primary key auto_increment, tid int not null, cid int not null, unique(tid,cid), foreign key(tid) references student(sid) on delete cascade on update cascade, foreign key(cid) references class(cid) on delete cascade on update cascade ); View Code

3、準(zhǔn)備數(shù)據(jù)

# ------------------------插入數(shù)據(jù) # ----------------------------設(shè)定4個 年級 ------------------------- insert into class_grade(gname) values ('一年級'), ('二年級'), ('三年級'), ('四年級'); # ---------------------設(shè)定每個年級的 班級 個數(shù)------------------------ 共10個班級 1年級1個班；2年級2個班；3年級3個班；4年級4個班insert into class(caption,grade_id) values ('一年級一班',1),('二年級一班',2), ('二年級二班',2),('三年級一班',3), ('三年級二班',3), ('三年級三班',3),('四年級一班',4), ('四年級二班',4), ('四年級三班',4), ('四年級四班',4);# ------------------設(shè)定 學(xué)生表 每個班名學(xué)生------------------------- 1 2 4 4 4 其余3--------------共30名學(xué)生 ('一年級一班') 1個學(xué)生 ('二年級一班') 2個學(xué)生 ('二年級二班') 3個學(xué)生 ('三年級一班') 4個學(xué)生 ('三年級二班') 4個學(xué)生 ('三年級三班') 4個學(xué)生 ('四年級一班') 3個學(xué)生 ('四年級二班') 3個學(xué)生 ('四年級三班') 3個學(xué)生 ('四年級四班') 3個學(xué)生insert into student(sname,gender,class_id) values ('喬丹','male',1),('alex','male',2), ('egon','female',2),('喬丹2','male',3), ('愛麗絲2','female',3), ('布萊恩2','male',3),('喬丹','male',4), ('alex2','male',4), ('egon2','female',4), ('victor2','male',4),('alex3','male',5), ('喬丹3','male',5), ('愛麗絲3','female',5), ('布萊恩3','male',5),('victor2','male',6), ('alex3','male',6), ('egon3','female',6), ('張勝利','male',6),('喬丹4','male',7), ('愛麗絲4','female',7), ('布萊恩4','male',7),('alex4','male',8), ('egon4','female',8), ('victor4','male',8),('喬丹5','male',9), ('愛麗絲5','female',9), ('布萊恩5','male',9),('alex5','male',10), ('egon5','female',10), ('victor5','male',10);# ---------------老師表---------------------- # 設(shè)置10名老師 ('吉澤')----沒授課---- insert into teacher(tname) values ('張三'), ('李四'), ('王五'), ('曹操'), ('劉備'), ('孫權(quán)'), ('許褚'), ('趙云'), ('黃蓋'), ('吉澤');# ----------------課程表------------------- # ---13門課程 ('藝術(shù)',10)---沒人選----- insert into course(cname,teacher_id) values ('語文',1), ('數(shù)學(xué)',2), ('英語',3), ('物理',4), ('化學(xué)',5), ('生物',6),('體育',7), ('音樂',8), ('美術(shù)',9), ('計算機(jī)',3), ('政治',3), ('歷史',6), ('藝術(shù)',10);# --------------------成績表------------------------ # 30個學(xué)生選課數(shù)為 每個學(xué)生 選課數(shù)為 ，1 ，2 ，3 ，4 ，5 ，6 ，7 ，其余全部2insert into score(student_id,course_id,score) values (1,1,70),(2,3,50),(2,4,60),(3,5,80),(3,6,90),(3,7,91),(4,7,100),(4,8,50),(4,9,60),(4,10,70),(5,9,60),(5,10,70),(5,11,80),(5,12,90),(5,1,92), (6,11,80),(6,12,90),(6,1,93),(6,2,94),(6,3,95),(6,4,96),(7,1,97),(7,2,50),(7,3,60),(7,4,66),(7,5,70),(7,6,80),(7,7,90), (8,3,60),(8,4,70),(9,5,80),(9,6,90),(10,7,99),(10,8,50), (11,9,60),(11,10,75),(12,11,80),(12,12,90),(13,1,92),(13,2,50),(14,3,60),(14,4,70),(15,5,80),(15,6,90), (16,7,99),(16,8,50),(17,9,61),(17,10,74),(18,11,80),(18,12,85),(19,1,77),(19,2,91),(20,3,100),(20,4,55),(21,5,60),(21,6,74), (22,7,80),(22,8,90),(23,9,88),(23,10,50),(24,11,60),(24,12,70),(25,1,80),(25,2,90),(26,3,85),(26,4,50), (27,5,61),(27,6,75),(28,7,80),(28,8,90),(29,9,100),(29,10,50),(30,11,61),(30,12,78);# -----------------班級任職表：teach2cls-------------1 345 其余 2cid：4個4 3個3 5個5 count(tid)--count(cid) cid---------count(cid)1------------2 1---------------12------------2 2---------------23------------2 3---------------24------------2 4---------------25------------2 5---------------46------------1 6---------------27------------3 7---------------18------------4 8---------------19------------4 9---------------210--------------1insert into teach2cls(tid,cid) values (1,5), (1,2), (2,3), (2,5), (3,5), (3,6), (4,7), (4,8), (5,9), (5,10),(6,1), (7,2), (7,3), (7,4),(8,4), (8,9), (8,5), (8,6),(9,3), (9,5), (9,1), (9,4), (9,8); View Code

4、查詢語句

# -----------------查詢語句------------------------- 1、自行創(chuàng)建測試數(shù)據(jù)；2、查詢學(xué)生總?cè)藬?shù)； select count(sid) as 學(xué)生總?cè)藬?shù) from student;3、查詢“生物”課程和“物理”課程成績都及格的學(xué)生id和姓名； select sid,sname from student where sid in (select student_id from score where course_id in (select cid from course where course.cname in('物理','生物')) and score>=60 group by student_id having count(course_id)=2 );4、查詢每個年級的班級數(shù)，取出班級數(shù)最多的前三個年級； selectgrade_id 年級編號,count(caption) as class_number fromclass group by grade_id order by class_number desc limit 3;5、查詢平均成績最高和最低的學(xué)生的id和姓名以及平均成績；------------------------------------------- select student.sid,student.sname,t1.avg_score from student, ( select student_id ,avg(score) as avg_score from score where student_id in ( (select student_id from score group by student_id order by avg(score) desc limit 1), (select student_id from score group by student_id order by avg(score) asc limit 1) ) group by student_id ) as t1 where student.sid = t1.student_id;6、查詢每個年級的學(xué)生人數(shù)；select gname,t2.count_student from class_grade inner join ( select t1.grade_id ,count(t1.sid)as count_student from (select student.sid,class.grade_id from student,class where student.class_id=class.cid) as t1 group by t1.grade_id ) as t2 on class_grade.gid=t2.grade_id order by t2.count_student asc;7、查詢每位學(xué)生的學(xué)號，姓名，選課數(shù)，平均成績； select sid,sname,t1.count_course,t1.avg_score from student inner join (select student_id,count(course_id) as count_course,avg(score) as avg_score from score group by student_id) as t1 on student.sid=t1.student_id;8、查詢學(xué)生編號為“2”的學(xué)生的姓名、該學(xué)生成績最高的課程名、成績最低的課程名及分?jǐn)?shù)； select student.sname,course.cname,t1.score from ( select student_id,course_id,score from score where student_id=2 and score in ( (select max(score) from score where student_id=2), (select min(score) from score where student_id=2) ) )as t1 inner join student on t1.student_id=student.sid inner join course on t1.course_id=course.cid ;9、查詢姓“李”的老師的個數(shù)和所帶班級數(shù)； select teacher.tid,teacher.tname,t1.count_class from teacher inner join ( select tid ,count(cid) as count_class from teach2cls where tid in (select tid from teacher where tname like '李%') group by tid ) as t1 where teacher.tid=t1.tid;10、查詢班級數(shù)小于5的年級id和年級名； select gid,gname,t2.count_class from class_grade inner join (select t1.grade_id,t1.count_class from (select grade_id ,count(cid) as count_class from class group by grade_id) as t1 where t1.count_class<5) as t2 on t2.grade_id=class_grade.gid;# 第二種更簡潔 select gid ,gname from class_grade where gid in (select grade_id from class group by grade_id having count(class.grade_id)<5);11、查詢班級信息，包括班級id、班級名稱、年級、年級級別(12為低年級，34為中年級，56為高年級)，示例結(jié)果 如下；班級id 班級名稱 年級 年級級別1 一年一班 一年級 低selectclass.cid as 班級id,class.caption 班級名稱,class_grade.gname as 年級,case when class_grade.gid between 1 and 2 then '低'when class_grade.gid between 3 and 4 then '中'when class_grade.gid between 5 and 6 then '高'else 0 end as '年級級別'fromclass,class_grade whereclass.grade_id=class_grade.gid;12、查詢學(xué)過“張三”老師2門課以上的同學(xué)的學(xué)號、姓名；select student.sid ,student.sname from student, (select student_id from score where course_id in ( select course.cid from teacher, course where teacher.tid = course.teacher_id and teacher.tname = '張三') group by student_id having count(course_id) > 2) as t1 where student.sid=t1.student_id;13、查詢教授課程超過2門的老師的id和姓名； select teacher.tid,teacher.tname from teacher, (select teacher_id,count(cid) from course group by teacher_id having count(cid)>2) as t1 where teacher.tid=t1.teacher_id;# 方法二更簡潔 select tid,tname from teacher where tid in (select teacher_id from course group by teacher_id having count(cid)>2);14、查詢學(xué)過編號“1”課程和編號“2”課程的同學(xué)的學(xué)號、姓名； select sid,sname from student where sid in (select student_id from score where course_id in (1,2));15、查詢沒有帶過高年級的老師id和姓名；select tid,tname from teacher where tid not in (select teach2cls.tid from teach2cls,class where teach2cls.cid=class.cid and class.grade_id between 5 and 6 );16、查詢學(xué)過“張三”老師所教的所有課的同學(xué)的學(xué)號、姓名；select student.sid,student.sname from student, (select score.student_id,score.course_id from score,(select cid ,teacher_id from course where teacher_id in(select tid from teacher where tname='張三')) as t1 where score.course_id=t1.cid ) as t2 where student.sid=t2.student_id;# 第二種更簡潔 select sid,sname from student where sid in (select student_id from score where course_id in(select cid from course where teacher_id in(select tid from teacher where tname='張三')) );17、查詢帶過超過2個班級的老師的id和姓名； select tid,tname from teacher where tid in (select tid from teach2cls group by tid having count(cid)>2);18、查詢課程編號“2”的成績比課程編號“1”課程低的所有同學(xué)的學(xué)號、姓名; select sid,sname from student where sid in (select t1.student_id from(select student_id,score from score where course_id =2) as t1,(select student_id,score from score where course_id =1) as t2 where t1.score<t2.score and t1.student_id=t2.student_id );19、查詢所帶班級數(shù)最多的老師id和姓名； select tid,tname from teacher where tid in (select tid from teach2cls group by tid having count(cid) =(select count(tid) from teach2cls group by tid order by count(cid) desc limit 1) );20、查詢有課程成績小于60分的同學(xué)的學(xué)號、姓名； select sid,sname from student where sid in (select distinct student_id from score where score<60 );21、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號、姓名； select sid,sname from student where sid not in (select student_id from scoregroup by student_id having count(course_id)=(select count(cid) from course) );22、查詢至少有一門課與學(xué)號為“1”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號和姓名； select sid ,sname from student where sid in (select student_id from scorewhere course_id in(select course_id from score where student_id=1) group by student_id );23、查詢至少學(xué)過學(xué)號為“1”同學(xué)所選課程中任意一門課的其他同學(xué)學(xué)號和姓名； select sid ,sname from student where sid in (select student_id from scorewhere student_id!=1 and course_id in(select course_id from score where student_id=1) group by student_id );24、查詢和“2”號同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)號和姓名； select sid,sname from student where sid in (select student_id from scorewhere student_id !=2 and course_id in(select course_id from score where student_id=2 group by course_id)group by student_idhaving count(course_id) =(select count(course_id) as count_course from score where student_id=2 group by student_id) );25、刪除學(xué)習(xí)“張三”老師課的score表記錄； delete from score where course_id in (select course.cid from course,teacher where course.teacher_id = teacher.tidand teacher.tname='張三' )26、向score表中插入一些記錄，這些記錄要求符合以下條件：①沒有上過編號“2”課程的同學(xué)學(xué)號；②插入“2”號課 程的平均成績； insert into score(student_id,course_id,score) select t1.sid,2,t2.avg_score from (select sid from student where sid not in(select student_id from score where course_id=2) ) as t1, (select avg(score) from score where course_id=2) as t2;27、按平均成績從低到高顯示所有學(xué)生的“語文”、“數(shù)學(xué)”、“英語”三門的課程成績，按如下形式顯示： 學(xué)生ID,語文, 數(shù)學(xué),英語,課程數(shù)和平均分； select sc.student_id, (selectscore.score from score left join course on score.course_id=course.cid where course.cname='語文'and score.student_id = sc.student_id )as Chinese, (selectscore.score from score left join course on score.course_id = course.cid where course.cname='數(shù)學(xué)'and score.student_id=sc.student_id )as math, (selectscore.score from score left join course on score.course_id = course.cid where course.cname='英語'and score.student_id=sc.student_id )as english,count(sc.course_id),avg(sc.score) from score as sc group bysc.student_id order byavg(sc.score) asc;28、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分； select course.cid,max(score.score) as 最高分 ,min(score.score) as 最低分 from course left join score on course.cid = score.course_id group by score.course_id;29、按各科平均成績從低到高和及格率的百分?jǐn)?shù)從高到低順序； # 這題有點(diǎn)小問題 select course_id,avg(score.score) as avg_score, (sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100) as percent from score group by course_id order by avg_score asc, percent desc;30、課程平均分從高到低顯示（顯示任課老師）； select course.cid,tname,t1.avg_score from course,teacher, ( select course_id,avg(score) as avg_score from score group by course_id) as t1 where course.cid=t1.course_id and teacher.tid=course.teacher_id order by t1.avg_score desc;31、查詢各科成績前三名的記錄(不考慮成績并列情況) ； select score.sid,score.student_id,score.course_id,score.score,t1.first_score,t1.second_score,t1.third_score from score inner join( selects1.sid,(select score from score as s2 where s1.course_id=s2.course_id order by score desc limit 0,1) as first_score,(select score from score as s3 where s1.course_id=s3.course_id order by score desc limit 1,1) as second_score,(select score from score as s4 where s1.course_id=s4.course_id order by score desc limit 2,1) as third_scorefrom score as s1 ) as t1 on score.sid = t1.sid where score.score in(t1.first_score,t1.second_score,t1.third_score );32、查詢每門課程被選修的學(xué)生數(shù)； select course.cid as 課程編號,count(score.student_id) as stu_num from score right join course on score.course_id = course.cid group by course_id;# 此時 right join course 以course為主33、查詢選修了2門以上課程的全部學(xué)生的學(xué)號和姓名； select sid,sname from student where sid in (select student_id from score group by student_id having count(course_id)>2);34、查詢男生、女生的人數(shù)，按倒序排列； select gender,count(sid) from student group by gender order by count(sid) desc;35、查詢姓“張”的學(xué)生名單； select student.sid,student.sname,student.gender,class.caption from class, student where student.sname like '張%' and student.class_id=class.cid;36、查詢同名同姓學(xué)生名單，并統(tǒng)計同名人數(shù)； select sname,count(sid) as count_sname from student group by sname having count(sid)>=2;37、查詢每門課程的平均成績，結(jié)果按平均成績升序排列，平均成績相同時，按課程號降序排列； #先按照avg(score)升序排，如果avg(score)相同則按照id 降序排 select course_id,avg(score) from score group by course_id order by avg(score) asc ,course_id desc;38、查詢課程名稱為“數(shù)學(xué)”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)；select student.sname,t1.score from student, (select student_id ,score from score where course_id = (select cid from course where cname='數(shù)學(xué)') and score <60) as t1 where t1.student_id=student.sid;39、查詢課程編號為“3”且課程成績在80分以上的學(xué)生的學(xué)號和姓名；select sid,sname from student where sid in (select student_id from score where course_id=3 and score>80);40、求選修了課程的學(xué)生人數(shù) select course_id,count(student_id) as count_student from score group by course_id;41、查詢選修“王五”老師所授課程的學(xué)生中，成績最高和最低的學(xué)生姓名及其成績；select student.sname,t2.course_id,t2.score,t2.max_score,t2.min_score from student inner join ( select score.student_id,score.course_id,score.score,t1.max_score,t1.min_score from score,( select course_id,max(score) as max_score ,min(score) as min_score from score where course_id in ( select cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = '王五') group by course_id) as t1 where score.course_id = t1.course_id and score.score in (max_score,min_score) )as t2 on student.sid = t2.student_id;42、查詢各個課程及相應(yīng)的選修人數(shù)；select course.cid,course.cname,t1.count_course from course, (select course.cid,count(score.student_id) as count_course from score right join course on course.cid=score.course_id group by score.course_id) as t1 where course.cid=t1.cid;43、查詢不同課程但成績相同的學(xué)生的學(xué)號、課程號、學(xué)生成績；select distinct s1.student_id,s2.student_id,s1.course_id as s1_course_id,s2.course_id as s2_course_id,s1.score,s2.score from score as s1, score as s2 where s1.course_id != s2.course_id and s1.score = s2.score;44、查詢每門課程成績最好的前兩名學(xué)生id和姓名；selectstudent.sid,student.sname,t2.course_id,t2.score,t2.first_score,t2.second_scorefromstudentinner join (selectscore.student_id,score.course_id,score.score,t1.first_score,t1.second_scorefromscoreinner join (selects1.sid,(select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,(select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_scorefromscore as s1) as t1 on score.sid = t1.sidwherescore.score in (t1.first_score,t1.second_score)) as t2 on student.sid = t2.student_id; 45、檢索至少選修兩門課程的學(xué)生學(xué)號； select student_id from score group by student_id having count(course_id)>1;46、查詢沒有學(xué)生選修的課程的課程號和課程名； select cid,cname from course where cid not in (select course_id from score group by course_id);47、查詢沒帶過任何班級的老師id和姓名； select tid,tname from teacher where tid not in (select tid from teach2cls group by tid);48、查詢有兩門以上課程超過80分的學(xué)生id及其平均成績； select student_id ,avg(score) from score where student_id in (select student_id from score where score>80 group by student_id having count(course_id)>2);49、檢索“3”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號； select student_id,score from score where course_id=3 and score<60 order by score desc;50、刪除編號為“2”的同學(xué)的“1”課程的成績； delete from score where sid= (select t1.sid from (select sid from score where student_id =2 and course_id=1) as t1);51、查詢同時選修了物理課和生物課的學(xué)生id和姓名；select sid,sname from student where sid in ( select student_id from score where course_id in ( select cid from course where course.cname in('物理','生物') ) group by student_id having count(course_id)=2 ); View Code

重點(diǎn)關(guān)注的語法題

1(12為低年級，34為中年級，56為高年級)

11、查詢班級信息，包括班級id、班級名稱、年級、年級級別(12為低年級，34為中年級，56為高年級)，示例結(jié)果 如下；班級id 班級名稱 年級 年級級別1 一年一班 一年級 低selectclass.cid as 班級id,class.caption 班級名稱,class_grade.gname as 年級,case when class_grade.gid between 1 and 2 then '低'when class_grade.gid between 3 and 4 then '中'when class_grade.gid between 5 and 6 then '高'else 0 end as '年級級別'fromclass,class_grade whereclass.grade_id=class_grade.gid;

2、刪除學(xué)習(xí)“張三”老師課的score表記錄；

delete from score where course_id in (select course.cid from course,teacher where course.teacher_id = teacher.tidand teacher.tname='張三' )

3、刪除編號為“2”的同學(xué)的“1”課程的成績；

delete from score where sid=
(select t1.sid from (select sid from score where student_id =2 and course_id=1) as t1);

4、truncate刪除表中的所有數(shù)據(jù)，保留表的結(jié)構(gòu)

?

轉(zhuǎn)載于:https://www.cnblogs.com/foremostxl/p/9778647.html

新人創(chuàng)作打卡挑戰(zhàn)賽發(fā)博客就能抽獎！定制產(chǎn)品紅包拿不停！

總結(jié)

以上是生活随笔為你收集整理的mysql闯关练习的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。