DB2604 - 数据定义与数据操纵

1 数据库创建

（一）数据库（模式）的创建

在创建数据库（模式）时，需指定所使用的字符集为gb2312（中文国标）、字符排序规则为gb2312_bin（英文区分大小写，中文按照汉语拼音排序）

use mysql;
drop schema if exists myschool26; # 删除原有的数据模式
create schema myschool26 charset gb2312 collate gb2312_bin;
use myschool26; # 设置当前工作模式

（二）基表的创建

表名和属性名用英文字符表示，表中各属性的数据类型和完整性约束见题目要求；除了在题目中说明允许取空值的属性，其他所有属性都不允许取空值。
所有的 FOREIGN KEY 必须添加 ON DELETE 和 ON UPDATE 违约定义，其中：
a) ON UPDATE 的违约处理规则都是 CASCADE；
b) 如果 FOREIGN KEY 允许取空值，则 ON DELETE 的违约处理规则是 SET NULL，否则违约处理规则是 RESTRICT。
请使用本文档中拟定的表名和属性名，以免影响下一步初始数据加载脚本的运行。

① 学生表 students

属性：
- 学号 sno char(9)
- 姓名 sname char(20)
- 性别 ssex char(2)
- 出生日期 birthday DATE
- 就读院系 dept char(20)
- 入学时间 enrolldate DATE
约束：学号是 PRIMARY KEY；就读院系和入学时间允许取空值；性别只能是‘男’或‘女’。

create table students(
    sno char(9) primary key,
    sname char(20) not null,
    ssex char(2) not null check(ssex in ('男', '女')),
    birthday DATE not null,
    dept char(20),
    enrolldate DATE
);

属性名数据类型约束

② 教师表 teachers

属性：
- 教师工号 tno char(9)
- 姓名 tname char(20)
- 工作院系 dept char(20)
约束：教师工号是 PRIMARY KEY；工作院系可以取空值。

create table teachers(
    tno char(9) primary key,
    tname char(20) not null,
    dept char(20)
);

③ 课程表 courses

属性：
- 课程号 cno char(8)
- 课程名 cname char(40)
- 学分 credit INT
- 课时数 chours INT
- 开课院系 dept char(20)
约束：课程号是 PRIMARY KEY；学分必须是大于0且小于10的整数；开课院系可以取空值。

create table courses(
    cno char(8) primary key,
    cname char(40) not null,
    credit int not null check(credit > 0 and credit < 10),
    chours int not null,
    dept char(20)
);

④ 课程班表 courseclass

属性：
- 课程班代码 clsno char(8)
- 课程号 cno char(8)
- 授课教师工号 tno char(9)
- 授课年份 clsyear INT
- 授课学期 clsterm char(4)
约束：课程班代码是 PRIMARY KEY，课程号和授课教师工号是两个 FOREIGN KEY；授课学期分为“春季、秋季、暑期”；授课教师工号可以取空值。

create table courseclass(
    clsno char(8) primary key,
    cno char(8) not null,
    tno char(9),
    clsyear int not null,
    clsterm char(4) not null check (clsterm in ('春季', '秋季', '暑期')),
    foreign key (cno) references courses(cno)
        on update cascade
        on delete restrict,
    foreign key (tno) references teachers(tno)
        on update cascade
        on delete set null
);

⑤ 选课表 enrollment

属性：
- 学号 sno char(9)
- 课程班代码 clsno char(8)
- 成绩 grade INT
约束：课程班代码和学号联合构成 PRIMARY KEY；课程班代码和学号是两个 FOREIGN KEY；成绩可以取空值，或者是 0 到 100 之间的整数。

create table enrollment(
    sno char(9) not null,
    clsno char(8) not null,
    grade int check(grade is null or (grade >= 0 and grade <= 100)),
    primary key (sno, clsno),
    foreign key (sno) references students(sno)
        on update cascade
        on delete restrict,
    foreign key (clsno) references courseclass(clsno)
        on update cascade
        on delete restrict
);

2 插入数据

3 数据查询

查询满足下述条件的课程的课程号、课程名、开课院系：课程名中含有汉字‘数’；结果按照开课院系和课程号的升序排列。

select cno, cname, dept
from courses
where cname like '%数%'
order by dept, cno;

