openGauss每日一练第 11 天 |学习笔记

原创 newdata 2022-12-04

422

openGauss每日一练第11天

今日目标：openGauss的逻辑结构：数据库管理

数据库是数据库对象的容器，在数据库中可以创建模式、表、索引等数据库对象。

数据库管理包括:创建数据库、删除数据库、重命名数据库和查看数据库信息

创建一个数据库，缺省情况下，新数据库将通过复制标准数据库template0来创建，且仅支持使用template0来创建

sql with as 用法

with as 也叫做子查询部分（subquery factoring），可以定义一个SQL段落，该SQL段落可以被整个SQL语句所用到类似于临时表的作用。with as 可以提高你的SQL语句的可读性，也有可以用在在UNION ALL的不同部分，作为提供临时数据的部分。

示例

with my_tables(table_catalog, table_schema, table_name, table_type) as      
(select table_catalog, table_schema, table_name, table_type          
 from information_schema.tables         
 where table_schema not in ('pg_catalog', 'information_schema','dbe_perf')
)  

select * from my_tables;

information_schema

信息模式本身是一个名为information_schema的模式。这个模式自动存在于所有数据库中。信息模式由一组视图构成，它们包含定义在当前数据库中对象的信息。这个模式的拥有者是初始数据库用户，并且该用户自然地拥有这个模式上的所有特权，包括删除它的能力。

information_schema.schemata

The view schemata contains all schemas in the current database that are owned by a currently enabled role.

Name	Data Type	Description
`catalog_name`	`sql_identifier`	Name of the database that the schema is contained in (always the current database)
`schema_name`	`sql_identifier`	Name of the schema
`schema_owner`	`sql_identifier`	Name of the owner of the schema
`default_character_set_catalog`	`sql_identifier`	Applies to a feature not available in PostgreSQL
`default_character_set_schema`	`sql_identifier`	Applies to a feature not available in PostgreSQL
`default_character_set_name`	`sql_identifier`	Applies to a feature not available in PostgreSQL
`sql_path`	`character_data`	Applies to a feature not available in PostgreSQL

information_schema.tables
The view tables contains all tables and views defined in the current database. Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

Name	Data Type	Description
`table_catalog`	`sql_identifier`	Name of the database that contains the table (always the current database)
`table_schema`	`sql_identifier`	Name of the schema that contains the table
`table_name`	`sql_identifier`	Name of the table
`table_type`	`character_data`	Type of the table: `BASE TABLE` for a persistent base table (the normal table type), `VIEW` for a view, `FOREIGN TABLE` for a foreign table, or `LOCAL TEMPORARY` for a temporary table
`self_referencing_column_name`	`sql_identifier`	Applies to a feature not available in PostgreSQL
`reference_generation`	`character_data`	Applies to a feature not available in PostgreSQL
`user_defined_type_catalog`	`sql_identifier`	If the table is a typed table, the name of the database that contains the underlying data type (always the current database), else null.
`user_defined_type_schema`	`sql_identifier`	If the table is a typed table, the name of the schema that contains the underlying data type, else null.
`user_defined_type_name`	`sql_identifier`	If the table is a typed table, the name of the underlying data type, else null.
`is_insertable_into`	`yes_or_no`	`YES` if the table is insertable into, `NO` if not (Base tables are always insertable into, views not necessarily.)
`is_typed`	`yes_or_no`	`YES` if the table is a typed table, `NO` if not
`commit_action`	`character_data`	Not yet implemented

系统表

PG_DATABASE

系统表存储关于可用数据库的信息。

