听说介绍对象？对！介绍数据库对象！

听说介绍对象？对！介绍数据库对象！

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

业务范围：数据库安装部署、日常维护、主备切换、故障处理、性能优化、技术培训等。
需要的伙伴或者商业合作请移步 公众号【呆呆的私房菜】获取联系方式。

阅读本文可以了解到PostgreSQL数据库对象基本概念及关于数据库、表空间和相关对象的管理。

01 数据库对象概述

逻辑存储结构是内部的组织和管理数据的方式。所有的数据库对象都有各自的object identifers，oid是一个无符号的4字节整数，相关对象的oid都存放在相关的系统catalog表中，比如数据库的oid和表的oid分别存放在pg_database和pg_class表中。

1. Database Cluster： 数据库集簇是指一组database，使用的是同一个配置文件并响应公共端口的请求。属于集群的数据库使用一个共同的文件系统位置，存在一套共同的后台进程和内存结构。

不同的数据库集簇可以通过物理存储的绝对路径（$PGDATA）及其端口号进行区分。

2. tablespace： 表空间是数据库对象的容器，一个表空间可以供多个数据库使用；pg_global系统字典表都存在tablesapce下。pg_default是默认表空间；在创建数据库的时候可以指定默认表空间。表空间实际上就是为数据库对象指定一个存储的目录。

3. Database： 数据库是一些SQL对象的命名集合，提供了数据存储功能；一般来说每个数据库对象（表、视图、序列、函数等）只属于一个数据库（除了几个系统表如pg_database、pg_indexes等属于database cluster并且对database cluster中的每个database都是可以访问的）；数据库是一个模式的集合，而模式包含表、函数等数据库对象。

**完整的层次是：database --> schema --> table/view/sequence/function等
**

4. schema： 模式是对数据库的逻辑分隔，可以理解为数据库对象分组；一个数据库可以存在多个模式，创建的数据对象都隶属于某个模式；在创建数据库的同时，就已经默认为数据库创建了一个默认模式（public）；不指定模式的数据库对象都存在public模式中；不同模式下的对象是可以重名的。

5. table： 表是数据库中的一个基本的数据库对象，除了普通表之外还有分区表和外部表；一个数据库中可以有若干表，表是用来存储数据的一种逻辑结构；表是由行和列组成的，因此也成为二维表；在PostgreSQL中，每个表最多可以创建250-1600个字段；每个表中的记录条数是无限制的；表文件缺省最大是1GB，超过1GB后，会以下划线+数字的形式作为文件后缀名创建新文件继续存储数据。

6. constraint： 约束时数据表列执行的规则，用来防止无效的数据被输入到数据库中，确保数据库中数据的准确性和可靠性；约束可以是列级也可以是表级。

7. index： 索引提供了在表中快速查找数据的数据库结构；在正确使用索引的前提下，可以明显提高数据库检索速度；索引指定表的一列或多列，并指示索引是否在升序或降序排列。

8. view： 视图是根据需求定义从1个或多个表（或视图）导出的数据结构；它是一个虚表，不存储实际数据，数据库中只存储了视图的定义；对视图的数据进行操作时，系统根据视图的定义去操作和视图相关的基表；在优化器中会把视图当作子查询；PostgreSQL视图默认是不可以更新的，除非创建了规则；同时PostgreSQL支持物化视图功能。

9. sequence： 序列是用来生成唯一的键。

10. function： 函数是一段预编译的代码，接收输入参数并返回一个值。函数可以是内置的系统函数，也可以是用户自定义的函数；函数主要用域计算和返回一个值，而不是执行一系列的SQL语句；它可以在select语句、where子句、order by子句等中使用；函数通常在SQL语句中嵌套使用，可以用于计算列值、过滤数据等；一般情况下，函数不包含事务控制语句，因为函数的目标是计算还不是执行修改数据库结构的操作。

12. procedure： 存储过程时一组预编译的SQL语句集，可以在需要的时候调用执行；存储过程通常由数据库管理员或有特殊权限的用户创建；存储过程主要用于封装和执行一系列的SQL语句以完成特定的任务；它可以接受参数、并且可以包含条件逻辑、循环等程序控制结构；存储过程可以被应用程序或其他存储过程调用；存储过程创建了之后可以在数据库中被重复使用，提高了代码可维护性和可重用性；存储过程可以包含事务控制语句，允许进行复杂的事务处理。

