Mysql练习题

 先创建对应数据表

#先创建表
#学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');#科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');#教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');#成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

 1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
 使用子查询分别获取01和02课程的成绩，然后进行比较，最后与Student表进行JOIN获取学生信息
SELECT * 
FROM Student 
RIGHT JOIN (
    SELECT t1.SId, class1, class2 
    FROM (
        SELECT SId, score AS class1 FROM sc WHERE sc.CId = '01'
    ) AS t1, 
    (
        SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02'
    ) AS t2
    WHERE t1.SId = t2.SId AND t1.class1 > t2.class2
) r 
ON Student.SId = r.SId;

 1.1 查询同时存在"01"课程和"02"课程的情况
 使用INNER JOIN获取同时选修了01和02课程的学生
SELECT * 
FROM (
    SELECT * FROM sc WHERE sc.CId = '01'
) AS t1, 
(
    SELECT * FROM sc WHERE sc.CId = '02'
) AS t2
WHERE t1.SId = t2.SId;

 1.2 查询存在"01"课程但可能不存在"02"课程的情况
 使用LEFT JOIN保留01课程的所有学生，02课程不存在时显示为NULL
SELECT * 
FROM (
    SELECT * FROM sc WHERE sc.CId = '01'
) AS t1
LEFT JOIN 
(
    SELECT * FROM sc WHERE sc.CId = '02'
) AS t2
ON t1.SId = t2.SId;

 1.3 查询不存在"01"课程但存在"02"课程的情况
 使用NOT IN排除选修了01课程的学生
SELECT * FROM sc
WHERE sc.SId NOT IN (
    SELECT SId FROM sc 
    WHERE sc.CId = '01'
) 
AND sc.CId = '02';

 2. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
 使用GROUP BY和HAVING计算每个学生的平均成绩，并筛选出大于60分的学生
SELECT Student.SId, Student.Sname, r.ss 
FROM Student 
RIGHT JOIN (
    SELECT SId, AVG(score) AS ss FROM sc
    GROUP BY SId
    HAVING AVG(score) >= 60
) r 
ON Student.SId = r.SId;

 3. 查询在SC表存在成绩的学生信息
 使用DISTINCT和JOIN获取在SC表中有成绩的学生信息
SELECT DISTINCT student.*
FROM student, sc
WHERE student.SId = sc.SId;

 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
 使用LEFT JOIN确保显示所有学生，即使他们没有选课
SELECT s.sid, s.sname, r.coursenumber, r.scoresum
FROM (
    SELECT student.sid, student.sname 
    FROM student
) s 
LEFT JOIN 
(
    SELECT sc.sid, SUM(sc.score) AS scoresum, COUNT(sc.cid) AS coursenumber
    FROM sc 
    GROUP BY sc.sid
) r 
ON s.sid = r.sid;

 4.2 查有成绩的学生信息
 使用EXISTS和IN关键字查询有成绩的学生
 EXISTS用于检查子查询是否至少会返回一行数据
 IN适合小表，EXISTS适合大表
SELECT * FROM student 
WHERE EXISTS (SELECT sc.sid FROM sc WHERE student.sid = sc.sid);

 5. 查询「李」姓老师的数量
 使用LIKE匹配姓氏为'李'的老师
SELECT COUNT(*)
FROM teacher
WHERE tname LIKE '李%';

 6. 查询学过「张三」老师授课的同学的信息
 多表JOIN查询张三老师授课的学生信息
SELECT student.* 
FROM student, teacher, course, sc
WHERE 
    student.sid = sc.sid 
    AND course.cid = sc.cid 
    AND course.tid = teacher.tid 
    AND tname = '张三';

 7. 查询没有学全所有课程的同学的信息
 使用NOT IN排除选了所有课程的学生
SELECT * FROM student
WHERE student.sid NOT IN (
    SELECT sc.sid FROM sc
    GROUP BY sc.sid
    HAVING COUNT(sc.cid) = (SELECT COUNT(cid) FROM course)
);

 8. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
 使用IN子查询查找与01号同学有相同课程的学生
SELECT * FROM student 
WHERE student.sid IN (
    SELECT sc.sid FROM sc 
    WHERE sc.cid IN (
        SELECT sc.cid FROM sc 
        WHERE sc.sid = '01'
    )
);

 9. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT s.*
FROM student s
WHERE s.sid IN (
    SELECT sc.sid
    FROM sc
    WHERE sc.cid IN (
        SELECT sc.cid
        FROM sc
        WHERE sc.sid = '01'
    )
    GROUP BY sc.sid
    HAVING COUNT(DISTINCT sc.cid) = (
        SELECT COUNT(DISTINCT cid)
        FROM sc
        WHERE sid = '01'
    )
) AND s.sid != '01';

 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
 使用NOT IN排除学过张三老师课程的学生
SELECT * FROM student
WHERE student.sid NOT IN (
    SELECT sc.sid FROM sc WHERE sc.cid IN (
        SELECT course.cid FROM course WHERE course.tid IN (
            SELECT teacher.tid FROM teacher WHERE tname = "张三"
        )
    )
);

 11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
 使用子查询统计不及格课程数量，并计算平均成绩
