📚 A-Level CIE Computer Science: SQL Key Concepts | A-Level CIE 计算机科学：SQL 考点精讲
Structured Query Language (SQL) is a fundamental topic in the Cambridge International AS & A Level Computer Science syllabus (9618). It enables you to define, manipulate, and query data within relational databases. Mastering SQL is essential for both the practical and theory examinations, as questions often require you to write precise statements for data retrieval, modification, and database structure definition.
结构化查询语言 (SQL) 是剑桥国际 AS 与 A Level 计算机科学 (9618) 教学大纲中的基础主题。它使你能够定义、操作和查询关系数据库中的数据。掌握 SQL 对实践和理论考试都至关重要，因为考题经常要求你编写精确的语句来实现数据检索、修改以及数据库结构定义。
This article distills the core SQL knowledge points tested by CIE, covering Data Definition Language (DDL), Data Manipulation Language (DML), joins, subqueries, and views. Each section presents a clear explanation with example statements, ensuring you can apply them confidently in exam conditions.
本文将提炼 CIE 考试的核心 SQL 知识点，涵盖数据定义语言 (DDL)、数据操作语言 (DML)、表连接、子查询和视图。每个部分都提供清晰的解释和示例语句，确保你能在考试中自信地运用。
1. SQL Overview and Role | SQL 概览与作用
SQL is the standard language for interacting with relational database management systems (RDBMS). It is divided into two main categories: Data Definition Language (DDL), which handles the structure of database objects, and Data Manipulation Language (DML), which deals with the data itself. In the CIE syllabus, you are expected to write SQL statements, not just understand theory.
SQL 是与关系数据库管理系统 (RDBMS) 交互的标准语言。它主要分为两大类：数据定义语言 (DDL)，处理数据库对象的结构；以及数据操作语言 (DML)，处理数据本身。在 CIE 教学大纲中，你需要能够编写 SQL 语句，而不仅仅是理解理论。
A relational database consists of tables (relations) made up of rows (records) and columns (attributes). The key concept is that tables can be linked through primary and foreign keys, which allows efficient querying across multiple tables using joins.
关系数据库由表（关系）组成，表由行（记录）和列（属性）构成。关键概念是表可以通过主键和外键关联起来，这样就可以通过连接 (join) 跨多个表高效查询。
Common SQL keywords and their purposes are shown below:
常见 SQL 关键字及其用途如下：
| Category | Keywords | Purpose |
|---|---|---|
| DDL | CREATE, ALTER, DROP |
Define/remove tables and constraints |
| DML | SELECT, INSERT, UPDATE, DELETE |
Retrieve and modify data |
类型：DDL 关键字 CREATE, ALTER, DROP 定义/删除表和约束；DML 关键字 SELECT, INSERT, UPDATE, DELETE 检索和修改数据。
2. Data Definition Language (DDL) – Creating and Modifying Tables | 数据定义语言 (DDL) – 创建与修改表
DDL statements allow you to create, alter, and delete the structure of database tables. The most basic statement is CREATE TABLE, which specifies the table name, column definitions, data types, and any constraints.
DDL 语句允许你创建、修改和删除数据库表的结构。最基本的语句是 CREATE TABLE，它指定表名、列定义、数据类型以及任何约束。
Example – creating a Student table:
示例 – 创建 Student 表：
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(40) NOT NULL,
LastName VARCHAR(40) NOT NULL,
DateOfBirth DATE,
YearGroup INT
);
This statement defines five columns with appropriate data types. Common data types for CIE include INT (integer), VARCHAR(n) (variable-length string up to n characters), DATE, and DECIMAL(p,s) (exact numeric with precision and scale).
这个语句定义了五列，使用了适当的数据类型。CIE 常见的数据类型包括 INT（整数）、VARCHAR(n)（最多 n 个字符的变长字符串）、DATE 以及 DECIMAL(p,s)（具有精度和小数位数的精确数值）。
To modify an existing table, you can use ALTER TABLE. For example, adding a column:
你可以使用 ALTER TABLE 修改现有表。例如，添加一列：
ALTER TABLE Student ADD Email VARCHAR(100);
You may also drop a column or change a data type using ALTER TABLE ... DROP COLUMN column_name; or ALTER TABLE ... MODIFY column_name datatype;. To remove the whole table, use DROP TABLE Student;.
你还可以使用 ALTER TABLE ... DROP COLUMN column_name; 删除列，或使用 ALTER TABLE ... MODIFY column_name datatype; 修改数据类型。要删除整个表，使用 DROP TABLE Student;。
3. Constraints and Keys | 约束与键
Constraints enforce rules on the data in a table to maintain integrity. The primary key uniquely identifies each row; a composite primary key can be formed from multiple columns. The foreign key establishes a link between two tables, ensuring referential integrity.
约束对表中的数据强制执行规则以保持完整性。主键唯一标识每一行；复合主键可以由多个列组成。外键在两个表之间建立联系，确保参照完整性。
Example with primary and foreign keys:
带有主键和外键的示例：
CREATE TABLE Customer (
CustID INT PRIMARY KEY,
Name VARCHAR(60) NOT NULL
);
CREATE TABLE Order (
OrderID INT PRIMARY KEY,
CustID INT,
OrderDate DATE,
FOREIGN KEY (CustID) REFERENCES Customer(CustID)
);
Other important constraints include NOT NULL (prevents empty values), UNIQUE (ensures all values in a column are distinct), and CHECK (validates a condition, e.g. CHECK (Quantity > 0)). In exam questions, you are often asked to write CREATE TABLE statements with appropriate constraints.
其他重要的约束有 NOT NULL（防止空值）、UNIQUE（确保列中所有值唯一）以及 CHECK（验证条件，例如 CHECK (Quantity > 0)）。在考题中，你经常会被要求编写带有适当约束的 CREATE TABLE 语句。
4. Data Manipulation Language (DML) – SELECT Basics | 数据操作语言 (DML) – SELECT 基础
The SELECT statement retrieves data from one or more tables. Its simplest form specifies the columns and the table name. The wildcard * selects all columns. You can also rename columns using an alias with the AS keyword.
SELECT 语句从一个或多个表中检索数据。最简单的形式是指定列名和表名。通配符 * 会选择所有列。你还可以使用 AS 关键字为列设置别名。
Examples:
示例：
SELECT FirstName, LastName FROM Student;
SELECT * FROM Student;
SELECT FirstName AS 'Given Name', LastName AS 'Family Name' FROM Student;
The DISTINCT keyword removes duplicate rows from the result set. SELECT DISTINCT YearGroup FROM Student; returns each year group only once.
DISTINCT 关键字从结果集中移除重复行。SELECT DISTINCT YearGroup FROM Student; 只返回每个年级一次。
Always remember that the order of execution in a SELECT query begins with FROM, but you write the clauses in the standard order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Understanding this will help you avoid syntax errors.
请始终记住，SELECT 查询的执行顺序从 FROM 开始，但你编写子句的标准顺序是：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。理解这一点有助于避免语法错误。
5. Filtering with WHERE and Sorting with ORDER BY | 使用 WHERE 过滤与 ORDER BY 排序
The WHERE clause filters rows based on a condition. Comparison operators include =, <> (or !=), >, <, >=, <=. Logical operators AND, OR, and NOT can combine conditions. Special operators like BETWEEN, LIKE (with % and _ wildcards), and IN are also testable.
WHERE 子句基于条件过滤行。比较运算符包括 =、<>（或 !=）、>、<、>=、<=。逻辑运算符 AND、OR 和 NOT 可以组合条件。特殊的运算符如 BETWEEN、LIKE（配合通配符 % 和 _）以及 IN 也在考试范围内。
Examples:
示例：
SELECT * FROM Student WHERE YearGroup = 12 AND LastName LIKE 'S%';
SELECT Name, Price FROM Product WHERE Price BETWEEN 10 AND 20;
SELECT * FROM Student WHERE YearGroup IN (11, 12);
To sort the result, use ORDER BY. By default, sorting is ascending (ASC); add DESC for descending order. Multiple columns can be specified.
要对结果排序，使用 ORDER BY。默认按升序 (ASC) 排序；添加 DESC 表示降序。可以指定多个列。
SELECT FirstName, LastName, DateOfBirth FROM Student ORDER BY YearGroup ASC, LastName DESC;
This sorts first by YearGroup ascending, then by LastName descending for same YearGroup values. In exams, be careful to place ORDER BY at the very end of the statement.
这首先按年级升序排序，然后同年级内按姓氏降序排序。在考试中，注意将 ORDER BY 放在语句的最末尾。
6. Aggregation Functions and GROUP BY | 聚合函数与 GROUP BY 分组
Aggregate functions perform calculations on a set of rows and return a single value. The five main functions are COUNT, SUM, AVG, MAX, and MIN. They are often used in conjunction with GROUP BY, which groups rows that have the same values in specified columns.
聚合函数对一组行执行计算并返回单个值。五个主要函数是 COUNT、SUM、AVG、MAX 和 MIN。它们通常与 GROUP BY 一起使用，后者将指定列值相同的行分为一组。
A critical rule for CIE exams: every column in the SELECT list that is not an aggregate function must appear in the GROUP BY clause. Otherwise, the statement is invalid.
CIE 考试的一个重要规则：SELECT 列表中不是聚合函数的每一列都必须出现在 GROUP BY 子句中。否则，语句无效。
Example – count students per year group:
示例 – 统计每个年级的学生人数：
SELECT YearGroup, COUNT(StudentID) AS NumStudents FROM Student GROUP BY YearGroup;
You can also aggregate multiple columns. For instance, to get the total and average order value per customer:
你还可以聚合多个列。例如，获取每个客户的订单总金额和平均金额：
SELECT CustID, SUM(Amount) AS TotalSpent, AVG(Amount) AS AvgOrder FROM Order GROUP BY CustID;
7. Filtering Groups with HAVING | 使用 HAVING 过滤分组
The HAVING clause filters groups created by GROUP BY, similar to how WHERE filters individual rows. The key difference is that HAVING can include aggregate functions, whereas WHERE cannot. HAVING is placed after GROUP BY.
HAVING 子句过滤由 GROUP BY 创建的分组，类似于 WHERE 过滤单个行。关键区别在于 HAVING 可以包含聚合函数，而 WHERE 不行。HAVING 放在 GROUP BY 之后。
Example – list only those year groups that have more than 15 students:
示例 – 只列出学生人数超过 15 人的年级：
SELECT YearGroup, COUNT(*) AS StudentCount FROM Student GROUP BY YearGroup HAVING COUNT(*) > 15;
You can also combine WHERE and HAVING. In such a case, WHERE filters rows before grouping, and HAVING filters groups after aggregation. For example, get the number of large orders (amount > 100) per customer, only showing those with more than 2 large orders:
你还可以结合 WHERE 和 HAVING。在这种情况下，WHERE 在分组前过滤行，HAVING 在聚合后过滤分组。例如，获取每位客户的大额订单（金额 > 100）数量，并只显示大额订单超过 2 个的客户：
SELECT CustID, COUNT(OrderID) FROM Order WHERE Amount > 100 GROUP BY CustID HAVING COUNT(OrderID) > 2;
8. Joining Tables | 连接表
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. The ON keyword specifies the join condition.
连接基于相关列合并两个或多个表的行。最常见的是 INNER JOIN，它只返回两个表中匹配的行。ON 关键字指定连接条件。
Example – list students along with their order details:
示例 – 列出学生及其订单详情：
SELECT Student.FirstName, Student.LastName, Order.OrderDate
FROM Student
INNER JOIN Order ON Student.StudentID = Order.StudentID;
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right; unmatched right columns show NULL. This is useful to find records that have no corresponding entry, such as students who have not placed any orders.
LEFT JOIN（或 LEFT OUTER JOIN）返回左表的所有行以及右表匹配的行；未匹配的右表列显示为 NULL。这对于查找没有对应条目的记录非常有用，例如从未下过订单的学生。
SELECT Student.FirstName, Order.OrderID
FROM Student
LEFT JOIN Order ON Student.StudentID = Order.StudentID;
CIE examinations frequently ask you to choose the correct join type to fulfill a requirement, such as 'show all students and any orders they may have'. Be familiar with both INNER and LEFT joins.
CIE 考试经常要求你选择合适的连接类型来实现需求，如“显示所有学生及其可能有的订单”。请熟悉 INNER 和 LEFT 这两种连接。
9. Subqueries and Nested SELECT | 子查询与嵌套 SELECT
A subquery is a SELECT statement embedded inside another SQL statement. It is often used in a WHERE clause with operators like IN, EXISTS, or with comparison operators when the subquery returns a single value. Subqueries must be enclosed in parentheses.
子查询是嵌入在另一个 SQL 语句中的 SELECT 语句。它常用于 WHERE 子句中，搭配 IN、EXISTS 等操作符，或者当子查询返回单个值时与比较运算符一起使用。子查询必须用括号括起来。
Example – find students who achieved the highest score in any test:
示例 – 找出在任何测试中获得最高分的学生：
SELECT FirstName, LastName FROM Student
WHERE StudentID IN (SELECT StudentID FROM Result WHERE Score = (SELECT MAX(Score) FROM Result));
Alternatively, you can use a correlated subquery that references columns from the outer query. For instance, find students whose overall average score is higher than the school average:
另外，你可以使用关联子查询，它引用外部查询的列。例如，找出总平均分高于全校平均分的学生：
SELECT StudentID, FirstName FROM Student s
WHERE (SELECT AVG(Score) FROM Result r WHERE r.StudentID = s.StudentID) > (SELECT AVG(Score) FROM Result);
While powerful, subqueries can often be rewritten as joins; exam questions may specify the required approach, so read carefully.
子查询虽然强大，但常常可以重写为连接；考试题目可能会指定要求使用的方法，所以请仔细阅读。
10. Modifying Data – INSERT, UPDATE, DELETE | 修改数据 – 插入、更新、删除
To add new rows, use INSERT INTO. You can specify the column names (recommended) or rely on their order. The VALUES clause provides the data, with string and date values in single quotes.
要添加新行，使用 INSERT INTO。你可以指定列名（推荐），也可以依赖列的顺序。VALUES 子句提供数据，字符串和日期值使用单引号。
Example:
示例：
INSERT INTO Student (StudentID, FirstName, LastName, YearGroup) VALUES (101, 'John', 'Doe', 12);
The UPDATE statement modifies existing rows. It is critical to include a WHERE clause; otherwise, all rows will be updated. Use SET to assign new values.
UPDATE 语句修改现有行。包含 WHERE 子句至关重要，否则所有行都会被更新。使用 SET 来分配新值。
UPDATE Student SET YearGroup = 13 WHERE StudentID = 101;
Similarly, DELETE FROM removes rows, and you must provide a WHERE condition. Omitting WHERE deletes all rows in the table.
类似地，DELETE FROM 删除行，你必须提供 WHERE 条件。省略 WHERE 会删除表中的所有行。
DELETE FROM Student WHERE YearGroup = 11;
In practical exams, ensure that your statements do not accidentally corrupt the sample dataset; always test with a SELECT first to check which rows will be affected.
在实践考试中，请确保你的语句不会意外损坏示例数据集；务必先用 SELECT 测试，以检查哪些行会受影响。
11. Views for Simplification and Security | 视图简化与安全
A view is a virtual table based on the result of a SELECT query. It does not store data physically but presents a predefined query. Views simplify complex queries by encapsulating joins and aggregations, and they can restrict access to sensitive columns by exposing only selected data.
视图
Published by TutorHao | A-Level Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导