PostgreSQL详解.docx

1、PostgreSQL详解 PostgreSQL数据库一:PostgreSQL介绍1.PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES，现在已经更名为POSTGRES，版本 4.2为基础的对象关系型数据库管理系统（ORDBMS）。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性：复杂查询、外键、触发器、视图、事务完整性、MVCC。同样，PostgreSQL 可以用许多方法扩展，比如， 通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言。并且，因为许可证的灵活，任何人都可以以任何目的免费使用、修改、和分发 PostgreSQL，不管是私用

2、、商用、还是学术研究使用。2.PostgreSQL图标3.PostgreSQL优点有目前世界上最丰富的数据类型的支持支持,其中有些数据类型可以说连商业数据库都不具备,具体类型下文会说明.PostgreSQL拥有一支非常活跃的开发队伍，而且在许多黑客的努力下，PostgreSQL 的质量日益提高PostgreSQL 对接口的支持也是非常丰富的，几乎支持所有类型的数据库客户端接口。这一点也可以说是 PostgreSQL 一大优点。4.PostgreSQL缺点首先，早期的 PostgreSQL 继承了几乎所有 Ingres, Postgres, Postgres95 的问题：过于学院味，因为首先它的

3、目的是数据库研究，因此不论在稳定性， 性能还是使用方方面面，长期以来一直没有得到重视，直到 PostgreSQL 项目开始以后，情况才越来越好，PostgreSQL 已经完全可以胜任任何中上规模范围内的应用范围的业务其次，PostgreSQL 的确还欠缺一些比较高端的数据库管理系统需要的特性，比如数据库集群，更优良的管理工具和更加自动化的系统优化功能 等提高数据库性能的机制等。5.目前官方最新版本:9.3.2二、windows下安装过程1、开始安装： 2、选择程序安装目录： 注：安装 PostgreSQL 的分区最好是 NTFS 格式的。PostgreSQL 首要任务是要保证数据的完整性，而

4、FAT 和 FAT32 文件系统不能提供这样的可靠性保障，而且 FAT 文件系统缺乏安全性保障，无法保证原始数据在XX的情况下被更改。此外，PostgreSQL 所使用的多分点功能完成表空间的这一特征在FAT文件系统下无法实现。然而，在某些系统中，只有一种 FAT 分区，这种情况下，可以正常安装 PostgreSQL，但不要进行数据库的初始化工作。安装完成后，在 FAT 分区上手动执行 initdb.exe 程序即可，但不能保证其安全性和可靠性，并且建立表空间也会失败。 3、选择数据存放目录： 4、输入数据库超级用户和创建的OS用户的密码 注：数据库超级用户是一个非管理员账户，这是为了减少黑客

5、利用在 PostgreSQL 发现的缺陷对系统造成损害，因此需要对数据库超级用户设置密码，如下图所示，安装程序自动建立的服务用户的用户名默认为 postgres。 5、设置服务监听端口，默认为5432 6、选择运行时语言环境 注：选择数据库存储区域的运行时语言环境（字符编码格式）。在选择语言环境时，若选择default locale可能会导致安装不正确；同时，PostgreSQL 不支持 GBK 和 GB18030 作为字符集，如果选择其它四个中文字符集：中文繁体 香港（ChineseTraditional, Hong Kong S.A.R.）、中文简体 新加坡（ChineseSimplifi

6、ed, Singapore）、中文繁体 台湾（ChineseTraditional, Taiwan）和中文繁体 澳门（ChineseTraditional, Marco S.A.R.），会导致查询结果和排序效果不正确。建议选择C，即不使用区域。-我选择了default localt，安装正确；建议选择default localt。 7、安装过程（2分钟） 8、安装完成注：多选框是安装额外的驱动和工具，可以不点。在安装目录可以看到其中：data存放数据文件、日志文件、控制文件、配置文件等。uninstall-postgresql.exe用于卸载已安装的数据库管理系统。pg_env.bat里配置了

