---

一、数据定义语言（DDL）

用于定义和管理数据库对象（如表、索引、视图等）。

1. CREATE：创建数据库对象

— 创建数据库
CREATE DATABASE database_name;

— 创建表
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    …
);

— 示例：创建一个员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,  — 主键，自增
    name VARCHAR(100) NOT NULL,         — 非空字符串
    age INT CHECK (age >= 0),           — 检查约束
    department_id INT,
    salary DECIMAL(10, 2) DEFAULT 0.00  — 默认值
);

2. ALTER：修改数据库对象

— 添加列
ALTER TABLE table_name ADD column_name datatype;

— 删除列
ALTER TABLE table_name DROP COLUMN column_name;

— 修改列的数据类型（具体语法因数据库而异）
ALTER TABLE table_name MODIFY column_name new_datatype;

— 示例：为 employees 表添加 email 列
ALTER TABLE employees ADD email VARCHAR(255);

3. DROP：删除数据库对象

— 删除表
DROP TABLE table_name;

— 删除数据库
DROP DATABASE database_name;

— 示例：删除 employees 表
DROP TABLE employees;

---

二、数据操作语言（DML）

用于操作数据库中的数据（增删改）。

1. INSERT：插入数据

— 插入完整行
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);

— 插入部分列
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

— 插入多行
INSERT INTO table_name (column1, column2)
VALUES
    (value1, value2),
    (value3, value4);

— 示例：插入员工数据
INSERT INTO employees (name, age, department_id, salary)
VALUES ('Alice', 30, 1, 5000.00);

2. UPDATE：更新数据

— 更新表中满足条件的记录
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

— 示例：将部门ID为1的员工工资增加10%
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 1;

3. DELETE：删除数据

— 删除满足条件的记录
DELETE FROM table_name
WHERE condition;

— 删除所有记录（保留表结构）
DELETE FROM table_name;

— 示例：删除年龄大于60的员工
DELETE FROM employees
WHERE age > 60;

---

三、数据查询语言（DQL）

用于从数据库中查询数据。

1. SELECT：基本查询

— 查询所有列
SELECT * FROM table_name;

— 查询指定列
SELECT column1, column2 FROM table_name;

— 去重查询
SELECT DISTINCT column_name FROM table_name;

— 示例：查询所有员工姓名和工资
SELECT name, salary FROM employees;

2. WHERE：条件过滤

— 使用比较运算符（=, <>, >, <, >=, <=）
SELECT * FROM table_name
WHERE column > value;

— 逻辑运算符（AND, OR, NOT）
SELECT * FROM employees
WHERE age > 25 AND department_id = 2;

— 范围查询（BETWEEN）
SELECT * FROM employees
WHERE salary BETWEEN 3000 AND 5000;

— 集合查询（IN）
SELECT * FROM employees
WHERE department_id IN (1, 2, 3);

— 模糊匹配（LIKE）
SELECT * FROM employees
WHERE name LIKE 'A%';  — 以A开头的名字

3. JOIN：多表连接

— 内连接（INNER JOIN）
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

— 左连接（LEFT JOIN）
SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

— 右连接（RIGHT JOIN）
SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

— 全连接（FULL JOIN）
SELECT employees.name, departments.name
FROM employees
FULL JOIN departments ON employees.department_id = departments.id;

4. 聚合函数与分组

— 聚合函数
SELECT COUNT(*) AS total, AVG(salary), MAX(salary), MIN(salary)
FROM employees;

— 分组查询（GROUP BY）
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

— 分组过滤（HAVING）
SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

5. 排序与分页

— 排序（ORDER BY）
SELECT * FROM employees
ORDER BY salary DESC;  — 降序排列

— 分页（LIMIT 和 OFFSET）
SELECT * FROM employees
LIMIT 10 OFFSET 20;  — 从第21条开始取10条记录

---

四、数据控制语言（DCL）

用于管理数据库权限。

1. GRANT：授予权限

— 授予用户对表的查询权限
GRANT SELECT ON table_name TO 'username'@'host';

— 授予所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

2. REVOKE：撤销权限

— 撤销用户的更新权限
REVOKE UPDATE ON table_name FROM 'username'@'host';

— 刷新权限
FLUSH PRIVILEGES;

---

五、常用函数

1. 字符串函数

SELECT CONCAT('Hello', ' ', 'World');  — 连接字符串
SELECT UPPER('hello');                 — 转大写
SELECT SUBSTRING('Hello World', 1, 5); — 取子串

2. 日期函数

SELECT NOW();                          — 当前日期和时间
SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);  — 日期加减
SELECT DATEDIFF('2023-01-10', '2023-01-01');    — 日期差

3. 数学函数

SELECT ROUND(3.14159, 2);  — 四舍五入
SELECT RAND();             — 随机数
SELECT ABS(-10);           — 绝对值

---

六、注意事项

1. 关键字大小写：SQL 关键字不区分大小写（如 SELECT 和 select 等效），但通常使用大写以增强可读性。
2. 引号：字符串用单引号 '，列名和表名通常不用引号（除非包含特殊字符）。
3. 通配符：% 匹配任意数量字符，_ 匹配单个字符。
4. 别名：使用 AS 为列或表起别名（如 SELECT name AS emp_name）。
5. NULL 值：使用 IS NULL 或 IS NOT NULL 判断空值。
6. 事务控制：
7. START TRANSACTION;  — 开始事务
   COMMIT;             — 提交事务
   ROLLBACK;           — 回滚事务
8. 安全性：避免直接使用 DROP 或 DELETE 时不加 WHERE 条件。

---

七、总结

SQL 的核心在于对数据的增删改查（CRUD）和结构管理。掌握上述基本语句后，可以通过组合使用（如子查询、视图、索引等）实现更复杂的功能。建议通过实际项目练习（如设计电商数据库、学生成绩管理系统等）巩固技能。

1.E-R图简介（什么是E-R图）
E-R模型（Entity-Relationship Model）是一种用于数据库设计的概念模型。它提供了一种描述现实世界中数据
组织和关联的图形化方法，用于表示实体、属性和联系之间的关系。
2.为什么要引入E-R图？
使用E-R模型有以下几个主要原因：

1. 数据建模：E-R模型提供了一种直观且易于理解的方法来建模现实世界中的数据。通过将实体、属性和关
   系抽象成图形化符号，可以更好地捕捉和表示数据之间的关系和结构。
2. 数据可视化：E-R模型允许将数据的组织和关联可视化。通过图形表示，可以清晰地展示实体之间的关
   系、属性的特征以及它们之间的连接方式。
3. 数据完整性：E-R模型有助于确保数据的完整性。通过定义实体之间的关系和约束条件，可以确保数据在
   插入、更新和删除时保持一致性和正确性。
4. 查询优化：E-R模型可以帮助优化数据库查询。通过了解实体之间的关系，可以设计出更有效的查询和连
   接方式，提高查询性能和响应时间。E-R模型还可以指导索引的创建，以支持常见的查询操作。
   总的来说，E-R模型提供了一种直观和规范的方法来描述和设计数据库。它有助于提高数据库设计的质量、数据
   的完整性和查询的性能，从而提升整个数据库系统的效率和可靠性。
   3.E-R图的构成
5. 实体（Entity）：实体表示现实世界中的一个独立对象，可以是人、物、地点、概念等。在E-R图中，实
   体用矩形框表示，框内写上实体的名称。
6. 属性（Attribute）：属性是描述实体特征的信息。每个实体可以有多个属性，例如一个人实体可以有姓
   名、年龄、性别等属性。属性以椭圆形状表示，并与相应的实体相连。其中能够唯一标识实体的属性称为
   主键。
7. 关系（Relationship）：关系表示实体之间的相互作用或联系。关系可以是一对一、一对多或多对多的。
   在E-R图中，关系用菱形表示，并与相关的实体相连。关系还可以具有属性，用于描述与关系相关的信
   息。
8. 主键（Primary Key）：用于唯一标识实体的属性，通常在实体框内用下划线或加粗表示。主键属性的值
   在整个实体集合中必须是唯一的，用于区分不同的实体。
   在 E-R 图中，根据实体之间的连接方式和关系类型，关联关系可以分为以下几种类型：
9. 一对一（One-to-One）关联：一个实体实例与另一个实体实例之间存在唯一的关联关系。这种关系表示为一个实
   体的一个实例与另一个实体的一个实例相连接。
10. 一对多（One-to-Many）关联：一个实体实例与另一个实体实例之间存在一对多的关联关系。这种关系表示为一个
    实体的一个实例与另一个实体的多个实例相连接。
11. 多对多（Many-to-Many）关联：多个实体实例与另一个实体实例之间存在多对多的关联关系。这种关系表示为一
    个实体的多个实例与另一个实体的多个实例相连接。
    我们来用图片举个例子：
    1.一对一
    2.一对多
    3.多对多
    在E-R图中，关系可以具有与之相关的属性，用于描述与该关系相关的信息。这些属性可以提供更详细的关系描
    述，以补充关系本身无法完全表达的信息。 比如职工与部门的工作关系，这个关系可以具有属性，如工作时
    间、工作性质。通过关系的属性以便于更好的描述职工在部门中的工作情况。
    这三个部分是构成E-R图最关键的部分，我们通过一个例子来加深印象：
    让我们来设计一个简单的 E-R 图来表示学生和课程之间的关系。假设我们有两个实体：学生（Student）和课程
    （Course）。一个学生可以注册多门课程，而一门课程可以有多名学生。
    设计过程如下：
12. 确定实体（Entities）：
13. 学生（Student）：属性包括学生ID（Student ID）、姓名（Name）、年级（Grade）等。
14. 课程（Course）：属性包括课程ID（Course ID）、课程名称（Course Name）、学分（Credit）等。
15. 确定关系（Relationship）：
16. 注册（Enrollment）：学生和课程之间的关系。这是一个多对多的关系，因为一个学生可以注册多门课程，而一
    门课程可以有多名学生。此关系可以具有属性，如选课日期（Enrollment Date）。
17. 确定主键（Primary Key）：
18. 学生实体的主键为学生ID（Student ID）。
19. 课程实体的主键为课程ID（Course ID）。
    E-R图如下：
    4.E-R图进阶
    恭喜你！现在你应该已经懂得如何画一个简单的E-R图了，现在我们要进一步学习E-R图中的部分细节。
    4.1弱实体
    想象一下，在数据库中，实体就像是人或物体，而属性就像是这些人或物体的特征。通常情况下，一个实体具
    有自己的标识，例如一个人有独特的身份证号码，一个产品有独特的产品编号。
    然而，有时候存在一种情况，某个实体的标识依赖于与其相关联的另一个实体。这时，我们称这个实体为弱实
    体。弱实体没有自己的唯一标识，它的标识需要依赖于与其相关联的另一个实体（强实体）。
    在 E-R 图中，弱实体通常用双矩形框表示。
    我们来举一个例子：
    订单小票和订单项（也就是小票上面的每个商品多少钱的那一项）之间的E-R图。
    订单项并不是一个单独存在的项，而是基于订单小票才会产生的一个实体，所以我们把它划分为弱实体。
    4.2部分键
    在 E-R 图中，弱实体通常没有自己的唯一标识，因此需要使用弱实体的部分键来唯一标识不同的实例。弱实体
    的部分键是通过指定其中一个属性与父实体的键结合从而形成相应弱实体的键，弱实体的这个属性称为弱实体
    的部分键。部分键用虚线标识。
    我们来举一个例子：
    还是订单小票和订单项（也就是小票上面的每个商品多少钱的那一项）之间的E-R图。
    这是我们上面的图片，我们基于这个进行一个拓展，把订单项这个弱实体中的部分键标识出来。
    我们只有通过商品ID与订单ID结合才能找到某一个订单项。 所以我们可以把商品ID定为部分键。

第一节
一、相关概念

1. Data：数据，是数据库中存储的基本对象，是描述事物的符号记录。
2. Database：数据库，是长期储存在计算机内、有组织的、可共享的大量数据的集合。
3. DBMS：数据库管理系统，是位于用户与操作系统之间的一层数据管理软件，用于科学地组织、存储和管理数据、高效地获取和维护数
   据。
4. DBS：数据库系统，指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统、应用系统、数据库管理员（DBA）构成
   。
5. 数据模型：是用来抽象、表示和处理现实世界中的数据和信息的工具，是对现实世界的模拟，是数据库系统的核心和基础；其组成元素
   有数据结构、数据操作和完整性约束。
