PostgreSQL的基础使用指南

2021-11-13

1.连接到数据库 #

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

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

 1ezytutors=# SELECT current_user;
 2 current_user
 3--------------
 4 postgres
 5(1 row)
 6
 7ezytutors=# \c demo demouser
 8You are now connected to database "demo" as user "demouser".
 9demo=> SELECT current_user;
10 current_user
11--------------
12 demouser
13(1 row)

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

 1cat .bash_profile
 2# .bash_profile
 3
 4if [ -f ~/.bashrc ]; then
 5        . ~/.bashrc
 6fi
 7
 8export  PGHOME=/usr/local/pgsql
 9export  PGDATA=/home/postgres/data
10export  PGHOST=127.0.0.1
11export  PGPORT=5432
12export  PATH=$PATH:$PGHOME/bin
13export  LD_LIBRARY_PATH=/usr/local/pgsql/lib

2.用户角色管理 #

2.1 创建用户并授权 #

创建用户并设置密码:

1CREATE USER foodbuser WITH PASSWORD 'password';

修改用户密码:

1ALTER USER foodbuser WITH PASSWORD '123';

将数据库foodb授权用户foodbuser:

1GRANT ALL PRIVILEGES ON DATABASE foodb TO foodbuser;

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

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

查看用户:

1\du

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

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

删除用户

1drop user foodbuser

2.2 角色管理 #

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

 1create role role1;
 2create user user1;
 3
 4\du
 5                             List of roles
 6 Role name |                         Attributes
 7-----------+------------------------------------------------------------
 8 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 9 role1     | Cannot login # 创建的角色不带登录属性
10 user1     |
11
12SELECT rolname from pg_roles ;
13           rolname
14-----------------------------
15 pg_database_owner
16 pg_read_all_data
17 pg_write_all_data
18 pg_monitor
19 pg_read_all_settings
20 pg_read_all_stats
21 pg_stat_scan_tables
22 pg_read_server_files
23 pg_write_server_files
24 pg_execute_server_program
25 pg_signal_backend
26 pg_checkpoint
27 pg_use_reserved_connections
28 pg_create_subscription
29 postgres
30 role1
31 user1
32(17 rows)
33
34 SELECT usename from pg_user;
35 usename
36----------
37 postgres
38 user1
39(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命令来设置和修改这些属性,以控制角色的权限。

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

1CREATE ROLE role_name WITH
2  LOGIN | SUPERUSER | CREATEDB | CREATEROLE | REPLICATION | BYPASSRLS |
3  CONNECTION LIMIT connlimit |
4  PASSWORD 'password';
5
6ALTER ROLE username CONNECTION LIMIT 10;

3.创建数据库 #

 1#创建数据库
 2CREATE DATABASE <dbname>;
 3
 4#查看所有数据库, 相当于mysql的show databases
 5\l
 6
 7#切换到指定数据库, 相当于mysql的use <dbname>
 8\c <dbname>
 9
10#删除数据库
11drop database <dbname>

4.创建表 #

4.1 数据类型 #

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

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

数值数据类型 #

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

字符串字符串类型包括 #

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

日期时间 #

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

其他数据类型 #

boolean
…

4.2 创建表 #

 1# 建表
 2CREATE TABLE table1(id int,body varchar(100));
 3
 4# 插入数据
 5insert into table1(id,body) values(1,'foo');
 6
 7# 查看当前数据库下所有表，相当于mysql的show tables
 8\d
 9
10# 查看表结构，相当于mysql的desc tablename
11\d test

4.3 自增字段 #

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

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

serial是一个伪类型，用于创建一个自增的4字节整数列。serial 的范围是1到2147483647。
```
1CREATE TABLE example_table (
2    id serial PRIMARY KEY,
3    name varchar(50)
4);
```
bigserial是一个伪类型，用于创建一个自增的8字节整数列。范围是1到9223372036854775807。
```
1CREATE TABLE example_table (
2    id bigserial PRIMARY KEY,
3    name varchar(50)
4);
```

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

1CREATE TABLE example_table (
2    id smallserial PRIMARY KEY,
3    name varchar(50)
4);

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

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

5.schema #

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

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

使用模式的好处:

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

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

 1# 创建schema： 
 2create schema schema1;
 3
 4create table schema1.table1(
 5   ID   INT              NOT NULL,
 6   NAME VARCHAR (20)     NOT NULL,
 7   PRIMARY KEY (ID)
 8);
 9
10# 删除schema： 
11drop schema schema1;
12
13# 删除schema同时级联删除schema下所有对象
14drop schema schema1 CASCADE;