名称	类型	描述
oid	oid	行标识符（隐含属性，必须明确选择）。
datname	name	数据库名称。
datdba	oid	数据库所有人，通常为其创建者。
encoding	integer	数据库的字符编码方式。
datcollate	name	数据库使用的排序顺序。
datctype	name	数据库使用的字符分类。
datistemplate	boolean	是否允许作为模板数据库。
datallowconn	boolean	如果为假，则没有用户可以连接到这个数据库。这个字段用于保护template0数据库不被更改。
datconnlimit	integer	该数据库上允许的最大并发连接数，-1表示无限制。
datlastsysoid	oid	数据库里最后一个系统OID 。
datfrozenxid	xid32	用于跟踪该数据库是否需要为了防止事务ID重叠而进行清理。当前版本该字段已经废弃使用，为保持前向兼容，保留此字段，新增datfrozenxid64用于记录此信息。
dattablespace	oid	数据库的缺省表空间。
datcompatibility	name	数据库兼容模式，当前支持四种兼容模式：A、B、C、PG，分别表示兼容O、MY、TD和POSTGRES。
datacl	aclitem[]	访问权限。
datfrozenxid64	xid	用于跟踪该数据库是否需要为了防止事务ID重叠而进行清理。
datminmxid	xid	该数据库中中所有在这个之前的多事务ID已经被一个事务ID替换。这用于跟踪该数据库是否需要为了防止事务ID重叠或者允许收缩pg_clog而进行清理。它是此数据库中所有表的pg_class.relminmxid中的最小值。

PG_TABLESPACE
PG_TABLESPACE系统表存储表空间信息。

名称	类型	描述
oid	oid	行标识符（隐含属性，必须明确选择）。
spcname	name	表空间名称。
spcowner	oid	表空间的所有者，通常是创建它的人。
spcacl	aclitem[]	访问权限。具体请参见[GRANT](mk:@MSITStore:F:\openGauss学习\openGauss-document-zh-3.0.0\openGauss 3.0.0 开发者指南（企业版）01.chm::/zh-cn_topic_0289900312.html)和[REVOKE](mk:@MSITStore:F:\openGauss学习\openGauss-document-zh-3.0.0\openGauss 3.0.0 开发者指南（企业版）01.chm::/zh-cn_topic_0289900263.html)。
spcoptions	text[]	表空间的选项。
spcmaxsize	text	可使用的最大磁盘空间大小，单位Byte。
relative	boolean	标识表空间指定的存储路径是否为相对路径。

pg_class

PG_CLASS系统表存储数据库对象信息及其之间的关系

1.表空间常见操作

pg_tablespace_size 查看表空间使用的磁盘空间

根据表空间名称查看表空间使用的磁盘空间
select pg_tablespace_size(tablespacename);
//根据oid查看表空间大小
select pg_tablespace_size(oid);
pg_size_pretty

描述：将以64位整数表示的字节值转换为具有单位的易读格式。

重命名表空间

alter tablaspace rename to
删除表空间注意用户必须是表空间的owner或者系统管理员才能删除表空间。需要先删除表空间的对象，再删除表空间

drop tablespace <表空间名称> //需要先删除表空间的对象，再删除表空间

2.课后作业

2.1.创建表空间enmtbs和数据库musicdb

omm=# create tablespace  enmtbs relative location 'tablesapce/enmtbs1';
CREATE TABLESPACE
omm=# 
omm=# create database musicdb with tablespace = enmtbs ;
CREATE DATABASE
omm=#

2.2 查看数据库集簇中有哪些数据库

– 方式1

\l

– 方式2

select datname from pg_database; /select * from pg_database;

omm=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 musicdb   | omm   | UTF8     | C       | C     | 
 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
(5 rows)

omm=# select oid, datname, dattablespace from pg_database;
  oid  |  datname  | dattablespace 
-------+-----------+---------------
     1 | template1 |          1663
 16384 | omm       |          1663
 16390 | musicdb   |         16389
 14555 | template0 |          1663
 14560 | postgres  |          1663
(5 rows)

omm=#

2.3 查看数据库默认的表空间信息

– 查看数据库默认表空间的对象OID：

select datname,dattablespace from pg_database where datname=<数据库命名>;

–根据表空间对象的OID，来查看表空间的名字：

select oid,spcname from pg_tablespace where oid = <表空间oid>;

– 表关联直接查询

select t.oid , spcname from pg_tablespace t ,pg_database d where t.oid = d.dattablespace and d.datname = ‘musicdb’;

omm=# select datname,dattablespace from pg_database where datname='musicdb';
 datname | dattablespace 
---------+---------------
 musicdb |         16389
(1 row)
omm=# select oid,spcname from pg_tablespace where oid = 16389;
  oid  | spcname 
