数据库原理

本笔记面是面向考试的，主要记录考试重点知识

第一章数据库基础

1. 三级模式
   外模式、模式、内模式
   外模式（子模式）：展示给用户看的数据视图
   模式（概念模式）：全体数据的逻辑结构
   内模式（物理模式）：数据的物理存储结构
2. 一个数据库只能有一个模式和物理模式，可以有多个外模式
3. 改索引、改存储结构、改文件组织方式 → 物理独立性
   改表的逻辑结构、增加属性、拆分关系表 → 逻辑独立性

第二章关系数据模型

关系完整性约束

1. 第一范式规则：列元素为最基本的单元不能含内部结构
2. 行查询时无序
3. 行（元组）唯一
4. 实体完整性规则：A是R的主键，那么A不能为空，主键属性不能取空值。
   键（主键：主观选用的键）：能够区分表中每一元组的属性集合
   超键：表中只存在唯一的键
5. 参照完整性规则：F是R的外键，且是S的主键（R和S有可能相同），则对于R中每个元组在F中的值取空或者等于S某个主键，外键值要么为空，要么等于被参照关系中某个元组的主键值。
6. 用户自定义完整性

关系代数

• 投影运算π（）：简单来说就是从表中取属性（可能多个）去重，如果是多个属性得到的是属性对 πA,B(R) ={(a1,b1),....}
  SELECT语句默认是不去重，关系运算是去重的

• 选择（限制）运算SELECT：σF(R)，选择表R中满足F的元组
  σ Cour = Information (Student)：选择学生表中来自信息系的学生元组

• 连接：从两个关系的笛卡尔积中选取属性间满足一定条件的元组

  1. 自然连接R⋈S：选取相同的属性作为中间人来连接两个表，自然连接要求至少有一个同名同域属性，且去重

  2. 条件链接R⋈F S：只要你写条件，哪怕属性名不同，只要值域兼容就能连

  3. 外连接不仅保留匹配的行还保留未匹配的行
     左外连接（保留左边匹配右边没匹配的）、右外连接（相反）

     

• 除运算：B ÷ A例 πSno, Cno(SC) ÷ K 先对SC关系在Sno和Cno属性上投影，然后对其中每个元组逐一求出象集，再找出包含K的属性（对）

关系演算*

ALPHA 语言 ：

1. 检索

   GET W(num) (elements) (if) 查询个数，查询对象，满足条件
   GET W (SC.Cno) : 查询所有被选修课程的课程号码

   RANGE Student X ：用元组变量X简化关系名Student，后面可以直接用X代替
   例: 查询计算机系 (CS) 所有学生都选修了的课程的课程号和课程名

   GET W (Course.Cno, Course.Cname):
     ∀ StudentX (StudentX.Sdept = 'CS'
       → ∃ SCY (SCY.Sno = StudentX.Sno ∧ SCY.Cno = Course.Cno))

2. 集函数

   函数名	功能
   COUNT	对元组计数
   TOTAL	求和
   MAX	最大值
   MIN	最小值
   AVG	平均值

3. 更新操作

   1. HOLD（更改必须用HOLD）读取到工作空间
   2. 宿主语言修改
   3. UPDATE语句送回数据库进行更新

   HOLD W (Student.Sno, Student.Sdept) : Student.Sno = '2020007' 
   MOVE 'IS' TO W.Sdept
   UPDATE W

4. 插入
   PUT W W为已经修改好的元组（插入元组）

5. 删除

   1. HOLD读取到W
   2. DELET W

第三章SQL语言

SQL功能	动词
数据查询	SELECT
数据定义	CREATE, DROP, ALTER
数据操纵	INSERT, UPDATE, DELETE
数据控制	GRANT, REVOKE