查询满足下述条件的学生的学号、姓名、就读院系：姓名中的第二个汉字是‘静’；结果按照学生姓名的升序排列。

select sno, sname, dept
from students
where sname like '_静%'
order by sname;

查询成绩为空值的选课记录，结果返回选课学生的学号和姓名、课程的课程号和课程名；结果按照课程号和学号的升序排列。

select s.sno, s.sname, c.cno, c.cname
from students s, enrollment e, courseclass cls, courses c
where s.sno = e.sno
  and e.clsno = cls.clsno
  and cls.cno = c.cno
  and grade is null
order by c.cno, s.sno; -- 两个字段

查询下列学生的课程修读情况（不考虑没有成绩的课程）：2023级、且所有成绩都及格；结果返回满足条件学生的学号，姓名，修读课程的门数、学分总数及平均成绩；并按照学号的升序输出查询结果。

select s.sno, s.sname, count(distinct c.cno), sum(credit), avg(grade)
from students s, enrollment e, courseclass cls, courses c
where s.sno = e.sno
  and e.clsno = cls.clsno
  and cls.cno = c.cno
  and s.sno like '23%'
  and grade is not null
group by s.sno, s.sname -- 查询需要这二者，分组就用这二者
having min(grade) >= 60
order by sno;

查询承担过授课任务的教师的授课情况，结果返回教师的工号和姓名、授课次数（讲授的课程班数）、累计课时数。结果按照累计课时数的降序和教师工号的升序排列。

select t.tno, t.tname, count(cls.clsno), sum(c.chours)
from teachers t, courseclass cls, courses c
where t.tno = cls.tno
  and cls.cno = c.cno
group by t.tno, t.tname
order by sum(chours) desc, t.tno;

按照姓氏统计所有在校学生的人数，结果返回姓氏和学生人数；结果按照人数的降序和姓氏的升序排列。说明：所有学生均为单字的姓氏，不考虑复姓情况；需要使用系统内置的字符处理函数。

初版：

select f_name, count(sno)
from (select
          substring(sname, 1, 1) f_name, sno
      from students) t1
group by f_name
order by count(sno) desc, f_name;

优化：

select substring(sname, 1, 1) f_name, count(sno) s_cnt
from students
group by f_name
order by s_cnt desc, f_name;

满足下述条件的学生的就读院系、学号和姓名：2021级、且存在自己就读学院开设的课程没有通过（没有选修、或没有成绩、或成绩不及格），结果按照学生就读院系和学号的升序排列。

select s.dept, s.sno, s.sname
from students s
where s.sno like '21%'
  and exists( # 存在
      select *
      from courses c
      where s.dept = c.dept # 院系开设的课程
        and not exists( # 没有（不存在） “通过”
            # 通过：有记录 and 成绩不为空 and 成绩 >= 60
            select *
            from enrollment e, courseclass cls
            where e.sno = s.sno
              and e.clsno = cls.clsno
              and cls.cno = c.cno
              and e.grade >= 60
        )
      )
order by s.dept, s.sno;

成绩不为空 and e.grade is not null 是否必要？

否，空值判断结果为 UNKNOWN，在 WHERE 条件中被当作 FALSE 处理，e.grade >= 60 已经隐式包含了 e.grade is not null 的判断

思路分析

第一步：锁定“主体”与“目标短语”

题目要求：“找出……的学生，且存在本院系课程没有通过”。这里的关键是处理这句略带绕口的条件。我们要把它拆成最原子的逻辑判断。

主体是谁？ 是学生。所以最外层一定是把 students 表作为大循环遍历的对象。
目标状态是什么？ “存在一门课” 满足 “没有通过”。

第二步：将“没有通过”转化为严谨的“防御性定义”

这一步是产生 NOT EXISTS 的关键分水岭。

在日常思维中，我们想到“没通过”，第一反应往往是去抓“不及格的人”（找 grade < 60）。但这在 SQL 里是一个巨大的陷阱：没选修这门课的人，在数据库里根本没有记录，你怎么抓？

所以，写 SQL 时必须切换到“防御性思维”：不要去定义什么是“没过”，而去否定“通过”。 只要我找不到你及格的证据，我就判定你这门课“没通过”。

自然语言： 成绩不及格或没选修。
SQL 逻辑： 不存在（该生选了这门课且成绩 >= 60 的记录）。

