数据库

A database 数据库 is an organised store of data, kept so that it is easy to search, sort and update. At IGCSE you work with a single-table database 单表数据库 — all the data is held in one table.

A database table is made of records and fields.

• A record 记录 is one row in the table — all the data about one thing (for example one student).
• A field 字段 is one column in the table — one piece of data that every record has (for example "First name").

Here each row is a record, and each column is a field.

Each field stores one data type 数据类型. You choose the type that best fits the data.

Worked example. Choose the data type for each field of a Member table.

A phone number looks like a number, but it is a common exam trap: stored as an integer it would lose its first 0, and no one adds phone numbers together.

A primary key 主键 is a field that holds a unique 唯一的 value for every record. No two records can have the same primary key, so it lets you pick out exactly one record.

In the table above, StudentID is a good primary key because every student has a different number. A field like FormClass would be a bad primary key, because many students share the same class.

Worked example. Which field of the Member table above should be the primary key? MemberID, because each member is given a different one. Age, Fee and Joined repeat; even a name such as Surname is a poor choice, because two members can share a surname. A good answer names the field and says it is unique for every record.

When data is put into a database, validation 验证 checks make sure it is sensible — for example a range check on an age field, or a presence check so a field is not left empty. (You saw these checks in topic 7.)

Structured Query Language 结构化查询语言 (SQL) is a language used to query 查询 a database — to pick out the records you want. You must understand and complete SQL scripts.

SELECT, FROM and WHERE

• SELECT says which fields to show.
• FROM says which table to use.
• WHERE gives a condition 条件, so only matching records are shown.

SELECT FirstName, FormClass
FROM Student
WHERE FeesPaid = TRUE;

This shows the first name and class of every student who has paid the fees.

Use * to select all fields:

SELECT *
FROM Student
WHERE FormClass = '10A';

ORDER BY

ORDER BY sorts the results. Use ASC for ascending 升序 (smallest first, A→Z) or DESC for descending 降序 (largest first, Z→A).

SELECT FirstName, DateOfBirth
FROM Student
ORDER BY DateOfBirth ASC;

AND and OR

Join conditions with AND (both must be true) or OR (at least one must be true).

SELECT FirstName
FROM Student
WHERE FormClass = '10A' AND FeesPaid = FALSE;

SUM and COUNT

• SUM adds up the values in a number field.
• COUNT counts how many records match.

SELECT COUNT(StudentID)
FROM Student
WHERE FeesPaid = FALSE;

This counts how many students have not paid. SUM works the same way but adds a number field instead of counting rows.

SELECT SUM(Fee)
FROM Member
WHERE Paid = TRUE;

This adds up the Fee of every member who has paid and shows one number. SUM needs a number field (integer or real); COUNT can count any field.

Working out the output

To find the output of an SQL script, read it in this order:

1. FROM — which table;
2. WHERE — keep only the records that match the condition;
3. SELECT — show only the chosen fields;
4. ORDER BY — put the results in order.

Following these steps, you can write down exactly which rows and columns the query returns.

Worked example. The Plant table:

Give the output of:

SELECT Name, Price
FROM Plant
WHERE Type = 'shrub' AND Height < 200
ORDER BY Price DESC;

1. FROM: the Plant table.
2. WHERE: shrubs are P01, P03 and P05; Holly is 300 tall, so only Rose and Lavender pass.
3. SELECT: only Name and Price.
4. ORDER BY Price DESC: the dearest first.

SELECT COUNT(PlantID) FROM Plant WHERE Type = 'herb'; would give 2, and SELECT SUM(Price) FROM Plant WHERE Type = 'herb'; would give 3.50.

Worked example. A Book table has the fields Title, Author, Price and InStock. Write a query showing the title and price of every book by Orwell that is in stock, cheapest first.

SELECT Title, Price
FROM Book
WHERE Author = 'Orwell' AND InStock = TRUE
ORDER BY Price ASC;

Build it in the reading order: FROM names the table; WHERE keeps only the matching records, and because there are two conditions they need AND; SELECT shows only the two fields asked for; ORDER BY … ASC sorts them. Text values go in quotes, and only the fields the question asks for belong in SELECT - adding Author just because you filtered on it is the commonest way to lose a mark here.

• A record is a row (all the data about one thing); a field is a column (one item that every record has).
• A primary key must be unique for every record, so it picks out exactly one record (StudentID, not FormClass).
• Learn the SQL parts: SELECT (which fields), FROM (which table), WHERE (the condition), ORDER BY (sort, ASC or DESC).
• Read a query in the order FROM → WHERE → SELECT → ORDER BY to work out its output.
• COUNT counts the matching records; SUM adds up a number field.