MySQL练习题

建表及数据

SHOW TABLES;
	SELECT database();
	SHOW DATABASES;
	CREATE DATABASE day05;
	USE day05;
	#1.创建学生表
	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', '女');
	#2.创建科目表
	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');
	#3.创建教师表
	CREATE TABLE Teacher
	(
	    TId   VARCHAR(10),
	    Tname VARCHAR(10)
	);
	INSERT INTO Teacher
	VALUES ('01', '张三');
	INSERT INTO Teacher
	VALUES ('02', '李四');
	INSERT INTO Teacher
	VALUES ('03', '王五');
	#4.创建成绩表
	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);
	DESC student;
	DESC course;
	DESC teacher;
	DESC sc;

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

SELECT *
FROM (SELECT c.CId, c.Cname, sc.score, s2.Sname name1
      FROM course c,
           sc sc
               RIGHT JOIN student s2 ON sc.SId = s2.SId
      WHERE c.CId = sc.CId
      GROUP BY c.CId, c.Cname, s2.Sname
      HAVING c.CId = '01') t1,
     (SELECT c.CId, c.Cname, sc.score, s.Sname name2
      FROM course c,
           student s,
           sc sc
      WHERE s.SId = sc.SId
        AND c.CId = sc.CId
      GROUP BY c.CId, c.Cname, s.Sname
      HAVING c.CId = '02') t2
WHERE t1.name1 = t2.name2
HAVING t1.score > t2.score;

SELECT st.*, class1, class2
FROM student st
         JOIN
     (SELECT *
      FROM (SELECT SId s1, score class1 FROM sc WHERE CId = '01') c1,
           (SELECT SId s2, score class2 FROM sc WHERE CId = '02') c2
      WHERE s1 = s2
        AND class1 > class2) c
     ON st.SId = c.s1;
SELECT *
FROM sc sc,
     course c
WHERE c.CId = sc.CId
  AND c.CId = '02';
SELECT *
FROM student;
SELECT *
FROM course;
SELECT *
FROM sc;

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

SELECT *
FROM (SELECT c.CId, c.Cname, sc.score, s2.Sname name1
      FROM course c,
           sc sc
               RIGHT JOIN student s2 ON sc.SId = s2.SId
      WHERE c.CId = sc.CId
      GROUP BY c.CId, c.Cname, s2.Sname
      HAVING c.CId = '01') t1,
     (SELECT c.CId, c.Cname, sc.score, s.Sname name2
      FROM course c,
           student s,
           sc sc
      WHERE s.SId = sc.SId
        AND c.CId = sc.CId
      GROUP BY c.CId, c.Cname, s.Sname
      HAVING c.CId = '02') t2
WHERE t1.name1 = t2.name2;

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

SELECT *
FROM ((SELECT * FROM sc WHERE CId = '01') t1
         LEFT JOIN
         (SELECT * FROM sc WHERE CId = '02') t2
         ON
             t1.Sid = t2.Sid);

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

SELECT *
FROM sc
WHERE sc.CId = '02'
  AND sc.SId NOT IN
      (SELECT SId FROM sc WHERE sc.CId = '01');

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT *, avg(sc.score) avgs
FROM student s,
     sc sc
WHERE s.SId = sc.SId
GROUP BY s.Sname
HAVING avgs > 60;

3.查询在 SC 表存在成绩的学生信息

SELECT *
FROM sc sc,
     student s
WHERE sc.SId = s.SId
GROUP BY s.Sname;
SELECT *
FROM student
WHERE SId IN (SELECT DISTINCT sid FROM sc);

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

SELECT s.SId, s.Sname, count(*) '选课总数', sum(sc.score) '成绩总和'
FROM student s,
     sc sc,
     course c
WHERE s.SId = sc.SId
  AND c.CId = sc.CId
GROUP BY s.Sname;

4.1显示没选课的学生(显示为NULL)

SELECT *
FROM student s,
     course c,
     sc sc
WHERE s.SId = sc.SId
  AND sc.CId = c.CId
