IGCSE AQA Computer Science: SQL Essentials | IGCSE AQA 计算机：SQL 考点精讲

📚 IGCSE AQA Computer Science: SQL Essentials | IGCSE AQA 计算机：SQL 考点精讲

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. In the IGCSE AQA Computer Science syllabus, understanding SQL commands is essential for both theory and practical tasks. This article breaks down every key SQL concept you need to master, from creating tables to querying data, ensuring you are fully prepared for exam questions.

结构化查询语言（SQL）是管理和操作关系数据库的标准语言。在 IGCSE AQA 计算机科学大纲中，理解 SQL 命令对于理论和实践任务都至关重要。本文逐一剖析你需要掌握的每个关键 SQL 概念，从创建表到查询数据，确保你为考试问题做好充分准备。

1. What is a Relational Database? | 什么是关系数据库？

A relational database stores data in tables made up of rows (records) and columns (fields). Each table represents an entity, and relationships between tables are established using primary and foreign keys. SQL is used to interact with this structured data.

关系数据库将数据存储在由行（记录）和列（字段）组成的表中。每个表代表一个实体，表之间的关系通过主键和外键建立。SQL 用于与这种结构化数据进行交互。

2. Creating a Table with CREATE TABLE | 使用 CREATE TABLE 创建表

The CREATE TABLE statement defines a new table structure, specifying column names, data types, and constraints. For example:

CREATE TABLE 语句定义一个新的表结构，指定列名、数据类型和约束。例如：

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

Common data types include INT for whole numbers, VARCHAR(n) for variable-length text, DATE for dates, and BOOLEAN for true/false values. The PRIMARY KEY constraint ensures each record is unique and not null.

常见的数据类型包括用于整数的 INT、用于可变长度文本的 VARCHAR(n)、用于日期的 DATE 以及用于真/假值的 BOOLEAN。PRIMARY KEY 约束确保每条记录唯一且非空。

You can also add NOT NULL, UNIQUE, and FOREIGN KEY constraints directly in the column definition.

你还可以在列定义中直接添加 NOT NULL（非空）、UNIQUE（唯一）和 FOREIGN KEY（外键）约束。

3. Modifying a Table with ALTER TABLE | 使用 ALTER TABLE 修改表结构

ALTER TABLE allows you to add, remove, or modify columns after a table has been created. To add a new column:

ALTER TABLE 允许你在表创建后添加、删除或修改列。要添加新列：

ALTER TABLE Student ADD Email VARCHAR(100);

To delete a column (if supported by the DBMS):

要删除一列（如果数据库管理系统支持）：

ALTER TABLE Student DROP COLUMN Email;

Changing a column’s data type can be done with the MODIFY or ALTER COLUMN command, depending on the specific SQL dialect.

更改列的数据类型可以使用 MODIFY 或 ALTER COLUMN 命令，具体取决于特定的 SQL 方言。

4. Removing a Table with DROP TABLE | 使用 DROP TABLE 删除表

The DROP TABLE command permanently deletes a table and all data stored in it. This action cannot be undone, so it should be used with caution.

DROP TABLE 命令会永久删除一个表及其存储的所有数据。此操作无法撤消，因此应谨慎使用。

DROP TABLE Student;

In exam scenarios, you might be asked to remove a table that is no longer needed. Always ensure you refer to the correct table name.

在考试情境中，你可能被要求删除一个不再需要的表。务必确保引用正确的表名。

5. Inserting Data with INSERT INTO | 使用 INSERT INTO 插入数据

INSERT INTO adds new rows of data to a table. You can specify the columns you are filling, followed by the VALUES clause.

INSERT INTO 向表中添加新的数据行。你可以指定要填充的列，后跟 VALUES 子句。

INSERT INTO Student (StudentID, FirstName, LastName, DateOfBirth)
VALUES (1, ‘Alice’, ‘Smith’, ‘2008-05-14’);

If you are inserting values for every column in the same order as the table definition, you can omit the column list:

如果你按照表定义的顺序为每一列插入值，可以省略列列表：

INSERT INTO Student VALUES (2, ‘Bob’, ‘Jones’, ‘2007-11-23’);

6. Querying Data with SELECT and FROM | 使用 SELECT 和 FROM 查询数据

The SELECT statement retrieves specific columns from a table. The FROM clause indicates which table(s) to query. To retrieve all columns, use the asterisk wildcard *.

SELECT 语句从表中检索特定的列。FROM 子句指示要查询哪个（些）表。要检索所有列，请使用星号通配符 *。

SELECT FirstName, LastName FROM Student;

SELECT * FROM Student;

Queries can be refined with clauses such as WHERE, ORDER BY, and GROUP BY to filter, sort, and aggregate data.

查询可以通过 WHERE、ORDER BY 和 GROUP BY 等子句进行细化，以对数据进行筛选、排序和聚合。

7. Filtering Rows with WHERE and Logical Operators | 使用 WHERE 和逻辑运算符筛选行

The WHERE clause filters records that meet specific conditions. Operators include =, <, >, <=, >=, <> (not equal), BETWEEN, LIKE, and IN.

WHERE 子句筛选满足特定条件的记录。运算符包括 =、<、>、<=、>=、<>（不等于）、BETWEEN、LIKE 和 IN。

