MySQL练习题（二）

环境准备

创建学生、课程、成绩和教师表并插入数据

CREATE TABLE student
(
    sid   INT PRIMARY KEY,
    sname VARCHAR(20) UNIQUE NOT NULL,
    sage  INT,
    ssex  CHAR(2)
);

CREATE TABLE course
(
    cid   VARCHAR(20) PRIMARY KEY,
    cname VARCHAR(20) UNIQUE NOT NULL,
    tid   INT
);

CREATE TABLE sc
(
    sid   INT,
    cid   VARCHAR(20),
    score INT,
    PRIMARY KEY (sid, cid)
);

CREATE TABLE teacher
(
    tid   INT PRIMARY KEY,
    tname VARCHAR(20),
    title VARCHAR(20)
);
-- 插入学生数据
INSERT INTO student (sid, sname, sage, ssex)
VALUES (1001, '王晓芳', 20, '女'),
       (1002, '李秋枫', 21, '男'),
       (1003, '高山', 22, '男'),
       (1004, '张纯玉', 20, '男'),
       (1005, '张冬云', 21, '女'),
       (1006, '王天仪', 22, '男'),
       (1007, '赵风雨', 21, '男'),
       (1008, '白馨', 21, '女'),
       (1009, '欧阳春岚', 22, '女'),
       (1010, '曾鹏程', 22, '男');

-- 插入教师数据
INSERT INTO teacher (tid, tname, title)
VALUES (10101, '王彤', '教授'),
       (10102, '孔世杰', '副教授'),
       (10103, '王山', '助教'),
       (10104, '叶平', '教授'),
       (10105, '杨文华', '讲师');

-- 插入课程数据
INSERT INTO course (cid, cname, tid)
VALUES ('001', '计算机', 10104),
       ('002', '自动化', 10102),
       ('003', '机电工程', 10104),
       ('004', 'C语言', 10104),
       ('005', '离散数学', 10101),
       ('006', '理论力学', 10105);

-- 插入成绩数据
INSERT INTO sc (sid, cid, score)
VALUES (1001, '001', 80),
       (1001, '002', 70),
       (1002, '001', 58),
       (1002, '002', 60),
       (1002, '003', 57),
       (1003, '002', 75),
       (1004, '001', 58),
       (1004, '002', 58),
       (1005, '005', 80),
       (1006, '006', 43),
       (1001, '005', 80),
       (1006, '005', 75),
       (1004, '006', 80),
       (1007, '003', 58);

(1)、查询“001”课程比“002”课程成绩高的所有学生的学号

1. 在子查询中，通过自连接 JOIN 将 sc 表关联两次，分别取别名 sc1 和 sc2。
2. sc1 对应 001 课程，sc2 对应 002 课程，并且通过 sc1.sid = sc2.sid 确保比较的是同一个学生的成绩。
3. WHERE sc1.cid = ‘001’ AND sc2.cid = ‘002’ AND sc1.score > sc2.score 用于筛选出在 001 课程中成绩高于 002 课程成绩的学生。
4. 最后在主查询中通过 WHERE s.sid IN (…) 获取这些学生的学号。

SELECT s.sid
FROM student AS s
WHERE s.sid IN (SELECT sc1.sid
                FROM sc AS sc1
                         JOIN sc AS sc2 ON sc1.sid = sc2.sid
                WHERE sc1.cid = '001'
                  AND sc2.cid = '002'
                  AND sc1.score > sc2.score);

(2)、查询姓“王”的老师的个数

select count(*) as "王老师"
from teacher
where tname like '王%';

(3)、查询没学过“叶平”老师课的同学的学号、姓名

-- 选择需要的列：学生编号（s.sid）、学生姓名（s.sname）
SELECT s.sid, s.sname
FROM student s
-- 条件：学生编号不在子查询的结果集中
WHERE s.sid NOT IN (
    -- 子查询：选择学习“叶平”老师课程的学生编号（sc.sid）
    SELECT sc.sid
    FROM sc
             -- 将成绩表（sc）与课程表（course）进行连接，连接条件是成绩表的课程编号（cid）与课程表的课程编号（cid）相等
             JOIN course c ON sc.cid = c.cid
        -- 将课程表（course）与教师表（teacher）进行连接，连接条件是课程表的教师编号（tid）与教师表的教师编号（tid）相等
             JOIN teacher t ON c.tid = t.tid
    -- 只选择教师名称为 '叶平' 的记录
    WHERE t.tname = '叶平')
-- 按学生编号降序排列
ORDER BY s.sid DESC;

SELECT s.sid, s.sname
FROM student s
WHERE s.sid NOT IN (SELECT sc.sid
                    FROM sc
                             JOIN course c ON sc.cid = c.cid
                             JOIN teacher t ON c.tid = t.tid
                    WHERE t.tname = '叶平')
order by s.sid desc;

(4)、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

-- 选择需要的列：学生编号（s.sid）、学生姓名（s.sname）、学生年龄（s.sage）、学生性别（s.ssex）
SELECT s.sid, s.sname, s.sage, s.ssex
FROM student s
-- 条件：学生编号在子查询1的结果集中，并且在子查询2的结果集中
WHERE s.sid IN (
    -- 子查询1：选择成绩表中课程编号为 '001' 的学生编号
    SELECT sid
    FROM sc
    WHERE cid = '001'
)
AND s.sid IN (
    -- 子查询2：选择成绩表中课程编号为 '002' 的学生编号
    SELECT sid
    FROM sc
    WHERE cid = '002'
);

