PostgreSQL教程(二):模式Schema详解

一个数据库包含一个或多个命名的模式,模式又包含表。模式还包含其它命名的对象,包括数据类型、函数,以及操作符。同一个对象名可以在不同的模式里使用而不会导致冲突; 比如,schema1和myschema都可以包含叫做mytable的表。和数据库不同,模式不是严格分离的:一个用户可以访问他所连接的数据库中的任意模式中的对象,只要他有权限。

我们需要模式有以下几个主要原因:

1). 允许多个用户使用一个数据库而不会干扰其它用户。
    2). 把数据库对象组织成逻辑组,让它们更便于管理。
    3). 第三方的应用可以放在不同的模式中,这样它们就不会和其它对象的名字冲突。
    
    1. 创建模式:
 

代码如下:

CREATE SCHEMA myschema;

通过以上命令可以创建名字为myschema的模式,在该模式被创建后,其便可拥有自己的一组逻辑对象,如表、视图和函数等。
    
    2. public模式:

在介绍后面的内容之前,这里我们需要先解释一下public模式。每当我们创建一个新的数据库时,PostgreSQL都会为我们自动创建该模式。当登录到该数据库时,如果没有特殊的指定,我们将以该模式(public)的形式操作各种数据对象,如:
 

代码如下:

CREATE TABLE products ( ... ) 等同于 CREATE TABLE public.products ( ... )

    3. 权限:
    缺省时,用户看不到模式中不属于他们所有的对象。为了让他们看得见,模式的所有者需要在模式上赋予USAGE权限。为了让用户使用模式中的对象,我们可能需要赋予额外的权限,只要是适合该对象的。PostgreSQL根据不同的对象提供了不同的权限类型,如:
 

代码如下:

GRANT ALL ON SCHEMA myschema TO public;

上面的ALL关键字将包含CREATE和USAGE两种权限。如果public模式拥有了myschema模式的CREATE权限,那么登录到该模式的用户将可以在myschema模式中创建任意对象,如:
 

代码如下:

CREATE TABLE myschema.products (
        product_no integer,
        name text,
        price numeric CHECK (price > 0),
    );

在为模式下的所有表赋予权限时,需要将权限拆分为各种不同的表操作,如:
 

代码如下:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
    GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO public;

在为模式下的所有Sequence序列对象赋予权限时,需要将权限拆分为各种不同的Sequence操作,如:
 

代码如下:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
    GRANT SELECT, UPDATE, USAGE ON SEQUENCES TO public;

在为模式下的所有函数赋予权限时,仅考虑执行权限,如:
 

代码如下:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
    GRANT EXECUTE ON FUNCTIONS TO public;

可以看出,通过以上方式在public模式下为myschema模式创建各种对象是极为不方便的。下面我们将要介绍另外一种方式,即通过role对象,直接登录并关联到myschema对象,之后便可以在myschema模式下直接创建各种所需的对象了。
   

代码如下:

CREATE ROLE myschema LOGIN PASSWORD '123456'; --创建了和该模式关联的角色对象。
    CREATE SCHEMA myschema AUTHORIZATION myschema; --将该模式关联到指定的角色,模式名和角色名可以不相等。

在Linux Shell下,以myschema的角色登录到数据库MyTest,在密码输入正确后将成功登录到该数据库。
 

代码如下:

/> psql -d MyTest -U myschema
    Password:
    MyTest=> CREATE TABLE test(i integer);
    CREATE TABLE
    MyTest=> \d   --查看该模式下,以及该模式有权限看到的tables信息列表。
              List of relations
     Schema     |   Name   | Type  |  Owner
    ------------+---------+------+----------
     myschema |   test     | table  | myschema
    (1 rows)

    4. 删除模式:
 

代码如下:

DROP SCHEMA myschema;

如果要删除模式及其所有对象,请使用级联删除:
 

代码如下:

DROP SCHEMA myschema CASCADE;

    5. 模式搜索路径:

