数据库

在数据库之前,程序把数据存储在平面文件(flat files)中——通常每个程序一个文件。这对小数据没问题,但在规模上会崩溃。

局限

• 数据冗余(data redundancy)——相同的数据(一个客户的地址)保存在几个文件中。
• 数据不一致(data inconsistency)——分别更新的冗余副本变得不同步。
• 数据依赖(data dependence)——程序绑定到文件格式;改变格式,每个程序都必须重写。
• 难以强制完整性(integrity)、难以安全共享、查询能力弱,以及每字段的安全性弱。

一个关系数据库(relational database)通过把数据存储在由一个所有程序都使用的软件(DBMS)管理的表中来修复这些。

• 表(table,关系)——行和列的一个网格;每种实体(entity)一个表(例如 CUSTOMER)。
• 记录(record,行,也叫元组(tuple))——一行;实体的一个实例。
• 字段(field,列,也叫属性(attribute))——一列;关于每条记录的一条信息。
• 主键(primary key)——唯一标识每条记录的一个字段(或多个字段);从不为空或重复。
• 外键(foreign key)——一个值匹配另一个表主键的字段,把两者链接起来。
• 复合键(composite key)——由两个或更多字段一起构成的一个主键。
• 候选键(candidate key)——任何可以是主键的字段。
• 次键(secondary key)——一个为快速搜索而建立索引的非主字段。
• 索引(indexing)——在一个字段上建立一个索引,使查找和连接运行得更快。
• 参照完整性(referential integrity)——每个外键值必须匹配一个现有的主键(没有孤立记录)。

一个表用简写书写,主键加下划线、外键标注:

CUSTOMER(CustomerID, Name, Phone)
ORDER(OrderID, CustomerID, OrderDate)   -- CustomerID is FK → CUSTOMER

一个实体关系图(entity-relationship diagram)显示结构:每个实体是一个矩形,每个关系是一条线,在每端标注基数(cardinality):

• 一对一(1:1)。
• 一对多(one-to-many,1:M)——每个客户有许多订单;每个订单有一个客户。
• 多对多(many-to-many,M:N)——学生选许多课程,而课程有许多学生。

一个多对多关系不能直接存储。把它拆成两个一对多关系,通过一个容纳两个外键的连接表(link table):

ENROLMENT(StudentID, CourseID, EnrolmentDate)

规范化(normalisation)组织表以减少冗余和不一致,按顺序经过各范式(normal forms)。

• 第一范式(1NF)——每个字段容纳一个单一(原子(atomic))值,没有重复组,并有一个主键。
• 第二范式(2NF)——处于 1NF,并且每个非键字段依赖于整个主键(只对一个复合键有意义)。
• 第三范式(3NF)——处于 2NF,并且每个非键字段只依赖于主键,而不依赖于另一个非键字段(没有传递依赖(transitive dependency))。

一个 3NF 设计把每个事实存储一次,所以插入/更新/删除异常消失。权衡是更多的表和更多的连接。以 3NF 为目标。

要产生一个 3NF 设计:找出实体及其属性;为每个选择一个主键;拆分重复/非原子字段(1NF);拆分依赖于一个复合键一部分的字段(2NF);拆分传递地依赖于键的字段(3NF);为关系添加外键。

例题。 表 ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity) 的复合主键是 (OrderID, ProductID)。把它规范化到 3NF。逐个用主键去检验每个非键字段。Quantity 同时依赖 OrderID 和 ProductID,这没问题。但 CustomerID 只依赖 OrderID - 也就是复合主键的一部分。这是部分依赖,所以该表不满足 2NF。把它拆成 ORDER_LINE(OrderID, ProductID, Quantity) 和 ORDER(OrderID, CustomerID, CustomerName)。再检验 3NF:在新的 ORDER 表中,CustomerName 依赖于 CustomerID,而后者不是主键 - 这是传递依赖。再拆一次:ORDER(OrderID, CustomerID) 和 CUSTOMER(CustomerID, CustomerName)。要点出破坏每种范式的那种依赖(部分依赖破坏 2NF,传递依赖破坏 3NF);写"它有重复数据"只是在描述症状,得不到分。

