SQL Exam Essentials | SQL 考点精讲

📚 SQL Exam Essentials | SQL 考点精讲

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. In the AQA GCSE Computer Science specification, you need to understand how to write simple SQL statements to retrieve, insert, update and delete data, as well as how to use basic clauses like WHERE, ORDER BY and JOIN to filter and combine information from tables. This article breaks down every essential SQL concept you will face in the exam, with clear syntax examples, common pitfalls and revision tips to help you score full marks on database questions.

结构化查询语言（SQL）是管理和操作关系型数据库的标准语言。在 AQA GCSE 计算机科学考纲中，你需要掌握如何编写简单的 SQL 语句来检索、插入、更新和删除数据，以及如何使用 WHERE、ORDER BY 和 JOIN 等基本子句来筛选和组合表中的信息。本文将逐一剖析考试涉及的每个核心 SQL 概念，提供清晰的语法示例、常见易错点和复习建议，助你在数据库题目上拿到满分。

1. What is SQL? | 什么是 SQL？

SQL stands for Structured Query Language and is used to communicate with a relational database. A relational database organises data into tables (also called relations) made up of rows (records) and columns (fields). SQL allows you to perform CRUD operations – Create, Read, Update and Delete – on the data stored in these tables. In the GCSE exam, you will mostly write data retrieval queries using SELECT, but you also need to know how to modify data and define basic table structures.

SQL 全称结构化查询语言，用于与关系型数据库通信。关系型数据库将数据组织为表（也称为关系），表由行（记录）和列（字段）组成。SQL 允许你对这些表中存储的数据执行增删改查（CRUD）操作。在 GCSE 考试中，你会大量编写使用 SELECT 的数据检索查询，但也需要知道如何修改数据和定义基本的表结构。

Every SQL statement ends with a semicolon (;), though many exam questions may omit it in pseudocode. Keywords like SELECT, FROM and WHERE are written in uppercase by convention, but SQL is not case‑sensitive for keywords. However, string values inside quotes are case‑sensitive. Always remember to match single quotes around text values: ‘London’, not ‘London’ or ‘London’.

每条 SQL 语句以分号（;）结尾，尽管许多试题中的伪代码可能会省略它。按照惯例，SELECT、FROM、WHERE 等关键字使用大写，但 SQL 关键字不区分大小写。然而，引号内的字符串值是区分大小写的。务必记住用单引号括起文本值：’London’，而不是 ‘London’ 或 ‘London’。

2. The SELECT and FROM Clauses | SELECT 与 FROM 子句

The most common SQL command is SELECT, which retrieves data from a database. The basic structure is: SELECT column1, column2 FROM table_name; To select all columns, use the asterisk wildcard (*). For example, SELECT * FROM Students; returns every column for every student.

最常用的 SQL 命令是 SELECT，它从数据库中检索数据。基本结构是：SELECT 列1, 列2 FROM 表名；要选择所有列，使用星号通配符（*）。例如，SELECT * FROM Students; 会返回每位学生的所有列。

When you list specific columns, separate them with commas: SELECT FirstName, LastName, TutorGroup FROM Students; The order of columns in the result set matches the order you specified. AQA exams often give you a table schema and ask you to write a query that displays only certain fields, so pay close attention to the exact column names.

当你列出具体列时，用逗号分隔它们：SELECT FirstName, LastName, TutorGroup FROM Students; 结果集中列的顺序与你指定的顺序一致。AQA 考试常给出一个表结构，要求你编写只显示特定字段的查询，因此请务必注意确切的列名。

A common mistake is forgetting the comma between column names, or placing a comma after the last column before FROM. The correct syntax is SELECT col1, col2, col3 FROM table; not SELECT col1, col2, col3, FROM table;.

一个常见错误是忘记列名之间的逗号，或者在最后一个列名之后、FROM 之前多加一个逗号。正确语法是 SELECT col1, col2, col3 FROM table; 而不是 SELECT col1, col2, col3, FROM table;。

3. Filtering Data with WHERE | 用 WHERE 筛选数据

The WHERE clause is used to filter records so that only rows meeting a specific condition are returned. It follows the FROM clause: SELECT column FROM table WHERE condition; For instance, SELECT * FROM Students WHERE Year = 11; retrieves all Year 11 students.