我们在使用一个数据库对象时可以使用它的全称来定位对象,然而这样做往往也是非常繁琐的,每次都不得不键入owner_name.object_name。PostgreSQL中提供了模式搜索路径,这有些类似于Linux中的$PATH环境变量,当我们执行一个Shell命令时,只有该命令位于$PATH的目录列表中,我们才可以通过命令名直接执行,否则就需要输入它的全路径名。PostgreSQL同样也通过查找一个搜索路径来判断一个表究竟是哪个表,这个路径是一个需要查找的模式列表。在搜索路径里找到的第一个表将被当作选定的表。如果在搜索路径中 没有匹配表,那么就报告一个错误,即使匹配表的名字在数据库其它的模式中存在也如此。
    在搜索路径中的第一个模式叫做当前模式。除了是搜索的第一个模式之外,它还是在CREATE TABLE没有声明模式名的时候,新建表所属于的模式。要显示当前搜索路径,使用下面的命令:
 

代码如下:

MyTest=> SHOW search_path;
      search_path
    ----------------
     "$user",public
    (1 row)

可以将新模式加入到搜索路径中,如:
 

代码如下:

SET search_path TO myschema,public;

为搜索路径设置指定的模式,如:
 

代码如下:

SET search_path TO myschema; --当前搜索路径中将只是包含myschema一种模式。

(0)

