[大数据]PostgresSQL 使用实践

1： 常见的系统表和系统视图

（1）显示所有的系统表

\dt pg_*

表名字 用途

pg_class?? ?表、索引、序列、视图(“关系”)
pg_database?? ?本集群内的数据库
pg_index?? ?附加的索引信息
pg_proc?? ?函数和过程
pg_trigger?? ?触发器
pg_type?? ?数据类型

(2) 显示所有的系统视图

\dv pg_*

视图名?? ?用途
pg_cursors?? ?打开的游标
pg_locks?? ?当前持有的锁
pg_roles?? ?数据库角色
pg_settings?? ?参数设置
pg_tables?? ?表
pg_user?? ?数据库用户
pg_views?? ?视图

2: 数据库的对象

PostgreSql数据库对象主要有数据库、表、用户、视图、索引、schema、函数、触发器等。

(1)：查询数据库表（系统表或用户创建的表）

[1]：select * from pg_catalog.pg_tables; 或者?select * from information_schema.tables;

[2]: select * from pg_catalog.pg_tables where tablename =?‘ XX’; 或者??select * from information_schema.tables where table_name = 'XX';

[3]: \d+ XX

(2) : 查询当前schema

select current_schema();

返回数据库中所有schema:

select * from?information_schema.schemata;

select * from pg_catalog.pg_namespace;

(3):查询当前的数据库： select current_database();

列出所有数据库：\l? 或 \l+

(4) 查看数据库中的视图 select * from pg_views where viewname = 'XXXX';

查看数据库中的视图 \dv?XXXX

(5) 查看表空间

\db

(6)查看用户信息

\dn (得到schema 和 owner信息)

(7) \d: 列出当前数据库的所有表格

\d [table_name]: 列出某一张表格的表结构

(8) 查看当前数据库用户：select * from current_user; / select current_user;

3： PG的权限体系

（1）数据库权限

• 允许连接数据库 ：默认情况下，数据库在创建后，允许public角色连接，即允许任何人连接

? ? ? ?(1) create user user_name password 'XXXX';

? ? ? ? (2) \du （查看创建的user）

? ? ? ? (3) 可以用这个user_name + password来连接数据库

? ? ? ? (4) 可以创建对象，但是都是创建到public shema上面，例如 create table test_t(a int); 可以用\d+ [test_name]来查在哪个schema、owner下。

• 允许创建数据库和调整数据库

? ? ? ? (1) 查看默认创建的用户权限

? ? ? ? ? ??select * from information_schema.table_privileges where grantee = 'default_user_name';

? ? ? ? ? ? 或者 \du

? ? ? ? (2) 创建数据库 \h create database? （查看创建数据库语法）

? ? ? ? ? ? ? ? create databse db_name owner='user_name' ; (这个user是具有创建database的权限的)

? ? ? ? (3) 超级用户进行用户的授权，之后

? ? ? ? ? ? ? ? create user user_name password 'XXXX'; (创建一个普通用户)

????????????????alter uname?createdb; (超级用户给用户user_name赋予创建数据库的权限)

? ? ? ? ? ? ? ? \du 查看

????????????????create databse db_name owner='user_name' ;

? ? ? ? ? ? ? ? 使用创建用户登录数据库 \c??db_name?db_name

• 允许数据中创建schema

????????默认情况下，数据库在创建后，不允许除了超级用户和owner之外的任何人在数据库中创建schema。

????????默认情况下，数据库在创建后，会自动创建名为public 的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象。

(2) 用户权限

角色/用户 在CREATE USER创建时赋予权限，ALTER USER方式变更权限。

角色/用户在GRANT方式赋予权限，REVOKE方式撤销权限

（3）pg_user、pg_authid、pg_roles、pg_auth_members区别

\d pg_authid

\d pg_user

\d pg_auth_members

（3）schema权限

? ????????一个数据库包含一个或多个已命名的模式，模式又包含表。模式还可以包含其它对象，包括数据类型、函数、操作符等。同一个对象名可以在不同的模式里使用而不会导致冲突；比如，schema1和myschema都可以包含一个名为mytable的表。和数据库不同，模式不是严格分离的：只要有权限，一个用户可以访问他所连接的数据库中的任意模式中的对象。

????????schema级别的权限，包括允许查看schema中的对象，允许在schema中创建对象。
? 默认情况下新建的schema的权限不会赋予给public角色，因此除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象。

schema的特点

• schema概念像命名空间
• schema下不能再有schema嵌套
• 各个对象比如表,函数等存放在各个schema下
• 同一个schema下不能有重复的对象名字,但在不同schema下可以重复

使用schema的作用

• 方便管理多个用户共享一个数据库,但是又可以互相独立.
• 方便管理众多对象,更有逻辑性
• 方便兼容某些第三方应用程序,创建对象时是有schema的

查看schema： \dn 或 \dnS+

创建schema: create schema shcema_name;

在schema上撞见表对象：create table schema_name.table_name(a int); 这样创建在?shcema_name上。要是不指定的话，默认是创建在public上的。对于指定的创建，他的查询、删除也要指schema。

删除schema: drop schema schema_name

drop schema schema_name cascade; (这样就会把这个schema下面的表也给删了)

其他相关的请查看链接：【PG管理维护】-Postgresql权限体系(万字总结测试) - 墨天轮

阅读世界，共赴山海

423全民读书节，邀你共读