课程作业

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

root@modb:~# su - omm
omm=# CREATE TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb WITH TABLESPACE = enmtbs;
CREATE DATABASE

2.查看数据库集簇中有哪些数据库

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

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

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)

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

omm=# \dn
List of schemas
Name | Owner
-----------------+--------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
jack | jack
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
user1 | user1
user10 | user10
user5 | user5
(14 rows)

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

omm=# with my_tables(table_catalog, table_schema, table_name, table_type) as
omm-# ( 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.更改数据库默认的表空间

omm=# CREATE TABLESPACE app_ts RELATIVE LOCATION 'tablespace/app_ts1';
CREATE TABLESPACE
omm=# ALTER DATABASE musicdb SET TABLESPACE app_ts;
ALTER DATABASE

7.重新命名数据库

omm=# ALTER DATABASE musicdb RENAME TO musicdb1;
ALTER DATABASE

8.修改数据库的默认用户

omm=# CREATE USER user1 IDENTIFIED BY 'user1@ustb2020';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user1 SYSADMIN;
ALTER ROLE
omm=# ALTER DATABASE musicdb1 OWNER to user1;
ALTER DATABASE

9.删除数据库

omm=# DROP DATABASE musicdb1;
DROP DATABASE