返回
基础
分类

最近准备下PostgreSQL数据库开发的相关知识,表都是数据存储的最核心、最基础的对象单元

日期: 2020-01-25 01:44 浏览次数 : 125

一、表的定义:

一、表的定义:

最近准备下PostgreSQL数据库开发的相关知识,本文把总结的PPT内容通过博客记录分享,本随笔的主要内容是介绍PostgreSQL数据库的基础信息,以及如何在我们的开发框架中使用PostgreSQL数据库,希望大家多多提意见。

对于任何一种关系型数据库而言,表都是数据存储的最核心、最基础的对象单元。现在就让我们从这里起步吧。 1. 创建表:复制代码 代码如下: CREATE TABLE products ( product_no integer, name text, price numeric ); 2. 删除表:复制代码 代码如下: DROP TABLE products; 3. 创建带有缺省值的表:复制代码 代码如下: CREATE TABLE products ( product_no integer, name text, price numeric DEFAULT 9.99 --DEFAULT是关键字,其后的数值9.99是字段price的默认值。 ); CREATE TABLE products ( product_no SERIAL, --SERIAL类型的字段表示该字段为自增字段,完全等同于Oracle中的Sequence。 name text, price numeric DEFAULT 9.99 ); 输出为:复制代码 代码如下: NOTICE: CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no" 4. 约束: 检查约束是表中最为常见的约束类型,它允许你声明在某个字段里的数值必须满足一个布尔表达式。不仅如此,我们也可以声明表级别的检查约束。复制代码 代码如下: CREATE TABLE products ( product_no integer, name text, --price字段的值必须大于0,否则在插入或修改该字段值是,将引发违规错误。还需要说明的是,该检查约束 --是匿名约束,即在表定义时没有显示命名该约束,这样PostgreSQL将会根据当前的表名、字段名和约束类型, --为该约束自动命名,如:products_price_check。 price numeric CHECK (price 0) ); CREATE TABLE products ( product_no integer, name text, --该字段的检查约束被显示命名为positive_price。这样做好处在于今后维护该约束时,可以根据该名进行直接操作。 price numeric CONSTRAINT positive_price CHECK (price 0) ); 下面的约束是非空约束,即约束的字段不能插入空值,或者是将已有数据更新为空值。复制代码 代码如下: CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric ); 如果一个字段中存在多个约束,在定义时可以不用考虑约束的声明顺序。复制代码 代码如下: CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price 0) ); 唯一性约束,即指定的字段不能插入重复值,或者是将某一记录的值更新为当前表中的已有值。复制代码 代码如下: CREATE TABLE products ( product_no integer UNIQUE, name text, price numeric );

    对于任何一种关系型数据库而言,表都是数据存储的最核心、最基础的对象单元。现在就让我们从这里起步吧。
    1. 创建表:
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric
    );
    
    2. 删除表:
    DROP TABLE products;
    
    3. 创建带有缺省值的表:
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric DEFAULT 9.99 --DEFAULT是关键字,其后的数值9.99是字段price的默认值。
    );
    
    CREATE TABLE products (
        product_no SERIAL,            --SERIAL类型的字段表示该字段为自增字段,完全等同于Oracle中的Sequence
        name text,
        price numeric DEFAULT 9.99
    );
    输出为:
    NOTICE:  CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no"
   
    4. 约束:
    检查约束是表中最为常见的约束类型,它允许你声明在某个字段里的数值必须满足一个布尔表达式。不仅如此,我们也可以声明表级别的检查约束。
    CREATE TABLE products (
        product_no integer,
        name text,
        --price字段的值必须大于0,否则在插入或修改该字段值是,将引发违规错误。还需要说明的是,该检查约束
        --是匿名约束,即在表定义时没有显示命名该约束,这样PostgreSQL将会根据当前的表名、字段名和约束类型,
        --为该约束自动命名,如:products_price_check。
        price numeric CHECK (price > 0)
    );
 
    CREATE TABLE products (
        product_no integer,
        name text,
        --该字段的检查约束被显示命名为positive_price。这样做好处在于今后维护该约束时,可以根据该名进行直接操作。
        price numeric CONSTRAINT positive_price CHECK (price > 0)
    );
    下面的约束是非空约束,即约束的字段不能插入空值,或者是将已有数据更新为空值。
    CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric
    );
    如果一个字段中存在多个约束,在定义时可以不用考虑约束的声明顺序。
    CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric NOT NULL CHECK (price > 0)
    );
    唯一性约束,即指定的字段不能插入重复值,或者是将某一记录的值更新为当前表中的已有值。
    CREATE TABLE products (
        product_no integer UNIQUE,
        name text,
        price numeric
    );

