sql显示第几条数据库(sql数据库基础语句)

概述

今天主要简单总结下PG数据库平时去查看表结构、索引、主键、外键、触发器sql显示第几条数据库的一些SQL，仅供参考。

一、查看XX表结构 \d tablename

或者如下sql显示第几条数据库：

SELECT A .attnum, A.attname AS field, T.typname AS TYPE, A.attlen AS LENGTH, A.atttypmod AS lengthvar, A.attnotnull AS NOTNULL, b.description AS COMMENT FROM pg_class C, pg_attribute A LEFT OUTER JOIN pg_description b ON A.attrelid = b.objoid AND A.attnum = b.objsubid, pg_type T WHERE C.relname = 'pg_stat_database' AND A.attnum > 0 AND A.attrelid = C.oid AND A.atttypid = T.oid ORDER BY A.attnum;

二、查看表索引SELECT A .SCHEMANAME, A.TABLENAME, A.INDEXNAME, A.TABLESPACE, A.INDEXDEF, B.AMNAME, C.INDEXRELID, C.INDNATTS, C.INDISUNIQUE, C.INDISPRIMARY, C.INDISCLUSTERED, D.DESCRIPTION FROM PG_AM B LEFT JOIN PG_CLASS F ON B.OID = F.RELAM LEFT JOIN PG_STAT_ALL_INDEXES E ON F.OID = E.INDEXRELID LEFT JOIN PG_INDEX C ON E.INDEXRELID = C.INDEXRELID LEFT OUTER JOIN PG_DESCRIPTION D ON C.INDEXRELID = D.OBJOID, PG_INDEXES A WHERE A.SCHEMANAME = E.SCHEMANAME AND A.TABLENAME = E.RELNAME AND A.INDEXNAME = E.INDEXRELNAME AND E.SCHEMANAME = 'public' --and E.RELNAME = 't_student';--或者直接查系统视图select * from pg_indexes

三、查看表主键--查询主键名称select c.relname,p.conname from pg_constraint p inner join pg_class c on p.conrelid=c.oid where p.contype='p'and c.relname='t_bdt_budget_d'--查询主键sql显示第几条数据库的详细信息SELECT C.relname, P.conname, A.attname, T.typname FROM pg_constraint P INNER JOIN pg_class C ON P.conrelid = C.oid INNER JOIN pg_attribute A ON A.attrelid = C.oid AND A.attnum = P.conkey [ 1 ] INNER JOIN pg_type T ON T.oid = A.atttypid WHERE P.contype = 'p' AND C.relname = 't_bdt_budget_d'

四、查看表外键-- 查看当前表IDSELECT oid, relname FROM pg_class WHERE relname = 'syslogfilter';-- 查看引用当前表ID作参考表的主外键约束信息SELECT * FROM pg_CONSTRAINT WHERE confrelid = '24935';-- 查看那些外键的名称SELECT oid, relname FROM pg_class WHERE oid in(SELECT conrelid FROM pg_CONSTRAINT WHERE confrelid = '24935');

五、查看触发器

\dy：查看触发器

--当前数据库所有的触发器SELECT * FROM pg_trigger--特定表的触发器SELECT * FROM pg_trigger t, pg_class c WHERE t.tgrelid=c.oid AND c.relname='company';

六、查看视图

\dv: 查看所有自己创建的视图 \dv+: 查看所有自己创建的视图，显示大小

 select * from pg_views

觉得有用的朋友多帮忙转发哦sql显示第几条数据库！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~