📚 SQL Exam Essentials for CCEA IGCSE Computer Science | IGCSE CCEA 计算机：SQL 考点精讲
Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. For CCEA IGCSE Computer Science, a solid understanding of SQL is essential—not just to answer exam questions, but to appreciate how real-world applications store and retrieve data. This article breaks down every keyword you need, with clear syntax and practical examples.
结构化查询语言（SQL）是管理和操作关系数据库的标准语言。在 CCEA IGCSE 计算机科目中，扎实掌握 SQL 至关重要——不仅是为了解答考试题目，也是为了理解现实世界的应用程序如何存储和检索数据。本文将拆解所有你需要掌握的 SQL 关键词，提供清晰的语法和实用示例。
1. What is SQL and Why It Matters | 什么是 SQL 及其重要性
SQL stands for Structured Query Language. It is used to communicate with relational databases, where data is organised into tables made up of rows (records) and columns (fields). Every table has a unique primary key to identify each record. SQL allows you to perform four main types of operations, often remembered by the acronym CRUD: Create, Read, Update, and Delete. In the CCEA IGCSE specification, you are expected to write and interpret SQL statements that retrieve and modify data, as well as define database structures.
SQL 代表结构化查询语言（Structured Query Language）。它用于与关系数据库通信，关系数据库中的数据被组织成由行（记录）和列（字段）组成的表。每个表都有一个唯一的主键来标识每条记录。SQL 允许你执行四种主要类型的操作，通常用缩写 CRUD 来记忆：创建（Create）、读取（Read）、更新（Update）和删除（Delete）。在 CCEA IGCSE 考试大纲中，要求你能够编写和解释检索与修改数据的 SQL 语句，以及定义数据库结构。
2. Retrieving Data with SELECT and FROM | 使用 SELECT 和 FROM 检索数据
The most fundamental SQL command is SELECT, which is used to retrieve data from a database. It must be followed by the columns you want to see, and then the FROM clause specifies which table to read from. To select all columns from a table, you can use the asterisk (*) wildcard. For example, to get all data from a table called Students, you would write: SELECT * FROM Students; If you only need the first name and date of birth, you would write: SELECT FirstName, DateOfBirth FROM Students; Remember that SQL keywords are not case-sensitive, but it is common practice to write them in uppercase for readability.
最基本的 SQL 命令是 SELECT，用于从数据库中检索数据。它后面必须跟着你想要查看的列，然后 FROM 子句指定从哪个表中读取。如果要选择一个表中的所有列，你可以使用星号（*）通配符。例如，要从名为 Students 的表中获取所有数据，你可以写：SELECT * FROM Students; 如果你只需要名字和出生日期，可以写：SELECT FirstName, DateOfBirth FROM Students; 请记住，SQL 关键字不区分大小写，但为了可读性，通常将它们大写书写。
3. Filtering Results with WHERE | 使用 WHERE 过滤结果
The WHERE clause allows you to filter records so that only those meeting a specified condition are returned. It comes after the FROM clause. You can use comparison operators such as =, >, <, >=, <=, and <> (or !=). For instance, SELECT * FROM Students WHERE YearGroup = 11; retrieves only students in Year 11. You can combine multiple conditions using AND and OR. For example, SELECT * FROM Students WHERE YearGroup = 11 AND House = ‘Elm’; Text values must be enclosed in single quotes. The LIKE operator is useful for pattern matching with wildcards: the percent sign (%) represents any number of characters, while the underscore (_) represents a single character. SELECT * FROM Students WHERE LastName LIKE ‘Sm%’; would return all students whose last name starts with ‘Sm’.
WHERE 子句允许你过滤记录，使得只有满足指定条件的记录才会被返回。它放在 FROM 子句之后。你可以使用比较运算符，如 =、>、<、>=、<= 和 <>（或 !=）。例如，SELECT * FROM Students WHERE YearGroup = 11; 仅检索 11 年级的学生。你可以使用 AND 和 OR 组合多个条件。例如，SELECT * FROM Students WHERE YearGroup = 11 AND House = ‘Elm’; 文本值必须用单引号括起来。LIKE 运算符在使用通配符进行模式匹配时很有用：百分号（%）代表任意数量的字符，而下划线（_）代表单个字符。SELECT * FROM Students WHERE LastName LIKE ‘Sm%’; 将返回所有姓氏以 ‘Sm’ 开头的学生。
4. Sorting Output with ORDER BY | 使用 ORDER BY 排序输出
By default, SQL does not guarantee any particular order of rows. To sort the result set, you use the ORDER BY clause followed by one or more column names. You can specify ascending order with ASC (this is the default) or descending order with DESC. For example, SELECT FirstName, LastName FROM Students ORDER BY LastName ASC; sorts alphabetically by last name. If you want to sort by multiple columns, list them separated by commas: SELECT * FROM Students ORDER BY YearGroup ASC, LastName DESC; This first sorts by year group in ascending order, and within the same year group, it sorts by last name in reverse alphabetical order.
默认情况下，SQL 不保证行的任何特定顺序。要对结果集进行排序，可以使用 ORDER BY 子句，后跟一个或多个列名。你可以用 ASC 指定升序（这是默认设置），用 DESC 指定降序。例如，SELECT FirstName, LastName FROM Students ORDER BY LastName ASC; 按姓氏字母顺序排序。如果要按多个列排序，可以用逗号分隔列出它们：SELECT * FROM Students ORDER BY YearGroup ASC, LastName DESC; 这首先按年级升序排序，在同一年级内，再按姓氏降序排序。
5. Inserting, Updating, and Deleting Data | 插入、更新和删除数据
To add a new record into a table, use the INSERT INTO statement. You specify the table name and the columns in which you wish to insert data, then provide the corresponding values. For example: INSERT INTO Students (StudentID, FirstName, LastName, YearGroup) VALUES (1056, ‘Emma’, ‘Watson’, 10); If you are inserting values for every column in the exact order they were defined, you can omit the column list, but this is less safe. To modify existing data, use the UPDATE statement with SET and a WHERE clause (omitting WHERE would update every row!). For instance: UPDATE Students SET YearGroup = 11 WHERE StudentID = 1056; To remove records, use DELETE FROM: DELETE FROM Students WHERE StudentID = 1056; Always double-check your WHERE condition to avoid accidental data loss.
要向表中添加新记录，可使用 INSERT INTO 语句。你需要指定表名和要插入数据的列，然后给出对应的值。例如：INSERT INTO Students (StudentID, FirstName, LastName, YearGroup) VALUES (1056, ‘Emma’, ‘Watson’, 10); 如果你按列定义的顺序为每一列插入值，可以省略列名列表，但这样做不太安全。要修改现有数据，可使用 UPDATE 语句，配合 SET 和 WHERE 子句（省略 WHERE 会更新每一行！）。例如：UPDATE Students SET YearGroup = 11 WHERE StudentID = 1056; 要删除记录，使用 DELETE FROM：DELETE FROM Students WHERE StudentID = 1056; 务必反复检查你的 WHERE 条件，以避免意外丢失数据。
6. Creating and Modifying Tables with DDL | 使用 DDL 创建和修改表
Data Definition Language (DDL) commands allow you to define and change the structure of database tables. The CREATE TABLE command sets up a new table with its column names, data types, and constraints. For example: CREATE TABLE Teachers (TeacherID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Subject VARCHAR(30)); Common data types in CCEA exams include INT (integer), VARCHAR(n) (variable-length text), CHAR(n) (fixed-length text), DATE, and DECIMAL(p,s). The PRIMARY KEY constraint ensures each record is unique. To change an existing table, you can use ALTER TABLE to add a new column: ALTER TABLE Teachers ADD Email VARCHAR(100); or to modify a column’s data type. To remove a table completely, use DROP TABLE: DROP TABLE Teachers; This is irreversible, so use it with caution.
数据定义语言（DDL）命令允许你定义和更改数据库表的结构。CREATE TABLE 命令用于创建一个新表，包含列名、数据类型和约束条件。例如：CREATE TABLE Teachers (TeacherID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Subject VARCHAR(30)); CCEA 考试中常见的数据类型包括 INT（整数）、VARCHAR(n)（可变长度文本）、CHAR(n)（定长文本）、DATE 和 DECIMAL(p,s)。PRIMARY KEY 约束确保每条记录唯一。要更改现有的表，你可以使用 ALTER TABLE 添加新列：ALTER TABLE Teachers ADD Email VARCHAR(100); 或者修改列的数据类型。要完全删除一个表，使用 DROP TABLE：DROP TABLE Teachers; 这是不可逆的，因此要谨慎使用。
7. Aggregate Functions and GROUP BY | 聚合函数与 GROUP BY
SQL provides built-in functions that perform calculations on a set of rows and return a single value. The most important for your exam are COUNT, SUM, AVG, MAX, and MIN. For example, SELECT COUNT(*) FROM Students; counts all records; SELECT AVG(Marks) FROM Results; calculates the average mark. However, the real power comes when you combine these with the GROUP BY clause. This groups rows that have the same values in specified columns, allowing you to apply aggregate functions to each group. For instance, to find how many students are in each house: SELECT House, COUNT(*) FROM Students GROUP BY House; This returns one row per house with the number of students. You can group by multiple columns as well.
SQL 提供了内置函数，可对一组行执行计算并返回单个值。考试中最重要的函数是 COUNT、SUM、AVG、MAX 和 MIN。例如，SELECT COUNT(*) FROM Students; 计算所有记录的数量；SELECT AVG(Marks) FROM Results; 计算平均分。然而，真正的强大之处在于将这些函数与 GROUP BY 子句结合使用。GROUP BY 将指定列中具有相同值的行分组，允许你对每个组应用聚合函数。例如，要查找每个学院有多少学生：SELECT House, COUNT(*) FROM Students GROUP BY House; 这将为每个学院返回一行，包含学生数量。你也可以按多个列进行分组。
8. Filtering Groups with HAVING | 使用 HAVING 过滤分组
While the WHERE clause filters individual rows before grouping, the HAVING clause filters the groups themselves after the GROUP BY operation. It is used with aggregate functions. For example, if you want to list only those houses that have more than 50 students, you would write: SELECT House, COUNT(*) FROM Students GROUP BY House HAVING COUNT(*) > 50; You cannot use WHERE with an aggregate function like COUNT(*) because WHERE works on rows, not on aggregated results. HAVING is essential for applying conditions to summarised data. A common exam question might ask you to show subjects where the average score is below 60: SELECT Subject, AVG(Score) FROM Results GROUP BY Subject HAVING AVG(Score) < 60;
WHERE 子句在分组前过滤单个行，而 HAVING 子句则在 GROUP BY 操作之后过滤组本身。它需要与聚合函数一起使用。例如，如果你想仅列出学生人数超过 50 人的学院，可以这样写：SELECT House, COUNT(*) FROM Students GROUP BY House HAVING COUNT(*) > 50; 你不能在 WHERE 中使用 COUNT(*) 这样的聚合函数，因为 WHERE 作用于行，而不是聚合结果。HAVING 对于对汇总数据应用条件至关重要。一个常见的考题可能会要求你显示平均分低于 60 分的科目：SELECT Subject, AVG(Score) FROM Results GROUP BY Subject HAVING AVG(Score) < 60;
9. Joining Tables: INNER JOIN | 连接表：INNER JOIN
Relational databases often store data across multiple tables to avoid redundancy. To bring related data together, you use a JOIN. The most common type is the INNER JOIN, which returns only rows where there is a match in both tables based on a related column. The syntax: SELECT columns FROM Table1 INNER JOIN Table2 ON Table1.commonField = Table2.commonField; For example, a school database might have a Students table and a Results table. To list each student’s name and their marks: SELECT Students.FirstName, Students.LastName, Results.Marks FROM Students INNER JOIN Results ON Students.StudentID = Results.StudentID; Notice how we use the table name as a prefix to avoid ambiguity when columns have the same name. INNER JOIN is the default JOIN type—if you write just JOIN, it acts as an INNER JOIN.
关系数据库通常将数据存储在多个表中以避免冗余。要将相关数据组合在一起，你需要使用 JOIN。最常见的类型是 INNER JOIN，它仅返回在两个表中基于相关列存在匹配的行。语法为：SELECT columns FROM Table1 INNER JOIN Table2 ON Table1.commonField = Table2.commonField; 例如，一个学校数据库可能有一个 Students 表和一个 Results 表。要列出每个学生的姓名及其分数：SELECT Students.FirstName, Students.LastName, Results.Marks FROM Students INNER JOIN Results ON Students.StudentID = Results.StudentID; 请注意，当列名相同时，我们使用表名作为前缀以避免歧义。INNER JOIN 是默认的连接类型——如果你只写 JOIN，它的作用相当于 INNER JOIN。
10. LEFT and RIGHT JOINs | LEFT JOIN 和 RIGHT JOIN
Sometimes you need to keep all records from one table, even if there is no matching record in the other. A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table; if no match exists, NULL values are shown for the right table’s columns. For example, SELECT Students.FirstName, Results.Marks FROM Students LEFT JOIN Results ON Students.StudentID = Results.StudentID; This would list every student, and if a student has no mark yet, the Marks column will appear as NULL. A RIGHT JOIN works the opposite way: it keeps all rows from the right table. In practice, most SQL questions at IGCSE level focus on INNER JOIN and occasionally LEFT JOIN to identify missing data.
有时，你需要保留一个表中的所有记录，即使另一个表中没有匹配的记录。LEFT JOIN（或 LEFT OUTER JOIN）返回左表中的所有行以及右表中的匹配行；如果没有匹配，右表的列将显示 NULL 值。例如，SELECT Students.FirstName, Results.Marks FROM Students LEFT JOIN Results ON Students.StudentID = Results.StudentID; 这将列出每一个学生，如果某个学生还没有分数，Marks 列将显示为 NULL。RIGHT JOIN 的作用相反：它保留右表中的所有行。在实践中，IGCSE 级别的大多数 SQL 问题侧重于 INNER JOIN，偶尔会考查 LEFT JOIN 以识别缺失数据。
11. Practical Tips for Exam Success | 考试成功实用技巧
In CCEA IGCSE Computer Science exams, SQL questions often present a schema of two or three tables. Before writing any code, identify the primary keys and foreign keys, and decide which columns you need. Always put semicolons at the end of your statements—it shows good practice. Watch out for common errors: forgetting single quotes around text values, using WHERE when you should use HAVING, and mixing up table prefixes in JOINs. If the question asks for ‘unique’ values, remember to use SELECT DISTINCT. To prepare, practise writing queries by hand, as you will have to do in the exam without a computer. The more you familiarise yourself with the standard patterns, the more confidently you will approach the exam.
在 CCEA IGCSE 计算机科目考试中，SQL 题目通常会给出两个或三个表的架构。在编写任何代码之前，请先确定主键和外键，并确定你需要哪些列。总是在语句末尾加上分号——这表明良好的编程习惯。注意常见错误：文本值忘记加单引号、在应该使用 HAVING 时使用了 WHERE、在 JOIN 中混淆了表前缀。如果题目要求 ‘unique’（唯一）值，请记住使用 SELECT DISTINCT。为了做好准备，要练习手写查询语句，因为在考试中你无法使用电脑。你对标准模式越熟悉，考试时就越有信心。
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课程辅导，国外大学本科硕士研究生博士课程论文辅导