pgsql基本操作

pgsql基本操作

一.关于系统表

pg_class

记录了数据库中的表，索引，序列，视图（"关系"）。 其中比较重要字段有：

• relname 表，索引，视图等的名字。

• relnamespace 包含这个关系的名字空间（模式）的 OID，对应pg_namespace.oid

• relkind r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表

pg_namespace

记录了数据库的名字空间（模式） 其中比较重要的字段有：

• nspname 名字空间的名字

• nspowner 名字空间的所有者

pg_attribute

记录了数据库关于表的字段的信息。 其中比较重要的字段有：

• attrelid 此列/字段所属的表，对应于pg_class.oid

• attname 字段名字

• atttypid 这个字段的数据类型,对应于pg_type.oid

• attlen 对于定长类型，typlen是该类型内部表现形式的字节数目。 对于变长类型，typlen 是负数。 -1 表示一种"变长"类型（有长度字属性的数据）， -2 表示这是一个 NULL 结尾的 C 字串。是本字段类型 pg_type.typlen 的拷贝。

• attnum 字段数目。普通字段是从 1 开始计数的。系统字段， 比如 oid， 有（任意）正数。

• atttypmod atttypmod 元组在创建表的时候 提供的类型相关的数据（比如，一个 varchar 字段的最大长度）。 它传递给类型相关的输入和长度转换函数当做第三个参数。 其值对那些不需要 atttypmod 的类型而言通常为 -1。

• attnotnull 这代表一个非空约束。我们可以改变这个字段以打开或者关闭这个约束。

• attisdropped 这个字段已经被删除了，不再有效。

注意：

1. 如果字段类型为变长类型（如varchar）,那么在atttypmod中存储的长度比实际长度多4。可见参考文档1。

2. 如果字段类型为numeric，那么可通过atttypmod获得长度、精度等信息，具体方式可见参考文档2。

pg_type

记录了数据库有关数据类型的信息。 其中比较重要的字段有：

• typname 数据类型名字

• typlen 对于定长类型，typlen是该类型内部表现形式的字节数目。 对于变长类型，typlen 是负数。 -1 表示一种"变长"类型（有长度字属性的数据）， -2 表示这是一个 NULL 结尾的 C 字串。

pg_description

记录了数据库中对象（表、字段等）的注释。 其中比较重要的字段有：

• objoid 这条描述所描述的对象的 OID。如果这条注释是一个表或表中字段的注释，那么，该值对应于pg_class.oid

• objsubid 对于一个表字段的注释，它是字段号，对应于pg_attribute.attnum。对于其它对象类型，它是零。

• description 作为对该对象的描述的任意文本

查询用户表

SELECT a.oid,
       a.relname AS name,
       b.description AS comment
  FROM pg_class a
       LEFT OUTER JOIN pg_description b ON b.objsubid=0 AND a.oid = b.objoid
 WHERE a.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public') --用户表一般存储在public模式下
   AND a.relkind='r'
 ORDER BY a.relname

使用表名查询表字段的定义

SELECT a.attnum,
       a.attname AS field,
       t.typname AS type,
       a.attlen AS length,
       a.atttypmod AS lengthvar,
       a.attnotnull AS notnull,
       b.description AS comment
  FROM pg_class c,
       pg_attribute a
       LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
       pg_type t
 WHERE c.relname = 'zc_zclx'
       and a.attnum > 0
       and a.attrelid = c.oid
       and a.atttypid = t.oid
 ORDER BY a.attnum

使用表oid查询表字段的定义

SELECT a.attname AS field,
       t.typname AS type,
       a.attlen AS length,
       a.atttypmod AS lengthvar,
       a.attnotnull AS notnull,
       b.description AS comment
  FROM pg_attribute a 
       LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
       pg_type t
 WHERE a.attnum > 0
       and a.attrelid = 162903
       and a.atttypid = t.oid
 ORDER BY a.attnum

二.PostgreSQL 模式（SCHEMA）

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

• 允许多个用户使用一个数据库并且不会互相干扰。

• 将数据库对象组织成逻辑组以便更容易管理。

• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套。

语法

我们可以使用 CREATE SCHEMA 语句来创建模式，语法格式如下：

CREATE SCHEMA myschema.mytable (
...
);

实例

接下来我们连接到 runoobdb 来创建模式 myschema：

runoobdb=# create schema myschema;
CREATE SCHEMA

输出结果 "CREATE SCHEMA" 就代表模式创建成功。

接下来我们再创建一个表格：

runoobdb=# create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以上命令创建了一个空的表格，我们使用以下 SQL 来查看表格是否创建：

runoobdb=# select * from myschema.company;
 id | name | age | address | salary 
----+------+-----+---------+--------
(0 rows)

删除模式

删除一个为空的模式（其中的所有对象已经被删除）：

DROP SCHEMA myschema;

删除一个模式以及其中包含的所有对象：

DROP SCHEMA myschema CASCADE;

三.PostgreSQL PRIVILEGES（权限）

无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。

对于大多数类型的对象，初始状态是只有所有者(或超级用户)才能修改或删除对象。要允许其他角色或用户使用它，必须为该用户设置权限。

在 PostgreSQL 中，权限分为以下几种：

• SELECT

• INSERT

• UPDATE

• DELETE

• TRUNCATE

• REFERENCES

• TRIGGER

• CREATE

• CONNECT

• TEMPORARY

• EXECUTE

• USAGE

根据对象的类型(表、函数等)，将指定权限应用于该对象。

要向用户分配权限，可以使用 GRANT 命令。

GRANT 语法

GRANT 命令的基本语法如下：

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

• privilege − 值可以为：SELECT，INSERT，UPDATE，DELETE， RULE，ALL。

• object − 要授予访问权限的对象名称。可能的对象有： table， view，sequence。

• PUBLIC − 表示所有用户。

• GROUP group − 为用户组授予权限。

• username − 要授予权限的用户名。PUBLIC 是代表所有用户的简短形式。

另外，我们可以使用 REVOKE 命令取消权限，REVOKE 语法：

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

实例

为了理解权限，创建一个用户：

runoobdb=# CREATE USER runoob WITH PASSWORD 'password';
CREATE ROLE

信息 CREATE ROLE 表示创建了一个用户 "runoob"。

实例

创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：

runoobdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

现在给用户 "runoob" 分配权限：

runoobdb=# GRANT ALL ON COMPANY TO runoob;
GRANT

信息 GRANT 表示所有权限已经分配给了 "runoob"。

下面撤销用户 "runoob" 的权限：

runoobdb=# REVOKE ALL ON COMPANY FROM runoob;
REVOKE

信息 REVOKE 表示已经将用户的权限撤销。

你也可以删除用户：

runoobdb=# DROP USER runoob;
DROP ROLE

信息 DROP ROLE 表示用户 "runoob" 已经从数据库中删除。

四.PostgreSQL AUTO INCREMENT（自动增长）

AUTO INCREMENT（自动增长） 会在新记录插入表中时生成一个唯一的数字。

PostgreSQL 使用序列来标识字段的自增长，数据类型有 smallserial、serial 和 bigserial 。这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。

使用 MySQL 设置自动增长的语句如下:

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL 是用 AUTO_INCREMENT 这个属性来标识字段的自增。

PostgreSQL 使用序列来标识字段的自增长：

CREATE TABLE runoob
(
    id serial NOT NULL,
    alttext text,
    imgurl text
)

SMALLSERIAL、SERIAL 和 BIGSERIAL 范围：

伪类型	存储大小	范围
SMALLSERIAL	2字节	1 到 32,767
SERIAL	4字节	1 到 2,147,483,647
BIGSERIAL	8字节	1 到 922,337,2036,854,775,807

语法

SERIAL 数据类型基础语法如下：

CREATE TABLE tablename (
   colname SERIAL
);

实例

假定我们要创建一张 COMPANY 表，并创建下面几个字段：

runoobdb=# CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

现在往表中插入几条记录：

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );
​
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );
​
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );
​
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
​
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );
​
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
​
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

查看 COMPANY 表的记录如下：

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000