WHERE 子句用于筛选记录，只返回满足特定条件的行。它跟在 FROM 子句之后：SELECT 列 FROM 表 WHERE 条件；例如，SELECT * FROM Students WHERE Year = 11; 检索所有 11 年级的学生。

You can use comparison operators: = (equal to), <> or != (not equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to). With text, use = to match an exact string: WHERE City = 'Manchester'. Remember the quotes around the text value. For numbers, no quotes: WHERE Age >= 16.

你可以使用比较运算符：=（等于）、<> 或 !=（不等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）。对于文本，使用 = 匹配完全相同的字符串：WHERE City = 'Manchester'。记得在文本值外加单引号。对于数字，不加引号：WHERE Age >= 16。

Multiple conditions can be combined with AND, OR and NOT. AND requires both conditions true; OR requires at least one true; NOT negates a condition. Use parentheses to group logic clearly: WHERE (Subject = ‘Maths’ OR Subject = ‘English’) AND Grade >= 6. This returns rows where the subject is Maths or English and the grade is at least 6.

多个条件可以用 AND、OR 和 NOT 组合。AND 要求所有条件为真；OR 要求至少一个为真；NOT 否定条件。使用括号清晰地分组逻辑：WHERE (Subject = ‘Maths’ OR Subject = ‘English’) AND Grade >= 6。这会返回科目为数学或英语且成绩至少为 6 的行。

4. Wildcards and the LIKE Operator | 通配符与 LIKE 运算符

When you need to match a pattern rather than an exact value, use the LIKE operator along with two wildcard characters: % (percent) represents zero, one or multiple characters; _ (underscore) represents exactly one character. Syntax: WHERE column LIKE ‘pattern’. For example, WHERE LastName LIKE ‘Sm%’ finds all surnames starting with ‘Sm’, such as ‘Smith’, ‘Smyth’, ‘Small’.

当需要匹配模式而非精确值时，使用 LIKE 运算符配合两个通配符：%（百分号）代表零个、一个或多个字符；_（下划线）代表恰好一个字符。语法：WHERE 列名 LIKE ‘模式’。例如，WHERE LastName LIKE ‘Sm%’ 找到所有以 ‘Sm’ 开头的姓氏，如 ‘Smith’、’Smyth’、’Small’。

To search for a specific number of characters, use underscores: WHERE Postcode LIKE ‘SW1_ 3A_’ (assuming the space is part of the pattern). This would match ‘SW1A 3AB’ but not ‘SW12 3AB’. Be careful with exam questions: they may ask you to retrieve records where a field ends with something, e.g. WHERE Email LIKE ‘%@school.org’.

若要搜索特定数量的字符，使用下划线：WHERE Postcode LIKE ‘SW1_ 3A_’（假设空格是模式的一部分）。这会匹配 ‘SW1A 3AB’，但不匹配 ‘SW12 3AB’。注意考试题目：可能要求检索字段以某内容结尾的记录，如 WHERE Email LIKE ‘%@school.org’。

Note that LIKE is case‑insensitive in some database systems, but for GCSE you should treat it as case‑sensitive for strings unless told otherwise. Always enclose the pattern in single quotes.

注意，在某些数据库系统中 LIKE 不区分大小写，但在 GCSE 中，除非另有说明，你应将字符串视为区分大小写。始终将模式放在单引号内。

5. Sorting Results with ORDER BY | 使用 ORDER BY 排序结果

The ORDER BY clause sorts the result set by one or more columns. By default, it sorts in ascending order (ASC). To sort in descending order, add DESC. Syntax: SELECT column FROM table ORDER BY column1 ASC, column2 DESC; For example, SELECT Name, Score FROM Results ORDER BY Score DESC, Name ASC; sorts by highest score first, and if scores are equal, alphabetically by name.

ORDER BY 子句对一个或多个列的结果集进行排序。默认按升序排序（ASC）。若要降序排序，添加 DESC。语法：SELECT 列 FROM 表 ORDER BY 列1 ASC, 列2 DESC; 例如，SELECT Name, Score FROM Results ORDER BY Score DESC, Name ASC; 先按最高分排序，若分数相同则按姓名升序排列。