SELECT student.SId, student.Sname, b.avg
FROM student 
RIGHT JOIN (
    SELECT sid, AVG(score) AS avg 
    FROM sc
    WHERE sid IN (
        SELECT sid FROM sc 
        WHERE score < 60 
        GROUP BY sid 
        HAVING COUNT(score) > 1
    )
    GROUP BY sid
) b ON student.sid = b.sid;

 12. 检索"01"课程分数小于60，按分数降序排列的学生信息
 使用ORDER BY按分数降序排列
SELECT student.*, sc.score 
FROM student, sc
WHERE student.sid = sc.sid
AND sc.score < 60
AND cid = "01"
ORDER BY sc.score DESC;

 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
 使用LEFT JOIN添加平均成绩，并按平均成绩排序
SELECT *  
FROM sc 
LEFT JOIN (
    SELECT sid, AVG(score) AS avscore FROM sc 
    GROUP BY sid
) r 
ON sc.sid = r.sid
ORDER BY avscore DESC;

 14. 查询各科成绩最高分、最低分和平均分
 使用聚合函数和CASE语句计算各统计值
SELECT 
    sc.CId,
    MAX(sc.score) AS 最高分,
    MIN(sc.score) AS 最低分,
    AVG(sc.score) AS 平均分,
    COUNT(*) AS 选修人数,
    SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS 及格率,
    SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END) / COUNT(*) AS 中等率,
    SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优良率,
    SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优秀率 
FROM sc
GROUP BY sc.CId
ORDER BY COUNT(*) DESC, sc.CId ASC;

 15. 按各科成绩进行排序，并显示排名
 使用自连接计算每门课程的成绩排名
SELECT a.cid, a.sid, a.score, COUNT(b.score) + 1 AS rank
FROM sc AS a 
LEFT JOIN sc AS b 
ON a.score < b.score AND a.cid = b.cid
GROUP BY a.cid, a.sid, a.score
ORDER BY a.cid, rank ASC;

 16. 查询学生的总成绩，并进行排名
 使用变量计算总成绩排名
SET @crank = 0;
SELECT q.sid, total, @crank := @crank + 1 AS rank 
FROM (
    SELECT sc.sid, SUM(sc.score) AS total FROM sc
    GROUP BY sc.sid
    ORDER BY total DESC
) q;

 17. 统计各科成绩各分数段人数
 使用CASE WHEN返回1后用SUM统计各分数段人数
SELECT course.cname, course.cid,
SUM(CASE WHEN sc.score <= 100 AND sc.score > 85 THEN 1 ELSE 0 END) AS "[10085]",
SUM(CASE WHEN sc.score <= 85 AND sc.score > 70 THEN 1 ELSE 0 END) AS "[8570]",
SUM(CASE WHEN sc.score <= 70 AND sc.score > 60 THEN 1 ELSE 0 END) AS "[7060]",
SUM(CASE WHEN sc.score <= 60 AND sc.score > 0 THEN 1 ELSE 0 END) AS "[600]"
FROM sc LEFT JOIN course
ON sc.cid = course.cid
GROUP BY sc.cid;

 18. 查询各科成绩前三名的记录
 使用自连接计算每门课程的成绩排名，筛选出排名前三的记录
SELECT a.sid, a.cid, a.score 
FROM sc AS a 
LEFT JOIN sc AS b 
ON a.cid = b.cid AND a.score < b.score
GROUP BY a.cid, a.sid
HAVING COUNT(b.cid) < 3
ORDER BY a.cid;

 19. 查询每门课程被选修的学生数
 使用GROUP BY统计每门课程的学生数
SELECT cid, COUNT(sid) FROM sc 
GROUP BY cid;

 20. 查询出只选修两门课程的学生学号和姓名
 使用HAVING子句筛选出选修两门课程的学生
SELECT student.sid, student.sname 
FROM student
WHERE student.sid IN (
    SELECT sc.sid FROM sc
    GROUP BY sc.sid
    HAVING COUNT(sc.cid) = 2
);

 21. 查询男生、女生人数
 使用GROUP BY统计男生和女生的人数
SELECT ssex, COUNT(*) FROM student
GROUP BY ssex;

 22. 查询名字中含有「风」字的学生信息
 使用LIKE匹配名字中包含「风」字的学生
SELECT *
FROM student 
WHERE student.Sname LIKE '%风%';

 23. 查询同名学生名单，并统计同名人数
 使用GROUP BY和HAVING统计同名学生
SELECT sname, COUNT(*) 
FROM student
GROUP BY sname
HAVING COUNT(*) > 1;

 24. 查询1990年出生的学生名单
 使用YEAR函数提取出生年份
SELECT *
FROM student
WHERE YEAR(student.Sage) = 1990;

 25. 查询每门课程的平均成绩，结果按平均成绩降序排列
 使用GROUP BY和ORDER BY计算每门课程的平均成绩
