📚 SQL Key Concepts Explained for GCSE CIE | GCSE CIE 计算机：SQL 考点精讲
Structured Query Language (SQL) is the standard language for interacting with relational databases. For CIE GCSE Computer Science, you need to understand how to retrieve, insert, update and delete data, as well as how to define database structures using simple DDL commands. This article covers every essential topic with paired English–Chinese explanations, sample queries and practical tips.
结构化查询语言（SQL）是与关系数据库交互的标准语言。在 CIE 的 GCSE 计算机科学考试中，你需要掌握如何检索、插入、更新和删除数据，以及如何使用简单的 DDL 命令定义数据库结构。本文将通过中英对照的解释、示例查询和实用技巧，覆盖所有重要考点。
1. What is SQL? | 什么是 SQL？
SQL stands for Structured Query Language. It is used to communicate with a relational database management system (RDBMS). SQL is declarative – you tell the database what you want, not how to get it. There are two main categories: DDL (Data Definition Language) for creating or altering tables, and DML (Data Manipulation Language) for working with the data itself.
SQL 代表结构化查询语言，用于与关系型数据库管理系统（RDBMS）通信。SQL 是声明式的——你告诉数据库你想要什么，而不是怎么去获取。它主要分为两类：DDL（数据定义语言）用于创建或修改表，DML（数据操纵语言）用于操作数据本身。
2. The SELECT Statement and Basic Retrieval | SELECT 语句与基本检索
The most common SQL command is SELECT. It fetches data from one or more tables. The basic structure is: SELECT column1, column2 FROM table_name;. You can retrieve all columns with the asterisk wildcard *. For example, SELECT * FROM Students; returns every row and every column from the Students table.
最常用的 SQL 命令是 SELECT，它从一个或多个表中提取数据。基本结构是：SELECT 列1, 列2 FROM 表名;。你可以用星号通配符 * 检索所有列。例如，SELECT * FROM Students; 会返回 Students 表中的每一行和每一列。
3. Filtering with WHERE | 使用 WHERE 子句进行筛选
The WHERE clause filters rows based on a condition. It supports operators like =, <, >, <=, >=, <> (not equal), AND, OR, NOT, and LIKE for pattern matching. Example: SELECT Name, Grade FROM Students WHERE Grade > 80;. The LIKE operator uses % for any sequence of characters and _ for a single character: SELECT * FROM Products WHERE ProductName LIKE 'A%'; finds products starting with ‘A’.
WHERE 子句根据条件筛选行数据。它支持运算符如 =、<、>、<=、>=、<>（不等于）、AND、OR、NOT，以及用于模式匹配的 LIKE。示例：SELECT Name, Grade FROM Students WHERE Grade > 80;。LIKE 运算符使用 % 表示任意字符序列，_ 表示单个字符：SELECT * FROM Products WHERE ProductName LIKE 'A%'; 会找出所有以 ‘A’ 开头的产品。
4. Sorting Results with ORDER BY | 使用 ORDER BY 排序结果
ORDER BY sorts the result set by one or more columns. By default, sorting is ascending (ASC); use DESC for descending order. Example: SELECT Name, Score FROM Results ORDER BY Score DESC; sorts from highest to lowest score. You can sort by multiple columns: ORDER BY Department ASC, Salary DESC; sorts first by Department alphabetically, then by Salary from high to low within each department.
ORDER BY 按一个或多个列对结果集进行排序。默认是升序 (ASC)；使用 DESC 表示降序。示例：SELECT Name, Score FROM Results ORDER BY Score DESC; 按分数从高到低排序。你也可以按多个列排序：ORDER BY Department ASC, Salary DESC; 先按部门字母顺序排，然后在每个部门内按工资从高到低排。
5. Inserting Data with INSERT INTO | 使用 INSERT INTO 插入数据
To add new rows to a table, use the INSERT INTO statement. Specify the table name, column list (optional but recommended), and the VALUES clause. Example: INSERT INTO Students (StudentID, Name, Grade) VALUES (101, 'Alice', 92);. If you are inserting values for every column in the correct order, you can omit the column list: INSERT INTO Students VALUES (102, 'Bob', 85);. Always respect primary key uniqueness and NOT NULL constraints.
要向表中添加新行，使用 INSERT INTO 语句。指定表名、列列表（可选但建议）和 VALUES 子句。示例：INSERT INTO Students (StudentID, Name, Grade) VALUES (101, 'Alice', 92);。如果按正确顺序为每一列都提供了值，可以省略列列表：INSERT INTO Students VALUES (102, 'Bob', 85);。始终要注意主键的唯一性和 NOT NULL 约束。
6. Updating Existing Records with UPDATE | 使用 UPDATE 更新现有记录
The UPDATE statement modifies existing data. It usually includes a SET clause and a WHERE clause. Always use WHERE to avoid updating all rows accidentally. Example: UPDATE Students SET Grade = 95 WHERE StudentID = 101;. You can update multiple columns at once: UPDATE Employees SET Salary = Salary * 1.1, JobTitle = 'Senior Developer' WHERE Department = 'IT';.
UPDATE 语句用于修改现有数据。它通常包含 SET 子句和 WHERE 子句。务必使用 WHERE 以避免意外更新所有行。示例：UPDATE Students SET Grade = 95 WHERE StudentID = 101;。你可以一次更新多个列：UPDATE Employees SET Salary = Salary * 1.1, JobTitle = 'Senior Developer' WHERE Department = 'IT';。
7. Deleting Rows with DELETE | 使用 DELETE 删除行
The DELETE statement removes rows from a table. Again, a WHERE clause is crucial to target specific rows. DELETE FROM Students WHERE StudentID = 102; removes only that student. If you omit WHERE, all rows will be deleted, but the table structure remains. To remove all rows quickly, you can use TRUNCATE TABLE table_name;, but this is not always examinable at GCSE level.
DELETE 语句从表中移除行。同样，WHERE 子句对于定位特定行至关重要。DELETE FROM Students WHERE StudentID = 102; 只会删除那名学生。如果你省略 WHERE，所有行都会被删除，但表结构会保留。要快速删除所有行，可以使用 TRUNCATE TABLE table_name;，但在 GCSE 阶段并不总是考查。
8. Creating Tables with CREATE TABLE | 使用 CREATE TABLE 创建表
This is a DDL command that defines a new table, its columns and data types. You must specify at least one column, and you can set constraints like PRIMARY KEY, NOT NULL, and UNIQUE. Example: CREATE TABLE Students (StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Grade INT);. Common data types include INT, VARCHAR(n), CHAR(n), DATE, and DECIMAL(p,s).
这是一条 DDL 命令，用于定义一个新表及其列和数据类型。你必须至少指定一列，并可以设置约束，如 PRIMARY KEY、NOT NULL 和 UNIQUE。示例：CREATE TABLE Students (StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Grade INT);。常见的数据类型包括 INT、VARCHAR(n)、CHAR(n)、DATE 和 DECIMAL(p,s)。
9. Primary Keys and Foreign Keys | 主键与外键
A primary key uniquely identifies each row in a table. It cannot be NULL and must contain unique values. A foreign key is a column that links to the primary key of another table, enforcing referential integrity. To define a foreign key: CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));. This ensures that every customer ID in Orders exists in the Customers table.
主键 唯一标识表中的每一行。它不能为 NULL，且必须包含唯一值。外键 是一个列，它链接到另一张表的主键，确保了引用完整性。定义外键的示例：CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));。这能确保 Orders 表中的每个客户 ID 都存在于 Customers 表中。
10. Simple Joins – Combining Tables | 简单联接——组合表格
When data is spread across multiple tables, you use JOIN to combine them. The typical form is INNER JOIN, which returns rows where there is a match in both tables. Syntax: SELECT Students.Name, Grades.Subject, Grades.Mark FROM Students INNER JOIN Grades ON Students.StudentID = Grades.StudentID;. This links the two tables on the matching StudentID column. Other join types (LEFT, RIGHT) are rarely tested at GCSE but worth knowing.
当数据分布在多张表中时，你可以使用 JOIN 来组合它们。常见的形式是 INNER JOIN，它返回在两个表中都有匹配的行。语法：SELECT Students.Name, Grades.Subject, Grades.Mark FROM Students INNER JOIN Grades ON Students.StudentID = Grades.StudentID;。这通过匹配的 StudentID 列将两张表连接起来。其他联接类型（LEFT、RIGHT）在 GCSE 中很少考查，但值得了解。
11. Aggregate Functions and GROUP BY | 聚合函数与 GROUP BY
SQL provides aggregate functions like COUNT, SUM, AVG, MIN, and MAX. They are often used with GROUP BY to summarise data. Example: SELECT Department, COUNT(*) AS EmployeeCount FROM Employees GROUP BY Department;. You can filter groups with HAVING, which works like WHERE but on aggregated data: SELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;.
SQL 提供了聚合函数，如 COUNT、SUM、AVG、MIN 和 MAX。它们通常与 GROUP BY 配合使用以汇总数据。示例：SELECT Department, COUNT(*) AS EmployeeCount FROM Employees GROUP BY Department;。你可以使用 HAVING 对分组进行筛选，它的作用类似于 WHERE，但用于聚合数据：SELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;。
12. Exam Tips and Common Pitfalls | 考试技巧与常见错误
In CIE GCSE exams, SQL questions often ask you to write a query for a given scenario. Always read the table structure carefully, use the exact column names provided, and remember to end statements with a semicolon. Common mistakes: forgetting WHERE in UPDATE/DELETE, misspelling column names, confusing HAVING with WHERE, and omitting the join condition. Practice using past paper scenarios – a small syntax slip can cost marks.
在 CIE 的 GCSE 考试中，SQL 题目通常会要求你为给定的场景编写查询。务必仔细阅读表结构，使用题目提供的准确列名，并记得以分号结束语句。常见错误包括：在 UPDATE/DELETE 中忘记加 WHERE、拼错列名、混淆 HAVING 和 WHERE，以及遗漏联接条件。多练习历年真题中的场景——一处小小的语法错误就可能导致失分。
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课程辅导，国外大学本科硕士研究生博士课程论文辅导