一个 DBMS(数据库管理系统)集中管理数据库。修复基于文件的局限的特性:

• 数据字典(data dictionary)——对每个表、字段、类型和键的描述;程序查询它而不是硬编码结构。
• 冗余/一致性控制——每个事实存储一次。
• 并发访问(concurrent access)控制——锁和事务让许多用户一次工作。
• 备份(backup)和恢复;安全和每用户权限。
• 完整性规则——键、唯一和范围约束,集中强制执行。
• 事务(transactions)——一组全部成功或全部失败的操作。
• 视图(views)——向每个用户显示"他们的"那一片数据的虚拟表。
• 数据管理(data management)和数据建模(data modelling)——控制数据如何存储,并把它的结构定义为一个逻辑模式(logical schema,逻辑设计,独立于物理存储)。
• 数据完整性(data integrity)和数据安全(data security)——集中强制正确性并控制访问。
• 一个查询处理器(query processor)运行查询;一个开发者接口(developer interface)给出构建应用程序的工具和 API。

它的工具包括一个数据字典编辑器、一个查询构建器、一个表单构建器、一个报表生成器、用户管理,以及一个 SQL 编辑器。

SQL(结构化查询语言,Structured Query Language)有两半:

• 数据定义语言(Data Definition Language,DDL)——创建或改变结构(表、键、约束)。
• 数据操纵语言(Data Manipulation Language,DML)——处理数据(插入、更新、删除、查询(query))。

DDL 基础

CREATE TABLE CUSTOMER (
  CustomerID INTEGER PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Phone VARCHAR(20)
);

添加一个外键:

CREATE TABLE ORDER (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER,
  OrderDate DATE,
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);

修改和删除:

ALTER TABLE CUSTOMER ADD Email VARCHAR(100);
DROP TABLE CUSTOMER;

常见类型:INTEGER、REAL、VARCHAR(n)、CHAR(n)(也叫 CHARACTER(n))、DATE、TIME、BOOLEAN、DECIMAL(p, s)。

DML 基础

用 SELECT 查询:

SELECT Name, Phone
FROM CUSTOMER
WHERE City = 'London'
ORDER BY Name ASC;

SELECT 列出字段,FROM 命名表,WHERE 过滤行,ORDER BY 排序。

一个连接(join)用一个外键关系组合两个表:

SELECT C.Name, O.OrderDate
FROM CUSTOMER C INNER JOIN ORDER O
  ON C.CustomerID = O.CustomerID
WHERE O.OrderDate >= '2024-01-01';

聚合函数(aggregate functions,COUNT、SUM、AVG、MIN、MAX)常与 GROUP BY 一起用:

SELECT CustomerID, COUNT(*) AS NumOrders
FROM ORDER
GROUP BY CustomerID;

插入、更新、删除:

INSERT INTO CUSTOMER (CustomerID, Name, Phone)
VALUES (101, 'Ada Lovelace', '020-1234-5678');

UPDATE CUSTOMER SET Phone = '020-9999-0000' WHERE CustomerID = 101;

DELETE FROM CUSTOMER WHERE CustomerID = 101;

总是在 UPDATE 和 DELETE 上加一个 WHERE 子句,否则改变会命中每一行。

考试 SQL 的提示

• 使用题目中确切的表名和字段名。
• 用单引号引起字符串('Smith');不要引起数字。
• 比较:=、<、>、<=、>=、<>。
• LIKE 'A%' 匹配任何以 A 开头的(% = 任何字符串,_ = 一个字符);IN (1,2,3);BETWEEN 10 AND 20。
• 用 AND / OR / NOT 组合条件,并以一个分号结束每条语句。

• 精确地定义术语:实体、属性、主键、外键,以及关系类型(1:1、1:多、多:多)。
• 在每个范式给出一个理由:1NF(没有重复组)、2NF(没有部分依赖)、3NF(没有非键依赖)。
• 解释一个 DBMS 提供什么(数据独立性、安全、完整性、并发访问)。
• 区分 DDL(定义结构)和 DML(查询和改变数据)。