学习目标
数据库是数据库对象的容器，在数据库中，可以创建模式、表、索引等数据库对象。openGauss数据库管理包括：创建数据库、删除数据库、重新命名数据库、查看数据库的信息。
创建一个新的数据库。缺省情况下，新数据库将通过复制标准系统数据库template0来创建，且仅支持使用template0来创建。
课程作业
1.创建表空间enmtbs和数据库musicdb
openGauss=# CREATE TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs_ts1';
CREATE TABLESPACE
openGauss=# CREATE DATABASE musicdb WITH TABLESPACE = enmtbs;
CREATE DATABASE
openGauss=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+-----------+---------+-------+-------------------
musicdb | omm | SQL_ASCII | C | C |
newdb1 | omm | SQL_ASCII | C | C |
newdb2 | omm | SQL_ASCII | C | C |
newdb3 | omm | SQL_ASCII | C | C |
omm | user10 | SQL_ASCII | C | C |
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
(8 rows)
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+------------------------
enmtbs | omm | tablespace/enmtbs_ts1
music_tbs | omm | tablespace/test_ts1
newtbs1 | omm | tablespace/newtbs1_ts1
pg_default | omm |
pg_global | omm |
(5 rows)
2.查看数据库集簇中有哪些数据库
openGauss=# SELECT datname FROM pg_database;
datname
-----------
template1
template0
omm
postgres
newdb1
newdb2
newdb3
musicdb
(8 rows)
openGauss=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+-----------+---------+-------+-------------------
musicdb | omm | SQL_ASCII | C | C |
newdb1 | omm | SQL_ASCII | C | C |
newdb2 | omm | SQL_ASCII | C | C |
newdb3 | omm | SQL_ASCII | C | C |
omm | user10 | SQL_ASCII | C | C |
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
(8 rows)
3.查看数据库默认的表空间信息
openGauss=# select datname,dattablespace,spcname from pg_database d, pg_tablespace t where d.dattablespace=t.oid;
datname | dattablespace | spcname
-----------+---------------+------------
template1 | 1663 | pg_default
template0 | 1663 | pg_default
omm | 16390 | music_tbs
postgres | 1663 | pg_default
newdb1 | 24679 | newtbs1
newdb2 | 24679 | newtbs1
newdb3 | 24679 | newtbs1
musicdb | 24694 | enmtbs
4.查看数据库下有哪些模式
openGauss=# \c musicdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "omm".
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 | dbe_sql_util | omm
musicdb | information_schema | omm
musicdb | db4ai | omm
(14 rows)
5.查询当前连接的数据库下有哪些表：
musicdb=# select * from pg_tables where schemaname not in ('pg_catalog', 'information_schema','dbe_perf');
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator | created | last_ddl_time
-----------------+-----------+------------+------------+------------+----------+-------------+--------------+---------+---------------
dbe_pldeveloper | gs_source | omm | | t | f | f | | |
dbe_pldeveloper | gs_errors | omm | | t | f | f | | |
db4ai | snapshot | omm | | t | f | f | | |
(3 rows)
6.更改数据库默认的表空间
很明显有用户连接的时候是不能改默认表空间的
musicdb=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+--------+-----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb | omm | SQL_ASCII | C | C | | 13 MB | enmtbs |
newdb1 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb2 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb3 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
omm | user10 | SQL_ASCII | C | C | | 13 MB | music_tbs |
postgres | omm | SQL_ASCII | C | C | | 27 MB | pg_default | default administrative connection database
template0 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(8 rows)
musicdb=# alter database musicdb set TABLESPACE newtbs1;
ERROR: cannot change the tablespace of the currently open database
musicdb=# \c omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# alter database musicdb set TABLESPACE newtbs1;
ALTER DATABASE
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+--------+-----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb1 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb2 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb3 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
omm | user10 | SQL_ASCII | C | C | | 13 MB | music_tbs |
postgres | omm | SQL_ASCII | C | C | | 27 MB | pg_default | default administrative connection database
template0 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(8 rows)
omm=#
7.重新命名数据库
omm=# alter database musicdb rename to musicdb1;
ALTER DATABASE
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+--------+-----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb1 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb1 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb2 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb3 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
omm | user10 | SQL_ASCII | C | C | | 13 MB | music_tbs |
postgres | omm | SQL_ASCII | C | C | | 27 MB | pg_default | default administrative connection database
template0 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | unmodifiable empty database
| |
8.修改数据库的默认用户
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+--------+-----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb1 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb1 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb2 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb3 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
omm | user10 | SQL_ASCII | C | C | | 13 MB | music_tbs |
postgres | omm | SQL_ASCII | C | C | | 27 MB | pg_default | default administrative connection database
template0 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(8 rows)
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
test | | {}
user1 | Sysadmin | {}
user10 | Sysadmin | {}
user2 | Sysadmin | {}
user3 | Sysadmin | {}
user5 | Sysadmin | {}
omm=# alter database musicdb1 owner to user1;
ALTER DATABASE
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+--------+-----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb1 | user1 | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb1 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb2 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
newdb3 | omm | SQL_ASCII | C | C | | 13 MB | newtbs1 |
omm | user10 | SQL_ASCII | C | C | | 13 MB | music_tbs |
postgres | omm | SQL_ASCII | C | C | | 27 MB | pg_default | default administrative connection database
template0 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(8 rows)
omm=#
9.删除数据库
omm=# drop database musicdb1;
DROP DATABASE
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