1、PostgreSQL数据库介绍

PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES,现在已经更名为PostgreSQL。它具有很多不错的特点:

开源:PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统),它在灵活的 BSD-风格许可证下发行。

兼容性好:PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99。

可靠性高:PostgreSQL标榜自己是世界上最先进的开源数据库。可靠性是PostgreSQL的最高优先级。它以坚如磐石的品质和良好的工程化而闻名,支持高事务、任务关键型应用。

广泛采用:自从MySQL被Oracle收购以后,PostgreSQL逐渐成为开源关系型数据库的首选,在功能上是全面超越MySQL的一个开源数据库。

良好支持:PostgreSQL对接口的支持也是非常丰富的,几乎支持所有类型的数据库客户端接;最丰富的数据类型的支持,其中有些数据类型可以说连商 业数据库都不具备,比如IP类型和几何类型等;

CREATE TABLE products ( product_no integer, name text, price numeric, UNIQUE (product_no) ); 为表中的多个字段定义联合唯一性。复制代码 代码如下: CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) ); 为唯一性约束命名。复制代码 代码如下: CREATE TABLE products ( product_no integer CONSTRAINT must_be_different UNIQUE, name text, price numeric ); 在插入数据时,空值(NULL)之间被视为不相等的数据,因此对于某一唯一性字段,可以多次插入空值。然而需要注意的是,这一规则并不是被所有数据库都遵守,因此在进行数据库移植时可能会造成一定的麻烦。 5. 主键和外键: 从技术上来讲,主键约束只是唯一约束和非空约束的组合。复制代码 代码如下: CREATE TABLE products ( product_no integer PRIMARY KEY, --字段product_no被定义为该表的唯一主键。 name text, price numeric ); 和唯一性约束一样,主键可以同时作用于多个字段,形成联合主键:复制代码 代码如下: CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (b, c) ); 外键约束声明一个字段的数值必须匹配另外一个表中某些行出现的数值。 我们把这个行为称做两个相关表之间的参考完整性。复制代码 代码如下: CREATE TABLE orders ( order_id integer PRIMARY KEY, --该表也可以有自己的主键。 --该表的product_no字段为上面products表主键(product_no)的外键。 product_no integer REFERENCES products(product_no), quantity integer ); CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, --该外键的字段数量和被引用表中主键的数量必须保持一致。 FOREIGN KEY (b, c) REFERENCES example (b, c) ); 当多个表之间存在了主外键的参考性约束关系时,如果想删除被应用表(主键表)中的某行记录,由于该行记录的主键字段值可能正在被其引用表(外键表)中某条记录所关联,所以删除操作将会失败。如果想完成此操作,一个显而易见的方法是先删除引用表中和该记录关联的行,之后再删除被引用表中的该行记录。然而需要说明的是,PostgreSQL为我们提供了更为方便的方式完成此类操作。复制代码 代码如下: CREATE TABLE products ( product_最近准备下PostgreSQL数据库开发的相关知识,表都是数据存储的最核心、最基础的对象单元。no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text ); CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, --限制选项 order_id integer REFERENCES orders ON DELETE CASCADE, --级联删除选项 quantity integer, PRIMARY KEY (product_no, order_id) ); 限制和级联删除是两种最常见的选项。RESTRICT 禁止删除被引用的行。 NO ACTION 的意思是如果在检查约束的时候,如果还存在任何引用行,则抛出错误; 如果你不声明任何东西,那么它就是缺省的行为。(这两个选择的实际区别是,NO ACTION 允许约束检查推迟到事务的晚些时候,而 RESTRICT 不行。) CASCADE声明在删除一个被引用的行的时候,引用它的行也会被自动删除掉。 在外键字段上的动作还有两个选项: SET NULL 和 SET DEFAULT。 这样会导致在被引用行删除的时候,引用它们的字段分别设置为空或者缺省值。 请注意这些选项并不能让你逃脱被观察和约束的境地。比如,如果一个动作声明 SET DEFAULT,但是缺省值并不能满足外键,那么动作就会失败。类似ON DELETE,还有ON UPDATE 选项,它是在被引用字段修改(更新)的时候调用的。可用的动作是一样的。

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric,
        UNIQUE (product_no)
    );
    为表中的多个字段定义联合唯一性。
    CREATE TABLE example (
        a integer,
        b integer,
        c integer,
        UNIQUE (a, c)
    );
    为唯一性约束命名。
    CREATE TABLE products (
        product_no integer CONSTRAINT must_be_different UNIQUE,
        name text,
        price numeric
    );
    在插入数据时,空值(NULL)之间被视为不相等的数据,因此对于某一唯一性字段,可以多次插入空值。然而需要注意的是,这一规则并不是被所有数据库都遵守,因此在进行数据库移植时可能会造成一定的麻烦。
