📚 GCSE WJEC Computer Science: Database Essentials Revision | GCSE WJEC 计算机：数据库考点精讲
Databases form the backbone of almost every modern software application, from social media platforms to online banking. For the WJEC GCSE Computer Science specification, a solid understanding of database concepts and practical SQL skills is essential. This article covers everything you need to know, from the fundamentals of tables and keys to writing queries that retrieve and modify data, ensuring you are well-prepared for your examinations.
数据库几乎是所有现代软件应用的支柱，从社交媒体平台到在线银行。对于 WJEC GCSE 计算机科学考试大纲来说，扎实理解数据库概念和实用的 SQL 技能至关重要。本文涵盖了你需要了解的所有内容，从表和键的基础知识到编写用于检索和修改数据的查询，确保你为考试做好充分准备。
1. What is a Database? | 什么是数据库？
A database is an organised collection of structured data, usually stored electronically in a computer system. Unlike a simple spreadsheet, a database allows multiple users and applications to access and manipulate the data simultaneously while maintaining consistency and security.
数据库是有组织的结构化数据集合，通常以电子方式存储在计算机系统中。与简单的电子表格不同，数据库允许多个用户和应用程序同时访问和操作数据，同时保持数据的一致性和安全性。
The primary purpose of a database is to store, retrieve and manage information efficiently. It eliminates many of the problems associated with traditional file-based systems, such as data duplication, inconsistency and difficulty in searching for specific records.
数据库的主要目的是高效地存储、检索和管理信息。它消除了传统基于文件的系统所面临的许多问题，例如数据重复、不一致以及难以搜索特定记录。
In the context of the WJEC exam, you need to be able to explain why a database is preferable to a flat file system and how it supports the creation of information systems that are reliable, scalable and easy to maintain.
在 WJEC 考试中，你需要能够解释为什么数据库优于平面文件系统，以及它如何支持创建可靠、可扩展且易于维护的信息系统。
2. Database Management Systems (DBMS) | 数据库管理系统
A Database Management System (DBMS) is software that acts as an interface between the database and the end-user or application. It handles the creation, querying, updating and administration of the database. Common examples include MySQL, Microsoft Access, SQLite and Oracle.
数据库管理系统（DBMS）是充当数据库与最终用户或应用程序之间接口的软件。它处理数据库的创建、查询、更新和管理。常见的例子包括 MySQL、Microsoft Access、SQLite 和 Oracle。
The DBMS enforces security rules, manages concurrent access when multiple users try to modify the same data, and ensures that data integrity constraints are not violated. For instance, if a field is defined to hold only numbers, the DBMS will reject any attempt to insert text.
DBMS 会执行安全规则，在多个用户试图修改相同数据时管理并发访问，并确保不会违反数据完整性约束。例如，如果一个字段被定义为只能容纳数字，则 DBMS 将拒绝任何插入文本的尝试。
WJEC questions often ask you to describe the functions of a DBMS. Remember to mention data storage, data retrieval, security, backup and recovery, and maintaining data integrity through validation and verification.
WJEC 考题经常要求你描述 DBMS 的功能。记得提及数据存储、数据检索、安全性、备份与恢复，以及通过验证和确认维护数据完整性。
3. Tables, Fields, Records and Data Types | 表、字段、记录和数据类型
Data in a relational database is organised into tables. A table is made up of rows and columns: each row represents a single record (or entity), and each column represents a field (or attribute). For example, a ‘Students’ table might have fields such as StudentID, FirstName, LastName, DateOfBirth and YearGroup.
关系型数据库中的数据被组织成表。表由行和列组成：每一行代表一条记录（或实体），每一列代表一个字段（或属性）。例如，一个 ‘Students’ 表可能包含 StudentID、FirstName、LastName、DateOfBirth 和 YearGroup 等字段。
Each field is assigned a specific data type that restricts the kind of information it can store. Common data types include TEXT or VARCHAR for characters, INTEGER for whole numbers, REAL or FLOAT for decimal numbers, DATE for dates, and BOOLEAN for true/false values. Choosing the correct data type is important for efficient storage and validation.
每个字段都被分配了一个特定的数据类型，用以限制它可以存储的信息类型。常见的数据类型包括用于字符的 TEXT 或 VARCHAR、用于整数的 INTEGER、用于小数的 REAL 或 FLOAT、用于日期的 DATE，以及用于真/假值的 BOOLEAN。选择正确的数据类型对于高效存储和验证至关重要。
You should be able to design a simple table structure given a scenario, identifying meaningful field names and suitable data types. In the exam, always check the context to avoid storing unnecessary data or using overly large data types that waste storage space.
你应该能够根据给定场景设计简单的表结构，确定有意义的字段名和合适的数据类型。在考试中，始终结合上下文检查，避免存储不必要的数据或使用过大的数据类型浪费存储空间。
4. Primary Keys and Foreign Keys | 主键和外键
A primary key is a field (or combination of fields) that uniquely identifies each record in a table. It must contain a value for every record, and that value must be unique. For example, StudentID could be the primary key in the Students table because no two students share the same ID.
主键是一个字段（或字段组合），用于唯一标识表中的每条记录。它必须为每条记录包含一个值，且该值必须是唯一的。例如，StudentID 可以作为 Students 表的主键，因为不会有两名学生共享同一个 ID。
A foreign key is a field in one table that refers to the primary key of another table. It creates a link between the two tables. For instance, if an Enrolments table contains a StudentID field that references the Students table, that StudentID is a foreign key. This relationship allows a database to reduce data duplication by storing information only once.
外键是一个表中的字段，它引用了另一个表的主键。它在两张表之间建立联系。例如，如果 Enrolments 表包含一个引用 Students 表的 StudentID 字段，那么该 StudentID 就是外键。这种关系允许数据库通过只存储一次信息来减少数据冗余。
WJEC papers frequently test your ability to identify and justify the choice of a primary key and to explain how foreign keys are used to create relationships. Always remember: a primary key is unique and not null; a foreign key matches a primary key in another table.
WJEC 试卷经常考查你是否能识别并说明主键的选择理由，以及解释如何用外键建立关系。务必记住：主键唯一且非空；外键与另一张表的主键匹配。
5. Relationships Between Tables | 表之间的关系
In a relational database, tables are linked through relationships. The three fundamental types are one-to-one, one-to-many and many-to-many. The most common is the one-to-many relationship, where one record in Table A can be associated with multiple records in Table B. For example, one teacher can teach many classes.
在关系型数据库中，表通过关系相互连接。三种基本类型是一对一、一对多和多对多。最常见的是一对多关系，即表 A 中的一条记录可以与表 B 中的多条记录相关联。例如，一位教师可以教授多个班级。
A many-to-many relationship requires an intermediate table (often called a junction or link table) to avoid data duplication. Imagine a Students table and a Clubs table: one student can join many clubs, and one club can have many members. To model this, you would create a StudentClubs table containing foreign keys from both Students and Clubs, together forming a composite key.
多对多关系需要一个中间表（通常称为连接表或链接表）来避免数据冗余。设想一个 Students 表和一个 Clubs 表：一名学生可以参加多个社团，一个社团也可以有多名成员。要对此建模，你需要创建一个 StudentClubs 表，包含来自 Students 和 Clubs 的外键，二者共同构成复合键。
You should be able to draw simple entity-relationship diagrams and explain how relationships help maintain referential integrity – meaning that a foreign key value must match an existing primary key value or be null, and that you cannot delete a record that is still referenced by another table without breaking the database structure.
你应该能够绘制简单的实体关系图，并解释关系如何帮助维持引用完整性——也就是说，外键值必须与现有的主键值相匹配或为空，并且你不能在未破坏数据库结构的情况下删除另一个表仍引用的记录。
6. Introduction to SQL | SQL 简介
SQL (Structured Query Language) is the standard language used to interact with relational databases. The WJEC specification expects you to write and interpret basic SQL statements. While there is no single set syntax for the exam, you need to be familiar with the most widely used commands: SELECT, INSERT, UPDATE and DELETE.
SQL（结构化查询语言）是用于与关系型数据库交互的标准语言。WJEC 大纲要求你会书写和解读基本的 SQL 语句。虽然考试中没有一套固定不变的语法，但你需要熟悉最广泛使用的命令：SELECT、INSERT、UPDATE 和 DELETE。
SQL can be divided into two main categories: DDL (Data Definition Language) for creating and modifying table structures, and DML (Data Manipulation Language) for working with the data itself. At GCSE level, the focus is almost entirely on DML queries that retrieve and change data.
SQL 可以分为两大类：用于创建和修改表结构的 DDL（数据定义语言），以及用于处理数据本身的 DML（数据操作语言）。在 GCSE 级别，重点几乎完全放在检索和更改数据的 DML 查询上。
You will often be given a table or a small set of tables and asked to write a query to solve a specific problem. Always read the column names and data types carefully, and remember that SQL keywords are not case-sensitive, but good practice is to write them in UPPER CASE for clarity.
你通常会面对一张表或一小批表，并被要求编写查询来解决特定问题。请仔细阅读列名和数据类型，并记住 SQL 关键字不区分大小写，但为了清晰起见，良好的做法是用大写形式书写它们。
7. SQL SELECT Queries | SQL SELECT 查询
The SELECT command is used to retrieve data from one or more tables. The simplest form is:
SELECT 命令用于从一张或多张表中检索数据。最简单的形式是：
SELECT * FROM Students;
This returns all columns and all rows from the Students table. The asterisk (*) acts as a wildcard meaning ‘all columns’. While useful for exploring data, it is often inefficient if you only need a few fields.
这将返回 Students 表中的所有列和所有行。星号（*）作为通配符，意思是“所有列”。虽然这在探索数据时很有用，但如果你只需要几个字段，它往往是低效的。
To select specific fields, list them after SELECT, separated by commas:
要选择特定字段，请在 SELECT 之后列出它们并用逗号分隔：
SELECT FirstName, LastName, DateOfBirth FROM Students;
When marking your exam scripts, examiners look for a correct understanding of the SELECT … FROM structure. Even if you miss a semicolon, you can still gain marks as long as the logic is sound. However, always aim for precise syntax.
在批改你的试卷时，考官会寻找对 SELECT … FROM 结构的正确理解。即使你漏掉了分号，只要逻辑合理，你仍然可以得到分数。不过，要始终力求精确的语法。
8. Filtering Data with WHERE | 使用 WHERE 过滤数据
The WHERE clause allows you to filter records so that only those that meet specific conditions are returned. It is the key mechanism for narrowing down results. For example, to find all students in Year 11:
WHERE 子句允许你过滤记录，以便只返回那些满足特定条件的记录。这是缩小结果范围的关键机制。例如，要查找所有 11 年级的学生：
SELECT * FROM Students WHERE YearGroup = 11;
You can combine conditions using logical operators such as AND, OR and NOT. For instance, to retrieve students in Year 11 who have a grade above 80:
你可以使用 AND、OR 和 NOT 等逻辑运算符来组合条件。例如，要检索成绩高于 80 分的 11 年级学生：
SELECT FirstName, LastName FROM Students WHERE YearGroup = 11 AND Grade > 80;
Comparison operators include =, <> (or != for not equal), >, <, >= and <=. The LIKE operator is used for pattern matching with wildcards: % represents any sequence of characters, and _ represents a single character. For example, WHERE LastName LIKE 'S%' finds all last names starting with 'S'.
比较运算符包括 =、<>（或 != 表示不等于）、>、<、>= 和 <=。LIKE 运算符用于通过通配符进行模式匹配：% 表示任意字符序列，_ 表示单个字符。例如，WHERE LastName LIKE 'S%' 会找出所有以 'S' 开头的姓氏。
9. Sorting Data with ORDER BY | 使用 ORDER BY 排序
The ORDER BY clause is used to sort the result set by one or more columns. By default, sorting is done in ascending order (A–Z, smallest first). To sort students alphabetically by last name:
ORDER BY 子句用于按一列或多列对结果集进行排序。默认情况下，排序是按升序进行的（A–Z，最小在前）。要按姓氏字母顺序对学生进行排序：
SELECT * FROM Students ORDER BY LastName;
To reverse the order, add the DESC keyword. The following example lists the oldest students first, assuming DateOfBirth holds the date of birth:
要逆序排列，请添加 DESC 关键字。以下示例假设 DateOfBirth 存放的是出生日期，首先列出年龄最大的学生：
SELECT FirstName, DateOfBirth FROM Students ORDER BY DateOfBirth ASC;
You can sort by multiple columns: ORDER BY YearGroup DESC, LastName ASC would group students by year group from highest to lowest, and within each year group sort them alphabetically by last name. Examiners often want to see that you understand how to combine ORDER BY with WHERE – the correct order of clauses is SELECT, FROM, WHERE, ORDER BY.
你可以按多个列排序：ORDER BY YearGroup DESC, LastName ASC 会先按年级从高到低分组，然后在每个年级组内按姓氏字母顺序排列。考官通常希望看到你理解如何将 ORDER BY 与 WHERE 结合使用——子句的正确顺序是 SELECT、FROM、WHERE、ORDER BY。
10. Inserting, Updating and Deleting Data | 插入、更新和删除数据
To add new records to a table, use the INSERT INTO command. You specify the table name, the columns you are populating, and the values to insert. For example:
要向表中添加新记录，请使用 INSERT INTO 命令。你需要指定表名、要填充的列以及要插入的值。例如：
INSERT INTO Students (StudentID, FirstName, LastName, YearGroup) VALUES (105, ‘Emma’, ‘Jones’, 10);
The UPDATE command modifies existing records. It is almost always used with a WHERE clause to avoid changing all rows unintentionally. To update Emma’s year group:
UPDATE 命令用于修改现有记录。它几乎总是与 WHERE 子句一起使用，以避免无意中更改所有行。要更新 Emma 的年级：
UPDATE Students SET YearGroup = 11 WHERE StudentID = 105;
The DELETE command removes records. Again, a WHERE clause is critical. Without it, all records will be removed. The following removes Emma’s record entirely:
DELETE 命令用于删除记录。同样，WHERE 子句至关重要。没有它，所有记录都将被删除。以下语句会完全删除 Emma 的记录：
DELETE FROM Students WHERE StudentID = 105;
For the exam, you must recognise that INSERT, UPDATE and DELETE are data manipulation operations that permanently change the database content. Make sure you can identify correct and incorrect statements, and that you always consider the risk of accidental data loss.
对于考试，你必须认识到 INSERT、UPDATE 和 DELETE 是会永久改变数据库内容的数据操作语句。确保你能够识别正确和错误的语句，并始终考虑到意外丢失数据的风险。
11. Data Integrity and Validation | 数据完整性与验证
Data integrity refers to the accuracy, consistency and reliability of data stored in a database. DBMSs provide several mechanisms to maintain integrity: field-level validation during data entry, referential integrity enforced through foreign key constraints, and entity integrity guaranteed by primary keys.
数据完整性是指数据库中存储数据的准确性、一致性和可靠性。DBMS 提供了几种维护完整性的机制：数据输入时的字段级验证、通过外键约束实现的引用完整性，以及由主键保证的实体完整性。
Common validation checks include presence check (ensures a value has been entered), length check, range check (e.g. age between 0 and 120), type check, and format check (e.g. a date must follow DD/MM/YYYY). In SQL, these can be enforced using CHECK constraints or by setting appropriate data types.
常见的验证检查包括存在性检查（确保已输入值）、长度检查、范围检查（例如年龄介于 0 到 120 之间）、类型检查以及格式检查（例如日期必须遵循 DD/MM/YYYY）。在 SQL 中，这些可以通过 CHECK 约束或设置适当的数据类型来实施。
Verification is a separate process, usually performed by a human, to ensure that the data entered matches the original source. For example, double-entry of email addresses or visually confirming a phone number. While validation can be automated, verification helps catch errors that slip through automated checks.
验证是一个独立的过程，通常由人工执行，以确保输入的数据与原始来源一致。例如，二次输入电子邮件地址或目视确认电话号码。虽然验证可以自动化，但验证有助于发现自动化检查漏掉的错误。
In the WJEC exam, you might be asked to suggest appropriate validation rules for a given scenario. Always link your suggestion directly to the characteristics of the field – e.g. a UK postcode should have a format check using both letters and numbers in a specific pattern.
在 WJEC 考试中，你可能会被要求为一个给定场景建议合适的验证规则。始终将你的建议直接与字段的特征联系起来——例如，英国邮政编码应该使用特定模式的字母和数字组合进行格式检查。
12. Advantages of Databases over Flat Files | 数据库相对于平面文件系统的优势
Before databases became widespread, data was often stored in flat files – simple text files or spreadsheets with a single table. While flat files are easy to create, they suffer from several weaknesses that databases solve effectively.
在数据库普及之前，数据通常存储在平面文件中——简单的文本文件或只包含单张表格的电子表格。虽然平面文件易于创建，但它们存在几个弱点，而数据库能有效地解决这些问题。
| Feature | Flat File | Relational Database |
|---|---|---|
| Data redundancy | High – same data may appear in many places | Low – data stored once and linked via keys |
| Consistency | Poor – updating one copy may miss others | Strong – changes propagate correctly |
| Concurrent access | Limited – file locking issues | Good – DBMS manages multi-user access |
| Security | Minimal – often just file permissions | Fine-grained – user roles, encryption, views |
| Query complexity | Difficult – requires custom programs | Easy – powerful SQL queries |
With a database, data independence is also achieved: the way data is stored can be changed without affecting the programs that use the data. This is a major reason why almost all modern business systems have migrated to database-driven architectures.
有了数据库，还可以实现数据独立性：数据的存储方式可以改变，而不会影响使用这些数据的程序。这是几乎所有现代商业系统都已迁移到数据库驱动架构的主要原因。
When answering an exam question on this topic, always present a comparison and give a concrete example. For instance, in a school flat file, a student’s address might be repeated in every attendance record; in a database, the address is stored once in the Students table and referenced by a foreign key, so updating the address only requires a single change.
在回答有关此主题的考试问题时，始终要进行比较并给出具体示例。例如，在学校的平面文件中，学生的地址可能会在每条考勤记录中重复出现；而在数据库中，地址只存储在 Students 表中一次，并通过外键引用，因此更新地址只需要改动一处。
Published by TutorHao | GCSE Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导