Database Key Concepts | 数据库考点精讲

📚 Database Key Concepts | 数据库考点精讲

A database is a fundamental tool in modern computing, used to store, manage and query large amounts of structured data. In the IGCSE CIE Computer Science syllabus, you are expected to understand the principles of relational databases, the role of tables, fields, records, primary and foreign keys, data validation, SQL, and the functions of a Database Management System (DBMS). This article covers the essential concepts, provides practical SQL examples, and explains key terminology you will meet in the exam.

数据库是现代计算中的基本工具，用于存储、管理和查询大量结构化数据。在 IGCSE CIE 计算机科学考试大纲中，你需要理解关系数据库的原理、表、字段、记录、主键和外键的作用、数据验证、SQL 以及数据库管理系统 (DBMS) 的功能。本文涵盖核心概念，提供实用的 SQL 示例，并解释你在考试中会遇到的关键术语。

1. Database and Table Basics | 数据库与表的基础

A database is an organised collection of related data. In a relational database, data is stored in tables. A table is a two‑dimensional structure made up of rows and columns, where each column represents a category of information (a field) and each row represents one complete entry (a record). This structured format allows efficient storage, retrieval, and manipulation of data.

数据库是一个有组织的相关数据集合。在关系型数据库中，数据存储在表中。表是一个由行和列组成的二维结构，其中每一列代表一个信息类别（字段），每一行代表一条完整的条目（记录）。这种结构化格式允许高效地存储、检索和操作数据。

For example, a school database might contain a table named Students with columns: StudentID, Name, DateOfBirth, and Grade. Each row stores the details of a single student.

例如，学校数据库可能包含一个名为 Students 的表，列包括：StudentID、Name、DateOfBirth 和 Grade。每一行存储一名学生的详细信息。

2. Fields, Records and Primary Key | 字段、记录与主键

A field is a single piece of information stored in a table, defined by a column. A record is a complete set of fields about one entity, stored in a row. The primary key is a field (or combination of fields) that uniquely identifies each record in a table. Every table must have a primary key, and its value must be unique and not null.

字段是存储在表中的单一信息，由列定义。记录是关于一个实体的完整字段集合，存储在行中。主键是唯一标识表中每条记录的字段（或字段组合）。每个表都必须有一个主键，其值必须唯一且不为空。

In the Students table, StudentID is an ideal primary key because no two students share the same ID. Using an auto‑increment integer for the primary key is a common practice.

在 Students 表中，StudentID 是一个理想的主键，因为没有两名学生共用同一个 ID。为自增整数使用主键是常见做法。

3. Foreign Keys and Relationships | 外键与关系

A foreign key is a field in one table that refers to the primary key of another table. It establishes a link between the two tables, allowing data to be connected logically. This is the foundation of relational databases and helps to reduce data redundancy.

外键是一个表中的字段，它引用另一个表的主键。它在两个表之间建立联系，使数据可以逻辑连接。这是关系数据库的基础，有助于减少数据冗余。

For example, an Orders table may have a CustomerID foreign key that references the Customers table’s primary key. One customer can place many orders (a one‑to‑many relationship). Understanding relationships helps you design normalized databases.

例如，Orders 表可能有一个 CustomerID 外键，它引用 Customers 表的主键。一名客户可以下很多订单（一对多关系）。理解关系有助于设计规范化的数据库。

4. Data Types | 数据类型

Each field in a table has a data type that defines the kind of data it can store. Choosing the correct data type ensures data integrity, saves storage space, and allows correct operations (e.g., numeric calculations or date comparisons).

表中的每个字段都有一个数据类型，定义了它可以存储的数据种类。选择正确的数据类型可以确保数据完整性、节省存储空间，并允许正确的操作（例如数值计算或日期比较）。

Data Type	中文	Example
VARCHAR / TEXT	变长字符串 / 文本	‘Alice’, ‘Laptop’
INTEGER	整数	101, -3
FLOAT / REAL	浮点数 / 实数	3.14, -0.007
DATE	日期	‘2025-04-01’
BOOLEAN	布尔值	TRUE / FALSE