必赢手机登录网址 ,   
    5. 主键和外键:
    从技术上来讲,主键约束只是唯一约束和非空约束的组合。
    CREATE TABLE products (
        product_no integer PRIMARY KEY--字段product_no被定义为该表的唯一主键。
        name text,
        price numeric
    );
    和唯一性约束一样,主键可以同时作用于多个字段,形成联合主键:
    CREATE TABLE example (
        a integer,
        b integer,
        c integer,
        PRIMARY KEY (b, c)
    );
    外键约束声明一个字段(或者一组字段)的数值必须匹配另外一个表中某些行出现的数值。 我们把这个行为称做两个相关表之间的参考完整性。
    CREATE TABLE orders (
        order_id integer PRIMARY KEY, --该表也可以有自己的主键。
        --该表的product_no字段为上面products表主键(product_no)的外键。
        product_no integer REFERENCES products(product_no),
        quantity integer
    );
   
    CREATE TABLE t1 (
        a integer PRIMARY KEY,
        b integer,
        c integer,
        --该外键的字段数量和被引用表中主键的数量必须保持一致。
        FOREIGN KEY (b, c) REFERENCES example (b, c)
    );  
    当多个表之间存在了主外键的参考性约束关系时,如果想删除被应用表(主键表)中的某行记录,由于该行记录的主键字段值可能正在被其引用表(外键表)中某条记录所关联,所以删除操作将会失败。如果想完成此操作,一个显而易见的方法是先删除引用表中和该记录关联的行,之后再删除被引用表中的该行记录。然而需要说明的是,PostgreSQL为我们提供了更为方便的方式完成此类操作。
    CREATE TABLE products (
        product_no integer PRIMARY KEY,
        name text,
        price numeric
    );
    
    CREATE TABLE orders (
        order_id integer PRIMARY KEY,
        shipping_address text
    );
    
    CREATE TABLE order_items (
        product_no integer REFERENCES products ON DELETE RESTRICT, --限制选项
        order_id integer REFERENCES orders ON DELETE CASCADE, --级联删除选项
        quantity integer,
        PRIMARY KEY (product_no, order_id)
    );
    限制和级联删除是两种最常见的选项。RESTRICT 禁止删除被引用的行。 NO ACTION 的意思是如果在检查约束的时候,如果还存在任何引用行,则抛出错误; 如果你不声明任何东西,那么它就是缺省的行为。(这两个选择的实际区别是,NO ACTION 允许约束检查推迟到事务的晚些时候,而 RESTRICT 不行。) CASCADE声明在删除一个被引用的行的时候,引用它的行也会被自动删除掉。 在外键字段上的动作还有两个选项: SET NULL 和 SET DEFAULT。 这样会导致在被引用行删除的时候,引用它们的字段分别设置为空或者缺省值。 请注意这些选项并不能让你逃脱被观察和约束的境地。比如,如果一个动作声明 SET DEFAULT,但是缺省值并不能满足外键,那么动作就会失败。类似ON DELETE,还有ON UPDATE 选项,它是在被引用字段修改(更新)的时候调用的。可用的动作是一样的。

2、安装数据库及管理工具

数据库安装
PostgreSQL数据库(

数据库管理工具
Navicat for PostgreSQL (如V11.1)

数据库建模工具
PowerDesigner (如V16.5)

 

数据库操作:

1)安装PostgreSQL数据库
2)安装Navicat for PostgreSQL管理数据库工具

必赢手机登录网址 1

3)安装PowerDesigner建模工具

必赢手机登录网址 2

