七. PostgreSQL逻辑结构（1）—数据库和模式

2017-12-18 本文已影响0人 shark_tear

在PostgreSQL里，逻辑结构从高到底依次是：数据库、模式（又叫架构）、表、行。当我们打开PostgreSQL官方自带的客户端软件pgadmin的时候，点开一个实例的连接，如下所示：

image.png

从图中我们可以看到，最外层的就是我们的在244服务器上的数据库服务实例，点开以后，第一层是数据库，我们点开postgres数据库。下层有

目录
事件触发器
拓展
架构

对于目录、时间触发器和拓展这里先不讲。但是要知道他们都是和架构是同一个层级的结构。在PostgreSQL里，架构又被称为模式（Schema），在一个数据库中会有多个模式。点开模式以后，如下所示：

image.png

模式下我们需要关注的对象是：

函数
序列
数据表
触发器函数
视图

后面会依次介绍这些内容，今天要学习的是表的结构。我们点开数据表，里面就可以看到各种表名了，我们随便点开一个表，查看一下表的结构，如下所示：

image.png

可以看到表里有

字段
约束
索引
规则
触发器
这些也是我们要关注的重点内容。后面几章我们来依次学习这些逻辑结构。在没有学习PostgreSQL的权限管理之前，我们默认使用具有最高权限的postgres账户来进行所有操作，同时也建议看这篇文章的所有人在操作之前一定要确认：不是在生产数据库上进行操作。建议最好是在自己的虚拟机上操作。

1. 数据库

对于数据库的操作包括数据库的创建、删除和修改，下面分别讲解：

创建数据库

数据库的创建命令格式是：

CREATE DATABASE dbname [with] 
                [owner [=] role_name] 
                [ TEMPLATE [=] template_name] 
                [ ENCODING [=] encoding] 
                [LC_COLLATE = lc_collate] 
                [LC_CTYPE [=] lc_ctype] 
                [ TABLESPACE [=] tablespace] 
                [CONNECTION LIMIT [=] connlimit ]

参数含义介绍：

with 表示后面会跟哪些参数，可带可不带
owner 表示数据库的所有者，可以指定数据库中存在的角色名，如果不指定，则默认是当前用户。
TEMPLATE 表示这个数据库以哪个模板来创建，前面的章节里讲过PostgreSQL自带两个模板template0和template1，可以在这两个模板的基础上继承生成新的数据库。
ENCODING 表示数据库的字符编码格式，默认是UTF8，开发人员可以根据自己的需要设置成不同的格式。
TABLESPACE 表示和数据库关联的表空间名字，在后面学习表空间的内容的时候介绍。
CONNECTION LIMIT 表示这个数据库允许的连接数，默认值是-1，表示没有限制。连接数的内容在之前数据库管理的章节里讲过。
LC_COLLATE 和 LC_CTYPE 是用于影响数据库字段排序的参数，根据数据库字符编码的格式来设置对应的值。

命令格式和参数含义介绍完以后，我们来看几个示例：

# 1. 创建一个数据库，不带任何参数
postgres=# create database testdb; 
CREATE DATABASE

#查看数据库，可以看到数据库所有者是postgres，就是当前用户，默认编码格式是UTF8
postgres=# \l testdb; 
                              List of databases
  Name  |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
--------+----------+----------+-------------+-------------+-------------------
 testdb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

# 2. 创建的时候指定数据库的所有者
postgres=# CREATE DATABASE testdb1 owner osdba;
CREATE DATABASE

#查看数据库信息，可以看到owner变成了osdba
postgres=# \l testdb1; 
                             List of databases
  Name   | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
---------+-------+----------+-------------+-------------+-------------------
 testdb1 | osdba | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)

删除数据库

删除数据库就一个命令：

DROP DATABASE [ IF EXISTS ] dbname