-------+---------
 16389 | enmtbs
(1 row)

omm=#
omm=# select t.oid ,spcname from pg_tablespace t ,pg_database d
omm-# where t.oid = d.dattablespace and d.datname = 'musicdb';
(1 row)

omm=#   oid  | spcname 
-------+---------
 16389 | enmtbs

omm=# select spcname from pg_tablespace where oid = (select dattablespace from pg_database where datname = 'musicdb');
 spcname 
---------
 enmtbs
(1 row)

omm=#

2.4. 查看数据库下有哪些模式

方式一

\dn+

方式二

select catalog_name ,schema_name ,schema_owner from information_schema.schemata;

omm=# \dn+
                                         List of schemas
      Name       | Owner | Access privileges |           Description            | WithBlockChain 
-----------------+-------+-------------------+----------------------------------+----------------
 blockchain      | omm   |                   | blockchain schema                | f
 cstore          | omm   |                   | reserved schema for DELTA tables | f
 db4ai           | omm   | omm=UC/omm       +| db4ai schema                     | f
                 |       | =U/omm            |                                  | 
 dbe_perf        | omm   |                   | dbe_perf schema                  | f
 dbe_pldebugger  | omm   | omm=UC/omm       +| dbe_pldebugger schema            | f
                 |       | =U/omm            |                                  | 
 dbe_pldeveloper | omm   | omm=UC/omm       +| dbe_pldeveloper schema           | f
                 |       | =U/omm            |                                  | 
 pkg_service     | omm   |                   | pkg_service schema               | f
 public          | omm   | omm=UC/omm       +| standard public schema           | f
                 |       | =U/omm            |                                  | 
 snapshot        | omm   |                   | snapshot schema                  | f
 sqladvisor      | omm   | omm=UC/omm       +| sqladvisor schema                | f
                 |       | =U/omm            |                                  | 
(10 rows)

omm=# \c musicdb 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "omm".
musicdb=# \dn+
                                         List of schemas
      Name       | Owner | Access privileges |           Description            | WithBlockChain 
-----------------+-------+-------------------+----------------------------------+----------------
 blockchain      | omm   |                   | blockchain schema                | f
 cstore          | omm   |                   | reserved schema for DELTA tables | f
 db4ai           | omm   | omm=UC/omm       +| db4ai schema                     | f
                 |       | =U/omm            |                                  | 
 dbe_perf        | omm   |                   | dbe_perf schema                  | f
 dbe_pldebugger  | omm   | omm=UC/omm       +| dbe_pldebugger schema            | f
                 |       | =U/omm            |                                  | 
 pkg_service     | omm   |                   | pkg_service schema               | f
 public          | omm   | omm=UC/omm       +| standard public schema           | f
                 |       | =U/omm            |                                  | 
 snapshot        | omm   |                   | snapshot schema                  | f
 sqladvisor      | omm   | omm=UC/omm       +| sqladvisor schema                | f
                 |       | =U/omm            |                                  | 
(10 rows)
                 |       | =U/omm            |                                  | 
 dbe_pldeveloper | omm   | omm=UC/omm       +| dbe_pldeveloper schema           | f

musicdb=#
musicdb=# select catalog_name ,schema_name ,schema_owner from information_schema.schemata;
 catalog_name |    schema_name     | schema_owner 
--------------+--------------------+--------------
 musicdb      | pg_toast           | omm
 musicdb      | cstore             | omm
 musicdb      | pkg_service        | omm
 musicdb      | dbe_perf           | omm
 musicdb      | snapshot           | omm
 musicdb      | blockchain         | omm
 musicdb      | pg_catalog         | omm
 musicdb      | public             | omm
 musicdb      | sqladvisor         | omm
 musicdb      | dbe_pldebugger     | omm
 musicdb      | dbe_pldeveloper    | omm
 musicdb      | information_schema | omm
 musicdb      | db4ai              | omm
(13 rows)

2.5 查询当前连接的数据库下有哪些表：

select table_catalog,table_schema,table_name,table_type from information_schema.tables where table_schema not in (‘pg_catalog’,‘information_schema’,‘dbe_perf’);