4)在PowerDesigner创建一个简单的表,获取表SQL,然后在Navicat 中执行脚本创建表,插入数据等。

二、系统字段:

二、系统字段:

3、PostgreSQL数据库语法

常规处理SQL,和其他数据库相同,如创建、删除表等常规操作。下面从网络上摘录一些关于PostgreSQL的常见操作。

1)表的定义:

  1. 创建表:

    CREATE TABLE products ( product_no integer, name text, price numeric );

  2. 删除表:

    DROP TABLE products;

  3. 创建带有缺省值的表:

    CREATE TABLE products ( product_no integer, name text, price numeric DEFAULT 9.99 --DEFAULT是关键字,其后的数值9.99是字段price的默认值。 );

    CREATE TABLE products ( product_no SERIAL, --SERIAL类型的字段表示该字段为自增字段,完全等同于Oracle中的Sequence。 name text, price numeric DEFAULT 9.99 );

  4. 约束:
    检查约束是表中最为常见的约束类型,它允许你声明在某个字段里的数值必须满足一个布尔表达式。不仅如此,我们也可以声明表级别的检查约束。

    CREATE TABLE products ( product_no integer, name text, --price字段的值必须大于0,否则在插入或修改该字段值是,将引发违规错误。还需要说明的是,该检查约束 --是匿名约束,即在表定义时没有显示命名该约束,这样PostgreSQL将会根据当前的表名、字段名和约束类型, --为该约束自动命名,如:products_price_check。 price numeric CHECK (price > 0) );

    CREATE TABLE products ( product_no integer, name text, --该字段的检查约束被显式命名为positive_price。这样做的好处在于今后维护该约束时,可以根据该名进行直接操作。 price numeric CONSTRAINT positive_price CHECK (price > 0) );

下面的约束是非空约束,即约束的字段不能插入空值,或者是将已有数据更新为空值。

CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
  1. 主键和外键:
    从技术上来讲,主键约束只是唯一约束和非空约束的组合。

    CREATE TABLE products ( product_no integer PRIMARY KEY, --字段product_no被定义为该表的唯一主键。 name text, price numeric );

和唯一性约束一样,主键可以同时作用于多个字段,形成联合主键:

CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (b, c)
);

外键约束声明一个字段(或者一组字段)的数值必须匹配另外一个表中某些行出现的数值。 我们把这个行为称做两个相关表之间的参考完整性。

CREATE TABLE orders (
order_id integer PRIMARY KEY, --该表也可以有自己的主键。
--该表的product_no字段为上面products表主键(product_no)的外键。
product_no integer REFERENCES products(product_no), 
quantity integer
);

当多个表之间存在了主外键的参考性约束关系时,如果想删除被引用表(主键表)中的某行记录,由于该行记录的主键字段值可能正在被其引用表(外键表)中某条记录所关联,所以删除操作将会失败。如果想完成此操作,一个显而易见的方法是先删除引用表中和该记录关联的行,之后再删除被引用表中的该行记录。然而需要说明的是,PostgreSQL为我们提供了更为方便的方式完成此类操作。

CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);

CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text
);

 CREATE TABLE order_items (
        product_no integer REFERENCES products ON DELETE RESTRICT, --限制选项
        order_id integer REFERENCES orders ON DELETE CASCADE, --级联删除选项
        quantity integer,
        PRIMARY KEY (product_no, order_id)
    );

限制和级联删除是两种最常见的选项。RESTRICT 禁止删除被引用的行。 NO ACTION 的意思是如果在检查约束的时候,如果还存在任何引用行,则抛出错误; 如果你不声明任何东西,那么它就是缺省的行为。(这两个选择的实际区别是,NO ACTION 允许约束检查推迟到事务的晚些时候,而 RESTRICT 不行。) CASCADE声明在删除一个被引用的行的时候,引用它的行也会被自动删除掉。 在外键字段上的动作还有两个选项: SET NULL 和 SET DEFAULT。 这样会导致在被引用行删除的时候,引用它们的字段分别设置为空或者缺省值。 请注意这些选项并不能让你逃脱被观察和约束的境地。比如,如果一个动作声明 SET DEFAULT,但是缺省值并不能满足外键,那么动作就会失败。类似ON DELETE,还有ON UPDATE 选项,它是在被引用字段修改(更新)的时候调用的。可用的动作是一样的。

2)表的修改:

  1. 增加字段:

    ALTER TABLE products ADD COLUMN description text;

