小白篇(二十二)：关系型数据库常用元数据(Mysql\Postgresql)

-- mysql下常用元数据表
SCHEMATA -- 数据库信息表
TABLES -- 表记录表
COLUMNS -- 表字段记录表

-- 查询mysql实例下,有哪些db
select SCHEMA_NAME from information_schema.SCHEMATA;


-- 查询表所属db\表名称\表创建时间\表更新时间\表描述
select 
TABLE_SCHEMA,TABLE_NAME,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT
from information_schema.TABLES;
-- 说明：里面还包含表很多信息，例如：表占磁盘大小，表数据量多少


-- 查询表所属db\表名称\字段名称\字段顺序\字段类型\字段描述
select 
 table_schema,table_name,column_name,ordinal_position,data_type,column_comment
from information_schema.columns;

-- 在postgresql一个db下，常用的元数据表
select * from pg_catalog.pg_database;-- 记录所有db信息
select * from pg_catalog.pg_tables; -- 记录当前db下表信息
select * from pg_catalog.pg_namespace;-- 记录当前db下命名空间
select * from pg_catalog.pg_attribute;-- 记录当前db下字段信息
select * from pg_catalog.pg_class; -- 记录当前db下,命名空间、表、字段关系
-- 也可以写成 数据库.pg_catalog.pg_database 三层结构
-- 如: my_test_db.pg_catalog.pg_database;
-- 理解为: 数据库.命名空间.表

-- 查询所有数据库
select * from pg_catalog.pg_database;
-- 说明：只要查询所有db时，不会受到每个db限制。
-- 其它查询都是限制在某个db下的

-- 查询表信息,只能查询到当前连接的db下的所有表
select * from pg_catalog.pg_tables;

-- 查询命名空间名称\表名称\表描述
select 
n.nspname,
c.relname,
cast(obj_description(c.relfilenode,'pg_class') as varchar) as comment 
from 
pg_catalog.pg_namespace n
inner join
pg_class c on n.oid = c.relnamespace
where  c.relkind = 'r' and c.relname not like 'pg_%' and c.relname not like 'sql_%' ;

-- 查询命名空间'stg'下，表'test_2020'的字段\描述\类型\顺序
SELECT
n.nspname,
c.relname,
a.attname,
col_description(a.attrelid,a.attnum) as comment,
format_type(a.atttypid,a.atttypmod) as type,
a.attnum
FROM 
pg_namespace n
inner join pg_class c on n.nspname ='stg' and n.oid = c.relnamespace
inner join pg_attribute a on c.relname = 'test_2020' and a.attrelid = c.oid and a.attnum>0;