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

待兔 等级 626 0 0

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

1、PostgreSQL数据库介绍

PostgreSQL是以加州大学伯克利分校计算机系开发的 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管理数据库工具

3)安装PowerDesigner建模工具

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 );


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 );


当多个表之间存在了主外键的参考性约束关系时,如果想删除被引用表(主键表)中的某行记录,由于该行记录的主键字段值可能正在被其引用表(外键表)中某条记录所关联,所以删除操作将会失败。如果想完成此操作,一个显而易见的方法是先删除引用表中和该记录关联的行,之后再删除被引用表中的该行记录。然而需要说明的是,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 <> '');


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 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; --增加一个非空约束。


4.  删除约束:

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)特殊字段对应关系(部分)  


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()


**SQL限定符**  
Sqlserver为\[\],如

Select [Name] from [User]


PostgreSQL为“”,如

Select “Name” from User

```

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

函数
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,实现多种数据库的支持》 有简单对多数据库的使用做了介绍,不过如果是基于多数据实现层的处理,那么则需要以框架的分层方式来实现,通过代码生成工具Databasse2Sharp的快速生成,整个开发工作变得更加高效和快速,而且各个分层的目录统一易懂。

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

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

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

收藏
评论区

相关推荐

阿里云德哥:PostgreSQL 数据库的前世今生
内容摘要 PostgreSQL是以加州大学伯克利分校计算机系开发的 Posrgres,现在已经更名为PostgreSQL。它是一个自由的对象关系数据库服务器(数据库管理系统),它在灵活的 BSD风格许可证下发行。PostgreSQL 中国社区发起人之一Digoal为我们带来PostgreSQL 前世今生、社区理念以及阿里云Postgr
postgresql和mysql哪个好
postgresql和mysql都是免费且功能强大的开源数据库,很多用户面对这两个库都会有一个问题,那就是哪一个才是最好的开源数据库,MySQL还是PostgreSQL呢?该选择哪一个开源数据库呢? postgresql和mysql哪个好 一.PostgreSQL相对于MySQL的优势 1、在SQL的标准实现上要比MySQL完善,而且功能实现比较严谨;
[DB]PostgreSQL 与 MySQL 相比,优势何在?
PostgreSQL 与 MySQL 相比,优势何在? 数据库 知乎 Pg 没有 MySQL 的各种坑 MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text... Pg 没有这个限制, text 能支持各种大小. 按照 SQL 标准, 做 null 判断不能用
PostgreSQL介绍以及如何开发框架中使用PostgreSQL数据库
最近准备下PostgreSQL数据库开发的相关知识,本文把总结的PPT内容通过博客记录分享,本随笔的主要内容是介绍PostgreSQL数据库的基础信息,以及如何在我们的开发框架中使用PostgreSQL数据库,希望大家多多提意见。 1、PostgreSQL数据库介绍 PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES,现在已经更
【译】为什么传统的PostgreSQL又流行起来了
本文译自 《Why oldschool PostgreSQL is so hip again》(https://www.infoworld.com/article/3240064/sql/whyoldschoolpostgresqlissohipagain.html) by Matt Asay(https://www.infoworl
PostgreSQL语法、连接
1.语法 1.插入数据(INSERT语句) 在PostgreSQL中,INSERT 查询用于在表中插入新行。 可以一次插入单行或多行到表中。 语法: INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, v
数据库编程 MySQL 技巧与经验
1.MySQL创建数据表时设定引擎并添加外键约束 创建两个数据表,在它们之间添加外键约束,然后在被添加外键的表中添加数据,发现并没有提示报错,很正常地插入了数据,说明外键没有添加成功,在SQL可视化工具里查看表的属性,并点击外部键会出现弹窗,提示是因为引擎的问题,导致不能添加外键。MySQL安装时默认用的表引擎是MyISAM,而MyISAM是不支持外键的,如
Kubernetes部署高可用PostgreSQL集群
PostgreSQL 是一个功能强大的开源关系数据库,它使用和扩展了 SQL 语言,并结合了许多安全存储和扩展最复杂数据工作负载的功能。PostgreSQL 的起源可以追溯到 1986 年,作为加利福尼亚大学伯克利分校 POSTGRES 项目的一部分,与 Linux 内核相似,PostgreSQL 是由社区驱动的开源项目,由全世界贡献者所维护。Postgre
MySQL8与PG10:新版本下的较量谁更胜一筹?
既然MySQL 8和PostgreSQL 10已经发布了,现在是时候回顾一下这两大开源关系型数据库是如何彼此竞争的。在这些版本之前,人们普遍认为,PostgreSQL在功能集表现更出色,也因其“学院派”风格而备受称赞,MySQL则更善长大规模并发读/写。但是随着它们最新版本的发布,两者之间的差距明显变小了。特性比较首先来看看我们都喜欢谈论的“时髦”功能。过去
Mysql - 查看、创建、更改 数据库和表
一、一探究竟 我想看看有多少个数据库,有多少个表,以及表里有啥东西。那么你可以这样: 命令: 查看多少个数据库:注意 后面带s 查看 SHOW DATABASES; 查看表 USE blog; SHOW TABLES; 查看表
Python开发 常见异常和解决办法
1.sqlalchemy创建外键关系报错property of that name exists on mapperSQLAlchemy是Python编程语言下的一款开源软件,提供了SQL工具包及对象关系映射(ORM)工具,使得在Python中操作MySQL更加简单。在给两个表创建外键关系时可能会报错:bashsqlalchemy.exc.Arg
PostgreSQL简史
现在被称为PostgreSQL的对象关系型数据库管理系统是从加州大学伯克利分校写的POSTGRES软件包发展而来的。经过二十多年的发展,PostgreSQL是世界上可以获得的最先进的开源数据库。 2.1. 伯克利的POSTGRES项目由Michael Stonebraker教授领导的POSTGRES项目是由防务高级研究项目局(DARPA)、陆军研究办公室(A
MySQL基础(非常全)
MySQL基础一、MySQL概述1、什么是数据库 ? 答:数据的仓库,如:在ATM的示例中我们创建了一个 db 目录,称其为数据库2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Server等 ? 答:他们均是一个软件,都有两个主要的功能: a. 将数据保存到文件或内存 b. 接收特定的命令,然后对文件进行相应
openGauss——VMware安装
写在前面 本教程适用于 0 基础小白,里面的很多命令、技术细节没有进行解释,因为那样要增补的内容太多了,都是一些 Linux 的相关知识,还有一些 openGauss 官方手册里面的内容,太过于啰嗦 学校上的数据库,安排的数据库的实验说要用华为这款开源的数据库,(估计是学校近些年一直跟华为合作),内核是拿 PostgreSQL 写的,老师给的教程是拿 Vir
PostgreSQL的函数和存储过程--MemFireDB
简介PostgreSQL是最流行的对象关系型数据库系统。它是一个强大的、高性能的数据库系统。在这篇文章中,我们将讨论如何使用函数和存储过程来执行操作,如插入、删除、更新和查询。感兴趣的同学可以通过 memfiredb.com 提供的免费云数据库一边操作一边阅读。 函数一般来说,函数是一组进行任何操作的SQL语句,如选择、插入、删除和更新。在PostgreSQ