📚 SQL Essentials for A-Level OCR Computer Science | A-Level OCR 计算机：SQL 考点精讲
Structured Query Language (SQL) is the standard language for interacting with relational databases. For the OCR A-Level Computer Science specification, a solid grasp of SQL is essential not only for the exam but also for understanding how modern applications store, retrieve, and manipulate data efficiently. This article covers every key SQL statement, clause, and concept you need to master, from data definition and manipulation to complex queries involving joins, aggregation, and indexing.
结构化查询语言（SQL）是与关系数据库交互的标准语言。在 OCR A-Level 计算机科学课程中，扎实掌握 SQL 不仅对考试至关重要，更有助于理解现代应用如何高效地存储、检索和处理数据。本文将涵盖你需要掌握的所有关键 SQL 语句、子句和概念，包括数据定义、数据操作，以及涉及连接、聚合和索引等复杂查询。
1. Relational Databases and SQL Basics | 关系数据库与 SQL 基础
A relational database organises data into tables (relations). Each table consists of rows (records) and columns (attributes). Every column has a defined data type, and rows are uniquely identified by a primary key. SQL allows us to define the structure of these tables and to manipulate the data within them.
关系数据库将数据组织到表（关系）中。每张表由行（记录）和列（属性）组成。每个列都有定义好的数据类型，而每一行则由一个主键唯一标识。SQL 让我们能够定义这些表的结构，并对其中的数据进行操作。
SQL is divided into two main categories: Data Definition Language (DDL) for creating and altering table structures, and Data Manipulation Language (DML) for inserting, updating, deleting, and querying data. The most common DML command is SELECT, which retrieves data from one or more tables.
SQL 主要分为两大类：数据定义语言（DDL），用于创建和修改表结构；以及数据操作语言（DML），用于插入、更新、删除和查询数据。最常用的 DML 命令是 SELECT，它从一个或多个表中检索数据。
2. Data Definition Language: CREATE and ALTER | 数据定义语言：CREATE 与 ALTER
The CREATE TABLE statement defines a new table, specifying its columns, data types, and any constraints. Once a table exists, the ALTER TABLE command can be used to add or drop columns, modify data types, or add constraints. Although less common in exam questions, DROP TABLE completely removes a table and its data.
CREATE TABLE 语句用于定义一个新表，指定其列、数据类型和各类约束。表创建后，可以使用 ALTER TABLE 命令来添加或删除列、修改数据类型或添加约束。虽然考得较少，但 DROP TABLE 会彻底删除一个表及其所有数据。
For example, to create a simple students table: CREATE TABLE students (student_id INT, name VARCHAR(50), dob DATE); To later add an email column: ALTER TABLE students ADD email VARCHAR(100);
例如，创建一个简单的 students 表：CREATE TABLE students (student_id INT, name VARCHAR(50), dob DATE); 之后再添加一个电子邮件列：ALTER TABLE students ADD email VARCHAR(100);
DDL also includes the CREATE INDEX statement, which improves query performance by creating an ordered data structure on specified columns. Although indexes are not required for relational integrity, they are a vital performance tool.
DDL 还包括 CREATE INDEX 语句，它通过在指定列上创建有序数据结构来提高查询性能。尽管索引并非关系完整性所必需，但它们是一种重要的性能工具。
3. Data Types, Keys, and Constraints | 数据类型、键与约束
OCR expects you to be familiar with common SQL data types: INTEGER (INT) for whole numbers, VARCHAR(n) for variable-length text, DATE for dates, BOOLEAN for true/false values, and DECIMAL(p,s) for exact numeric values. Choosing the correct type ensures data integrity and efficient storage.
OCR 要求你熟悉常见的 SQL 数据类型：用于整数的 INTEGER (INT)、用于变长文本的 VARCHAR(n)、用于日期的 DATE、用于布尔值的 BOOLEAN，以及用于精确数值的 DECIMAL(p,s)。选择正确的类型可以确保数据完整性和高效存储。
Constraints enforce rules on the data. A PRIMARY KEY uniquely identifies each row and cannot contain NULLs. A FOREIGN KEY links a column to the primary key of another table, enforcing referential integrity. Other constraints include NOT NULL (ensures a value is provided), UNIQUE (no duplicate values), and DEFAULT (sets a fallback value).
约束是对数据强制执行的规则。PRIMARY KEY 唯一标识每一行且不能包含 NULL 值。FOREIGN KEY 将一列与另一张表的主键关联起来，以此确保参照完整性。其他约束包括 NOT NULL（要求必须提供值）、UNIQUE（不允许重复值）和 DEFAULT（设置默认值）。
When a foreign key is defined, referential integrity ensures that a child record cannot reference a non-existent parent. Optionally, ON DELETE CASCADE or ON UPDATE CASCADE can be specified to propagate changes automatically.
定义外键后，参照完整性可确保子记录不能引用不存在的父记录。也可以选用 ON DELETE CASCADE 或 ON UPDATE CASCADE 来自动传播更改。
4. Data Manipulation: INSERT, UPDATE, DELETE | 数据操作：INSERT、UPDATE、DELETE
The INSERT INTO statement adds new rows to a table. You can either specify values for all columns in order or list the column names explicitly. INSERT INTO students (name, dob) VALUES (‘Alice’, ‘2006-05-14’);
INSERT INTO 语句用于向表中添加新行。可以按顺序为所有列提供值，也可以显式列出列名。INSERT INTO students (name, dob) VALUES (‘Alice’, ‘2006-05-14’);
UPDATE modifies existing rows. It is crucial to use a WHERE clause to target specific rows; otherwise, all rows will be changed. UPDATE students SET name = ‘Alicia’ WHERE student_id = 1;
UPDATE 用于修改现有行。务必使用 WHERE 子句来指定要更新的行，否则所有行都会被更改。UPDATE students SET name = ‘Alicia’ WHERE student_id = 1;
DELETE FROM removes rows from a table. Again, a WHERE clause is essential to avoid deleting all data. DELETE FROM students WHERE student_id = 99;
DELETE FROM 用于从表中删除行。同样，必须使用 WHERE 子句，以免删除所有数据。DELETE FROM students WHERE student_id = 99;
5. Basic Retrieval: SELECT and WHERE | 基本检索：SELECT 与 WHERE
The SELECT statement retrieves data from one or more tables. The simplest form is SELECT * FROM table_name; to obtain all columns. To restrict columns, list them separated by commas: SELECT name, dob FROM students;
SELECT 语句从一张或多张表中检索数据。最简单的形式是 SELECT * FROM table_name; 以获取所有列。要限制列，可以用逗号分隔列出：SELECT name, dob FROM students;
The WHERE clause filters rows based on a condition. Comparison operators include =, <> (or !=), <, >, <=, >=. SELECT name FROM students WHERE age >= 17; retrieves students aged 17 and above.
WHERE 子句根据条件过滤行。比较运算符包括 =、<>（或 !=）、<、>、<=、>=。SELECT name FROM students WHERE age >= 17; 将检索年龄大于等于 17 岁的学生。
You can combine conditions with AND, OR, and NOT. Use parentheses to clarify precedence: SELECT * FROM students WHERE (age = 16 OR age = 17) AND grade = ‘A’;
你可以使用 AND、OR 和 NOT 来组合条件。如需明确优先级，可使用括号：SELECT * FROM students WHERE (age = 16 OR age = 17) AND grade = ‘A’;
6. Advanced Filtering: LIKE, BETWEEN, IN, and NULL | 高级过滤：LIKE、BETWEEN、IN 与 NULL
The LIKE operator performs pattern matching on text. The wildcard % represents zero or more characters, while _ represents exactly one character. SELECT name FROM students WHERE name LIKE ‘A%’; finds names starting with A. LIKE ‘_a%’ would match names where the second letter is ‘a’.
LIKE 运算符对文本进行模式匹配。通配符 % 代表零个或多个字符，而 _ 代表恰好一个字符。SELECT name FROM students WHERE name LIKE ‘A%’; 可找出以 A 开头的名字。LIKE ‘_a%’ 则匹配第二个字母为 ‘a’ 的名字。
BETWEEN checks if a value falls within a range, inclusive of the boundaries. SELECT name FROM students WHERE age BETWEEN 16 AND 18; The IN operator matches against a list of values: SELECT * FROM students WHERE house IN (‘Gryffindor’, ‘Ravenclaw’);
BETWEEN 用于检查值是否在一个范围内（包含边界）。SELECT name FROM students WHERE age BETWEEN 16 AND 18; IN 运算符用于匹配一个值列表：SELECT * FROM students WHERE house IN (‘Gryffindor’, ‘Ravenclaw’);
To check for missing data, use IS NULL and IS NOT NULL. Do not use = NULL. SELECT name FROM students WHERE email IS NULL; returns students without an email address on file.
要检查缺失数据，应使用 IS NULL 和 IS NOT NULL，切勿使用 = NULL。SELECT name FROM students WHERE email IS NULL; 可返回未登记电子邮件地址的学生。
7. Joining Tables: INNER JOIN and LEFT JOIN | 表连接：INNER JOIN 与 LEFT JOIN
Joins combine rows from two or more tables based on a related column. The most common is the INNER JOIN, which returns only rows where there is a match in both tables. SELECT students.name, courses.title FROM students INNER JOIN enrolments ON students.student_id = enrolments.student_id INNER JOIN courses ON enrolments.course_id = courses.course_id;
连接操作根据相关列将两张或多张表中的行组合起来。最常用的是 INNER JOIN，它只返回两个表中匹配的行。SELECT students.name, courses.title FROM students INNER JOIN enrolments ON students.student_id = enrolments.student_id INNER JOIN courses ON enrolments.course_id = courses.course_id;
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table, along with matched rows from the right table. If there is no match, NULLs appear for columns of the right table. This is useful for finding students not enrolled in any course.
LEFT JOIN（或 LEFT OUTER JOIN）返回左表的所有行，以及右表中匹配的行。如果没有匹配，右表的列将显示为 NULL。这对于找出未选修任何课程的学生非常有用。
Table aliases make join queries more readable: FROM students s INNER JOIN enrolments e ON s.student_id = e.student_id.
使用表别名可以让连接查询更加易读：FROM students s INNER JOIN enrolments e ON s.student_id = e.student_id。
8. Aggregate Functions: COUNT, SUM, AVG, MIN, MAX | 聚合函数：COUNT、SUM、AVG、MIN、MAX
Aggregate functions perform a calculation on a set of rows and return a single value. COUNT(*) counts all rows, while COUNT(column) counts non-NULL values in that column. SUM and AVG work with numeric data; MIN and MAX return the smallest and largest values respectively.
聚合函数对一组行执行计算并返回单个值。COUNT(*) 计算所有行数，而 COUNT(column) 计算该列中非 NULL 值的数量。SUM 和 AVG 用于数值型数据；MIN 和 MAX 分别返回最小值和最大值。
For example, to find the average age of students: SELECT AVG(age) FROM students; To count how many distinct houses exist: SELECT COUNT(DISTINCT house) FROM students;
例如，要查找学生的平均年龄：SELECT AVG(age) FROM students; 要统计有多少个不同的学院：SELECT COUNT(DISTINCT house) FROM students;
Aggregate functions ignore NULLs except for COUNT(*), which counts every row regardless of NULLs. This behaviour can affect results, so careful attention is needed.
除了 COUNT(*)（它会计算每一行，无论是否包含 NULL），聚合函数都会忽略 NULL 值。这种行为可能影响结果，因此需要格外注意。
9. Grouping Data with GROUP BY | 使用 GROUP BY 对数据分组
The GROUP BY clause groups rows that have the same values in specified columns, allowing aggregate functions to be applied to each group. For instance, to count students per house: SELECT house, COUNT(*) FROM students GROUP BY house;
GROUP BY 子句将指定列中具有相同值的行分为一组，以便对每个组应用聚合函数。例如，按学院统计学生人数：SELECT house, COUNT(*) FROM students GROUP BY house;
Every column in the SELECT list that is not an aggregate must appear in the GROUP BY clause. This rule ensures that the output is unambiguous. A query like SELECT house, name, COUNT(*) FROM students GROUP BY house; would be invalid unless name is also grouped or aggregated.
SELECT 列表中所有不是聚合的列都必须出现在 GROUP BY 子句中。这条规则确保了输出的明确性。像 SELECT house, name, COUNT(*) FROM students GROUP BY house; 这样的查询是无效的，除非 name 也被分组或进行了聚合。
You can group by multiple columns: SELECT house, year, COUNT(*) FROM students GROUP BY house, year; This breaks down the student count by both house and academic year.
你可以按多列分组：SELECT house, year, COUNT(*) FROM students GROUP BY house, year; 这样会按学院和年级来细分学生人数。
10. Filtering Groups with HAVING | 使用 HAVING 过滤分组
The HAVING clause filters groups after aggregation, much like WHERE filters rows before aggregation. It is used with GROUP BY to set conditions on aggregate values. SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 10; returns only houses with more than 10 students.
HAVING 子句在聚合之后对分组进行过滤，就像 WHERE 在聚合之前对行进行过滤一样。它与 GROUP BY 搭配使用，以对聚合值设置条件。SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 10; 只返回学生人数超过 10 人的学院。
WHERE and HAVING can appear in the same query. The database first applies the WHERE condition to individual rows, then groups the surviving rows, and finally applies the HAVING condition to the groups. SELECT house, AVG(grade) FROM students WHERE enrolled = TRUE GROUP BY house HAVING AVG(grade) > 7.5;
WHERE 和 HAVING 可以出现在同一个查询中。数据库首先对各个行应用 WHERE 条件，然后对剩余的行进行分组，最后将 HAVING 条件应用于这些分组。SELECT house, AVG(grade) FROM students WHERE enrolled = TRUE GROUP BY house HAVING AVG(grade) > 7.5;
11. Sorting Results with ORDER BY | 使用 ORDER BY 对结果排序
The ORDER BY clause sorts the final result set. It can sort by one or more columns, either in ascending (ASC, the default) or descending (DESC) order. SELECT name, grade FROM students ORDER BY grade DESC;
ORDER BY 子句用于对最终结果集进行排序。它可以按一列或多列排序，可以是升序（ASC，默认）或降序（DESC）。SELECT name, grade FROM students ORDER BY grade DESC;
When multiple columns are specified, the sorting is performed in the listed sequence. ORDER BY house ASC, grade DESC would order students first by house alphabetically, and within each house, by grade from highest to lowest.
当指定多列时，排序将按所列顺序进行。ORDER BY house ASC, grade DESC 会先按学院的字母顺序排序，再在每个学院内部按成绩从高到低排序。
ORDER BY can reference columns by their position in the SELECT list, e.g., ORDER BY 2 DESC, 1 ASC, but using column names is clearer and preferred for the exam.
ORDER BY 可以通过列在 SELECT 列表中的位置来引用列，例如 ORDER BY 2 DESC, 1 ASC，但为了清晰起见，考试中最好使用列名。
12. Indexes and Performance | 索引与性能
An index is a separate data structure that allows the database to find rows more quickly. The CREATE INDEX statement builds an index on one or more columns of a table. CREATE INDEX idx_student_name ON students(name); can dramatically speed up searches on the name column.
索引是一种独立的数据结构，能让数据库更快地找到行。CREATE INDEX 语句在表的一列或多列上构建索引。CREATE INDEX idx_student_name ON students(name); 可以显著加快对 name 列的搜索。
However, indexes come with trade-offs. They occupy additional storage space and slow down INSERT, UPDATE, and DELETE operations because the index must be maintained. In exam questions, you may be asked to justify the use of indexes on foreign keys or columns frequently used in WHERE, JOIN, and ORDER BY clauses.
然而，索引也有代价。它们会占用额外的存储空间，并且会减慢 INSERT、UPDATE 和 DELETE 操作，因为索引本身也需要维护。在考题中，你可能会被要求论证在外键或经常用于 WHERE、JOIN 和 ORDER BY 子句的列上使用索引的理由。
Published by TutorHao | Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导