13. trigger： 触发器是一组动作或数据库回调函数。触发器在指定的表上执行数据库事件；用于验证输入数据、执行业务规则、保持审计跟踪等；对于操作修改的每一行，都会调用一个标记为for each rows的触发器，改触发器只对任何给定的操作执行一次，而不管修改了多少行数据；同一个事件可以定义同一类型的多个触发器，但条件是按明程按字母顺序触发；当触发器相关联的表被删除时，触发器会自动被删除。

02 数据库对象管理

**1. tablespace管理：
**PostgreSQL表空间可以有效的解决集群分区不足或者卷空间不足的问题，也可以用来区分冷热数据，提高数据库效率。

postgres=# \h create tablespace
命令：       CREATE TABLESPACE
描述：       建立新的表空间
语法：
CREATE TABLESPACE 表空间的名称
    [ OWNER { 新的属主 | CURRENT_USER | SESSION_USER } ]
    LOCATION '目录'
    [ WITH ( 表空间_选项 = 值 [, ... ] ) ]
    
postgres=# \h alter tablespace
命令：       ALTER TABLESPACE
描述：       更改表空间的定义
语法：
ALTER TABLESPACE 名称 RENAME TO 新的名称
ALTER TABLESPACE 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
ALTER TABLESPACE 名称 SET ( 表空间_选项 = 值 [, ... ] )
ALTER TABLESPACE 名称 RESET ( 表空间_选项 [, ... ] )

## 创建表空间
create database <tnsname> location '/home/postgres/<tbsname>';

## 指定数据库默认表空间
create database <dbname> tablespace <tbsname>;
## 改变数据库默认表空间需要断开数据库连接
alter database <dbname> tablespace <tbsname>;

## 指定表默认表空间
create table <tname> tablespace <tbsname>;
## 移动表到目标表空间，会锁表
alter table <tname> set tablespace <tbsname>; 

## 授权表空间使用权限
grant create on tablesapce <tbsname> to <user>;

## 删除表空间
drop tablesapce <tbsname>;

## 临时表空间（用来存储临时表或临时表索引、以及sql执行时产生的临时文件如排序、哈希、聚合等操作）
## pg允许用户配置多个临时表空间，配置时用逗号隔开
## 如果temp_tablespaces参数没有配置，则临时表空间对应的是默认表空间
## 为了提高性能，一般临时表空间推荐放在SSD或者IOPS，以及吞吐量较高的分区
alter role all set temp_tablespaces = '<tmp_tbs_name1>,<tmp_tbs_name2>';

**2. database管理：
**

postgres=# \h create database
命令：CREATE DATABASE
描述：建立新的数据库
语法：
CREATE DATABASE 名称
    [ [ WITH ] [ OWNER [=] 用户名 ]
           [ TEMPLATE [=] 模版 ]
           [ ENCODING [=] 字符集编码 ]
           [ LC_COLLATE [=] 排序规则 ]
           [ LC_CTYPE [=] 字符分类 ]
           [ TABLESPACE [=] 表空间的名称 ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] 连接限制 ]
           [ IS_TEMPLATE [=] istemplate ] ]
           
postgres=# \h alter database
命令：ALTER DATABASE
描述：更改一个数据库
语法：
ALTER DATABASE 名称 [ [ WITH ] 选项 [ ... ] ]
选项可以是
    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT 连接限制
    IS_TEMPLATE istemplate
ALTER DATABASE 名称 RENAME TO 新的名称
ALTER DATABASE 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
ALTER DATABASE 名称 SET TABLESPACE 新的表空间
ALTER DATABASE 名称 SET 配置参数 { TO | = } { 值 | DEFAULT }
ALTER DATABASE 名称 SET 配置参数 FROM CURRENT
ALTER DATABASE 名称 RESET 配置参数
ALTER DATABASE 名称 RESET ALL

postgres=# \h drop database
命令：DROP DATABASE
描述：移除数据库
语法：
DROP DATABASE [ IF EXISTS ] 名称

## 创建数据库，不指明数据库模板，默认克隆template1数据库
create database <dbname> with owner <oname> encoding 'UTF8' tablespace <tbsname>;;