这就是那层 NOT EXISTS 的由来。

总结思路流：

当你下次遇到类似包含 “所有/全部”、“没有/不存在”、“至少一个” 这类词汇的查询时，就可以启动这个思维流：

明确要找的主体表写在最外层（如 students）。
用 EXISTS 开启一个探照灯，去扫描被考察的范围（如本院系的 courses）。
用 NOT EXISTS 设定反向过滤网。因为“没发生过的事情（如没选课）”无法被正向 SELECT 出来，只能通过“不存在发生过的证据”来证明。

一旦习惯了这种“反向证明”的逻辑闭环，再写这种嵌套语句就会如同肌肉记忆一样自然了。

一个错解：

select s.dept, s.sno, s.sname
from students s
where s.sno like '21%'
  and exists(
    select *
    from enrollment e
    where e.sno = s.sno
      and not exists(
        select *
        from courseclass cls, courses c
        where cls.cno = c.cno
          and cls.clsno = e.clsno
          and c.dept = s.dept
          and grade is not null
          and grade >= 60
      )
    )
order by s.dept, s.sno;

错解分析

核心在于第二层使用了 “选课表”，第三层从选课表中筛选，致使语义变成了 “查询 2021 级的学生，这些学生至少选修了一门课，且这门课【要么是外院系的课，要么是不及格/无成绩的本院系课】”

当然还有左连接做法：

select distinct s.dept, s.sno, s.sname # 去重，一个学生挂多门课
from students s
# 【关键点1】先构造“应修组合”：学生 + 本院系的所有课程
join courses c on s.dept = c.dept
# 【关键点2】去左连接“实际及格的组合”
left join (
    select cno, sno
    from enrollment e
    join courseclass cls on cls.clsno = e.clsno
    where e.grade >= 60
) passed on s.sno = passed.sno and c.cno = passed.cno
where s.sno like '21%'
  # 【关键点3】左边有（应修），但右边是NULL（没及格），抓出这个学生
  and passed.cno is null
ORDER BY s.dept, s.sno;

满足下述条件的学生的就读院系、学号和姓名：2021级、且选修通过了自己就读学院开设的所有课程，结果按照学生就读院系和学号的升序排列。

双重否定：

select s.dept, s.sno, s.sname
from students s
where sno like '21%'
  and not exists( -- 不存在自己院系开设的一门课
    select *
    from courses c
    where c.dept = s.dept
      and not exists( -- 没有选修通过
        select *
        from enrollment e, courseclass cls
        where e.clsno = cls.clsno
          and e.sno = s.sno
          and cls.cno = c.cno
          and e.grade >= 60
    )
)
order by s.dept, s.sno;

计数法：

select s.dept, s.sno, s.sname
from students s, enrollment e, courseclass cls, courses c
where s.sno = e.sno
  and e.clsno = cls.clsno
  and cls.cno = c.cno
  and s.sno like '21%'
  and c.dept = s.dept
  and e.grade >= 60
group by s.dept, s.sno, s.sname
having count(distinct c.cno) = (
    select count(distinct c.cno)
    from courses c
    where c.dept = s.dept
);

查询每一位教师的授课起止年份，结果返回教师的工号、姓名、工作院系、第一次授课的年份、最后一次授课的年份；如果某位老师没有承担过授课任务，则授课起止年份返回空值；结果按照工作院系和教师工号的升序排列。

初版：

select t1.tno, t1.tname, t1.dept, min_year, max_year
from teachers t1
left outer join (
    select t.tno, t.tname, t.dept,
           min(cls.clsyear) min_year,
           max(cls.clsyear) max_year
    from teachers t, courseclass cls
    where t.tno = cls.tno
    group by t.tno, t.tname, t.dept
) teached on t1.tno = teached.tno
order by t1.dept, t1.tno;

优化：

select t.tno, t.tname, t.dept,
       min(cls.clsyear) min_year,
       max(cls.clsyear) max_year
from teachers t
left outer join courseclass cls on t.tno = cls.tno
group by t.tno, t.tname, t.dept
order by t.dept, t.tno;

当 LEFT OUTER JOIN 匹配不到记录时，MIN 和 MAX 函数作用于 NULL 值，结果依然是 NULL