新增的字段对于表中已经存在的行而言最初将先填充所给出的缺省值(如果你没有声明DEFAULT子句,那么缺省是空值)。

在新增字段时,可以同时给该字段指定约束。

ALTER TABLE products ADD COLUMN description text CHECK(description <> '');
  1. 删除字段:

    ALTER TABLE products DROP COLUMN description;

如果该表为被引用表,该字段为被引用字段,那么上面的删除操作将会失败。如果要想在删除被引用字段的同时级联的删除其所有引用字段,可以采用下面的语法形式。

ALTER TABLE products DROP COLUMN description CASCADE;
  1. 增加约束:

    ALTER TABLE products ADD CHECK(name <> ''); --增加一个表级约束 ALTER TABLE products ADD CONSTRAINT same_name UNIQUE(product_no);--增加命名的唯一性约束。 ALTER TABLE products ADD FOREIGN KEY(pdt_grp_id) REFERENCES pdt_grps; --增加外键约束。 ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; --增加一个非空约束。

  2. 删除约束:

    ALTER TABLE products DROP CONSTRAINT some_name;

对于显示命名的约束,可以根据其名称直接删除,对于隐式自动命名的约束,可以通过psql的d tablename来获取该约束的名字。和删除字段一样,如果你想删除有着被依赖关系的约束,你需要用CASCADE。一个例子是某个外键约束依赖被引用字段上的唯一约束或者主键约束。

3)权限:
只有表的所有者才能修改或者删除表的权限。要赋予一个权限,我们使用GRANT命令,要撤销一个权限,使用REVOKE命令。
需要指出的是,PUBLIC是特殊"用户"可以用于将权限赋予系统中的每一个用户。在声明权限的位置写ALL则将所有的与该对象类型相关的权限都赋予出去。

GRANT UPDATE ON table_name TO user; --将表的更新权限赋予指定的user。
GRANT SELECT ON table_name TO GROUP group; --将表的select权限赋予指定的组。
REVOKE ALL ON table_name FROM PUBLIC; --将表的所有权限从Public撤销。

最初,只有对象所有者(或者超级用户)可以赋予或者撤销对象的权限。但是,我们可以赋予一个"with grant option"权限,这样就给接受权限的人以授予该权限给其它人的权限。如果授予选项后来被撤销,那么所有那些从这个接受者接受了权限的用户(直接或者通过级连的授权)都将失去该权限。

PostgreSQL的每个数据表中都包含几个隐含定义的系统字段。因此,这些名字不能用于用户定义的字段名。这些系统字段的功能有些类似于Oracle中的rownum和rowid等。 oid: 行的对象标识符(对象ID)。这个字段只有在创建表的时候使用了WITH OIDS,或者是设置了配置参数default_with_oids时出现。这个字段的类型是oid(和字段同名)。 tableoid: 包含本行的表的OID。这个字段对那些从继承层次中选取的查询特别有用,因为如果没有它的话,我们就很难说明一行来自哪个独立的表。tableoid可以和pg_class的oid字段连接起来获取表名字。 xmin: 插入该行版本的事务的标识(事务ID)。 cmin: 在插入事务内部的命令标识(从零开始)。 xmax: 删除事务的标识(事务ID),如果不是被删除的行版本,那么是零。 cmax: 在删除事务内部的命令标识符,或者是零。 ctid: 一个行版本在它所处的表内的物理位置。请注意,尽管ctid可以用于非常快速地定位行版本,但每次VACUUM FULL之后,一个行的ctid都会被更新或者移动。因此ctid是不能作为长期的行标识符的。 OID是32位的量,是在同一个集群内通用的计数器上赋值的。对于一个大型或者长时间使用的数据库,这个计数器是有可能重叠的。因此,假设OID是唯一的是非常错误的,除非你自己采取了措施来保证它们是唯一的。如果你需要标识表中的行,我们强烈建议使用序列号生成器。 三、表的修改:

    PostgreSQL的每个数据表中都包含几个隐含定义的系统字段。因此,这些名字不能用于用户定义的字段名。这些系统字段的功能有些类似于Oracle中的rownum和rowid等。
    oid: 行的对象标识符(对象ID)。这个字段只有在创建表的时候使用了WITH OIDS,或者是设置了配置参数default_with_oids时出现。这个字段的类型是oid(和字段同名)。
    tableoid: 包含本行的表的OID。这个字段对那些从继承层次中选取的查询特别有用,因为如果没有它的话,我们就很难说明一行来自哪个独立的表。tableoid可以和pg_class的oid字段连接起来获取表名字。
    xmin: 插入该行版本的事务的标识(事务ID)。
    cmin: 在插入事务内部的命令标识(从零开始)。
    xmax: 删除事务的标识(事务ID),如果不是被删除的行版本,那么是零。
    cmax: 在删除事务内部的命令标识符,或者是零。
    ctid: 一个行版本在它所处的表内的物理位置。请注意,尽管ctid可以用于非常快速地定位行版本,但每次VACUUM FULL之后,一个行的ctid都会被更新或者移动。因此ctid是不能作为长期的行标识符的。   
    OID是32位的量,是在同一个集群内通用的计数器上赋值的。对于一个大型或者长时间使用的数据库,这个计数器是有可能重叠的。因此,假设OID是唯一的是非常错误的,除非你自己采取了措施来保证它们是唯一的。如果你需要标识表中的行,我们强烈建议使用序列号生成器。     
   
