📚 SQL Mastery for A-Level Edexcel Computer Science | A-Level Edexcel 计算机：SQL 考点精讲
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. In the A-Level Edexcel Computer Science specification, you are expected to demonstrate the ability to write efficient SQL statements for data definition, data manipulation, and data querying. This revision guide systematically covers every key SQL concept you need, from creating tables to complex joins and subqueries.
SQL（结构化查询语言）是管理和操作关系数据库的标准语言。在A-Level Edexcel计算机科学大纲中，你需要展示编写高效SQL语句以进行数据定义、数据操作和数据查询的能力。这份复习指南系统地涵盖了你需要掌握的每一个关键SQL概念，从创建表到复杂的连接与子查询。
1. Introduction to SQL and Its Role | SQL 简介及其作用
SQL enables users to interact with database systems like MySQL, PostgreSQL, or SQLite. In the Edexcel syllabus, you need to know how SQL is used in the back-end of applications and the distinction between DDL, DML, and DQL (Data Query Language).
SQL 使用户能够与 MySQL、PostgreSQL 或 SQLite 等数据库系统交互。在 Edexcel 教学大纲中，你需要了解 SQL 如何在应用程序后端使用，以及 DDL、DML 和 DQL（数据查询语言）之间的区别。
DDL commands define the structure of the database (CREATE, ALTER, DROP). DML commands manipulate the data within the tables (INSERT, UPDATE, DELETE). The SELECT statement forms the core of querying data.
DDL 命令定义数据库的结构（CREATE、ALTER、DROP）。DML 命令操作表中的数据（INSERT、UPDATE、DELETE）。SELECT 语句构成查询数据的核心。
2. Data Definition Language (DDL): CREATE, ALTER, DROP | 数据定义语言: CREATE、ALTER、DROP
The CREATE TABLE statement defines a new table with columns, data types, and constraints. For example: CREATE TABLE Student (ID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE); Data types commonly tested include INT, VARCHAR, DATE, and BOOLEAN.
CREATE TABLE 语句定义一个新表，包含列、数据类型和约束。例如：CREATE TABLE Student (ID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE); 常考的数据类型包括 INT、VARCHAR、DATE 和 BOOLEAN。
You may need to add a new column using ALTER TABLE: ALTER TABLE Student ADD Email VARCHAR(100); Dropping a table removes the entire structure: DROP TABLE Student;.
你可能需要使用 ALTER TABLE 添加新列：ALTER TABLE Student ADD Email VARCHAR(100); 删除表会移除整个结构：DROP TABLE Student;。
Specifying constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL is essential for enforcing referential integrity. The FOREIGN KEY constraint links tables, for instance: FOREIGN KEY (CourseID) REFERENCES Course(ID).
指定 PRIMARY KEY、FOREIGN KEY、UNIQUE 和 NOT NULL 等约束对于强制引用完整性至关重要。FOREIGN KEY 约束连接表，例如：FOREIGN KEY (CourseID) REFERENCES Course(ID)。
3. Data Manipulation Language (DML): INSERT, UPDATE, DELETE | 数据操作语言: INSERT、UPDATE、DELETE
INSERT adds new rows: INSERT INTO Student (ID, Name, DOB) VALUES (1, 'Alice', '2005-06-15'); You must match the number of values with the listed columns.
INSERT 添加新行：INSERT INTO Student (ID, Name, DOB) VALUES (1, 'Alice', '2005-06-15'); 你必须确保值的数量与列出的列数一致。
UPDATE modifies existing records: UPDATE Student SET Name = 'Alicia' WHERE ID = 1; Without a WHERE clause, all rows would be updated, which is a common mistake.
UPDATE 修改现有记录：UPDATE Student SET Name = 'Alicia' WHERE ID = 1; 如果缺少 WHERE 子句，所有行都会被更新，这是一个常见错误。
DELETE removes rows: DELETE FROM Student WHERE ID = 1; Similar to UPDATE, omitting the WHERE clause clears all entries from the table.
DELETE 删除行：DELETE FROM Student WHERE ID = 1; 与 UPDATE 类似，忽略 WHERE 子句会清空表中所有条目。
4. The SELECT Statement: Retrieving Data | SELECT 语句：检索数据
The SELECT statement fetches data from one or more tables. Its simplest form: SELECT * FROM Student; retrieves all columns. To get only specific columns, use SELECT Name, DOB FROM Student;.
SELECT 语句从一个或多个表中抓取数据。其最简单形式：SELECT * FROM Student; 检索所有列。若要仅获取特定列，使用 SELECT Name, DOB FROM Student;。
The SELECT clause can contain arithmetic expressions and aliases. For example, SELECT Name, 2025 - year(DOB) AS Age FROM Student; creates a calculated column with an alias.
SELECT 子句可以包含算术表达式和别名。例如，SELECT Name, 2025 - year(DOB) AS Age FROM Student; 创建一个带有别名的计算列。
DISTINCT eliminates duplicate rows: SELECT DISTINCT City FROM Student;. This is often used to find unique categorical values.
DISTINCT 消除重复行：SELECT DISTINCT City FROM Student;。这常用于查找唯一的分类值。
5. Filtering Data with WHERE Clause | 使用 WHERE 子句过滤数据
The WHERE clause filters rows based on conditions. Comparison operators include =, <, >, <=, >=, and <> (not equal). SELECT * FROM Student WHERE Grade > 80;
WHERE 子句根据条件筛选行。比较运算符包括 =、<、>、<=、>= 和 <>（不等于）。SELECT * FROM Student WHERE Grade > 80;
Logical operators AND, OR, and NOT combine conditions. For instance, SELECT * FROM Student WHERE Grade > 80 AND City = 'London'; Parentheses can control evaluation order.
逻辑运算符 AND、OR 和 NOT 组合条件。例如，SELECT * FROM Student WHERE Grade > 80 AND City = 'London'; 圆括号可以控制求值顺序。
BETWEEN selects a range: WHERE Grade BETWEEN 60 AND 80. The IN operator checks for membership in a list: WHERE City IN ('London','Paris'). LIKE allows pattern matching with ‘%’ (any number of characters) and ‘_’ (single character).
BETWEEN 选取一个范围：WHERE Grade BETWEEN 60 AND 80。IN 运算符检查是否在一个列表中：WHERE City IN ('London','Paris')。LIKE 允许使用 ‘%’（任意数量字符）和 ‘_’（单个字符）进行模式匹配。
NULL testing uses IS NULL or IS NOT NULL, never ‘= NULL’. Example: WHERE Email IS NULL.
NULL 测试使用 IS NULL 或 IS NOT NULL，绝不能用 ‘= NULL’。例如：WHERE Email IS NULL。
6. Sorting Results with ORDER BY | 使用 ORDER BY 排序结果
ORDER BY sorts the result set. By default, sorting is ascending (ASC). SELECT Name, Grade FROM Student ORDER BY Grade DESC; descending order shows highest grades first.
ORDER BY 对结果集进行排序。默认按升序（ASC）排列。SELECT Name, Grade FROM Student ORDER BY Grade DESC; 降序排列首先显示最高成绩。
Multiple columns can be specified: ORDER BY City ASC, Grade DESC sorts by City alphabetically, then by Grade descending within each city.
可以指定多个列：ORDER BY City ASC, Grade DESC 先按城市字母顺序排序，然后在每个城市内按成绩降序排列。
ORDER BY can be used with calculated columns or column aliases, but remember that WHERE clause filters rows before sorting occurs.
ORDER BY 可以与计算列或列别名一起使用，但要记住 WHERE 子句在排序之前过滤行。
7. Aggregate Functions: COUNT, SUM, AVG, MAX, MIN | 聚合函数：COUNT、SUM、AVG、MAX、MIN
Aggregate functions perform a calculation on a set of values and return a single value. COUNT(*) returns the number of rows. SELECT COUNT(*) FROM Student; gives total students.
聚合函数对一组值执行计算并返回单个值。COUNT(*) 返回行数。SELECT COUNT(*) FROM Student; 给出学生总数。
COUNT(column) counts non-null values of a specific column. SUM and AVG are used with numeric columns: SELECT AVG(Grade) FROM Student;. MAX and MIN retrieve extreme values.
COUNT(column) 只统计某列的非空值。SUM 和 AVG 与数值列一起使用：SELECT AVG(Grade) FROM Student;。MAX 和 MIN 检索极值。
You can use DISTINCT inside an aggregate: SELECT COUNT(DISTINCT City) FROM Student; returns the number of distinct cities. All aggregate functions except COUNT(*) ignore NULLs in the column.
你可以在聚合内使用 DISTINCT：SELECT COUNT(DISTINCT City) FROM Student; 返回不同城市的数量。除 COUNT(*) 外，所有聚合函数忽略列中的 NULL 值。
8. Grouping Data with GROUP BY and HAVING | 使用 GROUP BY 和 HAVING 分组数据
GROUP BY groups rows that have the same values in specified columns, often used with aggregates. SELECT City, AVG(Grade) FROM Student GROUP BY City; computes average grade per city.
GROUP BY 将指定列中具有相同值的行分组，常与聚合一起使用。SELECT City, AVG(Grade) FROM Student GROUP BY City; 计算每个城市的平均成绩。
Any column in the SELECT that is not part of an aggregate must be included in the GROUP BY clause. The HAVING clause filters groups after aggregation, similar to WHERE but applied to the grouped result.
SELECT 中任何未包含在聚合中的列都必须出现在 GROUP BY 子句中。HAVING 子句在聚合后过滤分组，与 WHERE 类似但应用于分组结果。
Example: SELECT City, COUNT(*) AS StudentCount FROM Student GROUP BY City HAVING COUNT(*) > 5; shows cities with more than 5 students. WHERE filters before grouping; HAVING filters after grouping.
示例：SELECT City, COUNT(*) AS StudentCount FROM Student GROUP BY City HAVING COUNT(*) > 5; 显示学生人数超过5人的城市。WHERE 在分组前过滤；HAVING 在分组后过滤。
9. Joining Tables: INNER JOIN and Other Joins | 连接表：INNER JOIN 及其他连接
When data is spread across multiple normalized 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 with matching values in both tables.
当数据分布在多个规范化的表中时，JOIN 基于相关列将两个或多个表的行组合起来。最常见的是 INNER JOIN，它只返回两个表中匹配的行。
Syntax: SELECT Student.Name, Course.Title FROM Student INNER JOIN Enrollment ON Student.ID = Enrollment.StudentID; The ON clause specifies the join condition.
语法：SELECT Student.Name, Course.Title FROM Student INNER JOIN Enrollment ON Student.ID = Enrollment.StudentID; ON 子句指定连接条件。
LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matched rows from the right table. Unmatched right columns show NULL. RIGHT JOIN is the reverse. Edexcel expects you to understand these variations.
LEFT JOIN（或 LEFT OUTER JOIN）返回左表的所有行以及右表的匹配行。未匹配的右表列显示 NULL。RIGHT JOIN 与之相反。Edexcel 希望你理解这些变体。
Using table aliases simplifies queries: SELECT s.Name, e.CourseID FROM Student s JOIN Enrollment e ON s.ID = e.StudentID;
使用表别名可以简化查询：SELECT s.Name, e.CourseID FROM Student s JOIN Enrollment e ON s.ID = e.StudentID;
10. Subqueries and Nested Queries | 子查询与嵌套查询
A subquery is a SELECT statement embedded inside another SQL statement. It can be placed in the WHERE, FROM, or SELECT clause. For example, finding students with above-average grades: SELECT Name FROM Student WHERE Grade > (SELECT AVG(Grade) FROM Student);
子查询是嵌入在另一 SQL 语句中的 SELECT 语句。它可以放在 WHERE、FROM 或 SELECT 子句中。例如，查找成绩高于平均分的学生：SELECT Name FROM Student WHERE Grade > (SELECT AVG(Grade) FROM Student);
Correlated subqueries reference columns from the outer query. These are re-evaluated for each row of the outer query. Example: SELECT Name FROM Student s WHERE Grade > (SELECT AVG(Grade) FROM Student WHERE City = s.City);
相关子查询引用外部查询中的列。这些子查询会针对外部查询的每一行重新求值。示例：SELECT Name FROM Student s WHERE Grade > (SELECT AVG(Grade) FROM Student WHERE City = s.City);
Subqueries can return single values, a list of values (used with IN), or a table (used with EXISTS or in the FROM clause). Always ensure that a subquery used with comparison operators returns exactly one row and one column.
子查询可以返回单个值、值列表（与 IN 配合使用）或一个表（与 EXISTS 配合使用或放在 FROM 子句中使用）。务必确保与比较运算符一起使用的子查询只返回一行一列。
11. Creating Views and Indexes | 创建视图和索引
A view is a virtual table based on the result set of a SELECT statement. It does not store data physically but presents a saved query. CREATE VIEW HighAchievers AS SELECT Name, Grade FROM Student WHERE Grade > 90; simplifies complex queries and enhances security.
视图是基于 SELECT 语句结果集的虚拟表。它不物理存储数据，而是呈现一个已保存的查询。CREATE VIEW HighAchievers AS SELECT Name, Grade FROM Student WHERE Grade > 90; 可以简化复杂查询并增强安全性。
Views can be queried like tables: SELECT * FROM HighAchievers;. Some views are updatable if they meet certain criteria (e.g., no aggregation, no joins from multiple tables).
视图可以像表一样被查询：SELECT * FROM HighAchievers;。如果满足特定条件（如无聚合、非多表连接），某些视图是可更新的。
An index improves the speed of data retrieval operations on a table at the cost of slower writes. CREATE INDEX idx_lastname ON Student (LastName); increases performance of searches and joins on that column.
索引以降低写入速度为代价，提高表上数据检索操作的速度。CREATE INDEX idx_lastname ON Student (LastName); 提高了对该列的搜索和连接性能。
In your exam, you should know when to propose an index (frequently searched columns, primary keys are automatically indexed). The DROP INDEX statement removes an index.
在考试中，你应知道何时建议使用索引（频繁搜索的列，主键会自动建索引）。DROP INDEX 语句用于删除索引。
12. Referential Integrity and Foreign Keys in Practice | 参照完整性与外键的实战
Referential integrity ensures that relationships between tables remain consistent. The FOREIGN KEY constraint enforces that a value in the child table must match a primary key value in the parent table or be NULL.
参照完整性确保表之间的关系保持一致。FOREIGN KEY 约束强制要求子表中的值必须匹配父表中的主键值或为 NULL。
Example: CREATE TABLE Enrollment (EnrollID INT PRIMARY KEY, StudentID INT, CourseID INT, FOREIGN KEY (StudentID) REFERENCES Student(ID), FOREIGN KEY (CourseID) REFERENCES Course(ID)); This prevents orphan records and maintains data accuracy.
示例：CREATE TABLE Enrollment (EnrollID INT PRIMARY KEY, StudentID INT, CourseID INT, FOREIGN KEY (StudentID) REFERENCES Student(ID), FOREIGN KEY (CourseID) REFERENCES Course(ID)); 这可以防止孤记录并保持数据准确性。
When deleting or updating referenced rows, ON DELETE CASCADE or ON UPDATE CASCADE can be specified to automatically propagate changes to the child table, maintaining integrity.
删除或更新被引用行时，可以指定 ON DELETE CASCADE 或 ON UPDATE CASCADE 以自动将更改传播到子表，从而保持完整性。
Understanding these constraints is essential both for writing correct SQL and for answering database design questions, as they directly relate to data consistency and the avoidance of anomalies.
理解这些约束对于编写正确的 SQL 和回答数据库设计问题都至关重要，因为它们直接关系到数据一致性和避免异常。
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课程辅导，国外大学本科硕士研究生博士课程论文辅导