GCSE Edexcel Computer Science: SQL Essentials | GCSE Edexcel 计算机：SQL 考点精讲

📚 GCSE Edexcel Computer Science: SQL Essentials | GCSE Edexcel 计算机：SQL 考点精讲

Structured Query Language (SQL) is the standard language used to communicate with relational databases. In the Edexcel GCSE Computer Science course, you are expected to understand how to retrieve, insert, update and delete data, as well as how to filter, sort and aggregate information using SQL queries. This article breaks down every essential SQL topic with clear examples, so you can approach the exam with confidence.

结构化查询语言（SQL）是与关系型数据库交互的标准语言。在 Edexcel GCSE 计算机科学课程中，你需要掌握如何检索、插入、更新和删除数据，以及如何使用 SQL 查询对信息进行筛选、排序和汇总。本文将通过清晰示例逐一分解每个核心 SQL 主题，助你自信迎考。

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

SQL stands for Structured Query Language. It allows users to communicate with a database to perform tasks such as retrieving specific records, adding new data, modifying existing information or deleting obsolete entries. A relational database stores data in tables consisting of rows and columns, where each column represents a field and each row represents a record.

SQL 代表结构化查询语言。用户可以通过它与数据库通信，执行诸如检索特定记录、添加新数据、修改已有信息或删除陈旧条目等任务。关系型数据库将数据存储在由行和列组成的表中，每一列代表一个字段，每一行代表一条记录。

In the Edexcel GCSE specification, you mainly work with a single table or join two tables to extract meaningful information. The exam will test your ability to write correct SQL syntax, so attention to detail with keywords and clause order is crucial.

在 Edexcel GCSE 考试大纲中，你主要操作单表或连接两个表来提取有意义的信息。考试会测试你编写正确 SQL 语法的能力，因此对关键字和子句顺序细节的关注至关重要。

2. The SELECT Statement | SELECT 语句

The SELECT statement is the most fundamental SQL command. It is used to retrieve data from one or more columns of a table. The basic structure is:

SELECT 语句是最基本的 SQL 命令。它用于从表的一个或多个列中检索数据。其基本结构如下：

SELECT column1, column2 FROM table_name;

To retrieve all columns, you can use the asterisk wildcard *. For example, SELECT * FROM Students; returns every column and every row in the Students table.

要检索所有列，可以使用星号通配符 *。例如，SELECT * FROM Students; 将返回 Students 表中的所有列和所有行。

You can also select specific columns to reduce the amount of data returned. For instance, SELECT FirstName, LastName FROM Students; will only list the first and last names of all students.

你还可以选择特定列以减少返回的数据量。例如，SELECT FirstName, LastName FROM Students; 将只列出所有学生的名字和姓氏。

3. Filtering Data with WHERE | 使用 WHERE 过滤数据

The WHERE clause is used to filter records and only return those that meet a specified condition. It comes after the FROM clause:

WHERE 子句用于筛选记录，仅返回满足指定条件的行。它紧跟在 FROM 子句之后：

SELECT column1, column2
FROM table_name
WHERE condition;

Conditions often use comparison operators such as =, <, >, <=, >=, and <> (not equal to). For example, to find all products with a price greater than 50, you would write:

条件常使用比较运算符，如 =、<、>、<=、>= 和 <>（不等于）。例如，要查找所有价格大于 50 的商品，可以这样写：

SELECT ProductName, Price
FROM Products
WHERE Price > 50;

If the column stores textual data, remember to enclose the value in single quotes. For instance, WHERE City = 'London'.

如果列中存储的是文本数据，记得将值括在单引号中。例如，WHERE City = 'London'。

4. Using Logical Operators (AND, OR) | 使用逻辑运算符（AND、OR）

You can combine multiple conditions in the WHERE clause using the logical operators AND and OR. AND requires both conditions to be true, while OR requires at least one condition to be true.

你可以在 WHERE 子句中使用逻辑运算符 AND 和 OR 组合多个条件。AND 要求两个条件都为真，而 OR 要求至少一个条件为真。

For example, to find students who are in Year 10 AND have a grade above 80:

例如，查找 Year 10 且成绩高于 80 的学生：

SELECT Name, Grade
FROM Students
WHERE Year = 10 AND Grade > 80;

To find customers who live in 'Manchester' OR 'Birmingham':

查找居住在 'Manchester' 或 'Birmingham' 的客户：

SELECT Name, City
FROM Customers
WHERE City = 'Manchester' OR City = 'Birmingham';

Use parentheses to group conditions and control the order of evaluation, just as you would in programming. For instance, WHERE (Age >= 18 AND City = 'London') OR (Age >= 16 AND City = 'Manchester').

使用括号对条件分组并控制求值顺序，就像编程中那样。例如，WHERE (Age >= 18 AND City = 'London') OR (Age >= 16 AND City = 'Manchester')。

