织梦cms仿某作文网站整站源码(带采集)安装数据库,网站建设深圳公司,衡水做阿里巴巴网站,网站开发验证码图片不显示如何通过SQL 的方式获得数据库中的一些关键信息#xff0c;是一个DB最正常的工作#xff0c;如何通过一些SQL来获得PG的一些关键的参数和信息或者是数据库中的一些信息是需要知道的一件事情。以下是部分 1一般来说每种数据库中都有一个或几个系统的数据库#xff0c;在PG中s… 如何通过SQL 的方式获得数据库中的一些关键信息是一个DB最正常的工作如何通过一些SQL来获得PG的一些关键的参数和信息或者是数据库中的一些信息是需要知道的一件事情。以下是部分 1一般来说每种数据库中都有一个或几个系统的数据库在PG中schemaname 以 pg_catalog开头都是系统表通过系统表我们就可以了解的大部分数据库系统所做的事情1 查看当前所有的表(用户表)SELECT relname FROM pg_class WHERE relname !~ ^(pg_|sql_) AND relkind r;或者也可以SELECT table_name FROM information_schema.tables WHERE table_type BASE TABLE AND table_schema NOT IN (pg_catalog, information_schema);2 查看用户建立的VIEWSELECT table_name FROM information_schema.views WHERE table_schema NOT IN (pg_catalog, information_schema) AND table_name !~ ^pg_;3 当前数据库的用户SELECT usename FROM pg_user;4 列出某个表的字段SELECT a.attname FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname 表名 AND a.attnum 0 AND a.attrelid c.oid AND a.atttypid t.oid5 查询表的索引SELECT relname, indkey FROM pg_class, pg_index WHERE pg_class.oid pg_index.indexrelid AND pg_class.oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname表名 AND pg_class.oidpg_index.indrelid AND indisunique ! t AND indisprimary ! t);查询这个表的那些字段被建立了索引SELECT t.relname, a.attname, a.attnum FROM pg_index cLEFT JOIN pg_class t ON c.indrelid t.oidLEFT JOIN pg_attribute a ON a.attrelid t.oid AND a.attnum ANY(indkey) WHERE t.relname 表名 ;获得当前数据库表的建立索引的语句SELECT tablename, indexname, indexdefFROM pg_indexesWHERE schemaname publicORDER BY tablename, indexname;6 系统中指定表建立的约束SELECT constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_name 表名;7 对某个约束的详细信息的展示SELECT c.conname AS constraint_name, CASE c.contype WHEN c THEN CHECK WHEN f THEN FOREIGN KEY WHEN p THEN PRIMARY KEY WHEN u THEN UNIQUE END AS constraint_type, CASE WHEN c.condeferrable f THEN 0 ELSE 1 END AS is_deferrable, CASE WHEN c.condeferred f THEN 0 ELSE 1 END AS is_deferred, t.relname AS table_name, array_to_string(c.conkey, ) AS constraint_key, CASE confupdtype WHEN a THEN NO ACTION WHEN r THEN RESTRICT WHEN c THEN CASCADE WHEN n THEN SET NULL WHEN d THEN SET DEFAULT END AS on_update, CASE confdeltype WHEN a THEN NO ACTION WHEN r THEN RESTRICT WHEN c THEN CASCADE WHEN n THEN SET NULL WHEN d THEN SET DEFAULT END AS on_delete, CASE confmatchtype WHEN u THEN UNSPECIFIED WHEN f THEN FULL WHEN p THEN PARTIAL END AS match_type, t2.relname AS references_table, array_to_string(c.confkey, ) AS fk_constraint_key FROM pg_constraint cLEFT JOIN pg_class t ON c.conrelid t.oidLEFT JOIN pg_class t2 ON c.confrelid t2.oid WHERE t.relname 表名 AND c.conname 约束名;8 列出相关的自增序列SELECT relname FROM pg_class WHERE relkind S AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE pg_% AND nspname ! information_schema);9 筛选相关数据库中建立的triggerSELECT DISTINCT trigger_name FROM information_schema.triggers WHERE trigger_schema NOT IN (pg_catalog, information_schema);以及关于 trigger 详细的信息SELECT * FROM information_schema.triggers WHERE trigger_schema NOT IN (pg_catalog, information_schema);10 查看系统中创建的函数SELECT routine_name FROM information_schema.routines WHERE specific_schema NOT IN (pg_catalog, information_schema) AND type_udt_name ! trigger;11 查看当前数据库中表的主键 SELECT tc.constraint_name, tc.constraint_type, tc.table_name, kcu.column_name, tc.is_deferrable, tc.initially_deferred, rc.match_option AS match_type, rc.update_rule AS on_update, rc.delete_rule AS on_delete, ccu.table_name AS references_table, ccu.column_name AS references_field FROM information_schema.table_constraints tcLEFT JOIN information_schema.key_column_usage kcu ON tc.constraint_catalog kcu.constraint_catalog AND tc.constraint_schema kcu.constraint_schema AND tc.constraint_name kcu.constraint_nameLEFT JOIN information_schema.referential_constraints rc ON tc.constraint_catalog rc.constraint_catalog AND tc.constraint_schema rc.constraint_schema AND tc.constraint_name rc.constraint_nameLEFT JOIN information_schema.constraint_column_usage ccu ON rc.unique_constraint_catalog ccu.constraint_catalog AND rc.unique_constraint_schema ccu.constraint_schema AND rc.unique_constraint_name ccu.constraint_name WHERE tc.table_name !~ ^(pg_|sql_) AND tc.constraint_type PRIMARY KEY;12 获得索引与表之间的关系SELECT a.index_name, b.attname FROM ( SELECT a.indrelid, c.relname index_name, unnest(a.indkey) index_num FROM pg_index a, pg_class b, pg_class c WHERE b.oida.indrelid AND a.indisprimary ! t AND a.indexrelidc.oid ) a, pg_attribute b WHERE a.indrelid b.attrelid AND a.index_num b.attnum and a.index_name !~ ^(pg_|sql_) ORDER BY a.index_name, a.index_num13 显示VIEW 之间的依赖关系 SELECT v.relname AS dependent_view, t.relname AS referenced_relation FROM pg_depend dv LEFT JOIN pg_class v ON v.oid dv.refobjid LEFT JOIN pg_namespace nv ON v.relnamespace nv.oidLEFT JOIN pg_depend dt ON dv.classid dt.classid AND dv.objid dt.objid AND dv.refobjid dt.refobjid AND dv.refclassid dt.refclassid AND dv.classid pg_catalog.pg_rewrite::regclass AND dv.refclassid pg_catalog.pg_class::regclass LEFT JOIN pg_class t ON t.oid dt.refobjid LEFT JOIN pg_namespace nt ON t.relnamespace nt.oid AND nv.nspname public AND nt.nspname public WHERE dv.deptype i AND v.relkind v AND t.relkind IN (r, v) AND v.relname testttt -- VIEW NAME GROUP BY v.relname, t.relname; 共享电子书需要可以加群自取