GROUP BY c.Cname;
SELECT *
FROM (SELECT Sname, SId FROM student) t1
         LEFT JOIN
         (SELECT CId, SId FROM sc) t3 ON t1.SId = t3.sid
GROUP BY t1.SId;
SELECT st.sid,
       st.Sname,
       CASE WHEN COUNT(sc.SId) > 0 THEN COUNT(sc.SId) ELSE NULL END 'nums',
       SUM(sc.score)                                                'sum'
FROM student st
         LEFT JOIN sc ON st.SId = sc.SId
GROUP BY st.SId;

4.2查有成绩的学生信息

SELECT *
FROM (SELECT Sname, SId FROM student) t1
         LEFT JOIN
         (SELECT CId, SId, score FROM sc) t3 ON t1.SId = t3.sid
GROUP BY t3.SId
HAVING t3.score IS NOT NULL;
SELECT *
FROM student
WHERE SId IN (SELECT DISTINCT SId FROM sc);
SELECT *
FROM student s
WHERE exists(SELECT *FROM sc WHERE sc.SId = s.SId);
SELECT *
FROM student
WHERE SId IN (SELECT DISTINCT SId FROM sc); -- 适用于右表小
SELECT *
FROM student st
WHERE EXISTS(SELECT * FROM sc WHERE sc.SId = st.SId);

5.查询「李」姓老师的数量

SELECT count(*)
FROM teacher
WHERE Tname LIKE '李%';

6.查询学过「张三」老师授课的同学的信息

SELECT s.SId, s.Sname, s.Sage, s.Ssex, t.Tname
FROM teacher t,
     student s,
     course c,
     sc sc
WHERE s.SId = sc.sid
  AND sc.CId = c.CId
  AND c.TId = t.TId
  AND t.Tname = '张三';

7.查询没有学全所有课程的同学的信息

SELECT t1.SId, t1.Sname, t1.Ssex, t1.Sage, count(*)
FROM student t1
         LEFT JOIN
     sc t2 ON t1.SId = t2.SId
GROUP BY t1.SId
HAVING count(*) < 3;

SELECT *
FROM student
WHERE sid NOT IN (SELECT sid FROM sc GROUP BY sid HAVING count(*) = (SELECT count(*) FROM course));
SELECT *
FROM student
WHERE SId NOT IN (
    SELECT SId FROM sc GROUP BY SId HAVING COUNT(*) = (SELECT COUNT(*) FROM course));

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT *
FROM student
WHERE sid IN (SELECT sid FROM sc WHERE cid IN (SELECT CId FROM sc WHERE sid = '01'));

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

解法一

SELECT *
FROM student
WHERE SId IN (SELECT sid
              FROM (SELECT sid, group_concat(cid ORDER BY cid) cc FROM sc GROUP BY sid) tt
              WHERE tt.cc IN (SELECT group_concat(CId ORDER BY CId) FROM sc WHERE sid = '01'));

解法二

SELECT *
FROM student
WHERE SId IN
      (SELECT SId
       FROM sc
       WHERE CId IN (SELECT CId FROM sc WHERE SId = '01')
         AND SId <> '01'
       GROUP BY SId
       HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE SId = '01'));

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT *
FROM student
WHERE sid NOT IN (
    SELECT sid
    FROM sc
    WHERE CId IN (
        SELECT c.CId
        FROM course c,
             teacher t
        WHERE c.CId = t.TId
          AND t.Tname = '张三'));

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT *
FROM (SELECT s.SId, s.Sname, s.Sage, s.Ssex, count(*) x, avg(score)
      FROM sc,
           student s
      WHERE score <= 60
        AND s.SId = sc.sid
      GROUP BY SId) t
WHERE t.x >= 2;
SELECT st.SId, sname, avg(score)
FROM student st
         JOIN sc ON st.sid = sc.SId
GROUP BY st.SId
HAVING COUNT(score <= 60 OR NULL) >= 2;

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT s.SId, s.Sname, s.Sage, s.Ssex, t.score
FROM (SELECT * FROM sc WHERE score < 60 AND CId = '01' GROUP BY SId) t,
     student s
