A-Level Computer Science: SQL Essentials Masterclass | A-Level计算机：SQL考点精讲

📚 A-Level Computer Science: SQL Essentials Masterclass | A-Level计算机：SQL考点精讲

Structured Query Language (SQL) is the backbone of relational database management systems, and a core topic in A-Level Computer Science. This article covers all the key SQL concepts you need to master, from basic data definition and manipulation to complex queries, joins, subqueries, normalisation, and transactions. Each section provides clear explanations and practical examples to help you excel in your exam.

结构化查询语言（SQL）是关系型数据库管理系统的基础，也是A-Level计算机科学的核心考点。本文涵盖了你需要掌握的所有关键SQL概念，从基本的数据定义与操纵，到复杂查询、表连接、子查询、规范化以及事务处理。每部分都提供了清晰的解释和实用的示例，帮助你从容应对考试。

1. Introduction to Databases and SQL | 数据库与SQL简介

A relational database stores data in tables, where each table consists of rows (records) and columns (fields). Tables are linked through keys, ensuring data integrity and minimising redundancy. SQL is the standard language used to interact with these databases.

关系型数据库将数据存储在表中，每个表由行（记录）和列（字段）构成。表之间通过键进行关联，从而保证数据完整性并减少冗余。SQL正是用于操作这类数据库的标准语言。

A primary key uniquely identifies each row in a table and cannot contain NULL values. A foreign key is a column in one table that references the primary key of another table, establishing a relationship between the two tables.

主键唯一标识表中的每一行，不能包含空值（NULL）。外键是某个表中的一列，它引用了另一张表的主键，从而在两张表之间建立起联系。

Key SQL commands are divided into several categories: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL). For A-Level, DDL and DML are the most important.

主要的SQL命令分为几类：数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）和事务控制语言（TCL）。对于A-Level考试而言，DDL和DML最为关键。

2. Data Definition Language (DDL) | 数据定义语言

DDL statements are used to define and modify the structure of database objects such as tables. The most essential ones are CREATE, ALTER, and DROP.

DDL语句用于定义和修改数据库对象（如表）的结构。最核心的命令包括CREATE、ALTER和DROP。

CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50), DateOfBirth DATE );

Note how we specify data types (INT, VARCHAR, DATE), constraints (PRIMARY KEY, NOT NULL), and the column names. ALTER TABLE can add or drop columns, or modify existing ones: ALTER TABLE Students ADD Email VARCHAR(100); DROP TABLE Students; permanently removes the table and all its data.

注意，在这里我们指定了数据类型（INT、VARCHAR、DATE）、约束（PRIMARY KEY、NOT NULL）以及列名。ALTER TABLE可以增加或删除列，或是修改已有列的定义：ALTER TABLE Students ADD Email VARCHAR(100); DROP TABLE Students;则会永久删除该表及其所有数据。

3. Data Manipulation Language (DML) | 数据操纵语言

DML focuses on the data itself. The three fundamental operations are INSERT, UPDATE, and DELETE.

DML的核心是操作数据本身。三个基本操作分别是INSERT、UPDATE和DELETE。

INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth) VALUES (1, 'Alice', 'Smith', '2005-04-12');

To change existing data: UPDATE Students SET LastName = 'Johnson' WHERE StudentID = 1; Always include a WHERE clause to target specific rows, otherwise all rows will be updated.

若要修改已有数据：UPDATE Students SET LastName = 'Johnson' WHERE StudentID = 1; 务必加上WHERE子句以锁定目标行，否则所有行都会被修改。

To delete rows: DELETE FROM Students WHERE StudentID = 1; Omitting the WHERE clause deletes every row from the table, so use it with caution.

删除行则使用：DELETE FROM Students WHERE StudentID = 1; 如果省略WHERE子句，表中所有行都会被删除，务必谨慎使用。

4. SELECT Queries and Filtering | SELECT查询与筛选

The SELECT statement retrieves data. The simplest form is SELECT * FROM Students; which returns all columns. You can list specific columns: SELECT FirstName, DateOfBirth FROM Students;

SELECT语句用于检索数据。最简单的形式是SELECT * FROM Students;，返回所有列。你也可以指定特定列：SELECT FirstName, DateOfBirth FROM Students;

The WHERE clause filters rows based on conditions. Operators include =, <> (or !=), >, <, >=, <=. Combine conditions with AND, OR, NOT.

WHERE子句根据条件筛选行。支持的运算符有=、<>（或!=）、>、<、>=、<=。可用AND、OR和NOT组合多个条件。

