最近我在做一个玩具项目，用来学习go语言，运维，feishu api等知识。它需要一个数据库。 考虑到postgresql是世界上最先进的开源数据库，我学习一下它，跟着这个教程。 本文不是详细的教程，看懂本文需要有一定基础，最好学过docker和mysql。 作为关系型数据库，pg有很多feature和其它关系型数据库类似，这样的feature，本文不详细展示。重点展示pg独有的feature。
安装
以下是docker-compose.yml文件。
services:
postgres:
image: postgres:15.2-bullseye
environment:
- TZ=Asia/Shanghai
- POSTGRES_PASSWORD=postgres
volumes:
- db-data:/var/lib/postgresql/data
- ./pg-dumps:/volume-pg-dumps
ports:
- "5432:5432"
mem_limit: 500MB
cpus: 0.5
restart: unless-stopped
pgadmin:
image: dpage/pgadmin4:6.21
environment:
- PGADMIN_DEFAULT_EMAIL=postgres@domain.com
- PGADMIN_DEFAULT_PASSWORD=postgres
ports:
- "5500:80"
restart: unless-stopped
volumes:
db-data:
其中pgadmin是pg官方出品的图形化的管理工具，没有它，用psql等工具也可以的。使用它，鼠标点点就能管理数据库。
使用docker compose up -d来启动此项目
➜ learn-postgresql git:(master) ✗ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
b103fff874ac dpage/pgadmin4:6.21 "/entrypoint.sh" 5 hours ago Up 5 hours 443/tcp, 0.0.0.0:5500->80/tcp, :::5500->80/tcp learn-postgresql-pgadmin-1
769057d14860 postgres:15.2-bullseye "docker-entrypoint.s…" 5 hours ago Up 5 hours 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp learn-postgresql-postgres-1
这样就表示启动成功了。
另外还等安装psql等工具来管理pg。通过sudo apt-get install -y postgresql-client来安装。
连接数据库
psql -h localhost -p 5432 -U postgres。
这里-h 是hostname, -p是port，-U是username。
在以上docker-compose.yml文件中，我们通过POSTGRES_PASSWORD设置了superuser的密码为postgres。而默认superuser为postgres。这里我们登录为postgres, 它会提示输入密码。密码就是postgres。
Password for user postgres:
psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1), server 15.2 (Debian 15.2-1.pgdg110+1))
WARNING: psql major version 14, server major version 15.
Some psql features might not work.
Type "help" for help.
postgres=#
这样就登录成功了。
导入示例数据库
数据库的操作一般就是增删查改。查询最基础，首先应该学习查询。当然得先有数据。 这里我们根据教程的这两章 示例数据库， 导入数据库， 来创建数据库和导入数据。这里用的是psql和pg_store这两个命令行工具。当然，用pgadmin也可以。
创建数据库
注意，在psql中执行语句时，一定要加;符号。否则应该是没用的。
postgres=# CREATE DATABASE dvdrental;
CREATE DATABASE
postgres=#
然后通过postgres=# exit退出psql。
下载数据库备份文件
在这里。 然后解压它
➜ learn-postgresql git:(master) ✗ unzip dvdrental.zip
Archive: dvdrental.zip
inflating: dvdrental.tar
载入数据
➜ learn-postgresql git:(master) ✗ pg_restore -h localhost -U postgres -d dvdrental dvdrental.tar
Password:
数据类型
注意这里只会展示一部分数据类型。pg支持的数据类型很多。官方文档
文本类型
- VARCHAR(n) 长度有最大限制。VARCHAR(255)表示最大长度为255个字符的字符串。
- TEXT 用于存储任意长度的文本字符串。与VARCHAR不同，TEXT类型没有最大长度限制。
数字类型
- SMALLINT：2字节整数类型，范围为-32768到+32767。
- INTEGER：4字节整数类型，范围为-2147483648到+2147483647。
- BIGINT：8字节整数类型，范围为-9223372036854775808到+9223372036854775807。 4. NUMERIC(p, s)：精确数值类型，其中p表示总位数，s表示小数位数。例如，NUMERIC(10, 2)表示最多有10位数字，其中2位是小数位。
- REAL：4字节浮点数类型，可以存储6位小数。
- DOUBLE PRECISION：8字节浮点数类型，可以存储15位小数。
- Serial 类似INTEGER，只是serial是自增的。通常用于作为表的主键。
时间/日期
- TIMESTAMP 时间戳
- INTERVAL 时间长度。类似于python中的timedelta。
数组
创建数据表
查询
在查询之前，我们应该先熟悉一下pg的数据类型。但是本章中用到的数据类型都是关系型数据库中常见的，就不详细说明了。后面有一章专门展示pg的数据类型。
现在我们就可以查询我们刚刚导入的数据了。
\l命令列出所有数据库
➜ learn-postgresql git:(master) ✗ psql -h localhost -p 5432 -U postgres
Password for user postgres:
psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1), server 15.2 (Debian 15.2-1.pgdg110+1))
WARNING: psql major version 14, server major version 15.
Some psql features might not work.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
dvdrental | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#
选择dvdrental。psql可以通过tab键自动补全。
postgres=# \c dvdrental
psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1), server 15.2 (Debian 15.2-1.pgdg110+1))
WARNING: psql major version 14, server major version 15.
Some psql features might not work.
You are now connected to database "dvdrental" as user "postgres".
dvdrental=#
\dt列出所有table
dvdrental=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | actor | table | postgres
public | address | table | postgres
public | category | table | postgres
public | city | table | postgres
public | country | table | postgres
public | customer | table | postgres
public | film | table | postgres
public | film_actor | table | postgres
public | film_category | table | postgres
public | inventory | table | postgres
public | language | table | postgres
public | payment | table | postgres
public | rental | table | postgres
public | staff | table | postgres
public | store | table | postgres
(15 rows)
dvdrental=#
以上就是刚刚导入的数据表了。
select
select * from customer;查询customer表中的所有列。select * from SOME_TABLE;不是个好的查询方式，应当指定查询哪些列。
select first_name, last_name from customer;查询customer表里的first_name, last_name两列。
order by
排序。以下语句，是从customer表中查询first_name, last_name，按照first_name升序。
SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name ASC;
类似地，DESC是降序。 pg支持在order by时，指定NULL值放在最前还是最后。 创建一个表然后尝试一下
-- create a new table
CREATE TABLE sort_demo(
num INT
);
-- insert some data
INSERT INTO sort_demo(num)
VALUES(1),(2),(3),(null);
dvdrental=# select num from sort_demo order by num NULLS FIRST;
num
-----
1
2
3
(4 rows)
dvdrental=# select num from sort_demo order by num NULLS LAST;
num
-----
1
2
3
(4 rows)
select distinct
筛选出非重复的行。注意，如果查询的是多列，那么pg会用各列的值的组合来判断重复的行。 先创建和表，然后尝试一下。
dvdrental=# CREATE TABLE distinct_demo (
id serial NOT NULL PRIMARY KEY,
bcolor VARCHAR,
fcolor VARCHAR
);
CREATE TABLE
dvdrental=# INSERT INTO distinct_demo (bcolor, fcolor)
VALUES
('red', 'red'),
('red', 'red'),
('red', NULL),
(NULL, 'red'),
('red', 'green'),
('red', 'blue'),
('green', 'red'),
('green', 'blue'),
('green', 'green'),
('blue', 'red'),
('blue', 'green'),
('blue', 'blue');
INSERT 0 12
dvdrental=# SELECT
DISTINCT bcolor,
fcolor
FROM
distinct_demo
ORDER BY
bcolor,
fcolor;
bcolor | fcolor
--------+--------
blue | blue
blue | green
blue | red
green | blue
green | green
green | red
red | blue
red | green
red | red
red |
| red
(11 rows)
也可以使用DISTINCT ON来让pg对查询到的行进行分组，每一组只返回一行。
dvdrental=# SELECT
DISTINCT ON (bcolor) bcolor,
fcolor
FROM
distinct_demo
ORDER BY
bcolor,
fcolor;
bcolor | fcolor
--------+--------
blue | blue
green | blue
red | blue
| red
(4 rows)
这里我们按照 bcolor 进行分组，然后按照 fcolor 排序，返回每个分组的bcolor和这个组最小的fcolor。
where
根据条件来对列进行筛选。 注意如下语句中的引号要用单引号。
dvdrental=# select last_name, first_name from customer where first_name = 'Jamie';
last_name | first_name
-----------+------------
Rice | Jamie
Waugh | Jamie
(2 rows)
支持使用通配符。以下语句中的WHERE first_name LIKE 'Ann%'，匹配first_name以Ann开头的行。
dvdrental=# SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE 'Ann%';
first_name | last_name
------------+-----------
Anna | Hill
Ann | Evans
Anne | Powell
Annie | Russell
Annette | Olson
(5 rows)
分页
使用LIMIT和OFFSET来
现在有一个film表。我们大致看一下它是什么样的。
select film_id, title from film order by film_id;
film_id | title
---------+-----------------------------
1 | Academy Dinosaur
2 | Ace Goldfinger
3 | Adaptation Holes
4 | Affair Prejudice
5 | African Egg
6 | Agent Truman
7 | Airplane Sierra
8 | Airport Pollock
9 | Alabama Devil
10 | Aladdin Calendar
现在我们跳过前3个file_id，而且只查询最多3行。
dvdrental=# SELECT
film_id,
title,
release_year
FROM
film
ORDER BY
film_id
LIMIT 3 OFFSET 3;
film_id | title | release_year
---------+------------------+--------------
4 | Affair Prejudice | 2006
5 | African Egg | 2006
6 | Agent Truman | 2006
(3 rows)
以上就是根据file_id来分页，每页3行，选第2页。
使用LIMIT和FETCH
FETCH类似于OFFSET。
IN
判断某个值是不是指定的一些值中的一个
dvdrental=# SELECT customer_id,
rental_id,
return_date
FROM
rental
WHERE
customer_id IN (1, 2)
ORDER BY
return_date DESC;
以上语句，从rental表里，筛选出custom_id 为1或2的行，按照return_date降序，返回其中的customer_id,rental_id,return_date。
group by
根据某个字段进行分组，对组里的列，进行统计。 以下语句，对payment表，根据custom_id分组，统计每个custom_id的amount的总和。
dvdrental=# SELECT
customer_id,
SUM (amount)
FROM
payment
GROUP BY
customer_id;
支持group by多个列。例如
SELECT customer_id, staff_id, SUM(amount) FROM payment GROUP BY staff_id, customer_id ORDER BY customer_id;根据staff_id和customer_id字段来分组。结果类似于这样。
customer_id | staff_id | sum
-------------+----------+--------
1 | 2 | 53.85
1 | 1 | 60.85
2 | 2 | 67.88
2 | 1 | 55.86
3 | 1 | 59.88
3 | 2 | 70.88
4 | 2 | 31.90
每一行的(customer_id, staff_id)都是唯一的。 这是统计每个customer和每个staff之间的amount的总和。
HAVING
having和where语法上有点像，但是使用方式完全不同。where是对行进行过滤的，having是对分组数据进行过滤的。
以下语句，
SELECT customer_id, SUM (amount) FROM payment GROUP BY customer_id HAVING SUM (amount) > 200;
以下语句，对payment表，根据custom_id分组，统计每个custom_id的amount的总和，然后过滤，只保留总amount大于200的结果。
JOIN
只返回两个表中匹配的行。INNER JOIN 默认使用等值连接，但也可以使用其他比较操作符。 在关系型数据库里，数据常常储存在多个表里。所以，常常需要从多个表查询数据。此时需要用到JOIN。JOIN有好几种，INNER JOIN，LEFT JOIN，RIGHT JOIN等。
INNER JOIN
INNER JOIN 是最常用的 join 类型，因为它可以快速地返回两个表中匹配的行，而且通常比其他 join 类型的效率更高。 现在我们有两个表，film和inventory。film里的一行表示一个电影，inventory的一行表示一个电影的一个库存。film和inventory是一对多的关系。film的一行可能对应inventory的一行或多行，或者，找不到任何一行与其对应。
dvdrental=# SELECT
film.film_id,
title,
inventory_id
FROM
film
INNER JOIN inventory
ON inventory.film_id = film.film_id
ORDER BY title;
使用INNER JOIN将这两个表连接起来，以便在同一行中展示每个电影的名字和1个库存。其中电影名字是从小到大排列的。 有的电影没有任何库存，这样的电影不会出现在返回结果里，因为只返回两个表中匹配的行。
dvdrental=# SELECT
film.film_id,
title,
inventory_id
FROM
film
INNER JOIN inventory
ON inventory.film_id = film.film_id
WHERE film.title = 'Chamber Italian';
film_id | title | inventory_id
---------+-----------------+--------------
133 | Chamber Italian | 612
133 | Chamber Italian | 613
133 | Chamber Italian | 614
133 | Chamber Italian | 615
(4 rows)
dvdrental=# SELECT
film.film_id,
title,
inventory_id
FROM
film
INNER JOIN inventory
ON inventory.film_id = film.film_id
WHERE film.title = 'Alice Fantasia';
film_id | title | inventory_id
---------+-------+--------------
(0 rows)
以上查询和结果表示，Chamber Italian是有库存的，Alice Fantasia没有库存，所以没出现在结果之中。
特色数据类型
相关的官方文档 数据类型很多。使用者容易找到适合业务的数据类型。 比如，用boolean储存只有两个值的字段。用cidr储存ipv4或ipv6网络地址，json储存文本json数据，money储存金额，uuid类型，数组类型。 而且，使用者还可以自己创建数据类型。
一些数据类型
这里展示一些数据类型的用法
TIMESTAMP
在web服务中最好以时间戳来代替使用日期时间。 在我看来，至少有以下优势：
- 储存和发送数据（例如后端向前端）时，不用考虑任何时区相关的问题。
- 解析时间变得很简单。日期时间的格式化有很多种格式。前几天我开发遇到一个问题，前端向后端发送数据的时候，因为格式不一致而解析失败，需要先转换日期时间字符串的格式。而时间戳格式很少。