1. 创建、修改、删除基本表

   --创建基本表
   CREATE TABLE SC
   	(
           Sno CHAR(9),
           Cno CHAR(4),
           Grade SMALLINT,
           PRIMARY KEY (Sno, Cno),
     	  	/*定义主键，必须满足表级完整性*/
       	FOREIGN KEY (Sno) REFERENCES Student (Sno),
           /*表级完整性约束条件， Sno是外键， 被参照表是Student*/
           FOREIGN KEY (Cno) REFERENCES Student (Cno)
       )
       
       
   --修改基本表
   ALTER TABLE <表名>
   [ADD <新列名> <数据类型> [完整性约束]]/**/
   [DROP <完整性约束名>] /*删除，SQL没有提供删除属性列的语句*/
   [MODIFY <列名><数据类型>] /*修改原有的列定义（修改数据类型）*/
   
   --创建索引
   CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>]]...)
   /*
   UNIQUE 索引值只对应唯一一条数据记录
   CLUSTER 创建聚簇索引（查询快，更新代价大，索引即数据，数据即索引）
   */
   CREATE UNIQUE INDEX Student ON Student(Sno);
   /* 为Student表按学号升序创建唯一索引 */

2. 查询

      -- IN
      /*查询IS，MA，CS系的学生姓名和性别*/
      SELECT Sname, Ssex
      FROM Student
      WHERE Sdept IN ('IS','MA','CS');
      
      -- 字符匹配
      --1. % :任意长度字符串（可以为0）
      --2. _ :任意单个字符
      --3. ESCAPE换码
      
      LIKE 'DB\_Design' ESCAPE '\' ;
      /*表示'\'是换吗字符跟在后面的字符取原本意思*/'
      
      -- ORDER BY
      /*对查询结果排序 ASC DESC*/
      -- GROUP BY [HAVING]
      /*对查询结果进行分组(满足某些条件)*/
      SELECT cno, AVG(score)
      FROM Selcou
      GROUP BY cno
      HAVING AVG(score) >= 85;
      
      --连接查询和EXISTS IN
      ⚠️ EXISTS必须关联外层表，否则会全表匹配 / 报错。
      SELECT s.name 
   FROM student s
      WHERE EXISTS (
          -- 只判断是否存在，不返回具体ID
          SELECT 1 FROM score sc 
          WHERE sc.student_id = s.id  -- 关键：关联外层表
      );
      
      --JOIN ON 外连接
      SELECT s_id
      FROM score
      JOIN course ON score.c_id = course.c_id
      WHERE course.c_name = '语文'

表结构说明（所有题目共用）

• 学生表 student
  s_id 学生编号
  s_name 姓名
  s_age 年龄
  s_class 班级
• 课程表 course
  c_id 课程编号
  c_name 课程名
• 成绩表 score
  s_id 学生编号
  c_id 课程编号
  score 分数

【题目 1】（中等偏难）
查询至少选修了 “语文” 和 “数学” 两门课的学生姓名。要求：分别用 IN 和 EXISTS 各写一种写法。

【题目 2】（难，经典面试题）
查询只选修了 1 门课的学生姓名。（注意：不是 “至少 1 门”，是恰好 1 门）

【题目 3】（难，坑点多）
查询没有选修任何课程的学生姓名。要求：
用 NOT IN 写
用 NOT EXISTS 写
说明两种写法在NULL 值下的区别和风险

【题目 4】（很难，综合考察）
查询选修了全部课程的学生姓名。（即：没有任何一门课程是这个学生没选的） 提示：这是经典的 **“全称量词” 转存在量词 ** 问题，必须用 NOT EXISTS 嵌套。

-- 第一题
--in
SELECT s_name
FROM student
WHERE s_id IN(
	SELECT s_id
    FROM score
    JOIN course ON score.c_id = course.c_id
    WHERE course.c_name = '语文'
)
AND s_id IN(
	SELECT s_id
    FROM score
    JOIN course ON score.c_id = course.c_id
    WHERE course.c_name = '数学'	
)

--EXIST
SELECT s_name
FROM student s
WHERE EXISTS (
    SELECT 1
    FROM score sc
    JOIN course c ON sc.c_id = c.c_id
    WHERE sc.s_id = s.s_id AND c.c_name = '语文'
)
AND EXISTS (
    SELECT 1
    FROM score sc
    JOIN course c ON sc.c_id = c.c_id
    WHERE sc.s_id = s.s_id AND c.c_name = '数学'
);