在一门课程的各个课程班中，查询各课程班的班平均成绩的最高值；结果返回课程的课程号、课程名、班平均成绩最高的课程班的代码、授课年份和授课学期；并按照课程号的升序和授课年份的降序输出查询结果。（在计算班平均成绩时，不需要考虑成绩为空值的选课元组）

select c.cno, c.cname, clsno, clsyear, clsterm
from courses c, (
    select cno, cls.clsno, clsyear, clsterm, avg(grade) avg_grade
    from courseclass cls, enrollment e
    where cls.clsno = e.clsno
    group by cno, cls.clsno, clsyear, clsterm
) cls_e
where c.cno = cls_e.cno
  and (c.cno, c.cname, avg_grade) in (
    select c2.cno, c2.cname, max(avg_grade)
    from courses c2, (
        select cno, cls2.clsno, clsyear, clsterm, avg(grade) avg_grade
        from courseclass cls2, enrollment e2
        where cls2.clsno = e2.clsno
        group by cno, cls2.clsno, clsyear, clsterm
    ) cls_e2
    where c2.cno = cls_e2.cno
    group by c2.cno, c2.cname
)
order by c.cno, clsyear desc;

参考答案 1：

select c.cno, c.cname, cls.clsno, cls.clsyear, cls.clsterm,
      cls_view.gradeavg as 班平均成绩最高值
from courses c, courseclass cls,
      (select clsno, avg(grade)
      from enrollment
      where grade is not null
      group by clsno) cls_view(clsno, gradeavg)
where c.cno=cls.cno and cls.clsno=cls_view.clsno and
      gradeavg >= ALL (select avg(grade)
                      from enrollment x, courseclass y
                      where x.clsno=y.clsno and y.cno=c.cno 
                        and x.gradeis not null 
                      group by x.clsno)
order by c.cno, cls.clsyear DESC;

参考答案 1：

# 创建课程班平均成绩统计视图
create view cls_view(clsno, cno, gradeavg) as
  select e.clsno, c.cno, avg(e.grade)
  from enrollment e, courseclass c
  where e.clsno = c.clsno and grade is not null
  group by e.clsno, c.cno;

# 在视图cls_view上完成本查询
select c.cno, c.cname, cls.clsno, cls.clsyear, cls.clsterm,
  v.gradeavg as 班平均成绩最高值
from courses c, courseclass cls, cls_view v
where c.cno=cls.cno and cls.clsno=v.clsno 
  and v.gradeavg >= ALL (select x.gradeavg
                        from cls_view x 
                        where x.cno=v.cno)
order by c.cno, cls.clsyear DESC;

优化一：使用 WITH 提取公共表达式（CTE）

WITH ClassAvg AS (
    -- 先计算好所有班级的平均分，存入公共表达式
    SELECT cls.cno, cls.clsno, cls.clsyear, cls.clsterm, AVG(e.grade) AS avg_grade
    FROM courseclass cls, enrollment e
    WHERE cls.clsno = e.clsno
    GROUP BY cls.cno, cls.clsno, cls.clsyear, cls.clsterm
)
SELECT c.cno, c.cname, a.clsno, a.clsyear, a.clsterm
FROM courses c, ClassAvg a
WHERE c.cno = a.cno
  AND (a.cno, a.avg_grade) IN (
      -- 直接从公共表达式里找最大值
      SELECT cno, MAX(avg_grade)
      FROM ClassAvg
      GROUP BY cno
  )
ORDER BY c.cno ASC, a.clsyear DESC;

优化二：使用窗口函数

WITH RankedClass AS (
    SELECT 
        c.cno, c.cname, cls.clsno, cls.clsyear, cls.clsterm,
        -- 按课程号(cno)分组，并在组内按平均成绩降序打上排名(rnk)
        RANK() OVER (PARTITION BY c.cno ORDER BY AVG(e.grade) DESC) as rnk
    FROM courses c
    JOIN courseclass cls ON c.cno = cls.cno
    JOIN enrollment e ON cls.clsno = e.clsno
    GROUP BY c.cno, c.cname, cls.clsno, cls.clsyear, cls.clsterm
)
SELECT cno, cname, clsno, clsyear, clsterm
FROM RankedClass
WHERE rnk = 1  -- 直接取每门课排名第一的班级
ORDER BY cno ASC, clsyear DESC;