When designing a table, you assign a suitable data type to each field. For instance, a price field should be FLOAT, and a name field should be VARCHAR.

设计表时，你需要为每个字段分配合适的数据类型。例如，价格字段应为 FLOAT，姓名字段应为 VARCHAR。

5. SQL SELECT and WHERE | SQL 基础：SELECT 与 WHERE

Structured Query Language (SQL) is the standard language for interacting with relational databases. The most fundamental command is SELECT, used to retrieve data from one or more tables.

结构化查询语言 (SQL) 是与关系数据库交互的标准语言。最基本的命令是 SELECT，用于从一个或多个表中检索数据。

SELECT name, grade FROM Students;

The WHERE clause filters records based on a specified condition. Operators such as =, <>, >, <, >=, <=, AND, OR, LIKE, and BETWEEN can be used.

WHERE 子句根据指定条件筛选记录。可以使用 =、<>、>、<、>=、<=、AND、OR、LIKE 和 BETWEEN 等运算符。

SELECT * FROM Students WHERE grade >= 80 AND city = ‘Beijing’;

The LIKE operator with wildcards % (any string) and _ (single character) enables pattern matching.

LIKE 操作符配合通配符 %（任意字符串）和 _（单个字符）可以进行模式匹配。

SELECT name FROM Students WHERE name LIKE ‘A%’;

This returns all names starting with the letter A.

这将返回所有以字母 A 开头的姓名。

6. SQL ORDER BY, GROUP BY and Aggregate Functions | SQL 进阶：ORDER BY、GROUP BY 与聚合函数

ORDER BY sorts the result set in ascending (ASC) or descending (DESC) order. The default is ascending. You can sort on multiple columns.

ORDER BY 按升序 (ASC) 或降序 (DESC) 对结果集排序。默认为升序。你可以按多个列排序。

SELECT name, grade FROM Students ORDER BY grade DESC;

GROUP BY groups rows that share common values in specified columns, often used with aggregate functions to produce summary rows. Common aggregate functions are: COUNT(), SUM(), AVG(), MAX(), MIN().

GROUP BY 将指定列中具有相同值的行分组，常与聚合函数一起使用以生成汇总行。常见的聚合函数有：COUNT()、SUM()、AVG()、MAX()、MIN()。

SELECT city, COUNT(*) AS total FROM Students GROUP BY city;

To filter groups, use the HAVING clause (similar to WHERE but for aggregated data). For example, to find cities with more than 10 students:

要筛选分组，使用 HAVING 子句（类似于 WHERE，但用于聚合数据）。例如，查找学生人数超过 10 人的城市：

SELECT city, COUNT(*) FROM Students GROUP BY city HAVING COUNT(*) > 10;

7. Data Validation | 数据验证

Data validation is the process of checking that the data entered meets certain rules or constraints before it is stored in the database. Validation helps maintain accuracy and reliability of the data and can be implemented at the database level and in the user interface.

数据验证是在数据存储到数据库之前检查其是否符合某些规则或约束的过程。验证有助于保持数据的准确性和可靠性，可以在数据库层面和用户界面中实现。

Common validation checks include: presence check (ensuring a required field is not left blank), range check (e.g., age between 0 and 120), format check (e.g., email must contain ‘@’), length check (e.g., password must be at least 8 characters), and check digit (an extra digit calculated from other digits, used in barcodes).

常见的验证检查包括：存在性检查（确保必填字段不为空）、范围检查（如年龄在 0 到 120 之间）、格式检查（如电子邮件必须包含 ‘@’）、长度检查（如密码至少 8 个字符）和校验位（由其他数字计算出的额外数字，用于条形码中）。

Verification is a separate process where a user confirms the data entered is correct, often by typing it twice. Both validation and verification are essential for data integrity.

验证是一个独立的过程，用户确认输入的数据正确，通常通过重复输入两次来进行。验证和核实对数据完整性都至关重要。

8. Functions of a DBMS | 数据库管理系统的功能

A Database Management System (DBMS) is software that allows users to create, maintain and control access to databases. Its key functions include:

数据库管理系统 (DBMS) 是允许用户创建、维护和控制数据库访问的软件。其主要功能包括：

