📚 IGCSE OCR Computer Science: SQL Key Points Explained | IGCSE OCR 计算机：SQL 考点精讲
Structured Query Language (SQL) is the standard language used to interact with relational databases. For the IGCSE OCR Computer Science specification, you need to be confident in writing SQL statements to retrieve, insert, update and delete data, as well as understanding the structure of basic database tables. This guide breaks down every key concept you must master, with clear examples and explanations, ensuring you are fully prepared for your examination.
结构化查询语言 (SQL) 是用于与关系型数据库交互的标准语言。在 IGCSE OCR 计算机科学课程大纲中，你需要熟练掌握编写 SQL 语句来检索、插入、更新和删除数据，同时理解基本数据库表的结构。本指南将逐一分解你必须掌握的每个核心概念，并配以清晰的示例和解释，确保你为考试做好充分准备。
1. Databases, Tables and SQL | 数据库、数据表与 SQL
A relational database stores data in tables, which consist of rows (records) and columns (fields). Each column has a specific data type, such as INTEGER, VARCHAR (text), DATE or BOOLEAN. SQL is the language that allows you to define, manipulate and query these tables.
关系型数据库将数据存储在数据表中，这些表由行（记录）和列（字段）组成。每一列都有特定的数据类型，例如 INTEGER（整数）、VARCHAR（文本）、DATE（日期）或 BOOLEAN（布尔型）。SQL 就是用来定义、操作和查询这些数据表的语言。
A table is uniquely identified by its name, and every column has a name that must be unique within that table. The intersection of a row and a column holds a single data value. In the exam, you will often be given a table schema and asked to write SQL to answer a particular information need.
每张数据表通过其名称唯一标识，而表中的每一列也有一个在该表内必须唯一的列名。行与列的交叉处存储着一个单独的数据值。在考试中，你经常会拿到一张表的结构，并被要求编写 SQL 来满足特定的信息需求。
Key terms you must know: primary key (a field that uniquely identifies each record), foreign key (a field in one table that references the primary key of another table), and data integrity (ensuring accuracy and consistency of data).
你必须掌握的关键术语：主键（唯一标识每条记录的字段）、外键（一张表中引用另一张表主键的字段）以及数据完整性（确保数据的准确性和一致性）。
2. The SELECT Statement – Retrieving Data | SELECT 语句 – 检索数据
The most fundamental SQL command is SELECT, which fetches data from a table. The simplest form is SELECT * FROM tablename; This returns all columns and all rows. However, in practice and in the exam, you usually need to specify only the columns you want.
最基础的 SQL 命令是 SELECT，用于从表中获取数据。最简单的形式是 SELECT * FROM tablename;，它会返回所有列和所有行。然而，在实际应用和考试中，你通常只需要指定想要的列。
For example, to retrieve just the ‘name’ and ‘age’ columns from a table called Students, you would write:
例如，若要从名为 Students 的表中仅检索 ‘name’ 和 ‘age’ 列，应这样写：
SELECT name, age FROM Students;
Using column names instead of * is more efficient and shows the examiner you understand exactly which data is required. You can also use SELECT to display calculated fields, such as price * 1.2 to show a price with tax, but IGCSE OCR mainly tests basic retrieval.
使用列名来代替 * 效率更高，并且能向阅卷人展示你完全清楚需要哪些数据。你也可以使用 SELECT 来展示计算字段，例如 price * 1.2 以显示含税价格，但 IGCSE OCR 主要考查基础的检索功能。
3. Filtering Results with WHERE | 使用 WHERE 过滤结果
To retrieve only those rows that meet a specific condition, you add a WHERE clause. The syntax is SELECT column1, column2 FROM table WHERE condition; The condition usually compares a column with a value using operators like =, <, >, <=, >=, <> (not equal).
若要只检索满足特定条件的行，就需要添加 WHERE 子句。其语法为 SELECT column1, column2 FROM table WHERE condition; 条件通常使用 =、<、>、<=、>=、<>（不等于）等运算符将列与某个值进行比较。
Example: find all students who are older than 15.
示例：找出所有年龄大于 15 岁的学生。
SELECT name FROM Students WHERE age > 15;
Text values must be enclosed in single quotes, such as WHERE city = ‘London’. Numeric values are written without quotes. You can also use WHERE with dates, e.g. WHERE birthdate = ’01/01/2009′, but the exact format depends on the database system; the OCR exam uses simple string comparisons for text fields.
文本值必须用单引号括起来，例如 WHERE city = ‘London’。数值则不加引号。你还可以在 WHERE 中使用日期，比如 WHERE birthdate = ’01/01/2009’，但具体格式取决于数据库系统；OCR 考试中对文本字段使用简单的字符串比较。
If there is no WHERE clause, all rows are returned. The WHERE clause allows you to ask precise questions of your data, which is a key exam skill.
如果没有 WHERE 子句，就会返回所有行。WHERE 子句使你能够向数据提出精确的问题，这是一项关键的考试技能。
4. Pattern Matching with LIKE | 使用 LIKE 进行模式匹配
When you don’t know the exact value to search for, you can use the LIKE operator with wildcards. The percent sign % represents zero, one or multiple characters. The underscore _ represents exactly one character.
当你不确定要搜索的确切值时，可以使用 LIKE 运算符配合通配符。百分号 % 表示零个、一个或多个字符，下划线 _ 则表示恰好一个字符。
For instance, to find all students whose name starts with ‘J’:
例如，要找出所有名字以 ‘J’ 开头的学生：
SELECT * FROM Students WHERE name LIKE ‘J%’;
To find names that have ‘an’ anywhere inside them, you would use LIKE ‘%an%’. If you want a name that is exactly four letters long and begins with ‘J’, you could write LIKE ‘J___’ (one J followed by three underscores).
若要查找名字中任意位置包含 ‘an’ 的情况，应使用 LIKE ‘%an%’。如果你想要一个恰好四个字母长且以 ‘J’ 开头的名字，可以写 LIKE ‘J___’（一个 J 后跟三个下划线）。
Remember: LIKE is used only with text (string) data. It is case‑sensitive in some database systems, but the OCR exam usually treats it as case‑insensitive for simplicity, unless stated otherwise. Always read the question carefully.
请记住：LIKE 仅用于文本（字符串）数据。在某些数据库系统中它是区分大小写的，但为了简化，OCR 考试通常将其视为不区分大小写，除非题目另有说明。务必仔细审题。
5. Combining Conditions with AND and OR | 用 AND 和 OR 组合条件
Often you need to filter data using more than one condition. The AND operator requires both conditions to be true, while OR requires at least one to be true. Parentheses can be used to group conditions and control the order of evaluation.
你常常需要使用多个条件来过滤数据。AND 运算符要求所有条件都成立，而 OR 要求至少有一个条件成立。圆括号可用于对条件进行分组并控制运算顺序。
Example: find students who are over 15 AND live in London.
示例：找出年龄超过 15 岁并且住在伦敦的学生。
SELECT name FROM Students WHERE age > 15 AND city = ‘London’;
If the question asked for students who are over 15 OR live in London, you would use OR. That would return a larger set of rows. Without parentheses, AND has higher precedence than OR, so WHERE condition1 OR condition2 AND condition3 means condition1 OR (condition2 AND condition3). To avoid confusion, always use parentheses in complex queries: WHERE (age > 15 OR city = ‘London’) AND gender = ‘F’.
如果题目要求找出年龄超过 15 岁或者住在伦敦的学生，就要使用 OR，这样会返回一个更大的行集。在没有圆括号的情况下，AND 的优先级高于 OR，因此 WHERE condition1 OR condition2 AND condition3 实际上表示 condition1 OR (condition2 AND condition3)。为避免混淆，在复杂查询中始终使用圆括号：WHERE (age > 15 OR city = ‘London’) AND gender = ‘F’。
6. Sorting Output with ORDER BY | 使用 ORDER BY 对输出排序
The ORDER BY clause sorts the result set by one or more columns, either in ascending (ASC) or descending (DESC) order. By default, ORDER BY sorts in ascending order if ASC or DESC is not specified.
ORDER BY 子句可以按一列或多列对结果集进行排序，排序方式可以是升序 (ASC) 或降序 (DESC)。如果未指定 ASC 或 DESC，ORDER BY 默认按升序排列。
Syntax: SELECT columns FROM table WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
语法：SELECT columns FROM table WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
For example, to list all students sorted by surname alphabetically:
例如，按姓氏字母顺序列出所有学生：
SELECT firstname, surname FROM Students ORDER BY surname ASC;
To display products sorted by price from highest to lowest, you would use ORDER BY price DESC. You can sort by multiple columns: ORDER BY department ASC, salary DESC sorts first by department and then by salary within each department. The position of ORDER BY is always after WHERE (if present) and before any semicolon.
若要按价格从高到低显示产品，应用 ORDER BY price DESC。你还可以按多列排序：ORDER BY department ASC, salary DESC 会先按部门排序，再在每个部门内按工资排序。ORDER BY 的位置始终在 WHERE（如果存在）之后、分号之前。
7. Inserting New Records with INSERT INTO | 使用 INSERT INTO 插入新记录
To add a new row to a table, you use the INSERT INTO statement. The basic form is INSERT INTO tablename (column1, column2, …) VALUES (value1, value2, …); You must provide values for all required columns (typically all columns that are not auto‑increment primary keys).
若要向表中添加新行，需使用 INSERT INTO 语句。其基本形式为 INSERT INTO tablename (column1, column2, …) VALUES (value1, value2, …); 你必须为所有必需的列提供值（通常是指所有不是自动递增主键的列）。
Example: adding a new student to the Students table.
示例：向 Students 表中添加一名新学生。
INSERT INTO Students (studentID, name, age, city) VALUES (107, ‘Alice’, 14, ‘Manchester’);
If you are inserting values for every column in the exact order they appear in the table, you can omit the column list: INSERT INTO Students VALUES (107, ‘Alice’, 14, ‘Manchester’); However, the exam expects you to be explicit and include the column names to avoid mistakes. Text values must be in quotes, numbers without quotes.
如果你为每一列都按表中列的顺序插入了值，就可以省略列名列表：INSERT INTO Students VALUES (107, ‘Alice’, 14, ‘Manchester’); 但考试希望你书写完整，包括列名以避免错误。文本值必须加引号，数值不加引号。
8. Modifying Existing Data with UPDATE | 使用 UPDATE 修改现有数据
UPDATE changes existing rows in a table. The syntax is UPDATE tablename SET column1 = value1, column2 = value2 WHERE condition; The WHERE clause is crucial: if you omit it, every row will be updated, which is a common mistake.
UPDATE 用于修改表中的现有行。语法为 UPDATE tablename SET column1 = value1, column2 = value2 WHERE condition; WHERE 子句至关重要：如果省略它，每一行都会被更新，这是一个常见错误。
Example: change the city of the student with studentID 107 to ‘Birmingham’.
示例：将学号为 107 的学生的城市改为 ‘Birmingham’。
UPDATE Students SET city = ‘Birmingham’ WHERE studentID = 107;
You can update multiple columns at once: UPDATE Students SET age = 15, city = ‘Leeds’ WHERE studentID = 107; Always double‑check the WHERE condition to ensure only the intended records are affected. In the exam, they will test your ability to write a specific, targeted update.
你可以同时更新多列：UPDATE Students SET age = 15, city = ‘Leeds’ WHERE studentID = 107; 务必仔细检查 WHERE 条件，确保只有想要修改的记录受到影响。在考试中，他们会考查你编写具有特定针对性的更新语句的能力。
9. Removing Records with DELETE | 使用 DELETE 删除记录
DELETE removes rows from a table. The basic form is DELETE FROM tablename WHERE condition; Like UPDATE, forgetting the WHERE clause will delete all rows from the table, leaving an empty table, so tread carefully.
DELETE 用于从表中删除行。其基本形式为 DELETE FROM tablename WHERE condition; 和 UPDATE 一样，若忘记加 WHERE 子句，将删除表中的所有行，只留下空表，因此必须谨慎操作。
Example: delete the record of the student with studentID 107.
示例：删除学号为 107 的学生记录。
DELETE FROM Students WHERE studentID = 107;
You cannot delete specific columns with DELETE; you can only delete entire rows. If you need to remove a specific value from a cell, you would use UPDATE to set that column to NULL (or an empty string), but NULL handling is beyond the IGCSE scope. Focus on row deletion using primary key or unique conditions.
你不能用 DELETE 来删除特定的列，只能删除整行。如果需要删除某个单元格中的特定值，应使用 UPDATE 将该列设置为 NULL（或空字符串），但 NULL 的处理超出了 IGCSE 范围。重点应放在使用主键或唯一条件进行行删除上。
10. Creating and Modifying Table Structure (Data Definition) | 创建与修改表结构（数据定义）
Although most IGCSE OCR questions focus on data manipulation (SELECT, INSERT, UPDATE, DELETE), you may also encounter Data Definition Language (DDL) commands. The most important is CREATE TABLE, used to define a new table and its columns.
尽管大部分 IGCSE OCR 试题侧重于数据操作（SELECT、INSERT、UPDATE、DELETE），但你也有可能遇到数据定义语言 (DDL) 命令。其中最重要的是 CREATE TABLE，用于定义新表及其列。
A typical CREATE TABLE statement specifies the column name, data type and any constraints such as PRIMARY KEY or NOT NULL:
一个典型的 CREATE TABLE 语句会指定列名、数据类型以及任何约束，例如 PRIMARY KEY 或 NOT NULL：
CREATE TABLE Students (studentID INTEGER PRIMARY KEY, name VARCHAR(50), age INTEGER, city VARCHAR(30));
You should be familiar with data types: INTEGER (whole number), VARCHAR(n) (variable‑length text up to n characters), DATE and BOOLEAN. NOT NULL means the field cannot be left empty. Primary key implies NOT NULL and unique. Another command, ALTER TABLE, can add or remove columns, but is less frequently examined.
你应该熟悉这些数据类型：INTEGER（整数）、VARCHAR(n)（最多包含 n 个字符的可变长度文本）、DATE 和 BOOLEAN。NOT NULL 表示该字段不能为空。主键则隐含了 NOT NULL 和唯一性。另一个命令 ALTER TABLE 可用于添加或删除列，但考查得较少。
11. Exam Tips and Common Pitfalls | 考试技巧与常见误区
When answering SQL questions, always write the keywords in capitals (e.g. SELECT, FROM, WHERE) – this is not strictly necessary but improves clarity. Place the semicolon at the end of each statement. Use single quotes for text values. Never put quotes around numbers.
在回答 SQL 题目时，始终将关键字大写（例如 SELECT、FROM、WHERE）——这虽然不是硬性要求，但能提高清晰度。在每个语句结尾加上分号。文本值使用单引号。不要用引号把数字括起来。
Common mistakes include: forgetting the WHERE clause in UPDATE or DELETE, using = to check for NULL (NULL must use IS NULL), mixing up LIKE patterns, and using incorrect comparison operators. OCR questions often provide a table description – annotate it and identify the relevant columns before writing your SQL.
常见错误包括：在 UPDATE 或 DELETE 中忘记添加 WHERE 子句，使用 = 来检查 NULL（NULL 必须使用 IS NULL），混淆 LIKE 模式，以及使用错误的比较运算符。OCR 试题通常会提供表结构的描述——在编写 SQL 之前，先做好标注并找出相关的列。
When you see a question asking for ‘all records where…’, think SELECT *. For ‘find the names of…’, list only the name column. Pay attention to the exact wording: ‘more than’ uses >, ‘at least’ uses >=. For ‘list in alphabetical order’, use ORDER BY column ASC.
当你看到题目要求“所有…的记录”，就要想到 SELECT *。若要求“找出…的姓名”，则只需列出姓名列。注意题目的准确措辞：“超过”用 >，“至少”用 >=。若要“按字母顺序列出”，则使用 ORDER BY column ASC。
Practice writing SQL by hand on paper. In the exam, you won’t have autocomplete, so you must memorise the syntax. A simple memorisation trick: order of clauses – SELECT, FROM, (JOIN), WHERE, (GROUP BY), ORDER BY – but IGCSE OCR only uses SELECT, FROM, WHERE, ORDER BY.
练习在纸上手写 SQL。在考场上没有自动补全功能，所以你必须牢记语法。一个简单的记忆技巧：子句的顺序——SELECT, FROM, (JOIN), WHERE, (GROUP BY), ORDER BY——不过 IGCSE OCR 只用到 SELECT, FROM, WHERE, ORDER BY。
12. Bringing It All Together: A Sample Exam Question | 综合演练：一道模拟试题
Let’s apply the concepts to a typical exam scenario. Table: Books with columns: BookID (integer, primary key), Title (text), Author (text), Genre (text), Price (real number).
让我们把这些概念应用到一个典型的考试场景中。数据表：Books，列包括：BookID（整数，主键），Title（文本），Author（文本），Genre（文本），Price（实数）。
Question 1: Write an SQL statement to display the titles and authors of all books in the ‘Fantasy’ genre, sorted by title alphabetically.
问题 1：编写一条 SQL 语句，显示所有类别为 ‘Fantasy’ 的图书的书名和作者，并按书名字母顺序排列。
SELECT Title, Author FROM Books WHERE Genre = ‘Fantasy’ ORDER BY Title ASC;
Question 2: Increase the price of all books written by ‘J.K. Rowling’ by 2.50.
问题 2：将作者为 ‘J.K. Rowling’ 的所有图书价格提高 2.50。
UPDATE Books SET Price = Price + 2.50 WHERE Author = ‘J.K. Rowling’;
Note that you can use arithmetic inside SET. Question 3: Delete the book with BookID 105.
注意，你可以在 SET 中使用算术运算。问题 3：删除 BookID 为 105 的图书。
DELETE FROM Books WHERE BookID = 105;
By practising such questions, you will internalise the patterns and perform confidently in the exam. Remember, SQL is a structured language – once you understand the logic, it becomes second nature.
通过练习这类题目，你将内化这些模式并在考试中充满信心。请记住，SQL 是一种结构化的语言——一旦理解了其逻辑，它就会变得得心应手。
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课程辅导，国外大学本科硕士研究生博士课程论文辅导