PostgreSQL介绍以及如何开发框架中应用PostgreSQL数据库

前不久准备下PostgreSQL数据库开发的连锁文化,本文把总括的PPT内容通过博客记录分享,本随笔的关键内容是介绍PostgreSQL数据库的底蕴音讯,以及如何在大家的开销框架中选择PostgreSQL数据库,希望我们多多提意见。

1、PostgreSQL数据库介绍

PostgreSQL是以加州大学Berkeley分校处理器系支付的
POSTGRES,现在已经改名为PostgreSQL。它具备众多科学的特性:

开源:PostgreSQL
是一个随意的靶子-关周密据库服务器(数据库管理连串),它在灵活的
BSD-风格许可证下发行。

包容性好:PostgreSQL 的风味覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99。

可相信性高:PostgreSQL标榜自己是世界上先导进的开源数据库。可信性是PostgreSQL的最高优先级。它以金城汤池的质量和漂亮的工程化而名高天下,援救高工作、职责关键型应用。

广阔运用:自从MySQL被Oracle收购之后,PostgreSQL渐渐改为开源关系型数据库的首选,在职能上是一揽子当先MySQL的一个开源数据库。

出色协理:PostgreSQL对接口的协理也是相当丰盛的,大概支持所有项目标数据库客户端接;最丰裕的数据类型的支持,其中多少数据类型可以说连商
业数据库都不有所,比如IP类型和几何类型等;

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

数据库安装
PostgreSQL数据库(https://www.postgresql.org/download/windows/)

数据库管理工具
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)
);

外键约束声喜宝(Hipp)(Nutrilon)个字段(或者一组字段)的数值必须合营其它一个表中某些行出现的数值。
我们把那一个行为称做三个相关表之间的参考完整性。

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”权限,那样就给接受权限的人以赋予该权限给其旁人的权力。倘若授予选项后来被撤销,那么具有那一个从这些接受者接受了权力的用户(间接或者经过级连的授权)都将错过该权限。

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版本音信
……

5、在付出框架中行使PostgreSQL数据库

本条属于多数据库接济的其中一种,我在头里的小说《在数据库访问项目中采取微软公司库Enterprise
Library,落成多种数据库的援助

有简要对一大半据库的应用做了介绍,但是若是是基于多数据已毕层的处理,那么则须求以框架的支行方式来贯彻,通过代码生成工具Databasse2夏普的飞速变动,整个开发工作变得更为高效和便捷,而且种种分支的目录统一易懂。

咱俩以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

相关文章