5. Wildcards and LIKE | 通配符和 LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Two wildcards are commonly used with LIKE: the percent sign % represents zero, one, or multiple characters, while the underscore _ represents a single character.

LIKE 运算符在 WHERE 子句中用于搜索列中的指定模式。LIKE 常与两个通配符配合使用：百分号 % 代表零个、一个或多个字符，下划线 _ 代表单个字符。

For example, to find all students whose first name starts with 'A':

例如，查找所有名字以 'A' 开头的学生：

SELECT FirstName
FROM Students
WHERE FirstName LIKE 'A%';

To find any student with exactly five letters in their last name where the third letter is 'c':

查找姓氏恰好五个字母且第三个字母为 'c' 的学生：

SELECT LastName
FROM Students
WHERE LastName LIKE '__c__';

Keep in mind that LIKE comparisons can be case‑insensitive depending on the database system, but for the exam, assume standard behaviour where case matches unless specified.

请注意，根据数据库系统的不同，LIKE 比较可能不区分大小写，但在考试中，除非特别说明，一般假定大小写匹配。

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

The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order. By default, if no order is specified, the sorting is ascending.

ORDER BY 子句用于将结果集按升序（ASC）或降序（DESC）排序。默认情况下，若未指定顺序，则按升序排序。

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;

You can sort by multiple columns. For example, to sort employees by department (ascending) and then by salary (descending):

你可以按多列排序。例如，先按部门升序，再按薪水降序排列员工：

SELECT Name, Department, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC;

Make sure ORDER BY is the last clause in your SELECT query, after WHERE but before any semicolon.

确保 ORDER BY 是 SELECT 查询中的最后一个子句，位于 WHERE 之后、分号之前。

7. Inserting, Updating and Deleting Data | 插入、更新和删除数据

In addition to retrieving data, SQL allows you to modify the contents of a table with INSERT, UPDATE and DELETE commands. These are important for maintaining a database.

除了检索数据，SQL 还允许使用 INSERT、UPDATE 和 DELETE 命令修改表内容。这些对于维护数据库至关重要。

INSERT adds a new row. You specify the table name, the columns you want to populate, and the values:

INSERT 添加新行。你需要指定表名、要填充的列以及相应的值：

INSERT INTO Students (StudentID, FirstName, LastName, Year)
VALUES (101, 'Emily', 'Clark', 11);

UPDATE changes existing data. Always include a WHERE clause to target specific rows; otherwise, every row will be updated!

UPDATE 更改已有数据。务必包含 WHERE 子句以针对特定行进行更新；否则所有行都会被更新！

UPDATE Students
SET Year = 12
WHERE StudentID = 101;

DELETE removes rows. Like UPDATE, forgetting a WHERE clause will delete every row in the table.

DELETE 删除行。与 UPDATE 类似，忘记 WHERE 子句将删除表中的所有行。

DELETE FROM Students
WHERE StudentID = 101;

In the exam, pay close attention to whether a condition is needed and make sure you are targeting the correct records.

在考试中，要特别注意是否需要条件，并确保你针对的是正确的记录。

8. Aggregate Functions (COUNT, SUM, AVG, MAX, MIN) | 聚合函数

Aggregate functions perform a calculation on a set of values and return a single value. The five main aggregate functions tested at GCSE are:

聚合函数对一组值进行计算并返回单个值。GCSE 考查的五个主要聚合函数是：

COUNT – returns the number of rows
SUM – returns the total sum of a numeric column
AVG – returns the average value of a numeric column
MAX – returns the largest value
MIN – returns the smallest value

COUNT – 返回行数
SUM – 返回数值列的总和
AVG – 返回数值列的平均值
MAX – 返回最大值
MIN – 返回最小值

You use them in the SELECT clause. For example, to count how many students are in the database:

它们在 SELECT 子句中使用。例如，统计数据库中有多少学生：

SELECT COUNT(*)
FROM Students;

To find the highest price among all products:

查找所有商品中的最高价格：

SELECT MAX(Price)
FROM Products;

When using aggregate functions, column names in the SELECT list that are not aggregated must appear in a GROUP BY clause if you are grouping data. This will be covered next.

使用聚合函数时，如果对数据进行分组，SELECT 列表中未聚合的列名必须出现在 GROUP BY 子句中。这将在下一节介绍。

9. Grouping Data with GROUP BY | 使用 GROUP BY 分组

The GROUP BY clause groups rows that have the same values in specified columns. It is often used with aggregate functions to produce summary reports.

GROUP BY 子句将指定列中具有相同值的行分为一组。它常与聚合函数一起使用，以生成汇总报告。

Suppose you have a Sales table and want to find the total sales for each product category. You would write:

假设你有一个 Sales 表，想查找每个产品类别的总销售额。你可以这样写：

SELECT Category, SUM(Amount)
FROM Sales
GROUP BY Category;

