Querying with SQL · 用 SQL 查询
| English | 中文 | Pinyin · 拼音 |
|---|---|---|
| SQL/ˌes kjuː ˈel/ | 结构化查询语言 | jié gòu huà chá xún yǔ yán |
| query/ˈkwɪərɪ/ | 查询 | chá xún |
| record/ˈrekɔːd/ | 记录 | jì lù |
| field/fiːld/ | 字段 | zì duàn |
| condition/kənˈdɪʃn/ | 条件 | tiáo jiàn |
Asking a database questions
- SQL 结构化查询语言 (Structured Query 查询 Language) picks out the records 记录 you want.
- A few keywords do almost everything at IGCSE.
- Read a query in a fixed order to work out its output.
向一个数据库提问
- SQL(结构化查询语言)挑出你想要的记录。
- 几个关键字在 IGCSE 几乎做一切。
- 以一个固定的顺序读一个查询以弄清它的输出。
SELECT, FROM, WHERE
SELECT= which fields 字段 to show (use*for all).FROM= which table.WHERE= a condition 条件, so only matching records appear.
Read an SQL query in this order: FROM (which table), WHERE (which rows), SELECT (which fields), ORDER BY (sort).
SELECT picks fields, FROM names the table, WHERE sets the condition
SELECT、FROM、WHERE
SELECT FirstName, FormClass
FROM Student
WHERE FeesPaid = TRUE;
SELECT= 显示哪些字段(用*表示全部)。FROM= 哪个表。WHERE= 一个条件,所以只有匹配的记录出现。
以这个顺序读一个 SQL 查询:FROM(哪个表)、WHERE(哪些行)、SELECT(哪些字段)、ORDER BY(排序)。
Run a SELECT … WHERE query · 运行一个 SELECT … WHERE 查询
WHERE filters the rows to those that match; SELECT then keeps only the columns you chose. Step through to watch the table shrink to the query's answer. · WHERE 把行过滤为匹配的;SELECT 然后只保留你选的列。逐步看表缩小为查询的答案。
In an SQL query, SELECT specifies: · 在一个 SQL 查询中,SELECT 指定:
SELECT chooses the fields; FROM the table; WHERE the rows; ORDER BY the sort. · SELECT 选择字段;FROM 选择表;WHERE 选择行;ORDER BY 选择排序。
The WHERE clause: · WHERE 子句:
WHERE filters rows by a condition. · WHERE 按一个条件过滤行。
ORDER BY, AND, OR
ORDER BYsorts:ASC(smallest/A→Z first) orDESC(largest/Z→A first).- Join conditions with
AND(both true) orOR(at least one true).
A range check accepts sensible values and rejects the rest
ORDER BY、AND、OR
SELECT FirstName, DateOfBirth
FROM Student
WHERE FormClass = '10A' AND FeesPaid = FALSE
ORDER BY DateOfBirth ASC;
ORDER BY排序:ASC(最小/A→Z 在前)或DESC(最大/Z→A 在前)。- 用
AND(两者都真)或OR(至少一个真)连接条件。
ORDER BY mark DESC sorts the results: · ORDER BY mark DESC 给结果排序:
DESC = descending (largest/Z→A first); ASC = ascending. · DESC = 降序(最大/Z→A 在前);ASC = 升序。
SUM, COUNT, and reading order
COUNTcounts matching records;SUMadds up a number field.- To work out a query's output, read it in this order:
- FROM (which table) → WHERE (keep matching rows) → SELECT (show chosen fields) → ORDER BY (sort).
Field types: text, number, date/time and boolean
SUM、COUNT 和阅读顺序
COUNT计数匹配的记录;SUM把一个数字字段加起来。- 要弄清一个查询的输出,以这个顺序读它:
- FROM(哪个表)→ WHERE(保留匹配的行)→ SELECT(显示选中的字段)→ ORDER BY(排序)。
SELECT COUNT(StudentID) FROM Student WHERE FeesPaid = FALSE;
Match each SQL keyword to what it does. · 把每个 SQL 关键字匹配到它做什么。
SELECT picks columns, WHERE filters rows, ORDER BY sorts, COUNT tallies the matching rows. · SELECT 挑选列,WHERE 过滤行,ORDER BY 排序,COUNT 统计匹配的行。
Put the SQL clauses in the order you read them to find the output. · 把 SQL 子句按你为找到输出而阅读它们的顺序排列。
FROM → WHERE → SELECT → ORDER BY gives you exactly which rows and columns appear. · FROM → WHERE → SELECT → ORDER BY 给你恰好哪些行和列出现。
You've got it
SELECT(fields) ·FROM(table) ·WHERE(condition);*= all fieldsORDER BY … ASC/DESCsorts;AND/ORjoin conditionsCOUNTcounts rows;SUMtotals a number field- read a query: FROM → WHERE → SELECT → ORDER BY
你掌握了
SELECT(字段)·FROM(表)·WHERE(条件);*= 所有字段ORDER BY … ASC/DESC排序;AND/OR连接条件COUNT计数行;SUM求和一个数字字段- 读一个查询:FROM → WHERE → SELECT → ORDER BY