4 数据更新

（一）元组插入

关闭事务自动提交标志：set autocommit=0;
在课程班表 courseclass 中插入一条元组：2025 年秋季，课程号 3208，授课教师工号 704，课程班号 2513208
在选课表 enrollment 中插入如下的选课元组：计算机学院 2024 级的所有同学关于课程班（课程班号 2513208）的选课元组（无课程成绩）
使用 commit 命令提交当前事务的执行结果。
查询成绩为空值的选课元组，结果返回：学生的学号和就读院系、课程的课程名和开课院系、授课教师的姓名、课程班的授课年份和学期。结果按照学号和课程号的升序排列。

set autocommit = 0;

insert into courseclass
values ('2513208', '3208', '704', 2025 , '秋季');

insert into enrollment
select sno, '2513208', NULL
from students
where dept = '计算机'
  and sno like '24%';

commit;

select s.sno, s.dept, c.cname, c.dept, t.tname, cls.clsyear, cls.clsterm
from students s
join enrollment e on s.sno = e.sno
join courseclass cls on e.clsno = cls.clsno
join courses c on cls.cno = c.cno
left join teachers t on cls.tno = t.tno # tno 可取空
where e.grade is null
order by s.sno, c.cno;

commit;

insert into 表名 (列1, 列2, ...) values (值1, 值2, ...) insert into 表名 select 子查询...

（二）元组修改

关闭事务自动提交标志：set autocommit=0;
将下述选课元组上的成绩值修改为 68：学号 211210166，课程班号 2111202
查询满足下述条件的学生的学号、姓名和就读院系：2021 级、且选修通过了‘计算机’学院开设的所有课程，结果按照学生学号的升序排列。
使用 rollback 命令放弃当前事务的更新结果，结束当前事务。
将下列选课元组上的成绩修改为空值：跨院系的课程选修（学生的就读院系和课程的开课院系不一样）
使用 rollback 命令放弃当前事务的更新结果，结束当前事务。

set autocommit = 0;

update enrollment
set grade = 68
where sno = '211210166' and clsno = '2111202';

select sno, sname, dept
from students s
where sno like '21%'
  and not exists(
      select *
      from courses c
      where c.dept = '计算机'
        and not exists(
            select *
            from enrollment e, courseclass cls
            where e.clsno = cls.clsno
              and e.sno = s.sno
              and cls.cno = c.cno
              and e.grade >= 60
        )
)
order by s.sno;

rollback;

update enrollment e
set grade = NULL
where exists(
    select *
    from students s, courseclass cls, courses c
    where e.sno = s.sno
      and e.clsno = cls.clsno
      and cls.cno = c.cno
      and s.dept != c.dept
);

rollback;

update 表名 set 列1 = 值1, 列2 = 值2, ... where 条件(e.g. exists(...))

（三）元组删除

关闭事务自动提交标志：set autocommit=0;
删除‘计算机’学院开设的‘人工智能导论’这门课程及其所有的选课元组。
使用 rollback 命令放弃当前事务的更新结果，结束当前事务。
删除课程班号为 2513208 的课程班元组及其所有的选课元组。
使用 commit 命令提交当前事务的执行结果。

set autocommit = 0;

# 删除‘计算机’学院开设的‘人工智能导论’这门课程及其所有的选课元组。
delete from enrollment
where clsno in (
    select clsno
    from courseclass
    where cno in (
        select cno
        from courses
        where dept = '计算机' and cname = '人工智能导论'
    )
);

delete from courseclass
where cno in (
    select cno
    from courses
    where dept = '计算机' and cname = '人工智能导论'
);

delete from courses
where dept = '计算机' and cname = '人工智能导论';

rollback;

# 删除课程班号为 2513208 的课程班元组及其所有的选课元组。
delete from enrollment
where clsno = '2513208';

delete from courseclass
where clsno = '2513208';

commit;

删除时要自底向上

delete from 表名 where 条件

5 视图创建与访问

创建第一个视图：用于查询每一门课程的累计修读学生人数和该门课程的平均成绩（视图属性包括：课程号、累计修读学生人数、平均成绩）

create view v1 as
select c.cno,
       count(e.sno) stu_cnt,
       avg(e.grade) avg_grade