Data storage, retrieval and update: The DBMS handles all read and write operations on the data files. Data security: It provides authentication and authorisation mechanisms to control who can access or modify data. Data backup and recovery: Regular backups and transaction logs enable restoration of data after hardware failure or accidental deletion. Concurrency control: It manages multiple users accessing data simultaneously, preventing conflicts. Data dictionary management: The DBMS stores metadata (data about data) such as table structures, constraints, and user privileges.

数据存储、检索和更新：DBMS 处理数据文件的所有读写操作。数据安全：它提供身份验证和授权机制，控制谁可以访问或修改数据。数据备份和恢复：定期备份和事务日志允许在硬件故障或意外删除后恢复数据。并发控制：它管理多个用户同时访问数据，防止冲突。数据字典管理：DBMS 存储元数据（关于数据的数据），如表结构、约束和用户权限。

9. Data Security and Backup | 数据安全与备份

Protecting data from unauthorised access, corruption, and loss is critical. Security measures include strong user passwords, encryption of sensitive data, and access rights (e.g., read‑only). A DBMS can enforce these through user accounts and privileges.

保护数据免受未经授权的访问、损坏和丢失至关重要。安全措施包括强密码、敏感数据加密和访问权限（如只读）。DBMS 可以通过用户账户和权限来强制执行这些措施。

Backup strategies are equally important. A full backup copies all data, while incremental backups only save changes since the last backup. Backups should be stored off‑site and tested regularly. A recovery plan ensures minimal downtime and data loss in a disaster.

备份策略同样重要。完全备份复制所有数据，而增量备份仅保存自上次备份以来的更改。备份应异地存储并定期测试。恢复计划可确保在灾难中最小化停机时间和数据丢失。

10. Transaction Processing | 事务处理

A transaction is a sequence of database operations that are treated as a single logical unit. The ACID properties define the reliability of transactions:

事务是被视为单一逻辑单元的一系列数据库操作。ACID 属性定义了事务的可靠性：

Atomicity – All operations succeed or none, ensuring no partial updates. Consistency – A transaction brings the database from one valid state to another, obeying all rules. Isolation – Concurrent transactions do not interfere with each other. Durability – Once committed, changes are permanent, even after a system failure.

原子性 – 所有操作要么全部成功，要么全部失败，确保没有部分更新。一致性 – 事务使数据库从一种有效状态变为另一种有效状态，遵守所有规则。隔离性 – 并发事务不会相互干扰。持久性 – 一旦提交，更改是永久的，即使系统故障后也不会丢失。

Commands like COMMIT and ROLLBACK are used to finalise or undo a transaction. These concepts are especially important in banking or booking systems.

使用 COMMIT 和 ROLLBACK 命令来最终确定或撤消事务。这些概念在银行或预订系统中尤为重要。

11. Normalisation | 规范化

Normalisation is the process of organising data to reduce redundancy and improve integrity. The first normal form (1NF) requires that a table has no repeating groups and each field contains atomic (indivisible) values.

规范化是组织数据以减少冗余和提高完整性的过程。第一范式 (1NF) 要求表没有重复组，并且每个字段包含原子（不可再分）值。

For example, a table storing multiple phone numbers in a single field violates 1NF. The solution is to split it into a separate PhoneNumbers table linked by a foreign key. This eliminates duplication and makes updates easier.

例如，在一个字段中存储多个电话号码的表违反了 1NF。解决方案是将其拆分为通过外键关联的单独的 PhoneNumbers 表。这样消除了重复并使更新更容易。

Higher normal forms (2NF, 3NF) deal with removing partial and transitive dependencies, but for IGCSE, a solid grasp of primary/foreign keys and the idea of reducing duplicate data is sufficient.

更高的范式 (2NF, 3NF) 涉及消除部分依赖和传递依赖，但对于 IGCSE，牢固掌握主键/外键以及减少重复数据的思想就足够了。

Published by TutorHao | Computer Science Revision Series | aleveler.com

更多咨询请联系16621398022（同微信）

Database Key Concepts | 数据库 考点精讲