9 · Databases
9 · 数据库
Single-table databases and SQL
单一表数据库与 SQL
One table, made of records and fields, with a primary key to tell records apart. SQL then pulls out exactly the rows and columns you ask for. The 0478 SQL you need is a short, fixed list of commands.
一张由记录和字段组成的表,配有主键来区分不同的记录。SQL 则能精确地取出你所要求的行和列。0478 所需的 SQL 是一份简短、固定的命令列表。
Explainer · 9.1
讲解 · 9.1
Fields, records, and a single table
字段、记录与单一表
A garden nursery keeps its stock in one table. Each plant on sale is one row; each thing recorded about a plant (its name, its height, its price) is one column. That is all a single-table database is: one table of data about one kind of thing.
一家园艺苗圃把库存记录在一张表中。每一株在售的植物是一行;每一项关于该植物的记录(名称、高度、价格)是一列。这就是单一表数据库的全部含义:一张关于某一类事物的数据表。
A record is one row: everything the table records about one plant (or one customer, one booking). A field is one column: one specific piece of data, held for every record. Every record has the same fields, so the number of fields is fixed while the number of records grows and shrinks.
记录是一行:表中记录的关于一株植物(或一位顾客、一次预订)的所有信息。字段是一列:一项特定的数据,每条记录都有。每条记录的字段都相同,所以字段的数量是固定的,而记录的数量会增减。
A single-table database holds exactly one table. Relational databases with several linked tables are an A Level topic.
单一表数据库恰好包含一张表。含有多张相互关联的表的关系型数据库,属于 A Level 的内容。
| PlantID | CommonName | Category | HeightCM | Price | InFlower | DateAdded |
|---|---|---|---|---|---|---|
| P01 | Bottlebrush | Shrub | 90 | 12.50 | TRUE | 2026-02-10 |
| P02 | Lemon Myrtle | Tree | 150 | 24.00 | FALSE | 2026-02-12 |
| P03 | Kangaroo Paw | Perennial | 45 | 9.75 | TRUE | 2026-02-12 |
| P04 | Lilly Pilly | Shrub | 120 | 15.00 | FALSE | 2026-03-01 |
| P05 | Wonga Vine | Climber | 30 | 11.20 | TRUE | 2026-03-01 |
| P06 | Flame Tree | Tree | 200 | 38.00 | FALSE | 2026-03-05 |
| P07 | Coastal Rosemary | Shrub | 60 | 8.50 | TRUE | 2026-03-09 |
| P08 | Native Violet | Perennial | 10 | 6.00 | TRUE | 2026-03-09 |
Field names are a single word with no spaces (DateAdded, not “Date Added”), and each one is chosen to say what it holds.
字段名是不含空格的单个词(应写作 DateAdded,而非"Date Added"),而且每个字段名都要能说明它保存的内容。
Validation is a rule the database checks as data is entered, to stop unreasonable data getting in: a presence check (the field cannot be left blank), a range check (HeightCM must be 1 to 500), a format check (PlantID must be a letter then two digits), a length check, a type check. Some checks the database software applies automatically from the field’s data type; others the developer sets up.
验证是数据库在数据输入时检查的一条规则,用于阻止不合理的数据被录入:存在性检查(该字段不能留空)、范围检查(HeightCM 必须在 1 到 500 之间)、格式检查(PlantID 必须是一个字母后跟两个数字)、长度检查、类型检查。有些检查由数据库软件根据字段的数据类型自动施加;另一些则由开发者自行设置。
Explainer · 9.2
讲解 · 9.2
Giving every field a data type
为每个字段指定数据类型
Every field is given one data type. It decides how the value is stored and displayed, and what you can do with it: a number field can be added and compared numerically, a text field cannot. The 0478 syllabus names six.
每个字段都被赋予一种数据类型。它决定了该值如何存储与显示,以及你能对它做什么:数字字段可以相加并按数值比较,文本字段则不能。0478 考纲列出了六种类型。
Click a field to see its data type and why.
点击一个字段,查看它的数据类型及原因。
| Field | 字段 | Example value | 示例值 |
|---|---|---|---|
Select a field above. The six 0478 types are text/alphanumeric, character (a single character), Boolean, integer, real and date/time.
在上方选择一个字段。0478 的六种类型是文本/字母数字型、字符型(单个字符)、布尔型、整数型、实数型和日期/时间型。
Explainer · 9.3
讲解 · 9.3
The primary key
主键
To act on one record, the database has to be able to pick it out from all the others with certainty. The field that makes that possible is the primary key.
要对某一条记录进行操作,数据库必须能够确切地把它从所有其他记录中挑选出来。能做到这一点的字段就是主键。
A primary key is a field whose value is never repeated in the table, so it uniquely identifies one record. In PLANT that is PlantID: every plant has its own code.
主键是一个在表中的值从不重复的字段,因此它能唯一标识一条记录。在 PLANT 中,这个字段是 PlantID:每株植物都有自己的编码。
Look for a field that must be different for every record: a code, an ID, a membership number. CommonName looks tempting but the nursery could stock two Bottlebrushes, so it is not safe. Price and HeightCM clearly repeat. When no real field is guaranteed unique, a new field (an ID) is added to be the key.
要寻找一个必须对每条记录都不同的字段:一个代码、一个 ID、一个会员编号。CommonName 看起来诱人,但苗圃可能同时进两株瓶刷树,所以并不安全。Price 和 HeightCM 显然会重复。当没有真正保证唯一的字段时,就添加一个新字段(一个 ID)来充当键。
Not a primary key: the word Field or Column. Markers reported candidates naming the table’s heading row instead of an actual field.
不是主键:写"字段"或"列"这样的词。考官报告称考生说出的是表的标题行,而不是一个实际的字段。
Explainer · 9.4
讲解 · 9.4
SQL: the commands you need
SQL:你需要掌握的命令
SQL (Structured Query Language) writes a query that pulls chosen data out of a table. Every query starts with SELECT and FROM; everything else is optional. The 0478 syllabus limits you to this list.
SQL(结构化查询语言)编写一条查询,从表中取出选定的数据。每条查询都以 SELECT 和 FROM 开头;其余部分都是可选的。0478 考纲把范围限定在以下这份列表内。
| Command | 命令 | Does | 作用 |
|---|---|---|---|
| SELECT | Chooses which fields (columns) to show. SELECT * shows every field. Every query begins here. |
选择要显示哪些字段(列)。SELECT * 显示所有字段。每条查询都从这里开始。 | |
| FROM | Names the table to read. Mandatory. | 指明要读取的表。必须写出。 | |
| WHERE | Keeps only the records (rows) that match a condition, for example WHERE Price > 10. Text values go in quotes: WHERE Category = 'Tree'. |
只保留符合某个条件的记录(行),例如 WHERE Price > 10。文本值要加引号:WHERE Category = 'Tree'。 | |
| ORDER BY | Sorts the results by a field. Add ASC for ascending (the default) or DESC for descending. |
按某个字段对结果排序。加上 ASC 表示升序(默认),加上 DESC 表示降序。 | |
| SUM | Totals one number field across the matching records: SELECT SUM(Price). Always written inside a SELECT. |
对匹配记录中的一个数字字段求总和:SELECT SUM(Price)。总是写在 SELECT 内。 | |
| COUNT | Gives how many records match: SELECT COUNT(PlantID). Always written inside a SELECT. |
给出有多少条记录匹配:SELECT COUNT(PlantID)。总是写在 SELECT 内。 | |
| AND / OR | Join conditions in a WHERE. AND needs both true; OR needs at least one. |
在 WHERE 中连接多个条件。AND 要求两者都为真;OR 只需要至少一个为真。 |
SELECT picks the columns, WHERE picks the rows. They are easy to mix up. SELECT CommonName, Price decides what to show; WHERE Category = 'Shrub' decides which records get shown. Comparison operators: =, <> (not equal), <, >, <=, >=.
SELECT 挑选列,WHERE 挑选行。这两者很容易混淆。SELECT CommonName, Price 决定显示什么;WHERE Category = 'Shrub' 决定显示哪些记录。比较运算符:=、<>(不等于)、<、>、<=、>=。
SQL questions are answered in SQL, on paper. You read a query and state its output, or complete a partly-written query. Keywords and field names must be spelled exactly.
SQL 题目要用 SQL 在纸上作答。你需要读懂一条查询并说出它的输出,或补全一条只写了一部分的查询。关键字和字段名必须完全按拼写写对。
Worked example · 9.4
示例讲解 · 9.4
Reading a query and its output
解读一条查询及其输出
Given the PLANT table above, here is a query and exactly what it returns.
基于上方的 PLANT 表,下面是一条查询以及它准确返回的结果。
SELECT CommonName, Price FROM PLANT WHERE Category = 'Shrub' AND Price < 13.00 ORDER BY Price DESC;
| CommonName | Price |
|---|---|
| Bottlebrush | 12.50 |
| Coastal Rosemary | 8.50 |
Three shrubs exist (Bottlebrush 12.50, Lilly Pilly 15.00, Coastal Rosemary 8.50). AND Price < 13.00 drops Lilly Pilly. SELECT shows only the two named fields. ORDER BY Price DESC puts the dearer one first.
共有三种灌木(Bottlebrush 12.50、Lilly Pilly 15.00、Coastal Rosemary 8.50)。AND Price < 13.00 排除了 Lilly Pilly。SELECT 只显示两个指定的字段。ORDER BY Price DESC 把价格更高的排在前面。
SELECT COUNT(PlantID) FROM PLANT WHERE InFlower = TRUE;
This returns a single value, 5: it counts the records where InFlower is true (P01, P03, P05, P07, P08), rather than listing them.
这会返回单一的值 5:它统计的是 InFlower 为真的记录数量(P01、P03、P05、P07、P08),而不是把它们列出来。
Try it yourself
动手试试
Run your own queries
运行你自己的查询
The PLANT table, live. Type a query and run it. The console only accepts the 0478 SQL scope, so if you reach for LIKE, BETWEEN, GROUP BY or an INSERT, it tells you they are outside the syllabus rather than just failing.
实时的 PLANT 表。输入一条查询并运行它。控制台只接受 0478 SQL 范围内的语法,所以如果你使用了 LIKE、BETWEEN、GROUP BY 或 INSERT,它会告诉你这些不在考纲范围内,而不是直接失败。
Check your understanding
检验你的理解
What does this output? SELECT CommonName, HeightCM FROM PLANT WHERE Category = 'Tree' ORDER BY HeightCM;
这会输出什么?SELECT CommonName, HeightCM FROM PLANT WHERE Category = 'Tree' ORDER BY HeightCM;
Check your understanding
检验你的理解
Which field is the only safe choice for the primary key of PLANT?
对于 PLANT 的主键,唯一安全的选择是哪个字段?
Common mistakes
常见错误
9.2 / 9.3 / 9.4
考官针对 9.2 / 9.3 / 9.4 报告了以下模式
RecurringIn a WHERE condition, markers reported candidates leaving the quotation marks off a text value: WHERE Category = Tree instead of WHERE Category = 'Tree'. Without the quotes the database reads Tree as a field name, and there is no such field.
反复出现在 WHERE 条件中,考官报告称考生漏掉了文本值的引号:写成 WHERE Category = Tree 而不是 WHERE Category = 'Tree'。没有引号,数据库会把 Tree 当作字段名读取,而并不存在这样的字段。
On data types, markers reported the programming word “string” given instead of the database word “text”, and candidates using a type that is not one of the six in the syllabus, or the same type for every field without thinking.
在数据类型方面,考官报告称考生用编程用语"字符串"来代替数据库用语"文本",也有考生使用了不在考纲六种类型之内的类型,或未经思考就给每个字段都用同一种类型。
On primary keys, candidates named the heading word “Field”, or picked a field that can repeat. And on SQL output questions: an incorrect field in the SELECT line, or spelling a keyword or field name wrongly.
在主键方面,考生说出的是标题词"字段",或选择了一个可能重复的字段。而在 SQL 输出题中:SELECT 行中的字段有误,或者关键字、字段名拼写错误。
Text value with no quotes: SELECT * FROM PLANT WHERE Category = Shrub;
文本值没有引号: SELECT * FROM PLANT WHERE Category = Shrub;
Quoted text value: SELECT * FROM PLANT WHERE Category = 'Shrub';. Numbers and TRUE/FALSE take no quotes; text and dates do.
加了引号的文本值: SELECT * FROM PLANT WHERE Category = 'Shrub';。数字和 TRUE/FALSE 不加引号;文本和日期需要加引号。
Wrong vocabulary: giving the data type of CommonName as “string”, or of Price as “float” or “double”.
用词错误:把 CommonName 的数据类型说成"字符串",或把 Price 说成"float"或"double"。
Syllabus terms: CommonName is text/alphanumeric, Price is real, InFlower is Boolean, DateAdded is date/time.
考纲用语:CommonName 是文本/字母数字型,Price 是实数型,InFlower 是布尔型,DateAdded 是日期/时间型。
Revision questions
复习题
Past-paper style. Write an answer, then reveal the marking points and mark yourself.
采用历年真题风格。写下答案,然后展开评分要点给自己打分。
[2]The PLANT table has 8 records and 7 fields. State what is meant by a record and by a field.
[2]PLANT 表有 8 条记录和 7 个字段。陈述"记录"和"字段"分别指的是什么。
Marking points
评分要点
- A record is one row of the table: all the data held about one plant.
- A field is one column: one item of data held for every record.
- 记录是表中的一行:关于一株植物所保存的全部数据。
- 字段是一列:为每条记录保存的某一项数据。
[3]State a suitable data type for HeightCM, Price and InFlower, using the terms from the syllabus.
[3]使用考纲中的术语,陈述适合 HeightCM、Price 和 InFlower 的数据类型。
Marking points
评分要点
- HeightCM: integer (the heights are whole centimetres) - real is also acceptable.
- Price: real (the prices have decimal places).
- InFlower: Boolean (TRUE / FALSE).
- HeightCM:整数型(高度是整厘米)- 实数型也可接受。
- Price:实数型(价格有小数位)。
- InFlower:布尔型(TRUE / FALSE)。
[2]Identify a suitable primary key for the PLANT table and give a reason for your choice.
[2]为 PLANT 表识别一个合适的主键,并给出你选择的理由。
Marking points
评分要点
- Primary key: PlantID.
- Reason: its value is different for every record (never repeated), so it uniquely identifies one plant. CommonName could repeat (two of the same plant), so it is not safe.
- 主键:PlantID。
- 理由:它的值对每条记录都不同(从不重复),所以能唯一标识一株植物。CommonName 可能重复(两株相同的植物),所以不安全。
[3]Write the output of: SELECT CommonName FROM PLANT WHERE HeightCM > 100 ORDER BY CommonName;
[3]写出以下查询的输出:SELECT CommonName FROM PLANT WHERE HeightCM > 100 ORDER BY CommonName;
Marking points
评分要点
- The CommonName of every plant with a HeightCM over 100, sorted alphabetically by CommonName:
- Flame Tree, Lemon Myrtle, Lilly Pilly.
- 每一株 HeightCM 大于 100 的植物的 CommonName,按 CommonName 字母顺序排列:
- Flame Tree、Lemon Myrtle、Lilly Pilly。
[4]Complete an SQL query to count how many plants are in the ‘Shrub’ category.
[4]补全一条 SQL 查询,统计有多少株植物属于"Shrub"类别。
Marking points
评分要点
- SELECT COUNT(PlantID) FROM PLANT WHERE Category = 'Shrub';
- The result is 3 (Bottlebrush, Lilly Pilly, Coastal Rosemary).
- Marks: COUNT( ) with a field or *; FROM PLANT; WHERE Category = 'Shrub' with the quotes; correct value 3.
- SELECT COUNT(PlantID) FROM PLANT WHERE Category = 'Shrub';
- 结果是 3(Bottlebrush、Lilly Pilly、Coastal Rosemary)。
- 评分点:COUNT( ) 带一个字段或 *;FROM PLANT;WHERE Category = 'Shrub' 带引号;正确的值 3。
Flashcards
记忆卡片
Active recall for this topic’s key terms.
针对本主题关键术语的主动回忆练习。
Keep this topic’s practice with you
把这个主题的练习保存下来
Free account: unlock every revision question and flashcard for Topic 9, and every other IGCSE 0478 statement.
免费账户:解锁主题 9 以及所有其他 IGCSE 0478 考纲陈述的全部复习题和记忆卡片。