7、数据库的几个环境变量. 3、图形化界面pgAdmin（大象） 对于每种数据库管理系统，都有相当多的设计与管理工具（可视化界面管理工具），有的是数据库厂商自己提供的（一般都至少有一个），有的是第三方公司开发的，你甚至可以自己写一个简单易用的管理工具。例如Oracle的Oracle SQL Developer（自己开发的）、PLSQL Developer（第三方公司开发的）、SQL Server Management Studio（自己开发的）、PostgreSQL就有好几款流行的管理工具，例如：pgAdmin、navicat_pgsql、phppgsql等。pgAdmin是一个针对Postgre

8、SQL数据库的设计和管理接口，可以在大多数操作系统上运行。软件用C+编写，具有很优秀的性能。pgadmin 是与 Postgres 分开发布的，可以从www.pgadmin.org下载。目前装个全功能的PostgreSQL数据库，自带该管理工具。打开pgAdmin，可以看到在第一部分安装的本地数据库的属性，如下图所示： 成功连接服务器后，如下图所示：图中可以看出，新安装的PostgreSQL数据库管理系统带有一个数据库postgres；已建好两个表空间：pg_default、pg_global。initdb.exe初始化的两个默认表空间pg_global、pg_default。数据库默认的表空

9、间pg_default 是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间，他是模板数据库template0和template1的默认表空间。initdb.exe初始化的两个默认表空间pg_global、pg_default。数据库默认的表空间pg_global是用来存储共享系统目录的默认空间。pg_default 为 PostgreSQL也可以理解成系统表空间，它对应的物理位置为 $PGDATA/base目录。在PostgreSQL（pg_catalog）下可以看到postgers数据库的一些数据字典和数据字典视图。新建一个服务器连接，连接远程

10、服务器上的PostgreSQL数据库（假设已有远程服务器上已安装好PostgreSQL数据库管理系统）： 4、Pgsql 对于每种数据库管理系统，都会提供一个命令行管理接口，例如Oracle的sqlplus，SQL Server的isql和osql等。凡是用图形管理界面可以实现的功能原则上都可以通过命令行界面命令实现。两者各有优缺点，使用场合不同。在windows下当然常用图形管理界面，因为在图像管理界面中往往都嵌有命令行工具，而在unix和linux下，当然就常用命令行工具了，除了我们在类unix下主要使用字符界面的原因外，还因为大部分情况下我们只能通过telnet或ssh工具远程连接服务器

11、进行操作，此时也只能使用命令行了。从开始目录打开SQL shell（pgsql）输入密码得到如下图界面： 5、PostgreSQL常用数据类型一、数值类型1. 整数类型： 类型smallint、integer和bigint存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用smallint。而只有在integer的范围不够的时候才使用bigint，因为前者(integer)绝对快得多。2. 任意精度数值： 类型numeric可以存储最多1

12、000位精度的数字并且准确地进行计算。因此非常适合用于货币金额和其它要求计算准确的数量。不过，numeric类型上的算术运算比整数类型或者浮点数类型要慢很多。 numeric字段的最大精度和最大比例都是可以配置的。要声明一个类型为numeric的字段，你可以用下面的语法： NUMERIC(precision,scale) 比如数字23.5141的精度为6，而刻度为4。 在目前的PostgreSQL版本中，decimal和numeric是等效的。3. 浮点数类型： 数据类型real和double是不准确的、牺牲精度的数字类型。不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，

13、因此存储后再把数据打印出来可能显示一些缺失。4. Serial(序号)类型： serial和bigserial类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。 CREATE TABLE tablename ( colname SERIAL ); 等价于 CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename( colname integer DEFAULT nextval(tablename_colname_seq) NOT NULL ); 这样，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取