musicdb=# select table_catalog,table_schema,table_name,table_type from information_schema.tables where table_schema not in ('pg_catalog','information_schema','dbe_perf');
 table_catalog |  table_schema   | table_name | table_type 
---------------+-----------------+------------+------------
 musicdb       | db4ai           | snapshot   | BASE TABLE
 musicdb       | dbe_pldeveloper | gs_errors  | BASE TABLE
 musicdb       | dbe_pldeveloper | gs_source  | BASE TABLE
(3 rows)

musicdb=# select table_catalog,table_schema,table_name,table_type from information_schema.tables ;                 
 table_catalog |    table_schema    |               table_name               | table_type 
---------------+--------------------+----------------------------------------+------------
 musicdb       | db4ai              | snapshot                               | BASE TABLE
 musicdb       | information_schema | sql_sizing_profiles                    | BASE TABLE
...

musicdb=#

2.6 更改数据库默认的表空间

数据库表空间修改后，对应文件系统的问题位置也发生了改变

alter database <数据库名称> set tablespace <表空间名称> ;

omm=# select datname,d.oid ,dattablespace,spcname from pg_database d,pg_tablespace t where d.dattablespace = t.oid and t.spcname in ('enmtbs','tps_new');
 datname |  oid  | dattablespace | spcname 
---------+-------+---------------+---------
 musicdb | 16390 |         16389 | enmtbs
(1 row)

omm=# \q
omm@modb:/var/lib/opengauss/data/pg_tblspc$ ls 
16389  16391
omm@modb:/var/lib/opengauss/data/pg_tblspc$ ls 16389/PG_9.2_201611171_gaussdb/
16390  pgsql_tmp
omm@modb:/var/lib/opengauss/data/pg_tblspc$ ls 16391/PG_9.2_201611171_gaussdb/
pgsql_tmp
omm@modb:/var/lib/opengauss/data/pg_tblspc$ 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=# alter database musicdb set tablespace tps_new ;
ALTER DATABASE
omm=# select t.spcname from pg_database d, pg_tablespace t where d.dattablespace = t.oid and d.datname='musicdb';
 spcname 
---------
 tps_new
(1 row)

omm=# \q
omm@modb:/var/lib/opengauss/data/pg_tblspc$ ls 16389/PG_9.2_201611171_gaussdb/
pgsql_tmp
omm@modb:/var/lib/opengauss/data/pg_tblspc$ ls 16391/PG_9.2_201611171_gaussdb/
16390  pgsql_tmp
omm@modb:/var/lib/opengauss/data/pg_tblspc$

2.7 重新命名数据库

alter database <原数据库名称>rename to <数据库新名称>;

omm=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 musicdb   | user1 | UTF8     | C       | C     | 
 omm       | omm   | UTF8     | C       | C     | 
 postgres  | omm   | UTF8     | C       | C     | 
 template0 | omm   | UTF8     | C       | C     | =c/omm           +omm=# 
           |       |          |         |       | omm=CTc/omm
 template1 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
(5 rows)


omm=# alter database musicdb rename to musicdb_new;
ALTER DATABASE
omm=# \l
                          List of databases
    Name     | Owner | Encoding | Collate | Ctype | Access privileges 
-------------+-------+----------+---------+-------+-------------------
 musicdb_new | user1 | UTF8     | C       | C     | 
 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
(5 rows)

2.8 修改数据库的默认用户

alter database < 数据库名称> owner to <用户名>;

omm=# create user user_test sysadmin  identified by 'gauss@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \l
                          List of databases
    Name     | Owner | Encoding | Collate | Ctype | Access privileges 
-------------+-------+----------+---------+-------+-------------------
 musicdb_new | user1 | UTF8     | C       | C     | 
 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
(5 rows)

omm=# alter database musicdb_new owner to user_test ;
ALTER DATABASE
omm=# \l
                            List of databases
    Name     |   Owner   | Encoding | Collate | Ctype | Access privileges 
-------------+-----------+----------+---------+-------+-------------------
 musicdb_new | user_test | UTF8     | C       | C     | 
 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
(5 rows)

2.9 删除数据库

drop database <数据库名称>；

omm=# drop database musicdb_new ;
DROP DATABASE
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=#

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者