青蛙小白
博客 / 2021/11

PostgreSQL的基础使用指南

2021/11/13 · — 字 · 阅读约 — 分钟 ·
#postgresql
目录

1.连接到数据库

#psql -h 服务器 -p 端口地址 -U 用户名 -d 数据库
psql -h 127.0.0.1 -p 5432 -U <dbuser> -d <dbname>

连接到数据库，进入PostgreSQL提示符即(psql shell)后，可以使用\c dbname dbuser切换登录用户，使用SELECT current_user;查看当前连接使用的数据库用户。

ezytutors=# SELECT current_user;
 current_user
--------------
 postgres
(1 row)

ezytutors=# \c demo demouser
You are now connected to database "demo" as user "demouser".
demo=> SELECT current_user;
 current_user
--------------
 demouser
(1 row)

如果是从服务器上登录, 使用su - postgres切换到postgres用户，会执行/home/postgres/.bash_profile，.bash_profile文件中有PG相关的环境变量，psql命令也被添加到了PATH环境变量中。

cat .bash_profile
# .bash_profile

if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

export  PGHOME=/usr/local/pgsql
export  PGDATA=/home/postgres/data
export  PGHOST=127.0.0.1
export  PGPORT=5432
export  PATH=$PATH:$PGHOME/bin
export  LD_LIBRARY_PATH=/usr/local/pgsql/lib

2.用户角色管理

2.1 创建用户并授权

创建用户并设置密码:

CREATE USER foodbuser WITH PASSWORD 'password';

修改用户密码:

ALTER USER foodbuser WITH PASSWORD '123';

将数据库foodb授权用户foodbuser:

GRANT ALL PRIVILEGES ON DATABASE foodb TO foodbuser;

从PG 15开始对public schema的权限控制有了一些改变，移除了默认的PUBLIC权限，需要显式授予用户对public schema的权限。下面的命令首先以postgres用户连接到foodb，然后再授予foodbuser用户对public schema的所有权限。

\c foodb postgres
GRANT ALL PRIVILEGES ON SCHEMA public TO foodbuser;

查看用户:

\du

收回foodbuser用户在数据库foodb上的所有权限:

REVOKE ALL PRIVILEGES ON DATABASE foodb from foodbuser;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public from foodbuser;

删除用户

drop user foodbuser

2.2 角色管理

在PostgreSQL里没有区分用户和角色的概念，“CREATE USER” 为 “CREATE ROLE” 的别名，这两个命令几乎是完全相同的，唯一的区别是"CREATE USER" 命令创建的用户默认带有LOGIN Attribute，而"CREATE ROLE" 命令创建的用户默认不带LOGIN Attribute。

create role role1;
create user user1;

\du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 role1     | Cannot login # 创建的角色不带登录属性
 user1     |

SELECT rolname from pg_roles ;
           rolname
-----------------------------
 pg_database_owner
 pg_read_all_data
 pg_write_all_data
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 pg_checkpoint
 pg_use_reserved_connections
 pg_create_subscription
 postgres
 role1
 user1
(17 rows)

 SELECT usename from pg_user;
 usename
----------
 postgres
 user1
(2 rows)

在PostgreSQL中,可以为角色(Role)设置各种属性(Attributes),主要的角色属性包括:

  • LOGIN - 角色是否可以登录,默认是NO。
  • SUPERUSER - 是否是超级用户,默认 NO。超级用户拥有所有权限。
  • CREATEDB - 是否可以创建数据库,默认NO。
  • CREATEROLE - 是否可以创建角色,默认NO。
  • INHERIT - 角色是否可以继承父角色的权限,默认YES。
  • REPLICATION - 角色是否可以进行流复制,默认NO。
  • BYPASSRLS - 角色是否无视行级安全性策略,默认NO。
  • CONNECTION LIMIT - 角色可以建立的连接数限制,默认无限制。
  • VALID UNTIL - 角色的有效期限制。
  • RESOURCE QUEUE - 角色进行查询所在的资源队列。
  • PASSWORD - 角色的登录密码。 可以使用ALTER ROLE命令来设置和修改这些属性,以控制角色的权限。

