微网站微商城,蓝色大气企业网站模板,电子商务网站建设步,网站建设主要包括什么查询表信息
使用系统表pg_tables查询数据库所有表的信息。
SELECT * FROM pg_tables;使用gsql的\d命令查询表结构。
示例#xff1a;先创建表customer_t1并插入数据。
CREATE TABLE customer_t1
( c_customer_sk integer, c_customer_id char(5)…查询表信息
使用系统表pg_tables查询数据库所有表的信息。
SELECT * FROM pg_tables;使用gsql的\d命令查询表结构。
示例先创建表customer_t1并插入数据。
CREATE TABLE customer_t1
( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8)
)
with (orientation column,compressionmiddle)
distribute by hash (c_last_name);
INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES (6885, map, Peter), (4321, river, Lily), (9527, world, James);查询表结构。若建表时不指定schema则表的默认schemaname是public
\d customer_t1; Table public.customer_t1 Column | Type | Modifiers | Storage | Stats target | Description
----------------------------------------------------------------------------- c_customer_sk | integer | | plain | | c_customer_id | character(5) | | extended | | c_first_name | character(6) | | extended | | c_last_name | character(8) | | extended | |
Has OIDs: no
Distribute By: HASH(c_last_name)
Location Nodes: ALL DATANODES
Options: orientationcolumn, compressionmiddle, colversion2.0, enable_deltafalse使用函数pg_get_tabledef查询表定义。
SELECT * FROM PG_GET_TABLEDEF(customer_t1); pg_get_tabledef
----------------------------------------------------------------------------------- SET search_path tpchobs; CREATE TABLE customer_t1 ( c_customer_sk integer, c_customer_id character(5), c_first_name character(6), c_last_name character(8) ) WITH (orientationcolumn, compressionmiddle, colversion2.0, enable_deltafalse) DISTRIBUTE BY HASH(c_last_name) TO GROUP group_version1;
(1 row)执行如下命令查询表customer_t1的所有数据。
SELECT * FROM customer_t1; c_customer_sk | c_customer_id | c_first_name | c_last_name
--------------------------------------------------------- 6885 | map | Peter | 4321 | river | Lily | 9527 | world | James |
(3 rows)使用SELECT查询表customer_t1中某一字段的所有数据。
SELECT c_customer_sk FROM customer_t1; c_customer_sk
--------------- 6885 4321 9527
(3 rows)查询表是否做过表分析执行如下命令会返回每个表最近一次做analyze的时间没有返回的则表示没有做过analyze。
select pg_stat_get_last_analyze_time(oid),relname from pg_class where relkindr; 查询public下的表做表分析的时间
select pg_stat_get_last_analyze_time(c.oid),c.relname from pg_class c left join pg_namespace n on c.relnamespace n.oid where c.relkindr and n.nspnamepublic; pg_stat_get_last_analyze_time | relname
----------------------------------------------------- 2022-05-17 07:48:26.92378200 | warehouse_t19 2022-05-17 07:48:26.96451200 | emp 2022-05-17 07:48:27.01670900 | test_trigger_src_tbl 2022-05-17 07:48:27.04538500 | customer 2022-05-17 07:48:27.06248600 | warehouse_t1 2022-05-17 07:48:27.11488400 | customer_t1 2022-05-17 07:48:27.17225600 | product_info_input 2022-05-17 07:48:27.19701400 | tt1 2022-05-17 07:48:27.21290600 | timezone_test
(9 rows)查询表大小
查询表的总大小包含表的索引和数据。
select pg_size_pretty(pg_total_relation_size(schemaname.tablename));示例
先在customer_t1创建索引
CREATE INDEX index1 ON customer_t1 USING btree(c_customer_sk);然后查询public模式下customer_t1表的大小。
select pg_size_pretty(pg_total_relation_size(public.customer_t1)); pg_size_pretty
---------------- 264 kB
(1 row)查询表的数据大小不包括索引
select pg_size_pretty(pg_relation_size(schemaname.tablename));示例查询public模式下customer_t1表的大小。
select pg_size_pretty(pg_relation_size(public.customer_t1)); pg_size_pretty
---------------- 208 kB
(1 row)查询系统中所有表占用空间大小排行
SELECT table_schema || . || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size( || table_schema || . || table_name || )) AS size FROM information_schema.tables
ORDER BY
pg_total_relation_size( || table_schema || . || table_name || ) DESC limit xx;示例1查询系统中所有表占用空间大小排行前15。
SELECT table_schema || . || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size( || table_schema || . || table_name || )) AS size FROM information_schema.tables
ORDER BY
pg_total_relation_size( || table_schema || . || table_name || ) DESC limit 15; table_full_name | size
------------------------------------ pg_catalog.pg_attribute | 2048 KB pg_catalog.pg_rewrite | 1888 KB pg_catalog.pg_depend | 1464 KB pg_catalog.pg_proc | 1464 KB pg_catalog.pg_class | 512 KB pg_catalog.pg_description | 504 KB pg_catalog.pg_collation | 360 KB pg_catalog.pg_statistic | 352 KB pg_catalog.pg_type | 344 KB pg_catalog.pg_operator | 224 KB pg_catalog.pg_amop | 208 KB public.tt1 | 160 KB pg_catalog.pg_amproc | 120 KB pg_catalog.pg_index | 120 KB pg_catalog.pg_constraint | 112 KB
(15 rows)示例2查询public模式下所有表占用空间排行。
SELECT table_schema || . || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size( || table_schema || . || table_name || )) AS size FROM information_schema.tables where table_schemapublic
ORDER BY
pg_total_relation_size( || table_schema || . || table_name || ) DESC limit 20; table_full_name | size
-------------------------------------- public.tt1 | 160 KB public.product_info_input | 112 KB public.customer_t1 | 96 KB public.warehouse_t19 | 48 KB public.emp | 32 KB public.customer | 0 bytes public.test_trigger_src_tbl | 0 bytes public.warehouse_t1 | 0 bytes
(8 rows)查询数据库
使用gsql的\l元命令查看数据库系统的数据库列表。
\l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------------------------------------------------------- gaussdb | Ruby | SQL_ASCII | C | C | template0 | Ruby | SQL_ASCII | C | C | c/Ruby | | | | | RubyCTc/Ruby template1 | Ruby | SQL_ASCII | C | C | c/Ruby | | | | | RubyCTc/Ruby
(3 rows)说明
l 如果用户在数据库安装的时候没有指定LC_COLLATE、LC_CTYPE参数则LC_COLLATE、LC_CTYPE参数的默认值为C。
l 如果用户在创建数据库时没有指定LC_COLLATE、LC_CTYPE参数则默认使用模板数据库的排序顺序及字符分类。
通过系统表pg_database查询数据库列表。
SELECT datname FROM pg_database; datname
----------- template1 template0 gaussdb
(3 rows)查询数据库大小
查询数据库的大小。
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;示例
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database; datname | pg_size_pretty
--------------------------- template1 | 61 MB template0 | 61 MB postgres | 320 MB
(3 rows)
