PostgreSQL基础知识整理

• 逻辑结构

  • 在一个实例下，允许存在多个互相独立的数据库，在一个数据库下，允许存在多个schema（schema并非完全独立，如，可存在跨越schema的外键），一个schema可以建立对象。

• 大小写和单双引号

  • 不使用双引号时: Postgraduate 会将标识符（如表名、列名等）转换为小写。

  • 双引号： 用于引用标识符，以保留其原始大小写或包含特殊字符（如空格）的标识符。

  • 单引号: 用于表示字符串文字。单引号内的内容被视为字符串。

• schema

  • 在 PostgreSQL 中，每个数据库创建时都会默认包含一个名为 public 的 schema。用户在没有指定 schema 时，所有对象（如表、视图、函数等）都会被创建在 public schema 中。一般来说，当你创建一个新用户时，默认情况下会为该用户自动创建一个同名的 schema。这个 schema 的名称与用户的用户名相同，且它通常被设置为该用户的默认 schema。

  • 但是，一个用户（或角色）可以拥有多个 schema，PostgreSQL 没有严格限制一个用户可以拥有多少个 schema。

  • 建库自带三个schema：public、pg_catalog、information_schema。

• 系统表

  • PostgreSQL 中有许多系统表（或系统视图），它们存储有关数据库的元数据和配置信息。位于pg_catalog模式下，一般都是以“pg_”开头，部分是针对当前库的，也有pg_roles、pg_database这样针对整个实例的。

  • information_schema也 是 PostgreSQL 中的一个重要 schema，用于提供符合 SQL通用 标准的数据库的元数据，其作用看似与pg_catalog重合，其实确实重合，其作用在于，作为一种标准化的查询方式，使得用户可以以一致的方式访问数据库结构信息，而不依赖于特定的数据库实现。其不提供 PostgreSQL 特有的系统细节，我们更多的在使用pg_catalog模式下的表查结构。

• 用户和权限

  • 在PostgreSQL中，用户实际上是角色的一种特殊情况。每个角色都可以被赋予特定的权限，而某些角色可以被指定为登录角色（即用户），允许它们连接到数据库。PostgreSQL 中有几个内置角色（用户），每个角色具有特定的权限和功能。以下是一些主要的内置角色：
    1. postgres：
       • 默认的超级用户角色。
       • 具有所有权限，可以执行任何操作。
    2. pg_read_all_data：
       • 允许读取所有数据库中的数据。
       • 适用于需要只读访问权限的用户。
    3. pg_write_all_data：
       • 允许对所有数据库中的数据进行写操作。
       • 适用于需要写入权限的用户。
    4. pg_execute_server_program：
       • 允许用户执行服务器程序。
    5. pg_monitor：
       • 允许监控数据库活动的权限。
       • 可以执行一些监控查询和获取状态信息。
    6. pg_signal_backend：
       • 允许用户发送信号到其他后台进程。
  • 权限决定了角色可以执行的数据库操作类型。PostgreSQL提供了多种权限类型，包括但不限于：
    • SELECT：允许查询表中的数据。
    • INSERT：允许向表中插入新行。
    • …
  • 在 PostgreSQL 中，特殊属性是赋予角色的全局权限，这些权限影响整个数据库系统的行为。通过角色的属性设置来实现不同权限级别的用户管理。

    • SUPERUSER ：超级用户。

    • CREATEDB: 允许用户创建数据库。

    • CREATEROLE: 允许用户创建其他角色。

    • LOGIN: 允许用户登录到数据库。

    • INHERIT: 允许用户继承其角色组的权限。

• 建库语句和建用户语句

  • create user xxx with password ‘xxxxx’;

  • create database xxx owner xxx;

• psql使用

  • 连入

    • psql -h host -p port -U username -d yourdatabase

  • 退出

    • \q

  • 切换库

    • \c databaseName

  • 列出所有对象结构

    • \d

  • 列出所有库

    • \l

  • 列出所有表

    • \dt

  • 列出某张表的表结构

    • \d tableName

  • 列出库下所有用户

    • \du

  • 列出所有对象结构的详细信息

    • \d+

  • 列出某张表的详细表结构

    • \d+ tableName