SQL（Structured Query Language）

一、数据定义语言（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); — 绝对值

六、注意事项

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

七、总结

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

数据库E-R图

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

数据建模：E-R模型提供了一种直观且易于理解的方法来建模现实世界中的数据。通过将实体、属性和关
系抽象成图形化符号，可以更好地捕捉和表示数据之间的关系和结构。
数据可视化：E-R模型允许将数据的组织和关联可视化。通过图形表示，可以清晰地展示实体之间的关
系、属性的特征以及它们之间的连接方式。
数据完整性：E-R模型有助于确保数据的完整性。通过定义实体之间的关系和约束条件，可以确保数据在
插入、更新和删除时保持一致性和正确性。
查询优化：E-R模型可以帮助优化数据库查询。通过了解实体之间的关系，可以设计出更有效的查询和连
接方式，提高查询性能和响应时间。E-R模型还可以指导索引的创建，以支持常见的查询操作。
总的来说，E-R模型提供了一种直观和规范的方法来描述和设计数据库。它有助于提高数据库设计的质量、数据
的完整性和查询的性能，从而提升整个数据库系统的效率和可靠性。
3.E-R图的构成
实体（Entity）：实体表示现实世界中的一个独立对象，可以是人、物、地点、概念等。在E-R图中，实
体用矩形框表示，框内写上实体的名称。
属性（Attribute）：属性是描述实体特征的信息。每个实体可以有多个属性，例如一个人实体可以有姓
名、年龄、性别等属性。属性以椭圆形状表示，并与相应的实体相连。其中能够唯一标识实体的属性称为
主键。
关系（Relationship）：关系表示实体之间的相互作用或联系。关系可以是一对一、一对多或多对多的。
在E-R图中，关系用菱形表示，并与相关的实体相连。关系还可以具有属性，用于描述与关系相关的信
息。
主键（Primary Key）：用于唯一标识实体的属性，通常在实体框内用下划线或加粗表示。主键属性的值
在整个实体集合中必须是唯一的，用于区分不同的实体。
在 E-R 图中，根据实体之间的连接方式和关系类型，关联关系可以分为以下几种类型：
一对一（One-to-One）关联：一个实体实例与另一个实体实例之间存在唯一的关联关系。这种关系表示为一个实
体的一个实例与另一个实体的一个实例相连接。
一对多（One-to-Many）关联：一个实体实例与另一个实体实例之间存在一对多的关联关系。这种关系表示为一个
实体的一个实例与另一个实体的多个实例相连接。
多对多（Many-to-Many）关联：多个实体实例与另一个实体实例之间存在多对多的关联关系。这种关系表示为一
个实体的多个实例与另一个实体的多个实例相连接。
我们来用图片举个例子：
1.一对一
2.一对多
3.多对多
在E-R图中，关系可以具有与之相关的属性，用于描述与该关系相关的信息。这些属性可以提供更详细的关系描
述，以补充关系本身无法完全表达的信息。比如职工与部门的工作关系，这个关系可以具有属性，如工作时
间、工作性质。通过关系的属性以便于更好的描述职工在部门中的工作情况。
这三个部分是构成E-R图最关键的部分，我们通过一个例子来加深印象：
让我们来设计一个简单的 E-R 图来表示学生和课程之间的关系。假设我们有两个实体：学生（Student）和课程
（Course）。一个学生可以注册多门课程，而一门课程可以有多名学生。
设计过程如下：
确定实体（Entities）：
学生（Student）：属性包括学生ID（Student ID）、姓名（Name）、年级（Grade）等。
课程（Course）：属性包括课程ID（Course ID）、课程名称（Course Name）、学分（Credit）等。
确定关系（Relationship）：
注册（Enrollment）：学生和课程之间的关系。这是一个多对多的关系，因为一个学生可以注册多门课程，而一
门课程可以有多名学生。此关系可以具有属性，如选课日期（Enrollment Date）。
确定主键（Primary Key）：
学生实体的主键为学生ID（Student ID）。
课程实体的主键为课程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：数据，是数据库中存储的基本对象，是描述事物的符号记录。
Database：数据库，是长期储存在计算机内、有组织的、可共享的大量数据的集合。
DBMS：数据库管理系统，是位于用户与操作系统之间的一层数据管理软件，用于科学地组织、存储和管理数据、高效地获取和维护数
据。
DBS：数据库系统，指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统、应用系统、数据库管理员（DBA）构成
。
数据模型：是用来抽象、表示和处理现实世界中的数据和信息的工具，是对现实世界的模拟，是数据库系统的核心和基础；其组成元素
有数据结构、数据操作和完整性约束。
概念模型：也称信息模型，是按用户的观点来对数据和信息建模，主要用于数据库设计。
逻辑模型：是按计算机系统的观点对数据建模，用于DBMS实现。
物理模型：是对数据最底层的抽象，描述数据在系统内部的表示方式和存取方法，在磁盘或磁带上的存储方式和存取方法，是面向计算
机系统的。
实体和属性：客观存在并可相互区别的事物称为实体。实体所具有的某一特性称为属性。
10.E-R图：即实体-关系图，用于描述现实世界的事物及其相互关系，是数据库概念模型设计的主要工具。
11.关系模式：从用户观点看，关系模式是由一组关系组成，每个关系的数据结构是一张规范化的二维表。
12.型/值：型是对某一类数据的结构和属性的说明；值是型的一个具体赋值，是型的实例。
13.数据库模式：是对数据库中全体数据的逻辑结构（数据项的名字、类型、取值范围等）和特征（数据之间的联系以及数据有关的安全性
、完整性要求）的描述。
14.数据库的三级系统结构：外模式、模式和内模式。
15.数据库内模式：又称为存储模式，是对数据库物理结构和存储方式的描述，是数据在数据库内部的表示方式。一个数据库只有一个内模
式。
16.数据库外模式：又称为子模式或用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据
视图。通常是模式的子集。一个数据库可有多个外模式。
17.数据库的二级映像：外模式/模式映像、模式/内模式映像。
二、重点知识点
数据库系统由数据库、数据库管理系统、应用系统和数据库管理员构成。
数据模型的组成要素是：数据结构、数据操作、完整性约束条件。
实体型之间的联系分为一对一、一对多和多对多三种类型。
常见的数据模型包括：关系、层次、网状、面向对象、对象关系映射等几种。
关系模型的完整性约束包括：实体完整性、参照完整性和用户定义完整性。
阐述数据库三级模式、二级映象的含义及作用。
数据库三级模式反映的是数据的三个抽象层次：模式是对数据库中全体数据的逻辑结构和特征的描述。内模式又称为存储模式，是对数据
库物理结构和存储方式的描述。外模式又称为子模式或用户模式，是对特定数据库用户相关的局部数据的逻辑结构和特征的描述。
数据库三级模式通过二级映象在 DBMS 内部实现这三个抽象层次的联系和转换。外模式面向应用程序，通过外模式/模式映象与逻辑模式
建立联系，实现数据的逻辑独立性。模式/内模式映象建立模式与内模式之间的一对一映射，实现数据的物理独立性。
第二节
一、相关概念
主键：能够唯一地标识一个元组的属性或属性组称为关系的键或候选键。若一个关系有多个候选键则可选其一作为主键(Primary key)。
外键：如果一个关系的一个或一组属性引用(参照)了另一个关系的主键，则称这个或这组属性为外码或外键(Foreign key)。
关系数据库：依照关系模型建立的数据库称为关系数据库。它是在某个应用领域的所有关系的集合。
关系模式：简单地说，关系模式就是对关系的型的定义，包括关系的属性构成、各属性的数据类型、属性间的依赖、元组语义及完整
性约束等。关系是关系模式在某一时刻的状态或内容，关系模型是型，关系是值，关系模型是静态的、稳定的，而关系是动态的、随时间不
断变化的，因为关系操作在不断地更新着数据库中的数据。
. 实体完整性：用于标识实体的唯一性。它要求基本关系必须要有一个能够标识元组唯一性的主键，主键不能为空，也不可取重复值。
参照完整性：用于维护实体之间的引用关系。它要求一个关系的外键要么为空，要么取与被参照关系对应的主键值，即外键值必须
是主键中已存在的值。
用户定义的完整性：就是针对某一具体应用的数据必须满足的语义约束。包括非空、唯一和布尔条件约束三种情况。
二、重要知识点
关系数据库语言分为关系代数、关系演算和结构化查询语言三大类。
关系的 5 种基本操作是选择、投影、并、差、笛卡尔积。
3.关系模式是对关系的描述，五元组形式化表示为：R（U，D，DOM，F），其中
R —— 关系名
U —— 组成该关系的属性名集合
D —— 属性组 U 中属性所来自的域
DOM —— 属性向域的映象集合
F —— 属性间的数据依赖关系集合
4.笛卡尔乘积，选择和投影运算如下
第三节
一、相关概念
SQL：结构化查询语言的简称，是关系数据库的标准语言。SQL 是一种通用的、功能极强的关系数据库语言，是对关系数据存取的标
准接口，也是不同数据库系统之间互操作的基础。集数据查询、数据操作、数据定义、和数据控制功能于一体。
数据定义：数据定义功能包括模式定义、表定义、视图和索引的定义。
嵌套查询：指将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询。
二、重要知识点
SQL 数据定义语句的操作对象有：模式、表、视图和索引。
SQL 数据定义语句的命令动词是：CREATE、DROP 和 ALTER。
RDBMS 中索引一般采用 B+树或 HASH 来实现。
索引可以分为唯一索引、非唯一索引和聚簇索引三种类型。
6.SQL 创建表语句的一般格式为
CREATE TABLE <表名>
( <列名> <数据类型>[ <列级完整性约束> ]
[，<列名> <数据类型>[ <列级完整性约束>] ] …
[，<表级完整性约束> ] ) ；
其中<数据类型>可以是数据库系统支持的各种数据类型，包括长度和精度。
列级完整性约束为针对单个列(本列)的完整性约束，包括 PRIMARY KEY、 REFERENCES表名(列名)、UNIQUE、NOT NULL 等。
表级完整性约束可以是基于表中多列的约束，包括 PRIMARY KEY ( 列名列表) 、FOREIGN KEY REFERENCES 表名(列名) 等。
SQL 创建索引语句的一般格式为
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> (<列名列表> ) ；
其中UNIQUE：表示创建唯一索引，缺省为非唯一索引；
CLUSTER：表示创建聚簇索引，缺省为非聚簇索引；
<列名列表>：一个或逗号分隔的多个列名，每个列名后可跟 ASC 或 DESC，表示升/降序，缺省为升序。多列时则按为多级排序。
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.计算机系统存在技术安全、管理安全和政策法律三类安全性问题。
TCSEC/TDI 标准由安全策略、责任、保证和文档四个方面内容构成。
常用存取控制方法包括自主存取控制(DAC)和强制存取控制(MAC)两种。
自主存取控制(DAC)的 SQL 语句包括 GRANT 和 REVOKE 两个。用户权限由数据对象和操作类型两部分构成。
常见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.数据依赖：反映一个关系内部属性与属性之间的约束关系，是现实世界属性间相互联系的抽象，属于数据内在的性质和语义的体现。
规范化理论：是用来设计良好的关系模式的基本理论。它通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异
常、更新异常和数据冗余问题。
函数依赖：简单地说，对于关系模式的两个属性子集X和Y，若X的任一取值能唯一确定Y的值，则称Y函数依赖于X，记作X→Y。
非平凡函数依赖：对于关系模式的两个属性子集X和Y，如果X→Y，但Y!⊆X，则称X→Y为非平凡函数依赖；如果X→Y，但Y⊆X，则称
X→Y为非平凡函数依赖。
完全函数依赖：对于关系模式的两个属性子集X和Y，如果X→Y，并且对于X的任何一个真子集X'，都没有X'→Y，则称Y对X完全函数依
赖。
范式：指符合某一种级别的关系模式的集合。在设计关系数据库时，根据满足依赖关系要求的不同定义为不同的范式。
规范化：指将一个低一级范式的关系模式，通过模式分解转换为若干个高一级范式的关系模式的集合的过程。
1NF：若关系模式的所有属性都是不可分的基本数据项，则该关系模式属于1NF。
2NF：1NF关系模式如果同时满足每一个非主属性完全函数依赖于码，则该关系模式属于2NF。
3NF：若关系模式的每一个非主属性既不部分依赖于码也不传递依赖于码，则该关系模式属于3NF。
BCNF：若一个关系模式的每一个决定因素都包含码，则该关系模式属于BCNF。
数据库设计：是指对于一个给定的应用环境，构造优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效
地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求。
数据库设计的6个基本步骤：需求分析，概念结构设计，逻辑结构设计，物理结构设计，数据库实施，数据库运行和维护。
概念结构设计：指将需求分析得到的用户需求抽象为信息结构即概念模型的过程。也就是通过对用户需求进行综合、归纳与抽象，形成
一个独立于具体DBMS的概念模型。
逻辑结构设计：将概念结构模型（基本E-R图）转换为某个DBMS产品所支持的数据模型相符合的逻辑结构，并对其进行优化。
物理结构设计：指为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程。包括设计数据库的存储结构与存取方法。
抽象：指对实际的人、物、事和概念进行人为处理，抽取所关心的共同特性，忽略非本质的细节，并把这些特性用各种概念精确地加以
描述，这些概念组成了某种模型。
数据库设计必须遵循结构设计和行为设计相结合的原则。
数据字典主要包括数据项、数据结构、数据流、数据存储和处理过程五个部分。
三种常用抽象方法是分类、聚集和概括。
局部 E-R 图之间的冲突主要表现在属性冲突、命名冲突和结构冲突三个方面。
数据库常用的存取方法包括索引方法、聚簇方法和 HASH方法三种。
确定数据存放位置和存储结构需要考虑的因素主要有：存取时间、存储空间利用率和维护代价等。
二、细说数据库三范式
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#;

2025-05-16-数据库-知识点整理

题目答案:

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 防误操作

重点理解：

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

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

第一范式（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即可！