三、表的修改:

4、数据库语法差异部分

1)特殊字段对应关系(部分)

必赢手机登录网址 3

2)分页处理
MySQL分页常见写法

select * from persons  limit 0,10;

起点位置为0,查询10条记录。

兼容MySQL与postgreSQL的写法

select * from persons limit 10 offset 0

从起点0开始查询,返回10条记录

 

3)其他类型
Guid生成(SQLServer 为newid() 函数)

PostgreSQL从9.1版本开始,执行下面可以创建guid的函数

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
select uuid_generate_v4()

必赢手机登录网址 4

 

 

SQL限定符
Sqlserver为[],如Select [Name] from [User]
PostgreSQL为“”,如 Select “Name” from User

自增长标识
SQLserver为 identity(1,1)
PostgreSQL使用SERIAL代替
SERIAL 对象的是int4类型(默认),bigserial类型对应int8类型
一个serial类型创建的序列在其所属字段被删除时,该序列也将被自动删除

必赢手机登录网址 5

函数
string || string
'Post' || 'greSQL‘
lower(string)
lower('TOM')
upper(string)
upper('tom')
substring(string [from int] [for int])
substring('Thomas' from 2 for 3)
to_char(timestamp, text)
to_char(current_timestamp, 'HH12:MI:SS')
to_timestamp(text, text)
to_timestamp('2016-10-29 12:27:05', 'YYYY-MM-DD HH24:MI:SS')

...

current_date 今天的日期
current_time 现在的时间
current_timestamp 现在的日期和时间
Localtime 今日的时间
Localtimestamp日期和时间
now() 当前的日期和时间(等效于 current_timestamp)

 

序列操作函数
Nextval、currval、lastval、setval
其他函数
NULLIF :当且仅当value1和value2相等时,NULLIF才返回NULL。否则它返回value1。
GREATEST和LEAST :GREATEST和LEAST函数从一个任意的数字表达式列表里选取最大或者最小的数值。

系统信息函数
current_database()当前数据库的名字
current_schema()当前模式的名字
current_user目前执行环境下的用户名
version() PostgreSQL版本信息
……