You can use column numbers instead of names in ORDER BY (e.g., ORDER BY 2 DESC), but AQA expects you to use column names for clarity. Always place ORDER BY after WHERE (if present). If both WHERE and ORDER BY are used, the correct order is SELECT, FROM, WHERE, ORDER BY.

在 ORDER BY 中可以使用列序号代替列名（如 ORDER BY 2 DESC），但 AQA 希望你使用列名以保持清晰。始终将 ORDER BY 放在 WHERE（如果有）之后。如果同时使用 WHERE 和 ORDER BY，正确的顺序是 SELECT、FROM、WHERE、ORDER BY。

6. Inserting Data with INSERT INTO | 用 INSERT INTO 插入数据

To add a new record to a table, use the INSERT INTO statement. There are two main forms. The first specifies both columns and values: INSERT INTO table (col1, col2) VALUES (val1, val2); For instance, INSERT INTO Students (StudentID, FirstName, LastName) VALUES (504, ‘Chloe’, ‘Adams’);

要向表中添加新记录，使用 INSERT INTO 语句。主要有两种形式。第一种同时指定列和值：INSERT INTO 表 (列1, 列2) VALUES (值1, 值2); 例如，INSERT INTO Students (StudentID, FirstName, LastName) VALUES (504, ‘Chloe’, ‘Adams’);

The second form omits the column list, provided you supply values for every column in the correct order: INSERT INTO Students VALUES (504, ‘Chloe’, ‘Adams’, 11, ‘Turing’); This approach is risky if the table structure changes. Exam questions usually expect you to list the columns explicitly.

第二种形式省略列名列表，但前提是你按正确顺序为每一列提供值：INSERT INTO Students VALUES (504, ‘Chloe’, ‘Adams’, 11, ‘Turing’); 如果表结构发生变化，这种方法有风险。考试题目通常希望你显式列出列名。

Remember that text values must be in single quotes. Primary key values must be unique; inserting a duplicate primary key will cause an error. Auto‑incremented fields (like an ID that increases automatically) should not be included in the column list if the database handles them automatically.

记住，文本值必须放在单引号内。主键值必须唯一；插入重复的主键会导致错误。如果某些字段是自动递增的（例如自动增加的 ID），在数据库自动处理的情况下，不应将它们包含在列列表中。

7. Modifying Records with UPDATE | 用 UPDATE 修改记录

The UPDATE statement changes existing data in a table. It is almost always paired with WHERE to avoid updating all rows. Syntax: UPDATE table SET col1 = val1, col2 = val2 WHERE condition; For example, UPDATE Students SET TutorGroup = ‘Hawking’ WHERE StudentID = 213; changes the tutor group of the student with ID 213.

UPDATE 语句修改表中的现有数据。它几乎总是与 WHERE 搭配使用，以避免更新所有行。语法：UPDATE 表 SET 列1 = 值1, 列2 = 值2 WHERE 条件; 例如，UPDATE Students SET TutorGroup = ‘Hawking’ WHERE StudentID = 213; 将学号为 213 的学生的导师组改为 ‘Hawking’。

If you forget the WHERE clause, every row will be updated, which is a common exam pitfall. You can update multiple columns at once by separating them with commas: SET Grade = ‘A’, Effort = ‘Excellent’ WHERE StudentID = 105;. The values can be text, numbers or dates, each formatted correctly.

如果忘记 WHERE 子句，每一行都会被更新，这是考试中常见的陷阱。你可以通过逗号分隔一次性更新多列：SET Grade = ‘A’, Effort = ‘Excellent’ WHERE StudentID = 105;。值可以是文本、数字或日期，各自格式正确即可。

8. Removing Records with DELETE | 用 DELETE 删除记录

The DELETE statement removes rows from a table. Like UPDATE, it should usually be restricted with WHERE. Syntax: DELETE FROM table WHERE condition; Example: DELETE FROM Students WHERE Year = 13 AND LeavingDate < '2025-07-01'; removes leavers who left before a certain date.

