建立如下表:
建表语句:
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、任课最多的老师中学生单科成绩最高的学生姓名
答案:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
#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、任课最多的老师中学生单科成绩最高的学生姓名
小知识:外键约束条件
外键约束有三种约束模式(都是针对父表的约束):模式一: district 严格约束(默认的 ),父表不能删除或者更新已经被子表数据引用的记录模式二:cascade 级联模式:父表的操作,对应的子表关联的数据也跟着操作 。模式三:set null:置空模式,父表操作之后,子表对应的数据(外键字段)也跟着被置空。通常的一个合理的约束模式是:删除的时候子表置空;更新的时候子表级联。指定模式的语法:foreign key(外键字段)references 父表(主键字段)on delete 模式 on update 模式;注意:删除置空的前提条件是 外键字段允许为空,不然外键会创建失败。外键虽然很强大,能够进行各种约束,但是外键的约束降低了数据的可控性和可拓展性。通常在实际开发时,很少使用外键来约束。