1. 增加字段:复制代码 代码如下: ALTER TABLE products ADD COLUMN description text; 新增的字段对于表中已经存在的行而言最初将先填充所给出的缺省值(如果你没有声明DEFAULT子句,那么缺省是空值)。 在新增字段时,可以同时给该字段指定约束。复制代码 代码如下: ALTER TABLE products ADD COLUMN description text CHECK(description ''); 2. 删除字段:复制代码 代码如下: ALTER TABLE products DROP COLUMN description; 如果该表为被引用表,该字段为被引用字段,那么上面的删除操作将会失败。如果要想在删除被引用字段的同时级联的删除其所有引用字段,可以采用下面的语法形式。复制代码 代码如下: ALTER TABLE products DROP COLUMN description CASCADE; 3. 增加约束:复制代码 代码如下: ALTER TABLE products ADD CHECK(name ''); --增加一个表级约束 ALTER TABLE products ADD CONSTRAINT some_name UNIQUE(product_no);--增加命名的唯一性约束。 ALTER TABLE products ADD FOREIGN KEY(pdt_grp_id) REFERENCES pdt_grps; --增加外键约束。 ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; --增加一个非空约束。 4. 删除约束:复制代码 代码如下: ALTER TABLE products DROP CONSTRAINT some_name; 对于显示命名的约束,可以根据其名称直接删除,对于隐式自动命名的约束,可以通过psql的/d tablename来获取该约束的名字。和删除字段一样,如果你想删除有着被依赖关系地约束,你需要用CASCADE。一个例子是某个外键约束依赖被引用字段上的唯一约束或者主键约束。如:复制代码 代码如下: MyTest=# /d products Table "public.products" Column | Type | Modifiers ------------+---------+----------- product_no | integer | name | text | price | numeric | Check constraints: "positive_price" CHECK (price 0::numeric) 和其他约束不同的是,非空约束没有名字,因此只能通过下面的方式删除:复制代码 代码如下: ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; 5. 改变字段的缺省值: 在为已有字段添加缺省值时,不会影响任何表中现有的数据行, 它只是为将来INSERT命令改变缺省值。复制代码 代码如下: ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; 下面为删除缺省值:复制代码 代码如下: ALTER TABLE products ALTER COLUMN price DROP DEFAULT 6. 修改字段的数据类型: 只有在字段里现有的每个项都可以用一个隐含的类型转换转换成新的类型时才可能成功。比如当前的数据都是整型,而转换的目标类型为numeric或varchar,这样的转换一般都可以成功。与此同时,PostgreSQL还将试图把字段的缺省值转换成新的类型, 还有涉及该字段的任何约束。但是这些转换可能失败,或者可能生成奇怪的结果。 在修改某字段类型之前,你最好删除那些约束,然后再把自己手工修改过的添加上去。 复制代码 代码如下: ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2); 7. 修改字段名:复制代码 代码如下: ALTER TABLE products RENAME COLUMN product_no TO product_number; 8. 修改表名:复制代码 代码如下: ALTER TABLE products RENAME TO items; 四、权限:

    1. 增加字段:
    ALTER TABLE products ADD COLUMN description text;
    新增的字段对于表中已经存在的行而言最初将先填充所给出的缺省值(如果你没有声明DEFAULT子句,那么缺省是空值)。
    在新增字段时,可以同时给该字段指定约束。
    ALTER TABLE products ADD COLUMN description text CHECK(description <> '');
    
    2. 删除字段:
    ALTER TABLE products DROP COLUMN description;
    如果该表为被引用表,该字段为被引用字段,那么上面的删除操作将会失败。如果要想在删除被引用字段的同时级联的删除其所有引用字段,可以采用下面的语法形式。
    ALTER TABLE products DROP COLUMN description CASCADE;
    
    3. 增加约束:
    ALTER TABLE products ADD CHECK(name <> '');  --增加一个表级约束
    ALTER TABLE products ADD CONSTRAINT some_name UNIQUE(product_no);--增加命名的唯一性约束。
    ALTER TABLE products ADD FOREIGN KEY(pdt_grp_id) REFERENCES pdt_grps; --增加外键约束。
    ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; --增加一个非空约束。
    
    4. 删除约束:
    ALTER TABLE products DROP CONSTRAINT some_name;
    对于显示命名的约束,可以根据其名称直接删除,对于隐式自动命名的约束,可以通过psql的d tablename来获取该约束的名字。和删除字段一样,如果你想删除有着被依赖关系地约束,你需要用CASCADE。一个例子是某个外键约束依赖被引用字段上的唯一约束或者主键约束。如:
    MyTest=# d products
         Table "public.products"
       Column     |  Type   | Modifiers
     ------------+---------+-----------
     product_no | integer |
     name          | text    |
     price           | numeric |
     Check constraints:
        "positive_price" CHECK (price > 0::numeric)
    和其他约束不同的是,非空约束没有名字,因此只能通过下面的方式删除:
    ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
    
    5. 改变字段的缺省值:
    在为已有字段添加缺省值时,不会影响任何表中现有的数据行, 它只是为将来INSERT命令改变缺省值。
    ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
    下面为删除缺省值:
    ALTER TABLE products ALTER COLUMN price DROP DEFAULT
   
    6. 修改字段的数据类型:
    只有在字段里现有的每个项都可以用一个隐含的类型转换转换成新的类型时才可能成功。比如当前的数据都是整型,而转换的目标类型为numeric或varchar,这样的转换一般都可以成功。与此同时,PostgreSQL还将试图把字段的缺省值(如果存在)转换成新的类型, 还有涉及该字段的任何约束。但是这些转换可能失败,或者可能生成奇怪的结果。 在修改某字段类型之前,你最好删除那些约束,然后再把自己手工修改过的添加上去。
    ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
       
    7. 修改字段名:
    ALTER TABLE products RENAME COLUMN product_no TO product_number;
    
    8. 修改表名:
    ALTER TABLE products RENAME TO items;
   
