Creating tables
Databases & SQL Lesson 12 2:09 English narration · English + 中文 subtitles burned in
Chapters
Transcript
SQL splits into two halves, and it is worth being able to name them.
SQL 分成两半，而能叫出它们的名字是有用的。
DDL, the data definition language, changes the structure — the tables themselves.
DDL，数据定义语言，改变的是结构——也就是表本身。
DML, the data manipulation language, works with the data inside them.
DML，数据操纵语言，处理的是表里面的数据。
Every query so far has been DML, and every one of them was a SELECT.
到目前为止的每一条查询都是 DML，而且每一条都是 SELECT。
This lesson starts the other half.
这一课开始讲另外一半。
CREATE TABLE is the main DDL command, and it reads like a form: the table's name, then one line per column, each giving that column a name and a type.
CREATE TABLE 是主要的 DDL 命令，它读起来像一张表格： 表的名字，然后每列一行，每一行给那一列一个名字和一个类型。
Run it and the table exists — with no rows in it.
运行它，这张表就存在了——里面一行数据都没有。
CREATE makes the shape; the data comes later, with INSERT, in two lessons' time.
CREATE 造的是形状；数据要等到两课之后，用 INSERT 才进来。
The types come up in exam questions, so learn them with an example each.
这些类型在考题里会出现，所以每一个都配一个例子来记。
Numbers first: INTEGER for whole numbers, and DECIMAL five two for money — five digits with two after the point.
先说数字：INTEGER 用于整数，DECIMAL(5,2) 用于金额—— 五位数字，小数点后两位。
Then two kinds of text: VARCHAR up to a length, CHAR exactly that length, which suits a form code like eleven A.
然后是两种文字：VARCHAR 是"最多这么长"， CHAR 是"正好这么长"，适合像 11A 这样的班级代码。
And dates and true or false have their own types too.
而日期、以及真假值，也各有自己的类型。
And this is where lesson nine's two keys get written down.
而这里就是第 9 课那两个键被写下来的地方。
PRIMARY KEY on a column says this one names one row.
某一列上的 PRIMARY KEY 表示：这一列指认某一行。
FOREIGN KEY, with REFERENCES, says this one points away, at another table's key.
FOREIGN KEY 加上 REFERENCES 表示：这一列指向别处，指向另一张表的键。
Declaring it is not decoration: from then on, if you try to insert an order for a customer who does not exist, the database refuses it.
声明它不是装饰： 从此以后，如果你试图为一个不存在的客户插入订单，数据库会拒绝。
Four things to take with you.
带走四点。
One: DDL changes the structure and DML works with the data.
第一：DDL 改结构，DML 处理数据。
Two: CREATE TABLE makes an empty table with named columns.
第二：CREATE TABLE 造出一张有列名的空表。
Three: every column needs a data type.
第三：每一列都需要一个数据类型。
Four: PRIMARY KEY and FOREIGN KEY are written into the definition.
第四：PRIMARY KEY 和 FOREIGN KEY 是写进表定义里的。
Now run the tasks below.
现在去做下面的题。