📚 SQL Key Concepts for IB & CIE Computer Science | IB CIE 计算机：SQL 考点精讲
Structured Query Language (SQL) is the universal language for managing and querying relational databases. For IB and CIE Computer Science students, mastering SQL is essential not only for exam success but also for understanding how data-driven applications work. This guide breaks down every key topic you need to know, from basic SELECT statements to complex joins and subqueries, all explained with clear examples.
结构化查询语言（SQL）是管理和查询关系数据库的通用语言。对于 IB 和 CIE 计算机科学的学生来说，掌握 SQL 不仅是考试成功的关键，也是理解数据驱动应用如何运作的基础。本指南将逐一解析你需要掌握的每个重要考点，从基础的 SELECT 语句到复杂的连接和子查询，均配有清晰的示例讲解。
1. Introduction to SQL | SQL 简介
SQL is a declarative language used to communicate with relational databases. It allows users to define, manipulate, and query data without worrying about low-level storage details. In both IB and CIE syllabuses, SQL appears as a core topic under database management, and exam questions often ask you to write queries or interpret given SQL code. SQL statements are divided into two main categories: Data Definition Language (DDL) for creating and modifying database structures, and Data Manipulation Language (DML) for inserting, updating, deleting, and querying data.
SQL 是一种声明式语言，用于与关系数据库进行通信。它使用户能够定义、操作和查询数据，而无需关心底层的存储细节。在 IB 和 CIE 课程中，SQL 是数据库管理部分的核心考点，试题常常要求编写查询语句或解释给定的 SQL 代码。SQL 语句主要分为两大类：数据定义语言（DDL），用于创建和修改数据库结构；以及数据操作语言（DML），用于插入、更新、删除和查询数据。
2. Data Definition Language (DDL) | 数据定义语言
DDL commands let you shape the structure of a database. The most frequently examined commands are CREATE TABLE, ALTER TABLE, and DROP TABLE. When creating a table, you must specify column names, data types (e.g., INTEGER, VARCHAR, DATE, BOOLEAN), and any constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, and UNIQUE. For example, the statement CREATE TABLE Student (id INTEGER PRIMARY KEY, name VARCHAR(50) NOT NULL, dob DATE); defines a simple student table. CIE exams often expect you to recognise data types suitable for given fields, while IB may ask for a full CREATE TABLE script including foreign key references.
DDL 命令用于塑造数据库的结构。最常考查的命令是 CREATE TABLE、ALTER TABLE 和 DROP TABLE。在创建表时，必须指定列名、数据类型（例如 INTEGER、VARCHAR、DATE、BOOLEAN）以及各种约束，如 PRIMARY KEY、FOREIGN KEY、NOT NULL 和 UNIQUE。例如，语句 CREATE TABLE Student (id INTEGER PRIMARY KEY, name VARCHAR(50) NOT NULL, dob DATE); 定义了一个简单的学生表。CIE 考试通常期待你为给定的字段选择合适的数据类型，而 IB 可能要求写出包含外键引用的完整 CREATE TABLE 脚本。
3. Data Manipulation Language (DML) | 数据操作语言
DML deals with the data itself. The four fundamental commands are INSERT INTO, UPDATE, DELETE, and SELECT. INSERT adds new rows; UPDATE modifies existing rows based on a condition; DELETE removes rows. A typical exam task is to write an UPDATE statement to change a specific value, for instance, UPDATE Student SET grade= ‘A’ WHERE id=101;. Missing the WHERE clause would update all rows, a common mistake that questions love to highlight. IB and CIE both test your ability to craft correct DML with precise conditions.
DML 处理的是数据本身。四个基本的命令是 INSERT INTO、UPDATE、DELETE 和 SELECT。INSERT 添加新行；UPDATE 根据条件修改现有行；DELETE 删除行。典型的考试任务是编写 UPDATE 语句来更改特定值，例如 UPDATE Student SET grade= ‘A’ WHERE id=101;。如果遗漏 WHERE 子句，将会更新所有行，这是题目经常强调的常见错误。IB 和 CIE 都会考查你编写带有精确条件的正确 DML 语句的能力。
4. SELECT Statement Fundamentals | SELECT 语句基础
The SELECT statement is the heart of data retrieval. Its basic syntax is SELECT column1, column2 FROM table;. To return all columns, you use SELECT * FROM table;, though in exams it is often preferable to list specific columns for clarity. You can also use the DISTINCT keyword to eliminate duplicate rows, e.g., SELECT DISTINCT city FROM Customer;. CIE often includes questions that require selecting computed expressions, such as SELECT name, price*1.2 AS price_with_vat FROM Product;, testing your understanding of aliases (AS) and arithmetic operations.
SELECT 语句是数据检索的核心。其基本语法为 SELECT column1, column2 FROM table;。若要返回所有列，可使用 SELECT * FROM table;，但在考试中，为了清晰起见，列出具体的列通常更好。你还可以使用 DISTINCT 关键字来消除重复行，例如 SELECT DISTINCT city FROM Customer;。CIE 考试经常包含要求选择计算表达式的题目，如 SELECT name, price*1.2 AS price_with_vat FROM Product;，以此考查你对别名（AS）和算术运算的理解。
5. Filtering with WHERE Clause | 使用 WHERE 子句进行筛选
The WHERE clause filters rows based on conditions. Common operators include =, <>, <, >, <=, >=, BETWEEN, LIKE, IN, IS NULL, and logical operators AND, OR, NOT. For example, SELECT * FROM Book WHERE price BETWEEN 10 AND 20 AND category = ‘Fiction’; returns fiction books in a specific price range. The LIKE operator supports pattern matching: WHERE name LIKE ‘A%’ finds names starting with ‘A’, and LIKE ‘_o%’ finds names whose second character is ‘o’. Both IB and CIE frequently examine wildcard characters (%) and underscore (_) usage.
WHERE 子句根据条件筛选行。常用运算符包括 =、<>、<、>、<=、>=、BETWEEN、LIKE、IN、IS NULL 以及逻辑运算符 AND、OR、NOT。例如，SELECT * FROM Book WHERE price BETWEEN 10 AND 20 AND category = ‘Fiction’; 返回指定价格范围内的小说类书籍。LIKE 运算符支持模式匹配：WHERE name LIKE ‘A%’ 查找以 ‘A’ 开头的名称，LIKE ‘_o%’ 查找第二个字符是 ‘o’ 的名称。IB 和 CIE 都经常考查通配符（%）和下划线（_）的使用。
6. Sorting Results with ORDER BY | 使用 ORDER BY 对结果排序
The ORDER BY clause sorts query results in ascending (ASC, the default) or descending (DESC) order. You can sort by multiple columns: SELECT name, score FROM Student ORDER BY score DESC, name ASC;. This orders by score descending, and if scores tie, by name ascending. Exams often ask you to predict the output order for a given ORDER BY, or to write a query that returns the top-N results. While standard SQL uses LIMIT or FETCH FIRST for top-N, many syllabuses still expect you to understand the concept even if they limit the syntax to ORDER BY alone.
ORDER BY 子句用于对查询结果进行升序（ASC，默认值）或降序（DESC）排序。你可以按多个列进行排序：SELECT name, score FROM Student ORDER BY score DESC, name ASC; 会先按分数降序排列，当分数相同时按姓名升序排列。考试经常要求你预测给定 ORDER BY 的输出顺序，或编写返回前 N 条结果的查询。尽管标准 SQL 使用 LIMIT 或 FETCH FIRST 来获取前 N 条，但许多大纲即使句法仅限于 ORDER BY，仍然期望你理解相关概念。
7. Aggregate Functions | 聚合函数
Aggregate functions perform calculations on a set of rows and return a single value. The five most important ones are COUNT, SUM, AVG, MAX, and MIN. For instance, SELECT COUNT(*) FROM Order WHERE status= ‘Shipped’; counts shipped orders. SELECT AVG(price) FROM Product WHERE category= ‘Electronics’; computes the average price of electronic items. Note that COUNT(*) counts all rows including nulls, whereas COUNT(column) ignores nulls. IB questions often ask you to combine aggregate functions with GROUP BY, while CIE likes to test aggregate functions in the context of subqueries.
聚合函数对一组行执行计算，并返回一个单一值。最重要的五个聚合函数是 COUNT、SUM、AVG、MAX 和 MIN。例如，SELECT COUNT(*) FROM Order WHERE status= ‘Shipped’; 统计已发货订单的数量。SELECT AVG(price) FROM Product WHERE category= ‘Electronics’; 计算电子产品类别的平均价格。请注意，COUNT(*) 计算所有行，包括包含空值的行，而 COUNT(column) 会忽略空值。IB 的题目经常要求将聚合函数与 GROUP BY 结合使用，而 CIE 喜欢在子查询的上下文中考查聚合函数。
8. Grouping Data with GROUP BY and HAVING | 使用 GROUP BY 和 HAVING 进行分组
GROUP BY groups rows that share the same values in specified columns, allowing aggregate functions to be applied per group. For example, SELECT department, COUNT(*) AS employee_count FROM Employee GROUP BY department; returns the number of employees in each department. The HAVING clause filters groups after aggregation, unlike WHERE which filters rows before grouping: SELECT department, AVG(salary) FROM Employee GROUP BY department HAVING AVG(salary) > 50000;. This is a classic exam trap—students often confuse WHERE and HAVING. Both IB and CIE explicitly test this distinction.
GROUP BY 将在指定列上共享相同值的行分组，从而允许对每个组应用聚合函数。例如，SELECT department, COUNT(*) AS employee_count FROM Employee GROUP BY department; 返回每个部门的员工人数。HAVING 子句在聚合之后过滤分组，这与 WHERE 在分组之前过滤行不同：SELECT department, AVG(salary) FROM Employee GROUP BY department HAVING AVG(salary) > 50000;。这是一个经典的考试陷阱——学生经常混淆 WHERE 和 HAVING。IB 和 CIE 都会明确考查这一区别。
9. Joining Tables | 连接表
Relational databases store data across multiple tables, so joining them is essential. The most common joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. INNER JOIN returns rows where there is a match in both tables; LEFT JOIN returns all rows from the left table and matching rows from the right. A typical on-paper question might be: “Write a query to list all customers and their orders, including customers with no orders.” That requires a LEFT JOIN: SELECT Customer.name, Order.order_id FROM Customer LEFT JOIN Order ON Customer.id = Order.customer_id;. CIE commonly includes join conditions with multiple criteria, while IB asks you to justify join choices.
关系数据库将数据存储在多个表中，因此连接它们是必不可少的。最常见的连接有 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN。INNER JOIN 返回两个表中都存在匹配的行；LEFT JOIN 返回左表的所有行，以及右表中的匹配行。典型的纸上题目可能是：“编写查询列出所有客户及其订单，包括没有订单的客户。”这需要一个 LEFT JOIN：SELECT Customer.name, Order.order_id FROM Customer LEFT JOIN Order ON Customer.id = Order.customer_id;。CIE 常常包含具有多个条件的连接条件，而 IB 会要求你证明连接选择的合理性。
10. Subqueries (Nested Queries) | 子查询（嵌套查询）
A subquery is a query placed inside another SQL statement, usually within WHERE, FROM, or SELECT clauses. Subqueries can return single values, a list, or even a whole table. A common example is finding employees who earn more than the average salary: SELECT name FROM Employee WHERE salary > (SELECT AVG(salary) FROM Employee);. When a subquery returns a list, you must use operators like IN, ANY, or ALL. CIE often tests correlated subqueries, where the inner query depends on the outer query’s row, while IB emphasises logical reasoning with subqueries as an alternative to joins.
子查询是放置于另一个 SQL 语句内部的查询，通常出现在 WHERE、FROM 或 SELECT 子句中。子查询可以返回单个值、一个列表，甚至整个表。一个常见的例子是查询收入高于平均工资的员工：SELECT name FROM Employee WHERE salary > (SELECT AVG(salary) FROM Employee);。当子查询返回一个列表时，你必须使用诸如 IN、ANY 或 ALL 等运算符。CIE 经常考查相关子查询，即内层查询依赖于外层查询当前行的情形，而 IB 则强调将子查询作为连接的一种替代方案进行逻辑推理。
11. Entity Relationship Modelling and Normalisation | 实体关系建模与规范化
Although not strictly SQL, understanding entity relationship diagrams (ERDs) and normalisation is tested alongside SQL in both IB and CIE. You need to identify entities, attributes, relationships, and keys, and to reduce data redundancy through normalisation up to Third Normal Form (3NF). For example, a table that contains repeating groups should be decomposed into separate linked tables using foreign keys. You might be asked to explain how a normalised design improves data integrity and how SQL queries can then efficiently retrieve related data through joins.
虽然严格来说这不属于 SQL，但理解实体关系图（ERD）和规范化在 IB 和 CIE 考试中是与 SQL 一起考查的。你需要识别实体、属性、关系和键，并通过规范化直到第三范式（3NF）来减少数据冗余。例如，包含重复组的表应该通过外键分解为单独的关联表。你可能会被要求解释规范化设计如何提高数据完整性，以及 SQL 查询如何通过连接高效地检索相关数据。
12. Common Exam Pitfalls and Tips | 常见考试陷阱与应试技巧
Examiners often design questions to test subtle details. Watch out for: forgetting to quote string values; using = with NULL (always use IS NULL or IS NOT NULL); confusing WHERE and HAVING; missing foreign key definitions in CREATE TABLE; and using aggregate functions incorrectly without GROUP BY. When faced with a query-writing question, first identify the required tables, then determine whether joins are needed, next apply filtering conditions, and finally decide on grouping and sorting. Writing a short plan before the full query can save you from losing marks on syntax slips.
考官常会设计题目来测试细节。要留意：忘记为字符串值加引号；对 NULL 使用 =（始终应使用 IS NULL 或 IS NOT NULL）；混淆 WHERE 和 HAVING；在 CREATE TABLE 中遗漏外键定义；以及在没有 GROUP BY 的情况下错误使用聚合函数。面对一道查询编写题时，首先确定要使用的表，然后判断是否需要连接，接着应用筛选条件，最后决定分组与排序。在写出完整查询前先列一个简短的计划，可以避免你因语法失误而丢分。
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课程辅导，国外大学本科硕士研究生博士课程论文辅导