https://www.cnblogs.com/zsty/p/10109125.html
http://www.manongjc.com/article/108264.html
DROP DATABASE IF EXISTS anan50; CREATE DATABASE anan50; USE anan50; -- 建表 -- 学生表 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) ); -- 课程表 CREATE TABLE `Course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) ); -- 教师表 CREATE TABLE `Teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) ); -- 成绩表 CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) ); -- 插入学生表测试数据 INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男'); INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男'); INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男'); INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男'); INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女'); INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女'); INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女'); INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女'); -- 课程表测试数据 INSERT INTO Course VALUES('01' , '语文' , '02'); INSERT INTO Course VALUES('02' , '数学' , '01'); INSERT INTO Course VALUES('03' , '英语' , '03'); -- 教师表测试数据 INSERT INTO Teacher VALUES('01' , '张三'); INSERT INTO Teacher VALUES('02' , '李四'); INSERT INTO Teacher VALUES('03' , '王五'); -- 成绩表测试数据 INSERT INTO Score VALUES('01' , '01' , 80); INSERT INTO Score VALUES('01' , '02' , 90); INSERT INTO Score VALUES('01' , '03' , 99); INSERT INTO Score VALUES('02' , '01' , 70); INSERT INTO Score VALUES('02' , '02' , 60); INSERT INTO Score VALUES('02' , '03' , 80); INSERT INTO Score VALUES('03' , '01' , 80); INSERT INTO Score VALUES('03' , '02' , 80); INSERT INTO Score VALUES('03' , '03' , 80); INSERT INTO Score VALUES('04' , '01' , 50); INSERT INTO Score VALUES('04' , '02' , 30); INSERT INTO Score VALUES('04' , '03' , 20); INSERT INTO Score VALUES('05' , '01' , 76); INSERT INTO Score VALUES('05' , '02' , 87); INSERT INTO Score VALUES('06' , '01' , 31); INSERT INTO Score VALUES('06' , '03' , 34); INSERT INTO Score VALUES('07' , '02' , 89); INSERT INTO Score VALUES('07' , '03' , 98); -- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 /* */ -- anan ---------------- -- (1) SELECT t1.`s_id` , t1.`s_score` score_01, t2.`s_score` score_02 FROM Score t1, Score t2 WHERE t1.`s_id` = t2.`s_id` AND t1.`c_id` = 01 AND t2.`c_id` = 02 AND t1.`s_score` > t2.`s_score`; -- (2) SELECT * FROM Student; -- (3) SELECT t4.*, t3.score_01, t3.score_02 FROM Student t4, (SELECT t1.`s_id` t1_id , t1.`s_score` score_01, t2.`s_score` score_02 FROM Score t1, Score t2 WHERE t1.`s_id` = t2.`s_id` AND t1.`c_id` = 01 AND t2.`c_id` = 02 AND t1.`s_score` > t2.`s_score`) t3 WHERE t4.`s_id` = t3.t1_id; -- 参考 ---------------- SELECT st.*,sc.s_score AS '语文' ,sc2.s_score '数学' FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01' LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02' OR sc2.`c_id` = NULL WHERE sc.s_score>sc2.s_score; -- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 SELECT st.*,sc.s_score AS '语文' ,sc2.s_score '数学' FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01' OR sc.c_id = NULL LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02' WHERE sc.s_score<sc2.s_score; -- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 -- anan ---------------- SELECT s_id, COUNT(c_id) FROM Score GROUP BY s_id; SELECT s_id, COUNT(c_id), AVG(s_score) av FROM Score GROUP BY s_id; SELECT s_id, COUNT(c_id), AVG(s_score) av FROM Score GROUP BY s_id HAVING av >= 60; SELECT st.s_id, st.`s_name`, t_av.av FROM Student st, (SELECT s_id, COUNT(c_id), AVG(s_score) av FROM Score GROUP BY s_id HAVING av >= 60) t_av WHERE st.`s_id` = t_av.s_id; -- 参考 ---------------- -- ROUND 函数用于把数值字段舍入为指定的小数位数。 SELECT st.s_id,st.s_name,ROUND(AVG(sc.s_score),2) cjScore FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id GROUP BY st.s_id HAVING AVG(sc.s_score)>=60; -- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- 参考 ---------------- SELECT DISTINCT s_id FROM score; -- SELECT a.s_id,a.s_name,0 AS avg_score FROM student a WHERE a.s_id NOT IN ( SELECT DISTINCT s_id FROM score); -- SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score FROM student b LEFT JOIN score a ON b.s_id = a.s_id GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)<60; -- SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score FROM student b LEFT JOIN score a ON b.s_id = a.s_id GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)<60 UNION SELECT a.s_id,a.s_name,0 AS avg_score FROM student a WHERE a.s_id NOT IN ( SELECT DISTINCT s_id FROM score); -- 参考 ---------------- SELECT st.s_id,st.s_name,(CASE WHEN ROUND(AVG(sc.s_score),2) IS NULL THEN 0 ELSE ROUND(AVG(sc.s_score)) END ) cjScore FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id GROUP BY st.s_id HAVING AVG(sc.s_score)<60 OR AVG(sc.s_score) IS NULL; -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 -- anan ---------------- SELECT * FROM Student st JOIN Score sc ON st.s_id = sc.`s_id` ; SELECT st.`s_id`, st.`s_name` FROM Student st JOIN Score sc ON st.s_id = sc.`s_id`; SELECT st.`s_id`, st.`s_name` FROM Student st JOIN Score sc ON st.s_id = sc.`s_id` GROUP BY sc.s_id; SELECT st.`s_id`, st.`s_name`, COUNT(sc.c_id) '选课总数', SUM(sc.s_score) '总成绩' FROM Student st JOIN Score sc ON st.s_id = sc.`s_id` GROUP BY sc.s_id; SELECT st.`s_id`, st.`s_name`, COUNT(sc.c_id) '选课总数', SUM(sc.s_score) '总成绩' FROM Student st LEFT JOIN Score sc ON st.s_id = sc.`s_id` GROUP BY sc.s_id; SELECT st.`s_id`, st.`s_name`, COUNT(sc.c_id) '选课总数', IFNULL(SUM(sc.s_score),0) '总成绩' FROM Student st LEFT JOIN Score sc ON st.s_id = sc.`s_id` GROUP BY sc.s_id; -- 6、查询"李"姓老师的数量 -- anan ---------------- SELECT * FROM Teacher WHERE t_name LIKE '李%'; SELECT COUNT(t_id) FROM Teacher WHERE t_name LIKE '李%'; -- 7、查询学过"张三"老师授课的同学的信息 -- anan ---------------- SELECT t1.* FROM Student t1, Course t2, Teacher t3, Score t4 WHERE t3.`t_name`='张三' AND t2.`t_id`=t3.`t_id` AND t2.`c_id`=t4.`c_id` AND t1.`s_id`=t4.`s_id`; -- 参考 ---------------- SELECT st.* FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id LEFT JOIN course c ON c.c_id=sc.c_id LEFT JOIN teacher t ON t.t_id=c.t_id WHERE t.t_name="张三"; -- 8、查询没学过"张三"老师授课的同学的信息 -- anan ---------------- SELECT * FROM Student WHERE s_id NOT IN (SELECT t1.s_id FROM Student t1, Course t2, Teacher t3, Score t4 WHERE t3.`t_name` = '张三' AND t2.`t_id`=t3.`t_id` AND t2.`c_id`=t4.`c_id` AND t1.`s_id`=t4.`s_id`); -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 -- anan ---------------- SELECT st.* FROM Student st LEFT JOIN Score sc1 ON st.`s_id` = sc1.`s_id` LEFT JOIN Score sc2 ON st.`s_id` = sc2.`s_id` WHERE sc1.`c_id` = '01' AND sc2.`c_id` = '02'; -- 参考 ---------------- SELECT st.* FROM Student st, Score sc1, Score sc2 WHERE st.`s_id` = sc1.`s_id` AND st.`s_id` = sc2.`s_id` AND sc1.`c_id` = '01' AND sc2.`c_id` = '02'; -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 -- anan ---------------- SELECT * FROM Student st WHERE st.`s_id` IN (SELECT st.s_id FROM Student st, Score sc1 WHERE st.`s_id` = sc1.`s_id` AND sc1.`c_id` = '01' ) AND st.`s_id` NOT IN (SELECT st.s_id FROM Student st, Score sc1 WHERE st.`s_id` = sc1.`s_id` AND sc1.`c_id` = '02'); -- 参考 ---------------- SELECT * FROM Student st WHERE st.`s_id` IN (SELECT sc1.s_id FROM Score sc1 WHERE sc1.`c_id` = '01' ) AND st.`s_id` NOT IN (SELECT sc1.s_id FROM Score sc1 WHERE sc1.`c_id` = '02' ); -- 11、查询没有学全所有课程的同学的信息 -- anan ---------------- SELECT * FROM Student st WHERE st.`s_id` NOT IN (SELECT sc1.s_id FROM Score sc1, Score sc2, Score sc3 WHERE sc1.`s_id` = sc2.`s_id` AND sc2.`s_id` = sc3.`s_id` AND sc1.`c_id` = '01' AND sc2.`c_id` = '02' AND sc3.`c_id` = '03'); -- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 -- anan ---------------- SELECT * FROM Student st WHERE st.`s_id` IN (SELECT sc2.s_id FROM Score sc2 WHERE sc2.`s_id` != '01' AND sc2.`c_id` IN (SELECT sc.c_id FROM Score sc WHERE sc.`s_id` = '01')); -- 参考 ---------------- SELECT * FROM student WHERE s_id IN( SELECT DISTINCT a.s_id FROM score a WHERE a.c_id IN(SELECT a.c_id FROM score a WHERE a.s_id='01') ); -- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 -- anan 参考了一下别人的---------------- SELECT st.* FROM Student st WHERE st.`s_id` IN (SELECT sc.`s_id` FROM Score sc WHERE sc.`c_id` IN (SELECT sc1.`c_id` FROM Score sc1 WHERE sc1.`s_id`='01') GROUP BY sc.s_id HAVING COUNT(sc.`c_id`) = (SELECT COUNT(sc1.`c_id`) FROM Score sc1 WHERE sc1.`s_id`='01')); -- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 -- anan ---------------- SELECT t1.`s_name` FROM Student t1 WHERE t1.`s_id` NOT IN (SELECT t4.`s_id` FROM Score t4 WHERE t4.`c_id` IN (SELECT t2.`c_id` FROM Course t2 WHERE t2.`t_id` IN (SELECT t3.`t_id` FROM Teacher t3 WHERE t3.`t_name` = '张三'))); -- 参考 ---------------- SELECT a.s_name FROM student a WHERE a.s_id NOT IN ( SELECT s_id FROM score WHERE c_id = (SELECT c_id FROM course WHERE t_id =( SELECT t_id FROM teacher WHERE t_name = '张三')) GROUP BY s_id); -- 参考 ---------------- SELECT st.s_name FROM student st WHERE st.s_id NOT IN ( SELECT sc.s_id FROM score sc INNER JOIN course c ON c.c_id=sc.c_id INNER JOIN teacher t ON t.t_id=c.t_id AND t.t_name="张三" ); -- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 -- anan ---------------- SELECT st.`s_id`, st.`s_name`, ROUND(AVG(sc.`s_score`)) avg_score FROM Score sc JOIN Student st ON st.`s_id` = sc.`s_id` WHERE sc.`s_score` < 60 GROUP BY sc.`s_id` HAVING COUNT(1) >= 2; -- 16、检索"01"课程分数小于60，按分数降序排列的学生信息 -- anan 没有显示出来分数---------------- SELECT st.* FROM Student st WHERE st.`s_id` IN (SELECT sc.`s_id` FROM Score sc WHERE sc.`c_id` = '01' AND sc.`s_score` < 60 ORDER BY sc.`s_score` DESC); -- anan 显示出了分数---------------- SELECT st.*, t1.score FROM Student st, ((SELECT sc.`s_id` id, sc.`s_score` score FROM Score sc WHERE sc.`c_id` = '01' AND sc.`s_score` < 60 ORDER BY sc.`s_score` DESC)) t1 WHERE st.`s_id` = t1.id; -- 参考 ---------------- SELECT st.*,sc.s_score FROM student st INNER JOIN score sc ON sc.s_id=st.s_id AND sc.c_id="01" AND sc.s_score<60 ORDER BY sc.s_score DESC; -- 参考 ---------------- SELECT a.*,b.c_id,b.s_score FROM student a,score b WHERE a.s_id = b.s_id AND b.c_id='01' AND b.s_score<60 ORDER BY b.s_score DESC; -- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 -- 可加round,case when then else end 使显示更完美 -- anan ---------------- SELECT sc.`s_id`, AVG(sc.`s_score`) avg_score FROM Score sc GROUP BY sc.`s_id` ORDER BY avg_score DESC; -- SELECT st.*, sc.*, t1.avg_score FROM Student st, Score sc, (SELECT sc.`s_id`, AVG(sc.`s_score`) avg_score FROM Score sc GROUP BY sc.`s_id` ORDER BY avg_score DESC) t1 WHERE sc.`s_id` = t1.`s_id` AND st.`s_id` = sc.`s_id` ORDER BY t1.avg_score DESC ; -- 参考 ---------------- SELECT a.s_id,(SELECT s_score FROM score WHERE s_id=a.s_id AND c_id='01') AS 语文, (SELECT s_score FROM score WHERE s_id=a.s_id AND c_id='02') AS 数学, (SELECT s_score FROM score WHERE s_id=a.s_id AND c_id='03') AS 英语, ROUND(AVG(s_score),2) AS 平均分 FROM score a GROUP BY a.s_id ORDER BY 平均分 DESC; -- 18.查询各科成绩最高分、最低分和平均分： -- 以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 -- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 -- anan + 参考 ---------------- SELECT c.`c_id`, c.`c_name`, MAX(sc.`s_score`) '最高分', MIN(sc.`s_score`) '最低分', AVG(sc.`s_score`) '平均分', (SELECT COUNT(s_id) FROM Score WHERE s_score >= 60 AND c_id = c.`c_id`)/ COUNT(*) '及格率', (SELECT COUNT(s_id) FROM Score WHERE s_score >= 70 AND s_score < 80 AND c_id = c.`c_id`)/ COUNT(*) '中等率', (SELECT COUNT(s_id) FROM Score WHERE s_score >= 80 AND s_score < 90 AND c_id = c.`c_id`)/ COUNT(*) '优良率', (SELECT COUNT(s_id) FROM Score WHERE s_score >= 90 AND c_id = c.`c_id`)/ COUNT(*) '优秀率' FROM Course c, Score sc WHERE c.`c_id` = sc.`c_id` GROUP BY sc.`c_id` ; -- 参考 ---------------- SELECT a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2), ROUND(100*(SUM(CASE WHEN a.s_score>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.s_score THEN 1 ELSE 0 END)),2) AS 及格率, ROUND(100*(SUM(CASE WHEN a.s_score>=70 AND a.s_score<80 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.s_score THEN 1 ELSE 0 END)),2) AS 中等率, ROUND(100*(SUM(CASE WHEN a.s_score>=80 AND a.s_score<90 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.s_score THEN 1 ELSE 0 END)),2) AS 优良率, ROUND(100*(SUM(CASE WHEN a.s_score>=90 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.s_score THEN 1 ELSE 0 END)),2) AS 优秀率 FROM score a LEFT JOIN course b ON a.c_id = b.c_id GROUP BY a.c_id,b.c_name; -- 19、按各科成绩进行排序，并显示排名(实现不完全) -- 这个题太难了，劝退了 -- mysql没有rank函数 SELECT s_id,c_id,s_score FROM score WHERE c_id='01' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC; SELECT a.s_id,a.c_id, @i:=@i +1 AS i保留排名, @k:=(CASE WHEN @score=a.s_score THEN @k ELSE @i END) AS rank不保留排名, @score:=a.s_score AS score FROM ( SELECT s_id,c_id,s_score FROM score WHERE c_id='01' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(SELECT @k:=0,@i:=0,@score:=0)s UNION SELECT a.s_id,a.c_id, @i:=@i +1 AS i, @k:=(CASE WHEN @score=a.s_score THEN @k ELSE @i END) AS rank, @score:=a.s_score AS score FROM ( SELECT s_id,c_id,s_score FROM score WHERE c_id='02' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(SELECT @k:=0,@i:=0,@score:=0)s UNION SELECT a.s_id,a.c_id, @i:=@i +1 AS i, @k:=(CASE WHEN @score=a.s_score THEN @k ELSE @i END) AS rank, @score:=a.s_score AS score FROM ( SELECT s_id,c_id,s_score FROM score WHERE c_id='03' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(SELECT @k:=0,@i:=0,@score:=0)s; -- 20、查询学生的总成绩并进行排名 -- anan + 参考---------------- SELECT st.*, (CASE WHEN SUM(sc.`s_score`) IS NULL THEN 0 ELSE SUM(sc.`s_score`) END) '总分' FROM Student st LEFT JOIN Score sc ON st.`s_id` = sc.`s_id` GROUP BY sc.`s_id` ORDER BY SUM(sc.`s_score`) DESC; -- anan + 参考---------------- SELECT t1.*, (@i:=@i+1) '排名' FROM (SELECT st.*, (CASE WHEN SUM(sc.`s_score`) IS NULL THEN 0 ELSE SUM(sc.`s_score`) END) '总分' FROM Student st LEFT JOIN Score sc ON st.`s_id` = sc.`s_id` GROUP BY sc.`s_id` ORDER BY SUM(sc.`s_score`) DESC) t1, (SELECT @i:=0) AS init; -- 参考 ---------------- SELECT a.s_id, @i:=@i+1 AS i, @k:=(CASE WHEN @score=a.sum_score THEN @k ELSE @i END) AS rank, @score:=a.sum_score AS score FROM (SELECT s_id,SUM(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC)a, (SELECT @k:=0,@i:=0,@score:=0)s -- anan ---------------- -- 参考 ---------------- /* -- ROUND 函数用于把数值字段舍入为指定的小数位数。 */ /* 数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left join时，on和where条件的区别如下： 1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。 */ /* count(*) 将返回表格中所有存在的行的总数包括值为 null 的行， count(列名) 将返回表格中除去 null 以外的所有行的总数 (有默认值的列也会被计入) 按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用count(*)。 */ /* 为查询的数据排序列号的SQL语句 SELECT (@i:=@i+1),t.* FROM table_name t,(SELECT @i:=0) AS j */