四、权限:

5、在开发框架中使用PostgreSQL数据库

这个属于多数据库支持的其中一种,我在之前的随笔《在数据库访问项目中使用微软企业库Enterprise Library,实现多种数据库的支持》 有简单对多数据库的使用做了介绍,不过如果是基于多数据实现层的处理,那么则需要以框架的分层方式来实现,通过代码生成工具Databasse2Sharp的快速生成,整个开发工作变得更加高效和快速,而且各个分层的目录统一易懂。

我们以PostgreSQL数据库支持为例,在开发框架上支持这种开发,则具有以下特点:

1)框架底层采用了微软企业库Enterprise Library作为底层数据库访问模块
2)多数据库支持,统一采用企业库的数据库访问对象,操作一致
3)PostgreSQL开发和SQLServer开发过程相似
4)配置文件修改ComponentDbType为npgsql即可配置为PostgreSQL
5)数据库指向是在BaseBLL对象构建的时候实现切换

必赢手机登录网址 6

必赢手机登录网址 7

上面就是整个框架数据访问层的整体设计和继承关系,我们在实际开发的时候,我们可以利用代码生成工具Database2Sharp进行快速开发,可以实现生成框架底层和WInfrom界面或者Web界面等内容,从而可以实现快速整合即可。

我们在开发实现PostgreSQL数据层对象的时候,可以把其他数据库层(如SQLServer的),然后修改对应的数据访问层基类和命名空间即可构造成PostgreSQL层了。

必赢手机登录网址 8

必赢手机登录网址 9

必赢手机登录网址 10

 必赢手机登录网址 11

只有表的所有者才能修改或者删除表的权限。要赋予一个权限,我们使用GRANT命令,要撤销一个权限,使用REVOKE命令。 需要指出的是,PUBLIC是特殊"用户"可以用于将权限赋予系统中的每一个用户。在声明权限的位置写ALL则将所有的与该对象类型相关的权限都赋予出去。复制代码 代码如下: GRANT UPDATE ON table_name TO user; --将表的更新权限赋予指定的user。 GRANT SELECT ON table_name TO GROUP group; --将表的select权限赋予指定的组。 REVOKE ALL ON table_name FROM PUBLIC; --将表的所有权限从Public撤销。 最初,只有对象所有者(或者超级用户)可以赋予或者撤销对象的权限。但是,我们可以赋予一个"with grant option"权限,这样就给接受权限的人以授予该权限给其它人的权限。如果授予选项后来被撤销,那么所有那些从这个接受者接受了权限的用户(直接或者通过级连的授权)都将失去该权限。

    只有表的所有者才能修改或者删除表的权限。要赋予一个权限,我们使用GRANT命令,要撤销一个权限,使用REVOKE命令。
    需要指出的是,PUBLIC是特殊"用户"可以用于将权限赋予系统中的每一个用户。在声明权限的位置写ALL则将所有的与该对象类型相关的权限都赋予出去。
    GRANT UPDATE ON table_name TO user;  --将表的更新权限赋予指定的user。
    GRANT SELECT ON table_name TO GROUP group; --将表的select权限赋予指定的组。
    REVOKE ALL ON table_name FROM PUBLIC; --将表的所有权限从Public撤销。
    最初,只有对象所有者(或者超级用户)可以赋予或者撤销对象的权限。但是,我们可以赋予一个"with grant option"权限,这样就给接受权限的人以授予该权限给其它人的权限。如果授予选项后来被撤销,那么所有那些从这个接受者接受了权限的用户(直接或者通过级连的授权)都将失去该权限。

这里需要特别说明的是,该博客中的大部分案例和段落均取自于PostgreSQL中文文档,如转载本系列博客,请同样注明该出处。

    这里需要特别说明的是,该博客中的大部分案例和段落均取自于PostgreSQL中文文档,如转载本系列博客,请同样注明该出处。