练习 42:SQL 删除

原文:Exercise 42: Deleting with SQL

译者:飞龙

协议:CC BY-NC-SA 4.0

自豪地采用谷歌翻译

这是最简单的练习,但我希望你键入代码之前思考一秒钟。如果你将SELECT写成"SELECT * FROM",将INSERT写成"INSERT INTO",那么你会怎么编写DELETE格式?你可以看下面,但是试着猜测它会是什么,然后看一看。

  1. /* make sure there's dead pets */
  2. SELECT name, age FROM pet WHERE dead = 1;
  3. /* aww poor robot */
  4. DELETE FROM pet WHERE dead = 1;
  5. /* make sure the robot is gone */
  6. SELECT * FROM pet;
  7. /* let's resurrect the robot */
  8. INSERT INTO pet VALUES (1, "Gigantor", "Robot", 1, 0);
  9. /* the robot LIVES! */
  10. SELECT * FROM pet;

我只是简单地通过删除它,然后使用dead=0将记录放回去,来为机器人实现非常复杂的更新。在以后的练习中,我将向你展示,如何使用UPDATE来实现它,所以不要以为这是更新的真正方法。

你已经熟悉了这个脚本中的大多数行,除了第五行。这里你拥有DELETE,它与其他命令格式几乎相同。你提供了DELETE FROM table WHERE tests,以及一种方式,将其看做移除行的SELECT。任何在WHERE子句中有效的内容在这里都有效。

使用其它表来删除

记得我说过:“DELETE就像SELECT,但它从表中删除行。” 限制是一次只能从一个表中删除。这意味着为了删除所有宠物,你需要执行一些额外的查询,然后基于它们删除。

一种方法是使用一个子查询,根据你已经编写的查询来选择要所需的 ID。还有其他的方法可以实现它,但是现在你可以根据你所知道的方法来实现它:

  1. DELETE FROM pet WHERE id IN (
  2. SELECT pet.id
  3. FROM pet, person_pet, person
  4. WHERE
  5. person.id = person_pet.person_id AND
  6. pet.id = person_pet.pet_id AND
  7. person.first_name = "Zed"
  8. );
  9. SELECT * FROM pet;
  10. SELECT * FROM person_pet;
  11. DELETE FROM person_pet
  12. WHERE pet_id NOT IN (
  13. SELECT id FROM pet
  14. );
  15. SELECT * FROM person_pet;

第 1~8 行是正常起步的DELETE命令,但是WHERE子句使用IN,匹配pet中的id列与子查询中返回的表。子查询(也称为子选择)是正常的SELECT,在尝试寻找人们拥有的宠物时,它应该看起来和以前你做的那个相似。

第 13~16 行中,然后我使用子查询,将任何不存在的宠物从person_pet表中给删除,使用NOT IN而不是IN

SQL 处理它的方式是以下过程:

  • 运行末尾处括号中的子查询,并创建一个表,带有所有列,就像普通SELECT一样。
  • 将此表视为一种临时表,来匹配pet.id列。
  • 浏览pet表,并删除拥有此临时表中(IN)的 ID 的任何行。

挑战练习

  • 将所有ex2.sqlex7.sql合并到一个文件中,并重执行上述脚本,以便你只需运行一个新文件即可重新创建数据库。
  • 添加一些东西到脚本中,来删除其他宠物,然后再次使用新值插入它们。记住,这不是你通常更新记录的方式,只是为了练习。
  • 练习编写SELECT命令,然后将它们放在DELETE WHERE IN中,来删除找到的记录。尝试删除你拥有的任何死亡宠物。
  • 反着操作,删除有死亡宠物的人。
  • 你真的需要删除死的宠物吗?为什么不在person_pet中移除他们的关系,并标记它们死了?写一个查询,从person_pet中去除死亡宠物。

深入学习

出于完整性,你需要阅读DELETE文档