6. 概念模型：也称信息模型，是按用户的观点来对数据和信息建模，主要用于数据库设计。
7. 逻辑模型：是按计算机系统的观点对数据建模，用于DBMS实现。
8. 物理模型：是对数据最底层的抽象，描述数据在系统内部的表示方式和存取方法，在磁盘或磁带上的存储方式和存取方法，是面向计算
   机系统的。
9. 实体和属性：客观存在并可相互区别的事物称为实体。实体所具有的某一特性称为属性。
   10.E-R图：即实体-关系图，用于描述现实世界的事物及其相互关系，是数据库概念模型设计的主要工具。
   11.关系模式：从用户观点看，关系模式是由一组关系组成，每个关系的数据结构是一张规范化的二维表。
   12.型/值：型是对某一类数据的结构和属性的说明；值是型的一个具体赋值，是型的实例。
   13.数据库模式：是对数据库中全体数据的逻辑结构（数据项的名字、类型、取值范围等）和特征（数据之间的联系以及数据有关的安全性
   、完整性要求）的描述。
   14.数据库的三级系统结构：外模式、模式和内模式。
   15.数据库内模式：又称为存储模式，是对数据库物理结构和存储方式的描述，是数据在数据库内部的表示方式。一个数据库只有一个内模
   式。
   16.数据库外模式：又称为子模式或用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据
   视图。通常是模式的子集。一个数据库可有多个外模式。
   17.数据库的二级映像：外模式/模式映像、模式/内模式映像。
   二、重点知识点
10. 数据库系统由数据库、数据库管理系统、应用系统和数据库管理员构成。
11. 数据模型的组成要素是：数据结构、数据操作、完整性约束条件。
12. 实体型之间的联系分为一对一、一对多和多对多三种类型。
13. 常见的数据模型包括：关系、层次、网状、面向对象、对象关系映射等几种。
14. 关系模型的完整性约束包括：实体完整性、参照完整性和用户定义完整性。
15. 阐述数据库三级模式、二级映象的含义及作用。
    数据库三级模式反映的是数据的三个抽象层次： 模式是对数据库中全体数据的逻辑结构和特征的描述。内模式又称为存储模式，是对数据
    库物理结构和存储方式的描述。外模式又称为子模式或用户模式，是对特定数据库用户相关的局部数据的逻辑结构和特征的描述。
    数据库三级模式通过二级映象在 DBMS 内部实现这三个抽象层次的联系和转换。外模式面向应用程序， 通过外模式/模式映象与逻辑模式
    建立联系， 实现数据的逻辑独立性。 模式/内模式映象建立模式与内模式之间的一对一映射， 实现数据的物理独立性。
    第二节
    一、相关概念
16. 主键： 能够唯一地标识一个元组的属性或属性组称为关系的键或候选键。 若一个关系有多个候选键则可选其一作为主键(Primary key)。
17. 外键：如果一个关系的一个或一组属性引用(参照)了另一个关系的主键，则称这个或这组属性为外码或外键(Foreign key)。
18. 关系数据库： 依照关系模型建立的数据库称为关系数据库。 它是在某个应用领域的所有关系的集合。
19. 关系模式： 简单地说，关系模式就是对关系的型的定义， 包括关系的属性构成、各属性的数据类型、 属性间的依赖、 元组语义及完整
    性约束等。 关系是关系模式在某一时刻的状态或内容， 关系模型是型， 关系是值， 关系模型是静态的、 稳定的， 而关系是动态的、随时间不
    断变化的，因为关系操作在不断地更新着数据库中的数据。
20. . 实体完整性：用于标识实体的唯一性。它要求基本关系必须要有一个能够标识元组唯一性的主键，主键不能为空，也不可取重复值。
21. 参照完整性： 用于维护实体之间的引用关系。 它要求一个关系的外键要么为空， 要么取与被参照关系对应的主键值，即外键值必须
    是主键中已存在的值。
22. 用户定义的完整性：就是针对某一具体应用的数据必须满足的语义约束。包括非空、 唯一和布尔条件约束三种情况。
    二、重要知识点
23. 关系数据库语言分为关系代数、关系演算和结构化查询语言三大类。
24. 关系的 5 种基本操作是选择、投影、并、差、笛卡尔积。
    3.关系模式是对关系的描述，五元组形式化表示为：R（U，D，DOM，F），其中
    R —— 关系名
    U —— 组成该关系的属性名集合
    D —— 属性组 U 中属性所来自的域
    DOM —— 属性向域的映象集合
    F —— 属性间的数据依赖关系集合
    4.笛卡尔乘积，选择和投影运算如下
    第三节
    一、相关概念
25. SQL：结构化查询语言的简称， 是关系数据库的标准语言。SQL 是一种通用的、 功能极强的关系数据库语言， 是对关系数据存取的标
    准接口， 也是不同数据库系统之间互操作的基础。集数据查询、数据操作、数据定义、和数据控制功能于一体。
26. 数据定义：数据定义功能包括模式定义、表定义、视图和索引的定义。
27. 嵌套查询：指将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询。
    二、重要知识点
28. SQL 数据定义语句的操作对象有：模式、表、视图和索引。
29. SQL 数据定义语句的命令动词是：CREATE、DROP 和 ALTER。
30. RDBMS 中索引一般采用 B+树或 HASH 来实现。
31. 索引可以分为唯一索引、非唯一索引和聚簇索引三种类型。
    6.SQL 创建表语句的一般格式为
    CREATE TABLE <表名>
    ( <列名> <数据类型>[ <列级完整性约束> ]
    [，<列名> <数据类型>[ <列级完整性约束>] ] …
    [，<表级完整性约束> ] ) ；
    其中<数据类型>可以是数据库系统支持的各种数据类型，包括长度和精度。
    列级完整性约束为针对单个列(本列)的完整性约束， 包括 PRIMARY KEY、 REFERENCES表名(列名)、UNIQUE、NOT NULL 等。
    表级完整性约束可以是基于表中多列的约束，包括 PRIMARY KEY ( 列名列表) 、FOREIGN KEY REFERENCES 表名(列名) 等。
32. SQL 创建索引语句的一般格式为
    CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
    ON <表名> (<列名列表> ) ；
    其中UNIQUE：表示创建唯一索引，缺省为非唯一索引；
    CLUSTER：表示创建聚簇索引，缺省为非聚簇索引；
    <列名列表>：一个或逗号分隔的多个列名，每个列名后可跟 ASC 或 DESC，表示升/降序，缺省为升序。多列时则按为多级排序。
33. SQL 查询语句的一般格式为
    SELECT [ALL｜DISTINCT] <算术表达式列表> FROM <表名或视图名列表>
    [ WHERE <条件表达式 1> ]
    [ GROUP BY <属性列表 1> [ HAVING <条件表达式 2 > ] ]
    [ ORDER BY <属性列表 2> [ ASC｜DESC ] ] ；
    其中
    ALL／DISTINCT： 缺省为 ALL， 即列出所有查询结果记录， 包括重复记录。 DISTINCT则对重复记录只列出一条。
    算术表达式列表：一个或多个逗号分隔的算术表达式，表达式由常量(包括数字和字符串)、列名、函数和算术运算符构成。每个表达式后还
    可跟别名。也可用 *代表查询表中的所有列。
    <表名或视图名列表>： 一个或多个逗号分隔的表或视图名。 表或视图名后可跟别名。
    条件表达式 1：包含关系或逻辑运算符的表达式，代表查询条件。
    条件表达式 2：包含关系或逻辑运算符的表达式，代表分组条件。
    <属性列表 1>：一个或逗号分隔的多个列名。
    <属性列表 2>： 一个或逗号分隔的多个列名， 每个列名后可跟 ASC 或 DESC， 表示升/降序，缺省为升序。
    关于SQL语句的知识这里先作如上简略介绍，具体写法下次将专门拿出一篇来叙述。
    第四节
    一、相关概念和知识
    1.触发器是用户定义在基本表上的一类由事件驱动的特殊过程。由服务器自动激活， 能执行更为复杂的检查和操作，具有更精细和更强大
    的数据控制能力。使用 CREATE TRIGGER 命令建立触发器。
    2.计算机系统存在技术安全、管理安全和政策法律三类安全性问题。
34. TCSEC/TDI 标准由安全策略、责任、保证和文档四个方面内容构成。
35. 常用存取控制方法包括自主存取控制(DAC)和强制存取控制(MAC)两种。
36. 自主存取控制(DAC)的 SQL 语句包括 GRANT 和 REVOKE 两个。 用户权限由数据对象和操作类型两部分构成。
37. 常见SQL 自主权限控制命令和例子。
    1) 把对 Student 和 Course 表的全部权限授予所有用户。
    GRANT ALL PRIVILIGES ON TABLE Student，Course TO PUBLIC ；
    2) 把对 Student 表的查询权和姓名修改权授予用户 U4。
    GRANT SELECT，UPDATE(Sname) ON TABLE Student TO U4 ；
    3) 把对 SC 表的插入权限授予 U5 用户，并允许他传播该权限。
    GRANT INSERT ON TABLE SC TO U5 WITHGRANT OPTION ；
    4) 把用户 U5 对 SC 表的 INSERT 权限收回，同时收回被他传播出去的授权。
    REVOKE INSERT ON TABLE SC FROM U5 CASCADE ；
    5) 创建一个角色 R1，并使其对 Student 表具有数据查询和更新权限。
    CREATE ROLE R1；
    GRANT SELECT，UPDATE ON TABLE Student TO R1；
    6) 对修改 Student 表结构的操作进行审计。
    AUDIT ALTER ON Student ；
    数据库知识总结(2)范式
    一、相关概念和知识点
    1.数据依赖：反映一个关系内部属性与属性之间的约束关系，是现实世界属性间相互联系的抽象，属于数据内在的性质和语义的体现。
38. 规范化理论：是用来设计良好的关系模式的基本理论。它通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异
    常、更新异常和数据冗余问题。
39. 函数依赖：简单地说，对于关系模式的两个属性子集X和Y，若X的任一取值能唯一确定Y的值，则称Y函数依赖于X，记作X→Y。
40. 非平凡函数依赖：对于关系模式的两个属性子集X和Y，如果X→Y，但Y!⊆X，则称X→Y为非平凡函数依赖；如果X→Y，但Y⊆X，则称
    X→Y为非平凡函数依赖。
41. 完全函数依赖：对于关系模式的两个属性子集X和Y，如果X→Y，并且对于X的任何一个真子集X'，都没有X'→Y，则称Y对X完全函数依
    赖。
42. 范式：指符合某一种级别的关系模式的集合。在设计关系数据库时，根据满足依赖关系要求的不同定义为不同的范式。
43. 规范化：指将一个低一级范式的关系模式，通过模式分解转换为若干个高一级范式的关系模式的集合的过程。
44. 1NF：若关系模式的所有属性都是不可分的基本数据项，则该关系模式属于1NF。
45. 2NF：1NF关系模式如果同时满足每一个非主属性完全函数依赖于码，则该关系模式属于2NF。
46. 3NF：若关系模式的每一个非主属性既不部分依赖于码也不传递依赖于码，则该关系模式属于3NF。
47. BCNF：若一个关系模式的每一个决定因素都包含码，则该关系模式属于BCNF。
48. 数据库设计：是指对于一个给定的应用环境，构造优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效
    地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求。
49. 数据库设计的6个基本步骤：需求分析，概念结构设计，逻辑结构设计，物理结构设计，数据库实施，数据库运行和维护。
50. 概念结构设计：指将需求分析得到的用户需求抽象为信息结构即概念模型的过程。也就是通过对用户需求进行综合、归纳与抽象，形成
    一个独立于具体DBMS的概念模型。
51. 逻辑结构设计：将概念结构模型（基本E-R图）转换为某个DBMS产品所支持的数据模型相符合的逻辑结构，并对其进行优化。
52. 物理结构设计：指为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程。包括设计数据库的存储结构与存取方法。
53. 抽象：指对实际的人、物、事和概念进行人为处理，抽取所关心的共同特性，忽略非本质的细节，并把这些特性用各种概念精确地加以
    描述，这些概念组成了某种模型。
