学习目标
数据库是数据库对象的容器，在数据库中，可以创建模式、表、索引等数据库对象。openGauss数据库管理包括：创建数据库、删除数据库、重新命名数据库、查看数据库的信息。
创建一个新的数据库。缺省情况下，新数据库将通过复制标准系统数据库template0来创建，且仅支持使用template0来创建。
课程学习
进行表空间和数据库的创建，修改并查看信息。
课程作业
1.创建表空间enmtbs和数据库musicdb
/*
--创建表空间enmtbs和数据库musicdb：
CREATE TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs1';
CREATE DATABASE musicdb WITH TABLESPACE = enmtbs;
*/
omm=# CREATE TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb WITH TABLESPACE = enmtbs;
omm=# CREATE DATABASE
omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+--------------------
enmtbs | omm | tablespace/enmtbs1
pg_default | omm |
pg_global | omm |
(3 rows)
2.查看数据库集簇中有哪些数据库
--查看数据库集簇中有哪些数据库:\l \l+
omm=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size |
Tablespace | Description
-----------+-------+----------+---------+-------+-------------------+-------+
------------+--------------------------------------------
musicdb | omm | UTF8 | C | C | | 12 MB |
enmtbs |
omm | omm | UTF8 | C | C | | 12 MB |
pg_default |
postgres | omm | UTF8 | C | C | | 12 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)
--或SELECT datname FROM pg_database;
omm=# SELECT datname FROM pg_database;
datname
-----------
template1
omm
musicdb
template0
postgres
(5 rows)
3.查看数据库默认的表空间信息
/*
--查看musicdb数据库默认表空间的对象OID：
select datname,dattablespace from pg_database where datname='musicdb';
--根据表空间对象的OID，来查看表空间的名字：
select oid,spcname from pg_tablespace where oid=16389;
–两语句合并查询数据库musicdb默认表空间的名字：
select spcname
from pg_tablespace
where oid=(
select dattablespace
from pg_database
where 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 spcname
omm-# from pg_tablespace
omm-# where oid=(
omm(# select dattablespace
omm(# from pg_database
omm(# where datname='musicdb' );
spcname
---------
enmtbs
(1 row)
4.查看数据库下有哪些模式
--查看数据库下有哪些模式 \dn+ (缺失pg_toast,pg_catalog,information_schema这三个 系统内置模式)
omm=# \dn+
********* QUERY **********
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description",
n.nspblockchain AS "WithBlockChain"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
**************************
List of schemas
Name | Owner | Access privileges | Description
| WithBlockChain
-----------------+-------+-------------------+-------------------------------
---+----------------
blockchain | omm | | blockchain schema
| f
cstore | omm | | reserved schema for DELTA tabl
es | 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)
--或SELECT catalog_name, schema_name, schema_owner FROM information_schema.schemata;
omm=# SELECT catalog_name, schema_name, schema_owner FROM information_schema.schemata;
catalog_name | schema_name | schema_owner
--------------+--------------------+--------------
omm | pg_toast | omm
omm | cstore | omm
omm | pkg_service | omm
omm | dbe_perf | omm
omm | snapshot | omm
omm | blockchain | omm
omm | pg_catalog | omm
omm | public | omm
omm | sqladvisor | omm
omm | dbe_pldebugger | omm
omm | dbe_pldeveloper | omm
omm | information_schema | omm
omm | db4ai | omm
(13 rows)
--出现不同结果原因是分别选用pg_catalog.pg_namespace和information_schema.schemata获取信息。
5.查询当前连接的数据库下有哪些表：
--查询当前连接的数据库omm下有哪些表：
/*
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;
*/
omm=# with my_tables
omm-# omm-# (table_catalog, table_schema, table_name, table_type) as
( select table_catalog, table_schema, table_name, table_type
omm(# from information_schema.tables
omm(# where table_schema not in ('pg_catalog', 'information_schema','dbe_perf')
omm(#
)
omm-# omm-# select * from my_tables;
table_catalog | table_schema | table_name | table_type
---------------+-----------------+------------+------------
omm | db4ai | snapshot | BASE TABLE
omm | dbe_pldeveloper | gs_errors | BASE TABLE
omm | dbe_pldeveloper | gs_source | BASE TABLE
(3 rows)
6.更改数据库默认的表空间
/*
--更改数据库默认的表空间
CREATE TABLESPACE qq_ts RELATIVE LOCATION 'tablespace/qq_ts1';
ALTER DATABASE musicdb SET TABLESPACE qq_ts;
--查看表空间是否更改完成
select spcname
from pg_tablespace
where oid=( select dattablespace from pg_database where datname='musicdb' );
*/
omm=# select spcname
from pg_tablespace
where oid=( select dattablespace from pg_database where datname='musicdb' );
spcname
---------
enmtbs
(1 row)
omm=# CREATE TABLESPACE qq_ts RELATIVE LOCATION 'tablespace/qq_ts1';
CREATE TABLESPACE
omm=# ALTER DATABASE musicdb SET TABLESPACE qq_ts;
ALTER DATABASE
omm=# select spcname
omm-# from pg_tablespace
omm-# where oid=( select dattablespace from pg_database where datname='musicdb' );
spcname
---------
qq_ts
(1 row
7.重新命名数据库
--重新命名数据库 ALTER DATABASE musicdb RENAME TO pptdb;
omm=# \l********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
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=# ALTER DATABASE musicdb RENAME TO pptdb;
ALTER DATABASE
omm=# \l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
pptdb | 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)
8.修改数据库的默认用户
--修改数据库的默认用户
/*
CREATE USER ppt IDENTIFIED BY 'ppt@1234';
ALTER USER ppt SYSADMIN;
ALTER DATABASE pptdb OWNER to ppt;
*/
omm=# CREATE USER ppt IDENTIFIED BY 'ppt@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER ppt SYSADMIN;
ALTER ROLE
omm=# ALTER DATABASE pptdb OWNER to ppt;
ALTER DATABASE
omm=# \l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
pptdb | ppt | 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.删除数据库
--删除数据库 DROP DATABASE pptdb;
omm=# drop user ppt;
ERROR: role "ppt" cannot be dropped because some objects depend on it
DETAIL: owner of database pptdb
omm=# ALTER DATABASE pptdb OWNER to omm;
ALTER DATABASE
omm=# \l********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(5 rows)
omm=# List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
pptdb | omm | UTF8 | C | C |
omm=# drop user ppt;
DROP ROLE
omm=# DROP DATABASE pptdb;
DROP DATABASE
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