WHERE t.sid = s.SId
ORDER BY t.score DESC;

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT *
FROM sc s
         LEFT JOIN
         (SELECT *, avg(score) avg FROM sc GROUP BY sc.SId) t ON s.sid = t.sid
ORDER BY avg DESC;
SELECT *
FROM sc
         JOIN
         (SELECT SId, avg(score) avg FROM sc GROUP BY SId) avgs
         ON sc.SId = avgs.SId
ORDER BY avgs.avg DESC;

14.查询各科成绩最高分、最低分和平均分

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT c.CId,
       c.Cname,
       count(*)                                             ss,
       max(score),
       min(score),
       avg(score),
       COUNT(score >= 60 OR NULL) / COUNT(*)                '及格率',
       COUNT(score >= 70 AND score < 80 OR NULL) / COUNT(*) '中等率',
       COUNT(score >= 80 AND score < 90 OR NULL) / COUNT(*) '优良率',
       COUNT(score >= 90 OR NULL) / COUNT(*)                '优秀率'
FROM course c,
     sc
WHERE c.cid = sc.CId
GROUP BY c.CId, c.Cname
ORDER BY count(*) DESC, c.CId ASC;

15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

SELECT cid, sid, score, rank() OVER (PARTITION BY CId ORDER BY score DESC ) rank_socre
FROM sc
ORDER BY CId;

16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SELECT tt.SId, tt.sum_socre, dense_rank() OVER (ORDER BY tt.sum_socre DESC ) st_rank
FROM (SELECT sid, sum(score) sum_socre FROM sc GROUP BY sid) tt
ORDER BY tt.sum_socre DESC;

17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT c.cid,
       c.Cname,
       count(*),
       count(85 < score OR NULL) / count(*)                 '[100-85]占比',
       count(score < 85 AND score >= 70 OR NULL) / count(*) '[85-70]占比',
       count(score < 70 AND score >= 60 OR NULL) / count(*) '[70-60]占比',
       count(score < 60 OR NULL) / count(*)                 '[60-0]占比'
FROM sc,
     course c
WHERE sc.CId = c.CId
GROUP BY c.CId;

18. 查询各科成绩前三名的记录

SELECT *
FROM (
         SELECT cid, sid, score, rank() OVER (PARTITION BY cid ORDER BY score DESC ) course_rank
         FROM sc
         ORDER BY CId ASC) t
WHERE t.course_rank <= 3;
SELECT o.*
FROM sc o
WHERE (SELECT COUNT(*) FROM sc WHERE o.CId = CId AND o.score < score) + 1
ORDER BY CId, score DESC, SId;

19.查询每门课程被选修的学生数

SELECT count(*)
FROM sc
GROUP BY CId;

20.查询出只选修两门课程的学生学号和姓名

SELECT s.sid, s.Sname
FROM sc,
     student s
WHERE sc.SId = s.SId
GROUP BY SId
HAVING count(*) = 2;

21.查询男生、女生人数

SELECT ssex, count(*)
FROM student
GROUP BY Ssex;

22.查询名字中含有「风」字的学生信息

SELECT *
FROM student
WHERE Sname LIKE '%风%'

23.查询同名学生名单，并统计同名人数

SELECT Sname, count(*)
FROM student
GROUP BY Sname
HAVING count(*) > 1;

24.查询 1990 年出生的学生名单

SELECT *
FROM student
WHERE year(sage) = '1990';

25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT cid, avg(score) avg_score
FROM sc
GROUP BY cid
ORDER BY avg_score DESC, cid;

26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT s.SId, s.Sname, avg(score) avg_score
FROM sc,
     student s
WHERE sc.SId = s.SId
GROUP BY sid
HAVING avg_score >= 85;

27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT s.sid, s.Sname, score
FROM course c,
     sc,
     student s
WHERE c.Cname = '数学'
  AND c.cid = sc.CId
  AND score < 60
  AND s.sid = sc.SId;

