打卡第十一天，数据库管理
本节学习opengauss数据库中，创建模式、表、索引等数据库对象。openGauss数据库管理包括：创建数据库、删除数据库、重新命名数据库、查看数据库的信息。
创建一个新的数据库。缺省情况下，新数据库将通过复制标准系统数据库template0来创建，且仅支持使用template0来创建。
整个操作相关的语法，还是跟其它数据库相似。
课程作业
1.创建表空间enmtbs和数据库musicdb
openGauss=# create tablespace enmtbs relative location 'tablespace/enmtbs';
CREATE TABLESPACE
openGauss=# create database musicdb with tablespace=enmtbs;
CREATE DATABASE
openGauss=# 2.查看数据库集簇中有哪些数据库
openGauss=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
musicdb | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
openGauss=# select * from pg_database;
datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlast
sysoid | datfrozenxid | dattablespace | datcompatibility | datacl | datfrozenxid64 | datminmxid
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+--------
-------+--------------+---------------+------------------+----------------------+----------------+------------
template1 | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 |
16378 | 0 | 1663 | A | {=c/omm,omm=CTc/omm} | 13486 | 2
musicdb | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 |
16378 | 13131 | 63292 | A | | 13131 | 2
template0 | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 |
16378 | 0 | 1663 | A | {=c/omm,omm=CTc/omm} | 13131 | 2
postgres | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 |
16378 | 0 | 1663 | A | | 13841 | 2
(4 rows)
openGauss=# select datname from pg_database;
datname
-----------
template1
musicdb
template0
postgres
(4 rows)
openGauss=# 3.查看数据库默认的表空间信息
openGauss=# select * from pg_tablespace where oid in (select dattablespace from pg_database where datname='musicdb
');
spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
---------+----------+--------+------------+------------+----------
enmtbs | 10 | | | | t
(1 row)4.查看数据库下有哪些模式
usicdb=# \dn
List of schemas
Name | Owner
----------------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_application_info | omm
dbe_file | omm
dbe_lob | omm
dbe_match | omm
dbe_output | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_random | omm
dbe_raw | omm
dbe_scheduler | omm
dbe_session | omm
dbe_sql | omm
dbe_sql_util | omm
dbe_task | omm
dbe_utility | omm
pkg_service | omm
pkg_util | omm
public | omm
snapshot | omm
sqladvisor | omm
sys | omm
(25 rows)
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 | dbe_perf | omm
musicdb | snapshot | omm
musicdb | blockchain | omm
musicdb | sys | omm
musicdb | pg_catalog | omm
musicdb | dbe_session | omm
musicdb | public | omm
musicdb | sqladvisor | omm
musicdb | dbe_pldebugger | omm
musicdb | dbe_pldeveloper | omm
musicdb | dbe_sql_util | omm
musicdb | information_schema | omm
musicdb | pkg_service | omm
musicdb | pkg_util | omm
musicdb | dbe_scheduler | omm
musicdb | dbe_file | omm
musicdb | dbe_random | omm
musicdb | dbe_application_info | omm
musicdb | dbe_output | omm
musicdb | dbe_raw | omm
musicdb | dbe_sql | omm
musicdb | dbe_lob | omm
musicdb | dbe_task | omm
musicdb | dbe_match | omm
musicdb | dbe_utility | omm
musicdb | db4ai | omm
(28 rows)
5.查询当前连接的数据库下有哪些表：
musicdb=# with my_tables(table_catalog, table_schema, table_name, table_type) as
musicdb-# ( select table_catalog, table_schema, table_name, table_type
musicdb(# from information_schema.tables
musicdb(# where table_schema not in ('pg_catalog', 'information_schema','dbe_perf')
musicdb(# )
musicdb-#
musicdb-# select * from my_tables;
table_catalog | table_schema | table_name | table_type
---------------+-----------------+-----------------------+------------
musicdb | db4ai | snapshot | BASE TABLE
musicdb | sys | adm_procedures | VIEW
musicdb | sys | adm_type_attrs | VIEW
musicdb | sys | adm_hist_snapshot | VIEW
musicdb | sys | db_synonyms | VIEW
musicdb | sys | my_synonyms | VIEW
musicdb | sys | adm_synonyms | VIEW
musicdb | sys | db_col_comments | VIEW
musicdb | sys | my_col_comments | VIEW
musicdb | sys | adm_col_comments | VIEW
musicdb | sys | db_tab_comments | VIEW
musicdb | sys | my_tab_comments | VIEW
musicdb | sys | adm_tab_comments | VIEW
musicdb | sys | db_cons_columns | VIEW
musicdb | sys | my_cons_columns | VIEW
musicdb | sys | adm_cons_columns | VIEW
musicdb | sys | db_constraints | VIEW
musicdb | sys | my_constraints | VIEW
musicdb | sys | adm_constraints | VIEW
musicdb | sys | db_ind_columns | VIEW
musicdb | sys | my_ind_columns | VIEW
musicdb | sys | adm_ind_columns | VIEW
musicdb | sys | db_ind_expressions | VIEW
musicdb | sys | my_ind_expressions | VIEW
musicdb | sys | adm_ind_expressions | VIEW
musicdb | sys | adm_objects | VIEW
musicdb | sys | adm_tablespaces | VIEW
musicdb | sys | adm_data_files | VIEW
musicdb | sys | db_users | VIEW
musicdb | sys | adm_users | VIEW
musicdb | sys | db_indexes | VIEW
musicdb | sys | my_indexes | VIEW
musicdb | sys | adm_indexes | VIEW
musicdb | sys | my_procedures | VIEW
musicdb | sys | db_procedures | VIEW
musicdb | sys | my_sequences | VIEW
musicdb | sys | db_sequences | VIEW
musicdb | sys | adm_sequences | VIEW
musicdb | sys | my_source | VIEW
musicdb | sys | db_dependencies | VIEW
musicdb | sys | db_source | VIEW
musicdb | sys | adm_source | VIEW
musicdb | sys | my_tab_columns | VIEW
musicdb | sys | db_triggers | VIEW
musicdb | sys | my_triggers | VIEW
musicdb | sys | adm_triggers | VIEW
musicdb | sys | my_views | VIEW
musicdb | sys | db_views | VIEW
musicdb | sys | adm_views | VIEW
musicdb | sys | db_all_tables | VIEW
musicdb | sys | db_tab_columns | VIEW
musicdb | sys | adm_tab_columns | VIEW
musicdb | sys | db_tables | VIEW
musicdb | sys | my_tables | VIEW
musicdb | sys | adm_tables | VIEW
musicdb | sys | db_objects | VIEW
musicdb | sys | my_objects | VIEW
musicdb | sys | db_ind_subpartitions | VIEW
musicdb | sys | my_ind_subpartitions | VIEW
musicdb | sys | adm_ind_subpartitions | VIEW
musicdb | sys | db_ind_partitions | VIEW
musicdb | sys | my_ind_partitions | VIEW
musicdb | sys | adm_ind_partitions | VIEW
musicdb | sys | db_part_indexes | VIEW
musicdb | sys | my_part_indexes | VIEW
musicdb | sys | adm_part_indexes | VIEW
musicdb | sys | db_tab_subpartitions | VIEW
musicdb | sys | my_tab_subpartitions | VIEW
musicdb | sys | adm_tab_subpartitions | VIEW
musicdb | sys | db_tab_partitions | VIEW
musicdb | sys | my_tab_partitions | VIEW
musicdb | sys | adm_tab_partitions | VIEW
musicdb | sys | db_part_tables | VIEW
musicdb | sys | my_part_tables | VIEW
musicdb | sys | adm_part_tables | VIEW
musicdb | sys | my_jobs | VIEW
musicdb | sys | sys_dummy | VIEW
musicdb | dbe_pldeveloper | gs_errors | BASE TABLE
musicdb | dbe_pldeveloper | gs_source | BASE TABLE
(79 rows)6.更改数据库默认的表空间
openGauss=# alter database musicdb set tablespace app_ts;
ALTER DATABASE
openGauss=# select spcname
openGauss-# from pg_tablespace
openGauss-# where oid=( select dattablespace
openGauss(# from pg_database
openGauss(# where datname='musicdb' );
spcname
---------
app_ts
(1 row)7.重新命名数据库
openGauss=# alter database musicdb rename to xxx;8.修改数据库的默认用户
xxx=> alter database xxx owner to user1;
ALTER DATABASE
xxx=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
xxx | user1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
xxx=> 9.删除数据库
openGauss=> drop database xxx;
DROP DATABASE「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
