SQL — DDL and DML · SQL——DDL 与 DML
| English | 中文 | Pinyin · 拼音 |
|---|---|---|
| SQL/ˌes kjuː ˈel/ | 结构化查询语言 | jié gòu huà chá xún yǔ yán |
| Data Definition Language/ˈdeɪtə ˌdefɪˈnɪʃn ˈlæŋɡwɪdʒ/ | 数据定义语言 | shù jù dìng yì yǔ yán |
| Data Manipulation Language/ˈdeɪtə məˌnɪpjʊˈleɪʃn ˈlæŋɡwɪdʒ/ | 数据操纵语言 | shù jù cāo zòng yǔ yán |
| INNER JOIN/ˈɪnə dʒɔɪn/ | 连接 | lián jiē |
| aggregate functions/ˈæɡrɪɡeɪt ˈfʌŋkʃnz/ | 聚合函数 | jù hé hán shù |
The language your grandparents' programmers also used
- In 1974 two IBM researchers designed a query language for Codd's tables and called it SEQUEL: Structured English Query Language. The name was trimmed to SQL 结构化查询语言, and the language never went away.
- Fifty years later, every bank, airline, hospital and website you use runs on it. A student typing
SELECTtoday is writing the same statement a programmer wrote before her parents were born. - It has two halves, and the exam asks you to read either and to write both: the Data Definition Language 数据定义语言 that builds the structure, and the Data Manipulation Language 数据操纵语言 that fills, changes and questions the data.
- This lesson is the subset of SQL on the syllabus, statement by statement, with the marks each one carries.
你祖父母那一代程序员也在用的语言
- 1974 年,两位 IBM 研究员为 Codd 的表设计了一种查询语言,叫 SEQUEL:结构化英语查询语言。名字被缩成 SQL(结构化查询语言),而这门语言再也没有离开。
- 五十年后,你用的每家银行、航空公司、医院和网站都运行在它上面。今天敲下
SELECT的学生,写的正是一位程序员在她父母出生前写过的同一条语句。 - 它有两半,考试要你能读其中任何一半、写出两半:构建结构的数据定义语言(Data Definition Language),和填充、修改、询问数据的数据操纵语言(Data Manipulation Language)。
- 这一课讲大纲上的 SQL 子集,一条一条语句,带上各自的得分点。
DDL and DML
- The DBMS carries out all creation and modification of the database's structure through its DDL: creating a database, creating and altering tables, adding keys.
- It carries out all queries and maintenance of the data through its DML: selecting, inserting, updating and deleting rows.
- SQL is the industry standard for both. Sorting a statement into the right half is a common one-mark question:
CREATE TABLEis DDL,SELECTis DML.
Structure on one side, data on the other
DDL 和 DML
- DBMS 通过它的 DDL 完成对数据库结构的所有创建和修改:创建数据库、创建和修改表、添加键。
- 它通过它的 DML 完成对数据的所有查询和维护:选取、插入、更新和删除行。
- SQL 是两者的行业标准。把一条语句归到正确的那一半是常见的一分题:
CREATE TABLE是 DDL,SELECT是 DML。
一边是结构,一边是数据
Which is part of the Data Definition Language (DDL)? · 哪个是数据定义语言(DDL)的一部分?
DDL changes the structure (CREATE, ALTER, DROP). SELECT/INSERT/UPDATE/DELETE are DML (working with data). · DDL 改变结构(CREATE、ALTER、DROP)。SELECT/INSERT/UPDATE/DELETE 是 DML(处理数据)。
DDL defines the structure (e.g. CREATE TABLE), while DML works with the data inside it (SELECT, INSERT, UPDATE, DELETE). · DDL 定义结构(例如 CREATE TABLE),而 DML 处理里面的数据(SELECT、INSERT、UPDATE、DELETE)。
Definition vs Manipulation: DDL shapes the tables; DML reads and changes the rows. · 定义对操作:DDL 塑造表;DML 读和改变行。
DDL: creating the structure
- Data types on the syllabus:
CHARACTER(a fixed number of characters),VARCHAR(n)(up to n characters),BOOLEAN,INTEGER,REAL,DATE,TIME. PRIMARY KEY (field)names the key;ALTER TABLE … ADDadds an attribute to an existing table.
DDL:创建结构
CREATE DATABASE Shop;
CREATE TABLE CUSTOMER (
CustomerID INTEGER,
Name VARCHAR(50),
Town VARCHAR(30),
Joined DATE,
Active BOOLEAN,
PRIMARY KEY (CustomerID)
);
ALTER TABLE CUSTOMER ADD Email VARCHAR(100);
- 大纲上的数据类型:
CHARACTER(固定个数的字符)、VARCHAR(n)(最多 n 个字符)、BOOLEAN、INTEGER、REAL、DATE、TIME。 PRIMARY KEY (字段)指定键;ALTER TABLE … ADD给已有的表添加属性。
Match each item of data to the SQL data type for it. · 把每项数据与它的 SQL 数据类型配对。
Two states, variable-length text, a number with a fractional part, a calendar date. INTEGER is for whole counts and TIME for a time of day. · 两种状态、变长文本、带小数部分的数、日历日期。INTEGER 用于整数计数,TIME 用于一天中的时刻。
Worked example: two tables with a foreign key
- Write the SQL to create an
ORDERStable withOrderIDas primary key,CustomerIDas a foreign key toCUSTOMER, and anOrderDate.
- The marks:
CREATE TABLEwith the name; each attribute with a suitable type; thePRIMARY KEY; theFOREIGN KEY … REFERENCESnaming the table and its field.
例题:带外键的两张表
- 编写 SQL 创建
ORDERS表,以OrderID为主键、CustomerID为指向CUSTOMER的外键,并有OrderDate。
CREATE TABLE ORDERS (
OrderID INTEGER,
CustomerID INTEGER,
OrderDate DATE,
PRIMARY KEY (OrderID),
FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);
- 得分点:带表名的
CREATE TABLE;每个属性有合适的类型;PRIMARY KEY;FOREIGN KEY … REFERENCES同时写出表和它的字段。
DML: asking a question with SELECT
SELECTlists the fields to output (*for all),FROMnames the table,WHEREkeeps only the rows that meet a condition,ORDER BYsorts the result,ASCorDESC.- Strings go in single quotes; numbers do not. Comparisons:
=,<,>,<=,>=,<>; conditions join withAND,OR,NOT;LIKE 'A%'matches text starting with A;BETWEEN 10 AND 20gives a range.
Only the rows that pass the WHERE reach the result
DML:用 SELECT 提问
SELECT Name, Town
FROM CUSTOMER
WHERE Town = 'London'
ORDER BY Name ASC;
SELECT列出要输出的字段(*表示全部),FROM指定表,WHERE只保留满足条件的行,ORDER BY给结果排序,ASC或DESC。- 字符串放在单引号里;数字不加。比较:
=、<、>、<=、>=、<>;条件用AND、OR、NOT连接;LIKE 'A%'匹配以 A 开头的文本;BETWEEN 10 AND 20给出范围。
只有通过 WHERE 的行才进入结果
Which SQL keyword retrieves data from a table? (one word) · 哪个 SQL 关键字从一个表中检索数据?(一个词)
SELECT lists the fields to retrieve; FROM names the table. · SELECT 列出要检索的字段;FROM 命名表。
The WHERE clause in a SELECT statement: · 一个 SELECT 语句中的 WHERE 子句:
WHERE filters rows by a condition; ORDER BY sorts; the SELECT list chooses columns. · WHERE 按一个条件过滤行;ORDER BY 排序;SELECT 列表选择列。
Worked example: write the query
- Write an SQL script to output the names and email addresses of all active customers in Manchester, in alphabetical order of name.
- One mark each: the right fields after
SELECT; the right table afterFROM; theWHEREwith both conditions and the string in single quotes;ORDER BY Name. Use the exact table and field names the question gives.
例题:写出查询
- 编写 SQL 脚本,按姓名字母顺序输出曼彻斯特所有活跃客户的姓名和电子邮件地址。
SELECT Name, Email
FROM CUSTOMER
WHERE Town = 'Manchester' AND Active = TRUE
ORDER BY Name;
- 各一分:
SELECT后正确的字段;FROM后正确的表;带两个条件、字符串加单引号的WHERE;ORDER BY Name。使用题目给出的精确表名和字段名。
Put the clauses of a SELECT statement in the order they are written. · 把 SELECT 语句的各子句按书写顺序排列。
Fields, table, filter, sort. ORDER BY is always last, and the statement ends with a semicolon. · 字段、表、过滤、排序。ORDER BY 永远在最后,语句以分号结束。
Two tables: INNER JOIN
- An INNER JOIN 连接 combines the rows of two tables where the foreign key in one matches the primary key in the other, named in the
ONclause. - Prefix a field with its table when the same name appears in both. The syllabus asks for queries over at most two tables.
两张表:INNER JOIN
SELECT CUSTOMER.Name, ORDERS.OrderDate
FROM CUSTOMER INNER JOIN ORDERS
ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE ORDERS.OrderDate >= '2024-01-01';
- INNER JOIN(连接)把两张表中一张的外键与另一张的主键匹配的行合并起来,匹配条件写在
ON子句里。 - 两张表中出现同名字段时,给字段加上表名前缀。大纲要求的查询最多涉及两张表。
Stitch two tables with INNER JOIN · 用 INNER JOIN 缝合两个表
A join matches rows where the foreign key equals the primary key — here Orders.CustomerID = Customer.CustomerID — and combines each matching pair into one wider row. · 一个连接匹配外键等于主键的行——这里 Orders.CustomerID = Customer.CustomerID——并把每个匹配的对组合成一个更宽的行。
An INNER JOIN is used to: · 一个 INNER JOIN 用来:
A JOIN combines two tables on a relationship (usually a foreign key matching a primary key). · 一个 JOIN 在一个关系上组合两个表(通常是一个外键匹配一个主键)。
Aggregates and GROUP BY
- Aggregate functions 聚合函数 summarise many rows into one value:
COUNTthe rows,SUMa total,AVGa mean. GROUP BYmakes one summary row per value of a field: the number of orders per customer. Without it, an aggregate summarises the whole table.
聚合与 GROUP BY
SELECT CustomerID, COUNT(*) AS NumOrders
FROM ORDERS
GROUP BY CustomerID;
SELECT AVG(Price) FROM PRODUCT;
SELECT SUM(Quantity) FROM ORDER_LINE WHERE OrderID = 1042;
- 聚合函数(aggregate functions)把多行汇总成一个值:
COUNT数行数,SUM求总和,AVG求平均。 GROUP BY让某字段的每个取值对应一行汇总:每个客户的订单数。没有它,聚合汇总的是整张表。
What does COUNT(*) return? · COUNT(*) 返回什么?
COUNT() counts rows; SUM/AVG/MIN/MAX are the other aggregate functions. · COUNT() 计数行;SUM/AVG/MIN/MAX 是其他的聚合函数。
To output the number of orders placed by each customer, the query needs: select all · 所有 that apply. · 要输出每个客户下的订单数,查询需要:选出所有适用的。
Count the rows, one group per customer, from the orders table. Sorting is optional. · 数行数,每个客户一组,来自订单表。排序是可选的。
Changing the data: INSERT, UPDATE, DELETE
INSERT INTO … VALUESadds a row: list the fields, then the values in the same order.UPDATE … SET … WHEREchanges matching rows.DELETE FROM … WHEREremoves them.- Always give
UPDATEandDELETEaWHEREclause, or the change hits every row in the table.
修改数据:INSERT、UPDATE、DELETE
INSERT INTO CUSTOMER (CustomerID, Name, Town, Joined, Active)
VALUES (101, 'Ada Lovelace', 'London', '2024-03-01', TRUE);
UPDATE CUSTOMER SET Town = 'Bristol' WHERE CustomerID = 101;
DELETE FROM CUSTOMER WHERE CustomerID = 101;
INSERT INTO … VALUES添加一行:列出字段,再按同样顺序列出值。UPDATE … SET … WHERE修改匹配的行。DELETE FROM … WHERE删除它们。UPDATE和DELETE一定要带WHERE子句,否则改动会波及表中每一行。
What happens if you run UPDATE or DELETE without a WHERE clause? · 如果你运行没有 WHERE 子句的 UPDATE 或 DELETE 会发生什么?
With no WHERE, the operation affects all rows — a common and dangerous mistake. · 没有 WHERE,操作影响所有的行——一个常见且危险的错误。
Match each DML statement to what it does. · 把每个 DML 语句与它做的事配对。
SELECT reads; INSERT adds; UPDATE changes; DELETE removes — the four core DML verbs. · SELECT 读;INSERT 添加;UPDATE 改变;DELETE 移除——四个核心的 DML 动词。
Worked example: read the statement
- State what this script outputs. The names of every customer who placed an order on 1 May 2024, one row per such order, in reverse alphabetical order.
- Read it in execution order: join the tables on the customer ID, keep the rows for that date, output the name, sort descending. A customer with two orders that day appears twice.
例题:读懂语句
SELECT Name
FROM CUSTOMER INNER JOIN ORDERS
ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE OrderDate = '2024-05-01'
ORDER BY Name DESC;
- *说出这个脚本输出什么。*2024 年 5 月 1 日下过订单的每位客户的姓名,每笔这样的订单一行,按字母倒序。
- 按执行顺序读:在客户 ID 上连接两表,保留该日期的行,输出姓名,降序排序。当天有两笔订单的客户出现两次。
Marks that slip away
- Strings in single quotes, numbers bare:
Town = 'London',CustomerID = 101. ORDER BYcomes afterWHERE; a join needs itsONclause; every statement ends with a semicolon.COUNT(*)counts rows, not distinct values; the per-group question needsGROUP BY.CREATE,ALTERandPRIMARY KEYare DDL;SELECT,INSERT,UPDATE,DELETEare DML. Use the exact names the question gives.
容易丢掉的分
- 字符串加单引号,数字不加:
Town = 'London',CustomerID = 101。 ORDER BY在WHERE之后;连接需要ON子句;每条语句以分号结束。COUNT(*)数的是行,不是不同的值;按组的问题需要GROUP BY。CREATE、ALTER和PRIMARY KEY是 DDL;SELECT、INSERT、UPDATE、DELETE是 DML。使用题目给出的精确名称。
You've got it
- DDL creates and changes the structure:
CREATE DATABASE,CREATE TABLEwith typed attributes,PRIMARY KEY,FOREIGN KEY … REFERENCES,ALTER TABLE … ADD· DML works with the data - types:
CHARACTER,VARCHAR(n),BOOLEAN,INTEGER,REAL,DATE,TIME SELECT fields FROM table WHERE condition ORDER BY field;INNER JOIN … ONfor two tables;COUNT,SUM,AVGwithGROUP BYfor one row per groupINSERT INTO … VALUES,UPDATE … SET … WHERE,DELETE FROM … WHERE: never an update or delete withoutWHERE
你掌握了
- DDL 创建和修改结构:
CREATE DATABASE、带类型属性的CREATE TABLE、PRIMARY KEY、FOREIGN KEY … REFERENCES、ALTER TABLE … ADD· DML 处理数据 - 类型:
CHARACTER、VARCHAR(n)、BOOLEAN、INTEGER、REAL、DATE、TIME SELECT 字段 FROM 表 WHERE 条件 ORDER BY 字段;两张表用INNER JOIN … ON;COUNT、SUM、AVG配GROUP BY得到每组一行INSERT INTO … VALUES、UPDATE … SET … WHERE、DELETE FROM … WHERE:更新或删除绝不能没有WHERE