--第二题
--IN
SELECT s_name
FROM student
WHERE s_id IN (
	SELECT s_id
    FROM score
    GROUP BY s_id 
    HAVING COUNT(c_id) = 1
)

--EXISTS
SELECT s_name
FROM student s
WHERE 
  -- 1. 至少有1门课
  EXISTS (
    SELECT 1
    FROM score sc
    WHERE sc.s_id = s.s_id
  )
  -- 2. 但不存在第2门课
  AND NOT EXISTS (
    SELECT 1
    FROM score sc1
    JOIN score sc2 
      ON sc1.s_id = sc2.s_id 
     AND sc1.c_id <> sc2.c_id  -- 两门不同课程
    WHERE sc1.s_id = s.s_id
  );
  
--第三题
--IN
SELECT s_name
FROM student
WHERE s_id NOT IN (
	SELECT s_id
    FROM score
)

--EXISTS
SELECT s_name
FROM student s
WHERE NOT EXISTS (
	SELECT 1
    FROM score sc
    WHERE sc.s_id = s.s_id
)


Student(sno, sname, gender, age, sdept)
Course(cno, cname, credit, cpre)
SelCou(sno, cno, score)
Dept(sdept, dname)

Student.sno 是主键
Course.cno 是主键
SelCou(sno, cno) 是主键
SelCou.sno 外键引用 Student.sno
SelCou.cno 外键引用 Course.cno
Dept.sdept 是主键
Student.sdept 外键引用 Dept.sdept
Course.cpre 表示先修课程号，可为空，引用 Course.cno


--查询所有计算机学院学生的学号、姓名和年龄。
SELECT Student.sno,Student.sname,Student.age
FROM Student
WHERE Student.sdept = 'CS';

--查询年龄在 18 到 22 岁之间的女生，输出学号、姓名、年龄、所在院系，并按年龄从大到小排序；年龄相同则按学号升序排序。
SELECT sno, sname, age, sdept
FROM Student
WHERE gender = 'female'
  AND age BETWEEN 18 AND 22
ORDER BY age DESC, sno ASC;

--查询每个学生的选课情况，输出:学号、姓名、课程号、课程名、成绩
SELECT s.sno, s.sname, c.cno, c.cname, sc.score
FROM Student s
JOIN SelCou sc ON s.sno = sc.sno
JOIN Course c ON sc.cno = c.cno;

--查询选修了课程名为 '数据库' 的学生，输出：学号、姓名、成绩
SELECT s.sno, s.sname, sc.score
FROM Student s
JOIN SelCou sc ON s.sno = sc.sno
JOIN Course c ON sc.cno = c.cno
             AND c.cname = '数据库';
             
--统计每门课程的选课人数和平均成绩，输出：课程号、课程名、选课人数、平均成绩
SELECT c.cno, c.cname, COUNT(sc.sno) , AVG(sc.score) 
FROM Course c
JOIN SelCou sc ON c.cno = sc.cno
GROUP BY c.cno, c.cname
ORDER BY c.cno ASC;

--查询平均成绩不低于 80 分的课程，输出：课程号、课程名、平均成绩
SELECT c.cno, c.cname, AVG(sc.score) AS avg_score
FROM Course c
JOIN SelCou sc ON c.cno = sc.cno
GROUP BY c.cno, c.cname
HAVING AVG(sc.score) >= 80
ORDER BY AVG(sc.score) DESC;

--查询选修了课程名为 '数据库' 的学生姓名。
SELECT s.sname
FROM Student s
WHERE s.sno IN (
	SELECT sc.sno
    FROM SelCou sc
    WHERE sc.cno IN (
    	SELECT c.cno
        FROM Course c
        WHERE c.cname = '数据库'
    )
);

--查询选修了全部课程的学生，输出：学号、姓名
SELECT s.sno, s.sname
FROM Student s
WHERE NOT EXISTS(
	SELECT * 
    FROM Course c
    WHERE NOT EXISTS(
    	SELECT * 
        FROM SelCou sc
        WHERE c.cno = sc.cno
        AND sc.sno = s.sno
    )
);