28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT s.SId, s.Sname, score, cid, (SELECT c.Cname FROM course c WHERE c.CId = sc.cid) name
FROM student s
         LEFT JOIN sc ON s.SId = sc.SId;

29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

这个我没太理解，理解一是任意一门成绩均在70分以上

SELECT s.sid, s.sname, c.Cname, score
FROM sc,
     course c,
     student s
WHERE score > 70
  AND c.CId = sc.CId
  AND s.SId = sc.SId;
SELECT st.Sname, c.Cname, s.score
FROM student st,
     sc s,
     course c
WHERE st.SId = s.SId
  AND s.cid = c.cid
  AND st.SId IN
      (SELECT sid FROM sc GROUP BY SId HAVING MIN(score) > 70);

理解二是存在一门成绩在70分以上即可满足条件

理解三就是找出所有大于70分的得分。

30.查询存在不及格的课程

SELECT cid FROM sc GROUP BY cid HAVING min(score)<60;

31.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名

SELECT s.sid, s.sname FROM sc,student s where sc.CId=01 and sc.score>=80 and s.SId=sc.sid;

32.求每门课程的学生人数

SELECT cid,count(*) FROM sc GROUP BY cid;

33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT s.sid,s.Sname,s.Ssex,s.Sage ,max(score) score FROM sc ,student s where CId=(SELECT cid FROM teacher t,course c  where t.Tname='张三' and t.TId=CId)
and s.sid=sc.SId;

34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT s.sid,s.Sname,s.Ssex,s.Sage ,max(score) OVER () score FROM sc ,student s where CId=(SELECT cid FROM teacher t,course c  where t.Tname='张三' and t.TId=CId)
and s.sid=sc.SId;

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

这个问题其实一开始没太明白啥意思，后来理解为某个人的几科分数是一样的，需要把这个人找出来

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 cid, sid;

36.查询每门功成绩最好的前两名

SELECT o.* FROM sc o where (SELECT COUNT(*) FROM sc WHERE CId = o.CId AND score > o.score) + 1 <= 2  ORDER BY o.cid, sid;

37.统计每门课程的学生选修人数（超过 5 人的课程才统计）

SELECT sc.CId,count(*) FROM sc GROUP BY CId HAVING count(*)>5 ;

38.检索至少选修两门课程的学生学号

SELECT sid,count(*) FROM sc GROUP BY SId HAVING count(*)>=2;

39.查询选修了全部课程的学生信息

SELECT *,count(*) FROM sc GROUP BY sid HAVING  count(*)=3;

40.查询各学生的年龄，只按年份来算

SELECT year(Sage) FROM student;

41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

SELECT SId '学生编号', Sname '学生姓名', TIMESTAMPDIFF(YEAR, Sage, NOW()) '学生年龄'
FROM student;

42.查询本周过生日的学生

有点复杂，需要拼接出本周的起止日期

SELECT * FROM student
WHERE DATE(CONCAT(YEAR(NOW()),'-',MONTH(Sage), '-', DAY(Sage)))
BETWEEN DATE(DATE_SUB(NOW(),INTERVAL WEEKDAY(NOW()) DAY))
AND DATE(DATE_ADD(NOW(),INTERVAL 6 - WEEKDAY(NOW()) DAY));
SELECT * FROM student;

43. 查询下周过生日的学生

同42

SELECT * FROM student
WHERE DATE(CONCAT(YEAR(NOW()),'-',MONTH(Sage), '-', DAY(Sage)))
BETWEEN DATE(DATE_SUB(NOW(),INTERVAL WEEKDAY(NOW()) - 7 DAY))
AND DATE(DATE_ADD(NOW(),INTERVAL 13 - WEEKDAY(NOW()) DAY));

44.查询本月过生日的学生

SELECT * FROM student WHERE month(sage) = month(NOW());

45.查询下月过生日的学生

注意本月是12月的话，下一个月份是1即可

SELECT * FROM student WHERE month(Sage) = (CASE WHEN month(NOW()) = 12 THEN 1 ELSE MONTH(NOW()) + 1 END);