1、PostgreSQL 数据库入门

本文实践环境

  • macOS Mojave 10.14.6
  • docker 19.03.1
  • docker-compose 1.24.1
  • node 11.10.1

什么是 PostgreSQL ?

PostgreSQL简称PG,是一款功能强大且开源免费的关系型数据库。它支持大部分 SQL 标准并且提供了许多现代特性:

  • 复杂查询
  • 外键
  • 触发器
  • 可更新视图
  • 事务完整性
  • 多版本并发控制

同样,PostgreSQL可以用许多方法扩展,比如, 通过增加新的:

  • 数据类型
  • 函数
  • 操作符
  • 聚集函数
  • 索引方法
  • 过程语言

docker 安装

在项目根目录下新建一个 docker-compose.yml 文件:

version: '3'

services:
    postgres:
        container_name: pg
        image: postgres:9.6  # 使用的是 9.6 版本的 pg 数据库
        restart: always
        ports:
            - '54321:5432'   # 把容器的 5432 端口映射到本机的 54321端口
        environment:
            POSTGRES_PASSWORD: 123456 # 设置 postgres 用户的默认密码
        volumes:
            - ./.docker/postgres-data:/var/lib/postgresql/data  # 把数据库目录挂载出来

让pg数据库服务在后台运行

 $ docker-compose up -d

查询容器运行情况

$ docker ps

尝试登录数据库

$ docker exec -it ${容器ID} psql -U postgres
image.png

什么是 psql ?

psql 是 pg 数据库中的一个命令行交互式客户端工具
使用 psql 连接数据库的方法:
psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]

这些连接参数也可以用环境变量指定,比如:

  • export PGDATABASE=testdb
  • export PGHOST=127.0.0.1
  • export PGPORT=5432
  • export PGUSER=postgres

psql 的常用命令

psql 的命令都是以斜杠 \ 开头的。
\h 查看帮助信息
\l 查看所有数据库
\q 退出 psql
\d [ pattern ] 该命令将显示每个匹配关系(表、视图、索引、序列)的信息,可以加上 + 查看更多的信息,如 \d+

  1. 不加任何参数表示查看当前数据库的所有表。

  2. \d tablename 后面跟一个表名,表示显示这个表的结构定义

  3. \d indexname 也可以显示索引的信息,如 \d 索引名称

  4. \d * 后面也可以跟通配符如 *?\d x*

  5. \d+将显示比 \d 更详细的信息,还会显示任何与表关系的注释,以及表中出现的 OID

\dt 只显示匹配的表
\di 只显示索引
\ds 只显示序列
\dv 只显示视图
\df 只显示函数

  1. \timing on\timing off 显示 SQL 已执行的时间,默认情况下是 off

  2. \dn 列出所有的 schema

  3. \du\dg 列出所有的数据库用户和角色

  4. \db 显示所有的表空间,表空间其实是一个目录,放在这个表空间的表,就是把表的数据文件发到这个表空间下。

  5. \dp\z 显示表的权限分配情况

  6. \encoding 指定客户端的字符编码,如 \encoding UTF8;

  7. \pset 设置输出的格式,\pset border 0 : 表示输出内容无边框。 border 1 :表示边框只在内部。border 2 : 内外都有边框

  8. \x 把表中的每一行的每列数据都拆分为单行展示,与 MySQL 中的 \G 的功能类似。

  9. \echo 用于输出一行信息,通常用于在 .sql 文件中输出一些提示信息。

  10. \password 设置密码

  11. \conninfo 列出当前数据库连接的信息

  12. \dx 查看数据库中安装的扩展 或 select * from pg_extension;

执行存储在外部文件中的 SQL 命令

\i <文件名> 执行存储在外部文件中的 sql 语句
当然也可以在 psql 命令行加 -f <filename> 来执行 SQL 脚本文件中的命令,如 psql -f ./test.sql

psql 的使用技巧和注意事项

  1. 在启动 psql 命令后中加 "-E" 参数,就可以把 psql 中各种以 "" 开头的命令执行的实际 SQL 打印出来,如下 psql -E postgres,如果想关闭此功能,可以使用 \set ECHO_HIDDEN on | off

  2. 自动提交方面的技巧

psql 中的事务是自动提交的,可以运行 begin; 然后执行 dml 语句,最后再执行 commit 或 rollback 语句。或 直接使用 psql 中的命令关闭自动提交的功能。\set AUTOCOMMIT off

  1. 查看数据库、表、索引大小
  select pg_size_pretty(pg_table_size('test'));   
  select pg_size_pretty(pg_database_size('david'));
  select pg_size_pretty(pg_indexes_size('test')); 

推荐阅读更多精彩内容