练习 39:SQL 创建

原文:Exercise 39: Creating with SQL

译者:飞龙

协议:CC BY-NC-SA 4.0

自豪地采用谷歌翻译

当我们谈论首字母缩写“CRUD”时,“C”代表“创建”,它不仅仅意味着创建表。这也意味着将数据插入到表中,并使用表和插入来链接表。由于我们需要一些表和一些数据来完成其余的 CRUD(增删改查),我们开始学习如何在 SQL 中执行最基本的创建操作。

表的创建

我在简介中说,可以对表内的数据执行“增删改查”操作。你如何把表放在首要位置?通过对数据库纲要(Schema)执行 CRUD ,第一个要学习的 SQL 语句是CREATE

  1. CREATE TABLE person (
  2. id INTEGER PRIMARY KEY,
  3. first_name TEXT,
  4. last_name TEXT,
  5. age INTEGER
  6. );

你可以将其放在一行中,但是我打算讨论每一行,所以写成了多行。这里是每行所做的事情:

ex1.sql:1

CREATE TABLE的起始,它提供了表的名称person。这个部分之后,之后将你想要的字段放到括号里。

ex1.sql:2

id列,它用于准确确定每一行。列的格式是NAME TYPE,并且这里我假设,我需要一个INTEGER也是PRIMARY KEY。这样做告诉 SQLite3 来将其特殊对待。

ex1.sql:3~4

first_namelast_name列。它们都是TEXT

ex1.sql:5

age列,只是一个INTEGER

ex1.sql:6

使用圆括号结束列的列表,之后是一个分号(;)。

创建多表的数据库

创建一个表不是特别实用。我希望你现在创建三个表,你可以在里面储存数据。

  1. CREATE TABLE person (
  2. id INTEGER PRIMARY KEY,
  3. first_name TEXT,
  4. last_name TEXT,
  5. age INTEGER
  6. );
  7. CREATE TABLE pet (
  8. id INTEGER PRIMARY KEY,
  9. name TEXT,
  10. breed TEXT,
  11. age INTEGER,
  12. dead INTEGER
  13. );
  14. CREATE TABLE person_pet (
  15. person_id INTEGER,
  16. pet_id INTEGER
  17. );

在此文件中,你正在为两种数据类型制作表,然后将它们与第三个表“链接”在一起。人们称这些“链接”表为“关系”,但没有生命的非常愚蠢的人把所有表都成为“关系”,并且热衷于使那些想要完成工作的人困惑。在我的书中,具有数据的表是“表”,将表连接在一起的表称为“关系”。

这里没有任何新东西,除非你看到person_pet,你会看到我已经写了两列:person_idpet_id。将两个表链接在一起,只是向person_pet插入一行。它拥有两行的 ID 列的值,你想要链接它们。例如,如果person包含一行id=20,pet有一行id=98,然后假设这个人拥有这个宠物,你会将person_id=20, pet_id=98插入到person_pet关系(表)中。

在接下来的几个练习中,我们将实际插入这样的数据。

插入数据

你有了要处理的几个表,所以现在我让你使用INSERT命令,放进去一些数据:

  1. INSERT INTO person (id, first_name, last_name, age)
  2. VALUES (0, "Zed", "Shaw", 37);
  3. INSERT INTO pet (id, name, breed, age, dead)
  4. VALUES (0, "Fluffy", "Unicorn", 1000, 0);
  5. INSERT INTO pet VALUES (1, "Gigantor", "Robot", 1, 1);

在这个文件中,我使用两种不同形式的INSERT命令。第一种形式是更明确的风格,最有可能是你应该使用的东西。它指定要插入的列,后跟VALUES,然后要包括的数据。这两个列表(列名和值)都在括号内,并以逗号分隔。

第七行的第二个版本是一个缩写版本,它不指定列,而是依赖于表中的隐式顺序。这种形式是危险的,因为你不知道你的语句实际访问哪一列,并且某些数据库对列没有可靠的排序。当你真的很懒惰时,最好只用这种形式。

插入引用数据

在最后一节,你会在表中放满人和宠物。唯一缺少的东西是,谁拥有什么宠物,这个数据存入person_pet表,如下所示:

  1. INSERT INTO person_pet (person_id, pet_id) VALUES (0, 0);
  2. INSERT INTO person_pet VALUES (0, 1);

我再次使用显式格式,然后使用隐式格式。我使用我想要的person表的行id(这里是0),和我想要的pet表的行id(同样,0是独角兽,1是死去的机器人)。然后,我们向person_pet关系表中插入一行,用于人与宠物之间的每个“连接”。

挑战练习

  • 创建另一个数据库,但为其它东西创建其他INTEGERTEXT字段,person可能拥有它们。
  • 在这些表中,我创建了第三个关系表来链接它们。你如何摆脱这个关系表person_pet,并将这些信息优雅放在person里面?这个变化暗示了什么?
  • 如果你可以把一行放入person_pet,你是否可以放多行?你如何记录一个疯狂的猫女士与 50 只猫?
  • 为人们可能拥有的汽车创建另一个表,并创建其对应的关系表。
  • 在你喜欢的搜索引擎中搜索“sqlite3 数据类型”,然后阅读 SQLite3 文档中的数据类型。记录你可以使用什么类型,以及其他看起来很重要的东西。我们稍后会介绍。
  • 插入你自己和你的宠物(或像我这样的虚拟宠物)。
  • 如果将上一个练习中的数据库更改为没有person_pet表,则使用该模式创建一个新数据库,并将相同的信息插入到该数据库中。
  • 回顾数据类型列表,并记录不同类型所需的格式。例如,请注意你有多少种方式来写入TEXT数据。
  • 为你和你的宠物添加关系。
  • 使用这张表,一只宠物可以被多于一个人拥有吗?这在逻辑上是可能的吗?家养的狗如何呢?严格来说,家庭中的每个人不是拥有它吗?
  • 考虑上面的东西,并且考虑到你有一个替代设计,将pet_id放在pearon表中,哪种设计更适合这种情况?

深入学习

请阅读 SQLite3 CREATE命令的文档,然后查看尽可能多的其他CREATE语句。你还应该阅读 https://sqlite.org/lang_insert.html 上的INSERT文档,这应该会引导你阅读许多其他页面。