SELECT s.sid, s.sname, s.sage, s.ssex
FROM student s
WHERE s.sid IN (SELECT sid
                FROM sc
                WHERE cid = '001')
  AND s.sid IN (SELECT sid
                FROM sc
                WHERE cid = '002');

(5)、查询所有课程成绩小于60分的同学的学号、姓名

-- 选择需要的列：学生编号（s.sid）、学生姓名（s.sname）
SELECT s.sid, s.sname
FROM student s
-- 将学生表（student）与成绩表（sc）进行连接，连接条件是学生表的学生编号（sid）与成绩表的学生编号（sid）相等
         INNER JOIN sc ON s.sid = sc.sid
-- 按学生编号（s.sid）、学生姓名（s.sname）进行分组
GROUP BY s.sid, s.sname
-- 只选择所有课程成绩都小于60分的学生，条件是该学生的所有课程成绩的最大值小于60
HAVING MAX(sc.score) < 60;

SELECT s.sid, s.sname
FROM student s
         INNER JOIN sc ON s.sid = sc.sid
GROUP BY s.sid, s.sname
HAVING MAX(sc.score) < 60;

(6)、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名

-- 选择需要的列：学生编号（s.sid）、学生姓名（s.sname）
SELECT DISTINCT s.sid, s.sname
FROM student s
-- 将学生表（student）与成绩表（sc1）进行连接，连接条件是学生表的学生编号（sid）与成绩表的学生编号（sid）相等
         INNER JOIN sc sc1 ON s.sid = sc1.sid
-- 将成绩表（sc1）与成绩表（sc2）进行连接，连接条件是成绩表的课程编号（cid）相等
         INNER JOIN sc sc2 ON sc1.cid = sc2.cid
-- 只选择成绩表（sc2）的学生编号为 1001 的记录，并排除学生编号为 1001 的自身记录
WHERE sc2.sid = 1001
  AND s.sid != 1001;

SELECT DISTINCT s.sid, s.sname
FROM student s
         INNER JOIN sc sc1 ON s.sid = sc1.sid
         INNER JOIN sc sc2 ON sc1.cid = sc2.cid
WHERE sc2.sid = 1001
  AND s.sid != 1001;

(7)、删除学习“叶平”老师课的SC表记录

-- 从成绩表（sc）中删除记录，条件是课程编号（cid）在子查询中
DELETE FROM sc
WHERE cid IN (
    -- 子查询：选择课程编号（c.cid）
    SELECT c.cid
    FROM course c
    -- 将课程表（course）与教师表（teacher）进行连接，连接条件是课程表的教师编号（tid）与教师表的教师编号（tid）相等
    INNER JOIN teacher t ON c.tid = t.tid
    -- 只选择教师名称为 '叶平' 的记录
    WHERE t.tname = '叶平'
);

DELETE
FROM sc
WHERE cid IN (SELECT c.cid
              FROM course c
                       INNER JOIN teacher t ON c.tid = t.tid
              WHERE t.tname = '叶平');

(8)、查询没有代课的老师有哪些

select *
from teacher as t
         left join course as c on t.tid = c.tid;

select t.tname
from teacher as t
         left join course as c on t.tid = c.tid
where c.tid is null;

(9)、查询只上“002”课的同学信息

这些学生都上了002课程

SELECT *
FROM student s
WHERE s.sid IN (SELECT sid
                FROM sc
                WHERE cid = 002);

找到所有上其他课程的学生 ID。

SELECT *
FROM student s
WHERE s.sid IN (SELECT sid
                FROM sc
                WHERE cid != 002);

主查询 从 student 表中选择学生数据，这些学生的 ID 既在第一个子查询结果中，又不在第二个子查询结果中。

SELECT s.sid, s.sname, s.sage, s.ssex
FROM student s
WHERE s.sid IN (SELECT sid
                FROM sc
                WHERE cid = '002')
  AND s.sid NOT IN (SELECT sid
                    FROM sc
                    WHERE cid != '002');

(10)、查询高于平均成绩的学生信息(个人成绩高于总平均成绩)

-- 选择需要的列：学生编号（s.sid）、学生姓名（s.sname）、学生年龄（s.sage）、学生性别（s.ssex）
SELECT distinct s.sid, s.sname, s.sage, s.ssex
FROM student s
-- 将学生表（student）与成绩表（sc）进行连接，连接条件是学生表的学生编号（sid）与成绩表的学生编号（sid）相等
         INNER JOIN sc ON s.sid = sc.sid
-- 只选择成绩高于总平均成绩的记录
WHERE sc.score > (
    -- 子查询：计算总平均成绩
    SELECT AVG(score)
    FROM sc);

SELECT distinct s.sid, s.sname, s.sage, s.ssex
FROM student s
         INNER JOIN sc ON s.sid = sc.sid
WHERE sc.score > (
    SELECT AVG(score)
    FROM sc);