IF EXISTS 参数是用于避免删除不存在的数据库，不带这个参数删除不存在的数据库时会报错，而带这个参数，则数据库存在时直接删除，不存在时直接跳过。一般在事务处理或脚本中会带上这个参数
几个测试用例如下：

#删除一个不存在的数据库testdb2，会报错。
postgres=# DROP DATABASE testdb2;
ERROR:  database "testdb2" does not exist

#使用IF EXISTS 参数删除不存在的数据库testdb2，会提示跳过，不会报错。
postgres=# DROP DATABASE IF EXISTS testdb2;
NOTICE:  database "testdb2" does not exist, skipping
DROP DATABASE

#删除前面创建的数据库testdb，提示删除成功
postgres=# DROP DATABASE testdb;
DROP DATABASE

#带IF EXISTS关键字删除存在的数据库testdb1，提示和不带关键字时相同
postgres=# DROP DATABASE IF EXISTS testdb1;
DROP DATABASE

修改数据库

因为数据库关联的属性和逻辑结构较多，因此修改数据库的命令比较复杂，命令的基本格式是：

ALTER DATABASE dbname [WITH] [option]

带WITH关键字时，option可以是下面的各种样式：

CONNECTION LIMIT connlimit：connlimit表示连接限制值
ALLOW_CONNECTIONS allowconn ：是否允许连接，allowconn为true时表示允许连接，为false表示不允许任何人连接到数据库
IS_TEMPLATE istemplate：istemplate的值是true或者false，true时表示这个数据库可以作为模板被其他数据库继承，false表示不可继承。

不带with关键字时，option有以下几种样式：

RENAME TO newname 修改数据库的名称
OWNER TO newowner 将数据库的所有者改为另外一个用户，这个命令只能是数据库所有者或者超级用户才能执行。
SET TABLESPACE new_tablespace 将数据库关联的表空间修改为新的表空间
SET config_param {TO | =} {value|DEFAULT} 将某个配置的值设置为某个值或者默认值（DEFAULT)
SET config_param FROM CURRENT 将某个配置的值设置为当前值？
RESET config_param 将某个配置的值恢复为默认值？
RESET ALL 将数据库恢复为默认配置？

示例1：带with关键字

# 查看当前数据库的连接数限制
SELECT datconnlimit FROM pg_database WHERE datname = 'testdb';
 datconnlimit 
--------------
           -1
(1 row)
#可以看到默认的设置是-1，即不限制连接，将它的值修改为1，然后再看
postgres=# alter database testdb with connection limit 1;
ALTER DATABASE
postgres=# select datconnlimit from pg_database where datname = 'testdb'; 
 datconnlimit 
--------------
            1
(1 row)
#这个时候要保证testdb数据库上没有任何连接，我们通过下面这个语句来查看
postgres=# select datname, usename  from pg_stat_activity where datname = 'testdb'; 
 datname | usename 
---------+---------
(0 rows)
# 如果像上面那样显示0行，说明该数据库上没有任何连接。我们打开两个终端，使用普通的osdba用户来连接
#第一次连接
-bash-4.2$ psql -U osdba -d testdb
Password for user osdba: 
psql (9.6.6)
Type "help" for help.

testdb=> 
# 第二次连接 
-bash-4.2$ psql -U osdba -d testdb 
Password for user osdba: 
psql: FATAL:  too many connections for database "testdb"
# 可以看到提示连接太多，说明限制生效。也说我们我们修改的配置生效。

示例2：不带with关键字

# 修改数据库testdb为testdb00
postgres=# ALTER DATABASE testdb RENAME TO testdb00; 
ALTER DATABASE
postgres=# \l testdb00; 
                               List of databases
   Name   |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
----------+----------+----------+-------------+-------------+-------------------
 testdb00 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)
# 修改后查看数据库，可以看到除了名称，数据库的其他信息都没有发生变化。查看数据库的连接限制，如下所示：
postgres=# select datconnlimit from pg_database where datname = 'testdb00'; 
 datconnlimit 
