Mysql测试题03--50题

#创建数据库db_practices03
CREATE DATABASE IF NOT EXISTS `db_practices03`;

#使用数据库db_practices03
USE `db_practices03`;

#创建学生表tb_student
CREATE TABLE IF NOT EXISTS `tb_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`)
);

#创建课程表tb_course
CREATE TABLE IF NOT EXISTS `tb_course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);

#创建教师表tb_teacher
CREATE TABLE IF NOT EXISTS `tb_teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);

#创建成绩表tb_score
CREATE TABLE IF NOT EXISTS `tb_score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
#--------------------------------------------------------------------------------

#向tb_student表中插入数据
INSERT INTO `tb_student` VALUES('01' , '赵雷','1990-01-01','男'); 
INSERT INTO `tb_student` VALUES('02' , '钱电','1990-12-21','男'); 
INSERT INTO `tb_student` VALUES('03' , '孙风','1990-05-20','男'); 
INSERT INTO `tb_student` VALUES('04' , '李云','1990-08-06','男'); 
INSERT INTO `tb_student` VALUES('05' , '周梅','1991-12-01','女'); 
INSERT INTO `tb_student` VALUES('06' , '吴兰','1992-03-01','女'); 
INSERT INTO `tb_student` VALUES('07' , '郑竹','1989-07-01','女'); 
INSERT INTO `tb_student` VALUES('08' , '王菊','1990-01-20','女');

#向tb_course表中插入数据
INSERT INTO `tb_course` VALUES('01' , '语文' , '02'); 
INSERT INTO `tb_course` VALUES('02' , '数学' , '01'); 
INSERT INTO `tb_course` VALUES('03' , '英语' , '03');

#向tb_teacher表中插入数据
INSERT INTO `tb_teacher` VALUES('01' , '张三'); 
INSERT INTO `tb_teacher` VALUES('02' , '李四'); 
INSERT INTO `tb_teacher` VALUES('03' , '王五');

#向tb_score表中插入数据
INSERT INTO `tb_score` VALUES('01' , '01' , 80);
INSERT INTO `tb_score` VALUES('01' , '02' , 90);
INSERT INTO `tb_score` VALUES('01' , '03' , 99);
INSERT INTO `tb_score` VALUES('02' , '01' , 70);
INSERT INTO `tb_score` VALUES('02' , '02' , 60);
INSERT INTO `tb_score` VALUES('02' , '03' , 80);
INSERT INTO `tb_score` VALUES('03' , '01' , 80);
INSERT INTO `tb_score` VALUES('03' , '02' , 80);
INSERT INTO `tb_score` VALUES('03' , '03' , 80);
INSERT INTO `tb_score` VALUES('04' , '01' , 50);
INSERT INTO `tb_score` VALUES('04' , '02' , 30);
INSERT INTO `tb_score` VALUES('04' , '03' , 20);
INSERT INTO `tb_score` VALUES('05' , '01' , 76);
INSERT INTO `tb_score` VALUES('05' , '02' , 87);
INSERT INTO `tb_score` VALUES('06' , '01' , 31);
INSERT INTO `tb_score` VALUES('06' , '03' , 34);
INSERT INTO `tb_score` VALUES('07' , '02' , 89);
INSERT INTO `tb_score` VALUES('07' , '03' , 98);
#--------------------------------------------------------------------------------

#01、查询“01”课程比“02”课程成绩高的学生的信息及课程分数
SELECT * FROM  (
    SELECT t1.s_id, class1, class2 
    FROM
        (SELECT s_id, s_score AS class1 FROM tb_score WHERE c_id = '01') AS t1, 
        (SELECT s_id, s_score AS class2 FROM tb_score WHERE c_id = '02') AS t2
    WHERE t1.s_id = t2.s_id AND t1.class1 > t2.class2
) r 
LEFT JOIN tb_student
ON tb_student.s_id = r.s_id;

#02、查询“01”课程比“02”课程成绩低的学生的信息及课程分数(题目 1 是成绩高)
SELECT * FROM  (
    SELECT t1.s_id, class1, class2 
    FROM
        (SELECT s_id, s_score AS class1 FROM tb_score WHERE c_id = '01') AS t1, 
        (SELECT s_id, s_score AS class2 FROM tb_score WHERE c_id = '02') AS t2
    WHERE t1.s_id = t2.s_id AND t1.class1< t2.class2
) r 
LEFT JOIN tb_student
ON tb_student.s_id = r.s_id;

#03、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT tb_student.s_id, s_name, AVG(s_score) AVG
FROM tb_student, tb_score
WHERE tb_student.s_id = tb_score.s_id
GROUP BY tb_student.s_id
HAVING AVG(s_score) >= 60;

#04、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SELECT tb_student.s_id, s_name, AVG(s_score) AVG
FROM tb_student LEFT JOIN tb_score ON (tb_student.s_id = tb_score.s_id)
GROUP BY tb_student.s_id
HAVING AVG(s_score) < 60 OR AVG(s_score) IS NULL;

#05、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT tb_score.s_id AS ‘学号’, s_name AS ‘姓名’,COUNT(c_id)AS ‘选课数’,SUM(s_score)AS ‘总成绩’
FROM tb_score JOIN tb_student ON tb_score.s_id = tb_student.s_id
GROUP BY tb_score.s_id,s_name;

#06、查询“李”姓老师的数量
SELECT COUNT(t_id)
FROM tb_teacher
WHERE t_name LIKE '李%';

#07、查询学过张三老师授课的同学的信息
SELECT *
FROM tb_student
WHERE s_id IN
(SELECT s_id FROM tb_score JOIN tb_course ON tb_score.c_id = tb_course.c_id
JOIN tb_teacher ON tb_course.t_id = tb_teacher.t_id
WHERE t_name = '张三');

#08、找出没有学过张三老师课程的学生
SELECT *
FROM tb_student
WHERE s_id NOT IN
(SELECT s_id FROM tb_score JOIN tb_course ON tb_score.c_id = tb_course.c_id
JOIN tb_teacher ON tb_course.t_id = tb_teacher.t_id
WHERE t_name = '张三');

#09、查询学过编号为 01，并且学过编号为 02 课程的学生信息
SELECT *
FROM tb_student
WHERE s_id IN
(SELECT a.s_id FROM
(SELECT s_id FROM tb_score WHERE c_id = '01') AS a
JOIN (SELECT s_id FROM tb_score WHERE c_id ='02') AS b
ON a.s_id= b.s_id);

#10、查询学过 01 课程，但是没有学过 02 课程的学生信息(注意和上面9题目的区别)
SELECT * FROM tb_student AS s 
WHERE s.s_id IN
(SELECT s_id FROM tb_score AS c WHERE c.c_id =01) AND 
s.s_id NOT IN (SELECT c.s_id FROM tb_score AS c WHERE c.c_id =02);

#11、查询没有学完全部课程的同学的信息
SELECT *
FROM tb_student
WHERE s_id IN (SELECT s_id FROM tb_score GROUP BY s_id
HAVING COUNT(c_id)<(SELECT COUNT(c_id) FROM tb_course));
#12、查询至少有一门课与学号为 01 的同学所学相同的同学的信息
SELECT DISTINCT a.s_id, a.s_name
FROM tb_student a JOIN tb_score b ON a.s_id= b.s_id
WHERE c_id IN
(SELECT c_id FROM tb_score WHERE s_id = '01')
AND a.s_id<>'01';

#13、查询和 01 同学学习的课程完全相同的同学的信息
SELECT *FROM tb_student WHERE tb_student.`s_id` IN(
SELECT s_id
FROM tb_score
WHERE c_id IN
(SELECT c_id FROM tb_score WHERE s_id='01')
AND s_id <> '01'
GROUP BY s_id
HAVING COUNT(c_id)=(SELECT COUNT(c_id) FROM tb_score WHERE s_id='01'));
#14、查询没有修过张三老师讲授的任何一门课程的学生姓名
SELECT s_name
FROM tb_student stu
WHERE stu.`s_id` NOT IN (SELECT DISTINCT s1.`s_id` FROM tb_score s1
INNER JOIN tb_score s2 ON s1.`s_id` = s2.`s_id` AND s1.`c_id`!= s2.`c_id`
WHERE s1.`c_id` IN (SELECT c.`c_id`FROM tb_teacher t
LEFT JOIN tb_course c ON t.`t_id` = c.`t_id`
WHERE t.`t_name` = '张三')
AND s2.`c_id` IN (SELECT c.`c_id`FROM tb_teacher t
LEFT JOIN tb_course c ON t.`t_id` = c.`c_id`                                       
WHERE t.`t_name` = '张三'));                                                
                                 
#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT tb_student.s_id, tb_student.`s_name`,b.avg
FROM tb_student RIGHT JOIN
(SELECT s_id, AVG(s_score) AS AVG FROM tb_score
    WHERE s_id IN (
              SELECT s_id FROM tb_score     
              WHERE s_score<60     
              GROUP BY s_id 
              HAVING COUNT(s_score)>1)
    GROUP BY s_id) b ON tb_student.s_id=b.s_id;

#16、检索 01 课程分数小于 60，按分数降序排列的学生信息

SELECT tb_student.*, tb_score.s_score FROM tb_student, tb_score
WHERE tb_student.s_id = tb_score.s_id
AND tb_score.s_score < 60
AND c_id ='01'
ORDER BY tb_score.s_score DESC;
#17、按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩

SELECT *  FROM tb_score
LEFT JOIN (
    SELECT s_id,AVG(s_score) AS avscore FROM tb_score 
    GROUP BY s_id
    )r 
ON tb_score.s_id = r.s_id
ORDER BY avscore DESC;
#18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程 ID，课程 name，最高分，最低分，平均分，及格 率，中等率，优良率，优秀率;及格:>=60，中等为:70-80，优良为:80-90，优秀为:>=90
SELECT 
sc.c_id ,
MAX(sc.s_score)AS 最高分,
MIN(sc.s_score)AS 最低分,
AVG(sc.s_score)AS 平均分,
COUNT(*)AS 选修人数,
SUM(CASE WHEN sc.s_score>=60 THEN 1 ELSE 0 END )/COUNT(*)AS 及格率,
SUM(CASE WHEN sc.s_score>=70 AND sc.s_score<80 THEN 1 ELSE 0 END )/COUNT(*)AS 中等率,
SUM(CASE WHEN sc.s_score>=80 AND sc.s_score<90 THEN 1 ELSE 0 END )/COUNT(*)AS 优良率,
SUM(CASE WHEN sc.s_score>=90 THEN 1 ELSE 0 END )/COUNT(*)AS 优秀率 
FROM tb_score sc
GROUP BY sc.c_id
ORDER BY COUNT(*)DESC, sc.c_id ASC

#19、按照各科成绩进行排序，并且显示排名
SELECT c_id , s_id, s_score , RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS 排名
FROM  tb_score;

#20、查询学生的总成绩，并进行排名
SELECT b.s_id AS 学生id, b.total AS 总分, RANK() OVER (ORDER BY b.total DESC) AS 排名
FROM (SELECT s_id, SUM(s_score) total
      FROM tb_score
      GROUP BY s_id) b;

#21、查询不同老师所教不同课程平均分从高到低显示
SELECT tb_teacher.t_id, tb_course.c_id, AVG(s_score)
FROM tb_teacher, tb_score, tb_course
WHERE tb_teacher.t_id = tb_course.t_id AND tb_score.c_id = tb_course.c_id
GROUP BY c_id
ORDER BY AVG(s_score) DESC;

#22、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩

SELECT sc1.c_id, tb_student.*, sc1.s_score, COUNT(sc2.c_id) + 1 ranking
FROM tb_student, tb_score sc1 LEFT JOIN tb_score sc2 ON sc1.c_id = sc2.c_id AND sc1.s_score < sc2.s_score
WHERE tb_student.s_id = sc1.s_id
GROUP BY sc1.c_id, sc1.s_id
HAVING COUNT(sc2.c_id) = 1 OR COUNT(sc2.c_id) = 2
ORDER BY sc1.c_id, ranking;
#23、统计各科成绩各分数段人数:课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60] 及所占百分比
SELECT tb_score.c_id '课程ID', c_name '课程名称', 
((SELECT COUNT(s_id)
    FROM tb_score
    WHERE s_score >= 85 AND c_id = tb_course.c_id) / (SELECT COUNT(s_id)
                        FROM tb_score
                        WHERE c_id = tb_course.c_id)) "[85-100]",
((SELECT COUNT(s_id)
    FROM tb_score
    WHERE s_score >= 70 AND s_score < 85 AND c_id = tb_course.c_id) / (SELECT COUNT(s_id)
                        FROM tb_score
                        WHERE c_id = tb_course.c_id)) "[70-85]",
((SELECT COUNT(s_id)
    FROM tb_score
    WHERE s_score >= 60 AND s_score < 70 AND c_id = tb_course.c_id) / (SELECT COUNT(s_id)
                        FROM tb_score
                        WHERE c_id = tb_course.c_id)) "[60-70]",
((SELECT COUNT(s_id)
    FROM tb_score
    WHERE s_score < 60 AND c_id = tb_course.c_id) /(SELECT COUNT(s_id)
                        FROM tb_score
                        WHERE c_id = tb_course.c_id)) "[0-60]"
FROM tb_course LEFT JOIN tb_score ON tb_score.c_id = tb_course.c_id 
GROUP BY tb_course.c_id;

#24、查询学生的平均成绩及名次
SELECT tb_student.*, sc1.avg, COUNT(sc2.s_id) + 1 ranking
FROM tb_student, (SELECT s_id, AVG(s_score) AVG
            FROM tb_score
            GROUP BY s_id) AS sc1 LEFT JOIN (SELECT s_id, AVG(s_score) AVG
                                            FROM tb_score
                                            GROUP BY s_id) AS sc2 ON sc1.avg < sc2.avg
WHERE tb_student.s_id = sc1.s_id
GROUP BY sc1.s_id
ORDER BY ranking;

#25、查询各科成绩前三名的记录
SELECT * FROM
(SELECT c_id,s_id,s_score, ROW_NUMBER () OVER  ( PARTITION BY c_id ORDER BY s_score DESC ) AS ranks
FROM
tb_score)  b
WHERE b.ranks<=3;

#26、查询每门课被选修的学生数
SELECT c_id , COUNT(s_id) AS 选修学生数
FROM  tb_score
GROUP BY c_id;

#27、查询出只有两门课程的全部学生的学号和姓名
SELECT tb_score.s_id ,S.s_name, COUNT(c_id) AS count_cid
FROM  tb_score
LEFT JOIN tb_student S ON tb_score.s_id = S.s_id
GROUP BY s_id
HAVING count_cid=2;

#28、查询男女生人数
SELECT s_sex AS 性别,COUNT(s_sex) AS 人数
FROM tb_student
GROUP BY s_sex;

#29、查询名字中含有 风 字的学生信息
SELECT * FROM tb_student WHERE s_name LIKE '%风%';

#30、查询同名同性的学生名单，并统计同名人数
SELECT s_name,COUNT(s_name) AS 同名人数 FROM tb_student
GROUP BY s_name
HAVING COUNT(s_name)>1;

#31、查询 1990 年出生的学生信息
SELECT * FROM tb_student WHERE YEAR(s_birth) =1990;

#32、查询每门课程的平均成绩，结果按平均成绩降序排列;平均成绩相同时，按课程编号 c_id 升序排列
SELECT c_id,AVG(s_score) avg_score FROM tb_score GROUP BY c_id ORDER BY avg_score DESC, c_id ASC;

#33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT tb_score.s_id,S.s_name,AVG(s_score) avg_score FROM tb_score
LEFT JOIN tb_student S ON tb_score.s_id = S.s_id
GROUP BY tb_score.s_id
HAVING avg_score>85;

#34、查询课程名称为数学，且分数低于 60 的学生姓名和分数
SELECT tb_score.s_id,S.s_name,tb_score.s_score FROM tb_score
LEFT JOIN tb_student S ON tb_score.s_id = S.s_id
WHERE tb_score.c_id=(SELECT c_id FROM tb_course WHERE c_name='数学')
AND tb_score.s_score<60;

#35、查询所有学生的课程及分数情况
SELECT S.s_name,tb_score.c_id,C.c_name,tb_score.s_score FROM tb_student S
LEFT JOIN tb_score  ON S.s_id = tb_score.s_id
LEFT JOIN tb_course C ON tb_score.c_id = C.c_id;

#36、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT S.s_name,tb_score.c_id,C.c_name,tb_score.s_score FROM tb_student S
LEFT JOIN tb_score ON S.s_id = tb_score.s_id
LEFT JOIN tb_course C ON tb_score.c_id = C.c_id
WHERE tb_score.s_score>70;

#37、查询不及格的课程
SELECT tb_score.c_id,C.c_name FROM tb_score
LEFT JOIN tb_course C ON tb_score.c_id = C.c_id
WHERE tb_score.s_score<60
GROUP BY tb_score.c_id;

#38、查询课程编号为 01 且课程成绩大于等于 80 的学生的学号和姓名
SELECT tb_student.s_id,tb_student.s_name 
FROM tb_student,tb_score
WHERE c_id="01"
AND tb_score.`s_score`>=80
AND tb_student.s_id = tb_score.s_id;

#39、每门课程的学生人数
SELECT tb_score.c_id,COUNT(*) AS 学生人数
FROM tb_score
GROUP BY tb_score.c_id;
#40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩：
SELECT tb_student.s_id, s_name, s_birth, s_sex, s_score
FROM tb_student, tb_teacher, tb_score, (SELECT c_id, MAX(s_score) MAX
                               FROM tb_score
                               GROUP BY c_id) AS sc
WHERE t_name = "张三" AND tb_teacher.t_id = sc.c_id AND tb_score.s_id = tb_student.s_id AND tb_score.c_id = sc.c_id AND tb_score.s_score = MAX;

#41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT  a.c_id, a.s_id,  a.s_score FROM tb_score AS a
INNER JOIN 
tb_score AS b
ON a.s_id = b.s_id
AND a.c_id != b.c_id
AND a.s_score = b.s_score
GROUP BY c_id, s_id;

#42、查询每门功成绩最好的前两名
SELECT a.s_id,a.c_id,a.s_score FROM tb_score AS a 
LEFT JOIN tb_score AS b 
ON a.c_id = b.c_id AND a.s_score<b.s_score
GROUP BY a.c_id, a.s_id
HAVING COUNT(b.c_id)<2
ORDER BY a.c_id;

#43、统计每门课程的学生选修人数(超过 5 人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排列， 若人数相同，按课程号升序排列

SELECT tb_score.c_id, COUNT(s_id) AS cc FROM tb_score
GROUP BY c_id
HAVING cc >5;
#44、检索至少选修两门课程的学生学号
SELECT s_id, COUNT(c_id) AS cc FROM tb_score
GROUP BY s_id
HAVING cc>=2;

#45、查询选修了全部课程的学生信息
SELECT tb_student.*
FROM tb_score ,tb_student 
WHERE tb_score.s_id=tb_student.s_id
GROUP BY tb_score.s_id
HAVING COUNT(*) = (SELECT DISTINCT COUNT(*) FROM tb_course )

#46、查询各学生的年龄:按照出生日期来算，当前月日 < 出生年月的月日则，年龄减 1
SELECT tb_student.s_id AS 学生编号,tb_student.s_name  AS  学生姓名,
TIMESTAMPDIFF(YEAR,tb_student.`s_birth`,CURDATE()) AS 学生年龄
FROM tb_student

#47、查询本周过生日的学生
SELECT *
FROM tb_student 
WHERE WEEKOFYEAR(tb_student.`s_birth`)=WEEKOFYEAR(CURDATE());

#48、查询下周过生日的学生

SELECT *
FROM tb_student 
WHERE WEEKOFYEAR(tb_student.`s_birth`)=WEEKOFYEAR(CURDATE())+1;
#49、查询本月过生的同学

SELECT *
FROM tb_student 
WHERE MONTH(tb_student.`s_birth`)=MONTH(CURDATE());
#50、查询下月过生的同学
SELECT *
FROM tb_student 
WHERE MONTH(tb_student.`s_birth`)=MONTH(CURDATE())+1;