课后作业

0.建库建用户授权

omm=# CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb  WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=#  CREATE USER user1 IDENTIFIED BY 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user1 SYSADMIN;
ALTER ROLE

1.查看当前数据库下有哪些模式

omm=# \c musicdb user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user1".
musicdb=> \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(10 rows)

2.然后为数据库musicdb创建4个模式，名称自定义

musicdb=> create schema  schm1  AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema  schm2  AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema  schm3  AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema  schm4  AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 schm1           | user1
 schm2           | user1
 schm3           | user1
 schm4           | user1
 snapshot        | omm
 sqladvisor      | omm
(14 rows)
musicdb=> 

3.在数据库musicdb的不同的模式下创建同名的表

musicdb=> 
musicdb=> create table schm1.ttt(col varchar(100));
CREATE TABLE
musicdb=> create table schm2.ttt(col varchar(100));
CREATE TABLE
musicdb=> create table schm3.ttt(col varchar(100));
CREATE TABLE
musicdb=> create table schm4.ttt(col varchar(100));
CREATE TABLE

4.访问musicdb数据库下不同模式的同名表

musicdb=> show SEARCH_PATH;
  search_path   
----------------
 "$user",public
(1 row)
musicdb=> select * from schm1.ttt;                    
 col 
-----
(0 rows)
musicdb=> 
musicdb=>  select * from schm2.ttt;               
 col 
-----
(0 rows)
musicdb=> 
musicdb=>  select * from schm3.ttt;         
 col 
-----
(0 rows)
musicdb=>     
musicdb=>  select * from schm4.ttt;
 col 
-----
(0 rows)
musicdb=> 

5.实验理解：模式是在数据库层面，用户是在实例层面