--------------
            1
(1 row)
# 可以看到除了名称变化了，连接限制也还是刚才我们设置的值。

上面就是数据库的相关内容。

2. 模式

在PostgreSQL中，连接到某个数据库上面以后，如果需要查看另外一个数据库的数据，则只能断开当前的数据连接，然后重新连接另外一个数据库。这样的情况下，当前端的应用同时连接多个数据库时，查看数据库来回切换就非常麻烦。而在PostgreSQL里存在模式这样一个概念，表、视图和函数等都是保存到模式里面，在同一个数据库里，可以同时查看多个模式里面的表，只需要通过点号(.)操作符来进行操作即可，格式是：
schema_name.table_name
这样操作的话，就可以实现一个模式对应一个应用，多个应用的数据存放到同一个项目数据库中，方便管理。

公共模式

但是我们在写SQL语句的时候，或者进行数据库连接的时候，却并没有使用这种访问方式，一般都是直接写的表名，而PostgreSQL也没有提示出错，这是为什么呢？这里面涉及到一个概念——公共模式，这个模式的意思是：当新建数据库以及连接数据库时，如果没有指定模式名称，都会自动创建一个叫做public的模式或者连接到public模式。
而PostgreSQL为什么会自动创建public模式或者连接到public模式呢？这是因为在我们的SQL语句中只输入了表名的时候，例如下面这样一个SQL语句：
SELECT * FROM table1 WHERE pub_date = '2017-01-01'
我们给出的表名就是table1，PostgreSQL在用户的搜索路径中查找table1这个表名。而每个用户的搜索路径可以通过search_path这个变量来查看：

postgres=# show search_path; 
   search_path   
-----------------
 "$user", public
(1 row)

上面就是postgres用户的搜索路径。搜索路径的规则是：

不同的路径名之间通过逗号隔开，PostgreSQL依次搜索，如果在哪个路径中找到表名，则是用这个路径下的表，如果没找到则查找下一个；
$user指的是连接到当前数据库的用户，如果存在一个和用户名相同的模式，且用户在这个模式中具有USAGE权限，则最先使用这个模式；
public默认在所有的用户路径下，默认所有用户都在public模式下具有USAGE权限，如果创建数据库时没有创建和用户名同名的模式，则登录时默认登录到public模式。

这就是为什么我们登录新建的数据库时，都会在public模式下。下面我们继续深入学习模式相关的操作：创建模式、删除模式和修改模式。

创建模式

创建模式的命令基本格式是：

CREATE SCHEMA schema_name [AUTHORIZATION role_specification] [schema_element [...] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

参数解释：

AUTHORIZATION role_specification：指的是将这个模式赋予给某个在数据库中的用户或角色
schema_element：指的是模式的其他参数，包括CREATE TABLE/VIEW/INDEX/SEQUENCE/TRIGGER和GRANT语句等。用于创建模式下的各种对象。

上面前两个语句分别是创建新的模式并赋权给某个用户，第二个语句是直接创建和某个用户同名的模式。后面两个语句则是在前面两个语句里添加了一个逻辑判断。
来看示例：

# 创建一个名为test_schema的模式
postgres=# CREATE SCHEMA test_schema AUTHORIZATION osdba;
CREATE SCHEMA
postgres=# \dn 
    List of schemas
    Name     |  Owner   
-------------+----------
 public      | postgres
 test_schema | osdba
(2 rows)

#创建一个和osdba用户同名的模式
postgres=# CREATE SCHEMA AUTHORIZATION osdba;
CREATE SCHEMA
postgres=# \dn 
    List of schemas
    Name     |  Owner   
-------------+----------
 osdba       | osdba
 public      | postgres
 test_schema | osdba
(3 rows)

#创建模式的同时创建一张表
postgres=# CREATE SCHEMA schema2 AUTHORIZATION osdba CREATE TABLE t1(id int primary key, comments varchar(50)); 
CREATE SCHEMA

# 查看创建的模式的信息
postgres=# \dn 
    List of schemas
    Name     |  Owner   
-------------+----------
 osdba       | osdba
 public      | postgres
 schema2     | osdba
 test_schema | osdba
(4 rows)

# 查看模式里的表t1的信息
postgres=# \d schema2.t1; 
              Table "schema2.t1"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 id       | integer               | not null
 comments | character varying(50) | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)

