GCSE OCR Computer Science: SQL Key Points | GCSE OCR 计算机：SQL 考点精讲

📚 GCSE OCR Computer Science: SQL Key Points | GCSE OCR 计算机：SQL 考点精讲

Structured Query Language (SQL) is the standard way to interact with relational databases. For the OCR GCSE Computer Science specification, you need to understand how to retrieve, insert, update, and delete data using simple yet powerful SQL statements. This guide breaks down every essential command and concept, with clear examples and dual explanations in English and Chinese.

结构化查询语言（SQL）是与关系型数据库交互的标准方式。在 OCR GCSE 计算机科学考试中，你需要掌握如何使用简单而强大的 SQL 语句来检索、插入、更新和删除数据。本指南将分解每一个基本命令和概念，并配以清晰的示例和中英双语解释。

---

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

SQL stands for Structured Query Language. It is a declarative language used to manage data held in a relational database management system (RDBMS). You tell the database what you want, not how to get it. For GCSE, you only need to know Data Manipulation Language (DML) commands: SELECT, INSERT, UPDATE, and DELETE.

SQL 代表结构化查询语言。它是一种声明式语言，用于管理关系数据库管理系统（RDBMS）中的数据。你告诉数据库你想要什么，而不是如何获取。在 GCSE 阶段，你只需要掌握数据操作语言（DML）命令：SELECT、INSERT、UPDATE 和 DELETE。

---

2. The SELECT Statement – Retrieving Data | SELECT 语句 – 检索数据

The most common SQL command is SELECT, which fetches data from a table. The basic syntax is:

SELECT column1, column2 FROM table_name;

To retrieve all columns, use the asterisk wildcard (*): SELECT * FROM table_name;.

最常用的 SQL 命令是 SELECT，它从表中提取数据。基本语法是：

SELECT 列1, 列2 FROM 表名;

要检索所有列，使用星号通配符 (*)：SELECT * FROM 表名;。

Example: SELECT FirstName, LastName FROM Students; returns only the first and last names of all students. The result is a temporary table called a result set.

示例：SELECT FirstName, LastName FROM Students; 仅返回所有学生的名字和姓氏。结果是一个临时表，称为结果集。

---

3. Filtering Rows with WHERE | 使用 WHERE 筛选行

The WHERE clause filters records based on a condition. It is placed after the FROM clause:

SELECT columns FROM table WHERE condition;

Conditions use comparison operators: =, <> (not equal), >, <, >=, <=. Logical operators AND, OR, NOT can combine conditions. Text values must be enclosed in single quotes (e.g. ‘London’).

WHERE 子句根据条件筛选记录。它放在 FROM 子句之后：

SELECT 列 FROM 表 WHERE 条件;

条件使用比较运算符：=、<>（不等于）、>、<、>=、<=。逻辑运算符 AND、OR、NOT 可以组合条件。文本值必须用单引号括起来（例如 ‘London’）。

Example: SELECT * FROM Products WHERE Price < 10 AND Category = ‘Books’; gets all book products priced under 10 units.

示例：SELECT * FROM Products WHERE Price < 10 AND Category = ‘Books’; 获取价格低于 10 单位的所有图书产品。

---

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

ORDER BY sorts the result set by one or more columns. It comes after the WHERE clause (if any). The default order is ascending (ASC); use DESC for descending.

SELECT columns FROM table WHERE condition ORDER BY column1 ASC, column2 DESC;

ORDER BY 按一列或多列对结果集进行排序。它放在 WHERE 子句之后（如果有）。默认顺序是升序（ASC）；使用 DESC 表示降序。

SELECT 列 FROM 表 WHERE 条件 ORDER BY 列1 ASC, 列2 DESC;

Example: SELECT Name, Score FROM Results WHERE Subject=’Maths’ ORDER BY Score DESC; lists maths students from highest to lowest score.

示例：SELECT Name, Score FROM Results WHERE Subject=’Maths’ ORDER BY Score DESC; 从高到低列出数学学生的分数。

---

5. Inserting New Data – INSERT INTO | 插入新数据 – INSERT INTO

To add a new row to a table, use INSERT INTO. You can specify the column names and corresponding values:

INSERT INTO table_name (col1, col2) VALUES (val1, val2);

If you are inserting values for every column in the exact order they appear in the table, you can omit the column list. Text and dates go in single quotes.

要向表中添加新行，使用 INSERT INTO。你可以指定列名和对应的值：

INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);

如果你要为表中的每一列按出现的确切顺序插入值，可以省略列列表。文本和日期用单引号括起来。

Example: INSERT INTO Students (StudentID, Name, Year) VALUES (105, ‘Ali Khan’, 11); adds a new student.

示例：INSERT INTO Students (StudentID, Name, Year) VALUES (105, ‘Ali Khan’, 11); 添加一名新学生。

---

6. Modifying Existing Data – UPDATE | 修改现有数据 – UPDATE

UPDATE changes existing records. You must specify which rows to change using WHERE; otherwise all rows get updated.

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

UPDATE 修改现有记录。你必须使用 WHERE 指定要更改哪些行；否则所有行都会被更新。

UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;

Example: UPDATE Products SET Price = Price * 1.1 WHERE Category = ‘Electronics’; raises prices by 10% for all electronic items.

