Changing the schema

Databases & SQL Lesson 13 1:53 English narration · English + 中文 subtitles burned in

space play · ←/→ 5s · j/l 10s · f fullscreen · ,/. speed

Chapters

Transcript

A table's design is not fixed once it is made. 一张表的设计并不是造好之后就定死的。

ALTER TABLE ADD gives an existing table a new column, named and typed just as it would have been in the CREATE. ALTER TABLE ... ADD 给一张已有的表加上一个新列，名字和类型的写法和在 CREATE 里完全一样。

Here the product table gains a fourth column for weight. 这里 product 表多出了第四列，用来放重量。

And look at what happened to the rows: every existing row is still there, now one cell wider. 再看看那些行怎么样了：原有的每一行都还在，只是现在宽了一格。

What goes in that new cell? 那个新格子里放什么？

NULL — which means we do not know, not nothing. NULL——意思是"我们不知道"，不是"没有"。

That is the honest answer: those three products were entered before anyone recorded a weight. 这才是诚实的答案：那三件商品是在有人记录重量之前就录入的。

Putting zero there would be a claim that they weigh nothing, which is false. 在那里放一个 0，等于声称它们重量为零，而那是假的。

You fill them in later, with UPDATE, in lesson fifteen. 你稍后再把它们填上，用 UPDATE，在第 15 课。

DROP TABLE goes the other way: it removes the table completely — its structure and all of its rows with it. DROP TABLE 是反方向的：它把这张表整个移除—— 它的结构，连同它所有的行。

After this statement there is no product table at all, and a SELECT against it is an error, not an empty result. 这条语句之后，product 表根本就不存在了，对它做 SELECT 会是一个错误，而不是一个空结果。

Two reasons to think before you run it. 在运行它之前有两个要想一想的理由。

There is no undo: the rows are gone, and only a backup brings them back. 没有撤销：那些行没了，只有备份能把它们找回来。

And another table may hold a foreign key pointing at this one, so those links break — in practice a database with foreign keys declared will usually refuse the DROP rather than break them. 而且另一张表可能有一个外键指着这一张，那些链接就断了—— 实际上，一个声明了外键的数据库通常会拒绝这次 DROP，而不是把链接弄断。

Keep the two straight: ALTER changes a design, and DROP ends one. 把这两者分清楚：ALTER 改动一个设计，而 DROP 终结一个设计。

Four things to take with you. 带走四点。

One: ALTER TABLE ADD gives an existing table a new column. 第一：ALTER TABLE ADD 给一张已有的表加一个新列。

Two: existing rows get NULL there, not zero. 第二：原有的行在那里得到 NULL，不是 0。

Three: DROP TABLE removes the structure and all its rows. 第三：DROP TABLE 移除结构和它所有的行。

Four: dropping a table other tables reference breaks their links. 第四：删掉一张被别的表引用的表，会打断它们的链接。

Now run the tasks below. 现在去做下面的题。