PostgreSQL學(xué)習(xí)第十篇 數(shù)據(jù)庫邏輯結(jié)構(gòu)介紹--數(shù)據(jù)庫

PG中，數(shù)據(jù)的組織結(jié)構(gòu)可以分為以下3層：

	1. 數(shù)據(jù)庫：1個(gè)PostgreSQL數(shù)據(jù)庫服務(wù)下可以管理多個(gè)數(shù)據(jù)庫；
	2. 表、索引：注：1般的PG中，表的術(shù)語叫relation，而其他數(shù)據(jù)庫中為table
	3. 數(shù)據(jù)行：注：PG中行的術(shù)語1般為tuple，而在其他數(shù)據(jù)庫中則為row

在PostgreSQL中，1個(gè)數(shù)據(jù)庫服務(wù)（或叫實(shí)例）下可以有多個(gè)數(shù)據(jù)庫，而1個(gè)數(shù)據(jù)庫不能屬于多個(gè)實(shí)例。但是在Oracle中，1個(gè)實(shí)例只能有1個(gè)數(shù)據(jù)庫，但1個(gè)數(shù)據(jù)庫可以在多個(gè)實(shí)例中（rac）

數(shù)據(jù)庫基本操作：創(chuàng)建數(shù)據(jù)庫、刪除數(shù)據(jù)庫、修改數(shù)據(jù)庫等

創(chuàng)建數(shù)據(jù)庫：
create database db_name;
可以指定所屬用戶、模板（默許為templete1）、字符編碼，表空間等

修改數(shù)據(jù)庫：
alter database db_name with option;

可以修改連接限制、重命名數(shù)據(jù)庫、修改數(shù)據(jù)庫所屬用戶等

刪除數(shù)據(jù)庫：
drop database db_name;

示例：
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ |                       | 7359 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ | =c/postgres          +| 7225 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ | =c/postgres          +| 7225 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(3 rows)

postgres=# create database test owner=postgres template=template1 encoding=utf8 tablespace=pg_default connection limit=⑴;
CREATE DATABASE
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ |                       | 7359 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ | =c/postgres          +| 7225 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ | =c/postgres          +| 7225 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
 test      | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ |                       | 7225 kB | pg_default |
(4 rows)

postgres=# alter database test rename to testn;
ALTER DATABASE
postgres=# create tablespace test location '/home/postgres/test';
CREATE TABLESPACE
postgres=# alter database testn tablespace test;
ALTER DATABASE
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ |                       | 7359 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ | =c/postgres          +| 7225 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ | =c/postgres          +| 7225 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
 testn     | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ |                       | 7225 kB | test       |
(4 rows)

postgres=#
postgres=# drop database test;
ERROR:  database "test" does not exist
postgres=# drop database if exists test;
NOTICE:  database "test" does not exist, skipping
DROP DATABASE
postgres=# drop database if exists testn;
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ |                       | 7359 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ | =c/postgres          +| 7225 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF⑻ | en_US.UTF⑻ | =c/postgres          +| 7225 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(3 rows)


 

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)