DELETE 语句从表中删除行。与 UPDATE 一样，通常要用 WHERE 加以限制。语法：DELETE FROM 表 WHERE 条件; 示例：DELETE FROM Students WHERE Year = 13 AND LeavingDate < '2025-07-01'; 删除在特定日期前离校的 13 年级毕业生。

Omitting WHERE deletes all rows, leaving an empty table. Some exam questions might test this understanding by showing a query without WHERE and asking what happens. To delete all rows but keep the table structure, use DELETE FROM table;. Do not confuse DELETE with DROP TABLE, which removes the entire table definition.

省略 WHERE 会删除所有行，留下空表。有些考题可能会展示没有 WHERE 的查询，询问会发生什么，以此考查你的理解。要删除所有行但保留表结构，使用 DELETE FROM table;。不要将 DELETE 与 DROP TABLE 混淆，后者会删除整个表的定义。

9. Combining Tables with INNER JOIN | 用 INNER JOIN 连接表

When data is normalised across multiple tables, you often need to combine them using a JOIN. The most common at GCSE is the INNER JOIN, which returns rows where there is a match in both tables based on a common field (usually a primary key in one table and a foreign key in another). Syntax: SELECT TableA.col1, TableB.col2 FROM TableA INNER JOIN TableB ON TableA.common = TableB.common;

当数据跨多个表规范化存储时，你常需要用 JOIN 将它们组合起来。GCSE 中最常见的是 INNER JOIN，它返回在两个表中基于公共字段（通常是一个表的主键和另一个表的外键）都有匹配的行。语法：SELECT 表A.列1, 表B.列2 FROM 表A INNER JOIN 表B ON 表A.公共字段 = 表B.公共字段;

Example: Students table has StudentID as primary key, and Exams table has StudentID as a foreign key. To list each student’s name and their exam score: SELECT Students.Name, Exams.Score FROM Students INNER JOIN Exams ON Students.StudentID = Exams.StudentID; Use table_name.column_name to qualify ambiguous column names, especially when both tables have a column with the same name.

示例：Students 表以 StudentID 为主键，Exams 表以 StudentID 为外键。要列出每位学生的姓名及其考试成绩：SELECT Students.Name, Exams.Score FROM Students INNER JOIN Exams ON Students.StudentID = Exams.StudentID; 使用表名.列名来限定不明确的列名，特别是当两个表有同名列时。

LEFT JOIN and RIGHT JOIN are not explicitly required by AQA, but knowing that INNER JOIN only returns matching rows helps you interpret database diagrams. In the exam, you might be given a relational schema and asked to write a query that pulls related data from two tables.

LEFT JOIN 和 RIGHT JOIN 并非 AQA 明确要求的，但知道 INNER JOIN 只返回匹配的行有助于你理解数据库图表。考试中，可能会给你一个关系模式，要求你编写从两个表中提取相关数据的查询。

10. Aggregate Functions and GROUP BY | 聚合函数与 GROUP BY

SQL provides built‑in functions to perform calculations on a set of rows: COUNT, SUM, AVG, MAX and MIN. COUNT(*) counts all rows; COUNT(column) counts non‑null values. SUM and AVG work on numeric columns. MAX and MIN return the highest and lowest values. Example: SELECT COUNT(*) FROM Students WHERE Year = 10; counts the number of Year 10 students.

SQL 提供内置函数来对一组行进行计算：COUNT、SUM、AVG、MAX 和 MIN。COUNT(*) 计数所有行；COUNT(列) 计数非空值。SUM 和 AVG 用于数值列。MAX 和 MIN 返回最高和最低值。示例：SELECT COUNT(*) FROM Students WHERE Year = 10; 计算 10 年级学生的人数。

To group results by a certain column, use GROUP BY. This is often paired with aggregate functions. For example, to find the highest score in each subject: SELECT Subject, MAX(Score) FROM Exams GROUP BY Subject; The HAVING clause filters groups after aggregation, similar to WHERE for rows. AQA may only touch on GROUP BY lightly; you should just know that GROUP BY is used with aggregates.

