postgresql 客户端_PostgreSQL--基础语法

本文章的大部分内容均摘录自<PostgreSQL即学即用>

1.创建database

最基本的创建数据库的SQL语句是:

CREATE DATABASE mydb;

改命令会以template1库为模板生成一份副本,并以此为副本作为新database,每个database都会有一个属主,这个新库的属主就是执行此SQL命令的角色.

PostgreSQL安装完毕后默认附带两个模板数据库:template0和template1.如果创建数据时没有指定哪个模板,那么系统会默认使用template1作为新库的模板.

你可以使用任何一个现存的database作为创建新库的模板.此外,你还可以将某个现存的数据库作为模板数据库,对于这种被标记为模板的数据库,postgreSQL会禁止其进行编辑或者删除.任何一个具备CREATEDB权限的角色都可以使用这种模板数据库.以超级用户身份运行以下SQL可以使任何数据库成为模板数据库.

UPDATE pg_database SET datistemplate = TRUE where datname = 'mydb';

如果你希望修改或者删除被标记为模板的数据库,请先将上述语句中的datistemplate字段改为false,这样就可以放开编辑限制.

2.schema的使用

schema可以对database中的对象进行逻辑分组管理.如果你的服务器上有一堆的database,那么管理起来也会很麻烦,可以通过schema来对数据进行分类并全部存放到一个database中.schema中的对象名不允许重复,但同一个database的不同schema中的对象是可以重名的.你可以字形管理和组织schema,例如假设要为一家航空公司设计IT系统,那么可以将飞机信息及其日常维护信息表放到一个单独的plane的schema中,把所有机组人员及其人事信息放到hr的schema中,再单独创建一个schema用于记录乘客相关的信息,这样就把所有信息分门别类隔离开来了.

另一种常见的管理schema的方法是基于角色的管理.当系统拥有多个客户端并且每个客户端的数据必须完全隔离时,这种方法特别合适.

假设你的工作是开发一套'宠物狗信息管理系统'并将该在线系统租赁给宠物狗SPA店使用.通过广告,现在你有了一些客户,但该系统的数据库目前仅用了一张dogs表来存储所有宠物狗的信息.你的系统前期已经满足了政府要求的一堆稀奇古怪的规定,还有一个要求没有达到,那就是客户间的数据必须完全隔离,即保证一家SPA店看不到另一家SPA店的宠物狗信息.为了满足这个需求,你可以为每家客户都建立一个单独的schema,每个schema中建立相同的一张dogs表.

创建schema的语句是:

CREATE schema my_schema;

然后把这个新的schema加入到search_path;

ALTER DATABASE mydb SET search_path = '"$USER",public,my_schema',

3.权限的类型

postgreSQL中支持的对象级权限包括SELECT,INSERT,UPDATE,ALTER,EXECUTE,TRUNCATE等以及一个附带的WITH GRANT修饰符.除了GRANT之外,前几类的权限都可以顾名思义猜到其含义,GRANT的用法后续继续讲解.

在创建你的数据库之前,需要先建立一个角色作为此database的所有者,所有者可以登录该库.

CREATE ROLE mydb_admin LOGIN PASSWORD 'some_password';

4.GRANT

grant 命令可以将权限授予他人,基本用法如下:

CREATE some_privilege TO some_role;

记住以下几条关于GRANT的使用原则:

只有权限的拥有者才能将权限授予别人,并且拥有者自身还得有GRANT的权限

有些权限只有对象的所有者才能拥有,任何情况下不能授予别人.这类权限包扩DROP和ALTER

授权时加上WITH GRANT子句,这意味着被授权者可以将得到的权限再次授予 别人

GRANT ALL ON ALL TABLES IN SCHEMA public TO mydb_admin WITH GRANT OPTION;