## 修改数据库
alter database <dbname> set <parameter> to <value>;
alter database <dbname> reset <parameter> / all;

## 删除数据库
drop database <dbname>;

## 查看数据库
select * from pg_database;
## 查看数据库大小
select pg_database_size('<dbname>'), pg_size_pretty(pg_database_size('<dbname>'));

**3. schema管理：
**

postgres=# \h create schema
命令：CREATE SCHEMA
描述：建立新的架构模式
语法：
CREATE SCHEMA 模式名称 [ AUTHORIZATION role_specification ] [ 模式中对象 [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ 模式中对象 [ ... ] ]
CREATE SCHEMA IF NOT EXISTS 模式名称 [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
这里role_specification可以是：
    用户名
  | CURRENT_USER
  | SESSION_USER
postgres=# \h alter schema
命令：ALTER SCHEMA
描述：更改架构模式的定义
语法：
ALTER SCHEMA 名称 RENAME TO 新的名称
ALTER SCHEMA 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }

postgres=# \h drop schema
命令：DROP SCHEMA
描述：移除一个模式
语法：
DROP SCHEMA [ IF EXISTS ] 名称 [, ...] [ CASCADE | RESTRICT ]

## 创建schema并指定所有者
create schema <schemaname> authorization <user>;
## 以所有者名创建schema
create schema authorization <user>;

## 重命名schema
alter schema <schemaname> rename to <new_schemaname>;
## 修改所有者
alter schema <schemaname> owner to <new_user>;

## 删除模式
drop schema <schemaname> cascade;

## 查看模式，默认是$user,public
## 当某个用户登录到数据库时，先查找和登录用户同名的schema，在查找public
show search_path;

## 修改设置search_path
## 会话级
set search_path <search_name>;
## 数据库级
alter database <dbname> set search_path <search_name>;

**4. table管理：
**

postgres=# \h create table
命令：CREATE TABLE
描述：建立新的数据表
语法：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] 表名 ( [
  { 列名称 数据_类型 [ COLLATE 校对规则 ] [ 列约束 [ ... ] ]
    | 表约束
    | LIKE 源表 [ like选项 ... ] }
    [, ... ]
] )
[ INHERITS ( 父表 [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { 列名称 | ( 表达式 ) } [ COLLATE 校对规则 ] [ 操作符类型的名称 ] [, ... ] ) ]
[ WITH ( 存储参数 [= 值] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE 表空间的名称 ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] 表名
    OF 类型名称 [ (
  { 列名称 [ WITH OPTIONS ] [ 列约束 [ ... ] ]
    | 表约束 }
    [, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { 列名称 | ( 表达式 ) } [ COLLATE 校对规则 ] [ 操作符类型的名称 ] [, ... ] ) ]
[ WITH ( 存储参数 [= 值] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE 表空间的名称 ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] 表名
    PARTITION OF 父表 [ (
  { 列名称 [ WITH OPTIONS ] [ 列约束 [ ... ] ]
    | 表约束 }
    [, ... ]
) ] { FOR VALUES 分区绑定规范 | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { 列名称 | ( 表达式 ) } [ COLLATE 校对规则 ] [ 操作符类型的名称 ] [, ... ] ) ]
[ WITH ( 存储参数 [= 值] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE 表空间的名称 ]

列的约束是:

[ CONSTRAINT 约束名称 ]
{ NOT NULL |
  NULL |
  CHECK ( 表达式 ) [ NO INHERIT ] |
  DEFAULT 默认_表达式 |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( 序列选项 ) ] |
  UNIQUE 索引参数 |
  PRIMARY KEY 索引参数 |
  REFERENCES 所引用的表 [ ( 所引用的列 ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE 操作 ] [ ON UPDATE 操作 ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

表约束是:

[ CONSTRAINT 约束名称 ]
{ CHECK ( 表达式 ) [ NO INHERIT ] |
  UNIQUE ( 列名称 [, ... ] ) 索引参数 |
  PRIMARY KEY ( 列名称 [, ... ] ) 索引参数 |
  EXCLUDE [ USING 访问索引的方法 ] ( 排除项 WITH 运算子 [, ... ] ) 索引参数 [ WHERE ( 述词 ) ] |
  FOREIGN KEY ( 列名称 [, ... ] ) REFERENCES 所引用的表 [ ( 所引用的列 [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE 操作 ] [ ON UPDATE 操作 ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

like_选项是

{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }

并且分区绑定的规范是:

IN ( { 数字_文字 | 字符串_文字 | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { 数字_文字 | 字符串_文字 | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
  TO ( { 数字_文字 | 字符串_文字 | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS 数字_文字, REMAINDER 数字_文字 )
在UNIQUE, PRIMARY KEY和EXCLUDE中的索引参数是：
[ INCLUDE ( 列名称 [, ... ] ) ]
[ WITH ( 存储参数 [= 值] [, ... ] ) ]
[ USING INDEX TABLESPACE 表空间的名称 ]
在EXCLUDE约束中的排除项是：
{ 列名称 | ( 表达式 ) } [ 操作符类型的名称 ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]


postgres=# \h alter table
命令：ALTER TABLE
描述：更改数据表的定义
语法：
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名称 [ * ]
    操作 [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名称 [ * ]
    RENAME [ COLUMN ] 列名称 TO new_column_name(新列名)
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名称 [ * ]
    RENAME CONSTRAINT 约束名称 TO new_constraint_name(新约束名)
ALTER TABLE [ IF EXISTS ] 名称
    RENAME TO 新的名称
ALTER TABLE [ IF EXISTS ] 名称
    SET SCHEMA 新的模式
ALTER TABLE ALL IN TABLESPACE 名称 [ OWNED BY 角色名称 [, ... ] ]
    SET TABLESPACE 新的表空间 [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] 名称
    ATTACH PARTITION 分区名 { FOR VALUES 分区绑定规范 | DEFAULT }
ALTER TABLE [ IF EXISTS ] 名称
    DETACH PARTITION 分区名

操作可以是下列选项之一

    ADD [ COLUMN ] [ IF NOT EXISTS ] 列名称 数据_类型 [ COLLATE 校对规则 ] [ 列约束 [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] 列名称 [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] 列名称 [ SET DATA ] TYPE 数据_类型 [ COLLATE 校对规则 ] [ USING 表达式 ]
    ALTER [ COLUMN ] 列名称 SET DEFAULT 表达式
    ALTER [ COLUMN ] 列名称 DROP DEFAULT
    ALTER [ COLUMN ] 列名称 { SET | DROP } NOT NULL
    ALTER [ COLUMN ] 列名称 ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( 序列选项 ) ]
    ALTER [ COLUMN ] 列名称 { SET GENERATED { ALWAYS | BY DEFAULT } | SET 序列选项 | RESTART [ [ WITH ] 重新启动后的序列值 ] } [...]
    ALTER [ COLUMN ] 列名称 DROP IDENTITY [ IF EXISTS ]
    ALTER [ COLUMN ] 列名称 SET STATISTICS 整数
    ALTER [ COLUMN ] 列名称 SET ( 属性选项 = 值 [, ... ] )
    ALTER [ COLUMN ] 列名称 RESET ( 属性选项 [, ... ] )
    ALTER [ COLUMN ] 列名称 SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD 表约束 [ NOT VALID ]
    ADD table_constraint_using_index(表约束使用索引)
    ALTER CONSTRAINT 约束名称 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    VALIDATE CONSTRAINT 约束名称
    DROP CONSTRAINT [ IF EXISTS ]  约束名称 [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ 触发器_名称 | ALL | USER ]
    ENABLE TRIGGER [ 触发器_名称 | ALL | USER ]
    ENABLE REPLICA TRIGGER 触发器_名称
    ENABLE ALWAYS TRIGGER 触发器_名称
    DISABLE RULE 重写规则名称
    ENABLE RULE 重写规则名称
    ENABLE REPLICA RULE 重写规则名称
    ENABLE ALWAYS RULE 重写规则名称
    DISABLE ROW LEVEL SECURITY
    ENABLE ROW LEVEL SECURITY
    FORCE ROW LEVEL SECURITY
    NO FORCE ROW LEVEL SECURITY
    CLUSTER ON 索引名称
    SET WITHOUT CLUSTER
    SET WITH OIDS
    SET WITHOUT OIDS
    SET TABLESPACE 新的表空间
    SET { LOGGED | UNLOGGED }
    SET ( 存储参数 [= 值] [, ... ] )
    RESET ( 存储参数 [, ... ] )
    INHERIT 父表
    NO INHERIT 父表
    OF 类型名称
    NOT OF
    OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
    REPLICA IDENTITY { DEFAULT | USING INDEX 索引名称 | FULL | NOTHING }

并且分区绑定的规范是:

IN ( { 数字_文字 | 字符串_文字 | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { 数字_文字 | 字符串_文字 | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
  TO ( { 数字_文字 | 字符串_文字 | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS 数字_文字, REMAINDER 数字_文字 )

并且列的约束是:

[ CONSTRAINT 约束名称 ]
{ NOT NULL |
  NULL |
  CHECK ( 表达式 ) [ NO INHERIT ] |
  DEFAULT 默认_表达式 |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( 序列选项 ) ] |
  UNIQUE 索引参数 |
  PRIMARY KEY 索引参数 |
  REFERENCES 所引用的表 [ ( 所引用的列 ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE 操作 ] [ ON UPDATE 操作 ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

表约束是:

[ CONSTRAINT 约束名称 ]
{ CHECK ( 表达式 ) [ NO INHERIT ] |
  UNIQUE ( 列名称 [, ... ] ) 索引参数 |
  PRIMARY KEY ( 列名称 [, ... ] ) 索引参数 |
  EXCLUDE [ USING 访问索引的方法 ] ( 排除项 WITH 运算子 [, ... ] ) 索引参数 [ WHERE ( 述词 ) ] |
  FOREIGN KEY ( 列名称 [, ... ] ) REFERENCES 所引用的表 [ ( 所引用的列 [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE 操作 ] [ ON UPDATE 操作 ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

table_constraint_using_index 是:

    [ CONSTRAINT 约束名称 ]
    { UNIQUE | PRIMARY KEY } USING INDEX 索引名称
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

在UNIQUE, PRIMARY KEY和EXCLUDE中的索引参数是：

[ INCLUDE ( 列名称 [, ... ] ) ]
[ WITH ( 存储参数 [= 值] [, ... ] ) ]
[ USING INDEX TABLESPACE 表空间的名称 ]

在EXCLUDE约束中的排除项是：

{ 列名称 | ( 表达式 ) } [ 操作符类型的名称 ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]


postgres=# \h dtop table
没有 "dtop table" 的帮助说明.
请尝试用不带参数的 \h 来看一下是否有可使用的帮助信息.
postgres=# 
postgres=# \h drop table
命令：DROP TABLE
描述：移除数据表
语法：
DROP TABLE [ IF EXISTS ] 名称 [, ...] [ CASCADE | RESTRICT ]


## 创建表
create table <tname> (<col1> <datatype>, <col2> <datatype>, ...);
## 创建表指定所属的模式
create table <schemaname>.<tname> (<col1> <datatype>, <col2> <datatype>, ...);
## 创建表指定所属表空间
create table <tname> (<col1> <datatype>, <col2> <datatype>, ...) tablespace <tbsname>;

## 修改表名
alter table <tname> rename to <new_tname>;
# 修改字段名
alter table <tname> rename <colname> to <new_colname>;
## 修改表所属模式
alter table <tname> set schema <new_schema>; 
## 增加字段
alter table <tname> add column <colname> <datatype>;
## 删除字段
alter table <tname> drop column <colname>;

## 删除表
drop table <tname> cascade;

**5. constraint管理：
**

## 示例
## 创建部门表
CREATE TABLE dept (
 deptno numeric(4) NOT NULL PRIMARY KEY, 
 deptname varchar(10), 
 loc text
);

## 创建员工表
CREATE TABLE emp(
 empno numeric(4) NOT NULL,
 ename varchar(10),job varchar(9),
 mgr numeric(4),hiredate date,
 sal numeric(7,2),comm numeric(7,2),
 deptno numeric(2), 
 CONSTRAINT emp_pk PRIMARY KEY (empno), 
 CONSTRAINT emp_ref_dept_fk FOREIGN KEY (deptno) REFERENCES dept (deptno) ON UPDATE NO ACTION ON DELETE NO ACTION, 
 CONSTRAINT emp_sal_ck CHECK (sal > 0)
);

## 添加主键约束
alter table emp add primary key(empno);

## 添加外键约束
## on update cascade: 被引用行更新时，引用行自动更新；
## on update restrict: 被引用的行禁止更新；
## on delete cascade: 被引用行删除时，引用行也一起删除；
## on dellete restrict: 被引用的行禁止删除；
alter table emp add foreign key(deptno) references dept(deptno) ON UPDATE NO ACTION ON DELETE NO ACTION；

##　添加唯一约束
alter table emp add constraint unique_goods_sid unique(empno);

## 删除主键、外键
alter table emp drop constraint <constraint_name>;

**6. index 管理：
**

postgres=# \h create index
命令：CREATE INDEX
描述：建立新的索引
语法：
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] 名称 ] ON [ ONLY ] 表名 [ USING 方法 ]
    ( { 列名称 | ( 表达式 ) } [ COLLATE 校对规则 ] [ 操作符类型的名称 ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( 列名称 [, ...] ) ]
    [ WITH ( 存储参数 [= 值] [, ... ] ) ]
    [ TABLESPACE 表空间的名称 ]
    [ WHERE 述词 ]

postgres=# \h alter index
命令：ALTER INDEX
描述：更改索引的定义
语法：
ALTER INDEX [ IF EXISTS ] 名称 RENAME TO 新的名称
ALTER INDEX [ IF EXISTS ] 名称 SET TABLESPACE 表空间的名称
ALTER INDEX 名称 ATTACH PARTITION 索引名称
ALTER INDEX 名称 DEPENDS ON EXTENSION extension_name(扩展名)
ALTER INDEX [ IF EXISTS ] 名称 SET ( 存储参数 [= 值] [, ... ] )
ALTER INDEX [ IF EXISTS ] 名称 RESET ( 存储参数 [, ... ] )
ALTER INDEX [ IF EXISTS ] 名称 ALTER [ COLUMN ] 列数
    SET STATISTICS 整数
ALTER INDEX ALL IN TABLESPACE 名称 [ OWNED BY 角色名称 [, ... ] ]
    SET TABLESPACE 新的表空间 [ NOWAIT ]

tgres=# \h drop index
命令：DROP INDEX
描述：移除一个索引
语法：
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] 名称 [, ...] [ CASCADE | RESTRICT ]

## 创建索引
create index <idxname> on <tname> using <indexmethod> (<colname>)

## 删除索引
drop index <idxname> cascade;

**7. view管理：
**

postgres=# \h create view
命令：       CREATE VIEW
描述：       建立新的视图
语法：
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW 名称 [ ( 列名称 [, ...] ) ]
    [ WITH ( view_option_name(视图选项名) [= view_option_value(视图选项值)] [, ... ] ) ]
    AS 查询
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

postgres=# \h alter view
命令：       ALTER VIEW
描述：       更改视图的定义
语法：
ALTER VIEW [ IF EXISTS ] 名称 ALTER [ COLUMN ] 列名称 SET DEFAULT 表达式
ALTER VIEW [ IF EXISTS ] 名称 ALTER [ COLUMN ] 列名称 DROP DEFAULT
ALTER VIEW [ IF EXISTS ] 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] 名称 RENAME TO 新的名称
ALTER VIEW [ IF EXISTS ] 名称 SET SCHEMA 新的模式
ALTER VIEW [ IF EXISTS ] 名称 SET ( view_option_name(视图选项名) [= view_option_value(视图选项值)] [, ... ] )
ALTER VIEW [ IF EXISTS ] 名称 RESET ( view_option_name(视图选项名) [, ... ] )

postgres=# \h drop view
命令：       DROP VIEW
描述：       移除一个视图
语法：
DROP VIEW [ IF EXISTS ] 名称 [, ...] [ CASCADE | RESTRICT ]

## 创建视图
create view <vname> as select <colname> from <tname>;

## 修改视图所有者
alter view <vname> owner to <new_owner>;
## 重命名视图
alter view <vname> rename to <new_vname>;
## 修改所属模式
alter view <vname> set schema <new_schemaname>;

## 删除视图
drop view <vname> cascade;

**8. sequence管理：
**

postgres=# \h create sequence
命令：       CREATE SEQUENCE
描述：       建立新的序列数产生器
语法：
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] 名称
    [ AS 数据_类型 ]
    [ INCREMENT [ BY ] 增量 ]
    [ MINVALUE 最小值 | NO MINVALUE ] [ MAXVALUE 最大值 | NO MAXVALUE ]
    [ START [ WITH ] 起始值 ] [ CACHE 缓存 ] [ [ NO ] CYCLE ]
    [ OWNED BY { 表名.列名称 | NONE } ]


postgres=# \h alter sequence
命令：       ALTER SEQUENCE
描述：       更改序列数产生器的定义
语法：
ALTER SEQUENCE [ IF EXISTS ] 名称
    [ AS 数据_类型 ]
    [ INCREMENT [ BY ] 增量 ]
    [ MINVALUE 最小值 | NO MINVALUE ] [ MAXVALUE 最大值 | NO MAXVALUE ]
    [ START [ WITH ] 起始值 ]
    [ RESTART [ [ WITH ] 重新启动后的序列值 ] ]
    [ CACHE 缓存 ] [ [ NO ] CYCLE ]
    [ OWNED BY { 表名.列名称 | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] 名称 RENAME TO 新的名称
ALTER SEQUENCE [ IF EXISTS ] 名称 SET SCHEMA 新的模式

postgres=# \h drop sequence
命令：       DROP SEQUENCE
描述：       移除序列
语法：
DROP SEQUENCE [ IF EXISTS ] 名称 [, ...] [ CASCADE | RESTRICT ]

## 创建序列
CREATE SEQUENCE dept_id_seq;

## 使用序列
INSERT INTO dept(deptno, deptname,loc) VALUES (nextval('dept_id_seq'), 'MARKETING','Shanghai');

## 删除索引
drop sequence <sname>;

**9. function管理：
**

postgres=# \h create function
命令：       CREATE FUNCTION
描述：       建立新的函数
语法：
CREATE [ OR REPLACE ] FUNCTION
    名称 ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [ { DEFAULT | = } 默认_表达式 ] [, ...] ] )
    [ RETURNS 返回类型
      | RETURNS TABLE ( 列名称 列的类型 [, ...] ) ]
  { LANGUAGE 语言名称
    | TRANSFORM { FOR TYPE 类型名称 } [, ... ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST 执行函数的开销
    | ROWS 返回记录的数量
    | SET 配置参数 { TO 值 | = 值 | FROM CURRENT }
    | AS '定义'
    | AS '目标文件', '链接_符号'
  } ...
  

postgres=# \h alter function
命令：       ALTER FUNCTION
描述：       更改函数的定义
语法：
ALTER FUNCTION 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    操作 [ ... ] [ RESTRICT ]
ALTER FUNCTION 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    RENAME TO 新的名称
ALTER FUNCTION 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
ALTER FUNCTION 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    SET SCHEMA 新的模式
ALTER FUNCTION 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    DEPENDS ON EXTENSION extension_name(扩展名)

操作可以是下列选项之一

    CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    IMMUTABLE | STABLE | VOLATILE
    [ NOT ] LEAKPROOF
    [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    PARALLEL { UNSAFE | RESTRICTED | SAFE }
    COST 执行函数的开销
    ROWS 返回记录的数量
    SET 配置参数 { TO | = } { 值 | DEFAULT }
    SET 配置参数 FROM CURRENT
    RESET 配置参数
    RESET ALL

postgres=# \h drop function
命令：       DROP FUNCTION
描述：       移除函数
语法：
DROP FUNCTION [ IF EXISTS ] 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ] [, ...]
    [ CASCADE | RESTRICT ]
    
#### 示例:
--创建函数
create function func_1(int,int) returns int as $$
declare
result int;
begin
result = $1 + $2;
return result;
end;
$$ language 'plpgsql';

## 调用函数
select func_1(5,3);

## 查看函数
select * from pg_proc where proname = 'func_1';
## 查看用户自定义函数
SELECT n.nspname AS "Schema",
       p.proname AS "Name",
       pg_get_functiondef(p.oid) AS "Definition"
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT LIKE 'pg_%'
  AND n.nspname NOT LIKE 'information_schema%'
  AND n.nspname NOT LIKE 'sql_%'
ORDER BY n.nspname, p.proname;

## 修改函数
alter function func_1(int,int) owner to <new_owner>; 

## 删除函数
drop function <function_name> cascade;

**10. procedure管理：
**

postgres=# \h create procedure
命令：       CREATE PROCEDURE
描述：       建立新的程序
语法：
CREATE [ OR REPLACE ] PROCEDURE
    名称 ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [ { DEFAULT | = } 默认_表达式 ] [, ...] ] )
  { LANGUAGE 语言名称
    | TRANSFORM { FOR TYPE 类型名称 } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET 配置参数 { TO 值 | = 值 | FROM CURRENT }
    | AS '定义'
    | AS '目标文件', '链接_符号'
  } ...

postgres=# \h alter procedure
命令：       ALTER PROCEDURE
描述：       更改程序的定义
语法：
ALTER PROCEDURE 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    操作 [ ... ] [ RESTRICT ]
ALTER PROCEDURE 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    RENAME TO 新的名称
ALTER PROCEDURE 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
ALTER PROCEDURE 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    SET SCHEMA 新的模式
ALTER PROCEDURE 名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ]
    DEPENDS ON EXTENSION extension_name(扩展名)

操作可以是下列选项之一

    [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    SET 配置参数 { TO | = } { 值 | DEFAULT }
    SET 配置参数 FROM CURRENT
    RESET 配置参数
    RESET ALL

postgres=# \h drop sequence
命令：       DROP SEQUENCE
描述：       移除序列
语法：
DROP SEQUENCE [ IF EXISTS ] 名称 [, ...] [ CASCADE | RESTRICT ]


#### 示例
## 创建存储过程
create procedure proc_1(in a int, inout b int, inout c int) language plpgsql 
as $$
declare
begin
 b := a+b;
 c := b*b;
end;
$$;

## 调用存储过程
call proc_1(2,3,4);

## 查看存储过程
SELECT n.nspname AS "Schema",
       p.proname AS "Name",
       pg_get_functiondef(p.oid) AS "Definition"
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT LIKE 'pg_%'
  AND n.nspname NOT LIKE 'information_schema%'
  AND n.nspname NOT LIKE 'sql_%'
ORDER BY n.nspname, p.proname;

## 删除存储过程
drop procedure <procdure_name> cascade;

**11. 触发器
**

postgres=# \h create trigger
命令：       CREATE TRIGGER
描述：       建立新的触发器
语法：
CREATE [ CONSTRAINT ] TRIGGER 名称 { BEFORE | AFTER | INSTEAD OF } { 事件 [ OR ... ] }
    ON 表名
    [ FROM 被引用表的名称 ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name(转换关系名) } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( 条件 ) ]
    EXECUTE { FUNCTION | PROCEDURE } 函数名称 ( 参数 )

事件可以下述之一:

    INSERT
    UPDATE [ OF 列名称 [, ... ] ]
    DELETE
    TRUNCATE


postgres=# \h alter trigger
命令：       ALTER TRIGGER
描述：       更改触发器的定义
语法：
ALTER TRIGGER 名称 ON 表名 RENAME TO 新的名称
ALTER TRIGGER 名称 ON 表名 DEPENDS ON EXTENSION extension_name(扩展名)
postgres=# \h drop trigger
命令：       DROP TRIGGER
描述：       移除触发器
语法：
DROP TRIGGER [ IF EXISTS ] 名称 ON 表名 [ CASCADE | RESTRICT ]

#### 示例:
## 创建触发器函数
CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS 
$example_table$
 BEGIN
 INSERT INTO EMP_AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
 RETURN NEW;
 END;
$example_table$ LANGUAGE plpgsql; 

## 创建触发器
CREATE TRIGGER example_trigger AFTER INSERT ON EMP FOR EACH ROW EXECUTE PROCEDURE auditlogfunc(); --插入记录，验证触发器
INSERT INTO EMP(ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
select * from EMP_AUDIT;

## 查看触发器
select tgrelid::regclass as reltable,tgname as trigger_name from pg_trigger;
## 修改触发器
alter trigger <triggername> on <tname> rename to <new_triggername>; 
## 删除触发器
drop trigger <triggername> on <tname> cascade;