📚 IB Computer Science: SQL Key Points Review | IB 计算机：SQL 考点精讲
Structured Query Language (SQL) is the backbone of modern database management and forms a vital part of the IB Computer Science curriculum. Whether you are tackling Paper 2 or preparing for the internal assessment, a solid grasp of SQL syntax, relational database concepts, and data manipulation is essential. This revision guide systematically covers every key SQL topic required by the IB syllabus, from foundational SELECT queries to advanced JOIN operations and data definition commands. Each section pairs clear English explanations with parallel Chinese summaries to cater to bilingual learners and help you master the content efficiently.
结构化查询语言（SQL）是现代数据库管理的核心，也是IB计算机科学课程的重要组成部分。无论你是在准备Paper 2还是内部评估，扎实掌握SQL语法、关系数据库概念以及数据操作都是至关重要的。本复习指南系统覆盖了IB大纲要求的每一个关键SQL主题，从基础的SELECT查询到高级的JOIN操作和数据定义命令。每个部分都将清晰的英文解释与对应的中文总结配对，以满足双语学习者的需求，帮助你高效掌握这些内容。
1. Relational Databases & Tables | 关系数据库与表
In the relational model, data is organised into relations (tables) consisting of rows (records) and columns (attributes). Each table represents an entity type, and each row represents a unique instance of that entity. The order of rows and columns is not significant, and all entries in a column share the same data domain. IB exam questions often ask students to identify relations from a given scenario and describe how tables are structured.
在关系模型中，数据被组织成由行（记录）和列（属性）组成的关系（表）。每个表代表一种实体类型，每一行代表该实体的一个唯一实例。行和列的顺序并不重要，列中的所有条目共享相同的数据域。IB考题经常要求学生从给定场景中识别关系，并描述表的结构。
A well-designed table should avoid repeating groups and ensure each attribute contains atomic values. For example, a Student table might have columns StudentID, Name, DateOfBirth, and Email. These simple, flat tables enable efficient querying using SQL.
一个设计良好的表应避免重复组，并确保每个属性包含原子值。例如，学生表可能有StudentID、Name、DateOfBirth和Email列。这些简单、扁平的表能够使用SQL进行高效查询。
2. Primary Keys & Foreign Keys | 主键与外键
A primary key is a column or combination of columns that uniquely identifies each row in a table. It must be unique and not null. In IB, you will often define a primary key using an auto-increment integer (e.g., StudentID INT PRIMARY KEY AUTO_INCREMENT) or a candidate key such as a passport number. The primary key ensures entity integrity.
主键是唯一标识表中每一行的一个列或列组合。它必须唯一且不为空。在IB中，你通常会使用自增整数（例如StudentID INT PRIMARY KEY AUTO_INCREMENT）或护照号码等候选键来定义主键。主键确保实体完整性。
A foreign key is a column in one table that refers to the primary key of another table. It establishes a link between the two tables and enforces referential integrity. For instance, an Enrolment table may have a foreign key StudentID referencing Student(StudentID). When updating or deleting parent records, actions such as CASCADE or SET NULL must be considered to maintain consistency.
外键是一个表中的列，它引用另一个表的主键。它在两个表之间建立联系，并强制引用完整性。例如，Enrolment表可以有一个外键StudentID引用Student(StudentID)。当更新或删除父记录时，必须考虑CASCADE或SET NULL等操作以保持一致性。
3. Basic SELECT Queries | 基本SELECT查询
The SELECT statement retrieves data from one or more tables. The most basic syntax is: SELECT column1, column2 FROM table; To select all columns, use SELECT * FROM table; IB candidates must be comfortable with writing simple retrieval queries and interpreting their output.
SELECT语句从一个或多个表中检索数据。最基本的语法是：SELECT column1, column2 FROM table; 要选择所有列，使用SELECT * FROM table; IB考生必须能够编写简单的检索查询并解释其输出。
Aliases can be used via the AS keyword to rename columns in the output: SELECT StudentID AS ID, Name AS SName FROM Student; This improves readability and is common in exam answers.
可以使用AS关键字为输出中的列重命名：SELECT StudentID AS ID, Name AS SName FROM Student; 这提高了可读性，在考试答案中很常见。
4. WHERE Clause & Conditional Filtering | WHERE子句与条件过滤
The WHERE clause allows filtering rows based on specified conditions. Comparisons use operators like =, <>, >, <, >=, <=. For example: SELECT * FROM Student WHERE DateOfBirth > ‘2006-01-01’; You can combine multiple conditions with AND, OR, and NOT. IB exams frequently test logical operator precedence and bracket usage.
WHERE子句允许根据指定条件过滤行。比较使用=、<>、>、<、>=、<=等运算符。例如：SELECT * FROM Student WHERE DateOfBirth > ‘2006-01-01’; 你可以用AND、OR和NOT组合多个条件。IB考试经常测试逻辑运算符优先级和括号的使用。
Be careful with NULL values; use IS NULL or IS NOT NULL instead of = NULL. For example: SELECT * FROM Student WHERE Email IS NOT NULL; This returns only students with an email address.
小心NULL值；使用IS NULL或IS NOT NULL，而不是= NULL。例如：SELECT * FROM Student WHERE Email IS NOT NULL; 这将仅返回有电子邮件地址的学生。
5. LIKE, BETWEEN, IN Operators | LIKE、BETWEEN、IN运算符
The LIKE operator is used for pattern matching with wildcards: % matches any sequence of characters, _ matches a single character. For example: SELECT * FROM Student WHERE Name LIKE ‘A%’ finds all students whose names start with ‘A’. BETWEEN simplifies range checks: WHERE Grade BETWEEN 6 AND 7 is equivalent to WHERE Grade >= 6 AND Grade <= 7.
LIKE运算符用于模式匹配，配合通配符：%匹配任意字符序列，_匹配单个字符。例如：SELECT * FROM Student WHERE Name LIKE ‘A%’ 查找所有姓名以’A’开头的学生。BETWEEN简化范围检查：WHERE Grade BETWEEN 6 AND 7 相当于 WHERE Grade >= 6 AND Grade <= 7。
The IN operator checks if a value matches any item in a list: WHERE Subject IN (‘Math’, ‘Physics’, ‘Computer Science’). This is cleaner than multiple OR conditions and is frequently used in IB queries.
IN运算符检查值是否匹配列表中的任何项目：WHERE Subject IN (‘Math’, ‘Physics’, ‘Computer Science’)。这比多个OR条件更简洁，在IB查询中经常使用。
6. Aggregate Functions: COUNT, SUM, AVG, MAX, MIN | 聚合函数：COUNT、SUM、AVG、MAX、MIN
SQL provides five main aggregate functions that operate on a set of values and return a single result. COUNT(*) returns the number of rows; COUNT(column) counts non-null entries. SUM(column) totals numeric values, AVG(column) computes the average, while MAX(column) and MIN(column) find extreme values. In IB exams, you may need to combine these with GROUP BY to produce summaries per group.
SQL提供了五个主要的聚合函数，它们对一组值进行操作并返回单一结果。COUNT(*)返回行数；COUNT(column)计数非空条目。SUM(column)对数值求和，AVG(column)计算平均值，而MAX(column)和MIN(column)找出极值。在IB考试中，你可能需要将这些函数与GROUP BY结合使用，以生成每组摘要。
Important: aggregate functions ignore NULLs except COUNT(*). When using DISTINCT, COUNT(DISTINCT column) counts unique values. For example: SELECT COUNT(DISTINCT Subject) FROM Enrolment; returns the number of different subjects enrolled.
重要提示：聚合函数忽略NULL值，但COUNT(*)除外。使用DISTINCT时，COUNT(DISTINCT column)计算唯一值的数量。例如：SELECT COUNT(DISTINCT Subject) FROM Enrolment; 返回已注册的不同科目数量。
7. GROUP BY & HAVING | GROUP BY与HAVING
GROUP BY partitions rows into groups based on one or more columns. It is almost always used with aggregate functions to compute group-level statistics. For example: SELECT Subject, COUNT(*) AS EnrolmentCount FROM Enrolment GROUP BY Subject; returns the number of students per subject. Without GROUP BY, aggregates would operate on the entire table.
GROUP BY根据一个或多个列将行划分为组。它几乎总是与聚合函数一起使用以计算组级统计信息。例如：SELECT Subject, COUNT(*) AS EnrolmentCount FROM Enrolment GROUP BY Subject; 返回每个科目的学生人数。如果没有GROUP BY，聚合将作用于整个表。
HAVING acts like a WHERE clause but filters groups after aggregation. For instance: SELECT Subject, COUNT(*) AS cnt FROM Enrolment GROUP BY Subject HAVING COUNT(*) > 5; displays only subjects with more than five students. Where vs. Having: WHERE filters individual rows; HAVING filters groups. This distinction is a classic IB exam pitfall.
HAVING的作用类似于WHERE子句，但在聚合之后过滤组。例如：SELECT Subject, COUNT(*) AS cnt FROM Enrolment GROUP BY Subject HAVING COUNT(*) > 5; 仅显示学生人数超过五人的科目。Where与Having的区别：WHERE过滤单独的行；HAVING过滤组。这个区别是IB考试的经典陷阱。
8. ORDER BY & DISTINCT | 排序与去重：ORDER BY和DISTINCT
The ORDER BY clause sorts the result set. Default order is ascending (ASC); use DESC for descending. You can sort by multiple columns: SELECT * FROM Student ORDER BY YearLevel ASC, Name DESC; IB mark schemes often require correct ordering based on given criteria.
ORDER BY子句对结果集进行排序。默认顺序是升序（ASC）；降序使用DESC。你可以按多列排序：SELECT * FROM Student ORDER BY YearLevel ASC, Name DESC; IB评分方案通常要求根据给定标准正确排序。
DISTINCT eliminates duplicate rows from the result. Place DISTINCT immediately after SELECT: SELECT DISTINCT Subject FROM Enrolment; It applies to all selected columns combined. When combined with ORDER BY, the sort columns must appear in the SELECT list.
DISTINCT从结果中消除重复行。将DISTINCT紧跟在SELECT之后：SELECT DISTINCT Subject FROM Enrolment; 它适用于所有选定列的组合。当与ORDER BY结合使用时，排序列必须出现在SELECT列表中。
9. Table Joins: INNER, LEFT, RIGHT | 表连接：INNER、LEFT、RIGHT
Joins combine rows from two or more tables based on a related column. The most common is the INNER JOIN, which returns only rows with matching values in both tables. Syntax: SELECT … FROM TableA INNER JOIN TableB ON TableA.key = TableB.key; For example, to list student names with their subjects: SELECT Student.Name, Enrolment.Subject FROM Student INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID;
连接（Join）基于相关列将两个或多个表的行组合在一起。最常见的是INNER JOIN，它仅返回两个表中匹配值存在的行。语法：SELECT … FROM TableA INNER JOIN TableB ON TableA.key = TableB.key; 例如，列出学生姓名及其科目：SELECT Student.Name, Enrolment.Subject FROM Student INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID;
LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matched rows from the right table. Unmatched right columns are filled with NULL. RIGHT JOIN works conversely. IB may ask for a query that includes all students even if they have no enrolments—this requires a LEFT JOIN. Fully understand these to handle scenarios like “include all customers, even those without orders”.
LEFT JOIN（或LEFT OUTER JOIN）返回左表的所有行以及右表中匹配的行。未匹配的右表列用NULL填充。RIGHT JOIN则反之。IB可能会要求一个查询，即使学生没有注册任何科目也要包含所有学生——这需要使用LEFT JOIN。充分理解这些连接以处理诸如“包含所有客户，即使是那些没有订单的客户”的场景。
10. Data Manipulation: INSERT, UPDATE, DELETE | 数据操作：INSERT、UPDATE、DELETE
INSERT adds new rows: INSERT INTO Student (StudentID, Name, DOB) VALUES (101, ‘Alice’, ‘2007-05-14’); When inserting values for all columns in order, the column list can be omitted, but it is safer to include it. IB may ask you to write an INSERT based on given data.
INSERT添加新行：INSERT INTO Student (StudentID, Name, DOB) VALUES (101, ‘Alice’, ‘2007-05-14’); 当按顺序为所有列插入值时，可以省略列列表，但包含列列表更安全。IB可能会要求你根据给定数据编写INSERT语句。
UPDATE modifies existing rows: UPDATE Student SET Email = ‘alice@school.edu’ WHERE StudentID = 101; Without a WHERE clause, all rows would be updated—a critical mistake to avoid. DELETE removes rows: DELETE FROM Student WHERE StudentID = 101; Like UPDATE, forgetting the WHERE condition leads to total data loss. Always tie DELETE and UPDATE to specific primary key values in exam answers.
UPDATE修改现有行：UPDATE Student SET Email = ‘alice@school.edu’ WHERE StudentID = 101; 如果没有WHERE子句，所有行都将被更新——这是一个必须避免的关键错误。DELETE删除行：DELETE FROM Student WHERE StudentID = 101; 与UPDATE一样，忘记WHERE条件会导致全部数据丢失。在考试答案中，始终将DELETE和UPDATE与特定的主键值关联。
11. Data Definition: CREATE, ALTER, DROP | 数据定义：CREATE、ALTER、DROP
Data Definition Language (DDL) commands define and modify the structure of database objects. CREATE TABLE is fundamental: CREATE TABLE Student ( StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, DOB DATE ); IB expects you to include appropriate data types (INT, VARCHAR, DATE, DECIMAL, BOOLEAN) and constraints like NOT NULL, UNIQUE, DEFAULT.
数据定义语言（DDL）命令定义和修改数据库对象的结构。CREATE TABLE是基础：CREATE TABLE Student ( StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, DOB DATE ); IB期望你包含适当的数据类型（INT、VARCHAR、DATE、DECIMAL、BOOLEAN）以及类似NOT NULL、UNIQUE、DEFAULT的约束。
ALTER TABLE modifies an existing table: to add a column ALTER TABLE Student ADD Email VARCHAR(100); to drop a column ALTER TABLE Student DROP COLUMN Email; (syntax may vary). DROP TABLE permanently removes a table and all its data: DROP TABLE Student; These commands appear frequently in the Database option of Paper 2.
ALTER TABLE修改现有表：添加列 ALTER TABLE Student ADD Email VARCHAR(100); 删除列 ALTER TABLE Student DROP COLUMN Email;（语法可能有所不同）。DROP TABLE永久删除表及其所有数据：DROP TABLE Student; 这些命令经常出现在Paper 2的数据库选项中。
12. Normalization & Data Redundancy | 规范化与数据冗余
Normalization is the process of organising data to reduce redundancy and improve integrity. IB introduces the three normal forms (1NF, 2NF, 3NF). 1NF requires atomic values and no repeating groups. 2NF builds on 1NF and requires that non-key attributes are fully dependent on the entire primary key (no partial dependencies). 3NF removes transitive dependencies, where non-key attributes depend on other non-key attributes.
规范化是组织数据以减少冗余并提高完整性的过程。IB介绍了三种范式（1NF、2NF、3NF）。1NF要求原子值和没有重复组。2NF在1NF的基础上要求非键属性完全依赖于整个主键（无部分依赖）。3NF消除传递依赖，即非键属性依赖于其他非键属性。
Denormalization is sometimes performed intentionally for performance gains in read-heavy systems, but IB questions typically focus on identifying anomalies (insertion, deletion, update) that arise from unnormalized designs and explaining how normalization resolves them. Be prepared to normalise a given table to 3NF by splitting it into new relations and defining primary and foreign keys.
反规范化有时为了在读取密集型系统中提高性能而有意实施，但IB问题通常聚焦于识别非规范化设计产生的异常（插入、删除、更新），并解释规范化如何解决这些异常。准备好将给定表格规范化到3NF，将其拆分为新关系并定义主键和外键。
Published by TutorHao | IB Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导