📚 SQL Essentials for IGCSE Computer Science | IGCSE 计算机：SQL 考点精讲
Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. For IGCSE Computer Science, you need to understand how to retrieve, insert, update and delete data, as well as how to create simple database structures. This article breaks down every core SQL command and concept you will encounter in your exams, with clear examples and bilingual explanations.
结构化查询语言（SQL）是管理和操作关系数据库的标准语言。在 IGCSE 计算机科学考试中，你需要掌握如何检索、插入、更新和删除数据，以及如何创建简单的数据库结构。本文将拆解每个你会遇到的 SQL 核心命令和概念，配以清晰的示例和中英双语解释。
1. What is a Database? | 什么是数据库？
A database is a structured collection of data stored electronically. In a relational database, data is organised into tables, which consist of rows (records) and columns (fields). Each table usually has a primary key that uniquely identifies each record.
数据库是以电子方式存储的结构化数据集合。在关系数据库中，数据被组织成表，表由行（记录）和列（字段）组成。每个表通常有一个主键，用于唯一标识每条记录。
SQL allows us to communicate with the database to perform queries and updates. The IGCSE syllabus focuses on practical usage of SQL, not on installing database systems.
SQL 使我们能与数据库通信以执行查询和更新。IGCSE 考纲侧重于 SQL 的实际操作，而非安装数据库系统。
2. Retrieving Data with SELECT | 使用 SELECT 检索数据
The SELECT statement is used to fetch data from a database. The basic syntax is SELECT column1, column2 FROM table_name;. To select all columns, use an asterisk: SELECT * FROM table_name;.
SELECT 语句用于从数据库中取回数据。基本语法是 SELECT 列1, 列2 FROM 表名;。要选择所有列，使用星号：SELECT * FROM 表名;。
Example: to retrieve the name and age of all students from the ‘Students’ table, you would write: SELECT Name, Age FROM Students;.
示例：要从 ‘Students’ 表中检索所有学生的姓名和年龄，你可以写：SELECT Name, Age FROM Students;。
In exams, you may be given a table structure and asked to write a query. Always pay attention to the exact column names given.
在考试中，你可能会遇到给定的表结构，并被要求编写查询。务必注意给出的确切列名。
3. Filtering Results with WHERE | 使用 WHERE 过滤结果
The WHERE clause filters records based on a condition. It is added after the table name: SELECT columns FROM table WHERE condition;.
WHERE 子句根据条件过滤记录。它加在表名之后：SELECT 列 FROM 表名 WHERE 条件;。
Common operators used are =, >, <, >=, <=, <> (not equal). For text, use single quotes: WHERE Country = ‘UK’. For numbers, no quotes: WHERE Age >= 16.
常用运算符有 =、>、<、>=、<=、<>（不等于）。对于文本，使用单引号：WHERE Country = ‘UK’。对于数字，不加引号：WHERE Age >= 16。
You can combine conditions using AND, OR, and NOT. Example: SELECT * FROM Employees WHERE Department = ‘Sales’ AND Salary > 30000;
你可以使用 AND、OR 和 NOT 组合条件。例如：SELECT * FROM Employees WHERE Department = ‘Sales’ AND Salary > 30000;
4. Sorting Data with ORDER BY | 使用 ORDER BY 排序数据
The ORDER BY keyword sorts the result set in ascending (ASC) or descending (DESC) order. Ascending is the default. Place it at the end of the query: SELECT * FROM Products ORDER BY Price DESC;.
ORDER BY 关键字按升序 (ASC) 或降序 (DESC) 对结果集排序。升序是默认值。将其放在查询末尾：SELECT * FROM Products ORDER BY Price DESC;。
You can sort by multiple columns: ORDER BY Department ASC, Salary DESC; sorts first by department alphabetically, then by salary descending within each department.
你可以按多列排序：ORDER BY Department ASC, Salary DESC; 首先按部门字母升序，然后在每个部门内按工资降序排序。
Test your understanding: how would you list all students ordered by their registration date, newest first? Answer: SELECT * FROM Students ORDER BY RegistrationDate DESC;
测试你的理解：如何按注册日期从新到旧的顺序列出所有学生？答案：SELECT * FROM Students ORDER BY RegistrationDate DESC;
5. Inserting New Records with INSERT INTO | 使用 INSERT INTO 插入新记录
To add a new row into a table, use INSERT INTO. Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value2);.
要向表中添加新行，使用 INSERT INTO。语法：INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);。
Ensure the values match the column data types. Text values must be in single quotes, numbers without quotes. If you are inserting values for all columns in their defined order, you can omit the column list: INSERT INTO Students VALUES (101, ‘Emma’, 17);
确保值匹配列的数据类型。文本值必须用单引号，数字不加引号。如果你要按定义顺序为所有列插入值，可以省略列列表：INSERT INTO Students VALUES (101, ‘Emma’, 17);
IGCSE questions often provide a table and ask you to write an INSERT statement for a given record. Remember to use the correct table and column names exactly as specified.
IGCSE 考题通常会给出一个表格，要求你为给定记录编写 INSERT 语句。切记要准确使用指定的表名和列名。
6. Updating Existing Records with UPDATE | 使用 UPDATE 更新现有记录
The UPDATE statement modifies existing data. Syntax: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;. The WHERE clause is crucial; omitting it updates all rows!
UPDATE 语句修改现有数据。语法：UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;。WHERE 子句至关重要；省略它会更新所有行！
Example: increase the salary of employee with ID 203 by 10%: UPDATE Employees SET Salary = Salary * 1.10 WHERE EmployeeID = 203;
示例：将员工 ID 203 的工资提高 10%：UPDATE Employees SET Salary = Salary * 1.10 WHERE EmployeeID = 203;
Always use the primary key in the WHERE clause for safe, precise updates. The IGCSE exam expects you to recognise the danger of missing WHERE.
始终在 WHERE 子句中使用主键以确保安全、精确的更新。IGCSE 考试要求你意识到缺失 WHERE 的风险。
7. Deleting Records with DELETE | 使用 DELETE 删除记录
DELETE removes rows from a table. Syntax: DELETE FROM table_name WHERE condition;. As with UPDATE, without WHERE, all rows are deleted, though the table structure remains.
DELETE 从表中移除行。语法：DELETE FROM 表名 WHERE 条件;。与 UPDATE 一样，没有 WHERE 会删除所有行，但表结构保留。
Example: remove all orders placed before 2020: DELETE FROM Orders WHERE OrderDate < '2020-01-01';
示例：删除所有 2020 年之前的订单：DELETE FROM Orders WHERE OrderDate < '2020-01-01';
To delete a specific record, reference its primary key: DELETE FROM Students WHERE StudentID = 45;
要删除特定记录，引用其主键：DELETE FROM Students WHERE StudentID = 45;
Note the difference between DELETE and DROP TABLE. DROP removes the entire table, while DELETE removes data only.
注意 DELETE 和 DROP TABLE 的区别。DROP 移除整张表，而 DELETE 仅删除数据。
8. Creating Tables with DDL | 使用 DDL 创建表
Data Definition Language (DDL) includes CREATE, ALTER, and DROP. The exam may ask you to write a CREATE TABLE statement. Syntax: CREATE TABLE table_name ( column1 datatype PRIMARY KEY, column2 datatype NOT NULL, … );
数据定义语言（DDL）包括 CREATE、ALTER 和 DROP。考试可能会要求你编写 CREATE TABLE 语句。语法：CREATE TABLE 表名 ( 列1 数据类型 PRIMARY KEY, 列2 数据类型 NOT NULL, … );
Common data types: INTEGER, REAL/FLOAT, TEXT/VARCHAR(n), DATE, BOOLEAN. Example: CREATE TABLE Books ( BookID INTEGER PRIMARY KEY, Title TEXT NOT NULL, Author TEXT, Price REAL );
常见数据类型：INTEGER、REAL/FLOAT、TEXT/VARCHAR(n)、DATE、BOOLEAN。示例：CREATE TABLE Books ( BookID INTEGER PRIMARY KEY, Title TEXT NOT NULL, Author TEXT, Price REAL );
Setting a primary key ensures each record is unique and is often a requirement. Use NOT NULL for mandatory fields.
设置主键可确保每条记录唯一，并且通常是必选项。对必填字段使用 NOT NULL。
9. Aggregating Data with Functions | 使用聚合函数汇总数据
SQL provides aggregate functions to perform calculations on a set of values: COUNT, SUM, AVG, MAX, MIN. These are used with SELECT: SELECT COUNT(*) FROM Students WHERE Grade = ‘A’;
SQL 提供聚合函数来对一组值执行计算：COUNT、SUM、AVG、MAX、MIN。它们与 SELECT 配合使用：SELECT COUNT(*) FROM Students WHERE Grade = ‘A’;
COUNT(*) counts the number of rows. SUM(column) adds up numeric values. AVG(column) computes the average. MAX and MIN find the highest and lowest values.
COUNT(*) 计算行数。SUM(column) 将数值相加。AVG(column) 计算平均值。MAX 和 MIN 找出最高值和最低值。
Example: find the total sales from the Sales table: SELECT SUM(Amount) FROM Sales; To give the result a meaningful column header, use an alias: SELECT SUM(Amount) AS TotalSales FROM Sales;
示例：从 Sales 表中计算总销售额：SELECT SUM(Amount) FROM Sales; 要为结果赋予有意义的列标题，使用别名：SELECT SUM(Amount) AS TotalSales FROM Sales;
10. Grouping Data with GROUP BY and HAVING | 使用 GROUP BY 和 HAVING 分组数据
GROUP BY groups rows that have the same values in specified columns. It is often used with aggregate functions to produce summary reports. Syntax: SELECT column, aggregate_function(column) FROM table GROUP BY column;
GROUP BY 将指定列中具有相同值的行分组。它常与聚合函数一起使用以生成汇总报告。语法：SELECT 列, 聚合函数(列) FROM 表 GROUP BY 列;
Example: count the number of employees in each department: SELECT Department, COUNT(*) FROM Employees GROUP BY Department;
示例：统计每个部门的员工人数：SELECT Department, COUNT(*) FROM Employees GROUP BY Department;
HAVING filters groups, similar to WHERE for individual rows. WHERE cannot be used with aggregate functions. To display only departments with more than 5 employees: SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5;
HAVING 用于过滤分组，类似于 WHERE 对个别行的作用。WHERE 不能用于聚合函数。要仅显示员工数超过 5 的部门：SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5;
11. Joining Tables with INNER JOIN | 使用 INNER JOIN 连接表
A JOIN combines rows from two or more tables based on a related column. The most common is INNER JOIN, which returns rows where there is a match in both tables.
JOIN 基于相关列将两个或多个表的行组合在一起。最常见的是 INNER JOIN，它返回两个表中存在匹配的行。
Syntax: SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column; For clarity, use table aliases. Example: list order IDs and customer names from Orders and Customers tables linked by CustomerID: SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
语法：SELECT 列 FROM 表1 INNER JOIN 表2 ON 表1.列 = 表2.列; 为求清晰，可使用表别名。示例：从 Orders 和 Customers 表列出订单 ID 和客户姓名，通过 CustomerID 关联：SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
IGCSE usually sticks to simple joins with two tables. Remember to prefix column names with the table name if the column name exists in both tables to avoid ambiguity.
IGCSE 通常仅涉及两个表的简单连接。如果列名在两个表中都存在，记得在列名前加上表名以避免歧义。
12. Using Wildcards and Other Operators | 使用通配符和其他运算符
The LIKE operator is used with WHERE to search for a pattern in a text column. The percent sign (%) represents zero or more characters; underscore (_) represents a single character.
LIKE 运算符与 WHERE 一起使用，以在文本列中搜索模式。百分号 (%) 表示零个或多个字符；下划线 (_) 表示单个字符。
Example: find all students whose name starts with ‘A’: SELECT * FROM Students WHERE Name LIKE ‘A%’; To find names that have ‘an’ anywhere: WHERE Name LIKE ‘%an%’;
示例：查找所有名字以 ‘A’ 开头的学生：SELECT * FROM Students WHERE Name LIKE ‘A%’; 要查找名字中任意位置包含 ‘an’ 的：WHERE Name LIKE ‘%an%’;
The IN operator allows you to specify multiple values in a WHERE clause: WHERE Country IN (‘France’, ‘Germany’, ‘Italy’); BETWEEN selects values within a range: WHERE Age BETWEEN 13 AND 19;
IN 运算符允许你在 WHERE 子句中指定多个值：WHERE Country IN (‘France’, ‘Germany’, ‘Italy’); BETWEEN 选择在一定范围内的值：WHERE Age BETWEEN 13 AND 19;
Treat NULL values carefully. Use IS NULL or IS NOT NULL in conditions, because NULL is not equal to anything: WHERE Email IS NULL;
小心处理 NULL 值。在条件中使用 IS NULL 或 IS NOT NULL，因为 NULL 不等于任何东西：WHERE Email IS NULL;
Published by TutorHao | IGCSE Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导