14、值。应用了一个NOT NULL约束以确保空值不会被插入。在大多数情况下你可能还希望附加一个UNIQUE或者PRIMARY KEY约束避免意外地插入重复的数值，但这个不是自动发生的。因此，如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。 还需要另外说明的是，一个serial类型创建的序列在其所属字段被删除时，该序列也将被自动删除，但是其它情况下是不会被删除的。因此，如果你想用同一个序列发生器同时给几个字段提供数据，那么就应该以独立对象的方式创建该序列发生器。2、字符类型SQL 定义了两种基本的字符类型，varchar(n)和char(n)，这里的n是一

15、个正整数。两种类型都可以存储最多n个字符长的字串，试图存储更长的字串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字串将被截断为最大长度。如果没有长度声明，char等于char(1)，而varchar则可以接受任何长度的字串。这里需要注意的是，如果是将数值转换成char(n)或者varchar(n)，那么超长的数值将被截断成n个字符，而不会抛出错误。最后需要提示的是，这三种类型之间没有性能差别，只不过是在使用char类型时增加了存储尺寸。虽然在某些其它的数据库系统里，char(n)有一定的性能优势，但在PostgreSQL里没有。在大多数情况下，应该使用text或者

16、varchar。3、日期/时间类型1. 日期/时间输入: 任何日期或者时间的文本输入均需要由单引号包围，就象一个文本字符串一样。日期格式时间格式时间戳类型的有效输入由一个日期和时间的联接组成，后面跟着一个可选的时区。因此，1999-01-08 04:05:06和1999-01-08 04:05:06 -8:00都是有效的数值。2，示例在插入数据之前先查看datestyle系统变量的值：Sql: 输出窗口5、布尔类型：PostgreSQL支持标准的SQL boolean数据类型。boolean只能有两个状态之一：真(True)或 假(False)。该类型占用1个字节。真值的有效文本值是：TRUE

17、 ，t ，true， y ，yes ，1而对于假而言，你可以使用下面这些：FALSE ，f ，false ，n ，no ，06、位串类型：位串就是一串1和0的字串。它们可以用于存储和视觉化位掩码。我们有两种类型的SQL位类型：bit(n)和bit varying(n); 这里的n是一个正整数。bit类型的数据必须准确匹配长度n; 试图存储短些或者长一些的数据都是错误的。类型bit varying数据是最长n的变长类型；更长的串会被拒绝。写一个没有长度的bit等效于bit(1)，没有长度的bit varying相当于没有长度限制。 针对该类型，最后需要提醒的是，如果我们明确地把一个位串值转换成b

18、it(n)，那么它的右边将被截断或者在右边补齐零，直到刚好n位，而不会抛出任何错误。类似地，如果我们明确地把一个位串数值转换成bit varying(n)，如果它超过n位，那么它的右边将被截断。 见如下具体使用方式：这句sql执行时就会报错,因为.a的数据的长度是3,如果所插数据长度不为三则报错.注:(:)为转换符7、数组类型：1. 数组类型声明： 创建字段含有数组类型的表。 插入数组数据2查询数组数据和其他语言一样，PostgreSQL中数组也是通过下标数字(写在方括弧内)的方式进行访 问，只是PostgreSQL中数组元素的下标是从1开始n结束。PostgreSQL中还提供了访问数组范围的

19、功能，即ARRAY脚标下界:脚标上界。3. 修改数组数据代替全部数组值：更新数组中某一元素：更新数组某一范围的元素：直接赋值扩大数组：8、复合类型PostgreSQL中复合类型有些类似于C语言中的结构体，也可以被视为Oracle中的记录类型，但是还是感觉复合类型这个命名比较贴切。它实际上只是一个字段名和它们的数据类型的列表。PostgreSQL允许像简单数据类型那样使用复合类型。比如，表字段可以声明为一个复合类型。CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric );和声明一个数据表相比，

20、声明类型时需要加AS关键字，同时在声明TYPE时不能定义任何约束。下面我们看一下如何在表中指定复合类型的字段，如：CREATE TABLE on_hand ( item inventory_item, count integer);最后需要指出的是，在创建表的时候，PostgreSQL也会自动创建一个与该表对应的复合类型，名字与表字相同，即表示该表的复合类型。复合类型值输入： 我们可以使用文本常量的方式表示复合类型值，即在圆括号里包围字段值并且用逗号分隔它们。你也可以将任何字段值用双引号括起，如果值本身包含逗号或者圆括号，那么就用双引号括起，对于上面的inventory_item复合类型的输入

