Databases
IGCSE Computer Science Topic 9 8:17 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Before computers, a library kept a drawer like this.
在计算机出现之前，图书馆有这样一排抽屉。
One card per book, each holding the same few facts, all kept in order — so a librarian could find any book in seconds.
每本书一张卡片， 每张卡片记录同样的几项信息，全部按顺序排好——所以图书管理员几秒钟就能找到任何一本书。
That is a database: an organised store of data, easy to search, to sort and to update.
这就是数据库：一个有组织的数据存储，便于查找、排序和更新。
Today the drawers have gone and the job is done by machines like these.
今天抽屉不见了，这份工作交给了这样的机器。
The idea has not changed.
想法一点也没有变。
Here is the plan.
这是今天的安排。
First the shape of a table: records and fields.
先看表的结构：记录和字段。
Then the data type of each field.
然后是每个字段该用的数据类型。
Then the primary key, the field that tells records apart.
接着是主键，也就是把每条记录区分开的那个字段。
Then validation, which stops bad data getting in.
然后是验证，它挡住错误的数据。
And last, SQL, the language for asking a table questions — that part carries the most marks.
最后是 SQL，用来向表提问的语言——这一部分分值最高。
And remember: at this level everything lives in one table.
还要记住：在这个阶段，所有数据都放在一张表里。
Here is a single-table database — a Student table.
这是一个单表数据库——一张 Student 表。
Read it two ways.
它要从两个方向读。
Across, each row is a record: everything the table knows about one student.
横着看，每一行是一条记录：表里关于某一个学生的全部信息。
Amy is one record, Ben is another.
Amy 是一条记录，Ben 是另一条。
Down, each column is a field: one piece of information every record has.
竖着看，每一列是一个字段：每条记录都有的某一项信息。
Say it now — a record is a row, a field is a column.
现在自己说一遍——记录是行，字段是列。
Students lose marks every year by swapping them.
每年都有学生因为把这两个词说反而丢分。
And notice the shaded first column: we come back to it.
再注意左边那一列有底色的字段：我们等一下回来讲它。
Every field is given a data type — the kind of value it may hold.
每个字段都要指定一个数据类型——也就是它可以存放哪一类值。
The syllabus names six.
考纲列了六种。
Text, also called alphanumeric, holds letters, digits and symbols.
文本，也叫字母数字型，可以存字母、数字和符号。
Character holds exactly one letter, like M or F.
字符只存一个字母，比如 M 或 F。
Boolean holds one of only two values.
布尔值只有两个取值。
Integer holds a whole number, like a mark.
整数存整的数字，比如分数。
Real holds a number with a decimal point.
实数存带小数点的数字。
And date and time holds a date or a time.
日期时间存一个日期或者时间。
Now the trap. A phone number is text, not an integer: it can begin with a zero, and you never do arithmetic on it.
现在讲一个坑：电话号码是文本，不是整数—— 它可以以零开头，而且你绝不会拿它做算术。
Now that shaded column.
现在回到那一列。
A primary key is the field that uniquely identifies each record.
主键是能唯一标识每一条记录的字段。
No two records may ever share its value.
任何两条记录都不能有相同的主键值。
Give me a StudentID and I find exactly one student.
给我一个 StudentID，我就能找出唯一的那个学生。
Good keys are made for the job: a student number, an ISBN, an order number.
好的主键都是为此设计的： 学号、ISBN、订单号。
A key is never blank and never repeated.
主键不能空着，也不能重复。
And a name is a poor key, because two real people do share the name Li Wei — which is why databases invent an ID field.
而姓名是很差的主键，因为现实中真的有两个人都叫李伟——所以数据库才要另外造一个 ID 字段。
Now choose one.
现在来选一个。
A library stores its books in this table: Title, Author, Price and ISBN.
图书馆用这张表存放它的书：Title、Author、Price 和 ISBN。
Which is the primary key?
哪一个是主键？
Try Author first.
先看 Author。
One author writes many books, so the value repeats.
一个作者会写很多本书，值会重复。
Price?
Price 呢？
Two books easily cost the same.
两本书很容易卖一样的价钱。
Title, then?
那 Title 呢？
Careful: different books share titles, and a library often holds two copies of one book.
小心：不同的书会同名， 而且图书馆常常收着同一本书的两册。
ISBN is the answer — no two books share one.
答案是 ISBN——任何两本书都不会相同。
And the test is always the same question: could two records ever hold the same value?
而判断的方法永远是同一个问题：会不会有两条记录取到相同的值？
Data has to get in, and people mistype.
数据要进到表里，而人打字会出错。
Validation is an automatic check made as data is entered; anything that fails is refused.
验证是在输入时自动做的检查，凡是不通过的一律拒绝。
A range check keeps a value between limits: an age from zero to a hundred and twenty, so sixteen is accepted and two hundred rejected.
范围检查把值限制在界限之间：年龄从零到一百二十，所以十六被接受，二百被拒绝。
A type check insists on a number, not letters.
类型检查要求是数字，不是字母。
A presence check refuses an empty field.
存在性检查不允许字段留空。
A format check demands the right pattern.
格式检查要求正确的写法。
One warning: validation only proves a value is possible, never that it is true.
一个提醒：验证只能证明一个值是可能的，永远不能证明它是真的。
The table is built and filled.
表已经建好、也装满了数据。
How do we ask it questions?
那我们怎么向它提问呢？
With SQL — structured query language.
用 SQL——结构化查询语言。
Here is a complete query, in three parts.
这是一条完整的查询，分三部分。
SELECT names the fields you want to see.
SELECT 说明你想看哪些字段。
FROM names the table you are reading.
FROM 说明你读的是哪张表。
And WHERE gives the condition.
WHERE 给出条件。
Two small things students drop: a single equals sign, not two, and a semicolon at the end.
有两个小地方学生常常漏掉：一个等号，不是两个；句末有一个分号。
Now read the query out in plain English: show the first name and form class of every student who has paid.
现在用大白话把它读出来：把每一个已经交费的学生的名字和班级显示出来。
Now watch that query run — the skill the exam tests.
现在看这条查询跑起来——这正是考试要考的能力。
Start with FROM: that gives us the whole Student table.
先看 FROM： 它把整张 Student 表交给我们。
Next comes WHERE: keep only the records whose fees paid value is true.
接着是 WHERE：只保留 FeesPaid 为真的记录。
Amy stays.
Amy 留下。
Ben is dropped, and a dropped record never comes back.
Ben 被去掉，而被去掉的记录不会再回来。
Then SELECT: of the records left, show only two fields.
然后是 SELECT：在剩下的记录里，只显示两个字段。
What remains is the output — three rows and two columns.
剩下的就是输出——三行、两列。
So WHERE chooses the rows and SELECT chooses the columns.
所以 WHERE 决定行，SELECT 决定列。
Two different jobs, and that split is worth half the SQL marks.
这是两件不同的事，而这个分工值 SQL 一半的分数。
When a question gives you a query and a table and asks what it prints, do not read from the top down.
如果题目给你一条查询和一张表，问它输出什么，不要从上往下读。
Read it in this order.
要按这个顺序读。
FROM first: which table are we in?
先看 FROM：我们在哪张表里？
Then WHERE: cross out every record that fails the condition.
然后是 WHERE：把每一条不满足条件的记录划掉。
Then SELECT: keep only the chosen columns.
然后是 SELECT：只留下选中的那几列。
And last, ORDER BY: put the surviving rows in order.
最后是 ORDER BY：把剩下的行排好顺序。
Always the same four steps, always in that order.
永远是这四步，永远是这个顺序。
Work through them with a pencil and the output falls out.
拿铅笔走一遍，输出自然就出来了。
ORDER BY sorts the output, and it is the step most often left off.
ORDER BY 给输出排序，而它是最常被漏掉的一步。
Look at this query: it lists the name and mark of every student above eighty, best first.
看这条查询：它列出每一个分数高于八十的学生的姓名和分数，最好的排在最前面。
The last line does that.
最后一行做的就是这件事。
DESC means descending — largest first, or Z back to A.
DESC 表示降序——大的在前，或者从 Z 回到 A。
ASC means ascending — smallest first, or A to Z.
ASC 表示升序——小的在前，或者从 A 到 Z。
And here is the catch: leave the word off and the database sorts ascending, so best first comes out backwards.
这里有个坑：把这个词漏掉，数据库就按升序排，于是最好的跑到了最后。
When a question says highest or newest first, write DESC.
题目说最高的或最新的在前，就写 DESC。
One condition is often not enough, so we join conditions with AND or OR.
一个条件常常不够用，于是用 AND 或者 OR 把条件连起来。
Here are five students, and two conditions: form class is ten A, and fees paid is false.
这里有五个学生，还有两个条件：班级是十 A，以及 FeesPaid 为假。
Join them with AND and both must be true, so only Eli survives — one record.
用 AND 连起来，两个条件都必须成立，所以只有 Eli 留下——一条记录。
Now join exactly the same two conditions with OR, and Amy, Ben, Chen and Eli all survive — four records.
现在用 OR 连同样这两个条件，于是 Amy、Ben、Chen 和 Eli 都留下——四条记录。
One word, and one became four.
一个词，一就变成了四。
Remember it like this: AND narrows the list down, OR widens it out.
这样记：AND 把范围收窄，OR 把范围放宽。
Students mix them up constantly.
学生总是把它们弄混。
Two more words, and both give you a single number instead of a list.
还有两个词，它们给你的都是一个数字，而不是一串记录。
COUNT counts how many records match.
COUNT 数的是有多少条记录符合条件。
COUNT of StudentID, from Student, where fees paid is false, answers how many students owe money — and the answer is a number, two, not a list.
对 StudentID 用 COUNT，从 Student 表， 取 FeesPaid 为假的记录，回答的就是有多少学生欠费——答案是一个数字，二，不是一串记录。
SUM adds up the values in a number field.
SUM 把某个数字字段里的值加起来。
SUM of Price, from Book, where in stock is true, gives the total value on the shelf.
对 Price 用 SUM，从 Book 表，取在库的书， 得到的是货架上的总价值。
Keep them apart: COUNT counts records, SUM adds a number field.
要分清楚：COUNT 数记录，SUM 加一个数字字段。
And you can never add up a column of names.
而且你永远没法把一列姓名加起来。
Everything in one question.
一道题涵盖整个专题。
Write a query showing the title and price of every book by Orwell that is in stock, cheapest first.
写一条查询，显示 Orwell 写的、并且在库的每一本书的书名和价格， 最便宜的排在最前面。
Pause the video and try it yourself.
先暂停视频，自己试一试。
Ready?
好了吗？
Build it in the reading order.
按阅读顺序来搭。
FROM Book names the table.
FROM Book 指明了表。
Then WHERE: two conditions, by Orwell and in stock, so they need AND; and Orwell is text, so it goes in quotes.
然后是 WHERE：两个条件，Orwell 写的、并且在库， 所以要用 AND 连起来；又因为 Orwell 是文本，要放在引号里。
Then SELECT: Title and Price, only those two.
然后是 SELECT：Title 和 Price，只要这两个。
Adding Author is the commonest lost mark here.
把 Author 也加进去，是这里最常见的失分。
And finally ORDER BY Price ascending — cheapest first means ascending.
最后是 ORDER BY Price 升序—— 最便宜的在前就是升序。
Four marks students throw away.
四个学生白白丢掉的分。
One: a record is a row and a field is a column — never the other way round.
第一，记录是行，字段是列——绝不能反过来。
Two: a primary key must be unique for every record, so StudentID, never FormClass.
第二，主键对每一条记录都必须唯一，所以用 StudentID，不要用 FormClass。
Three: WHERE chooses the rows and SELECT chooses the columns; text values go in quotes.
第三，WHERE 决定行，SELECT 决定列；文本值要放在引号里。
Four: when a question says highest, best or newest first, write DESC — leaving it off costs the mark.
第四，题目说最高的、最好的、最新的排在前面时，就写 DESC——漏掉就丢一分。
Get those four right and this topic is yours.
这四点做对，这个专题就是你的了。