PostgreSQL 学习 03

archive time: 2023-11-10

闲着也是闲着，不如学一下 PostgreSQL

数据库表格设计
查询关联数据

最近一直是休息状态，但是懒散久了总感觉有点呆不住，那就再学点 PostgreSQL 吧

数据库表格设计

基本上，需要用到数据库的场合下，不会只有一个表格需要管理，通常是需要管理多个相互关联的表格

并且需要知道，我们使用数据库的时候，表格结构一旦确定下来，出于维护方便和成本考虑，轻易不会修改

这种情况下，数据之间的管理就显得尤为重要，也就需要我们对于表格有个良好的设计

需要哪些表格

在开始设计之前，我们需要明确自己的需求，也就是我们需要处理那些数据，有哪些表格需要设计

在 SQL and PostgreSQL The Complete Developer’s Guide 中，我们需要设计一个给 图片分享应用 的数据库，大致包括用户，图片，评论以及点赞这几个数据

通常情况下，我们的数据库结构是需要根据程序的结构设计的

例如用户，一个用户类型包括了 用户发帖数，用户跟随数，用户粉丝数 以及 用户帖子 大概这些内容

而一个帖子，可能包含了标题，头图，图片以及 图片描述 等内容

这些就是我们设计数据库表格和关系的依据

表格间的关系

而在我们要设计的这四个表格中，我们可以知道，用户是可以发图片的，用户是可以评论和点赞别人帖子的，一个图片是可以被点赞和被评论的，等等，这些就是我们说的 “关系”

我们常见的关系类型有:

一对多: 一个用户可以有多个图片
多对一: 多张照片属于同一个用户
一对一: 一个账户对应一个用户
多对多: 学生可能要上多门课，每一门课有很多学生

这些关系还可以通过主键和外键来体现

所谓主键，就是一个表格中每个记录独有的数据

而外键指的就是这个表格的每个记录与其他表格中记录的关系

例如，在图片表格中，每个图片都有一个独一无二的哈希值，这个可以作为图片的主键

每个图片都是由某一位用户发的，所以有个 user_id 作为记号，这就是对应的外键

但是一个用户可以发多个图片，这就意味着多个图片的 user_id 可以一样，但是哈希值一定是不同的

自动生成序列

对于图片 ID 或者用户 ID 一类的东西，我们可以选择自己生成，然后手动插入到数据库

除此之外，我们还可以让数据库自己为我们生成对应的序列，也就是 serial 类型

create table users (
  id serial primary key
  , username character varying (64)
);

这样，我们在插入数据的时候就不需要插入 id，而是只需要给数据库 username 就好

insert into users (
  username
) values
  ('Alice')
  , ('Bob')
  , ('Susan')
  , ('Jack');

此时，我们的 users 表格数据就是:

psqldev=# select * from users;
 id | username
----+----------
  1 | Alice
  2 | Bob
  3 | Susan
  4 | Jack
(4 行记录)

对于图片表格，我们可以用类似的设计

create table photos (
  id serial primary key
  , url character varying (256)
  , user_id integer references users(id)
);

图片 ID 我们没有使用哈希值，因为这里只是示例，并没有真正存储图片，所以使用序列就好了

其次，为了表明图片与用户的关系，我们使用了用户 ID 作为外键

外键的语法是:

<name> <type> references <table name>(<cols>)

也就是 <name> 这个名字的数据对应的是 <table name> 这个表中的 <cols> 对应数据

对这个表插入数据是类似的:

insert into photos (
  url
  , user_id
) values
  ('https://img1.jpg', 4)
  , ('https://img2.jpg', 4)
  , ('https://img3.jpg', 2)
  , ('https://img4.jpg', 3)
  , ('https://img5.jpg', 3)
  , ('https://img6.jpg', 2)
  , ('https://img7.jpg', 1)
  , ('https://img8.jpg', 1)
  , ('https://img9.jpg', 1);

此时，我们的 photos 表格数据为:

psqldev=# select * from photos;
 id |       url        | user_id
----+------------------+---------
  1 | https://img1.jpg |       4
  2 | https://img2.jpg |       4
  3 | https://img3.jpg |       2
  4 | https://img4.jpg |       3
  5 | https://img5.jpg |       3
  6 | https://img6.jpg |       2
  7 | https://img7.jpg |       1
  8 | https://img8.jpg |       1
  9 | https://img9.jpg |       1
(9 行记录)

查询关联数据

我们使用 user_id 将 photos 和 users 联系在了一起，那么这样做有什么用呢？

例如，我们想要查看某个用户发的所有图片，这个情况下，外键就非常有用了

select * from photos
  where user_id = 4;

我们会得到如下输出:

psqldev=# select * from photos
  where user_id = 4;
 id |       url        | user_id
----+------------------+---------
  1 | https://img1.jpg |       4
  2 | https://img2.jpg |       4
(2 行记录)

如果要看所有照片和用户的关系呢？

select * from photos
  join users on users.id = photos.user_id;

其中 join 是将两个表格合并在了一起，并且是依据 user_id 与 users.id 的对应关系，输出如下:

psqldev=# select * from photos
  join users on users.id = photos.user_id;
 id |       url        | user_id | id | username
----+------------------+---------+----+----------
  9 | https://img9.jpg |       1 |  1 | Alice
  8 | https://img8.jpg |       1 |  1 | Alice
  7 | https://img7.jpg |       1 |  1 | Alice
  6 | https://img6.jpg |       2 |  2 | Bob
  3 | https://img3.jpg |       2 |  2 | Bob
  5 | https://img5.jpg |       3 |  3 | Susan
  4 | https://img4.jpg |       3 |  3 | Susan
  2 | https://img2.jpg |       4 |  4 | Jack
  1 | https://img1.jpg |       4 |  4 | Jack
(9 行记录)

我们使用外键是为了保证 数据一致性，也就是保证我们插入的图片数据一定能与某个用户联系

如果我们插入了某个不存在的用户 ID，数据库就会发现这个错误，并且提示我们:

psqldev=# insert into photos (
  url
  , user_id
) values
  ('https://img16.jpg', 16);
ERROR:  insert or update on table "photos" violates foreign key constraint "photos_user_id_fkey"
描述:  Key (user_id)=(16) is not present in table "users".

这里，数据库直接告诉我们，我们要插入的数据中，16 这个 user_id 是不存在的

不过某些情况下，我们想要插入一个图片，但是这个图片是非常特殊的，不属于任何一个用户，例如应用图标

我们可以使用 null 这个特殊数据

insert into photos (
  url
  , user_id
) values
  ('https://app-icon.png', null);

如果我们这个时候再查看我们的 photos 表格数据就会发现， https://app-icon.png 对应的 user_id 是空白一片，也就是 NULL

psqldev=# select * from photos;
 id |         url          | user_id
----+----------------------+---------
  1 | https://img1.jpg     |       4
  2 | https://img2.jpg     |       4
  3 | https://img3.jpg     |       2
  4 | https://img4.jpg     |       3
  5 | https://img5.jpg     |       3
  6 | https://img6.jpg     |       2
  7 | https://img7.jpg     |       1
  8 | https://img8.jpg     |       1
  9 | https://img9.jpg     |       1
 11 | https://app-icon.png |
(10 行记录)

好，今天就暂时学到这里吧，后面的删除什么的就之后再看了

Kevin Stephen's Blogs