删除模式

删除模式的命令格式是：
DROP SCHEMA schema_name
看示例：

#删除刚才创建的一个模式test_schema；
postgres=# DROP SCHEMA test_schema; 
DROP SCHEMA
postgres=# \dn 
  List of schemas
  Name   |  Owner   
---------+----------
 osdba   | osdba
 public  | postgres
 schema2 | osdba
(3 rows)

直接删除，非常简单。

修改模式

对于模式，只允许修改其名称和它的属主这两种信息，命令格式如下：

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

示例如下：

#将当前的schema2模式修改未sma2
postgres=# ALTER SCHEMA schema2 RENAME TO sma2;
ALTER SCHEMA
postgres=# \dn 
  List of schemas
  Name  |  Owner   
--------+----------
 osdba  | osdba
 public | postgres
 sma2   | osdba
(3 rows)

# 将sma2的属主修改为postgres
postgres=# ALTER SCHEMA sma2 OWNER TO postgres; 
ALTER SCHEMA
postgres=# \dn 
  List of schemas
  Name  |  Owner   
--------+----------
 osdba  | osdba
 public | postgres
 sma2   | postgres
(3 rows)

模式的安全性

前面我们说到，默认情况下会自动创建一个public模式，连接的时候也是连接到public模式，但是因为默认情况下，所有的用户在public模式下都有USAGE和CREATE权限，而具有CREATE权限，就可以在public模式下创建各种对象。这种情况下，很容易导致数据库管理混乱。因此为了方便管理，同时为了数据的安全性，最后不要使用public模式，同时也不要随便对外开放最高权限的postgres用户。而应该为每个应用单独建立一个模式，使用和模式对应的用户名，实现专号和专户对应。这样不光便于管理，同时有利于排错。

在某个库里创建一个和角色同名的模式，那么这个用户切换到这个库的时候，默认模式就是它的同名模式，这个同名模式的默认拥有者也是该角色，而不是它的创建者。如下面示例中所示：

# 创建dbuser角色同名的模式dbuser，可以看到它的拥有者也是dbuser，而不是创建者postgres
test=# create schema authorization dbuser; 
CREATE SCHEMA
test=# \dn 
   List of schemas
   Name   |  Owner   
----------+----------
 dbuser   | dbuser
 public   | postgres
 topology | postgres
(3 rows)

# 使用dbuser用户登录，查看当前模式
-bash-4.2$ psql -U dbuser -d test  
psql (9.6.9, server 9.5.13)
Type "help" for help.

test=> select current_schema; 
 current_schema 
----------------
 dbuser
(1 row)

以上就是数据库逻辑结构中数据库和模式的内容，在下一篇文章中，会继续介绍数据库的结构内容，包括表、视图、索引、触发器、表空间等内容。

七. PostgreSQL逻辑结构（1）—数据库和模式

1. 数据库

创建数据库

删除数据库

修改数据库

示例1：带with关键字

示例2：不带with关键字

2. 模式

公共模式

创建模式

删除模式

修改模式

模式的安全性

猜你喜欢

热点阅读

七. PostgreSQL逻辑结构（1）—数据库和模式

1. 数据库

创建数据库

删除数据库

修改数据库

示例1：带with关键字

示例2： 不带with关键字

2. 模式

公共模式

创建模式

删除模式

修改模式

模式的安全性

猜你喜欢

热点阅读

示例2：不带with关键字