概述

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服务器。

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

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

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

  3. 生成postgres的默认配置文件

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

  4. 创建使用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

  5. 创建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

  6. 编译镜像

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

  7. 启动容器，此时一个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)

  2. 使用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=#

  3. 创建数据库

    postgres=# CREATE DATABASE book_store;
CREATE DATABASE

  4. 查看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

  5. 切换到book_store数据库

    postgres=# \c book_store
Password: 
You are now connected to database "book_store" as user "postgres".

    需要此登陆到postgres服务器的用户的密码，此处输出超级管理员的密码，docker-compose.yaml中文件中配置的helloworld

  6. 创建数据表

    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=#

  7. 查看所有表

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

book_store=#

  8. 查看指定表详情

    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=#

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

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

  10. 添加表字段

    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

  11. 删除表字段

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

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

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

book_store=# DROP TABLE b_test;
DROP TABLE

  13. 一般创建表，管理表结构都是数据库管理员来操作，我们需要创建一些用户给程序来使用，这避免了给程序使用的账号分配过大的权限，提高安全性。下面以创建一个apple用户为例

    book_store=# create user apple with password 'hello';
CREATE ROLE

    在PostgreSQL中，可以使用CREATE ROLE或CREATE USER命令来创建一个新用户。CREATE USER实质上是CREATE ROLE的一个包装器，不同之处在于CREATE USER会默认赋予登录权限

  14. 给apple用户分配权限

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

  15. 给序列分配权限

    GRANT ALL PRIVILEGES ON SEQUENCE b_book_id_seq TO PUBLIC;

    由于给表b_book添加了一个自增序列，postgres会给这个字段创建一个序列

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

  1. 打开一个终端，登陆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=>

  2. 查询b_book表数据

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

    此时还没有数据

  3. 插入表数据

    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字段是可以自增的

  4. 更新表数据

    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

  5. 删除表数据

    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如何创建数据库、如何创建表、如何创建用户、如何进行权限管理、如何使用新创建的用户对数据表进行增删改查。

推荐阅读

1. 十分钟带你入门mariadb数据库开发

2.【Git从入门到精通】系列课程03：一分钟让你成为开源的一分子/使用github托管我们的代码

3.【Go零基础系列】002：第一个Go程序

4.【Git从入门到精通】系列课程01：git的安装

5. Vs code调试Go程序时怎样查看CPU寄存器的值

6. 搭建Golang在线开发环境（随时随地码代码）

7. 使用VS Code调试Go程序

# postgresql # 数据库开发 # 数据库
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Springboot+MySQL 实现从数据库获取数据展示到前端

1.运行效果效果图如下：2.创建数据库表数据库名为"springboot",数据库表名为"user"，数据库表里面放了5条数据，字段分别为：“id”，“name”，“age”，“email”。3.新建SpringBoot项目1、选择Spring Initial...

avatar 腾讯云开发者社区

数据库 实验二 数据库的简单查询和连接查询实验

– l）用Transact-SQL语句表示下列操作．在学生选课库中实现其数据查询操作．– ①求数学系学生的学号和姓名。select sno,sname from student where sdept='MA';– ②求选修了课程的学生学号。select distinct sno from sc;– ③求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。s

avatar 腾讯云开发者社区

Spring Boot Mybatis数据库datasource多数据源配置

application.yaml文件配置spring:datasource:db1:driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://192.168.0.1:3306/ellassay_dev?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBe

avatar 腾讯云开发者社区