实操作业
实操目标
数据库是数据库对象的容器，在数据库中，可以创建模式、表、索引等数据库对象。openGauss数据库管理包括：创建数据库、删除数据库、重新命名数据库、查看数据库的信息。创建一个新的数据库。缺省情况下，新数据库将通过复制标准系统数据库template0来创建，且仅支持使用template0来创建。
1.创建表空间enmtbs和数据库musicdb
drop tablespace if exists enmtbs;
drop database if exists musicdb;
drop user if exists user1;
create user user1 SYSADMIN PASSWORD 'test@123';
create tablespace enmtbs owner user1 relative location 'tablespace/enmtbs';
create database musicdb owner user1 tablespace enmtbs;
2.查看数据库集簇中有哪些数据库
--元命令
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+-------+----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb | user1 | UTF8 | C | C | | 12 MB | enmtbs |
omm | omm | UTF8 | C | C | | 12 MB | pg_default |
postgres | omm | UTF8 | C | C | | 14 MB | pg_default | default administrative connection database
template0 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(5 rows)
--使用元命令查看表结构
omm=# \dS+ pg_database
Table "pg_catalog.pg_database"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+-----------+-----------+----------+--------------+-------------
datname | name | not null | plain | |
datdba | oid | not null | plain | |
encoding | integer | not null | plain | |
datcollate | name | not null | plain | |
datctype | name | not null | plain | |
datistemplate | boolean | not null | plain | |
datallowconn | boolean | not null | plain | |
datconnlimit | integer | not null | plain | |
datlastsysoid | oid | not null | plain | |
datfrozenxid | xid32 | not null | plain | |
dattablespace | oid | not null | plain | |
datcompatibility | name | not null | plain | |
datacl | aclitem[] | | extended | |
datfrozenxid64 | xid | | plain | |
datminmxid | xid | | plain | |
Indexes:
"pg_database_datname_index" UNIQUE, btree (datname) TABLESPACE pg_global, tablespace "pg_global"
"pg_database_oid_index" UNIQUE, btree (oid) TABLESPACE pg_global, tablespace "pg_global"
Replica Identity: NOTHING
Has OIDs: yes
Tablespace: "pg_global"
--sql语句数据库信息
select oid,datname,datdba,dattablespace from pg_database;
select oid,* from pg_tablespace;
3.查看数据库默认的表空间信息
--sql语句方式
select a.oid 库ID,b.oid 表空间ID,b.spcname 空间名称,datname 库名称 from pg_database a ,pg_tablespace b where a.dattablespace=b.oid;
--元命令方式
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+-------+----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb | user1 | UTF8 | C | C | | 12 MB | enmtbs |
omm | omm | UTF8 | C | C | | 12 MB | pg_default |
postgres | omm | UTF8 | C | C | | 14 MB | pg_default | default administrative connection database
template0 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(5 rows)
4.查看数据库下有哪些模式
--元命令方式
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 | |
test | test | | | f
user1 | user1 | | | f
(12 rows)
--sql语句方式
SELECT catalog_name 数据库名, schema_name 模式名, schema_owner 模式所有者 FROM information_schema.schemata;
数据库名 | 模式名 | 模式所有者
--------------+--------------------+-----------------
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
musicdb | schtest | test
5.查询当前连接的数据库下有哪些表
--元命令方式
musicdb=> \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+---------+----------------------------------+-------------
public | t1 | table | test | 0 bytes | {orientation=row,compression=no} |
(1 row)
--sql语句方式
select * from pg_tables t
--查看非系统表
select * from pg_tables t where t.created is not null;
6.更改数据库默认的表空间
musicdb=> \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+-------+----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb | user1 | UTF8 | C | C | | 12 MB | enmtbs |
omm | omm | UTF8 | C | C | | 12 MB | pg_default |
postgres | omm | UTF8 | C | C | | 14 MB | pg_default | default administrative connection database
template0 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(5 rows)
omm=# alter database musicdb set tablespace pg_default ;
omm=# ALTER DATABASE
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+-------+----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb | user1 | UTF8 | C | C | | 12 MB | pg_default |
omm | omm | UTF8 | C | C | | 12 MB | pg_default |
postgres | omm | UTF8 | C | C | | 14 MB | pg_default | default administrative connection database
template0 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(5 rows)
7.重新命名数据库
omm=# alter database musicdb rename to musicdb_new;
omm=# ALTER DATABASE
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-------------+-------+----------+---------+-------+-------------------+-------+------------+--------------------------------------------
musicdb_new | user1 | UTF8 | C | C | | 12 MB | pg_default |
omm | omm | UTF8 | C | C | | 12 MB | pg_default |
| | | | | omm=CTc/omm | | |
template1 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
(5 rows)
postgres | omm | UTF8 | C | C | | 14 MB | pg_default | default administrative connection database
template0 | omm | UTF8 | C | C | =c/omm +| 12 MB | pg_default | default template for new databases
--注 重命名数据库不会改变数据库的oid
8.修改数据库的默认用户
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 omm;
ALTER DATABASE
omm=# \l
musicdb_new | 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)
9.删除数据库
omm=# select oid,datname,datdba from pg_database ;
oid | datname | datdba
-------+--------------+--------
1 | template1 | 10
16384 | omm | 10
14555 | template0 | 10
14560 | postgres | 10
16402 | musicdb_new2 | 16389
(5 rows)
omm=# drop database if exists musicdb_new2;
DROP DATABASE
mm=# select oid,datname,datdba from pg_database ;
oid | datname | datdba
-------+-----------+--------
1 | template1 | 10
16384 | omm | 10
14555 | template0 | 10
14560 | postgres | 10
(4 rows)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
