推荐|十分钟带你入门PostgreSQL数据库开发

概述

PostgreSQL(大部分情况通常简称postgres)是一个功能强大的开源对象关系数据库系统，PostgreSQL的起源可以追溯到1986年，是加州大学伯克利分校Postgres项目的一部分，在核心平台上拥有超过39年的积极开发经验。PostgresSQL可在所有主要操作系统上运行，自2001年以来一直符合ACID标准。且自 2023 年 9 月版本 16 版本发布以来，PostgreSQL 至少符合 SQL：2023 Core 一致性的 179 个强制性功能中的 170 个（没有任何关系数据库完全符合此标准）。

下面我们来看一下目前PostgreSQL的流行程度，截至到2024年PostgreSQL在关系型数据库中占据了17.2%的市场份额，排名第二，如下图所示：
在这里插入图片描述

我们再来看一下Postgres在github上与Mysql的受欢迎程度

在这里插入图片描述

可以看出PostgreSQL的Fork数和Star数均小优于Mysql。PostgreSQL相对于Mysql来说也有很多优势所在，例如支持更多的数据类型（地理空间数据类型、JSONB），支持大数据量（可以处理PB级别的数据），接下来我们来看一看PostgreSQL和MySQL的优缺点。

在这里插入图片描述

总上述所，作为一名编程开发人员，有必要学习一下市场占有率排名第二的PostgreSQL，接下来，本文将从零开始带领读者搭建postgresSQL，并创建一个数据库演示在PostgreSQL使用SQL对数据进行存取。

安装PostgreSQL

为了降低安装的复杂性，降低读者学习PostgreSQL入门的门槛，本文将使用Docker容器化技术快速创建一个可以使用的PostgreSQL服务器。

安装Docker，安装教程可以参考《使用国内源安装docker》

创建mariadb目录，用于管理docker compose

jagitch@jagitch-MS-7B93:~$ mkdir postgres && cd postgres

生成postgres的默认配置文件

jagitch@jagitch-MS-7B93:postgres$ docker run -i --rm postgres \
cat /usr/share/postgresql/postgresql.conf.sample > my-postgres.conf

创建使用zh_CN.UTF-8的postgres的Dockerfile文件

jagitch@jagitch-MS-7B93:postgres$ touch Dockerfile

Dockerfile文件内容如下：

FROM postgres
RUN localedef -i zh_CN -c -f UTF-8 -A /usr/share/locale/locale.alias zh_CN.UTF-8
ENV LANG zh_CN.utf8

创建docker-compose.yaml文件

jagitch@jagitch-MS-7B93:postgres$ touch docker-compose.yaml

services:
  db:
    build: .
    restart: always
    container_name: my-postgres
    volumes:
      - ./data:/var/lib/postgresql/data/pgdata
      - ./my-postgres.conf:/etc/postgresql/postgresql.conf
      - /etc/localtime:/etc/localtime:ro
    ports:
      - '5432:5432'
    environment:
      POSTGRES_PASSWORD: "helloworld"
      PGDATA: "/var/lib/postgresql/data/pgdata"
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

编译镜像

jagitch@jagitch-MS-7B93:postgres$ docker compose build

启动容器，此时一个PostgreSQL服务器就启动好了
```
jagitch@jagitch-MS-7B93:postgres$ docker compose up -d
```
- 1

使用管理员账号创建数据库

安装postgres命令行客户端psql

jagitch@jagitch-MS-7B93:postgres$ sudo apt install postgresql-client

jagitch@jagitch-MS-7B93:postgres$ psql --version
psql (PostgreSQL) 16.3 (Ubuntu 16.3-0ubuntu0.24.04.1)

使用psql连接postgres服务器，postgres默认的超级用户名就是postgres

jagitch@jagitch-MS-7B93:postgres$ psql -h 127.0.0.1 -p 5432 -U postgres -W
Password: 
psql (16.3 (Ubuntu 16.3-0ubuntu0.24.04.1))
Type "help" for help.

postgres=#

创建数据库

postgres=# CREATE DATABASE book_store;
CREATE DATABASE

查看postgres服务器所有的数据库

postgres=# \l
                                                       List of databases
    Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
------------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 book_store | postgres | UTF8     | libc            | zh_CN.utf8 | zh_CN.utf8 |            |           | 
 postgres   | postgres | UTF8     | libc            | zh_CN.utf8 | zh_CN.utf8 |            |           | 
 template0  | postgres | UTF8     | libc            | zh_CN.utf8 | zh_CN.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1  | postgres | UTF8     | libc            | zh_CN.utf8 | zh_CN.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres

