MySQL练习题——课后一练

学习目标：

很久没有练习写sql了，现在根据题库练习几题，并以后随时看看，知道sql的一些常用语法，特殊技巧

---

学习内容：

以上面的表为例，练习sql题目和答案如下： CREATE TABLE class ( cid INT not null, caption VARCHAR(11) NOT null ); CREATE TABLE student( sid INT NOT NULL, sname VARCHAR(11) NOT null, gender char(1) DEFAULT '男' NOT NULL, class_id INT, PRIMARY KEY(sid) ); create table teacher( tid int not null, tname VARCHAR(11) not null, PRIMARY key(tid) ); CREATE TABLE course( cid INT NOT NULL, cname VARCHAR(11) NOT null, teacher_id int NOT NULL, PRIMARY KEY(cid) ); CREATE TABLE score( sid INT NOT NULL, student_id INT NOT null, course_id int NOT NULL, number INT not null, PRIMARY KEY(sid) ); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (1, 1, 1, 60); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (2, 1, 2, 59); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (3, 2, 1, 98); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (4, 2, 2, 100); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (5, 3, 1, 43); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (6, 3, 2, 99); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (7, 3, 3, 65); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (8, 4, 3, 46); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (9, 4, 4, 89); INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (10, 5, 4, 54); -- https://www.cnblogs.com/wupeiqi/articles/5729934.html -- 查询“1”课程比“2”课程成绩高的所有学生的学号； SELECT a.student_id FROM ( SELECT student_id, number FROM score WHERE course_id = 1 ) a, ( SELECT student_id, number FROM score WHERE course_id = 2 ) b WHERE a.student_id = b.student_id AND a.number < b.number; -- 查询平均成绩大于60分的同学的学号和平均成绩； SELECT student_id, avg(number) AS a FROM score GROUP BY student_id HAVING a > 60; -- 连表查询 select * FROM course c, teacher t WHERE c.teacher_id = t.tid; select * FROM course c LEFT JOIN teacher t on c.teacher_id = t.tid; -- 查询所有同学的学号、姓名、选课数、总成绩； SELECT a.student_id, a.countNum, a.sumNum, b.sname FROM ( SELECT student_id, count(course_id) AS countNum, SUM(number) AS sumNum FROM score GROUP BY student_id ) a LEFT JOIN student b ON a.student_id = b.sid; -- 查询姓“李”的老师的个数； select count(tname) FROM teacher WHERE tname like "李%"; -- 查询没学过“苍空”老师课的同学的学号、姓名； SELECT s.student_id, st.sname FROM score s, student st WHERE st.sid = s.student_id AND s.student_id NOT IN ( SELECT DISTINCT s.student_id FROM score s WHERE s.course_id IN ( SELECT c.cid FROM teacher t, course c WHERE t.tid = c.teacher_id AND tname = '苍空' ) ) GROUP BY s.student_id -- 查询学过编号“1”课程并且也学过编号“3”课程的同学的学号、姓名； SELECT a.sid, a.sname FROM student a INNER JOIN ( SELECT s.student_id FROM ( SELECT * FROM score WHERE course_id IN (1, 3) ) s GROUP BY s.student_id HAVING count(s.student_id) = 2 ) b ON b.student_id = a.sid; -- 查询学过“波多”老师所教的所有课的同学的学号、姓名； SELECT s.sid, s.sname FROM student s JOIN ( SELECT a.student_id FROM score a WHERE a.course_id IN ( SELECT c.cid FROM teacher t, course c WHERE c.teacher_id = t.tid AND t.tname = '波多' ) GROUP BY a.student_id HAVING COUNT(a.student_id) = ( SELECT count(c.cid) FROM teacher t, course c WHERE c.teacher_id = t.tid AND t.tname = '波多' ) ) c ON c.student_id = s.sid; -- 9、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名； SELECT st.sid, st.sname FROM ( SELECT a.student_id FROM ( SELECT s.student_id, s.number FROM score s WHERE s.course_id = 1 ) a, ( SELECT s.student_id, s.number FROM score s WHERE s.course_id = 2 ) b WHERE a.student_id = b.student_id AND a.number > b.number ) b, student st WHERE st.sid = b.student_id; -- 查询有课程成绩小于60分的同学的学号、姓名； SELECT st.sid, st.sname FROM ( SELECT sc.student_id FROM score sc GROUP BY sc.student_id HAVING MIN(sc.number) < 60 ) scc, student st WHERE st.sid = scc.student_id; -- 查询没有学全所有课的同学的学号、姓名； SELECT st.sid, st.sname FROM ( SELECT sc.student_id FROM score sc GROUP BY sc.student_id HAVING count(sc.student_id) = ( SELECT count(cid) FROM course ) ) a, student st WHERE st.sid = a.student_id; -- 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名； SELECT st.sid, st.sname FROM student st, ( SELECT DISTINCT s.student_id FROM score s WHERE s.course_id IN ( SELECT sc.course_id FROM score sc WHERE sc.student_id = 1 ) ) a WHERE st.sid = a.student_id; -- 查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名； SELECT st.sid, st.sname FROM student st, ( SELECT DISTINCT sc2.student_id FROM score sc2 WHERE sc2.student_id != 1 AND sc2.course_id IN ( SELECT sc.course_id FROM score sc WHERE sc.student_id = 1 ) ) sc3 WHERE sc3.student_id = st.sid -- 查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名； SELECT st4.sid, st4.sname FROM student st4, ( SELECT sc2.student_id FROM score sc2 WHERE sc2.course_id IN ( SELECT sc.course_id FROM score sc WHERE sc.student_id = 2 ) AND sc2.student_id != 2 GROUP BY sc2.student_id HAVING count(sc2.student_id) = ( SELECT count(sc.course_id) FROM score sc WHERE sc.student_id = 2 ) ) sc5 WHERE st4.sid = sc5.student_id; -- 15、删除学习“苍空”老师课的SC表记录 DELETE FROM score WHERE course_id IN ( SELECT co.cid FROM teacher te, course co WHERE te.tid = co.teacher_id AND te.tname = '苍空' ); -- 16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩； INSERT INTO score (student_id, course_id, number) SELECT a.student_id, 2, b.avgNum FROM ( SELECT DISTINCT sc4.student_id FROM score sc4 WHERE sc4.student_id NOT IN ( SELECT sc3.student_id FROM score sc3 WHERE sc3.course_id = 2 ) ) a, ( SELECT avg(number) AS avgNum FROM ( SELECT * FROM score sc WHERE sc.course_id = 2 ) sc2 GROUP BY sc2.course_id ) b -- 按平均成绩从低到高显示所有学生的“生物”、“体育”、“物理”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分； SELECT sc.student_id , SUM(IF(`course_id` = 1, number, NULL)) AS 生物 , SUM(IF(`course_id` = 2, number, NULL)) AS 体育 , SUM(IF(`course_id` = 3, number, NULL)) AS 物理 , COUNT(course_id) AS 有效课程 , avg(number) AS 平均分 FROM ( SELECT * FROM score WHERE course_id IN (1, 2, 3) ) sc GROUP BY sc.student_id SELECT student_id, SUM(CASE `course_id` WHEN (SELECT cid FROM course WHERE cname = "生物") THEN number ELSE null END) as '生物', SUM(CASE `course_id` WHEN (SELECT cid FROM course WHERE cname = "体育") THEN number ELSE null END) as '体育', SUM(CASE `course_id` WHEN (SELECT cid FROM course WHERE cname = "物理") THEN number ELSE null END) as '物理' FROM score GROUP BY student_id -- 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； SELECT sc.course_id AS 课程ID, MAX(sc.number) AS 最高分, MIN(sc.number) AS 最低分 FROM score sc GROUP BY sc.course_id -- 按各科平均成绩从低到高和及格率的百分数从高到低顺序； SELECT sc.course_id, avg(sc.number) AS avgNum , SUM(IF(number >= 60, 1, 0)) / count(1) AS pass FROM score sc GROUP BY sc.course_id ORDER BY avgNum ASC, pass DESC select sc.course_id,SUM( IF(number>=60,1,0) ) ,SUM( IF(number<60,1,0) ),count(1) FROM score sc GROUP BY sc.course_id; -- 课程平均分从高到低显示（现实任课老师）； SELECT a.course_id, a.avgNum, t.tname FROM ( SELECT sc.course_id, AVG(sc.number) AS avgNum FROM score sc GROUP BY sc.course_id ORDER BY avgNum DESC ) a, course c, teacher t WHERE a.course_id = c.cid AND c.teacher_id = t.tid; -- 查询各科成绩前三名的记录:(不考虑成绩并列情况) SELECT s1.* FROM score s1 WHERE ( SELECT COUNT(1) FROM score s2 WHERE s1.course_id=s2.course_id AND s1.number<= s2.number )<=3 ORDER BY s1.course_id,s1.number DESC; SELECT a.student_id , a.course_id ,a.number FROM ( SELECT s1.student_id, s1.course_id, s1.number, s2.course_id AS course_id2 , s2.number AS number2 FROM score s1, score s2 WHERE s1.course_id = s2.course_id AND s1.number <= s2.number ) a GROUP BY a.course_id ,a.number HAVING count(1) <=3 ORDER BY a.course_id, a.number DESC -- 查询每门课程被选修的学生数； SELECT count(sc.student_id) FROM score as sc GROUP BY sc.course_id; -- 查询出只选修了一门课程的全部学生的学号和姓名； SELECT s.sid, s.sname FROM student s, ( SELECT sc.student_id, count(sc.student_id) AS countNum FROM score sc GROUP BY sc.student_id HAVING countNum = 1 ) a WHERE s.sid = a.student_id -- 查询男生、女生的人数； SELECT st.gender, COUNT(1) FROM student st GROUP BY st.gender; -- 询姓“陈”的学生名单 SELECT * FROM student WHERE sname like "陈%"; -- 查询同名同姓学生名单，并统计同名人数； SELECT st1.sid, st1.sname, st1.gender, count(1) AS countNum FROM student st1, student st2 WHERE st1.sname = st2.sname GROUP BY st1.sid HAVING countNum >= 2 -- 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列； SELECT sc.course_id, avg(sc.number) AS avgNum FROM score sc GROUP BY sc.course_id ORDER BY avgNum ASC, sc.course_id DESC -- 查询平均成绩大于60的所有学生的学号、姓名和平均成绩； SELECT a.student_id, st.sname, a.avgNum FROM student st, ( SELECT sc.student_id, avg(sc.number) AS avgNum FROM score sc GROUP BY sc.student_id HAVING avgNum > 60 ) a WHERE st.sid = a.student_id; -- 查询课程名称为“生物”，且分数低于60的学生姓名和分数； SELECT st.sname, sc.student_id, sc.number FROM score sc, course co, student st WHERE co.cname = '生物' AND sc.course_id = co.cid AND st.sid = sc.student_id AND sc.number < 60 -- 查询课程编号为001且课程成绩在80分以上的学生的学号和姓名； SELECT sc.student_id,st.sname FROM score sc,student st WHERE st.sid = sc.student_id and sc.course_id = 1 and sc.number>=80 -- 求选了课程的学生人数 SELECT count(DISTINCT(student_id)) FROM score -- 查询选修“苍空”老师所授课程的学生中，成绩最高的学生姓名及其成绩； SELECT st.sname, sc.student_id, sc.course_id, sc.number FROM score sc, student st WHERE st.sid = sc.student_id AND sc.course_id IN ( SELECT co.cid FROM teacher te, course co WHERE te.tid = co.teacher_id AND te.tname = '苍空' ) ORDER BY sc.number DESC LIMIT 0, 1 -- 33、查询各个课程及相应的选修人数； SELECT sc.course_id ,count(1) countNum FROM score sc GROUP BY sc.course_id -- 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩； -- 查询每门课程成绩最好的前两名； SELECT s1.* FROM score s1 WHERE ( SELECT COUNT(1) FROM score s2 WHERE s1.course_id=s2.course_id AND s1.number<= s2.number )<=3 ORDER BY s1.course_id,s1.number DESC; SELECT a.student_id , a.course_id ,a.number FROM ( SELECT s1.student_id, s1.course_id, s1.number, s2.course_id AS course_id2 , s2.number AS number2 FROM score s1, score s2 WHERE s1.course_id = s2.course_id AND s1.number <= s2.number ) a GROUP BY a.course_id ,a.number HAVING count(1) <=2 ORDER BY a.course_id, a.number DESC -- 检索至少选修两门课程的学生学号 SELECT student_id FROM score GROUP BY student_id HAVING COUNT(student_id)>=2 -- 查询全部学生都选修的课程的课程号和课程名； SELECT sc.course_id, count(sc.student_id) AS countNum FROM score sc GROUP BY sc.course_id HAVING countNum = ( SELECT COUNT(1) FROM student ) -- 查询没学过“苍空”老师讲授的任一门课程的学生姓名； SELECT * FROM student st WHERE st.sid NOT IN ( SELECT DISTINCT sc.student_id FROM score sc WHERE sc.course_id IN ( SELECT c.cid FROM teacher t, course c WHERE t.tid = c.teacher_id AND t.tname = '苍空' ) ) -- 查询两门以上不及格课程的同学的学号及其平均成绩 SELECT sc.student_id , SUM(IF(number < 60, 1, 0)) AS fail , avg(number) AS avgNum FROM score sc GROUP BY sc.student_id HAVING fail >= 2; -- 检索“1”课程分数小于60，按分数降序排列的同学学号； SELECT sc.student_id FROM score sc WHERE sc.course_id = 1 AND sc.number < 60 ORDER BY sc.number DESC -- 删除“8”同学的“1”课程的成绩； DELETE FROM score WHERE student_id = 8 and course_id = 1;

这些sql，时不时的练练手，还是有用的。

【完】

正在前往BAT的路上修行