sql语句练习题 50题
在网上搜索sql练习题，找到了sql50题，过程中也有对别人的参考
所有的题我都自己做了一遍，敲过了一遍，代码方面应该是没有问题的。
做完之后，对连接，分组，逆向思维，有了更深刻的认识，同时还学到了case语法，关于时间的函数，以及排名的实现
有的语句可能写的比较累赘麻烦，这里是为了练习，就不要在意这些了
有很多题目，例如算平均数的地方都是有些争议，例如学生压根没选其中某门课，算平均分是按学生自己选的课程，还是按所有的课程数目，这个就看大家怎么去理解。
表结构以及表数据
-- 学生编号,学生姓名, 出生年月,学生性别
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`)
);
-- 学生编号,课程编号,分数`course`
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"课程成绩高的学生的信息及课程分数
/*(题目说的不清楚，假使这里要查的学生信息只有学生编号，学生姓名,课程分数由于比较的是01和02，只展示01课程和02课程的分数) */
SELECT
t1.`s_id`,
t1.`s_name`,
t2.`s_score` AS 01course,
t3.`s_score` AS 02course
FROM Student t1 ,Score t2,Score t3
WHERE t1.`s_id`=t2.`s_id`
AND t1.`s_id`=t3.`s_id`
AND t2.`c_id`='01'
AND t3.`c_id`='02'
AND t2.`s_score`>t3.`s_score`;
/*上面是隐式内连接的写法，把几张表连起来用where筛选，下面是先显式内连接再外连接*/
SELECT
t1.`s_id`,t1.`s_name`,
t2.`s_score` AS 01course,
t3.`s_score` AS 02course
FROM Student t1
JOIN Score t2 ON t1.`s_id` =t2.`s_id` AND t2.`c_id`='01'
LEFT JOIN Score t3 ON t1.`s_id`=t3.`s_id` AND t3.`c_id`='02' WHERE t2.`s_score`>t3.`s_score` ;
为了方便好理解这个，拆开看下每步求得是什么
如果不加where的比较大小判断
/*第三种写法，采用子查询的方式，大体上也是先分开筛选01，02的，不过感觉写的有点重复，这里是练习，就不在意这些了*/
SELECT t1.`s_id`,t1.`s_name`,
t2.`s_score` AS 01course,
t3.s_score AS 02Course
FROM Student t1,(SELECT s1.`s_id` ,s1.`s_score` FROM Score s1 WHERE s1.`c_id`='01' ) t2,
(SELECT s2.`s_id`,s2.`s_score` FROM Score s2 WHERE s2.`c_id`='02') t3
WHERE t1.`s_id`=t2.s_id AND t1.`s_id`=t3.s_id AND t2.s_score>t3.s_score;
-------------------------------------------------------------------------------------------------
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
/*这个跟第一题一样，改变下比较的符号就行了*/
SELECT
t1.`s_id`,
t1.`s_name`,
t2.`s_score` AS 01course,
t3.`s_score` AS 02course
FROM Student t1 ,Score t2,Score t3
WHERE t1.`s_id`=t2.`s_id`
AND t1.`s_id`=t3.`s_id`
AND t2.`c_id`='01'
AND t3.`c_id`='02'
AND t2.`s_score`<t3.`s_score`;
-- ------------------
SELECT
t1.`s_id`,t1.`s_name`,
t2.`s_score` AS 01course,
t3.`s_score` AS 02course；
FROM Student t1
JOIN Score t2 ON t1.`s_id` =t2.`s_id` AND t2.`c_id`='01'
LEFT JOIN Score t3 ON t1.`s_id`=t3.`s_id` AND t3.`c_id`='02'
WHERE t2.`s_score`<t3.`s_score` ;
-- ------------------
SELECT t1.`s_id`,t1.`s_name`,
t2.`s_score` AS 01course,
t3.s_score AS 02Course
FROM Student t1,(SELECT s1.`s_id` ,s1.`s_score` FROM Score s1 WHERE s1.`c_id`='01' ) t2,
(SELECT s2.`s_id`,s2.`s_score` FROM Score s2 WHERE s2.`c_id`='02') t3
WHERE t1.`s_id`=t2.s_id AND t1.`s_id`=t3.s_id AND t2.s_score<t3.s_score;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
这个题考的是分组，我们先只查学生的id和学生平均成绩大于60的，只在score表上做操作。注意分组后再加筛选条件用having
SELECT
t1.`s_id`,
AVG(t1.`s_score`) AS avgnumber
FROM score t1
GROUP BY t1.`s_id`
HAVING AVG(t1.`s_score`)>60;
然后我们再将其和学生表联系起来
SELECT
t1.`s_id`,
t1.`s_name`,
AVG(t2.`s_score`) AS avgnumber
FROM Student t1,Score t2
WHERE t1.`s_id`=t2.`s_id`
GROUP BY t2.`s_id`
HAVING AVG(t2.`s_score`)>60;
结果如下
下面是原作者的，原作者用了保留小数精确度的函数，。然后分组条件那里我觉得写一个就行，毕竟学生id和学生姓名是一一对应关系。
SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score FROM
student b
JOIN score a ON b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score >=60;
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
这道题我原本用的内连接，但是用内连接展示的只能是符合条件两表都有的数据。有个数据只在学生表有，但是score表没他的课程和分数记录
SELECT
t1.`s_id`,t1.`s_name`,
COUNT(t2.`c_id`) AS CourseSum,
SUM(t2.`s_score`) AS ScoreSum
FROM Student t1 ,Score t2
WHERE t1.`s_id`=t2.`s_id`
GROUP BY t2.`s_id` ;
没有学生王菊的数据，于是采用外连接的方法，除去符合条件的，还包括单表独有的
SELECT
t1.`s_id`,t1.`s_name`,
IFNULL(COUNT(t2.`c_id`),0) AS CourseSum,
IFNULL(SUM(t2.`s_score`),0) AS ScoreSum
FROM student t1 LEFT JOIN score t2
ON t1.`s_id`=t2.`s_id` GROUP BY t2.`s_id`;
-- 6、查询"李"姓老师的数量
SELECT
COUNT(t1.t_id)
FROM (SELECT teacher.`t_id` FROM teacher WHERE teacher.`t_name` LIKE '李%') t1 ;
SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '李%';
-- 7、查询学过"张三"老师授课的同学的信息
/*方式一：通过内连接的方式把4张表连起来，加上筛选条件，比较好理解*/
SELECT
t1.`s_id`,t1.`s_name`
FROM student t1,course t2,teacher t3,score t4
WHERE t3.`t_name`='张三'
AND t3.`t_id`=t2.`t_id`
AND t2.`c_id`=t4.`c_id`
AND t4.`s_id`=t1.`s_id`;
/*方式二：通过子查询的方式，先根据课程表和老师表选出张三老师教的课程编号，然后在成绩表里去匹配这些编号，进而得到学生信息*/
SELECT
a.`s_id`,a.`s_name`
FROM student a JOIN score b
ON a.`s_id`=b.`s_id`
WHERE b.`c_id` IN
(SELECT
c.`c_id`
FROM course c JOIN teacher d
ON c.`t_id`=d.`t_id`
WHERE d.`t_name`='张三'
);
/*原作者方式：连续子查询，先求出张三老师的老师编号，根据老师编号找到课程编号，再去score中根据课程编号找到学生的信息*/
SELECT a.*
FROM student a
JOIN score b ON a.s_id=b.s_id
WHERE b.c_id IN(
SELECT c_id
FROM course
WHERE t_id =(
SELECT t_id
FROM teacher
WHERE t_name = '张三'));
-- 8、查询没学过"张三"老师授课的同学的信息
/*方式一：由于上题以及知道了学过张三老师的同学信息，那么就把上题作为一个子查询用进来，所有学生的信息，使用not in,如果学生id不在张三课程学生的id里，即为没学过的*/
SELECT
e.`s_id`,e.`s_name`
FROM student e
WHERE e.`s_id` NOT IN
(
SELECT
a.`s_id`
FROM student a JOIN score b
ON a.`s_id`=b.`s_id`
WHERE b.`c_id` IN
(SELECT
c.`c_id`
FROM course c JOIN teacher d
ON c.`t_id`=d.`t_id`
WHERE d.`t_name`='张三'
)
)
/*方式二：与方式一相同，不过排除方式是使用left join ，并且连接条件加上右表 为null,这样即是左表独有的数据*/
SELECT
e.`s_id`,e.`s_name`
FROM student e
LEFT JOIN
(
SELECT
a.`s_id`
FROM student a JOIN score b
ON a.`s_id`=b.`s_id`
WHERE b.`c_id` IN
(SELECT
c.`c_id`
FROM course c JOIN teacher d
ON c.`t_id`=d.`t_id`
WHERE d.`t_name`='张三'
)
) g ON e.`s_id`=g.`s_id` WHERE g.`s_id` IS NULL ;
/*方式三：连续子查询，根据老师姓名查老师id,根据老师id查课程id，根据课程id查学生id,再排除*/
SELECT
a.`s_id`,a.`s_name`
FROM student a
WHERE a.`s_id` NOT IN(
SELECT b.`s_id`
FROM student b ,score c WHERE b.`s_id`=c.`s_id` AND c.`c_id` IN(
SELECT d.`c_id`
FROM course d
WHERE d.`t_id` IN(
SELECT teacher.`t_id`
FROM teacher
WHERE teacher.`t_name`='张三'
)
)
)
-- 9 、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
/*方式一：形式上复杂点，但是很好理解，学生表单独和分数表去显式内连接两次，两次的筛选条件分别是课程号为01，02.然后形成的两个结果集再显示内连接下， 连接条件即是同时选了01，02的，因为是同时，所以最外面的s_id和s_name写前面的还是后面的都无所谓*/
SELECT t5.s_id,t5.s_name FROM
(SELECT
t1.`s_id`, t1.`s_name`
FROM student t1 JOIN score t2
ON t1.`s_id`=t2.`s_id`
WHERE t2.`c_id` ='01') t6
JOIN (SELECT t3.`s_id`,t3.`s_name`
FROM student t3 JOIN score t4
ON t3.`s_id`=t4.`s_id`
WHERE t4.`c_id`='02') t5 ON t5.`s_id`=t6.`s_id`;
/*方式二：与上面是一个思路，只不过第一次先显式内连接求选了01的学生信息，后面则直接和分数表再内连接，因为同时选了0102的，那么选了01的学生必定在分数表里同时找到02的记录，所以后面直接连分数表，没有像上面连结果集*/
SELECT t3.s_id,t3.s_name
FROM
(SELECT
t1.`s_id`, t1.`s_name`
FROM student t1 JOIN score t2
ON t1.`s_id`=t2.`s_id`
WHERE t2.`c_id` ='01') t3
JOIN score t4 ON t3.s_id=t4.`s_id` AND t4.`c_id`='02' ;
/*方式三：采用隐式内连接，where 加and把几个连接条件和筛选条件全写上，比较好理解*/
SELECT
t1.`s_id`,t1.`s_name`
FROM student t1,score t2,score t3
WHERE t1.`s_id`=t2.`s_id`
AND t2.`c_id`='01'
AND t1.`s_id`=t3.`s_id`
AND t3.`c_id`='02'
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
/*方式一：先找出修了01的学生信息，然后找出修了02的学生的信息，not in排除*/
SELECT
t1.`s_id`,t1.`s_name`
FROM student t1
JOIN score t2 ON t1.`s_id`=t2.`s_id`
WHERE t2.`c_id`='01' AND t1.`s_id` NOT IN
(
SELECT t4.`s_id`
FROM student t4
JOIN score t5 ON t4.`s_id`=t5.`s_id`
WHERE t5.`c_id`='02'
) ;
/*方式二：先找出修了01的学生信息，然后直接通过score表找修了02课程的学生信息，因为分数表有学生id，不需要像第一种去做连接*/
SELECT
t1.`s_id`,t1.`s_name`
FROM student t1
WHERE t1.`s_id`
IN(
SELECT t2.`s_id`
FROM student t2,score t3
WHERE t2.`s_id`=t3.`s_id`
AND t3.`c_id`='01'
AND t2.`s_id` NOT IN (
SELECT t4.`s_id`
FROM score t4 WHERE t4.`c_id`='02'
)
)
/*方式三：先显式内连接，分别找出修了01，02的，然后两个结果集再左外连接，排除公共的，即令两个集合连接时右边的条件为null,这样就剩下左边独有的*/
SELECT t3.s_id,t3.s_name
FROM
(SELECT
t1.`s_id`,t1.`s_name`
FROM student t1
JOIN score t2 ON t1.`s_id`=t2.`s_id`
WHERE t2.`c_id`='01' ) t3
LEFT JOIN (
SELECT t4.`s_id`,t4.`s_name`
FROM student t4 JOIN score t5
ON t4.`s_id`=t5.`s_id`
WHERE t5.`c_id`='02'
) t6 ON t3.s_id=t6.s_id WHERE t6.s_id IS NULL;
/*方式四：原作者提供，查找是否学习0201课程的时候先不用与学生表关联，只需排除，得到学号，最后再从学生表查找学生信息，这里是在练习，所以各种写法都尝试着写写*/
SELECT a.* FROM student a WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id='01' ) AND a.s_id NOT IN(SELECT s_id FROM score WHERE c_id='02')
-- 11、查询没有学全所有课程的同学的信息
/*方式一：连接分组，计算课程的数目是否小于课程表的数目，因为包括一门课都没选的，所以要用左连*/
SELECT t1.`s_id`,t1.`s_name`,COUNT(t2.c_id) FROM student t1 LEFT JOIN score t2 ON t1.`s_id`=t2.s_id GROUP BY t1.`s_id` HAVING COUNT(t2.c_id)<(SELECT COUNT(t3.`c_id`) FROM course t3 )
/*方式二：先单表分组，找出学全了课程的学生id,然后利用学生表去筛选下，和上面是两种思维*/
SELECT t1.`s_id`,t1.`s_name` FROM student t1 WHERE t1.`s_id` NOT IN ( SELECT t2.`s_id` FROM score t2 GROUP BY t2.`s_id` HAVING COUNT(t2.`c_id`)=(SELECT COUNT(t3.c_id)FROM course t3) )
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
/*方式一：先找出01同学学的课程，两表连接，除去01同学本身，*/
SELECT DISTINCT t2.`s_id`, t2.`s_name` FROM student t2,score t3 WHERE t2.`s_id`=t3.`s_id` AND t2.`s_id`!='01' AND t3.`c_id` IN( SELECT t1.`c_id` FROM score t1 WHERE t1.`s_id`='01' ) ;
/*方式二：连续子查询*/
SELECT t1.`s_id`,t1.`s_name` FROM student t1 WHERE t1.`s_id`!='01' AND t1.`s_id` IN( SELECT t2.`s_id` FROM score t2 WHERE t2.`c_id` IN ( SELECT t3.`c_id` FROM score t3 WHERE t3.`s_id`='01' ) ) ;
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
这个题也是想了很久，一开始想的思路是，在学生表和分数表连接后用in（01学过的课程）然后加个分组判断下数目和01学过课程数相同的，但这种是不对的，例如01学了abc,02学了acd,02有一门课程在01学过的名单里，这就被记录上了，但其实他们的课程不完全相同。
后来想到了在判断课程数的条件下，利用双重否定，即not in(01学过的课程)，这里也不用像in一样纠结是满足一个还是全部之类的问题，只要学生有一门课程不在01的课程id集合里，就一定会被记录的。然后再正常的连接，加条件，学生不在（学过01同学没学过课程的学生集合），而且加上了与01课程数相同的条件，这样求出来的就是与01完全一样的课程的同学
思路分解
-- 01号同学的课程数量
SELECT COUNT(t1.`c_id`)FROM score t1 WHERE t1.`s_id`='01';
-- 01同学所选课程的id
SELECT t1.`c_id` FROM score t1 WHERE t1.`s_id`='01';
-- 课程与01同学有不同的同学的id,有一门即会被记录
SELECT t2.`s_id` FROM score t2 WHERE t2.`c_id` NOT IN( SELECT t1.`c_id` FROM score t1 WHERE t1.`s_id`='01');
解答
SELECT t3.`s_id`,t3.`s_name`
FROM student t3 JOIN score t4
ON t3.`s_id`=t4.`s_id`
WHERE t4.`s_id`!=01
AND t4.`s_id` NOT IN
(SELECT t2.`s_id` FROM score t2 WHERE t2.`c_id` NOT IN( SELECT t1.`c_id` FROM score t1 WHERE t1.`s_id`='01')
)
GROUP BY t4.`s_id`
HAVING COUNT(t4.`c_id`)=(SELECT COUNT(t1.`c_id`)FROM score t1 WHERE t1.`s_id`='01');
/*方式二：来源原作者文章，供大家参考，也是利用的双重否定*/
SELECT
Student.*
FROM
Student
WHERE
s_id IN (SELECT s_id FROM Score GROUP BY s_id HAVING COUNT(s_id) = (
SELECT COUNT(c_id) FROM Score WHERE s_id = '01'
)
)
AND s_id NOT IN (
SELECT s_id FROM Score
WHERE c_id IN(
SELECT DISTINCT c_id FROM Score
WHERE c_id NOT IN (
SELECT c_id FROM Score WHERE s_id = '01'
)
) GROUP BY s_id
)
AND s_id NOT IN ('01')
/*方式三：来源原作者文章，供大家参考*/
SELECT
t3.*
FROM
(
SELECT
s_id,
GROUP_CONCAT(c_id ORDER BY c_id) group1
FROM
score
WHERE
s_id <> '01'
GROUP BY
s_id
) t1
INNER JOIN (
SELECT
GROUP_CONCAT(c_id ORDER BY c_id) group2
FROM
score
WHERE
s_id = '01'
GROUP BY
s_id
) t2 ON t1.group1 = t2.group2
INNER JOIN student t3 ON t1.s_id = t3.s_id
-- 14查询没学过张三老师讲授的任何一门课的学生姓名
/*根据课程表和教师表找出张三教的课程的集合，然后通过分数表找出学了张三课程的学生，最后再用学生表not in排除*/
SELECT s1.`s_id`,s1.`s_name`
FROM student s1 WHERE s1.`s_id`
NOT IN(
SELECT t4.`s_id`
FROM score t4
WHERE t4.`c_id` IN(
SELECT t2.`c_id`
FROM teacher t1,course t2
WHERE t1.`t_id`=t2.`t_id`
AND t1.`t_name`='张三'
))
-- 15、查询两名及其以上不及格课程同学的学号，姓名，及平均成绩
/*方式一：连接分组的同时，把分数限制加上，分组后把符合条件的行筛出来，考的知识点就是count里不能写判断条件，count(*)算行数*/
SELECT t1.`s_id`,t1.`s_name` ,SUM(t2.`s_score`)/COUNT(t2.`c_id`) AS avgnumber
FROM student t1,score t2
WHERE
t1.`s_id`=t2.`s_id`
AND t2.s_score<60
GROUP BY t2.`s_id`
HAVING COUNT(*)>=2
/*方式二：先找出两门没及格的学生id，再连接表*/
SELECT t2.`s_id`,t2.`s_name`,SUM(t3.`s_score`)/COUNT(t3.`c_id`) AS avgnumber
FROM student t2,score t3
WHERE t2.`s_id`=t3.`s_id`
AND t3.`s_id` IN(
SELECT t1.`s_id`
FROM score t1 WHERE t1.`s_score`<60 GROUP BY t1.`s_id` HAVING COUNT(*)>=2
)
GROUP BY t2.s_id
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT t1.`s_id`,t1.`s_name`,t2.`s_score` FROM student t1,score t2 WHERE t1.`s_id`=t2.`s_id` AND t2.`s_score`<60 AND t2.`c_id`='01' ORDER BY t2.`s_score` DESC ;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
/*如果要带上一门课都没选的学生，连接处改为左连接*/
SELECT t1.`s_id`,t1.`s_name`,(SELECT t3.`s_score` FROM score t3 WHERE t3.s_id=t2.`s_id` AND t3.`c_id`='01') AS 语文, (SELECT t4.s_score FROM score t4 WHERE t4.s_id=t2.`s_id` AND t4.c_id ='02') AS 数学, (SELECT t5.s_score FROM score t5 WHERE t5.s_id=t2.s_id AND t5.c_id ='03') AS 英语, SUM(t2.s_score)/COUNT(t2.c_id) AS avgnumber FROM student t1,score t2 WHERE t1.`s_id`=t2.`s_id` GROUP BY t1.`s_id` ORDER BY avgnumber DESC;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
/*这里要学下case语法，简单介绍一下，具体的可以网上搜
case 字段
when 判断条件 then 结果 else 结果
end
*/
SELECT t2.`c_id`, t2.`c_name`, MAX(t1.`s_score`), MIN(t1.`s_score`), AVG(t1.`s_score`), 100*SUM(CASE WHEN t1.s_score >=60 THEN 1 ELSE 0 END)/COUNT(1) AS 及格率, 100*SUM(CASE WHEN t1.s_score >=70 AND t1.s_score<80 THEN 1 ELSE 0 END)/COUNT(1) AS 中等率, 100*SUM(CASE WHEN t1.s_score >=80 AND t1.s_score<90 THEN 1 ELSE 0 END)/COUNT(1) AS 优良率, 100*SUM(CASE WHEN t1.s_score >=90 THEN 1 ELSE 0 END )/COUNT(1) AS 优秀率 FROM score t1,course t2 WHERE t1.`c_id`=t2.`c_id` GROUP BY t1.`c_id`;
-- 19、按各科成绩进行排序，并显示排名
/*8.0有rank()函数，8.0一下没有，可以使用用户变量，从这里开始会有好几道关于名次的题目，把这里的三种方式弄清楚就差不多了*/
-- 普通使用
SELECT t1.`s_id`,t1.`c_id`,t1.`s_score`,@r:=@r+1 AS '名次' FROM score t1 ,(SELECT @r:=0) q ORDER BY t1.`s_score` DESC ;
-- 并排名次展示
SELECT t1.`s_id`,t1.`c_id`,t1.`s_score`,(CASE WHEN @temp=t1.`s_score` THEN @r WHEN @temp:=t1.`s_score`THEN @r:=@r+1 END) AS '名次' FROM score t1,(SELECT @r:=0,@temp :=NULL) q ORDER BY t1.`s_score` DESC;
-- 并排名次跳过
SELECT a.s_id,a.c_id,a.s_score,a.名次 FROM ( SELECT t1.`s_id`,t1.`c_id`,t1.`s_score`,@r:=IF(@temp=t1.`s_score` , @r,@ra) AS '名次' ,@ra:=@ra+1,@temp:=t1.`s_score`FROM score t1, (SELECT @r:=0,@temp:=NULL,@ra:=1)q ORDER BY t1.`s_score`DESC ) a ;
上面的几种写法都是不分课程，所有的混在一起，因为题目说的也不明确，下面用分开的
SELECT * FROM ( SELECT t1.`s_id`,t1.`c_id`,t1.`s_score`,@r:=@r+1 AS'名次' FROM score t1,(SELECT @r:=0)a WHERE t1.`c_id`='01' ORDER BY t1.`s_score`DESC ) AS k1 UNION SELECT * FROM ( SELECT t2.s_id,t2.`c_id`,t2.s_score,@s:=@s+1 AS '名次' FROM score t2, (SELECT @s:=0)b WHERE t2.c_id='02' ORDER BY t2.s_score DESC ) AS k2 UNION SELECT *FROM (SELECT t3.s_id,t3.c_id,t3.s_score,@t:=@t+1 AS '名次' FROM score t3,(SELECT @t:=0)c WHERE t3.c_id='03' ORDER BY t3.s_score DESC )AS k3 ;
-- 20、查询学生的总成绩并进行排名
SELECT a.*,@r:=@r+1 AS '名次' FROM (SELECT t1.`s_id`,IFNULL(SUM(t1.`s_score`),0) AS sumnumber FROM score t1 GROUP BY t1.`s_id` ORDER BY sumnumber DESC) a,(SELECT @r:=0)b
-- 21、查询不同老师所教不同课程平均分从高到低显示
SELECT t3.`t_name`,t2.`c_name`,AVG(t1.`s_score`) AS avgnumber FROM score t1,course t2,teacher t3 WHERE t1.`c_id`=t2.`c_id` AND t2.`t_id`=t3.`t_id` GROUP BY t1.`c_id` ORDER BY avgnumber DESC;
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
/*这个题我直接拿的原作者的，把课程单独分开算的话，其实每个题跟前面几道求名次的题差不多，然后再筛选2-3名，最后三个部份并起来*/
SELECT d.*,c.排名,c.s_score,c.c_id FROM ( SELECT a.s_id,a.s_score,a.c_id,@i:=@i+1 AS 排名 FROM score a,(SELECT @i:=0)s WHERE a.c_id='01' ORDER BY a.s_score DESC )c LEFT JOIN student d ON c.s_id=d.s_id WHERE 排名 BETWEEN 2 AND 3 UNION SELECT d.*,c.排名,c.s_score,c.c_id FROM ( SELECT a.s_id,a.s_score,a.c_id,@j:=@j+1 AS 排名 FROM score a,(SELECT @j:=0)s WHERE a.c_id='02' ORDER BY a.s_score DESC )c LEFT JOIN student d ON c.s_id=d.s_id WHERE 排名 BETWEEN 2 AND 3 UNION SELECT d.*,c.排名,c.s_score,c.c_id FROM ( SELECT a.s_id,a.s_score,a.c_id,@k:=@k+1 AS 排名 FROM score a,(SELECT @k:=0)s WHERE a.c_id='03' ORDER BY a.s_score DESC )c LEFT JOIN student d ON c.s_id=d.s_id WHERE 排名 BETWEEN 2 AND 3;
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT t1.`c_id`,t2.`c_name`, SUM(CASE WHEN t1.`s_score`>85 AND t1.`s_score`<=100 THEN 1 ELSE 0 END) AS '85-100人数',100*SUM(CASE WHEN t1.`s_score`>85 AND t1.`s_score`<=100 THEN 1 ELSE 0 END) /COUNT(1) AS 占比, SUM(CASE WHEN t1.s_score >70 AND t1.s_score <=85 THEN 1 ELSE 0 END) AS '70-85人数' ,100*SUM(CASE WHEN t1.s_score >70 AND t1.s_score <=85 THEN 1 ELSE 0 END) /COUNT(1) AS 占比, SUM(CASE WHEN t1.s_score >60 AND t1.s_score <=70 THEN 1 ELSE 0 END) AS '60-70人数' ,100*SUM(CASE WHEN t1.s_score >60 AND t1.s_score <=70 THEN 1 ELSE 0 END) /COUNT(1) AS 占比, SUM(CASE WHEN t1.s_score >0 AND t1.s_score <=60 THEN 1 ELSE 0 END) AS '0-60人数' ,100*SUM(CASE WHEN t1.s_score >0 AND t1.s_score <=60 THEN 1 ELSE 0 END) /COUNT(1) AS 占比 FROM score t1, course t2 WHERE t1.`c_id`=t2.`c_id` GROUP BY t1.`c_id`;
-- 24、查询学生平均成绩及其名次
/*分组后再用变量去完成名次功能会出问题，名次排序是跟着id走的，而且变量初始赋值语句不知道写哪合适，所以先分组排序作为一个集合，再进行排序*/
SELECT a.*,@r:=@r+1 AS '名次' FROM (SELECT t1.`s_id`,SUM(t1.`s_score`)/COUNT(1) AS avgNumber FROM score t1 GROUP BY t1.`s_id` ORDER BY avgNumber DESC )a,(SELECT @r:=0)b
-- 25、查询各科成绩前三名的记录
/*这个题我直接拿的原作者的答案，还是不太明白*/
SELECT a.s_id,a.c_id,a.s_score FROM score a LEFT JOIN score b ON a.c_id = b.c_id AND a.s_score<b.s_score GROUP BY a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3 ORDER BY a.c_id,a.s_score DESC;
-- 26、查询每门课程被选修的学生数
SELECT t1.`c_id`,COUNT(t1.`s_id`) FROM score t1 GROUP BY t1.`c_id`
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT t2.`s_id`,t2.`s_name` FROM score t1,student t2 WHERE t1.`s_id`=t2.`s_id` GROUP BY t1.`s_id` HAVING COUNT(t1.`c_id`)=2;
-- 28、查询男生、女生人数
SELECT t1.`s_sex`,COUNT(t1.`s_sex`) FROM student t1 GROUP BY t1.`s_sex`
-- 29、查询名字中含有"风"字的学生信息
SELECT t1.`s_id`,t1.`s_name` FROM student t1 WHERE t1.`s_name` LIKE '%风%'
-- 30、查询同名同性别学生名单，并统计同名人数
/*这个是直接拿的原作者的，题目都不是很明白，同名是同姓还是同姓名，语句倒不是很难理解*/
SELECT t1.`s_name`,t1.`s_sex`,COUNT(1) FROM student t1,student t2 WHERE t1.`s_id`!=t2.s_id AND t1.`s_name`=t2.s_name AND t1.`s_sex`=t2.s_sex GROUP BY t1.`s_name`,t1.`s_sex`
-- 31、查询1990年出生的学生名单
SELECT t1.`s_id`,t1.`s_name` FROM student t1 WHERE t1.`s_birth` LIKE '1990%';
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT t1.`c_id`,AVG(t1.`s_score`) AS avgnumber FROM score t1 GROUP BY t1.`c_id` ORDER BY AVG(t1.`s_score`) DESC ,t1.`c_id` ASC
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT t1.`s_id`,t1.`s_name`,AVG(t2.`s_score`) FROM student t1,score t2 WHERE t1.`s_id`=t2.`s_id` GROUP BY t1.`s_id` HAVING AVG(t2.`s_score`)>=85;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT t1.`s_name`,t2.`s_score` FROM student t1,score t2,course t3 WHERE t1.`s_id`=t2.`s_id` AND t2.`c_id`=t3.c_id AND t3.c_name='数学' AND t2.`s_score`<60;
-- 35、查询所有学生的课程及分数情况；
/*这个我是按着原作者的思路写的，每门课的成绩展示，用sum和case组合起来还是很巧妙的*/
SELECT t1.`s_name`, SUM(CASE WHEN t3.`c_name`='语文' THEN t2.`s_score` ELSE 0 END ) AS '语文', SUM(CASE WHEN t3.`c_name`='数学' THEN t2.`s_score` ELSE 0 END ) AS '数学', SUM(CASE WHEN t3.`c_name`='英语' THEN t2.`s_score` ELSE 0 END ) AS '英语' FROM student t1 LEFT JOIN score t2 ON t1.`s_id`=t2.`s_id` LEFT JOIN course t3 ON t2.`c_id`=t3.`c_id` GROUP BY t1.`s_id`
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT t2.`s_name`,t3.c_name,t1.`s_score` FROM score t1,student t2,course t3 WHERE t1.`s_id`=t2.`s_id` AND t1.`c_id`=t3.c_id AND t1.`s_score`>70;
-- 37、查询不及格的课程
SELECT t2.`s_name`,t3.c_name,t1.`s_score` FROM score t1,student t2,course t3 WHERE t1.`s_id`=t2.`s_id` AND t1.`c_id`=t3.c_id AND t1.`s_score`<60;
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
SELECT t2.`s_id`, t2.`s_name` FROM score t1,student t2 WHERE t1.`s_id`=t2.`s_id` AND t1.`c_id`='01' AND t1.`s_score`>=80;
-- 39、求每门课程的学生人数
SELECT t1.`c_id`,COUNT(*) AS 选课人数 FROM score t1 GROUP BY t1.`c_id`;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
/*张三老师教的课程id*/
SELECT t1.`c_id` FROM course t1,teacher t2 WHERE t1.`t_id`=t2.`t_id` AND t2.`t_name`='张三';
SELECT t3.s_name,t4.`s_score` FROM student t3,score t4 WHERE t3.s_id=t4.`s_id` AND t4.`c_id` IN (SELECT t1.`c_id` FROM course t1,teacher t2 WHERE t1.`t_id`=t2.`t_id` AND t2.`t_name`='张三') ORDER BY t4.`s_score` DESC LIMIT 0,1;
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT t1.`s_id`,t1.`c_id`,t1.`s_score` FROM score t1,score t2 WHERE t1.`c_id`!=t2.`c_id` AND t1.`s_score`=t2.`s_score`;
-- 42、查询每门功成绩最好的前两名
SELECT a.s_id,a.c_id,a.s_score FROM score a WHERE (SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id AND b.s_score>=a.s_score)<=2 ORDER BY a.c_id;
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT t1.c_id,COUNT(*) AS learnNumber FROM score t1 GROUP BY t1.c_id HAVING learnNumber>5 ORDER BY learnNumber DESC,c_id ASC;
-- 44、检索至少选修两门课程的学生学号
SELECT t2.`s_id` ,COUNT(1) FROM score t2 GROUP BY t2.`s_id` HAVING COUNT(1)>=2 ;
-- 45、查询选修了全部课程的学生信息
SELECT t1.`s_id`,t1.`s_name` FROM student t1,score t2 WHERE t1.`s_id`=t2.`s_id` GROUP BY t1.`s_id` HAVING COUNT(1)= ( SELECT COUNT(1) FROM course )
-- 46、查询各学生的年龄(按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一)
/*初次接触关于时间的题目，可以跳过这个题，看看后面的题，摸清关键字怎么用再来看这个题，同时这个题借助case语法*/
SELECT t1.`s_name`, t1.`s_birth`, (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(t1.`s_birth`,'%Y') -( CASE WHEN DATE_FORMAT(NOW(),'%m%d')<DATE_FORMAT(t1.`s_birth`,'%m%d') THEN 1 ELSE 0 END) )AS age FROM student t1
-- 47、查询本周过生日的学生
/*weak函数，计算日期是本年的第几个星期*/
SELECT WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
SELECT t1.`s_id`,t1.`s_name`,t1.`s_birth` FROM student t1 WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(t1.`s_birth`);
-- 48、查询下周过生日的学生
SELECT t1.`s_id`,t1.`s_name`,t1.`s_birth` FROM student t1 WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1=WEEK(t1.`s_birth`)
-- 49、查询本月过生日的学生
SELECT t1.`s_id`,t1.`s_name`,t1.`s_birth` FROM student t1 WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))=MONTH(t1.`s_birth`);
-- 50、查询下月过生日的学生
SELECT t1.`s_id`,t1.`s_name`,t1.`s_birth` FROM student t1 WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1=MONTH(t1.`s_birth`);