切换到book_store数据库
```
postgres=# \c book_store
Password: 
You are now connected to database "book_store" as user "postgres".
```
- 1
- 2
- 3
需要此登陆到postgres服务器的用户的密码，此处输出超级管理员的密码，docker-compose.yaml中文件中配置的helloworld

创建数据表

book_store=# CREATE TABLE b_book(
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
author VARCHAR(50) NOT NULL,
isbn VARCHAR(30)
);

CREATE TABLE
book_store=#

查看所有表

book_store=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | b_book | table | postgres
(1 row)

book_store=#

查看指定表详情

book_store=# \d b_book
                                   Table "public.b_book"
 Column |         Type          | Collation | Nullable |              Default               
--------+-----------------------+-----------+----------+------------------------------------
 id     | integer               |           | not null | nextval('b_book_id_seq'::regclass)
 name   | character varying(50) |           | not null | 
 author | character varying(50) |           | not null | 
 isbn   | character varying(30) |           |          | 
Indexes:
    "b_book_pkey" PRIMARY KEY, btree (id)

book_store=#

修改表字段，例如将author字段长度改为100

book_store=# ALTER TABLE b_book ALTER COLUMN author TYPE varchar(100);
ALTER TABLE

添加表字段

book_store=# ALTER TABLE b_book ADD COLUMN created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE

book_store=# ALTER TABLE b_book ADD COLUMN remark TEXT;
ALTER TABLE

删除表字段

book_store=# ALTER TABLE b_book DROP COLUMN remark;
ALTER TABLE

删除表，先创建表b_test，再删除b_test表

book_store=# CREATE TABLE b_test(id SERIAL);
CREATE TABLE

book_store=# DROP TABLE b_test;
DROP TABLE

一般创建表，管理表结构都是数据库管理员来操作，我们需要创建一些用户给程序来使用，这避免了给程序使用的账号分配过大的权限，提高安全性。下面以创建一个apple用户为例
```
book_store=# create user apple with password 'hello';
CREATE ROLE
```
- 1
- 2
在PostgreSQL中，可以使用CREATE ROLE或CREATE USER命令来创建一个新用户。CREATE USER实质上是CREATE ROLE的一个包装器，不同之处在于CREATE USER会默认赋予登录权限

给apple用户分配权限

book_store=# GRANT SELECT,INSERT,DELETE,UPDATE ON ALL TABLES IN SCHEMA public TO apple;
GRANT

给序列分配权限
```
GRANT ALL PRIVILEGES ON SEQUENCE b_book_id_seq TO PUBLIC;
```
- 1
由于给表b_book添加了一个自增序列，postgres会给这个字段创建一个序列

使用新创建的apple用户进行增删改查

打开一个终端，登陆mysql

jagitch@jagitch-MS-7B93:postgres$ psql -h 127.0.0.1 -p 5432 -d book_store -U apple -W
Password: 
psql (16.3 (Ubuntu 16.3-0ubuntu0.24.04.1))
Type "help" for help.

book_store=>

查询b_book表数据

book_store=> SELECT * FROM b_book;
 id | name | author | isbn | created 
----+------+--------+------+---------
(0 rows)

此时还没有数据

插入表数据

book_store=> INSERT INTO b_book(name,author)
     VALUES('红楼梦','曹雪芹');
INSERT 0 1
book_store=> INSERT INTO b_book(name,author)
     VALUES('西游记','吴承恩');

book_store=# SELECT * FROM b_book;
 id |  name  | author | isbn |          created           
----+--------+--------+------+----------------------------
  1 | 红楼梦 | 曹雪芹 |      | 2024-06-01 14:38:06.347841
  2 | 西游记 | 吴承恩 |      | 2024-06-01 14:38:15.56265
(2 rows)

可以发现，数据类型为serial的id字段是可以自增的

更新表数据

book_store=> UPDATE b_book set isbn='978-7-0200-0220-7' where name = '红楼梦';
UPDATE 1
book_store=> UPDATE b_book set isbn='978-7-0200-0873-5' where name = '西游记';
UPDATE 1

删除表数据

book_store=> DELETE FROM b_book where name = '红楼梦';
DELETE 1


book_store=# SELECT * FROM b_book;
 id |  name  | author |       isbn        |          created          
----+--------+--------+-------------------+---------------------------
  2 | 西游记 | 吴承恩 | 978-7-0200-0873-5 | 2024-06-01 14:38:15.56265
(1 row)

总结

本文详细介绍了如何使用Docker安装postgres，并使用psql演示了postgres如何创建数据库、如何创建表、如何创建用户、如何进行权限管理、如何使用新创建的用户对数据表进行增删改查。

十分钟带你入门PostgreSQL数据库开发

概述

安装PostgreSQL

使用管理员账号创建数据库

使用新创建的apple用户进行增删改查

总结

推荐阅读

评论记录：