3. 视图
   虚关系，在创建视图的时候不执行其中的SELECT语句只是给个基本框架，到查询的时候再进行数据查询

   CREATE VIEW <视图名> [(<列名>[,<列名>]...)]
   AS <子查询>
   [WITH CHECK OPTION];--表示在对视图进行更新插入删除操作的时候满足视图定义的谓词条件（子查询中的条件表达式）
   
   --行列子集视图：建立在一个表上且只对行列进行的选择
   --也可以建立在多个表上，说明视图的属性列避免冲突
   CREATE VIEW IS_S1(Sno, Snmae , Grade)
   	AS
   	SELECT Student.Sno, Sname, Grade
   	FROM Student, SC 
   	WHERE 	Sdept = 'IS' AND
   			Student.Sno = SC.Sno AND
               SC.Cno = '1'
               
   DELETE VIEW IS_S1
   
   --更新VIEW同更新表
   UPDATE IS_S1
   SET snmae = 'aaa'
   WHERE sno = '11451';

   ​

SQL定义约束

非过程性和过程性完整性约束

1. 前者在创建表的时候定义，在任何对表执行 INSERT/UPDATE/DELETE 操作时，自动触发检查，确保数据始终满足约束条件
2. 后者在对相关数据进行操作的时候自动进行的判断

断言

根据 SQL 标准，断言创建后，任何对断言中所涉及的表执行数据修改操作时，都会触发断言检查，具体如下：

1. 触发的操作类型

只要执行以下任意一种操作，且操作涉及断言 CHECK 子句中引用的表 / 数据，就会触发检查：

• INSERT：插入新数据时，检查插入后的数据是否满足断言条件
• UPDATE：更新数据时，检查修改后的数据是否满足断言条件
• DELETE：删除数据时，检查删除后剩余的数据是否满足断言条件

2. 检查的核心逻辑

• 断言的 CHECK 条件必须在任何时刻都为真，只要有一次操作导致条件不成立，该操作就会被 DBMS 拒绝执行，数据不会被修改。
• 断言是声明式约束，由数据库自动触发，无需手动调用，也不支持 BEFORE/AFTER 等自定义时机（区别于触发器）。

--创建断言语法
CREATE ASSERTION assertionName
CHECK (searchCondition);

--删除断言
DROP ADDERTION assertionName；

--使用SQL断言机制定义完整性约束
--必须在选修‘数学’课后才能选修其他课程
> CREATE ASSERTION AsserMath01 CHECK (
	NOT EXISTS(
    	SELECT sno FROM SelCou
        WHERE cno IN (
        	SELECT cno FROM Course
            WHERE cname <> '数学'
        )
        AND sno NOT IN (
        	SELECT sno FROM Selcou
            WHERE cno IN (
            	SELECT cno FROM Course WHERE cname = '数学'
            )
        )
    )
)；

约束检测时机

控制数据库什么时候检查约束是否合法：

• 立即检测（默认）

  执行一句 SQL，马上检查约束，不满足就直接报错回滚。

• 延迟检测

  先不检查，等到 ** 事务提交（COMMIT）** 时再统一检查。

  适合：先破坏约束、最后再修复的场景。

  比如你前面那题：先改选课表、再改学生表，中间会短暂违反外键，就需要延迟检测。

--定义约束的时候可以指定立即检测或者延迟检测

CREATE TABLE 表名 (
    列名 类型,
    CONSTRAINT 约束名
        FOREIGN KEY(...) REFERENCES ...
        [ NOT DEFERRABLE | DEFERRABLE ]
        [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ]
);

触发器

更加灵活，可以跨表循环，针对更加基础的单一操作进行检测

--当向选课表 SelCou 插入一条记录时，自动输出 “新增选课成功”

CREATE TRIGGER tri_insert_selcou
AFTER INSERT ON SelCou
FOR EACH ROW
BEGIN
    -- 这里可以写日志、修改其他表、复杂判断
    INSERT INTO log(info) VALUES('新增一条选课');
END;