54. 数据库设计必须遵循结构设计和行为设计相结合的原则。
55. 数据字典主要包括数据项、数据结构、数据流、数据存储和处理过程五个部分。
56. 三种常用抽象方法是分类、聚集和概括。
57. 局部 E-R 图之间的冲突主要表现在属性冲突、命名冲突和结构冲突三个方面。
58. 数据库常用的存取方法包括索引方法、聚簇方法和 HASH方法三种。
59. 确定数据存放位置和存储结构需要考虑的因素主要有： 存取时间、 存储空间利用率和维护代价等。
    二、细说数据库三范式
    2.1 第一范式（1NF）无重复的列
    第一范式（1NF）中数据库表的每一列都是不可分割的基本数据项
    同一列中不能有多个值
    即实体中的某个属性不能有多个值或者不能有重复的属性。
    简而言之，第一范式就是无重复的列。
    在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
    2.2 第二范式（2NF）属性完全依赖于主键[消除部分子函数依赖]
    满足第二范式（2NF）必须先满足第一范式（1NF）。
    第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。
    为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。
    第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属
    性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存
    储各个实例的惟一标识。简而言之，第二范式就是属性完全依赖于主键。
    2.3 第三范式（3NF）属性不依赖于其它非主属性[消除传递依赖]
    满足第三范式（3NF）必须先满足第二范式（2NF）。
    简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
    例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就
    不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则
    就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。
    2.4 具体实例剖析
    下面列举一个学校的学生系统的实例，以示几个范式的应用。
    在设计数据库表结构之前，我们先确定一下要设计的内容包括那些。学号、学生姓名、年龄、性别、课程、课程学分、系别、学科成绩，
    系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。我们对于这些信息，说关心的问题有如下几个方面。
    1）学生有那些基本信息
    2）学生选了那些课，成绩是什么
    3）每个课的学分是多少
    4）学生属于那个系，系的基本信息是什么。
    首先第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑
    型、日期型等。在当前的任何关系数据库管理系统（DBMS）中，不允许你把数据库表的一列再分成二列或多列，因此做出的都是符合第一范式
    的数据库。
    我们再考虑第二范式，把所有这些信息放到一个表中(学号，学生姓名、年龄、性别、课程、课程学分、系别、学科成绩，系办地址、系办
    电话)下面存在如下的依赖关系。
    1）（学号）→ (姓名, 年龄，性别，系别，系办地址、系办电话)
    2） (课程名称) → (学分)
    3）（学号，课程）→ (学科成绩)
    根据依赖关系我们可以把选课关系表SelectCourse改为如下三个表：
    学生：Student(学号，姓名, 年龄，性别，系别，系办地址、系办电话)；
    课程：Course(课程名称, 学分)；
    选课关系：SelectCourse(学号, 课程名称, 成绩)。
    事实上，对照第二范式的要求，这就是满足第二范式的数据库表，若不满足第二范式，会产生如下问题
    数据冗余： 同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
    更新异常： 1)若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。
    2)假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。
    删除异常 ： 假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。
    很显然，这也会导致插入异常。
    我们再考虑如何将其改成满足第三范式的数据库表，接着看上面的学生表Student(学号，姓名, 年龄，性别，系别，系办地址、系办电话)，
    关键字为单一关键字"学号"，因为存在如下决定关系：
    （学号）→ (姓名, 年龄，性别，系别，系办地址、系办电话)
    但是还存在下面的决定关系
    (学号) → (所在学院)→(学院地点, 学院电话)
    即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。
    它也会存在数据冗余、更新异常、插入异常和删除异常的情况（这里就不具体分析了，参照第二范式中的分析）。根据第三范式把学生关
    系表分为如下两个表就可以满足第三范式了：
    学生：(学号, 姓名, 年龄, 性别，系别)；
    系别：(系别, 系办地址、系办电话)。
    SQL语句总结
    SQL语句中常用关键词及其解释如下：
    1）SELECT
    将资料从数据库中的表格内选出，两个关键字：从 (FROM) 数据库中的表格内选出 (SELECT)。语法为
    SELECT "栏位名" FROM "表格名"。
    2）DISTINCT
    在上述 SELECT 关键词后加上一个 DISTINCT 就可以去除选择出来的栏位中的重复，从而完成求得这个表格/栏位内有哪些不同的值的功能。语
    法为
    SELECT DISTINCT "栏位名" FROM "表格名"。
    3）WHERE
    这个关键词可以帮助我们选择性地抓资料，而不是全取出来。语法为
    SELECT "栏位名" FROM "表格名" WHERE "条件"
    4）AND OR
    上例中的 WHERE 指令可以被用来由表格中有条件地选取资料。这个条件可能是简单的 (像上一页的例子)，也可能是复杂的。复杂条件是由二
    或多个简单条件透过 AND 或是 OR 的连接而成。语法为：
    SELECT "栏位名" FROM "表格名" WHERE "简单条件" {[AND|OR] "简单条件"}+
    5）IN
    在 SQL 中，在两个情况下会用到 IN 这个指令；这一页将介绍其中之一：与 WHERE 有关的那一个情况。在这个用法下，我们事先已知道至少
    一个我们需要的值，而我们将这些知道的值都放入 IN 这个子句。语法为：
    SELECT "栏位名" FROM "表格名" WHERE "栏位名" IN ('值一', '值二', …)
    6）BETWEEN
    IN 这个指令可以让我们依照一或数个不连续 (discrete)的值的限制之内抓出资料库中的值，而 BETWEEN 则是让我们可以运用一个范围 (range)
    内抓出资料库中的值，语法为：
    SELECT "栏位名" FROM "表格名" WHERE "栏位名" BETWEEN '值一' AND '值二'
    7）LIKE
    LIKE 是另一个在 WHERE 子句中会用到的指令。基本上， LIKE 能让我们依据一个模式(pattern) 来找出我们要的资料。语法为：
    SELECT "栏位名" FROM "表格名" WHERE "栏位名" LIKE {模式}
    8）ORDER BY
    我们经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大 (ascending) 或是由大往小(descending)。在这种情况下，我们就可以运
    用 ORDER BY 这个指令来达到我们的目的。语法为：
    SELECT "栏位名" FROM "表格名 [WHERE "条件"] ORDER BY "栏位名" [ASC, DESC]
    9）函数
    函数允许我们能够对这些数字的型态存在的行或者列做运算，包括 AVG (平均)、COUNT (计数)、MAX (最大值)、MIN (最小值)、SUM (总合)。
    语法为：
    SELECT "函数名"("栏位名") FROM "表格名"
    10）COUNT
    这个关键词能够帮我我们统计有多少笔资料被选出来，语法为：
    SELECT COUNT("栏位名") FROM "表格名"
    11）GROUP BY
    GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。语法为：
    SELECT "栏位1", SUM("栏位2") FROM "表格名" GROUP BY "栏位1"
    12）HAVING
    该关键词可以帮助我们对函数产生的值来设定条件。语法为：
    SELECT "栏位1", SUM("栏位2") FROM "表格名" GROUP BY "栏位1" HAVING (函数条件)
    13）ALIAS
    我们可以通过ALIAS为列名称和表名称指定别名，语法为：
    SELECT "表格别名"."栏位1" "栏位别名" FROM "表格名" "表格别名"
    下面为一个例子，通过它我们应该能很好地掌握以上关键词的使用方法。
    Student(S#,Sname,Sage,Ssex) 学生表
    Course(C#,Cname,T#) 课程表
    SC(S#,C#,score) 成绩表
    Teacher(T#,Tname) 教师表
    问题：
    1、查询“001”课程比“002”课程成绩高的所有学生的学号；
    select a.S#
    from (select s#,score from SC where C#=’001′) a,
    (select s#,score from SC where C#=’002′) b
    where a.score>b.score and a.s#=b.s#;
    2、查询平均成绩大于60分的同学的学号和平均成绩；
    select S#,avg(score)
    from sc
    group by S# having avg(score) >60;
    3、查询所有同学的学号、姓名、选课数、总成绩；
    select Student.S#,Student.Sname,count(SC.C#),sum(score)
    from Student left Outer join SC on Student.S#=SC.S#
    group by Student.S#,Sname
    4、查询姓“李”的老师的个数；
    select count(distinct(Tname))
    from Teacher
    where Tname like ‘李%’;
    5、查询没学过“叶平”老师课的同学的学号、姓名；
    select Student.S#,Student.Sname
    from Student
    where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’);
    6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
    select Student.S#,Student.Sname
    from Student,SC
    where Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);
    7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
    select S#,Sname
    from Student
    where S# in
    (select S#
    from SC ,Course ,Teacher
    where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select count(C#) from
    Course,Teacher where Teacher.T#=Course.T# and Tname=’叶平’));
    8、查询所有课程成绩小于60分的同学的学号、姓名；
    select S#,Sname
    from Student
    where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
    9、查询没有学全所有课的同学的学号、姓名；
    select Student.S#,Student.Sname
    from Student,SC
    where Student.S#=SC.S#
    group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； select S#,Sname from Student,SC where Student.S#=SC.S# and C# in （select C# from SC where S#='1001'）; 11、删除学习“叶平”老师课的SC表记录； Delect SC from course ,Teacher where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平'; 12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 SELECT L.C# 课程ID,L.score 最高分,R.score 最低分 FROM SC L ,SC R WHERE L.C# = R.C# and L.score = (SELECT MAX(IL.score) FROM SC IL,Student IM WHERE IL.C# = L.C# and IM.S#=IL.S# GROUP BY IL.C#) and R.Score = (SELECT MIN(IR.score) FROM SC IR WHERE IR.C# = R.C# GROUP BY IR.C# ); 13、查询学生平均成绩及其名次 SELECT 1+(SELECT COUNT( distinct 平均成绩) FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T1 WHERE 平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩
    FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T2
    ORDER BY 平均成绩 desc;
    14、查询各科成绩前三名的记录:(不考虑成绩并列情况)
    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
    FROM SC t1
    WHERE score IN (SELECT TOP 3 score
    FROM SC
    WHERE t1.C#= C#
    ORDER BY score DESC)
    ORDER BY t1.C#;
    15、查询每门功成绩最好的前两名
    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
    FROM SC t1
    WHERE score IN (SELECT TOP 2 score
    FROM SC
    WHERE t1.C#= C#
    ORDER BY score DESC )
    ORDER BY t1.C#;

题目答案:

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即可！

一、SQL语言分类
DDL（数据定义语言）
功能：定义/修改数据库结构（表、索引、视图）。
核心语句：CREATE, ALTER, DROP。
DML（数据操纵语言）
功能：操作数据（增删改查）。
核心语句：INSERT, UPDATE, DELETE, SELECT。
DCL（数据控制语言）
功能：管理权限。
核心语句：GRANT, REVOKE。

二、数据定义语言（DDL）
1. 表操作
CREATE TABLE
功能：创建新表，定义列、数据类型及约束。
示例：
CREATE TABLE Employee (
  Eno CHAR(4) PRIMARY KEY,       — 主键约束
  Ename VARCHAR(10) NOT NULL,    — 非空约束
  Age INT CHECK (Age >= 18),     — 检查约束（年龄≥18）
  Dno CHAR(2) REFERENCES Department(Dno)  — 外键约束
);
ALTER TABLE
功能：修改表结构（增删列、约束）。
示例：
ALTER TABLE Employee ADD COLUMN Phone VARCHAR(11);  — 新增列
ALTER TABLE Employee DROP COLUMN Phone;             — 删除列
ALTER TABLE Employee ALTER COLUMN Age TINYINT;      — 修改列类型
DROP TABLE
功能：删除表（结构+数据）。
对比：
DELETE FROM Employee：仅删除数据，保留表结构。
TRUNCATE TABLE Employee：快速清空表，不可回滚。

2. 索引（Index）
CREATE INDEX
功能：加速查询，支持排序/分组。
类型：
聚集索引：数据物理排序与索引一致（如字典目录）。
非聚集索引：独立存储索引和数据地址（如图书馆书名索引）。
示例：
CREATE CLUSTERED INDEX idx_dno ON Employee(Dno);  — 聚集索引
CREATE NONCLUSTERED INDEX idx_ename ON Employee(Ename);  — 非聚集索引
设计原则：
适用于大表、高频查询列（如WHERE、JOIN条件列）。
避免在小表或低选择性列（如性别）建索引。

3. 视图（View）
CREATE VIEW
功能：基于查询结果的虚拟表，简化复杂操作。
示例：
CREATE VIEW V_Emp_02 AS
SELECT * FROM Employee WHERE Dno = '02' WITH CHECK OPTION;  — 限制更新仅限部门02
作用：
简化查询：将多表连接结果保存为视图。
数据安全：隐藏敏感列（如仅允许查询工资范围）。
逻辑独立性：基表结构变化时，仅需调整视图定义。
限制：
含聚合函数（AVG）、DISTINCT或分组的视图不可更新。

三、数据操纵语言（DML）
1. 数据插入（INSERT）
功能：向表中添加数据。
示例：
— 单行插入
INSERT INTO Employee (Eno, Ename, Age) VALUES ('1001', '张三', 25);

— 多行插入（从其他表导入）
INSERT INTO Engineer SELECT * FROM Employee WHERE Title = '工程师';
2. 数据更新（UPDATE）
功能：修改现有数据。
示例：
— 条件更新
UPDATE Employee SET Age = 30 WHERE Ename = '张三';

— 子查询更新（技术科员工工资翻倍）
UPDATE Salary SET Basepay = Basepay * 2
WHERE Eno IN (SELECT Eno FROM Employee WHERE Dno = '技术科');
3. 数据删除（DELETE）
功能：删除数据。
示例：
— 删除指定行
DELETE FROM Employee WHERE Age > 60;

— 清空表（高效但不可回滚）
TRUNCATE TABLE Employee;

4. 数据查询（SELECT）
基础查询：
SELECT Ename, Age FROM Employee WHERE Dno = '02' ORDER BY Age DESC;
条件查询：
比较：WHERE Salary > 5000
范围：WHERE Age BETWEEN 20 AND 30
模糊匹配：WHERE Ename LIKE '张%'（匹配“张三”“张伟”）
聚合与分组：
SELECT Dno, AVG(Age) AS AvgAge
FROM Employee
GROUP BY Dno
HAVING AVG(Age) > 30;  — 筛选部门平均年龄>30
连接查询：
— 内连接（匹配两表关联数据）
SELECT E.Ename, D.Dname
FROM Employee E JOIN Department D ON E.Dno = D.Dno;

— 左外连接（保留左表未匹配行）
SELECT E.Ename, D.Dname
FROM Employee E LEFT JOIN Department D ON E.Dno = D.Dno;
子查询：
— 嵌套子查询（技术科员工）
SELECT * FROM Employee
WHERE Dno = (SELECT Dno FROM Department WHERE Dname = '技术科');

— EXISTS子查询（检查是否存在关联数据）
SELECT * FROM Employee E
WHERE EXISTS (SELECT 1 FROM Item_Emp WHERE Eno = E.Eno);

四、重点与难点
复杂查询：
多表连接（如Employee与Department）。
子查询嵌套（如EXISTS判断是否存在数据）。
性能优化：
合理使用索引，避免全表扫描。
避免在WHERE中对列使用函数（如YEAR(Date)导致索引失效）。
视图更新：
简单视图（行列子集）可更新，复杂视图（含分组/聚合）不可更新。

五、总结
DDL：定义结构（表、索引、视图），需注意约束和索引设计。
DML：操作数据，重点掌握复杂查询（连接、子查询、分组）。
索引：加速查询但需权衡维护成本。
视图：简化操作、增强安全性和逻辑独立性。
以下是结合知识库内容整理的SQL知识点，重点补充了自身连接、左右连接、复杂嵌套子查询的示例与讲解，以及WHERE与HAVING的区别：

六、连接查询
1. 自身连接（Self-Join）
定义：同一张表的不同实例进行连接，通过别名区分。
用途：处理表内数据的层级关系或自反关系（如员工与经理）。
示例：
— 查询每个员工的姓名及其对应的经理姓名
SELECT e.Ename AS Employee, m.Ename AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.MgrEno = m.Eno;  — MgrEno是经理编号
说明：
通过别名e（员工）和m（经理）区分同一表的两个实例。
使用LEFT JOIN确保无经理的员工也显示（如CEO）。

2. 左右连接（LEFT/RIGHT JOIN）
左外连接（LEFT JOIN）：
功能：返回左表所有记录，右表匹配的记录；未匹配的右表字段显示NULL。
示例：
— 查询所有部门及员工（即使部门无员工）
SELECT D.Dname, E.Ename
FROM Department D
LEFT JOIN Employee E ON D.Dno = E.Dno;
右外连接（RIGHT JOIN）：
功能：返回右表所有记录，左表匹配的记录；未匹配的左表字段显示NULL。
示例：
— 查询所有员工及其部门（即使员工未分配部门）
SELECT E.Ename, D.Dname
FROM Employee E
RIGHT JOIN Department D ON E.Dno = D.Dno;
对比：
LEFT JOIN关注左表完整性，RIGHT JOIN关注右表完整性。
通过交换表顺序，二者可互相转换。

七、复杂嵌套子查询
1. 嵌套子查询
定义：子查询独立执行，结果传递给外部查询。
示例：
— 查询部门“技术科”的员工姓名
SELECT Ename
FROM Employee
WHERE Dno = (SELECT Dno FROM Department WHERE Dname = '技术科');
2. 相关子查询
定义：子查询依赖外部查询的值，逐行执行。
示例：
— 查询比部门平均年龄大的员工
SELECT Ename, Age
FROM Employee E1
WHERE Age > (SELECT AVG(Age) FROM Employee E2 WHERE E2.Dno = E1.Dno);
3. EXISTS子查询
功能：判断子查询是否返回结果，常用于存在性检查。
示例：
— 查询参与过项目的员工
SELECT Ename
FROM Employee E
WHERE EXISTS (SELECT 1 FROM Item_Emp WHERE Eno = E.Eno);

八、WHERE与HAVING的区别
对比项
WHERE
HAVING
作用对象
行（基表或视图）
组（分组后的结果）
执行时间
分组前过滤
分组后过滤
聚合函数
不可使用（如AVG, SUM）
可使用聚合函数
示例
WHERE Age > 30
HAVING AVG(Age) > 30
典型场景：
— WHERE过滤行，HAVING过滤组
SELECT Dno, AVG(Age) AS AvgAge
FROM Employee
WHERE Age > 20                     — 过滤年龄>20的员工
GROUP BY Dno
HAVING AVG(Age) > 30;             — 过滤平均年龄>30的部门

九、重点总结
自身连接：通过别名处理表内层级关系（如员工与经理）。
左右连接：
LEFT JOIN保留左表所有行，RIGHT JOIN保留右表所有行。
未匹配的字段显示为NULL。
复杂子查询：
嵌套子查询独立执行，相关子查询逐行依赖外部查询。
EXISTS用于存在性判断。
WHERE vs HAVING：
WHERE在分组前过滤行，不可用聚合函数。
HAVING在分组后过滤组，必须与GROUP BY搭配使用。

第一章 绪论
1.1 数据库系统概述
四个基本概念
数据（Data）
定义：描述事物的符号记录，与其语义不可分割（如学生成绩、体重等）。
数据库（DB）
定义：长期存储、有组织、可共享的数据集合。
特征：结构化、共享性高、冗余度低、独立性高、易扩展。
数据库管理系统（DBMS）
功能：数据定义、组织存储、操纵、事务管理、维护等。
位于操作系统与应用系统之间，是核心基础软件。
数据库系统（DBS）
构成：数据库 + DBMS + 应用系统 + 数据库管理员（DBA）。
数据管理技术的发展
人工管理阶段（20世纪40-50年代）：数据不保存、不共享。
文件系统阶段（50-60年代）：数据长期保存，但冗余度高、独立性差。
数据库系统阶段（60年代后）：结构化、共享性高、独立性强。
数据库系统的特点
数据结构化（面向全组织，整体结构化）。
数据共享性高，冗余度低，易扩充。
数据独立性高（物理独立性、逻辑独立性）。
数据由DBMS统一管理（安全性、完整性、并发控制、恢复）。

1.2 数据模型
数据模型分类
概念模型（信息模型）：按用户视角建模，如E-R模型。
逻辑模型（机器视角）：层次模型、网状模型、关系模型等。
物理模型：数据在存储介质上的组织方式。
数据模型的组成要素
数据结构：数据的类型、关系（如关系模型中的表结构）。
数据操作：查询、插入、删除、修改。
完整性约束：实体完整性、参照完整性、用户定义完整性。
概念模型（E-R模型）
实体与联系
实体（Entity）：客观存在的事物（如学生、课程）。
属性（Attribute）：实体的特征（如学号、姓名）。
码（Key）：唯一标识实体的属性集。
联系（Relationship）：实体间的关联（1:1、1:N、M:N）。
E-R图表示法
实体：矩形框；属性：椭圆；联系：菱形（标注联系类型）。
常用数据模型
层次模型
结构：树形结构（根节点唯一，子节点有唯一父节点）。
优点：查询效率高；缺点：多对多联系表示复杂。
网状模型
结构：允许节点有多个父节点，支持多对多联系。
优点：直接表示复杂关系；缺点：结构复杂，用户难掌握。
关系模型
结构：二维表（行：元组；列：属性）。
特点：规范化（不可再分的数据项）、操作基于集合。
完整性约束：实体完整性、参照完整性、用户定义完整性。

1.3 数据库系统结构
三级模式结构
外模式（子模式）：用户视角的数据视图（如应用程序接口）。
模式（逻辑模式）：全局数据逻辑结构（如表结构定义）。
内模式（存储模式）：数据的物理存储方式（如索引、存储路径）。
二级映像与数据独立性
外模式/模式映像：保证逻辑独立性（模式变化时外模式可保持不变）。
模式/内模式映像：保证物理独立性（存储结构变化时模式可保持不变）。

1.4 数据库系统的组成
硬件：存储设备（磁盘、磁带）、高速通道。
软件：DBMS、操作系统、开发工具、应用系统。
人员
DBA：设计数据库结构、维护、安全管理。
系统分析员/设计人员：需求分析与数据库设计。
程序员：开发数据库应用。
用户：通过接口访问数据库（偶然用户、简单用户、复杂用户）。

1.5 小结
数据库系统的核心是数据模型与三级模式结构。
关系模型是主流模型，支持高数据独立性与规范化设计。
数据库系统的高效管理依赖DBMS与合理的体系结构。
以下是基于文件内容的第二章 关系数据库知识点归纳整理：
第二章 关系数据库
2.1 关系数据结构及形式化定义
关系模型基础
关系（Relation）：二维表结构，由行（元组）和列（属性）组成，基于集合代数理论构建。
域（Domain）：属性的取值范围（如整数、字符串、性别集合{男, 女}等）。
笛卡尔积：域的集合的笛卡尔积是所有域值组合的集合，每个元素称为一个元组。
关系定义：笛卡尔积的子集，具有实际意义的元组集合。
关系的关键概念
属性（Attribute）：表的列，描述实体的特征。
码（Key）
候选码：能唯一标识元组的最小属性组。
主码：选定的候选码，用于唯一标识元组。
外码（Foreign Key）：引用其他关系主码的属性（组）。
全码（All-Key）：所有属性共同组成候选码的极端情况。
关系的性质
列同质性（每列数据类型相同）。
元组唯一性（无重复行）。
分量原子性（属性值不可再分）。
行列顺序无关性（顺序不影响逻辑结构）。

2.2 关系操作
基本操作
查询操作：选择（σ）、投影（π）、连接（⋈）、除（÷）、并（∪）、差（−）、笛卡尔积（×）。
更新操作：插入、删除、修改。
集合操作：操作对象和结果均为集合（如并、交、差）。
关系语言分类
关系代数语言：基于代数运算（如ISBL）。
关系演算语言：基于谓词逻辑（如元组演算语言ALPHA、域演算语言QBE）。
SQL：结合关系代数与演算的结构化查询语言。

2.3 关系的完整性
实体完整性
规则：主码属性不能为空值（NULL）。
意义：确保每个元组可唯一标识。
参照完整性
规则：外码值必须为空值，或等于被参照关系的某个主码值。
示例：学生表的“专业号”必须引用专业表中存在的专业号。
用户定义的完整性
定义：针对具体应用的约束（如课程表的“学分”只能取1-4的整数）。
实现：通过DBMS提供的约束机制（如非空、唯一、检查约束）。

2.4 关系代数
基本运算
选择（σ）：按条件筛选元组（如σ_年龄>20(学生)）。
投影（π）：选择特定列（如π_姓名,年龄(学生)）。
笛卡尔积（×）：组合两个关系的元组。
并（∪）、差（−）：集合操作（要求属性相同）。
扩展运算
连接（⋈）：基于条件合并元组（如等值连接、自然连接）。
除（÷）：查询满足所有条件的元组（如查询选修所有课程的学生）。

2.5 关系演算
元组关系演算
语法：{ t | P(t) }，其中P为谓词（如t[学号] = '001'）。
示例：查询年龄大于20的学生：{ t | 学生(t) ∧ t.年龄 > 20 }。
域关系演算
语法：{ <x1, x2, …> | P(x1, x2, …) }（如QBE语言）。
示例：查询年龄为18的学生姓名：{  | ∃a (学生(学号, n, a) ∧ a = 18) }。

2.6 小结
核心内容：关系模型通过二维表结构统一表示实体与联系，依赖关系代数和完整性约束实现高效数据管理。
关键规则：实体完整性、参照完整性是关系数据库的基石，用户定义完整性增强语义约束。
实践意义：关系操作（如SQL）是数据库应用开发的基础，需熟练掌握查询与更新操作。
以下是基于文件内容的第二章 关系数据库（续）知识点归纳整理：
第二章 关系数据库（续）
2.4 关系代数（续）
2.4.1 传统的集合运算
并（Union）
定义：关系 ( R ) 和 ( S ) 具有相同目数（属性），且属性域相同，结果为属于 ( R ) 或 ( S ) 的元组集合。
符号：( R \cup S )。
差（Difference）
定义：结果为属于 ( R ) 但不属于 ( S ) 的元组集合。
符号：( R – S )。
交（Intersection）
定义：结果为同时属于 ( R ) 和 ( S ) 的元组集合。
符号：( R \cap S )。
笛卡尔积（Cartesian Product）
定义：关系 ( R )（( n ) 目）与 ( S )（( m ) 目）的笛卡尔积为 ( n + m ) 目关系，每个元组由 ( R ) 和 ( S ) 的元组组合而成。
符号：( R \times S )。

2.4.2 专门的关系运算
选择（Selection）
定义：从关系中选取满足条件的元组。
符号：( \sigma{F}(R) )，如 ( \sigma{\text{年龄}<20}(\text{学生}) )。
投影（Projection）
定义：从关系中选取指定属性列，消除重复元组。
符号：( \pi{A}(R) )，如 ( \pi{\text{姓名, 系}}(\text{学生}) )。
连接（Join）
等值连接：从笛卡尔积中选取属性值相等的元组。
符号：( R \bowtie_{A=B} S )。
自然连接：等值连接后去除重复属性列。
符号：( R \bowtie S )。
外连接：保留未匹配的元组，填充空值。
左外连接：保留左表所有元组。
右外连接：保留右表所有元组。
除（Division）
定义：给定关系 ( R(X, Y) ) 和 ( S(Y) )，结果为 ( R ) 中满足 ( Y ) 值包含 ( S ) 的 ( X ) 值。
符号：( R \div S )。
示例：查询选修所有课程的学生，即 ( \pi{\text{学号, 课号}}(\text{选修}) \div \pi{\text{课号}}(\text{课程}) )。

2.4.3 综合示例
查询选修1号和3号课程的学生：
构建临时关系 ( K = {1, 3} )。
计算 ( \pi_{\text{学号, 课号}}(\text{选修}) \div K )。
查询选修全部课程的学生：
( \pi{\text{学号, 课号}}(\text{选修}) \div \pi{\text{课号}}(\text{课程}) )。

2.5 关系演算
元组关系演算
语法：( { t | P(t) } )，其中 ( P ) 为谓词。
示例：查询年龄大于20的学生：
( { t | \text{学生}(t) \land t.\text{年龄} > 20 } )。
域关系演算
语法：( { <x_1, x_2, …> | P(x_1, x_2, …) } )。
示例：查询年龄为18的学生姓名：
( {  | \exists a (\text{学生}(\text{学号}, n, a) \land a = 18) } )。

2.6 小结
核心内容：
关系代数是关系数据库的理论基础，包括集合运算和专门运算。
除运算是复杂查询（如“全部”条件）的关键工具。
自然连接与外连接处理多表关联，需注意属性匹配和空值处理。
实践意义：
关系代数是SQL语言的理论基础，掌握其运算逻辑有助于优化查询语句。
综合示例展示了如何将实际问题转化为关系代数表达式。
以下是基于文件内容的第三章 SQL语言知识点归纳整理：
第三章 SQL语言
3.1 SQL概述
SQL的定义
Structured Query Language：结构化查询语言，关系数据库的标准语言。
特点：
一体化（集数据定义、操纵、控制于一体）。
高度非过程化（只需说明“做什么”，无需“怎么做”）。
面向集合操作（一次处理多行数据）。
SQL的组成
数据定义语言（DDL）：创建、修改、删除数据库对象（如表、索引）。
数据操纵语言（DML）：查询与更新数据（SELECT、INSERT、UPDATE、DELETE）。
数据控制语言（DCL）：权限管理（GRANT、REVOKE）。
嵌入式SQL：与宿主语言（如C、Java）结合使用。

3.2 数据定义（DDL）
创建表（CREATE TABLE）
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname VARCHAR(20) UNIQUE,
  Sage INT CHECK (Sage >= 18),
  Sdept VARCHAR(20) DEFAULT '计算机系'
);
修改表（ALTER TABLE）
添加列：ALTER TABLE Student ADD COLUMN Saddress VARCHAR(50);
删除列：ALTER TABLE Student DROP COLUMN Saddress;
修改约束：ALTER TABLE Student ALTER COLUMN Sage SET DEFAULT 18;
删除表（DROP TABLE）
DROP TABLE Student CASCADE;（级联删除依赖对象）。

3.3 数据查询（SELECT）
基本查询
投影：SELECT Sname, Sage FROM Student;
选择：SELECT * FROM Student WHERE Sage > 20;
排序：SELECT * FROM Student ORDER BY Sage DESC;
聚合函数
COUNT(), SUM(), AVG(), MAX(), MIN()
示例：SELECT AVG(Sage) FROM Student;
分组与过滤（GROUP BY + HAVING）
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept
HAVING AVG(Sage) > 20;
连接查询
内连接：SELECT * FROM Student JOIN Course ON Student.Cno = Course.Cno;
外连接：
左外连接：LEFT JOIN（保留左表所有行）。
右外连接：RIGHT JOIN（保留右表所有行）。
自然连接：NATURAL JOIN（自动匹配同名属性）。
子查询
标量子查询：返回单值（如WHERE Sage > (SELECT AVG(Sage) FROM Student)）。
行子查询：返回多列（如WHERE (Sno, Cno) IN (SELECT …)）。
表子查询：返回多行多列（如EXISTS子查询）。
集合查询
UNION（并集）、INTERSECT（交集）、EXCEPT（差集）。

3.4 数据更新（DML）
插入数据（INSERT）
单行插入：INSERT INTO Student VALUES ('001', '张三', 20, '计算机系');
多行插入：INSERT INTO Student SELECT …（从其他表导入）。
修改数据（UPDATE）
UPDATE Student
SET Sage = Sage + 1
WHERE Sdept = '计算机系';
删除数据（DELETE）
删除特定记录：DELETE FROM Student WHERE Sno = '001';
删除全部记录：DELETE FROM Student;（慎用！）

3.5 视图（View）
定义视图
CREATE VIEW CS_Student AS
SELECT * FROM Student
WHERE Sdept = '计算机系';
视图的作用
简化复杂查询。
提供逻辑独立性（基表结构变化时视图可保持稳定）。
实现安全性（限制用户访问敏感数据）。
更新限制
可更新视图：不含聚合、分组、DISTINCT的单表视图。
不可更新视图：涉及多表连接、子查询的视图。

3.6 索引（Index）
创建索引
CREATE [UNIQUE] INDEX Stu_Index
ON Student(Sname DESC, Sage ASC);
索引的优缺点
优点：加速查询（尤其WHERE、JOIN、ORDER BY操作）。
缺点：占用存储空间，降低更新速度（需维护索引）。

3.7 数据控制（DCL）
权限授予（GRANT）
GRANT SELECT, INSERT ON Student
TO User1 WITH GRANT OPTION;
权限回收（REVOKE）
REVOKE UPDATE ON Student
FROM User1 CASCADE;

3.8 事务处理
事务特性（ACID）
原子性（Atomicity）：事务全做或全不做。
一致性（Consistency）：事务保持数据完整性。
隔离性（Isolation）：并发事务互不干扰。
持久性（Durability）：提交后修改永久保存。
事务控制语句
COMMIT：提交事务。
ROLLBACK：回滚事务。
SAVEPOINT：设置保存点（部分回滚）。

3.9 小结
核心内容：
SQL是关系数据库的核心语言，涵盖数据定义、查询、更新与控制。
复杂查询（连接、子查询、集合操作）是SQL的重点与难点。
事务与索引是数据库性能与安全的关键技术。
实践意义：
熟练编写高效SQL语句是数据库应用开发的基础。
合理使用视图、索引与事务可优化系统性能与安全性。
以下是基于文件内容的第三章 SQL语言（续）知识点归纳整理：
第三章 SQL语言（续）
3.10 嵌入式SQL
基本概念
宿主语言：如C、Java等，与SQL结合使用。
预编译：将嵌入式SQL转换为宿主语言函数调用。
关键语法
声明部分：EXEC SQL BEGIN DECLARE SECTION; 和 EXEC SQL END DECLARE SECTION;
游标（Cursor）：处理多行查询结果。
声明游标：EXEC SQL DECLARE cur_student CURSOR FOR SELECT * FROM Student;
打开游标：EXEC SQL OPEN cur_student;
提取数据：EXEC SQL FETCH cur_student INTO :sno, :sname;
关闭游标：EXEC SQL CLOSE cur_student;
动态SQL
运行时构建SQL语句（如EXEC SQL EXECUTE IMMEDIATE 'DELETE FROM Student WHERE Sage > 20';）。

3.11 存储过程与函数
存储过程（Stored Procedure）
创建：
CREATE PROCEDURE UpdateScore(
  IN stu_no CHAR(9),
  IN new_score INT
)
BEGIN
  UPDATE SC SET Score = new_score WHERE Sno = stu_no;
END;
调用：CALL UpdateScore('001', 90);
函数（Function）
创建：
CREATE FUNCTION GetAvgScore(sdept VARCHAR(20))
RETURNS FLOAT
BEGIN
  DECLARE avg_score FLOAT;
  SELECT AVG(Score) INTO avg_score FROM Student WHERE Sdept = sdept;
  RETURN avg_score;
END;
调用：SELECT GetAvgScore('计算机系');

3.12 触发器（Trigger）
定义与作用
触发器：当特定事件（INSERT/UPDATE/DELETE）发生时自动执行的存储过程。
用途：实现复杂业务规则、数据审计、完整性约束。
语法示例
CREATE TRIGGER before_insert_student
BEFORE INSERT ON Student
FOR EACH ROW
BEGIN
  IF NEW.Sage < 18 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄不能小于18岁';
  END IF;
END;

3.13 高级查询优化
窗口函数（Window Functions）
ROW_NUMBER()：为结果集分区内的行分配唯一序号。
RANK() / DENSE_RANK()：基于排序的排名。
示例：
SELECT Sname, Sage,
       RANK() OVER (ORDER BY Sage DESC) AS age_rank
FROM Student;
递归查询（WITH RECURSIVE）
场景：查询树形结构（如组织层级、物料清单）。
示例：
WITH RECURSIVE org_tree AS (
  SELECT * FROM Employee WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.* FROM Employee e INNER JOIN org_tree o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM org_tree;

3.14 事务隔离级别
隔离级别类型
READ UNCOMMITTED：最低级别，允许脏读。
READ COMMITTED：仅读取已提交数据（避免脏读）。
REPEATABLE READ：保证同一事务中多次读取结果一致（避免不可重复读）。
SERIALIZABLE：最高级别，完全隔离（避免幻读）。
设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3.15 数据库安全性
用户认证与授权
创建用户：CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
角色管理：
CREATE ROLE 'developer';
GRANT SELECT, INSERT ON db.* TO 'developer';
GRANT 'developer' TO 'user1';
行级安全（RLS）
策略定义：限制用户只能访问特定行。
示例：
CREATE POLICY student_policy ON Student
FOR SELECT
USING (Sdept = CURRENT_USER);

3.16 小结
核心内容：
嵌入式SQL与存储过程增强数据库编程能力。
触发器实现自动化业务逻辑，窗口函数解决复杂分析需求。
事务隔离级别与安全性机制保障数据一致性与访问控制。
实践意义：
掌握高级SQL特性可提升复杂业务场景的开发效率。
合理使用触发器与隔离级别可避免数据异常与并发问题。
以下是基于文件内容的第三章 SQL语言（续）知识点归纳整理：
第三章 SQL语言（续）
3.17 数据库完整性约束
实体完整性
主键约束：
ALTER TABLE Student ADD CONSTRAINT PK_Sno PRIMARY KEY (Sno);
唯一约束：
ALTER TABLE Student ADD CONSTRAINT UQ_Sname UNIQUE (Sname);
参照完整性
外键约束：
ALTER TABLE SC
ADD CONSTRAINT FK_Sno FOREIGN KEY (Sno) REFERENCES Student(Sno)
ON DELETE CASCADE ON UPDATE CASCADE;
用户定义的完整性
检查约束：
ALTER TABLE Student ADD CONSTRAINT CK_Sage CHECK (Sage >= 18);
默认值约束：
ALTER TABLE Student ALTER COLUMN Sdept SET DEFAULT '计算机系';

3.18 规范化理论
范式（Normal Forms）
第一范式（1NF）：属性不可再分（如地址字段需拆分为省、市、街道）。
第二范式（2NF）：消除非主属性对候选码的部分依赖。
第三范式（3NF）：消除非主属性对候选码的传递依赖。
BC范式（BCNF）：消除主属性对候选码的部分和传递依赖。
反规范化
适用场景：以牺牲部分范式为代价提升查询性能（如冗余字段、预计算字段）。
示例：在订单表中冗余存储商品价格以避免频繁JOIN。

3.19 查询优化
优化策略
选择运算优化：优先执行选择运算以减少中间结果集。
连接顺序优化：小表驱动大表（如Student JOIN SC而非SC JOIN Student）。
索引优化：为WHERE、JOIN、ORDER BY字段创建索引。
执行计划分析
EXPLAIN命令：查看SQL执行计划（如MySQL的EXPLAIN SELECT …）。
关键指标：扫描行数、索引使用情况、临时表创建。

3.20 数据库设计案例
需求分析
确定实体（学生、课程、教师）、属性（学号、课程名、职称）及联系（选课、授课）。
E-R图设计
实体：学生（Sno, Sname, Sage）、课程（Cno, Cname）。
联系：选课（SC: Sno, Cno, Score），授课（Teaching: Tno, Cno）。
逻辑模型转换
实体表：Student, Course, Teacher。
联系表：SC, Teaching，外键约束确保参照完整性。

3.21 分布式数据库基础
分布式数据库特点
数据分片：水平分片（按行）、垂直分片（按列）。
复制与同步：主从复制、多主复制。
事务管理：两阶段提交（2PC）保证分布式事务一致性。
SQL分布式查询
数据定位：SELECT * FROM Student WHERE Sdept = '计算机系' AT SITE1;
跨节点JOIN：通过全局名称或链接服务器实现。

3.22 小结
核心内容：
完整性约束是数据库可靠性的基石，需结合业务需求设计。
规范化理论指导数据库设计，避免冗余与更新异常。
查询优化与分布式技术是应对大数据与高并发的关键。
实践意义：
合理设计数据库结构可显著提升系统性能与可维护性。
掌握分布式SQL技术是应对现代分布式系统的基础能力。
以下是基于文件内容的第四章 数据库安全性知识点归纳整理：
第四章 数据库安全性
4.1 数据库安全性概述
安全性定义
核心目标：防止未授权访问、篡改或破坏数据，确保数据的机密性、完整性和可用性。
应用场景：军事机密、金融数据、医疗档案等敏感信息保护。
安全标准
TCSEC/TDI标准（橘皮书）：
安全级别划分（从低到高）：D（最低）、C1/C2、B1/B2/B3、A1（最高）。
关键指标：安全策略、责任、保证、文档。
CC标准（Common Criteria）：国际通用的安全评估标准。

4.2 数据库安全性控制
用户标识与鉴别
用户标识：通过用户名、用户ID等唯一标识用户。
鉴别方法：口令验证、生物特征识别、动态令牌等。
存取控制
自主存取控制（DAC）
授权与回收：
GRANT SELECT, INSERT ON Student TO User1;  — 授权
REVOKE UPDATE ON Student FROM User1;       — 回收权限
缺点：可能因权限传递导致数据泄露。
强制存取控制（MAC）
敏感度标记：
许可证级别（主体，如用户）：Top Secret > Secret > Confidential > Public。
密级（客体，如数据）：同上。
规则：
读操作：主体许可证级别 ≥ 客体密级。
写操作：主体许可证级别 = 客体密级。
数据库角色
角色定义：将权限绑定到角色，简化授权管理。
示例：
CREATE ROLE Manager;                — 创建角色
GRANT SELECT ON SC TO Manager;      — 角色授权
GRANT Manager TO User1, User2;      — 分配角色

4.3 视图机制
安全作用：通过视图限制用户访问特定数据。
示例：
CREATE VIEW CS_Student AS
SELECT * FROM Student WHERE Sdept = '计算机系';  — 创建视图
GRANT SELECT ON CS_Student TO WangPing;          — 授权视图

4.4 审计（Audit）
功能：记录用户操作日志，追踪非法行为。
分类：
用户级审计：针对特定表或视图的操作记录。
系统级审计：监控登录、权限变更等全局事件。
SQL语法：
AUDIT ALTER, UPDATE ON SC;       — 开启审计
NOAUDIT ALTER, UPDATE ON SC;     — 关闭审计

4.5 数据加密
加密方法：
替换加密：替换数据中的字符（如凯撒密码）。
置换加密：重新排列数据位置（如列置换）。
混合加密：结合对称与非对称加密（如AES+RSA）。
应用场景：敏感字段加密（如密码、身份证号）。

4.6 统计数据库安全性
问题：通过多次合法查询推导出敏感信息（如某疾病患者数量）。
防护策略：
查询限制：单次查询需覆盖足够多记录（如N≥100）。
交叉保护：限制查询结果的重叠比例（如M≤10%）。

4.7 小结
核心内容：
存取控制（DAC与MAC）是数据库安全的核心机制。
审计与加密提供事后追踪与主动防护。
统计数据库需防范推导攻击，确保数据匿名化。
实践意义：
合理设计权限体系与加密策略可有效防范数据泄露。
审计日志是合规性检查（如GDPR）的重要依据。
以下是基于文件内容的第五章 数据库完整性知识点归纳整理：
第五章 数据库完整性
5.1 完整性约束概述
完整性定义
目标：确保数据库中数据的正确性、有效性和一致性。
分类：
实体完整性：主键唯一且非空。
参照完整性：外键必须引用已存在的主键或为空。
用户定义完整性：业务规则约束（如年龄范围、性别枚举）。
约束实施机制
静态约束：数据本身需满足的条件（如字段非空）。
动态约束：数据操作时需满足的条件（如余额不能为负）。

5.2 实体完整性
主键约束
定义方式：
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname VARCHAR(20)
);
验证规则：
主键值唯一且不允许为空。
更新主键时需保证新值不冲突。
唯一约束
示例：确保姓名唯一：
ALTER TABLE Student ADD CONSTRAINT UQ_Sname UNIQUE (Sname);

5.3 参照完整性
外键约束
定义方式：
CREATE TABLE SC (
  Sno CHAR(9),
  Cno CHAR(4),
  Score INT,
  FOREIGN KEY (Sno) REFERENCES Student(Sno)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
外键操作规则：
ON DELETE CASCADE：删除主表记录时级联删除子表记录。
ON UPDATE CASCADE：更新主表主键时同步更新子表外键。
SET NULL：删除或更新时将外键设为空值。
违反参照完整性的处理
拒绝操作（默认行为）。
级联操作（CASCADE）。
置空（SET NULL）。

5.4 用户定义的完整性
属性约束
非空约束（NOT NULL）：
Sname VARCHAR(20) NOT NULL
检查约束（CHECK）：
Sage INT CHECK (Sage >= 18 AND Sage <= 60)
默认值约束（DEFAULT）：
Sdept VARCHAR(20) DEFAULT '计算机系'
域约束
自定义数据类型：
CREATE DOMAIN GenderType CHAR(2) CHECK (VALUE IN ('男', '女'));

5.5 触发器（Trigger）
触发器的作用
主动完整性检查：在INSERT、UPDATE、DELETE时自动执行预定义逻辑。
复杂业务规则：如更新库存时自动计算总价。
触发器语法
CREATE TRIGGER before_update_score
BEFORE UPDATE ON SC
FOR EACH ROW
BEGIN
  IF NEW.Score < 0 OR NEW.Score > 100 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '成绩必须在0-100之间';
  END IF;
END;

5.6 事务与完整性
事务的ACID特性
原子性（Atomicity）：事务全做或全不做。
一致性（Consistency）：事务保持数据完整。
隔离性（Isolation）：并发事务互不干扰。
持久性（Durability）：提交后修改永久保存。
事务与约束的协同
事务回滚时撤销违反约束的操作。
事务提交前需通过所有完整性检查。

5.7 完整性约束的冲突与解决
常见冲突场景
插入外键值时主表无对应主键。
更新主键导致子表外键失效。
检查约束与业务逻辑冲突。
解决策略
优先级规则：系统约束 > 用户定义约束。
延迟检查：事务提交时再验证约束。

5.8 小结
核心内容：
实体完整性与参照完整性是关系模型的基础，用户定义完整性扩展业务规则。
触发器提供灵活的主动约束机制，事务保证操作的原子性与一致性。
实践意义：
合理设计约束可避免数据异常（如脏数据、孤儿记录）。
触发器需谨慎使用，避免过度耦合导致维护困难。
以下是基于文件内容的第六章 数据库恢复技术知识点归纳整理：
第六章 数据库恢复技术
6.1 事务与恢复概述
事务的ACID特性
原子性（Atomicity）：事务全做或全不做。
一致性（Consistency）：事务保持数据完整。
隔离性（Isolation）：并发事务互不干扰。
持久性（Durability）：提交后修改永久保存。
故障类型
事务故障：逻辑错误（如违反约束、死锁）。
系统故障：断电、OS崩溃导致数据丢失。
介质故障：磁盘损坏导致数据不可访问。

6.2 日志文件
日志的作用
记录事务对数据库的更新操作（如INSERT、UPDATE、DELETE）。
支持UNDO（撤销未提交事务）和REDO（重做已提交事务）。
日志记录内容
事务标识（TID）、操作类型、数据对象、旧值、新值。
示例：<T1, start>, <T1, A, 100, 200>, <T1, commit>
日志管理原则
先写日志原则（WAL）：日志记录必须在数据修改前写入磁盘。

6.3 恢复技术
UNDO与REDO
UNDO：撤销未提交事务（如回滚或系统故障）。
REDO：重做已提交事务（如系统故障后恢复）。
检查点（Checkpoint）
作用：减少恢复时需处理的日志量。
步骤：
将当前内存中的脏页（已修改但未写入磁盘的数据）写入磁盘。
记录检查点日志（如<Checkpoint, T1, T2>）。

6.4 基于日志的恢复策略
事务故障恢复
UNDO失败事务：反向扫描日志，撤销未提交操作。
系统故障恢复
REDO已提交事务：正向扫描日志，重做已提交但未写入磁盘的操作。
UNDO未提交事务：反向扫描日志，撤销未提交操作。
介质故障恢复
备份恢复：从备份中恢复数据，结合日志重做后续操作。

6.5 ARIES恢复算法
三阶段恢复
分析阶段：确定故障时未提交的事务及受影响的数据页。
重做阶段：从检查点开始重做所有已提交事务。
撤销阶段：撤销未提交事务。
关键数据结构
事务表（Transaction Table）：记录活跃事务状态。
脏页表（Dirty Page Table）：记录内存中已修改但未写入磁盘的数据页。

6.6 备份与恢复策略
备份类型
完全备份：备份全部数据库数据。
增量备份：仅备份自上次备份以来修改的数据。
归档日志模式
启用归档日志后，日志文件在检查点后保留，支持时间点恢复（PITR）。

6.7 小结
核心内容：
日志与检查点是恢复技术的核心，确保数据的原子性与持久性。
ARIES算法通过分析、重做、撤销三阶段实现高效恢复。
备份策略需结合业务需求选择完全或增量备份。
实践意义：
理解恢复机制可设计高可用性数据库系统。
合理配置日志与检查点可显著减少故障恢复时间。
以下是基于文件内容的第七章 数据库设计知识点归纳整理：
第七章 数据库设计
7.1 数据库设计概述
设计目标
构造优化的数据库逻辑模式和物理结构，建立高效、可靠的数据库应用系统。
满足用户的信息管理需求和数据操作需求，支持未来扩展。
设计特点
三分技术，七分管理：需结合技术实现与业务管理需求。
迭代性：设计过程需反复调整以适应需求变化。
设计步骤
需求分析 → 概念设计 → 逻辑设计 → 物理设计 → 实施与维护。

7.2 需求分析
任务
调查用户需求，明确数据与处理要求（信息、功能、安全等）。
确定系统边界与功能范围。
方法
用户访谈：通过问卷、会议等方式收集需求。
数据流分析：绘制数据流图（DFD），明确数据输入/输出与处理流程。
建立数据字典：定义数据结构、关系及约束。
输出成果
数据字典：包含数据项、结构、流、存储等的详细描述。
需求说明书：明确功能需求与非功能需求（性能、安全）。

7.3 概念结构设计
核心任务
构建概念模型（如E-R图），抽象描述现实世界的实体与联系。
设计步骤
实体识别：确定核心实体（如学生、课程）。
属性定义：为实体分配属性（如学号、姓名）。
联系建立：定义实体间关系（1:1、1:N、M:N）及属性（如选课成绩）。
优化模型：消除冗余，确保模型简洁清晰。
E-R图表示
矩形：实体；椭圆：属性；菱形：联系；线段：连接实体与联系。

7.4 逻辑结构设计
核心任务
将概念模型（E-R图）转换为关系模式，并进行规范化处理。
转换规则
实体转换：每个实体转化为一个关系表（如学生表）。
联系转换：
1:1联系：合并到任一实体表或独立成表。
1:N联系：外键添加到“多”方表。
M:N联系：独立成表（含双方主键及联系属性）。
规范化处理
范式应用：确保关系模式满足1NF（原子性）、2NF（消除部分依赖）、3NF（消除传递依赖）、BCNF（消除主属性依赖）。
反规范化：在性能需求下适当冗余（如预计算字段）。

7.5 物理结构设计
设计内容
存储结构：确定数据文件组织方式（堆、索引、聚簇）。
存取方法：选择索引类型（B+树、哈希）以优化查询。
存储分配：规划磁盘空间，考虑数据增长与访问频率。
性能优化
索引策略：为高频查询字段（如主键、外键）创建索引。
分区技术：水平/垂直分表提升大数据量处理效率。

7.6 数据库实施与维护
实施阶段
建库建表：使用DDL语句创建数据库对象。
数据入库：通过ETL工具或脚本导入初始数据。
功能开发：编写应用程序接口（如SQL、ORM）。
系统测试：验证功能正确性与性能指标。
维护阶段
性能监控：定期分析慢查询日志，优化索引与SQL语句。
备份恢复：制定备份策略（全量、增量），确保数据安全。
版本升级：根据需求扩展字段或调整结构。

7.7 小结
核心内容：
数据库设计需经历需求分析、概念建模、逻辑转换、物理优化及实施维护的完整流程。
规范化是逻辑设计的核心，E-R模型是概念设计的基础工具。
物理设计需权衡存储效率与查询性能，维护阶段需持续优化与监控。
实践意义：
合理的设计流程可减少冗余与异常，提升系统扩展性与稳定性。
规范化与反规范化的平衡是实际项目中的关键决策点。
以下是基于文件内容的第七章 数据库设计（续）知识点归纳整理：
第七章 数据库设计（续）
7.8 高级逻辑设计
反规范化技术
适用场景：以冗余换取查询性能提升（如OLAP系统）。
常见方法：
增加冗余列：预计算常用连接字段（如订单表冗余商品价格）。
合并表：将频繁JOIN的表合并为宽表。
代价：增加存储开销，需额外维护一致性（如触发器或批量更新）。
视图设计
逻辑数据独立性：通过视图隔离基表结构变化。
安全性：限制用户仅能访问视图定义的数据子集。
示例：
CREATE VIEW Student_Course AS
SELECT S.Sno, Sname, Cno, Score
FROM Student S JOIN SC ON S.Sno = SC.Sno;

7.9 物理设计优化
索引策略
聚簇索引：数据按索引顺序存储（每表仅一个聚簇索引）。
覆盖索引：索引包含查询所需全部字段，避免回表（如CREATE INDEX idx ON SC(Sno, Score)）。
组合索引：按查询条件顺序创建（如WHERE Sno = '001' AND Cno = 'C1'）。
分区技术
水平分区：按行划分（如按时间分区订单表）。
垂直分区：按列划分（如将大字段单独存储）。
分区键选择：需与查询条件匹配（如范围查询常用时间字段）。
存储参数调优
填充因子：控制索引页填充比例，减少页分裂（如高更新表设为70%）。
缓冲池配置：分配更多内存给热点数据页。

7.10 分布式数据库设计
设计挑战
数据分片：水平分片（按行划分）、垂直分片（按列划分）。
副本管理：主从复制、多主复制，需解决冲突（如时间戳或仲裁机制）。
分布式事务：两阶段提交（2PC）保证ACID特性。
分片策略
哈希分片：根据键值哈希分布（如用户ID % 分片数）。
范围分片：按键值范围划分（如订单按日期分片）。
混合分片：结合哈希与范围（如先按地区哈希，再按时间范围）。

7.11 数据库安全设计
权限控制
最小权限原则：仅授予用户必要的权限。
角色分级：如管理员、审计员、普通用户。
加密设计
透明数据加密（TDE）：加密整个数据文件（如Oracle TDE）。
字段级加密：敏感字段单独加密（如AES加密身份证号）。
审计设计
细粒度审计：记录敏感操作（如修改工资表的SQL语句）。
日志分离：审计日志存储于独立服务器，防篡改。

7.12 数据库性能调优
查询优化
执行计划分析：使用EXPLAIN查看索引使用情况。
慢查询日志：定位执行时间超过阈值的SQL。
参数调优
内存分配：调整缓冲池、日志缓冲区大小。
并发控制：设置最大连接数、锁超时时间。
批量操作优化
批量插入：使用LOAD DATA INFILE或多值INSERT。
批量更新：结合CASE WHEN实现一次语句更新多行。

7.13 设计案例：电商系统
需求分析
实体：用户、商品、订单、库存。
关键操作：下单、支付、库存扣减、物流跟踪。
E-R模型
用户表：用户ID、姓名、地址。
订单表：订单ID、用户ID、商品ID、数量、金额。
库存表：商品ID、库存量、版本号（乐观锁）。
反规范化设计
订单快照：冗余商品名称与价格，避免历史数据变更影响查询。

7.14 小结
核心内容：
反规范化与索引优化是提升性能的关键手段。
分布式设计需权衡分片策略与事务一致性。
安全设计需结合权限、加密与审计形成纵深防御。
实践意义：
物理设计需根据业务场景选择存储与索引策略（如OLTP与OLAP差异）。
持续的性能监控与调优是数据库稳定运行的保障。
以下是基于文件内容的第七章 数据库设计（续2）知识点归纳整理：
第七章 数据库设计（续2）
7.4 逻辑结构设计（续）
7.4.1 E-R图向关系模型的转换
转换规则
实体转换：每个实体转化为一个关系表，主键为实体的主码。
示例：学生（学号，姓名，年龄，系）
联系转换：
1:1联系：可合并到任一实体表或独立成表。
1:N联系：外键添加到“多”方表。
M:N联系：独立成表，包含双方主键及联系属性。
示例：选修（学号，课程号，成绩）
属性处理
复合属性：分解为简单属性（如“地址”拆分为省、市）。
多值属性：单独建表（如电话（学号，电话号码））。

7.4.2 数据模型的优化
规范化处理
范式应用：确保关系模式满足3NF或BCNF，消除冗余。
反规范化：在查询性能需求下适当冗余（如预计算字段）。
优化策略
分解冗余表：拆分频繁更新的大表。
合并关联表：减少JOIN操作（如订单表冗余商品名称）。

7.4.3 用户子模式设计
设计目标
简化用户视图，提高安全性。
支持不同用户需求（如管理层与操作层）。
实现方法
视图机制：为不同角色创建定制视图。
字段别名：使用业务术语替代技术字段名。

7.5 数据库物理设计
存储结构设计
行存储 vs 列存储：OLTP适用行存储，OLAP适用列存储。
分区策略：
水平分区：按时间或范围划分（如订单按年分区）。
垂直分区：分离冷热数据（如将大字段单独存储）。
索引设计
B+树索引：适合范围查询与排序。
哈希索引：适合等值查询（如用户登录验证）。
聚簇索引：按主键物理排序，提升查询效率。
硬件资源分配
内存配置：增大缓冲池以提升高频数据访问速度。
磁盘规划：日志文件与数据文件分离存储。

7.6 数据库实施与维护
数据加载
ETL工具：使用Kettle或Informatica迁移数据。
批量导入：通过LOAD DATA INFILE加速初始数据入库。
试运行与调试
压力测试：模拟高并发场景验证系统稳定性。
SQL优化：通过EXPLAIN分析执行计划，添加缺失索引。
运行维护
性能监控：定期分析慢查询日志，优化Top SQL。
备份策略：
全量备份：每日凌晨执行。
增量备份：每小时备份事务日志。
版本升级：通过灰度发布逐步迁移新旧数据。

7.7 小结
核心内容：
逻辑设计需平衡规范化与性能，E-R图转换是关键。
物理设计需结合业务场景选择存储与索引策略。
维护阶段需持续监控性能，定期优化与备份。
实践意义：
合理设计用户子模式可提升系统易用性与安全性。
物理设计直接影响系统吞吐量与响应时间，需反复调优。
以下是基于文件内容的第八章 数据库编程知识点归纳整理：
第八章 数据库编程
8.1 嵌入式SQL
基本概念
嵌入式SQL：将SQL语句嵌入到宿主语言（如C、Java）中，通过预编译器处理生成可执行代码。
宿主语言：负责流程控制与数据处理，SQL负责数据库操作。
处理流程
预编译：将嵌入式SQL转换为宿主语言函数调用。
绑定参数：通过主变量（Host Variable）传递数据。
执行与通信：SQL执行结果通过主变量返回宿主语言。
游标（Cursor）
作用：处理多行查询结果，逐条读取数据。
操作步骤：
声明游标：DECLARE CURSOR。
打开游标：OPEN。
提取数据：FETCH。
关闭游标：CLOSE。
示例：
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, salary FROM Employee WHERE dept = 'Sales';
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO :name, :salary;
动态SQL
适用场景：运行时动态生成SQL语句。
语法：
EXEC SQL PREPARE stmt FROM :sql_string;
EXEC SQL EXECUTE stmt;

8.2 存储过程（Stored Procedure）
定义与优势
存储过程：预编译并存储在数据库中的SQL代码块，可通过名称调用。
优点：
减少网络传输开销。
提高代码复用性与执行效率。
增强安全性（通过权限控制）。
PL/SQL块结构
CREATE PROCEDURE procedure_name (参数列表)
BEGIN
  — SQL语句与逻辑控制
  DECLARE变量;
  IF条件 THEN
    — 操作
  END IF;
  FOR循环…
END;
示例：转账存储过程
CREATE PROCEDURE transfer(
  IN from_account CHAR(10),
  IN to_account CHAR(10),
  IN amount DECIMAL
)
BEGIN
  START TRANSACTION;
  UPDATE Accounts SET balance = balance – amount WHERE account_id = from_account;
  UPDATE Accounts SET balance = balance + amount WHERE account_id = to_account;
  COMMIT;
END;
调用与管理
调用：CALL transfer('A001', 'A002', 1000);
删除：DROP PROCEDURE transfer;

8.3 ODBC编程
ODBC架构
驱动管理器：加载数据库驱动，管理连接。
驱动程序：实现具体数据库的API调用。
数据源（DSN）：配置数据库连接信息（如MySQL、Oracle）。
编程步骤
分配环境句柄：SQLAllocHandle(SQL_HANDLE_ENV, …)。
连接数据库：SQLConnect()或SQLDriverConnect()。
执行SQL：SQLExecDirect()或预编译语句。
处理结果集：通过绑定变量获取数据。
断开连接：SQLDisconnect()。
示例代码（伪代码）
SQLHENV env;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLConnect(env, "DSN=MyDB;UID=user;PWD=pass");
SQLExecDirect("SELECT * FROM Employees", …);
SQLFetch(…); // 获取数据
SQLDisconnect(env);

8.4 小结
核心内容：
嵌入式SQL通过游标与主语言交互，适合复杂事务处理。
存储过程提升代码复用与安全性，支持事务管理。
ODBC提供跨数据库访问的统一接口，依赖驱动实现。
实践意义：
嵌入式SQL与存储过程是传统企业级应用的核心技术。
ODBC/JDBC是现代应用连接数据库的标准方式，需掌握基本编程模式。
 

20250328

主键约束 不可为空 唯一

唯一性约束 可为空 唯一

ADD 数据列名 数据类型 #添加新的列

DROP COLUMN 列名 #删除列

ALTER COLUMN 列名 新的数据类型 #改该列的数据类型，只能加长不能缩短

 

以下是关于 SQL 中 UPDATE、DELETE 和 TRUNCATE 语句的使用方法及注意事项的详细说明：

1. UPDATE 语句

用途：修改表中已有的数据。

基本语法：

UPDATE 表名
SET 列1 = 值1, 列2 = 值2, …
WHERE 条件;

示例：

— 将用户ID为1的用户的年龄更新为25
UPDATE users
SET age = 25
WHERE user_id = 1;

— 同时更新多个字段
UPDATE employees
SET salary = 50000, department = 'HR'
WHERE employee_id = 101;

注意事项：

• 务必使用 WHERE 子句：如果省略 WHERE，表中所有行的指定列都会被更新！
• 事务安全：在生产环境中，建议在更新前开启事务（如 BEGIN TRANSACTION），以便在出错时回滚。
• 备份数据：对关键数据操作前，建议先备份（如 CREATE TABLE users_backup AS SELECT * FROM users）。

2. DELETE 语句

用途：删除表中的数据行。

基本语法：

DELETE FROM 表名
WHERE 条件;

示例：

— 删除用户ID为1的记录
DELETE FROM users
WHERE user_id = 1;

— 删除所有年龄大于60的记录
DELETE FROM employees
WHERE age > 60;

注意事项：

• WHERE 子句同样关键：省略 WHERE 会删除表中所有数据！
• 性能问题：删除大量数据时，DELETE 会逐行操作并记录日志，可能较慢。
• 触发器：DELETE 会触发 DELETE 类型的触发器（如果有）。
• 事务与备份：同 UPDATE，建议操作前备份并使用事务。

3. TRUNCATE 语句

用途：快速删除表中所有数据（不可逆操作）。

基本语法：

TRUNCATE TABLE 表名;

示例：

TRUNCATE TABLE logs;  — 清空 logs 表的所有数据

注意事项：

• 不可逆：TRUNCATE 会直接删除所有数据，且无法通过事务回滚（除非在支持事务的数据库中显式使用 ROLLBACK）。
• 效率高：不逐行删除，而是释放数据页，日志记录少，速度远快于 DELETE。
• 重置自增列：会将自增列（如 AUTO_INCREMENT 或 IDENTITY）重置为初始值。
• 权限要求：通常需要更高权限（如 DROP 权限）。
• 不触发触发器：不会触发 DELETE 触发器。

DELETE vs TRUNCATE 对比

特性	DELETE	TRUNCATE
删除范围	可以有条件删除部分数据	只能删除全部数据
日志记录	逐行记录日志，较慢	整体释放数据页，速度快
事务回滚	可回滚（需在事务中）	通常不可回滚
自增列重置	不重置自增列	重置自增列到初始值
触发器	触发 DELETE 触发器	不触发
语法复杂度	支持复杂条件（如子查询）	仅能清空整个表

使用建议

1. 谨慎操作：在生产环境执行前，先用 SELECT * FROM 表名 WHERE 条件 确认影响范围。
2. 优先使用 TRUNCATE：如果需要清空表且无需条件，优先选择 TRUNCATE 以提高效率。
3. 避免全表操作：对大表执行 DELETE 或 TRUNCATE 时，注意锁表和性能问题。
4. 权限控制：严格限制对 TRUNCATE 的权限，防止误操作。

 

 

DISTINCT查询时去重

SELECT DISTINCT FROM WHERE GROUP BY HAVING ORDER BY

在 SQL 中，DISTINCT 关键字用于在查询结果中去除重复的行。当与其他子句（如 WHERE、GROUP BY、HAVING、ORDER BY）结合时，需注意它们的执行顺序和逻辑关系。以下是具体用法及示例：

1. 基本语法

SELECT DISTINCT column1, column2, …
FROM table_name
WHERE condition
GROUP BY column1, column2, …
HAVING group_condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

2. 各子句与 DISTINCT 的结合使用

(1) SELECT DISTINCT 基础去重

• 作用：返回指定列的唯一组合值。
• 示例：

• — 查询不重复的部门名称
  SELECT DISTINCT department
  FROM employees;– 查询部门和职位的唯一组合
  SELECT DISTINCT department, position
  FROM employees;

(2) WHERE 子句过滤后去重

• 执行顺序：先执行 WHERE 过滤数据，再对结果去重。
• 示例：

• — 查询工资高于 5000 的员工所在城市（去重）
  SELECT DISTINCT city
  FROM employees
  WHERE salary > 5000;

(3) GROUP BY 与 DISTINCT 的异同

• 区别：
  • DISTINCT 直接去重，返回所有列的唯一值。
  • GROUP BY 用于分组聚合（如 COUNT, SUM）。
• 示例：

• — 使用 GROUP BY 统计每个部门的人数（无需 DISTINCT）
  SELECT department, COUNT(*) AS employee_count
  FROM employees
  GROUP BY department;– 若同时需要去重和分组，需明确逻辑（如统计不同职位的数量）
  SELECT department, COUNT(DISTINCT position) AS unique_positions
  FROM employees
  GROUP BY department;

(4) HAVING 过滤分组后的结果

• 作用：对 GROUP BY 分组后的结果进行条件过滤。
• 示例：

• — 查询员工数超过 5 人的部门（需 GROUP BY）
  SELECT department
  FROM employees
  GROUP BY department
  HAVING COUNT(*) > 5;– 结合 DISTINCT 和 HAVING（较少见）
  SELECT department, COUNT(DISTINCT city) AS cities
  FROM employees
  GROUP BY department
  HAVING cities > 2;

(5) ORDER BY 排序去重结果

• 作用：对最终结果按指定列排序。
• 示例：

• — 按注册时间降序排列不重复的用户名
  SELECT DISTINCT username
  FROM users
  ORDER BY registration_date DESC;

3. 注意事项

1. 多列去重：DISTINCT 作用于所有列的组合，而非单列。例如：

• SELECTDISTINCT department, position FROM employees;
  — 结果中 department 和 position 的组合是唯一的。

1. 性能问题：DISTINCT 可能导致全表扫描和排序，大数据量时需谨慎使用。
2. 避免混淆：

• • DISTINCT 不适用于聚合函数内部（但 COUNT(DISTINCT column) 是合法的）。
  • 不能同时使用 DISTINCT 和 GROUP BY 对同一列去重（逻辑冲突）。

1. 替代方案：若仅需去重，优先用 DISTINCT；若需分组统计，使用 GROUP BY。

4. 完整示例

— 查询工资高于 5000 的员工所在城市（去重），并按城市名排序
SELECT DISTINCT city
FROM employees
WHERE salary > 5000
ORDER BY city;

— 统计每个部门的不同职位数量，并筛选职位数超过 2 的部门
SELECT department, COUNT(DISTINCT position) AS unique_positions
FROM employees
GROUP BY department
HAVING unique_positions > 2;

通过合理组合这些子句，可以高效实现复杂的去重查询需求。

SELECT * from STUDENT t where ROWNUM<0; #最近六条记录

SELECT * FROM STUDENT WHERE SNAME LIKE '%张%';
SELECT * FROM STUDENT WHERE SNAME LIKE '%张__'; #下划线代表一个字符 百分号代表任意字符

    select* from STU1 t
    oDBCImporter Command Window
    where t.birthday between to_date(2000-01-01',yyyy-mm-d') and to_date(2003/12/31','yyy/mm/dd')

SELECT count(*) from STUDENT;
ASC为升序，DESC为降序
'='是精确匹配，不可用过量通配符
'LIKE'是模糊匹配
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;/*这是ORACLE的写法*/
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 *
FROM STUDENT
WHERE 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 SNAME
FROM STUDENT
WHERE CLNO IN (
SELECT CLNO
FROM CLASS
WHERE SPECIAL = '软件工程'
AND TRIM(INYEAR) = '2020' — 处理 INYEAR 是 CHAR(8) 的空格填充问题
)
AND SUBSTR(SNO, 1, 7) NOT IN ( — 匹配 GRADE.SNO 的 CHAR(7) 长度
SELECT SNO FROM GRADE
);
/*步入迷惑*/
(10)
SELECT
C.CNO,
C.CNAME,
C.CPNO AS 直接先修课号,
(SELECT CPNO FROM COURSE WHERE CNO = C.CPNO) AS 间接先修课号,
(SELECT CNAME FROM COURSE
WHERE CNO = (SELECT CPNO FROM COURSE WHERE CNO = C.CPNO)) AS 间接先修课名
FROM COURSE C;

SQL语言初步知识点与考点归纳

一、SQL概述

1. SQL简介

• • 定义：结构化查询语言（Structured Query Language），用于管理关系型数据库的标准语言。
  • 历史与标准化：
    • 1970年，E.F.Codd提出关系模型；1974年由Boyce和Chamberlin设计初版（SEQUEL）。
    • 1986年ANSI发布首个标准（SQL-86），后续版本包括SQL-92 正在使用的标准（SQL2）、SQL:1999（支持面向对象）、SQL:2003（支持XML）等。
  • 特点：
    • 一体化：集DDL、DML、DCL于一体。
    • 非过程化：用户只需说明“做什么”，无需指定“怎么做”。
    • 面向集合：操作对象是数据集合而非单条记录。
    • 两种使用方式：自含式语言（独立使用）和嵌入式语言（嵌入其他编程语言）。

1. SQL语言分类

类别	功能	关键语句
DDL	数据定义	CREATE, ALTER, DROP
DML	数据操作	SELECT, INSERT, UPDATE, DELETE
DCL	数据控制	GRANT, REVOKE

二、数据定义语言（DDL）

1. 基本表操作

• • 创建表：

• CREATETABLE表名 (
  列名1 数据类型 [约束],
  列名2 数据类型 [约束],
  …
  [PRIMARY KEY (列名),]
  [FOREIGN KEY (列名) REFERENCES 父表名(列名),]
  [CHECK (条件),]
  );

• • • 示例：

• CREATETABLE Student (
  sno CHAR(8) NOT NULL,
  sname VARCHAR(20) NOT NULL,
  sage NUMERIC(3,0),
  ssex CHAR(2),
  clno CHAR(6),
  PRIMARY KEY (sno)
  );

• • 修改表：
    • 添加列：ALTER TABLE 表名 ADD 列名 数据类型 [约束];
    • 删除列：ALTER TABLE 表名 DROP COLUMN 列名;
    • 修改列类型：ALTER TABLE 表名 ALTER COLUMN 列名 新数据类型;
  • 删除表：DROP TABLE 表名;（删除表结构及数据，不可逆）。

1. 索引操作

• • 创建索引：

• CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX索引名
  ON 表名 (列名 [ASC|DESC], …);

• • • 聚集索引：数据行物理存储顺序与索引顺序一致（每表仅一个）。
    • 非聚集索引：独立于数据存储的B树结构（默认类型）。
  • 删除索引：DROP INDEX 表名.索引名;

三、数据操作语言（DML）

1. 插入数据

• INSERTINTO表名 [(列名1, 列名2, …)]
  VALUES (值1, 值2, …);

• • 批量插入：INSERT INTO 表名 SELECT …（从其他表导入数据）。

1. 更新数据

• UPDATE表名
  SET 列名1 = 表达式1, 列名2 = 表达式2, …
  [WHERE 条件];

• • 示例：UPDATE Employee SET salary = salary * 1.1 WHERE dept = 'IT';

1. 删除数据

• DELETEFROM表名 [WHERE条件];

• • 清空表：TRUNCATE TABLE 表名;（删除所有数据，保留结构，效率高于DELETE）。

四、数据查询（SELECT）

1. 基础语法

• SELECT [DISTINCT] 列名1, 列名2, …
  FROM 表名
  [WHERE 条件]
  [GROUP BY 分组列]
  [HAVING 分组条件]
  [ORDER BY 排序列];

• • 示例：SELECT sno, sname FROM Student WHERE ssex = '男';

1. 子查询

• • 嵌套查询：在WHERE或HAVING子句中使用另一个SELECT语句。
  • 示例：查找年龄大于平均年龄的学生：

• SELECT*FROM Student
  WHERE sage > (SELECT AVG(sage) FROM Student);

五、视图（View）

1. 定义与特点

• • 视图：虚拟表，基于SELECT查询结果生成，不存储数据。
  • 作用：简化复杂查询、增强安全性（限制数据访问）、逻辑数据独立性。

1. 创建与删除

• CREATEVIEW视图名 AS SELECT语句;
  DROP VIEW 视图名;

• • 示例：创建女生视图：

• CREATEVIEW Girl_Student AS
  SELECT * FROM Student WHERE ssex = '女';

六、考点总结

1. 高频考点：

• • SQL语句分类：区分DDL、DML、DCL的语句。
  • 索引类型：聚集索引与非聚集索引的区别。
  • 约束类型：主键、外键、唯一性约束、检查约束的定义。
  • 子查询与连接：IN、EXISTS、JOIN的使用场景。
  • 数据类型选择：CHAR与VARCHAR、NUMERIC与FLOAT的适用场景。

1. 易错点：

• • DROP TABLE与DELETE的区别（结构 vs 数据）。
  • 修改表结构时，新增列必须允许NULL或设置默认值。
  • 索引的维护成本（过多索引影响写性能）。