SELECT sc.cid, course.cname, AVG(SC.SCORE) AS average 
FROM sc, course
WHERE sc.cid = course.cid
GROUP BY sc.cid 
ORDER BY average DESC, cid ASC;

 26. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
 使用HAVING子句筛选平均成绩大于85的学生
SELECT student.sid, student.sname, AVG(sc.score) AS aver 
FROM student, sc
WHERE student.sid = sc.sid
GROUP BY sc.sid
HAVING aver > 85;

 27. 查询课程名称为「数学」，且分数低于60的学生姓名和分数
 多表JOIN查询数学课程分数低于60的学生
SELECT student.sname, sc.score 
FROM student, sc, course
WHERE student.sid = sc.sid
AND course.cid = sc.cid
AND course.cname = "数学"
AND sc.score < 60;

 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
 使用LEFT JOIN确保显示所有学生
SELECT student.sname, cid, score 
FROM student
LEFT JOIN sc
ON student.sid = sc.sid;

 29. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
 多表JOIN查询成绩在70分以上的学生信息
SELECT student.sname, course.cname, sc.score 
FROM student, course, sc
WHERE sc.score > 70
AND student.sid = sc.sid
AND sc.cid = course.cid;

 30. 查询存在不及格的课程
 使用DISTINCT获取不及格的课程
SELECT DISTINCT sc.CId
FROM sc
WHERE sc.score < 60;

 31. 查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名
 查询01号课程成绩在80分及以上的学生
SELECT student.sid, student.sname 
FROM student, sc
WHERE cid = "01"
AND score >= 80
AND student.sid = sc.sid;

 32. 求每门课程的学生人数
 使用GROUP BY统计每门课程的学生人数
SELECT sc.CId, COUNT(*) AS 学生人数
FROM sc
GROUP BY sc.CId;

 33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
 使用ORDER BY和LIMIT获取最高分的学生
SELECT student.*, sc.score, sc.cid 
FROM student, teacher, course, sc 
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
ORDER BY score DESC
LIMIT 1;

 34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
 使用子查询获取最高分，然后筛选出所有达到该分数的学生
SELECT student.*, sc.score, sc.cid 
FROM student, teacher, course, sc 
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
AND sc.score = (
    SELECT MAX(sc.score) 
    FROM sc, student, teacher, course
    WHERE teacher.tid = course.tid
    AND sc.sid = student.sid
    AND sc.cid = course.cid
    AND teacher.tname = "张三"
);

 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
 使用INNER JOIN查找不同课程中成绩相同的学生
SELECT a.cid, a.sid, a.score 
FROM sc AS a
INNER JOIN sc AS b
ON a.sid = b.sid
AND a.cid != b.cid
AND a.score = b.score
GROUP BY a.cid, a.sid;

 36. 查询每门课程成绩最好的前两名
 使用自连接计算每门课程的成绩排名，筛选出排名前两名的记录
SELECT a.sid, a.cid, a.score 
FROM sc AS a 
LEFT JOIN sc AS b 
ON a.cid = b.cid AND a.score < b.score
GROUP BY a.cid, a.sid
HAVING COUNT(b.cid) < 2
ORDER BY a.cid;

 37. 统计每门课程的学生选修人数（超过5人的课程才统计）
 使用HAVING子句筛选出选修人数超过5人的课程
SELECT sc.cid, COUNT(sid) AS cc 
FROM sc
GROUP BY cid
HAVING cc > 5;

 38. 检索至少选修两门课程的学生学号
 使用HAVING子句筛选出选修两门及以上课程的学生
SELECT sid, COUNT(cid) AS cc 
FROM sc
GROUP BY sid
HAVING cc >= 2;

 39. 查询选修了全部课程的学生信息
 使用GROUP BY和HAVING确保学生选修了所有课程
SELECT student.*
FROM sc, student 
WHERE sc.SId = student.SId
GROUP BY sc.SId
HAVING COUNT(*) = (SELECT COUNT(*) FROM course);

 40. 查询各学生的年龄，只按年份来算
 使用TIMESTAMPDIFF计算年龄
SELECT student.SId AS 学生编号, student.Sname AS 学生姓名,
TIMESTAMPDIFF(YEAR, student.Sage, CURDATE()) AS 学生年龄
FROM student;

 41. 查询本周过生日的学生
 使用WEEKOFYEAR函数匹配本周生日
SELECT *
FROM student 
WHERE WEEKOFYEAR(student.Sage) = WEEKOFYEAR(CURDATE());

 42. 查询下周过生日的学生
 使用WEEKOFYEAR函数匹配下周生日
SELECT *
FROM student 
WHERE WEEKOFYEAR(student.Sage) = WEEKOFYEAR(CURDATE()) + 1;

 43. 查询本月过生日的学生
 使用MONTH函数匹配本月生日
SELECT *
FROM student 
WHERE MONTH(student.Sage) = MONTH(CURDATE());

 44. 查询下月过生日的学生
 使用MONTH函数匹配下月生日
SELECT *
FROM student 
WHERE MONTH(student.Sage) = MONTH(CURDATE()) + 1;