from courses c
left join courseclass cls on c.cno = cls.cno
left join enrollment e on cls.clsno = e.clsno
group by c.cno;

参考答案：

create view v1_course as
select cls.cno, count(*) as num_of_stud, avg(sc.grade) as avg_grade
from courseclass cls, enrollment sc
where cls.clsno=sc.clsno
group by cls.cno;

create view 视图名 as

视图的嵌套定义和查询

(1) 创建第二个视图，用于查询每一门课程的累计修读学生人数、成绩大于课程平均成绩的学生人数（视图属性包括：课程号、累计修读学生人数、大于课程平均成绩的学生人数）

create view v2 as
select v1.cno, v1.stu_cnt,
       (
           select count(sno)
           from courseclass cls, enrollment e
           where cls.cno = v1.cno
             and cls.clsno = e.clsno
             and e.grade > v1.avg_grade
       ) stu_cnt2
from v1;

参考答案：

create view v2_course as
select v.cno, v.num_of_stud, 
      count(*) as num_of_upstud # 下面一堆都只是为了这个count服务
from v1_course v, enrollment sc, courseclass cls
where v.cno=cls.cno and cls.clsno=sc.clsno and sc.grade>v.avg_grade
group by v.cno

(2) 查询满足下述条件的课程的课程号、课程名、开课院系：在修读过的学生中，半数以上学生的成绩低于该课程平均成绩。结果按照开课院系和课程号的升序排列。

select c.cno, c.cname, c.dept
from courses c, v1, courseclass cls, enrollment e
where c.cno = v1.cno
  and v1.cno = cls.cno
  and cls.clsno = e.clsno
  and e.grade < v1.avg_grade
group by c.cno, c.cname, c.dept, v1.stu_cnt
having count(sno) * 2 > stu_cnt
order by c.dept, c.cno;

WITH CHECK OPTION 选项及视图更新

（一）带有 WITH CHECK OPTION 选项的视图创建与查询 (1) 创建第三个视图，用于查询课程修读未通过（成绩为空或不及格）的情况，在创建视图命令中使用 WITH CHECK OPTION 选项。视图中的属性包括：学生的学号、姓名、就读院系，未通过课程的课程名、课程班代码、成绩。 (2) 在第三个视图上查询课程修读未通过的情况。 (3) 关闭事务自动提交标志：set autocommit=0;

create view v3 as
select s.sno, s.sname, s.dept, c.cname, cls.clsno, e.grade
from students s, enrollment e, courseclass cls, courses c
where s.sno = e.sno
  and e.clsno = cls.clsno
  and cls.cno = c.cno
  and (grade is null or grade < 60)
with check option;

select * from v3;

set autocommit = 0;

（二）不合要求的视图更新 (4) 选择一条成绩不及格的选课元组（记为元组 t），在第三个视图上将元组 t 的成绩修改为 60 分。 (5) 使用 rollback 命令放弃当前事务的更新结果，结束当前事务。

select sno, sname, dept, cname, clsno, grade
into @t_sno, @t_sname, @t_dept, @t_cname, @t_clsno, @t_grade
from v3
where grade is not null and grade < 60
limit 1;

update v3
set grade = 60
where sno = @t_sno and clsno = @t_clsno;

rollback;

（三）基表的数据更新 (6) 直接在选课表 enrollment 上，将选课元组 t 的成绩修改为 70 分。 (7) 再次在第三个视图上查询课程修读未通过的情况。 (8) 使用 rollback 命令放弃当前事务的更新结果，结束当前事务。

update enrollment
set grade = 70
where sno = @t_sno and clsno = @t_clsno;

select * from v3;

rollback;

（四）符合视图定义的数据更新（在第三个视图上完成下述数据更新） (9) 将元组t的成绩修改为 50 分。 (10) 将‘数据结构’课程的不及格成绩统一修改为空值。 (11) 最后，再一次在第三个视图上查询课程修读未通过的情况。 (12) 使用 rollback 命令放弃当前事务的更新结果，结束当前事务。

update v3
set grade = 50
where sno = @t_sno and clsno = @t_clsno;

update v3
set grade = null
where cname = '数据结构';

select * from v3;

rollback;

删除在本实验创建的所有视图。

drop view v1;
drop view v2;
drop view v3;