mysql的小练习
建立如下表：
建表語句：
class表創建語句
create table class(cid int not null auto_increment primary key, caption varchar(32) not null)engine=innodb default charset=utf8;student表創建語句
create table student(-> sid int not null auto_increment primary key,-> name varchar(32) not null,-> gender varchar(8) not null,-> class_id int not null)engine=innodb default charset=utf8;teacher表創建語句
create table teacher(-> tid int not null auto_increment primary key,-> tname varchar(32) not null)engine=innodb default charset=utf8;course表創建語句
create table course(-> cid int not null auto_increment primary key,-> cname varchar(16) not null,-> teacher_id int not null)engine=innodb default charset=utf8;score表創建語句
create table score(-> sid int not null auto_increment primary key,-> student_id int not null,-> corse_id int not null,-> number int not null)engine=innodb default charset=utf8;
練習題目：
1、查詢所有的課程的名稱以及對應的任課老師姓名2、查詢學生表中男女生各有多少人3、查詢物理成績等于100的學生的姓名4、查詢平均成績大于八十分的同學的姓名和平均成績5、查詢所有學生的學號，姓名，選課數，總成績6、 查詢姓李老師的個數7、 查詢沒有報李平老師課的學生姓名8、 查詢物理課程比生物課程高的學生的學號9、 查詢沒有同時選修物理課程和體育課程的學生姓名10、查詢掛科超過兩門(包括兩門)的學生姓名和班級 、查詢選修了所有課程的學生姓名12、查詢李平老師教的課程的所有成績記錄13、查詢全部學生都選修了的課程號和課程名14、查詢每門課程被選修的次數15、查詢之選修了一門課程的學生姓名和學號16、查詢所有學生考出的成績并按從高到低排序（成績去重）17、查詢平均成績大于85的學生姓名和平均成績18、查詢生物成績不及格的學生姓名和對應生物分數19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名20、查詢每門課程成績最好的前兩名學生姓名21、查詢不同課程但成績相同的學號，課程號，成績22、查詢沒學過“葉平”老師課程的學生姓名以及選修的課程名稱；23、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名；24、任課最多的老師中學生單科成績最高的學生姓名
答案：
#1、查詢所有的課程的名稱以及對應的任課老師姓名 SELECTcourse.cname,teacher.tname FROMcourse INNER JOIN teacher ON course.teacher_id = teacher.tid;#2、查詢學生表中男女生各有多少人 SELECTgender 性別,count(1) 人數 FROMstudent GROUP BYgender;#3、查詢物理成績等于100的學生的姓名 SELECTstudent.sname FROMstudent WHEREsid IN (SELECTstudent_idFROMscoreINNER JOIN course ON score.course_id = course.cidWHEREcourse.cname = '物理'AND score.num = 100);#4、查詢平均成績大于八十分的同學的姓名和平均成績 SELECTstudent.sname,t1.avg_num FROMstudent INNER JOIN (SELECTstudent_id,avg(num) AS avg_numFROMscoreGROUP BYstudent_idHAVINGavg(num) > 80 ) AS t1 ON student.sid = t1.student_id;#5、查詢所有學生的學號，姓名，選課數，總成績(注意：對于那些沒有選修任何課程的學生也算在內) SELECTstudent.sid,student.sname,t1.course_num,t1.total_num FROMstudent LEFT JOIN (SELECTstudent_id,COUNT(course_id) course_num,sum(num) total_numFROMscoreGROUP BYstudent_id ) AS t1 ON student.sid = t1.student_id;#6、 查詢姓李老師的個數 SELECTcount(tid) FROMteacher WHEREtname LIKE '李%';#7、 查詢沒有報李平老師課的學生姓名(找出報名李平老師課程的學生，然后取反就可以) SELECTstudent.sname FROMstudent WHEREsid NOT IN (SELECT DISTINCTstudent_idFROMscoreWHEREcourse_id IN (SELECTcourse.cidFROMcourseINNER JOIN teacher ON course.teacher_id = teacher.tidWHEREteacher.tname = '李平老師'));#8、 查詢物理課程比生物課程高的學生的學號(分別得到物理成績表與生物成績表，然后連表即可) SELECTt1.student_id FROM(SELECTstudent_id,numFROMscoreWHEREcourse_id = (SELECTcidFROMcourseWHEREcname = '物理')) AS t1 INNER JOIN (SELECTstudent_id,numFROMscoreWHEREcourse_id = (SELECTcidFROMcourseWHEREcname = '生物') ) AS t2 ON t1.student_id = t2.student_id WHEREt1.num > t2.num;#9、 查詢沒有同時選修物理課程和體育課程的學生姓名(沒有同時選修指的是選修了一門的，思路是得到物理+體育課程的學生信息表，然后基于學生分組，統計count(課程)=1) SELECTstudent.sname FROMstudent WHEREsid IN (SELECTstudent_idFROMscoreWHEREcourse_id IN (SELECTcidFROMcourseWHEREcname = '物理'OR cname = '體育')GROUP BYstudent_idHAVINGCOUNT(course_id) = 1);#10、查詢掛科超過兩門(包括兩門)的學生姓名和班級(求出<60的表，然后對學生進行分組，統計課程數目>=2) SELECTstudent.sname,class.caption FROMstudent INNER JOIN (SELECTstudent_idFROMscoreWHEREnum < 60GROUP BYstudent_idHAVINGcount(course_id) >= 2 ) AS t1 INNER JOIN class ON student.sid = t1.student_id AND student.class_id = class.cid;#11、查詢選修了所有課程的學生姓名(先從course表統計課程的總數，然后基于score表按照student_id分組，統計課程數據等于課程總數即可) SELECTstudent.sname FROMstudent WHEREsid IN (SELECTstudent_idFROMscoreGROUP BYstudent_idHAVINGCOUNT(course_id) = (SELECT count(cid) FROM course));#12、查詢李平老師教的課程的所有成績記錄 SELECT* FROMscore WHEREcourse_id IN (SELECTcidFROMcourseINNER JOIN teacher ON course.teacher_id = teacher.tidWHEREteacher.tname = '李平老師');#13、查詢全部學生都選修了的課程號和課程名(取所有學生數，然后基于score表的課程分組，找出count(student_id)等于學生數即可) SELECTcid,cname FROMcourse WHEREcid IN (SELECTcourse_idFROMscoreGROUP BYcourse_idHAVINGCOUNT(student_id) = (SELECTCOUNT(sid)FROMstudent));#14、查詢每門課程被選修的次數 SELECTcourse_id,COUNT(student_id) FROMscore GROUP BYcourse_id;#15、查詢之選修了一門課程的學生姓名和學號 SELECTsid,sname FROMstudent WHEREsid IN (SELECTstudent_idFROMscoreGROUP BYstudent_idHAVINGCOUNT(course_id) = 1);#16、查詢所有學生考出的成績并按從高到低排序（成績去重） SELECT DISTINCTnum FROMscore ORDER BYnum DESC;#17、查詢平均成績大于85的學生姓名和平均成績 SELECTsname,t1.avg_num FROMstudent INNER JOIN (SELECTstudent_id,avg(num) avg_numFROMscoreGROUP BYstudent_idHAVINGAVG(num) > 85 ) t1 ON student.sid = t1.student_id;#18、查詢生物成績不及格的學生姓名和對應生物分數 SELECTsname 姓名,num 生物成績 FROMscore LEFT JOIN course ON score.course_id = course.cid LEFT JOIN student ON score.student_id = student.sid WHEREcourse.cname = '生物' AND score.num < 60;#19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名 SELECTsname FROMstudent WHEREsid = (SELECTstudent_idFROMscoreWHEREcourse_id IN (SELECTcourse.cidFROMcourseINNER JOIN teacher ON course.teacher_id = teacher.tidWHEREteacher.tname = '李平老師')GROUP BYstudent_idORDER BYAVG(num) DESCLIMIT 1);#20、查詢每門課程成績最好的前兩名學生姓名 #查看每門課程按照分數排序的信息，為下列查找正確與否提供依據 SELECT* FROMscore ORDER BYcourse_id,num DESC;#表1：求出每門課程的課程course_id,與最高分數first_num SELECTcourse_id,max(num) first_num FROMscore GROUP BYcourse_id;#表2：去掉最高分，再按照課程分組，取得的最高分，就是第二高的分數second_num SELECTscore.course_id,max(num) second_num FROMscore INNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id ) AS t ON score.course_id = t.course_id WHEREscore.num < t.first_num GROUP BYcourse_id;#將表1和表2聯合到一起，得到一張表t3，包含課程course_id與該們課程的first_num與second_num SELECTt1.course_id,t1.first_num,t2.second_num FROM(SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t1 INNER JOIN (SELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id ) AS t2 ON t1.course_id = t2.course_id;#查詢前兩名的學生（有可能出現并列第一或者并列第二的情況） SELECTscore.student_id,t3.course_id,t3.first_num,t3.second_num FROMscore INNER JOIN (SELECTt1.course_id,t1.first_num,t2.second_numFROM(SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t1INNER JOIN (SELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id) AS t2 ON t1.course_id = t2.course_id ) AS t3 ON score.course_id = t3.course_id WHEREscore.num >= t3.second_num AND score.num <= t3.first_num;#排序后可以看的明顯點 SELECTscore.student_id,t3.course_id,t3.first_num,t3.second_num FROMscore INNER JOIN (SELECTt1.course_id,t1.first_num,t2.second_numFROM(SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t1INNER JOIN (SELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id) AS t2 ON t1.course_id = t2.course_id ) AS t3 ON score.course_id = t3.course_id WHEREscore.num >= t3.second_num AND score.num <= t3.first_num ORDER BYcourse_id;#可以用以下命令驗證上述查詢的正確性 SELECT* FROMscore ORDER BYcourse_id,num DESC;-- 21、查詢不同課程但成績相同的學號，課程號，成績 -- 22、查詢沒學過“葉平”老師課程的學生姓名以及選修的課程名稱； -- 23、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名； -- 24、任課最多的老師中學生單科成績最高的學生姓名View Code
小知識：外鍵約束條件
外鍵約束有三種約束模式（都是針對父表的約束）：模式一： district 嚴格約束（默認的 ），父表不能刪除或者更新已經被子表數據引用的記錄模式二：cascade 級聯模式：父表的操作，對應的子表關聯的數據也跟著操作 。模式三：set null：置空模式，父表操作之后，子表對應的數據（外鍵字段）也跟著被置空。通常的一個合理的約束模式是：刪除的時候子表置空；更新的時候子表級聯。指定模式的語法：foreign key(外鍵字段)references 父表(主鍵字段)on delete 模式 on update 模式;注意：刪除置空的前提條件是 外鍵字段允許為空，不然外鍵會創建失敗。外鍵雖然很強大，能夠進行各種約束，但是外鍵的約束降低了數據的可控性和可拓展性。通常在實際開發時，很少使用外鍵來約束。
?
轉載于:https://www.cnblogs.com/one-tom/p/11177105.html
總結
- 上一篇： 浅说——九讲背包之01背包
- 下一篇： 白芨多少钱啊？