正确设置角色属性很重要,可以提高数据库安全性,并控制不同角色的访问权限。

CREATE ROLE role_name WITH
  LOGIN | SUPERUSER | CREATEDB | CREATEROLE | REPLICATION | BYPASSRLS |
  CONNECTION LIMIT connlimit |
  PASSWORD 'password';

ALTER ROLE username CONNECTION LIMIT 10;

3.创建数据库

#创建数据库
CREATE DATABASE <dbname>;

#查看所有数据库, 相当于mysql的show databases
\l

#切换到指定数据库, 相当于mysql的use <dbname>
\c <dbname>

#删除数据库
drop database <dbname>

4.创建表

4.1 数据类型

PG表的列主要有三种数据类型:

  • 数值数据类型
  • 字符串数据类型
  • 日期/时间数据类型

数值数据类型

类型名称存储长度描述范围
smallint2字节16位整数-32768 到 +32767
integer4字节32位整数-2147483648 到 +2147483647
bigint8字节64位整数-9223372036854775808 到 +9223372036854775807
numeric或decimal变长用户指定精度的定点数，精确小数点前131072位，小数点后16383位
money8字节等同于numeric(10,2)用于存储货币金额的场景-92233720368547758.08 到 +92233720368547758.07
real4字节表示可变精度的浮点数，不精确大约6位十进制数字精度,1.18e-38到3.4e38
double precision8字节表示可变精度的浮点数，不精确大约15位十进制数字精度, 2.22e-308到1.79e308

字符串字符串类型包括

  • char(size)，character(size): 固定长度字符串，size规定了需存储的字符数，由右边的空格补齐
  • varchar(size)，character varying(size): 可变长度字符串，size规定了需存储的字符数
  • text: 可变长度字符串。

日期时间

  • timestamp: 日期和时间
  • date: 日期，无时间
  • time: 时间

其他数据类型

  • boolean
  • …

4.2 创建表

# 建表
CREATE TABLE table1(id int,body varchar(100));

# 插入数据
insert into table1(id,body) values(1,'foo');

# 查看当前数据库下所有表，相当于mysql的show tables
\d

# 查看表结构，相当于mysql的desc tablename
\d test

4.3 自增字段

PostgreSQL使用序列来标识字段的自增长，有smallserial、serial和bigserial。这些类似于MySQL的AUTO_INCREMENT属性。

smallserial、serial 和 bigserial 不是PostgreSQL中的独立数据类型，而是与序列相关的几个数据类型的别名或称为伪类型。它们通常用于创建一个自增的整数列，常用于作为主键的数据类型。

  1. serial是一个伪类型，用于创建一个自增的4字节整数列。serial 的范围是1到2147483647。

    CREATE TABLE example_table (
    id serial PRIMARY KEY,
    name varchar(50)
);

  2. bigserial是一个伪类型，用于创建一个自增的8字节整数列。范围是1到9223372036854775807。

    CREATE TABLE example_table (
    id bigserial PRIMARY KEY,
    name varchar(50)
);

  3. smallserial是一个为类型，用于创建一个自增的2字节整数列。范围是1到32767。

    CREATE TABLE example_table (
    id smallserial PRIMARY KEY,
    name varchar(50)
);

这些伪类型的使用使得在插入数据时，系统会自动为列生成递增的唯一值，这对于创建自动递增的主键非常方便。

INSERT INTO example_table (name) VALUES( 'foo');
INSERT INTO example_table (name) VALUES( 'bar');

5.schema

PostgreSQL的schema(模式)可以看做是表的集合。一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会引起冲突，例如schema1和schema2都可以包含名为table1的表。

使用模式的好处:

  • 允许多个用户使用一个数据库并且不会互相干扰。
  • 将数据库对象组织成逻辑组以便更容易管理。
  • 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套。

# 创建schema： 
create schema schema1;

create table schema1.table1(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   PRIMARY KEY (ID)
);

# 删除schema： 
drop schema schema1;

# 删除schema同时级联删除schema下所有对象
drop schema schema1 CASCADE;
TAGS # postgresql
评论