SELECT * FROM Students WHERE LastName = 'Smith' AND DateOfBirth > '2005-01-01';

Other useful operators: BETWEEN for ranges, IN for a list of values, LIKE for pattern matching (with % for any sequence of characters, _ for a single character). Example: SELECT * FROM Students WHERE FirstName LIKE 'A%';

其他常用运算符：BETWEEN用于范围筛选，IN用于匹配多个值，LIKE用于模式匹配（%代表任意多个字符，_代表单个字符）。例如：SELECT * FROM Students WHERE FirstName LIKE 'A%';

5. Sorting and Aggregation Functions | 排序与聚合函数

ORDER BY sorts the result set. Use ASC (ascending, default) or DESC (descending). SELECT * FROM Students ORDER BY LastName ASC, FirstName DESC;

ORDER BY对结果集进行排序。使用ASC（升序，默认）或DESC（降序）。SELECT * FROM Students ORDER BY LastName ASC, FirstName DESC;

Aggregate functions perform calculations on a set of values and return a single value. The most common ones are COUNT, SUM, AVG, MAX, and MIN. For example, SELECT COUNT(*) FROM Students; returns the total number of rows.

聚合函数对一组值进行计算并返回单个值。最常见的聚合函数包括COUNT、SUM、AVG、MAX和MIN。例如，SELECT COUNT(*) FROM Students;返回表中行的总数。

SELECT AVG(Mark) FROM Results WHERE Subject = 'Computer Science';

Note that COUNT(column) ignores NULLs, whereas COUNT(*) counts all rows. Aggregates are often used with DISTINCT: SELECT COUNT(DISTINCT Country) FROM Customers;

注意，COUNT(column)会忽略NULL值，而COUNT(*)则计算所有行。聚合函数常与DISTINCT搭配使用：SELECT COUNT(DISTINCT Country) FROM Customers;

6. Grouping Data with GROUP BY and HAVING | 使用GROUP BY和HAVING分组数据

GROUP BY splits the result set into groups based on one or more columns, so that aggregates can be calculated per group. For example, to find the number of students in each country: SELECT Country, COUNT(*) FROM Students GROUP BY Country;

GROUP BY根据一列或多列将结果集划分为若干组，以便按组计算聚合值。例如，查询每个国家的学生人数：SELECT Country, COUNT(*) FROM Students GROUP BY Country;

The HAVING clause filters groups, just as WHERE filters rows. HAVING is used after GROUP BY and typically operates on aggregate results. SELECT Country, COUNT(*) AS StudentCount FROM Students GROUP BY Country HAVING COUNT(*) > 5;

HAVING子句用于筛选分组，就像WHERE筛选行一样。HAVING用在GROUP BY之后，通常针对聚合结果进行过滤。SELECT Country, COUNT(*) AS StudentCount FROM Students GROUP BY Country HAVING COUNT(*) > 5;

Remember the processing order: WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. You cannot use column aliases in WHERE, but you can use them in ORDER BY.

请牢记SQL的执行顺序：WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY。不能在WHERE中使用列的别名，但可以在ORDER BY中使用。

7. Table Joins | 表连接

Joins combine rows from two or more tables based on a related column. The most common type is the INNER JOIN, which returns only rows that have matching values in both tables.

连接（Join）根据相关列将两个或多个表中的行组合起来。最常见的类型是INNER JOIN，它只返回在两张表中都有匹配值的行。

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

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table, together with matching rows from the right table. If there is no match, NULLs appear for the right table's columns. RIGHT JOIN works analogously. FULL OUTER JOIN combines both left and right outer joins, but not all database systems support it directly.

LEFT JOIN（或LEFT OUTER JOIN）返回左表的所有行，以及右表中匹配的行；若无匹配，则右表对应的列显示NULL。RIGHT JOIN同理。FULL OUTER JOIN则结合了左外连接和右外连接的效果，但并非所有数据库系统都直接支持。

Join Type	Result
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from left table, matched rows from right
RIGHT JOIN	All rows from right table, matched rows from left
FULL OUTER JOIN	All rows from both tables, with NULLs where no match

8. Subqueries and Nested Queries | 子查询与嵌套查询

A subquery is a query nested inside another SELECT, INSERT, UPDATE, or DELETE statement. It can be used in the WHERE clause, FROM clause, or SELECT clause.

子查询是嵌套在另一个SELECT、INSERT、UPDATE或DELETE语句内的查询。它可以出现在WHERE子句、FROM子句或SELECT子句中。

SELECT FirstName FROM Students WHERE StudentID IN (SELECT StudentID FROM Enrolments WHERE Course = 'Computer Science');

