Deleting rows
Databases & SQL Lesson 16 1:49 English narration · English + 中文 subtitles burned in
Chapters
Transcript
DELETE removes rows.
DELETE 移除行。
Name the table and give a WHERE, and that one row goes — the glue, here.
写出表名，给一个 WHERE，那一行就走了——这里是胶水那一行。
Notice the two things that did not happen: the other rows are untouched, and the table is still there, three columns wide, ready to hold rows again.
注意有两件事没有发生： 其他行没被碰过，而且这张表还在，三列宽，随时可以再装行进去。
That last point is worth its own beat, because these two get confused.
最后那一点值得单独讲，因为这两个常被搞混。
DELETE removes rows and leaves the table.
DELETE 移除行，把表留下。
DROP TABLE takes the table with them.
DROP TABLE 连表一起带走。
After a DELETE the table is empty and you can INSERT into it again; after a DROP there is no table to be empty, and a SELECT against it is an error.
DELETE 之后，表是空的，你可以再往里 INSERT； DROP 之后，根本没有表可以"空"， 而对它做 SELECT 会是一个错误。
The warning from last lesson, one notch worse.
上一课的警告，再狠一档。
Leave the WHERE out and DELETE takes every row at once.
把 WHERE 去掉，DELETE 会一次拿走每一行。
The statement succeeds, no error appears, and the table is empty.
语句成功了，没有报错，而表空了。
There is no undo — only a backup brings those rows back.
没有撤销——只有备份能把那些行找回来。
So write the WHERE before you write anything else on that line.
所以在那一行上，先把 WHERE 写好，再写别的。
One case where the database protects you.
有一种情况数据库会保护你。
If another table holds a foreign key pointing at the row you are deleting, that order would be left pointing at nothing — which referential integrity forbids.
如果另一张表有一个外键指着你正要删掉的那一行， 那个订单就会剩下来指向空无——而参照完整性不允许这样。
So the delete is refused.
于是这次删除被拒绝了。
That is the rule from lesson nine doing its job, seen from the other side.
这就是第 9 课那条规则在干活，只不过是从另一面看到的。
Four things to take with you.
带走四点。
One: DELETE removes the rows the WHERE picks.
第一：DELETE 移除 WHERE 挑中的那些行。
Two: the table itself stays, empty or not.
第二：表本身留下来，不管它空不空。
Three: a DELETE with no WHERE empties the table.
第三：不带 WHERE 的 DELETE 会把表清空。
Four: test a risky delete as a SELECT with the same WHERE.
第四：危险的删除，先用同样的 WHERE 当 SELECT 试一遍。
Now run the tasks below.
现在去做下面的题。