班级表 class
学生表student
老师表 teacher
课程表course
成绩表 score
准备数据
创建数据库
create database tang_test charset='utf8';
创建表
CREATE TABLE class ( cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, caption VARCHAR(20) ) DEFAULT CHARSET = 'utf8'; CREATE TABLE student ( sid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, sname VARCHAR(20), gender VARCHAR(20), class_id INT, CONSTRAINT fk_clsid FOREIGN KEY (class_id) REFERENCES class (cid) ) DEFAULT CHARSET = 'utf8'; CREATE TABLE teacher ( tid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tname VARCHAR(32) ) DEFAULT CHARSET = 'utf8'; CREATE TABLE course ( cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, cname VARCHAR(20), tearch_id INT, CONSTRAINT fk_tea FOREIGN KEY (tearch_id) REFERENCES teacher (tid) ) DEFAULT CHARSET = 'utf8'; CREATE TABLE score ( sid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, student_id INT, corse_id INT, number INT, CONSTRAINT fk_sco_stu FOREIGN KEY (student_id) REFERENCES student (sid), CONSTRAINT fk_sco_cor FOREIGN KEY (corse_id) REFERENCES course (cid) ) DEFAULT CHARSET = 'utf8';
添加联合唯一约束
ALTER TABLE score ADD UNIQUE i_stu_cor(student_id, corse_id);
题目与答案
# 查询“生物”课程比“物理”课程成绩高的所有学生的学号； SELECT tb1.student_id FROM (SELECT student_id, number FROM score s LEFT JOIN course c ON s.corse_id = c.cid WHERE c.cname = '体育') AS tb1 LEFT JOIN ( SELECT student_id, number FROM score s LEFT JOIN course c ON s.corse_id = c.cid WHERE c.cname = '物理') AS tb2 ON tb1.student_id = tb2.student_id WHERE tb1.number > tb2.number; # 查询平均成绩大于60分的同学的学号和平均成绩； SELECT s.sid, avg(number) FROM score LEFT JOIN student s ON score.student_id = s.sid GROUP BY s.sid HAVING avg(number) > 60; # 查询所有同学的学号、姓名、选课数、总成绩； SELECT student.sid, student.sname, count(s.corse_id), sum(s.number) FROM student LEFT JOIN score s ON student.sid = s.student_id GROUP BY student.sid; # 查询姓“李”的老师的个数； SELECT count(tid) FROM teacher WHERE tname LIKE "李%"; # 查询没学过“叶平”老师课的同学的学号、姓名； SELECT sid, sname FROM student WHERE sid NOT IN ( SELECT DISTINCT student_id FROM score WHERE corse_id IN ( SELECT course.cid FROM course LEFT JOIN teacher t ON course.tearch_id = t.tid WHERE t.tname = "叶平" )); # 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； SELECT tb2.sid, tb2.sname FROM (SELECT student_id, corse_id FROM score WHERE corse_id = 2 OR corse_id = 3) AS tb1 LEFT JOIN student tb2 ON tb2.sid = tb1.student_id GROUP BY student_id HAVING count(student_id) > 1; # 查询学过“叶平”老师所教的所有课的同学的学号、姓名； SELECT student.sid, student.sname FROM student WHERE sid IN ( SELECT DISTINCT student_id FROM score WHERE corse_id IN ( SELECT course.cid FROM course LEFT JOIN teacher t ON course.tearch_id = t.tid WHERE t.tname = '苍空') ); # 9.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； # 跟第一题差不多 # 查询有课程成绩小于60分的同学的学号、姓名； SELECT student.sid, student.sname FROM student WHERE sid IN ( SELECT DISTINCT student_id FROM score WHERE number < 60 ); # 查询没有学全所有课的同学的学号、姓名； SELECT sid, sname FROM student WHERE sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING count(number) = ( SELECT COUNT(1) FROM course)); SELECT s.sid, s.sname FROM score LEFT JOIN student s ON score.student_id = s.sid GROUP BY score.student_id HAVING count(number) = (SELECT count(1) FROM course); # 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名； # 1 先找到001同学所学的所有课程id # 2.条件学号不为1 以及所学课程in 1号学生所学的id里面 SELECT s.sid, s.sname FROM score LEFT JOIN student s ON score.student_id = s.sid WHERE student_id != 1 AND score.corse_id IN ( SELECT corse_id FROM score WHERE student_id = 1) GROUP BY student_id; # 查询至少学过学号为“002”同学所有课的其他同学学号和姓名； SELECT student_id, sname, count(score.corse_id) FROM score LEFT JOIN student s ON score.student_id = s.sid WHERE score.student_id != 2 AND score.corse_id IN ( SELECT corse_id FROM score WHERE student_id = 2) GROUP BY student_id HAVING count(corse_id) = (SELECT count(corse_id) FROM score WHERE student_id = 2); SELECT student_id, sname, count(corse_id) FROM score LEFT JOIN student ON score.student_id = student.sid WHERE student_id != 1 AND corse_id IN (SELECT corse_id FROM score WHERE student_id = 1) GROUP BY student_id HAVING count(corse_id) = (SELECT count(corse_id) FROM score WHERE student_id = 1); # 查询和“001”号的同学学习的课程完全相同的其他同学学号和姓名； # 总个数相等 但 不一定所学的就等于1号所学的 # 2号所学的课程都被学到 但验证不了总个数相等 SELECT student_id, sname FROM score LEFT JOIN student ON score.student_id = student.sid # 总的数量=1号总的数量 WHERE student_id IN (SELECT student_id FROM score WHERE student_id != 2 GROUP BY student_id HAVING count(corse_id) = (SELECT count(1) FROM score WHERE student_id = 2)) AND corse_id IN ( # 1号所学的课程数量都已被学到 SELECT corse_id FROM score WHERE corse_id IN (SELECT corse_id FROM score WHERE student_id = 2) GROUP BY student_id HAVING count(corse_id) = (SELECT count(*) FROM score WHERE student_id = 2) ); # 15、删除学习“叶平”老师课的score表记录； DELETE FROM score WHERE score.corse_id IN (SELECT cid FROM course LEFT JOIN teacher t ON course.tearch_id = t.tid WHERE t.tname = '叶平'); # 向SC表中插入一些记录，这些记录要求符合以下条件： # ①没有上过编号“002”课程的同学学号； # ②插入“002”号课程的平均成绩； INSERT INTO score (student_id, corse_id, number) SELECT sid, 2, (SELECT avg(number) FROM score WHERE corse_id = 2) FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE corse_id = 2 ); # 17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分； SELECT student_id, (SELECT number FROM score LEFT JOIN course c ON score.corse_id = c.cid WHERE c.cname = '生物' AND score.student_id = sc.student_id) AS "生物", (SELECT number FROM score LEFT JOIN course c ON score.corse_id = c.cid WHERE c.cname = '物理' AND score.student_id = sc.student_id) AS "物理", (SELECT number FROM score LEFT JOIN course c ON score.corse_id = c.cid WHERE c.cname = '体育' AND score.student_id = sc.student_id) AS "体育", count(sc.corse_id), avg(number) FROM score AS sc WHERE sc.corse_id IN (SELECT cid FROM course WHERE course.cname = '生物' OR course.cname = '物理' OR course.cname = '体育') GROUP BY sc.student_id ORDER BY avg(number); # 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； SELECT corse_id, c.cname, max(number), min(number) FROM score LEFT JOIN course c ON score.corse_id = c.cid GROUP BY corse_id; # 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序； # 思路：case when .. then SELECT corse_id, avg(number) AS avgnum, sum(CASE WHEN score.number > 60 THEN 1 ELSE 0 END) / count(1) * 100 AS percent FROM score GROUP BY corse_id ORDER BY avgnum ASC, percent DESC; # 20、课程平均分从高到低显示（显示任课老师） SELECT avg(number), t.tname FROM score LEFT JOIN course c ON score.corse_id = c.cid LEFT JOIN teacher t ON c.tearch_id = t.tid GROUP BY corse_id ORDER BY avg(number) DESC; # 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) # 思路 先找到第一名和第四名的值 SELECT score.sid, score.corse_id, score.number, T.first_num, T.second_num FROM score LEFT JOIN (SELECT sid, (SELECT number FROM score AS s2 WHERE s2.corse_id = s1.corse_id ORDER BY number DESC LIMIT 0, 1) AS first_num, (SELECT number FROM score AS s2 WHERE s2.corse_id = s1.corse_id ORDER BY number DESC LIMIT 3, 1) AS second_num, FROM score AS s1) AS T ON score.sid = T.sid WHERE score.number <= T.first_num AND score.number >= T.second_num; # 22、查询每门课程被选修的学生数； SELECT corse_id, count(1) FROM score GROUP BY corse_id; # 23、查询出只选修了一门课程的全部学生的学号和姓名； SELECT s.sid, s.sname, count(1) FROM score LEFT JOIN student s ON score.student_id = s.sid GROUP BY student_id HAVING count(1) = 1; # 24、查询男生、女生的人数； # 男生总数为一张表 女生总数为一张表，每张表里面都只有一个字段 # 查询两张表 SELECT * FROM (SELECT count(1) AS man FROM student WHERE student.gender = '男') AS A, (SELECT count(1) AS wuman FROM student WHERE student.gender = '女') AS B # 25、查询姓“张”的学生名单； SELECT * FROM student WHERE sname LIKE '张%'; # 26、查询同名同姓学生名单，并统计同名人数； SELECT sname, count(1) FROM student GROUP BY sname; # 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列； SELECT corse_id, avg(if(isnull(number), 0, number)) AS avg FROM score GROUP BY corse_id ORDER BY avg ASC, corse_id DESC # 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩； SELECT s.sid, s.sname, avg(if(isnull(number), 0, number)) AS avg FROM score LEFT JOIN student s ON score.student_id = s.sid GROUP BY student_id HAVING avg > 85; # 29、查询课程名称为“数学”，且分数低于60的学生姓名和分数； SELECT s.sid, s.sname, score.number FROM score LEFT JOIN student s ON score.student_id = s.sid LEFT JOIN course c ON score.corse_id = c.cid WHERE c.cname = '数学' AND number < 60; # 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； SELECT s.sid, s.sname, score.number, score.corse_id FROM score LEFT JOIN student s ON score.student_id = s.sid WHERE corse_id = '3' AND number > 80; # 31、求选了课程的学生人数 # 第一种做法 SELECT count(DISTINCT student_id) FROM score; # 第二种做法 SELECT count(c) FROM ( SELECT count(student_id) AS c FROM score GROUP BY student_id) AS A; # 查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩； # 思路 先找到杨艳所教的课程id，然后再根据课程id分组 排序取第一个 SELECT corse_id, s.sname, score.number FROM score LEFT JOIN student s ON score.student_id = s.sid WHERE score.corse_id IN (SELECT course.cid FROM course LEFT JOIN teacher t ON course.tearch_id = t.tid WHERE t.tname = '波多') ORDER BY number DESC LIMIT 1; # 33、查询各个课程及相应的选修人数； SELECT corse_id, count(1), c.cname FROM score LEFT JOIN course c ON score.corse_id = c.cid GROUP BY corse_id; # 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩； # 同一张表进行链表操作 SELECT DISTINCT s1.corse_id, s2.corse_id, s1.number, s2.number, s1.student_id, s2.student_id FROM score AS s1, score AS s2 WHERE s1.corse_id != s2.corse_id AND s1.number = s2.number # 35、查询每门课程成绩最好的前两名； # 思路 先找到第一名跟第二名同学的成绩 组成一张新的表 SELECT score.sid, corse_id, score.number FROM score LEFT JOIN (SELECT sid, (SELECT number FROM score AS s2 WHERE s2.corse_id = s1.corse_id ORDER BY number DESC LIMIT 1 OFFSET 0) AS first_num, (SELECT number FROM score AS s2 WHERE s2.corse_id = s1.corse_id ORDER BY number DESC LIMIT 1 OFFSET 1) AS second_num FROM score AS s1) AS T ON score.sid = T.sid WHERE score.number <= T.first_num AND score.number >= T.second_num ORDER BY score.corse_id DESC, score.number DESC; # 36、检索至少选修两门课程的学生学号； SELECT score.sid FROM score GROUP BY student_id HAVING count(student_id) > 1; # 37、查询全部学生都选修的课程的课程号和课程名； SELECT cid, course.cname FROM course WHERE course.cid IN (SELECT corse_id FROM score GROUP BY corse_id HAVING count(1) = (SELECT count(1) FROM student)); # 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名； # 先找到叶平老师所教的课程id # 然后找到有学过任意一门是属于叶平老师的课的学生id # 然后学生不在里面 SELECT student.sname FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE score.corse_id IN ( SELECT cid FROM course LEFT JOIN teacher ON course.tearch_id = teacher.tid WHERE tname = '苍空' ) ); # 错误的做法 # select student_id,student.sname from score # left join student on score.student_id = student.sid # where score.corse_id not in ( # select cid from course left join teacher on course.tearch_id = teacher.tid where tname = '张磊老师' # ) # group by student_id # 39、查询两门以上不及格课程的同学的学号及其平均成绩； SELECT student_id,count(1) FROM score WHERE number < 60 GROUP BY student_id HAVING count(1) > 2; # 40、检索“004”课程分数小于60，按分数降序排列的同学学号； SELECT student_id,number FROM score WHERE number < 60 and corse_id = 4 ORDER BY number DESC; # 41、删除“002”同学的“001”课程的成绩； # SELECT * FROM score WHERE student_id = 2 and corse_id = 1; DELETE FROM score WHERE student_id = 2 and corse_id = 1;