21、如下：(fuzzy dice,42,1.99)如果希望类型中的某个字段为NULL，只需在其对应的位置不予输入即可，如下面的输入中price字段的值为NULL， (fuzzy dice,42,) 如果只是需要一个空字串，而非NULL，写一对双引号，如： (,42,)在更多的场合中PostgreSQL推荐使用ROW表达式来构建复合类型值，使用该种方式相对简单，无需考虑更多标识字符问题，如： ROW(fuzzy dice, 42, 1.99) ROW(, 42, NULL) 注：对于ROW表达式，如果里面的字段数量超过1个，那么关键字ROW就可以省略，因此以上形式可以简化为： (fuzzy dice

22、, 42, 1.99) (, 42, NULL)访问复合类型： 访问复合类型中的字段和访问数据表中的字段在形式上极为相似，只是为了对二者加以区分，PostgreSQL设定在访问复合类型中的字段时，类型部分需要用圆括号括起，以避免混淆，如： SELECT (item).name FROM on_hand WHERE (item).price 9.99; 如果在查询中也需要用到表名，那么表名和类型名都需要被圆括号括起，如： SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price 9.99;修改复合类型： 见如下几个示例：

23、 -直接插入复合类型的数据，这里是通过ROW表达式来完成的。 INSERT INTO on_hand(item) VALUES(ROW(fuzzy dice,42,1.99); -在更新操作中，也是可以通过ROW表达式来完成。 UPDATE on_hand SET item = ROW(fuzzy dice,42,1.99) WHERE count = 0; -在更新复合类型中的一个字段时，我们不能在SET后面出现的字段名周围加圆括号， 但是在等号右边的表达式里引用同一个字段时却需要圆括号。 UPDATE on_hand SET item.price = (item).price + 1 WH

24、ERE count = 0; -可以在插入中，直接插入复合类型中字段。 INSERT INTO on_hand (item.supplier_id, item.price) VALUES(100, 2.2);九 postgreSQL jdbc详解当前postgreSQL9.3 jdbc 有三种版本 这是当前版本的驱动程序。除非你有不寻常的要求(运行旧的应用程序或jvm),这是您应该使用的驱动程序。它支持Postgresql 7.2或更新,需要1.5或更新的jdk。 它包含支持SSL和javax.sql包。 它有三种版本,JDBC3 JDBC4,jdbc41。 如果您使用的是1.6,那么你应该使

25、用JDBC4版本。如果您使用的是1.7或1.8,那么你应该使用JDBC41版本。十 postgreSQL 远程访问安装PostgreSQL数据库之后，默认是只接受本地访问连接。如果想在其他主机上访问PostgreSQL数据库服务器，就需要进行相应的配置。配置远程连接PostgreSQL数据库的步骤很简单，只需要修改data目录下的pg_hba.conf和postgresql.conf,其中 pg_hba.conf是用来配置对数据库的访问权限，postgresql.conf文件用来配置PostgreSQL数据库服务器的相应的参数。下面介绍配置的步骤：1.修改pg_hba.conf文件，配置用户的

26、访问权限：其中，第81行数据是新添加的内容，表示允许网段192.168.85.12上的主机使用所有合法的数据库用户名访问数据库，并提供加密的密码验证。(如果IP最后面的数字是0,那么其中的数字24是子网掩码，表示允许192.168.85.0-192.168.85.255的计算机访问！)2.修改postgresql.conf文件，将数据库服务器的监听模式修改为监听所有主机发出的连接请求。定位到#listen_addresses=localhost。PostgreSQL安装完成后，默认是只接受来在本机localhost的连接请求。将行开头都#去掉，将行内容修改为listen_addresses=*来允许数据库服务器监听来自任何主机的连接请求