一、学习目标
本节课是本次实训的第十五节课，本节课的重点依然是与表管理相关的知识，特别是如何查看表的相关信息，如通过openGauss的元命令来查看表相关信息，或者通过SQL语句查看表相关信息。
如openGauss官网链接
https://docs.opengauss.org/zh/docs/3.0.0-lite/docs/Toolreference/%E5%85%83%E5%91%BD%E4%BB%A4%E5%8F%82%E8%80%83.html 【元命令参考】，在该篇文章里详细列举了如何通过元命令来查看表的相关信息下，如：
\d+ [PATTERN] 列出所有表、视图和索引
\det[+] [PATTERN] 列出所有的外部表
\dl \lo_list的别名，显示一个大对象的列表
\dp [PATTERN] 列出一列可用的表、视图以及相关的权限信息
另一种就是通过一些SQL语句来管理表,如下：
--赋予所有表的操作权限
grant SELECT,INSERT,UPDATE,DELETE on all tables in schema public to jack1;
-- 查看用户表
select * from pg_user;
--查询表上未被使用的索引
SELECT s.schemaname,
s.relname AS tablename,
s.indexrelname AS indexname,
pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0 -- has never been scanned
AND 0 <>ALL (i.indkey)
AND NOT i.indisunique
AND NOT EXISTS
(SELECT 1 FROM pg_catalog.pg_constraint c
WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;
二、测试练习
2.1 创建表及约束
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
omm=# \db
pg_global | omm |
(2 rows)
omm=# List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
omm=#
-- 创建表空间tbs_opdb
omm=# CREATE TABLESPACE tbs_opdb relative location 'tablespace/tbs_opdb';
CREATE TABLESPACE
-- 创建数据库并指定字符集为GBK，指定默认表空间tbs_opdb
omm=# CREATE DATABASE opdb WITH ENCODING 'GBK' template = template0 tablespace=tbs_opdb;
CREATE DATABASE
omm=#
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
opdb | omm | GBK | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(5 rows)
omm=# ldb
omm-# \db
List of tablespaces
Name | Owner | Location
------------+-------+---------------------
pg_default | omm |
pg_global | omm |
tbs_opdb | omm | tablespace/tbs_opdb
(3 rows)
-- 切换到opdb数据库
omm=# \c opdb
opdb=# Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "opdb" as user "omm".
opdb=# \d
No relations found.
-- 创建表opdb_tb1并设置相关字段约束
opdb=# create table opdb_tb1(
opdb(# id bigint,
opdb(# name varchar(50) not null,
opdb(# gender varchar(10) default 'male',
opdb(# primary key(id)
);opdb(#
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "opdb_tb1_pkey" for table "opdb_tb1"
CREATE TABLE
opdb=# \d opdb_tb1
Table "public.opdb_tb1"
Column | Type | Modifiers
--------+-----------------------+-----------------------------------
id | bigint | not null
name | character varying(50) | not null
gender | character varying(10) | default 'male'::character varying
Indexes:
"opdb_tb1_pkey" PRIMARY KEY, btree (id) TABLESPACE tbs_opdb
opdb=# insert into opdb_tb1 (id,name) values (1,'shanglei');
INSERT 0 1
opdb=# insert into opdb_tb1 (id,name) values (2,'zhangsan');
opdb=# INSERT 0 1
opdb=# select * from opdb_tb1;
id | name | gender
----+----------+--------
1 | shanglei | male
2 | zhangsan | male
(2 rows)
2.2 使用元命令查看表定义和模式及所有者
opdb=# \d opdb_tb1
Table "public.opdb_tb1"
Column | Type | Modifiers
--------+-----------------------+-----------------------------------
id | bigint | not null
name | character varying(50) | not null
gender | character varying(10) | default 'male'::character varying
Indexes:
"opdb_tb1_pkey" PRIMARY KEY, btree (id) TABLESPACE tbs_opdb
opdb=#
opdb=# \d+ opdb_tb1
Table "public.opdb_tb1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------------------------------+----------+--------------+-------------
id | bigint | not null | plain | |
name | character varying(50) | not null | extended | |
gender | character varying(10) | default 'male'::character varying | extended | |
Indexes:
"opdb_tb1_pkey" PRIMARY KEY, btree (id) TABLESPACE tbs_opdb
Has OIDs: no
Options: orientation=row, compression=no
\d+ 相对于 \d 看到的信息更详细
2.3 查看模式下的表
-- 通过元命令查看opdb库下有哪些表
opdb=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+----------+-------+-------+----------------------------------
public | opdb_tb1 | table | omm | {orientation=row,compression=no}
(1 row)
-- 通过SQL查看opdb下public模式下有哪些表
opdb=# SELECT table_name FROM information_schema.tables WHERE table_schema='public';
table_name
------------
opdb_tb1
(1 row)
2.4 查看表下约束信息
-- 通过元命令查看表opdb_tb1模式、所有者及约束信息等
opdb=# \d+ opdb_tb1
Table "public.opdb_tb1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------------------------------+----------+--------------+-------------
id | bigint | not null | plain | |
name | character varying(50) | not null | extended | |
gender | character varying(10) | default 'male'::character varying | extended | |
Indexes:
"opdb_tb1_pkey" PRIMARY KEY, btree (id) TABLESPACE tbs_opdb
Has OIDs: no
Options: orientation=row, compression=no
-- 通过SQL查询表及其约束信息
opdb=# select conname, connamespace, contype, conkey
opdb-# from pg_constraint
opdb-# where conrelid in ( select oid
opdb(# from pg_class
opdb(# where relname='opdb_tb1');
conname | connamespace | contype | conkey
---------------+--------------+---------+--------
opdb_tb1_pkey | 2200 | p | {1}
(1 row)
2.5 查询表属于数据库哪个模式
-- 使用 \d 或 \d+ 都可以显示某个表属于哪个模式
opdb=# \d+ opdb_tb1
Table "public.opdb_tb1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------------------------------+----------+--------------+-------------
id | bigint | not null | plain | |
name | character varying(50) | not null | extended | |
gender | character varying(10) | default 'male'::character varying | extended | |
Indexes:
"opdb_tb1_pkey" PRIMARY KEY, btree (id) TABLESPACE tbs_opdb
Has OIDs: no
Options: orientation=row, compression=no
opdb=# \x
Expanded display is on.
-- 也可以使用如下SQL查询某个表属于哪个模式
opdb=# SELECT * FROM information_schema.tables WHERE table_name='opdb_tb1';
-[ RECORD 1 ]----------------+-----------
table_catalog | opdb
table_schema | public
table_name | opdb_tb1
opdb=# table_type | BASE TABLE
self_referencing_column_name |
reference_generation |
user_defined_type_catalog |
user_defined_type_schema |
user_defined_type_name |
is_insertable_into | YES
is_typed | NO
commit_action |
三、学习心得
通过本节课的学习，要多学会使用元命令结合SQL来查询表的相关信息，对于日常使用的元命令和SQL信息要多做整理，以便在日常运维中能快速使用，提高工作效率。
最后修改时间：2022-12-08 21:32:06
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