SELECT * FROM Student WHERE LastName = ‘Smith’;

Combine conditions using AND, OR, and NOT to create complex filters. For instance, to find students whose first name is ‘Alice’ and were born after 2007:

使用 AND、OR 和 NOT 组合条件以创建复杂的筛选器。例如，要查找名叫 ‘Alice’ 且出生年份在 2007 年之后的学生：

SELECT * FROM Student
WHERE FirstName = ‘Alice’ AND DateOfBirth > ‘2007-12-31’;

The LIKE operator is used for pattern matching with wildcards % (multiple characters) and _ (single character).

LIKE 运算符用于使用通配符 %（多个字符）和 _（单个字符）进行模式匹配。

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

ORDER BY sorts the result set by one or more columns, either in ascending (ASC, the default) or descending (DESC) order.

ORDER BY 按一个或多个列对结果集进行排序，可以是升序（ASC，默认）或降序（DESC）。

SELECT * FROM Student ORDER BY LastName ASC;

To sort by multiple columns, separate them with commas; the sort is applied in the order specified.

要按多个列排序，用逗号分隔它们；排序按指定的顺序依次应用。

9. Using Aggregate Functions and GROUP BY | 使用聚合函数和 GROUP BY

Aggregate functions perform calculations on a set of rows and return a single value. Common functions include COUNT(), SUM(), AVG(), MAX(), and MIN(). They are often used with GROUP BY to group rows sharing a common characteristic.

聚合函数对一组行执行计算并返回单个值。常见的函数包括 COUNT()、SUM()、AVG()、MAX() 和 MIN()。它们通常与 GROUP BY 一起使用，以对共享一个共同特征的行进行分组。

SELECT COUNT(*) AS TotalStudents FROM Student;

When using GROUP BY, the SELECT list may only contain grouped columns and aggregate functions. The HAVING clause filters groups, similar to WHERE but for aggregated data.

当使用 GROUP BY 时，SELECT 列表只能包含分组的列和聚合函数。HAVING 子句用于筛选分组，类似于 WHERE，但针对的是聚合后的数据。

SELECT LastName, COUNT(*) AS Count
FROM Student GROUP BY LastName
HAVING COUNT(*) > 1;

10. Updating and Deleting Data | 更新和删除数据

UPDATE modifies existing records. It is essential to include a WHERE clause to target specific rows, otherwise all rows will be updated.

UPDATE 修改现有记录。务必包含 WHERE 子句以定位特定行，否则所有行都会被更新。

UPDATE Student SET Email = ‘alice@example.com’
WHERE StudentID = 1;

The DELETE statement removes rows from a table. Without a WHERE clause, all rows are deleted. Use carefully and always back up data first.

DELETE 语句从表中删除行。如果没有 WHERE 子句，所有行都会被删除。使用时要小心，并始终先备份数据。

DELETE FROM Student WHERE StudentID = 2;

11. Understanding Joins and Table Relationships | 理解连接和表之间的关系

Databases often consist of multiple related tables. Joins are used to combine rows from two or more tables based on a related column, typically a foreign key. The most common join is the INNER JOIN, which returns records that have matching values in both tables.

数据库通常由多个相关的表组成。连接（Joins） 用于根据相关列（通常是外键）将两个或多个表中的行组合起来。最常见的连接是 INNER JOIN，它返回两个表中具有匹配值的记录。

Consider two tables: Orders and Customers, where Orders.CustomerID references Customers.CustomerID.

考虑两个表：Orders 和 Customers，其中 Orders.CustomerID 引用 Customers.CustomerID。

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Other join types include LEFT JOIN (returns all rows from the left table), RIGHT JOIN, and FULL OUTER JOIN. For IGCSE AQA, understanding INNER JOIN and LEFT JOIN is usually sufficient.

其他连接类型包括 LEFT JOIN（返回左表的所有行）、RIGHT JOIN 和 FULL OUTER JOIN。对于 IGCSE AQA，了解 INNER JOIN 和 LEFT JOIN 通常就足够了。

12. Data Integrity and Constraints | 数据完整性和约束

SQL provides constraints to enforce data integrity. Besides PRIMARY KEY and NOT NULL, you can define FOREIGN KEY constraints to maintain referential integrity. A foreign key ensures that a value in one table matches a primary key in another table.

SQL 提供了约束来确保数据完整性。除了 PRIMARY KEY 和 NOT NULL，你还可以定义 FOREIGN KEY 约束来维护引用完整性。外键确保一个表中的值与另一个表中的主键相匹配。

CREATE TABLE Enrolment (
  EnrolmentID INT PRIMARY KEY,
  StudentID INT,
  CourseID INT,
  FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

The DISTINCT keyword removes duplicate rows from a query result, and NULL represents missing or unknown data. Use IS NULL and IS NOT NULL to test for null values.

DISTINCT 关键字从查询结果中删除重复行，而 NULL 表示缺失或未知的数据。使用 IS NULL 和 IS NOT NULL 来测试空值。

SELECT DISTINCT LastName FROM Student;

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

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

IGCSE AQA Computer Science: SQL Essentials | IGCSE AQA 计算机：SQL 考点精讲