The rule to remember is: any column in the SELECT list that is not part of an aggregate function must be included in the GROUP BY clause.

要记住的规则是：SELECT 列表中不属于聚合函数的任何列都必须包含在 GROUP BY 子句中。

You can group by multiple columns. For example, to get the average score per subject per class:

你可以按多列分组。例如，要获得每个班级每门学科的平均分：

SELECT Subject, Class, AVG(Score)
FROM Results
GROUP BY Subject, Class;

10. Filtering Groups with HAVING | 使用 HAVING 过滤分组

WHERE filters rows before grouping, but to filter the groups themselves based on aggregate results, you must use the HAVING clause. HAVING comes after GROUP BY.

WHERE 在分组之前筛选行，但要基于聚合结果筛选分组本身，必须使用 HAVING 子句。HAVING 位于 GROUP BY 之后。

For instance, to find categories where the total sales amount exceeds 1000:

例如，查找总销售额超过 1000 的类别：

SELECT Category, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Category
HAVING SUM(Amount) > 1000;

Note the difference: WHERE checks individual row conditions, while HAVING checks conditions on groups. You can have both a WHERE and a HAVING in the same query; the database will first filter rows with WHERE, then form groups, then apply HAVING.

注意区别：WHERE 检查单个行的条件，而 HAVING 检查分组条件。同一个查询中可同时包含 WHERE 和 HAVING；数据库将首先使用 WHERE 筛选行，然后形成组，最后应用 HAVING。

11. Joining Tables with INNER JOIN | 使用 INNER JOIN 连接表

Many databases contain multiple related tables. To combine data from two tables, you use a JOIN. The most common type tested at GCSE is the INNER JOIN, which returns only the rows that have matching values in both tables.

许多数据库包含多个相关表。要合并两个表中的数据，需要使用 JOIN。GCSE 考查最常见的类型是 INNER JOIN，它只返回两个表中具有匹配值的行。

The typical syntax is:

典型语法是：

SELECT table1.column, table2.column
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

For example, given a Students table and an Enrolments table linked by StudentID, to list each student's name and the course they enrolled in:

例如，假设有一个 Students 表和一个 Enrolments 表，通过 StudentID 关联，要列出每个学生的姓名及所注册的课程：

SELECT Students.FirstName, Students.LastName, Enrolments.Course
FROM Students
INNER JOIN Enrolments
ON Students.StudentID = Enrolments.StudentID;

When joining, it is good practice to prefix column names with the table name to avoid ambiguity, especially when columns share the same name across tables.

进行连接时，最好在列名前加上表名以避免歧义，特别是当不同表中有同名列时。

Be prepared for exam questions that ask you to write a query linking two tables using a common key. Always check which fields form the relationship.

准备好应对要求通过公共键连接两个表编写查询的考题。务必检查哪些字段构成关系。

12. Exam Tips and Common Pitfalls | 考试技巧与常见错误

To maximise your marks, keep these pointers in mind:

要最大化得分，请牢记以下要点：

Always end your statement with a semicolon. While some database systems are forgiving, the mark scheme may require it.
Spell SQL keywords correctly (e.g., SELECT, FROM, WHERE, GROUP BY, ORDER BY).
If a question asks for unique values, remember to use SELECT DISTINCT to avoid duplicates.
Do not confuse ORDER BY and GROUP BY. Ordering sorts the result set; grouping aggregates rows.
When using GROUP BY, ensure every non‑aggregated column in SELECT appears in GROUP BY.
Use HAVING for aggregate conditions; use WHERE for row conditions.
Check if the question requires data from two tables. If so, an INNER JOIN with the matching primary/foreign key is likely needed.
Always test your query logically against the expected output—read the question carefully to confirm you are selecting, filtering and ordering as requested.

始终以分号结束语句。虽然某些数据库系统会宽容处理，但评分方案可能要求这样做。
正确拼写 SQL 关键词（例如 SELECT、FROM、WHERE、GROUP BY、ORDER BY）。
如果题目要求唯一值，记得使用 SELECT DISTINCT 以避免重复。
不要混淆 ORDER BY 和 GROUP BY。排序是对结果集进行排列；分组是对行进行聚合。
使用 GROUP BY 时，确保 SELECT 中每个非聚合列都出现在 GROUP BY 中。
对聚合条件使用 HAVING；对行条件使用 WHERE。
检查题目是否需要来自两个表的数据。如果需要，很可能需要使用主键/外键进行 INNER JOIN。
始终依据预期输出从逻辑上检验你的查询——仔细读题，确认你按要求进行了选择、筛选和排序。

Practice writing queries by hand as you will in the exam. The more familiar you become with the syntax, the easier it will be to spot mistakes.

多练习手写查询，就像考试中那样。你对语法越熟悉，就越容易发现错误。

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

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

GCSE Edexcel Computer Science: SQL Essentials | GCSE Edexcel 计算机：SQL 考点精讲