题目答案:

11-12题
11
(1)ALTER TABLE STUDENT ADD NATION VARCHAR2(20);
(2)ALTER TABLE STUDENT DROP COLUMN NATION;
(3)INSERT INTO GRADE VALUES ('2021110','3','80');
(4)UPDATE GRADE SET GMARK='70' WHERE SNO='2021110';
(5)DELETE FROM GRADE WHERE SNO='2021110';
(6)CREATE INDEX IX_CLASS ON GRADE(CNO ASC);
(7)DROP INDEX IX_CLASS;






12
(1)SELECT DISTINCT CNO FROM GRADE;
(2)SELECT * FROM STUDENT T WHERE T.SsEX='女' AND T.CLNO='20311';
(3)SELECT T.SNAME,T.SSEX,T.SBIRTH FROM STUDENT T WHERE T.CLNO='20311' OR T.CLNO='20312';
(4)SELECT * FROM STUDENT T WHERE T.SNAME LIKE '李%';
(5)SELECT COUNT(*)
FROM STUDENT S
WHERE S.CLNO=(SELECT CLNO FROM STUDENT WHERE SNAME="李勇")
(6)SELECT
MAX(GMARK) AS 最高分,
MIN(GMARK) AS 最低分,
AVG(GMARK) AS 平均分
FROM GRADE
WHERE CNO = (SELECT CNO FROM COURSE WHERE CNAME = '操作系统');
(7)SELECT COUNT(DISTINCT SNO)
FROM GRADE;
(8)SELECT COUNT(DISTINCT SNO)
FROM GRADE WHERE CNO=(SELECT CNO FROM COURSE WHERE CNAME='操作系统');
(9)
SELECT S.SNAME
FROM STUDENT S LEFT JOIN CLASS C ON S.CLNO=C.CLNO LEFT JOIN GRADE G ON S.SNO=G.SNO
WHERE C.INYEAR='2020' AND C.SPECIAL='计算机科学与技术' AND G.GMARK IS NULL;

(10)
SELECT C1.CNAME, C2.CNAME
    FROM COURSE C1,COURSE C2
        WHERE C2.CNO=C1.CPNO OR C1.CPNO IS NULL;
13
(1)
SELECT *
    FROM STUDENT S
        WHERE S.CLNO=(SELECT T.CLNO
                      FROM STUDENT T
                          WHERE T.SNAME='李勇'
                          );
(2)
SELECT S.*
    FROM STUDENT S,GRADE G
        WHERE S.SNO=G.SNO
            AND G.CNO IN (SELECT G2.CNO
                         FROM GRADE G2,STUDENT S2
                             WHERE G2.SNO=S2.SNO
                             AND S2.SNAME='李勇');
(3)
SELECT *
    FROM STUDENT S
        WHERE S.SBIRTH BETWEEN (SELECT S.SBIRTH
                                FROM STUDENT S2
                                WHERE S2.SNAME='李勇')
            AND to_date('2005-01-01','yyyy-mm-dd');
(4)
SELECT S.SNO,S.SNAME
    FROM STUDENT S,COURSE C,GRADE G
        WHERE C.CNAME='操作系统'
        AND C.CNO=G.CNO
        AND S.SNO=G.SNO;
(5)
SELECT S.SNAME
FROM STUDENT S
WHERE NOT EXISTS (
    SELECT 1
    FROM GRADE G
    WHERE G.SNO = S.SNO AND G.CNO = '1'
);
(6)
SELECT G1.SNO, G1.CNO
FROM GRADE G1
WHERE G1.GMARK > (
    SELECT AVG(G2.GMARK)
    FROM GRADE G2
    WHERE G2.SNO = G1.SNO
);
(7)
SELECT S.SNAME
FROM STUDENT S
WHERE NOT EXISTS (
    SELECT C.CNO
    FROM COURSE C
    WHERE NOT EXISTS (
        SELECT G.CNO
        FROM GRADE G
        WHERE G.SNO = S.SNO AND G.CNO = C.CNO
    )
);
14
(1)

15
(1)

16
(1)

数据库知识整理

---

一、SQL 查询基础

1. 嵌套子查询

• 使用 IN 关键字：
  外层查询依赖内层查询结果，内层查询先执行，结果传递给外层。
• SELECT a.eno, a.ename
  FROM employee a, department b
  WHERE a.dno = b.dno
    AND b.dname = '技术科';
• 相关子查询与 EXISTS：
  外层逐条遍历，内层根据外层值动态执行。
• — 示例：查询存在员工的部门
  SELECT dname
  FROM department d
  WHERE EXISTS (
      SELECT 1
      FROM employee e
      WHERE e.dno = d.dno
  );

2. 视图操作

• 创建或替换视图：
• CREATE OR REPLACE VIEW v AS
  SELECT …; — 视图定义查询

---

二、数据库设计理论

1. 关系模式评估标准

• 问题判断：
  • 数据冗余程度（如重复存储姓名）
  • 增删改异常（如删除最后一个课程记录导致学生信息丢失）
• 关系模式表示：
  R(U, D, DOM, F)
  • U：属性集合（如学号、姓名）
  • D：域（数据类型，如 VARCHAR(10)）
  • DOM：属性到域的映射
  • F：函数依赖（如学号 → 姓名）

2. 函数依赖

类型	定义	示例	说明
完全函数依赖	多属性组合决定另一属性，缺一不可	(学号, 课程号) → 成绩	成绩需学号和课程号共同确定
部分函数依赖	主键中部分属性即可决定非主属性	(学号, 课程号) → 姓名（学号可单独决定姓名）	导致数据冗余
平凡函数依赖	右侧属性包含在左侧集合中	(学号, 姓名) → 学号	永远成立，无实际意义
非平凡函数依赖	右侧属性不全在左侧	学号 → 姓名	提供有效信息

