练习 43:SQL 管理

原文:Exercise 43: SQL Administration

译者:飞龙

协议:CC BY-NC-SA 4.0

自豪地采用谷歌翻译

“管理”一词在数据库中重载了。它的意思是“确保 PostgreSQL 服务器保持运行”,或者是“为新软件的部署更改和迁移表”。在本练习中,我只介绍如何更改和迁移简单的纲要。管理完整数据库服务器不在本书的范围之内。

销毁和更改表

您已经遇到了DROP TABLE作为删除表的方式。我将向您展示另一种使用方式,以及如何使用ALTER TABLE在表中添加或删除列。

  1. /* Only drop table if it exists. */
  2. DROP TABLE IF EXISTS person;
  3. /* Create again to work with it. */
  4. CREATE TABLE person (
  5. id INTEGER PRIMARY KEY,
  6. first_name TEXT,
  7. last_name TEXT,
  8. age INTEGER
  9. );
  10. /* Rename the table to peoples. */
  11. ALTER TABLE person RENAME TO peoples;
  12. /* Add a hatred column to peoples. */
  13. ALTER TABLE peoples ADD COLUMN hatred INTEGER;
  14. /* Rename peoples back to person. */
  15. ALTER TABLE peoples RENAME TO person;
  16. .schema person
  17. /* We don't need that. */
  18. DROP TABLE person;

我正在对表进行一些虚假更改,来演示这些命令,但使用ALTER TABLEDROP TABLE语句,这是你可以在 SQLite3 中执行的一切事情。我会讲解它,让你了解发生了什么:

ex21.sql:2

使用IF EXISTS修饰符,仅当表已经存在时,才会丢弃。当你在没有表的新的数据库上运行你的.sql脚本时,这抑制了错误。

ex21.sql:5

仅仅重新创建表来处理它。

ex21.sql:13

使用ALTER TABLE来将其重命名为peoples

ex21.sql:16

向新命名的表peoples中添加新的一列hatred,它是个INTEGER

ex21.sql:19

peoples重命名回到person,因为这对于表来说是个愚蠢的名称。

ex21.sql:21

转储person的纲要,所以你可以看到,它拥有新的hatred列。

ex21.sql:24

在这个练习之后,丢弃这个表来打扫干净。

迁移和演化数据

我们来应用您学到的一些技巧。我会让你选取你的数据库,并将纲要“演进”成不同的形式。你需要确保你很好地了解了以前的练习,并且让你的code.sql正常工作。如果你没有完成每一个这些东西,那么回去把所有东西都弄清楚。

为了确保你在正确的状态中尝试这个练习,当你运行你的code.sql,你应该可以运行.schema,像这样:

  1. $ sqlite3 ex13.db < code.sql
  2. $ sqlite3 ex13.db .schema
  3. CREATE TABLE person (
  4. id INTEGER PRIMARY KEY,
  5. first_name TEXT,
  6. last_name TEXT,
  7. age INTEGER
  8. );
  9. CREATE TABLE person_pet (
  10. person_id INTEGER,
  11. pet_id INTEGER
  12. );
  13. CREATE TABLE pet (
  14. id INTEGER PRIMARY KEY,
  15. name TEXT,
  16. breed TEXT,
  17. age INTEGER,
  18. dead INTEGER,
  19. dob DATETIME
  20. );

确保你的表像我的表。并且如果不是,返回去并且移除任何命令,它们在上一个练习之后执行ALTER TABLE或者任何东西。

挑战性练习

您所要完成的任务是数据库更改的以下列表:

  • person添加dead列,就像pets中那样。
  • person添加phone_number列。
  • person添加salary列,它是float
  • personpet添加dob列,它是DATETIME
  • person_pet添加purchased_on列,它是DATETIME
  • pet添加parent列,它是INTEGER,并且持有它父母的id
  • 使用UPDATE语句,使用新的列数据更新现有的数据库记录。不要忘记person_pet关系表中的purchased_on列,来表明这个人什么时候购买这个宠物。
  • 再增加四个人和五个宠物,并为它们分配从属关系,以及哪个宠物是父母。在最后一部分,请记住,您获取父母的 ID,然后将其设置在parent列中。
  • 写一个查询,寻找 2004 年以后购买的所有宠物及其所有者的名字。关键是基于purchased_on列将person_pet映射到petparent
  • 写一个查询,寻找给定宠物的父母。再次查看pet.parent来实现它。实际上很简单,所以不要小题大做。
  • 更新你的code.sql文件,你已经把所有的代码放了进去,让它使用DROP TABLE IF EXISTS语法。
  • 使用ALTER TABLE,向person添加heightweight列,并将其放在你的code.sql文件中。
  • 运行新的code.sql脚本来重置数据库,你应该没有错误。

您应该通过编写一个ex13.sql文件来实现,文件里面是这些新的东西。然后通过使用code.sql重置数据库来测试它,然后运行ex13.sql来更改数据库,并执行SELECT查询来确认您进行了正确的更改。

深入学习

继续阅读DROP TABLEALTER TABLE的文档,然后访问 SQLite3 语言页面,并且阅读文档的其余CREATEDROP语句。