Correlated subqueries reference columns from the outer query: SELECT FirstName FROM Students s WHERE EXISTS (SELECT 1 FROM Enrolments e WHERE e.StudentID = s.StudentID AND e.Course = 'Computer Science'); Such subqueries are executed once per outer row and can be less efficient.

关联子查询会引用外部查询的列：SELECT FirstName FROM Students s WHERE EXISTS (SELECT 1 FROM Enrolments e WHERE e.StudentID = s.StudentID AND e.Course = 'Computer Science'); 这种子查询针对外部查询的每一行执行一次，效率可能较低。

Subqueries can also return scalar values for comparison: SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

子查询还可以返回标量值用于比较：SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

9. Database Normalisation | 数据库规范化

Normalisation is the process of organising data to minimise redundancy and avoid update anomalies. For A-Level, you need to understand the first three normal forms (1NF, 2NF, 3NF).

规范化是组织数据以减少冗余并避免更新异常的过程。在A-Level中，你需要掌握前三种范式（1NF、2NF、3NF）。

1NF: A table is in First Normal Form if all attributes contain atomic (indivisible) values and there are no repeating groups. Each intersection of row and column must hold a single value.

第一范式（1NF）：如果所有属性都包含原子值（不可再分），且不存在重复组，则该表满足第一范式。每一行与每一列的交点必须只容纳一个值。

2NF: A table is in Second Normal Form if it is in 1NF and every non-key attribute is fully functionally dependent on the entire primary key. Partial dependencies must be removed; this applies mainly to tables with composite primary keys.

第二范式（2NF）：在满足1NF的基础上，如果每个非键属性都完全函数依赖于整个主键，则该表满足第二范式。必须消除部分依赖；这主要适用于具有复合主键的表。

3NF: A table is in Third Normal Form if it is in 2NF and no non-key attribute is transitively dependent on the primary key. In other words, non-key columns should depend only on the primary key and not on other non-key columns.

第三范式（3NF）：在满足2NF的基础上，如果没有非键属性传递依赖于主键，则该表满足第三范式。换言之，非键列只应依赖于主键，而不能依赖于其他非键列。

An example of 2NF violation: a table OrderDetails(OrderID, ProductID, ProductName, Quantity) where ProductName depends only on ProductID, part of the composite key, creating a partial dependency. To fix, split into separate tables.

一个违反2NF的例子：表OrderDetails(OrderID, ProductID, ProductName, Quantity)中，ProductName仅依赖于复合主键的一部分ProductID，形成部分依赖。解决办法是拆分成独立的表。

10. Indexes and Transactions | 索引与事务

An index is a database structure that improves the speed of data retrieval operations on a table. It can be created on one or more columns. However, indexes also slow down INSERT, UPDATE, and DELETE because the index must be updated.

索引是一种数据库结构，用于加快表上数据检索操作的速度。它可以在一个或多个列上创建。然而，索引也会降低INSERT、UPDATE和DELETE的性能，因为索引本身也需要更新。

CREATE INDEX idx_lastname ON Students (LastName); This creates an index on the LastName column, making WHERE searches on that column much faster. DROP INDEX idx_lastname; removes it.

CREATE INDEX idx_lastname ON Students (LastName); 这会在LastName列上创建索引，从而极大加快基于该列的WHERE搜索。DROP INDEX idx_lastname;则将其删除。

A transaction is a sequence of SQL statements that are treated as a single logical unit. Transactions follow the ACID properties: Atomicity (all or nothing), Consistency (database remains valid), Isolation (concurrent transactions do not interfere), and Durability (committed changes are permanent).

事务是被视为一个逻辑单元的一系列SQL语句。事务遵循ACID特性：原子性（全有或全无）、一致性（数据库保持有效状态）、隔离性（并发事务互不干扰）和持久性（已提交的更改永久保存）。

BEGIN TRANSACTION; UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1; UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2; COMMIT; If something goes wrong, ROLLBACK; undoes any changes made since the BEGIN.

BEGIN TRANSACTION; UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1; UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2; COMMIT; 如果中途出错，可使用ROLLBACK;撤销自BEGIN以来的所有更改。

Understanding transactions and ACID is essential for maintaining data integrity in multi-user environments, a concept frequently examined in A-Level papers.

理解事务和ACID是维持多用户环境中数据完整性的关键，这一概念在A-Level试卷中常作为考点出现。

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

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

A-Level Computer Science: SQL Essentials Masterclass | A-Level计算机：SQL考点精讲