3. 码（Key）与属性

• 候选码：能唯一标识记录的最小属性集（如学号、身份证号）
• 主码：从候选码中选定的核心标识（如学号）
• 主属性：属于任一候选码的属性（如学号、身份证号）
• 非主属性：不包含在候选码中的属性（如姓名、年龄）

类比：

• 候选码 = 多个候选人
• 主码 = 当选总统
• 主属性 = 候选人成员
• 非主属性 = 普通群众

---

三、数据完整性

1. 完整性类型

类型	实现方式	示例
域完整性	CHECK, DEFAULT	性别只能是男/女
实体完整性	PRIMARY KEY	学号不能为空且唯一
参照完整性	FOREIGN KEY	外键关联主表主键
用户定义	触发器、业务规则	教授工资 ≥ 1000 元

2. 外键约束处理

• 违约操作：
  • NO ACTION（默认）：拒绝操作
  • CASCADE：级联删除/更新
  • SET NULL/SET DEFAULT：设为空或默认值
• ALTER TABLE Employee
  ADD CONSTRAINT fk_dept
  FOREIGN KEY (Dno) REFERENCES Department(Dno)
  ON DELETE CASCADE;

---

四、数据库安全性

• 核心目标：防止非法访问
• 实现手段：
  • 权限管理：GRANT, REVOKE
  • 视图机制：隐藏敏感数据（如薪资）
  • 审计追踪：记录操作日志

与完整性的区别：

• 完整性防误操作（如无效数据）
• 安全性防非法访问（如黑客入侵）

---

五、并发控制与恢复

1. 并发问题

• 丢失更新、脏读、不可重复读、幻读
• 解决机制：
  • 事务（BEGIN, COMMIT, ROLLBACK）
  • 锁（行锁、表锁）
  • ACID 特性（原子性、一致性、隔离性、持久性）

2. 数据恢复

• 日志：记录变更（UNDO/REDO）
• 检查点（Checkpoint）：定期保存状态
• 恢复操作：
  • UNDO：回滚未提交事务
  • REDO：重做已提交事务

---

六、触发器（Trigger）

• 作用：自动执行特定操作（如数据校验、级联更新）
• 示例：
• CREATE TRIGGER trg_check_grade
  BEFORE INSERT ON SC
  FOR EACH ROW
  BEGIN
      IF NEW.grade < 0 OR NEW.grade > 100 THEN
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '成绩必须在0~100之间';
      END IF;
  END;

##

概念	核心区别
完全依赖 vs 部分依赖	是否需要全部主键属性
平凡依赖 vs 非平凡依赖	右侧是否在左侧存在
安全性 vs 完整性	防非法访问 vs 防误操作

---

重点理解：

1. 主键与外键维护数据一致性
2. 函数依赖影响数据库范式设计（如消除部分依赖）
3. 事务与锁保障并发安全
4. 视图与触发器增强数据管理灵活性

---

用生活中的例子讲清楚数据库范式

---

第一范式（1NF）：字段不能再拆分

通俗理解：
每一列的值都必须是「最小单元」，不能拆成更小的部分。
反例：

学号	地址（省、市、区合并）
001	广东省深圳市南山区

问题：无法单独查询“深圳市的所有学生”。
解决：拆分成独立字段

学号	省	市	区
001	广东	深圳	南山

---

第二范式（2NF）：消除「部分依赖」

前提：先满足1NF。
通俗理解：
如果主键是多个属性组合（比如学号+课程号），那么所有非主键字段必须依赖整个主键，而不是其中一部分。
反例：

学号	课程号	姓名	成绩
001	C1	张三	90

• 主键是（学号+课程号）
• 姓名只依赖学号 → 部分依赖（冗余！）

解决：拆表

学生表(学号, 姓名) 
选课表(学号, 课程号, 成绩)

---

第三范式（3NF）：消除「传递依赖」

前提：先满足2NF。
通俗理解：
非主键字段之间不能互相依赖，只能直接依赖主键。
反例：

学号	班级	班主任
001	一班	王老师

• 班主任依赖班级 → 班级传递依赖学号 → 更新异常（换班主任要改多行）

解决：拆表

学生表(学号, 班级) 
班级表(班级, 班主任)

---

BC范式（BCNF）：所有决定因素都是候选键

通俗理解：
任何能决定其他字段的属性，必须是候选键（唯一标识记录的字段）。
反例：
仓库管理表(仓库ID, 物品ID, 数量, 管理员)

• 仓库ID + 物品ID → 数量
• 管理员 → 仓库ID（一个管理员只管一个仓库）

问题：

• 管理员不是候选键，却能决定仓库ID → 违反BCNF

解决：拆表

库存表(仓库ID, 物品ID, 数量) 
仓库管理员表(仓库ID, 管理员)

---

总结对比表

范式	核心要求	解决问题	生活类比
1NF	字段不可再分	数据结构混乱	把地址拆成省市区
2NF	消除部分依赖	数据冗余	学生姓名不依赖课程号
3NF	消除传递依赖	更新异常	班主任不依赖学号，依赖班级
BCNF	所有决定因素是候选键	复杂依赖冲突	管理员必须和仓库绑定

---

一句话总结

• 1NF：字段不能再拆
• 2NF：主键整体决定其他字段
• 3NF：非主键之间不能互相依赖
• BCNF：谁决定别人，谁就必须是候选键

实际意义：
范式越高，数据冗余越少，更新越安全，但查询可能越复杂。实际设计时通常做到3NF即可！

Share this post: