SQL Essentials for IGCSE Edexcel Computer Science | IGCSE Edexcel 计算机：SQL 考点精讲

📚 SQL Essentials for IGCSE Edexcel Computer Science | IGCSE Edexcel 计算机：SQL 考点精讲

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. In the IGCSE Edexcel Computer Science syllabus, you are expected to understand how to retrieve, insert, update, and delete data, as well as how to define the structure of a database. This article explains every key concept you need to master, with clear examples and bilingual explanations.

结构化查询语言（SQL）是管理和操作关系数据库的标准语言。在 IGCSE Edexcel 计算机科学课程中，你需要掌握如何检索、插入、更新和删除数据，以及如何定义数据库的结构。本文用清晰的示例和中英双语解释每一个你必须掌握的核心概念。

1. Relational Databases and SQL | 关系数据库与 SQL 概述

A relational database stores data in tables (relations) made up of rows (records) and columns (fields). Each table represents an entity, and relationships between tables are created using foreign keys. SQL is the language used to communicate with the database management system (DBMS) to perform CRUD operations: Create, Read, Update, Delete.

关系数据库将数据存储在由行（记录）和列（字段）组成的表（关系）中。每个表代表一个实体，表之间通过外键建立联系。SQL 是用来与数据库管理系统（DBMS）通信的语言，以执行 CRUD 操作：创建、读取、更新、删除。

2. The Basic SELECT Statement | 基本 SELECT 语句

The SELECT statement is used to query data from a table. The simplest form retrieves all columns using an asterisk *. For example, SELECT * FROM Students; returns every row and column from the Students table.

SELECT 语句用于从表中查询数据。最简单的形式用星号 * 检索所有列。例如 SELECT * FROM Students; 将返回 Students 表中的每一行和每一列。

To fetch only specific columns, list them separated by commas: SELECT FirstName, LastName FROM Students; This improves efficiency and clarity.

如果只取特定列，用逗号分隔列出列名：SELECT FirstName, LastName FROM Students; 这能提高效率并使意图更清晰。

3. Filtering Rows with WHERE | 用 WHERE 子句筛选行

The WHERE clause allows you to filter records based on a condition. For instance, SELECT * FROM Students WHERE Grade = 'A'; retrieves only the students who achieved an A grade. Operators like =, <> (not equal), >, <, >=, <= are commonly used.

WHERE 子句让你根据条件筛选记录。例如 SELECT * FROM Students WHERE Grade = 'A'; 只检索成绩为 A 的学生。常用的运算符有 =、<>（不等于）、>、<、>=、<=。

You can combine conditions using AND, OR, NOT. SELECT * FROM Students WHERE Grade = 'A' AND Attendance > 90; finds students with both a high grade and high attendance.

可以用 AND、OR、NOT 组合条件。SELECT * FROM Students WHERE Grade = 'A' AND Attendance > 90; 查找成绩为 A 并且出勤率高于 90% 的学生。

The LIKE operator is used for pattern matching with wildcards: % matches any sequence of characters, _ matches a single character. SELECT * FROM Students WHERE LastName LIKE 'S%'; returns students whose last name starts with S.

LIKE 运算符配合通配符进行模式匹配：% 匹配任意字符序列，_ 匹配单个字符。SELECT * FROM Students WHERE LastName LIKE 'S%'; 返回姓氏以 S 开头的学生。

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

The ORDER BY clause sorts the result set by one or more columns. The default order is ascending (ASC). To sort descending, add DESC. SELECT * FROM Students ORDER BY LastName ASC; sorts alphabetically by last name.

ORDER BY 子句按一个或多个列对结果集进行排序。默认顺序为升序（ASC）。若要降序，添加 DESC。SELECT * FROM Students ORDER BY LastName ASC; 按姓氏的字母顺序排序。

Multiple columns can be used: SELECT * FROM Students ORDER BY Grade DESC, LastName ASC; first sorts by grade descending, then by last name ascending for the same grade.

可以按多个列排序：SELECT * FROM Students ORDER BY Grade DESC, LastName ASC; 先按成绩降序排列，成绩相同时再按姓氏升序排列。

5. Inserting New Records | 插入新记录

To add a new row to a table, use the INSERT INTO statement. Specify the table name and the columns for which you are providing values, then the VALUES clause. INSERT INTO Students (FirstName, LastName, Grade) VALUES ('Li', 'Wei', 'B');

要向表中添加新行，使用 INSERT INTO 语句。指定表名、要提供值的列，然后用 VALUES 子句给出数据。INSERT INTO Students (FirstName, LastName, Grade) VALUES ('Li', 'Wei', 'B');

If you are inserting values for all columns in the same order as they appear in the table definition, you can omit the column list: INSERT INTO Students VALUES (7, 'Li', 'Wei', 'B', 95); However, it is safer to explicitly name the columns.

如果插入的值顺序与表定义中的列顺序完全一致，可以省略列列表：INSERT INTO Students VALUES (7, 'Li', 'Wei', 'B', 95); 但显式指定列名更加安全。

6. Updating and Deleting Data | 更新与删除数据

The UPDATE statement modifies existing records. Always include a WHERE clause to target specific rows; otherwise, all rows will be updated. UPDATE Students SET Grade = 'A' WHERE StudentID = 3; changes the grade of the student with ID 3.

UPDATE 语句用于修改现有记录。务必加上 WHERE 子句以指定需要更新的行，否则所有行都会被更新。UPDATE Students SET Grade = 'A' WHERE StudentID = 3; 将学号为 3 的学生的成绩改为 A。

Multiple columns can be updated simultaneously: UPDATE Students SET Grade = 'B', Attendance = 88 WHERE StudentID = 5;

可以同时更新多个列：UPDATE Students SET Grade = 'B', Attendance = 88 WHERE StudentID = 5;

DELETE removes rows from a table. Like UPDATE, it is critical to use WHERE. DELETE FROM Students WHERE StudentID = 7; deletes a specific student’s record. Omitting WHERE would empty the entire table.

DELETE 从表中删除行。和 UPDATE 一样，使用 WHERE 至关重要。DELETE FROM Students WHERE StudentID = 7; 删除特定学生的记录。省略 WHERE 会清空整个表。

7. Creating Tables and Data Types | 创建表与数据类型

The CREATE TABLE statement defines a new table along with its columns and their data types. Common data types include: INTEGER for whole numbers, VARCHAR(n) for variable‑length text up to n characters, CHAR(n) for fixed‑length text, DATE for dates, and BOOLEAN for true/false values.

CREATE TABLE 语句用于定义新表及其列和数据类型。常见的数据类型有：INTEGER（整数）、VARCHAR(n)（可变长度文本，最多 n 个字符）、CHAR(n)（固定长度文本）、DATE（日期）以及 BOOLEAN（布尔值）。

Example: CREATE TABLE Teachers ( TeacherID INTEGER, FirstName VARCHAR(50), LastName VARCHAR(50), Subject VARCHAR(30), HireDate DATE ); This creates the Teachers table with five fields.

示例：CREATE TABLE Teachers ( TeacherID INTEGER, FirstName VARCHAR(50), LastName VARCHAR(50), Subject VARCHAR(30), HireDate DATE ); 这样便创建了一个包含五个字段的 Teachers 表。

8. Primary Keys and Foreign Keys | 主键与外键

A primary key uniquely identifies each record in a table. It is often an integer ID column. To declare a primary key during table creation, use PRIMARY KEY (ColumnName) or write PRIMARY KEY after the column definition. Example: StudentID INTEGER PRIMARY KEY.

主键唯一标识表中的每条记录。它通常是一个整数 ID 列。在创建表时声明主键，可以使用 PRIMARY KEY (ColumnName) 或在列定义之后写上 PRIMARY KEY。例如：StudentID INTEGER PRIMARY KEY。

A foreign key creates a link between two tables. It is a column in one table that refers to the primary key of another table. This enforces referential integrity. Syntax: FOREIGN KEY (ColumnName) REFERENCES OtherTable(PKColumn). For instance, in a Marks table we might write FOREIGN KEY (StudentID) REFERENCES Students(StudentID).

外键在两个表之间建立联系。它是某个表中的一个列，引用了另一个表的主键。这样可以确保引用完整性。语法为：FOREIGN KEY (ColumnName) REFERENCES OtherTable(PKColumn)。例如，在 Marks 表中我们可以写 FOREIGN KEY (StudentID) REFERENCES Students(StudentID)。

9. Joining Tables with INNER JOIN | 用 INNER JOIN 连接表

When data is spread across related tables, you use JOIN to bring it together. The most common type is INNER JOIN, which returns rows where there is a match in both tables.

当数据分布在相互关联的多个表中时，使用 JOIN 可以将它们整合在一起。最常见的是 INNER JOIN，它返回两个表中匹配的行。

The basic syntax: SELECT columns FROM Table1 INNER JOIN Table2 ON Table1.PK = Table2.FK; For example, to list each student’s name and their marks: SELECT Students.FirstName, Students.LastName, Marks.Score FROM Students INNER JOIN Marks ON Students.StudentID = Marks.StudentID;