示例：UPDATE Products SET Price = Price * 1.1 WHERE Category = ‘Electronics’; 将所有电子产品的价格提高 10%。

---

7. Deleting Data – DELETE FROM | 删除数据 – DELETE FROM

DELETE FROM removes rows from a table. Use WHERE to choose which rows; without WHERE, all rows are deleted (the table structure remains).

DELETE FROM table_name WHERE condition;

DELETE FROM 从表中删除行。使用 WHERE 来选择要删除的行；如果没有 WHERE，所有行都会被删除（表结构保留）。

DELETE FROM 表名 WHERE 条件;

Example: DELETE FROM Enrolments WHERE StudentID = 205 AND CourseID = ‘C4’; removes a specific student’s enrolment.

示例：DELETE FROM Enrolments WHERE StudentID = 205 AND CourseID = ‘C4’; 删除特定学生的选课记录。

---

8. Using Wildcards with LIKE | 使用 LIKE 与通配符

The LIKE operator is used in a WHERE clause to search for a pattern. Two common wildcards in OCR SQL are:

• % – represents zero, one, or multiple characters
• _ – represents a single character

Example: SELECT * FROM Staff WHERE Surname LIKE ‘Sm%’; finds all staff whose surname starts with ‘Sm’. SELECT * FROM Books WHERE Title LIKE ‘The_%’; finds titles starting with ‘The’ followed by at least one character.

LIKE 运算符用于 WHERE 子句中，按模式搜索。OCR SQL 中两个常见的通配符是：

• % – 代表零个、一个或多个字符
• _ – 代表单个字符

示例：SELECT * FROM Staff WHERE Surname LIKE ‘Sm%’; 查找所有姓氏以 ‘Sm’ 开头的员工。SELECT * FROM Books WHERE Title LIKE ‘The_%’; 查找以 ‘The’ 开头且其后至少有一个字符的书名。

---

9. Joining Tables – INNER JOIN | 连接表 – INNER JOIN

Data is often spread across multiple tables linked by keys. An INNER JOIN combines rows from two tables where the join condition is true. The basic syntax is:

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

数据通常分布在由键连接的多个表中。INNER JOIN 将来自两个表中满足连接条件的行组合在一起。基本语法是：

SELECT 表1.列, 表2.列
FROM 表1
INNER JOIN 表2
ON 表1.公共字段 = 表2.公共字段;

Example: SELECT Students.Name, Courses.Title FROM Students INNER JOIN Enrolments ON Students.StudentID = Enrolments.StudentID INNER JOIN Courses ON Enrolments.CourseID = Courses.CourseID; shows each student’s name and their enrolled course titles.

示例：SELECT Students.Name, Courses.Title FROM Students INNER JOIN Enrolments ON Students.StudentID = Enrolments.StudentID INNER JOIN Courses ON Enrolments.CourseID = Courses.CourseID; 显示每个学生的姓名和他们所选课程的标题。

---

10. Using Aliases with AS | 使用别名 AS

The AS keyword gives a temporary name (alias) to a column or table. This makes output more readable or simplifies complex queries.

SELECT column AS ‘Alias Name’ FROM table;

Example: SELECT Surname AS ‘Family Name’, DateOfBirth AS ‘DOB’ FROM Pupils; renames the output columns.

AS 关键字为列或表提供临时名称（别名）。这使输出更易读，或简化复杂查询。

SELECT 列 AS ‘别名’ FROM 表;

示例：SELECT Surname AS ‘Family Name’, DateOfBirth AS ‘DOB’ FROM Pupils; 重命名输出列。

---

11. Avoiding Duplicates with DISTINCT | 使用 DISTINCT 去除重复

SELECT DISTINCT removes duplicate rows from the result set. It applies to all selected columns. Use it when you need unique values, such as a list of all cities where customers live.

SELECT DISTINCT column FROM table;

Example: SELECT DISTINCT Country FROM Customers; lists each country only once.

SELECT DISTINCT 从结果集中删除重复行。它适用于所有选定的列。当你需要唯一值时使用它，例如列出客户居住的所有城市。

SELECT DISTINCT 列 FROM 表;

示例：SELECT DISTINCT Country FROM Customers; 每个国家只列出一次。

---

12. SQL Command Summary Table | SQL 命令汇总表

The table below summarises the key SQL keywords you must know for the OCR GCSE exam, along with their function and a brief example.

下表总结了 OCR GCSE 考试中你必须掌握的关键 SQL 关键词，以及它们的功能和简要示例。

Keyword	Function	Example snippet
SELECT	Retrieve data	SELECT Name FROM Students;
FROM	Specify table	FROM Employees
WHERE	Filter rows	WHERE Age >= 18
ORDER BY	Sort results	ORDER BY Score DESC
INSERT INTO	Add new row	INSERT INTO Products VALUES (…)
UPDATE	Modify existing rows	UPDATE Items SET Price = 5.99
DELETE FROM	Remove rows	DELETE FROM Orders WHERE Status=’Cancelled’
LIKE	Pattern matching	WHERE Title LIKE ‘A%’
INNER JOIN	Combine tables	… ON Table1.ID = Table2.ID
AS	Alias for column/table	SELECT Surname AS ‘Last Name’
DISTINCT	Remove duplicates	SELECT DISTINCT City FROM Offices;

---

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

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