要按某一列对结果进行分组，使用 GROUP BY。它常与聚合函数一起使用。例如，要找到每门科目的最高分：SELECT Subject, MAX(Score) FROM Exams GROUP BY Subject; HAVING 子句在聚合后筛选分组，类似于针对行的 WHERE。AQA 对 GROUP BY 可能仅作简单考查；你只需知道 GROUP BY 与聚合函数一起使用即可。

11. Data Types and Table Creation (AQA Context) | 数据类型与建表（AQA 情境）

While the specification focuses on data manipulation, you should recognise common SQL data types: INTEGER (whole numbers), REAL/DECIMAL (numbers with a fractional part), VARCHAR(n) or TEXT (variable‑length strings), BOOLEAN (true/false), DATE and TIME. In exam questions, you may be asked to identify appropriate data types for given fields, or to create a simple table using CREATE TABLE.

虽然考纲侧重于数据操作，但你应认识常见的 SQL 数据类型：INTEGER（整数）、REAL/DECIMAL（带小数部分的数）、VARCHAR(n) 或 TEXT（可变长度字符串）、BOOLEAN（真/假）、DATE 和 TIME。考题中，可能会要求你为给定字段选择合适的数据类型，或使用 CREATE TABLE 创建简单表。

A basic CREATE TABLE example: CREATE TABLE Books (BookID INTEGER PRIMARY KEY, Title VARCHAR(100), Author VARCHAR(50), Published DATE); Notice the primary key constraint and semicolons. AQA may not require full DDL syntax, but understanding it reinforces your relational database knowledge.

一个基本的 CREATE TABLE 示例：CREATE TABLE Books (BookID INTEGER PRIMARY KEY, Title VARCHAR(100), Author VARCHAR(50), Published DATE); 注意主键约束和分号。AQA 可能不要求完整的 DDL 语法，但理解它能巩固你的关系型数据库知识。

12. Exam Tips and Common Mistakes | 应试技巧与常见错误

Read the schema carefully: Exam questions provide table names and column names exactly as they appear in the database. Use those exact spellings and cases. If a column is called ‘StudentID’, do not write ‘student_id’.
仔细阅读模式：考题会给出数据库中确切的表名和列名。使用与题目完全相同的拼写和大小写。如果列名为 ‘StudentID’，不要写成 ‘student_id’。
Quote text, not numbers: A common mistake is wrapping numbers in quotes (’10’) or forgetting quotes around text. Text literals need single quotes; numeric values do not.
引号括文本，不括数字：一个常见错误是将数字放在引号内（’10’），或忘记给文本加引号。文本字面量需要单引号；数值不需要。
Order of clauses: The mandatory order is SELECT -> FROM -> WHERE -> ORDER BY. Mixing up WHERE and ORDER BY loses marks. INSERT, UPDATE and DELETE have their own fixed structures.
子句顺序：强制顺序是 SELECT -> FROM -> WHERE -> ORDER BY。混淆 WHERE 和 ORDER BY 会丢分。INSERT、UPDATE 和 DELETE 也有各自的固定结构。
Always consider the question’s context: If the question asks for ‘students in Year 10 with a grade above 5’, your query must include both conditions, often with AND.
始终考虑题目语境：如果题目要求查找 ’10 年级成绩高于 5 的学生’，你的查询必须包含这两个条件，通常使用 AND。
Use meaningful aliases for clarity: When joining, prefix columns with table names to avoid ambiguity, e.g., Students.Name instead of just Name.
使用有意义的别名以保证清晰：连接时，用表名前缀列名以避免歧义，例如使用 Students.Name 而不仅仅是 Name。
Practice past paper questions: SQL is best learned by writing queries. Try to recreate the sample databases in your mind and test your logic.
练习历年试题：学习 SQL 的最佳方式是动手写查询。试着在脑海中重建样题数据库，并检验你的逻辑。

Lastly, remember that SQL is a declarative language – you say what you want, not how to get it. Focus on the correct syntax and the logical flow of filtering, sorting and joining. With careful attention to detail, you can secure every SQL mark in the exam.

最后，请记住 SQL 是一种声明式语言——你说出想要什么，而不是如何得到它。专注于正确的语法以及筛选、排序和连接的逻辑流程。只要注重细节，你就能在考试中拿到所有 SQL 相关分数。

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

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

SQL Exam Essentials | SQL 考点精讲