基本语法：SELECT columns FROM Table1 INNER JOIN Table2 ON Table1.PK = Table2.FK; 例如，要列出每位学生的姓名及其分数：SELECT Students.FirstName, Students.LastName, Marks.Score FROM Students INNER JOIN Marks ON Students.StudentID = Marks.StudentID;

You can also use table aliases to shorten the query: SELECT s.FirstName, m.Score FROM Students s INNER JOIN Marks m ON s.StudentID = m.StudentID;

你也可以使用表别名来缩短查询：SELECT s.FirstName, m.Score FROM Students s INNER JOIN Marks m ON s.StudentID = m.StudentID;

10. Using Aggregate Functions | 使用聚合函数

SQL provides functions that calculate a single value from a group of rows. The most important are: COUNT returns the number of rows, SUM adds up numeric values, AVG computes the average, MAX and MIN find the highest and lowest values.

SQL 提供了一些函数，可以从一组行中计算出一个单一值。其中最重要的是：COUNT 返回行数，SUM 对数值求和，AVG 计算平均值，MAX 和 MIN 分别找出最大值和最小值。

Example: SELECT COUNT(*) FROM Students; gives the total number of students. SELECT AVG(Score) FROM Marks WHERE Subject = 'Maths'; calculates the average maths score.

示例：SELECT COUNT(*) FROM Students; 给出学生总人数。SELECT AVG(Score) FROM Marks WHERE Subject = 'Maths'; 计算数学科目的平均分。

When using aggregate functions, you cannot mix ordinary columns without grouping. This leads us to GROUP BY.

在使用聚合函数时，如果不进行分组，就不能将普通列与聚合值混在一起。这就引出了 GROUP BY。

11. Grouping Data with GROUP BY | 用 GROUP BY 分组数据

The GROUP BY clause groups rows that have the same values in specified columns. It is typically used with aggregate functions to produce summary reports. For example, to count the number of students in each grade: SELECT Grade, COUNT(*) FROM Students GROUP BY Grade;

GROUP BY 子句将指定列中具有相同值的行分为一组。它通常与聚合函数结合使用以生成汇总报告。例如，统计每个成绩等级的学生人数：SELECT Grade, COUNT(*) FROM Students GROUP BY Grade;

You can filter groups using HAVING, which works like WHERE but operates on aggregated results. SELECT Subject, AVG(Score) FROM Marks GROUP BY Subject HAVING AVG(Score) > 70; shows subjects where the average score exceeds 70.

你可以用 HAVING 过滤分组，它的作用类似于 WHERE，但作用于聚合后的结果。SELECT Subject, AVG(Score) FROM Marks GROUP BY Subject HAVING AVG(Score) > 70; 显示平均分超过 70 的科目。

Remember: WHERE filters rows before grouping; HAVING filters groups after aggregation.

记住：WHERE 在分组前过滤行；HAVING 在聚合后过滤分组。

12. Exam Tips and Common Pitfalls | 考试技巧与常见错误

When writing SQL in the exam, always use correct spelling for keywords: SELECT, FROM, WHERE, ORDER BY, INSERT INTO, UPDATE, DELETE, CREATE TABLE, etc. Semi-colons at the end of statements are optional but good practice.

在考试中编写 SQL 时，务必保证关键字拼写正确：SELECT、FROM、WHERE、ORDER BY、INSERT INTO、UPDATE、DELETE、CREATE TABLE 等。语句末尾的分号是可选的，但属于良好习惯。

Do not confuse DELETE with DROP. DELETE removes rows from a table; DROP removes the entire table structure. Also, remember that UPDATE and DELETE must almost always include WHERE to avoid affecting all rows.

不要混淆 DELETE 和 DROP。DELETE 删除表中的行；DROP 删除整个表结构。另外请记住，UPDATE 和 DELETE 几乎总要包含 WHERE，以免影响所有行。

When joining tables, make sure the ON condition uses the correct primary–foreign key pair, and always qualify column names with the table name (or alias) if the same column name exists in multiple tables.

在连接表时，确保 ON 条件使用了正确的主键-外键对。如果多个表中存在相同的列名，一定要用表名（或别名）限定列名。

Finally, practice interpreting questions: “list all…”, “show the total…”, “find the highest…”, “for each…”. These keywords hint at SELECT, aggregate functions, MAX/MIN, or GROUP BY respectively.

最后，练习解读题目中的关键词：”列出所有…”（list all…）提示 SELECT，”显示总数…”（show the total）提示聚合函数，”找出最高…”（find the highest）提示 MAX，”对于每个…”（for each…）提示 GROUP BY。

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

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