相关推荐

  • PostgreSQL教程(十三):数据库管理详解

    一.概述: 数据库可以被看成是SQL对象(数据库对象)的命名集合,通常而言,每个数据库对象(表.函数等)只属于一个数据库.不过对于部分系统表而言,如pg_database,是属于整个集群的.更准确地说,数据库是模式的集合,而模式包含表.函数等SQL对象.因此完整的对象层次应该是这样的:服务器.数据库.模式.表或其他类型的对象. 在与数据库服务器建立连接时,该连接只能与一个数据库形成关联,不允许在一个会话中进行多个数据库的访问.如以postgres用户登录,该用户可以访问的缺省数据库为postgr

  • PostgreSQL教程(四):数据类型详解

    一.数值类型: 下面是PostgreSQL所支持的数值类型的列表和简单说明: 1. 整数类型: 类型smallint.integer和bigint存储各种范围的全部是数字的数,也就是没有小数部分的数字.试图存储超出范围以外的数值将导致一个错误.常用的类型是integer,因为它提供了在范围.存储空间和性能之间的最佳平衡.一般只有在磁盘空间紧张的时候才使用smallint.而只有在integer的范围不够的时候才使用bigint,因为前者(integer)绝对快得多.     2. 任意精度数值:

  • PostgreSQL教程(六):函数和操作符详解(2)

    六.模式匹配: PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式.     1. LIKE:   复制代码 代码如下: string LIKE pattern [ ESCAPE escape-character ]     string NOT LIKE pattern [ ESCAPE escape-character ] 每个pattern定义一个字串的集合.如果该string包含在pattern代

  • PostgreSQL教程(十四):数据库维护

    一.恢复磁盘空间: 在PostgreSQL中,使用delete和update语句删除或更新的数据行并没有被实际删除,而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期.因此当数据表中的数据变化极为频繁时,那么在一段时间之后该表所占用的空间将会变得很大,然而数据量却可能变化不大.要解决该问题,需要定期对数据变化频繁的数据表执行VACUUM操作. VACUUM命令存在两种形式,VACUUM和VACUUM FULL,它们之间的区别见如下表格:   无VACUUM VACUUM VACUUM

  • PostgreSQL教程(十):性能提升技巧

    一.使用EXPLAIN: PostgreSQL为每个查询都生成一个查询规划,因为选择正确的查询路径对性能的影响是极为关键的.PostgreSQL本身已经包含了一个规划器用于寻找最优规划,我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划.     PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树,其中最底层的节点为表扫描节点,用于从数据表中返回检索出的数据行.然而,不同的扫描节点类型代表着不同的表访问模式,如:顺序扫描.索引扫描,以及位图索引扫描等.如果查

  • PostgreSQL教程(十一):服务器配置

    一.服务器进程的启动和关闭: 下面是pg_ctl命令的使用方法和常用选项,需要指出的是,该命令是postgres命令的封装体,因此在使用上比直接使用postgres更加方便. 复制代码 代码如下: pg_ctl init[db] [-D DATADIR] [-s] [-o "OPTIONS"]     pg_ctl start     [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]    

  • PostgreSQL教程(十二):角色和权限管理介绍

    PostgreSQL是通过角色来管理数据库访问权限的,我们可以将一个角色看成是一个数据库用户,或者一组数据库用户.角色可以拥有数据库对象,如表.索引,也可以把这些对象上的权限赋予其它角色,以控制哪些用户对哪些对象拥有哪些权限.     一.数据库角色: 1. 创建角色:   复制代码 代码如下: CREATE ROLE role_name; 2. 删除角色:   复制代码 代码如下: DROP ROLE role_name; 3. 查询角色: 检查系统表pg_role,如:   复制代码 代码如

  • PostgreSQL教程(五):函数和操作符详解(1)

    一.逻辑操作符: 常用的逻辑操作符有:AND.OR和NOT.其语义与其它编程语言中的逻辑操作符完全相同. 二.比较操作符: 下面是PostgreSQL中提供的比较操作符列表: 比较操作符可以用于所有可以比较的数据类型.所有比较操作符都是双目操作符,且返回boolean类型.除了比较操作符以外,我们还可以使用BETWEEN语句,如:     a BETWEEN x AND y 等效于 a >= x AND a <= y        a NOT BETWEEN x AND y 等效于 a <

  • PostgreSQL教程(一):数据表详解

    一.表的定义: 对于任何一种关系型数据库而言,表都是数据存储的最核心.最基础的对象单元.现在就让我们从这里起步吧.     1. 创建表:   复制代码 代码如下: CREATE TABLE products (         product_no integer,         name text,         price numeric     );     2. 删除表:   复制代码 代码如下: DROP TABLE products;     3. 创建带有缺省值的表:   复

  • PostgreSQL教程(三):表的继承和分区表详解

    一.表的继承: 这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生,然而它的实现方式和设计原理却是简单易懂,现在就让我们从一个简单的例子开始吧.     1. 第一个继承表:   复制代码 代码如下: CREATE TABLE cities (   --父表         name        text,         population float,         altitude     int     );     CREATE TABLE capitals (

  • PostgreSQL教程(七):函数和操作符详解(3)

    九.序列操作函数: 序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表.一个序列对象通常用于为行或者表生成唯一的标识符.下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法. 函数 返回类型 描述 nextval(regclass) bigint 递增序列对象到它的下一个数值并且返回该值.这个动作是自动完成的.即使多个会话并发运行nextval,每个进程也会安全地收到一个唯一的序列值. currval(regclass) bigint 在当

  • PostgreSQL教程(八):索引详解

    一.索引的类型: PostgreSQL提供了多 种索引类型:B-Tree.Hash.GiST和GIN,由于它们使用了不同的算法,因此每种索引类型都有其适合的查询类型,缺省时,CREATE INDEX命令将创建B-Tree索引.         1. B-Tree:   复制代码 代码如下: CREATE TABLE test1 (         id integer,         content varchar     );     CREATE INDEX test1_id_index

  • PostgreSQL教程(九):事物隔离介绍

    在SQL的标准中事物隔离级别分为以下四种:     1. 读未提交(Read uncommitted)     2. 读已提交(Read committed)     3. 可重复读(Repeatable read)     4. 可串行化(Serializable)     然而PostgreSQL在9.1之前的版本中只是实现了其中两种,即读已提交和可串行化,如果在实际应用中选择了另外两种,那么PostgreSQL将会自动向更严格的隔离级别调整.在PostgreSQL v9.1的版本中提供了三

随机推荐