Postgresql从小白到高手 九 : psql高级查询及内部视图使用

Postgresql从小白到高手 九:pgsql 复杂查询及内部表高级查询

---

---

一、多表查询

内联 ：inner join ··· on 简写 join ··· on
结果集只有符合 筛选条件的才展现。

外联
left outer join ···· on 简写 – left join ·· on
这个查询是一个左外连接， 因为在连接操作符左部的表中的行在输出中至少要出现一次， 而在右部的表的行只有在能找到匹配的左部表行时才被输出。 如果输出的左部表的行没有对应匹配的右部表的行，那么右部表行的列将填充空值（null）。

右联接
right join ··· on
与左连接相对应，右表展现，左表符合条件展现，无符合数据列展现为null

全外连接
full outer join ··· on
同时在查询结果上做左连接和右连接，不满足条件时，值为null 。

自连接
select * from test1 t1 join test1 t2 on t1.code = t2.code
自连接在处理 字段细节分析很有效果 。

union all
将结果集合并，不会删除重复行，但要求左右结果集有相同列

二、pgsql内部表

1.内部表

两个页：
information_schema：
表信息：
information_schema.tables ，相当于Oracle中的all_tables
字段信息：
information_schema.columns，相当于Oracle中的all_tab_cloumns
约束信息：
information_schema.table_constraints
权限信息：
table_privileges中记录了表权限，column_privileges中记录了列上的权限，routine_privileges上记录了function/procedure的权限，role_usage_grants记录了sequence/domain等类型的对象的usage权限，跟usage_privileges类似
视图信息：
Views中记录视图基础信息，view_table_usage记录视图所依赖的表，view_routine_usage记录所依赖的function, view_column_usage记录所涉及的字段
查视图：
select * from information_schema.views
查表列信息：
SELECT
*
FROM
information_schema.columns
WHERE
table_name = ‘employee’
ORDER BY
ordinal_position;

查函数：
select * from information_schema.routines where routine_type = ‘FUNCTION’;
查触发器：
select * from information_schema.triggers;

pg_catalog
查询索引：
select * from pg_catalog.pg_indexes;
查视图：
select * from pg_catalog.pg_views;

表名字	用途
pg_aggregate	聚集函数
pg_am	索引访问方法
pg_amop	访问方法操作符
pg_amproc	访问方法支持过程
pg_attrdef	字段缺省值
pg_attribute	表的列(也称为”属性”或”字段”)
pg_authid	认证标识符(角色)
pg_auth_members	认证标识符成员关系
pg_autovacuum	每个关系一个的自动清理配置参数
pg_cast	转换(数据类型转换)
pg_class	表、索引、序列、视图(“关系”)
pg_constraint	检查约束、唯一约束、主键约束、外键约束
pg_conversion	编码转换信息
pg_database	本集群内的数据库
pg_depend	数据库对象之间的依赖性
pg_description	数据库对象的描述或注释
pg_index	附加的索引信息
pg_inherits	表继承层次
pg_language	用于写函数的语言
pg_largeobject	大对象
pg_listener	异步通知
pg_namespace	模式
pg_opclass	索引访问方法操作符类
pg_operator	操作符
pg_pltemplate	过程语言使用的模板数据
pg_proc	函数和过程
pg_rewrite	查询重写规则
pg_shdepend	在共享对象上的依赖性
pg_shdescription	共享对象上的注释
pg_statistic	优化器统计
pg_tablespace	这个数据库集群里面的表空间
pg_trigger	触发器
pg_type	数据类型

2.内部表查询应用

查锁表
SELECT
locktype,
database,
relation::regclass,
mode,
granted
FROM
pg_locks
WHERE
relation IS NOT NULL
AND mode != ‘AccessShareLock’
AND NOT granted;

查询字段在存过中的使用：
select n.nspname as “Schema”,p.proname from pg_proc p
LEFT JOIN pg_namespace n ON n.oid = p.pronamespace
where upper(prosrc) like upper(‘%account_period%’) – 表字段名称
–and n.nspname = ‘public’
and p.proname not in (‘account_period’);

查询库中字段在各个表的信息：
SELECT
table_schema || ‘.’ || table_name AS “table”,
column_name,
data_type,
is_nullable,
column_default,character_maximum_length
FROM
information_schema.columns
where column_name in (‘classcode’,‘policyno’) and table_schema =‘public’
and character_maximum_length not in (‘200’,‘15’)
ORDER BY
table_schema,
table_name,
ordinal_position;

查询表占用空间：
SELECT nspname || ‘.’ || relname AS “relation”,
pg_size_pretty(pg_total_relation_size(C.oid)) AS “total_size”
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN (‘pg_catalog’, ‘information_schema’)
AND C.relkind <> ‘i’
AND nspname !~ ‘^pg_toast’
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;

查数据库占用空间：
SELECT
pg_database.datname AS “database_name”,
pg_size_pretty(pg_database_size (pg_database.datname)) AS size_in_mb
FROM
pg_database
ORDER BY
size_in_mb DESC;

清理表空间：
VACUUM (VERBOSE, FULL, FREEZE);
VACUUM (VERBOSE, FULL, FREEZE, TABLE_NAME);

pg_repack 扩展包需安装
pg_repack-- 打包整理表空间 – pg_repack table_name;