PostgreSQL 从菜鸟到专家(中文版)
目录
引言............................................................................................................................................................... 1
第一章 POSTGRESQL 介绍 ......................................................................................................................... 2
基于数据编程 .............................................................................................................................................2
静态数据.................................................................................................................................................................2
用于数据存储的扁平文件....................................................................................................................................... 2
重复单元和其他问题...............................................................................................................................................3
什么是数据库管理系统 .............................................................................................................................. 3
数据模型.................................................................................................................................................................4
查询语言.................................................................................................................................................................6
数据库管理系统的责任...........................................................................................................................................7
什么是 POSTGRESQL? ...............................................................................................................................8
PostgreSQL 历史简介............................................................................................................................................ 8
PostgreSQL 架构................................................................................................................................................... 9
通过 PostgreSQL 访问数据.................................................................................................................................... 9
什么是开源? ...........................................................................................................................................10
相关资源 .................................................................................................................................................. 10
第二章 关系数据库原理 ...............................................................................................................................11
电子表格的局限性 .................................................................................................................................... 11
将数据存入数据库 ..................................................................................................................................... 13
选择列.................................................................................................................................................................. 13
为每个列选择数据类型.........................................................................................................................................13
标记行的唯一性....................................................................................................................................................14
在数据库中访问数据.................................................................................................................................. 14
通过网络访问数据................................................................................................................................................ 15
处理多用户访问....................................................................................................................................................16
数据分片和分块....................................................................................................................................................16
增加信息...............................................................................................................................................................18
设计表.................................................................................................................................................................. 20
基本数据类型....................................................................................................................................................... 25
处理未知的值:空值(NULL)............................................................................................................................ 26
回顾示例数据库....................................................................................................................................................26
摘要 ......................................................................................................................................................... 27
第三章 初步使用 POSTGRESQL ................................................................................................................28
在 LINUX 和 UNIX 系统中安装 POSTGRESQL .............................................................................................. 28
在 Linux 中使用二进制文件安装 PostgreSQL ........................................................................... 28
通过源码安装 PostgreSQL .................................................................................................................. 32
在 Linux 和 Unix 上配置 PostgreSQL .................................................................................................. 34
在 WINDOWS 中安装 POSTGRESQL .......................................................................................................... 39
使用 Windows 安装程序...................................................................................................................... 40
I
配置客户机访问 ................................................................................................................................... 45
建立示例数据库 ....................................................................................................................................... 46
添加用户记录.......................................................................................................................................46
建立数据库 ...........................................................................................................................................47
建表 ..................................................................................................................................................... 47
移除表..................................................................................................................................................49
填充表..................................................................................................................................................49
摘要 ......................................................................................................................................................... 52
第四章 访问你的数据 ...................................................................................................................................53
使用 PSQL.................................................................................................................................................53
在 Linux 系统中启动 .............................................................................................................................53
在 Windows 系统中启动 ...................................................................................................................... 54
解决启动问题.......................................................................................................................................54
使用一些基本的 psql 命令 ....................................................................................................................56
使用 SELECT 语句 .................................................................................................................................. 57
覆盖列名 .............................................................................................................................................. 59
控制行的顺序.......................................................................................................................................59
消除重复数据.......................................................................................................................................61
执行计算 .................................................................................................................................................. 63
选择行 ......................................................................................................................................................64
使用更复杂的条件 ................................................................................................................................66
模式匹配 .............................................................................................................................................. 67
限制结果集 ...........................................................................................................................................68
检查空值(NULL) ................................................................................................................................. 69
检查时间和日期 ....................................................................................................................................... 70
设置时间和日期的风格 ........................................................................................................................ 70
使用日期和时间函数............................................................................................................................ 74
多个表协同工作 ....................................................................................................................................... 75
关联两个表 ...........................................................................................................................................75
给表赋予别名.......................................................................................................................................80
关联三个或更多的表............................................................................................................................ 80
SQL92 的 SELECT 语法 ..........................................................................................................................84
摘要 ......................................................................................................................................................... 85
第五章 POSTGRESQL 的命令行和图形界面工具 ...................................................................................... 86
PSQL.........................................................................................................................................................86
启动 psql ..............................................................................................................................................86
在 psql 中输入命令 ...............................................................................................................................87
使用命令历史.......................................................................................................................................87
在 psql 中执行脚本文件 ....................................................................................................................... 88
检查数据库 ...........................................................................................................................................89
psql 命令行快速参考 ............................................................................................................................89
psql 内部命令快速参考 ........................................................................................................................ 90
设置 ODBC ..............................................................................................................................................92
在 Windows 中安装 ODBC 驱动程序 ...................................................................................................92
在 Windows 中建立一个数据源 ............................................................................................................94
在 Linux/Unix 中安装 ODBC 驱动程序 .................................................................................................96
在 Linux/Unix 中建立一个数据源 ......................................................................................................... 96
PGADMIN III .............................................................................................................................................. 96
安装 pgAdmin III .................................................................................................................................. 96
使用 pgAdmin III .................................................................................................................................. 97
PHPPGADMIN .......................................................................................................................................... 100
安装 phpPgAdmin ..............................................................................................................................100
II
使用 phpPgAdmin ..............................................................................................................................101
MICROSOFT ACCESS............................................................................................................................... 103
使用链接表 .........................................................................................................................................103
输入数据及建立报表..........................................................................................................................106
MICROSOFT EXCEL................................................................................................................................. 108
POSTGRESQL 相关工具的资源.............................................................................................................. 111
摘要 ....................................................................................................................................................... 111
第六章 数据交互 ........................................................................................................................................112
添加数据到数据库中 ..............................................................................................................................112
使用基本的 INSERT 语句 .................................................................................................................. 112
使用更安全的插入语句 ...................................................................................................................... 114
插入数据到 serial 类型的列中 ............................................................................................................115
插入空值 ............................................................................................................................................ 118
使用\copy 命令 ...................................................................................................................................120
直接从另一个程序加载数据............................................................................................................... 122
修改数据库中的数据 ..............................................................................................................................124
使用 UPDATE 语句 ............................................................................................................................125
通过另一个表更新 ..............................................................................................................................126
从数据库删除数据 ................................................................................................................................. 127
使用 DELETE 语句 ............................................................................................................................ 127
使用 TRUNCATE 语句.......................................................................................................................128
摘要 ....................................................................................................................................................... 129
第七章 高级数据选择 ................................................................................................................................ 131
聚集函数 ................................................................................................................................................131
count 函数 ..........................................................................................................................................132
Min 函数 .............................................................................................................................................139
Max 函数 ............................................................................................................................................139
Sum 函数........................................................................................................................................... 140
Avg 函数 ............................................................................................................................................ 140
子查询 ....................................................................................................................................................141
返回多行记录的子查询 ...................................................................................................................... 143
相关子查询 .........................................................................................................................................144
存在子查询(Existence Subqueries).............................................................................................. 146
UNION 连接 ...........................................................................................................................................147
自连接 ....................................................................................................................................................149
外连接 ....................................................................................................................................................150
摘要 ....................................................................................................................................................... 154
第八章 数据定义 ........................................................................................................................................155
数据类型 ................................................................................................................................................155
布尔数据类型.....................................................................................................................................155
字符数据类型.....................................................................................................................................157
数字数据类型.....................................................................................................................................159
时间型数据类型 ................................................................................................................................. 161
数组 ................................................................................................................................................... 162
数据操作 ................................................................................................................................................164
在数据类型之间转换..........................................................................................................................165
用于数据操作的函数..........................................................................................................................166
魔法变量 ............................................................................................................................................ 167
OID 列 ................................................................................................................................................167
表管理 ....................................................................................................................................................168
建表 ................................................................................................................................................... 168
III
使用列约束 .........................................................................................................................................169
使用表约束 .........................................................................................................................................172
修改表结构 .........................................................................................................................................173
删除表................................................................................................................................................176
使用临时表 .........................................................................................................................................176
视图 ....................................................................................................................................................... 177
建立视图 ............................................................................................................................................ 177
删除和替换视图 ................................................................................................................................. 180
外键约束 ................................................................................................................................................180
作为一个列的约束的外键 ...................................................................................................................181
作为一个表的约束的外键 ...................................................................................................................181
外键约束的选项 ................................................................................................................................. 186
摘要 ....................................................................................................................................................... 188
第九章 事务和锁 ........................................................................................................................................189
什么是事务 ............................................................................................................................................ 189
将数据改变分组到逻辑单元............................................................................................................... 189
并发多用户访问数据..........................................................................................................................190
ACID 规则 ..........................................................................................................................................191
事务日志 ............................................................................................................................................ 191
单用户情况下的事务 ..............................................................................................................................192
包括多个表的事务 ..............................................................................................................................194
事务和保存点.....................................................................................................................................195
事务的限制 .........................................................................................................................................198
多用户情况下的事务 ..............................................................................................................................199
实现隔离 ............................................................................................................................................ 199
改变隔离级别.....................................................................................................................................203
使用显式和隐式的事务 ...................................................................................................................... 203
锁 ...........................................................................................................................................................203
避免死锁 ............................................................................................................................................ 204
显式锁定 ............................................................................................................................................ 206
摘要 ....................................................................................................................................................... 207
第十章 函数、存储过程和触发器.............................................................................................................. 208
运算符 ....................................................................................................................................................208
运算符的优先级和结合性 ...................................................................................................................209
数学运算符 .........................................................................................................................................210
比较和字符串运算符..........................................................................................................................211
其他运算符 .........................................................................................................................................212
内置函数 ................................................................................................................................................212
过程语言 ................................................................................................................................................214
PL/pgSQL 入门 ..................................................................................................................................215
函数重载 ............................................................................................................................................ 217
列出函数 ............................................................................................................................................ 218
删除函数 ............................................................................................................................................ 218
引号 ................................................................................................................................................... 218
解析一个存储过程 ................................................................................................................................. 219
函数参数 ............................................................................................................................................ 219
注释 ................................................................................................................................................... 220
声明 ................................................................................................................................................... 221
赋值 ................................................................................................................................................... 223
执行控制结构.....................................................................................................................................225
动态查询 ............................................................................................................................................ 231
IV
SQL 函数 ............................................................................................................................................... 232
触发器 ....................................................................................................................................................233
定义触发器过程 ................................................................................................................................. 233
建立触发器 .........................................................................................................................................233
为什么使用存储过程和触发器 ............................................................................................................... 238
摘要 ....................................................................................................................................................... 239
V
引言
欢迎来到 PostgreSQL 数据库从入门到精通。
在我们生涯的早期,我们开始理解开源软件的质量。不仅通常情况下它们可以自由使用,而且它们提供极高的质量。如
果你发现问题,你可以检查源代码,理解程序工作过程。如果你找到一个错误,你可以自己修复或找别人帮你修复。我们从
1978 年开始试用开源软件,包括优秀的 GNU 工具,包括 GNU Emacs 和 GCC。我们从 1993 年开始使用 Linux 并愉快地使
用 Linux 内核和 GNU 工具建立了一个完整的,自由的计算环境,并且使用 X Window 系统提供了一个图形用户界面。
PostgreSQL 是 一 个 采 取 相 同 的 开 源 理 念 的 优 秀 的 数 据 库 系 统 ( 更 多 关 于 开 源 和 自 由 的 信 息 , 请 访 问
http://www.opensource.org)。
数据库是非常有用的东西。很多人发现“桌面数据库”在办公室和家里的小应用程序中非常有用。很多网站是数据驱动
的,很多内容都由网页服务器后面的数据库提供。随着数据库的普及,我们觉得有必要写一本书介绍数据库理论和实践。
我们写这本书整体介绍数据库,全面覆盖现代的关系数据库的能以及怎么高效使用它们。使用 PostgreSQL 作为他的数
据库的人都没有觉得 PostgreSQL 在哪方面有什么不足。它支持优秀的数据库设计,非常有弹性和扩展性,且运行在几乎你
所能想到的计算机上,包括 Linux,Unix,Windows,Mac OS X,AIX,Solaris 以及 HP-UX。
对了,免得你好奇,我告诉你 PostgreSQL 念做“post-gres-cue-el”(而不是“post-gray-ess-cue-el”)。
本书大致分为三部分。第一部分包括入门,包括数据库概述(它们是什么以及它们用来干什么),尤其是 PostgreSQL
的概述(怎样获得,安装和启动以及使用)。如果你同时运行了示例,到第 5 章完成的时候,你将建立起第一个可工作的数
据库并且可以使用一些工具来做一些有用的事情,例如输入数据和执行查询。
本书的第二部分深刻地探索关系数据库的核心:SQL 查询语言。通过示例程序和“做一个尝试”章节,你将学到数据库
编程的很多方面。,从简单的数据插入和修改,强大的各类查询到通过存储过程和触发器扩展数据库功能。本章最重要的内
容是数据库无关,所以你从本章获取的知识能够让你在使用其他数据库时如鱼得水。当然,所有的用来说明的示例资源是用
PostgreSQL 来作为示例数据库。关于 PostgreSQL 的管理和数据库设计的习惯用法也将在本章完整讨论。
本书的第三部分关注于在你的程序中发挥 PostgreSQL 的能力。本章涵盖了通过大量的编程语言连接到数据库、执行查
询以及处理结果集。无论你是使用 PHP 或者 Perl 开发动态网页、用 Java 或者 C#开发企业级应用程序或者用 C 写一个客户
端程序,你将找到你想要的章节。
这是 PostgreSQL 从入门到精通的第二版;第一版在 2001 年发布。从那时起,每章的内容都根据 8.0 版本的 PostgreSQL
做了升级。我们在本书中有机会补充了新的一章讨论通过 C#访问 PostgreSQL。
1
第一章PostgreSQL 介绍
本书都是关于一个最近最成功的开源产品,一个名叫 PostgreSQL 的关系数据库。
数据库开发商和开源开发者都是 PostgreSQL 的热心拥护者。任何使用程序管理大量数据的人都可以从数据库中获得大
量益处。PostgreSQL 是一个非常优秀的关系数据库实现,全功能,开源且免费使用。
PostgreSQL 支持大量的主流开发语言,包括 C,C++,Perl,Python,Java,Tcl 以及 PHP。它是最接近工业标准 SQL92
的查询语言,并且正在实现新的功能以兼容最新的 SQL 标准:SQL2003。PostgreSQL 也获得数个奖项,包括三次被评为
Linux Journal 杂志编辑选择奖最佳数据库(2000,2003 和 2004 年度)以及 2004 年度 Linux 新媒体奖最佳数据库系统。我们
也许我们有点超越自我。你也许想知道到底 PostgreSQL 是什么,为什么你要使用它。
本章我们将设置场景为本书的剩余部分并提供一些关于数据库的概括性背景知识、不同类型的数据库、为什么他们非常
有用以及 PostgreSQL 在哪些地方符合这些要求。
基于数据编程
基本上所有的非普通计算机程序操作大量的数据,而且大量的应用程序被开发用来处理数据而不是进行计算任务。有些
人估计当今世界上 80%的应用程序开发会通过某种方式连接到数据库中的复杂数据,所以数据库对于很多应用来说是一种非
常重要的基础。
以数据编程的资源很丰富。大部分好的编程书籍都包含章节关于建立、存储和操作数据。有三本书(由 Wrox 发布)包
含以数据编程的信息:
《Beginning Linux Programming》第三版覆盖了 DBM 库以及 MySQL 数据库系统。
《Professional Linux Programming》包含关于 PostgreSQL 和 MySQL 数据库系统的章节。
《Beginning Databases with MySQL》全面覆盖了 MySQL 数据库系统。
静态数据
数据有很多形态和大小,我们根据数据的不同性质处理数据。有些情况下,数据非常简单——也许仅仅是一个数字例如
π的数值存在程序中用来画圆。程序本身可能包含这个硬编码的圆周率的值。我们把这种数据叫做静态数据,它永远不需要
改变。
另一种静态数据的例子是欧洲一些国家的汇率。在欧元区,货币都按固定的保留小数点后 6 位的精度兑换到欧元。假设
我们开发一个欧元区货币转换程序。他可能有一个硬编码的货币名称和基本汇率表,保存对应到欧元的兑换点数。这些汇率
永远不会改变。不过还没完,因为这张表会随着欧元区国家数量的增长而增长。当一个国家注册到欧元区,这个国家货币对
欧元的汇率将被设定,它将需要被加入这张表中。当这种情况发生,汇率转换程序需要修改,内置的货币表被修改,程序需
要重建。这需要在每次货币表发生变化时做一次。
一个更好的办法是应用程序读取一个包含简单的货币信息的文件,文件可能包含货币的名称、国际符号以及汇率。之后
在这个表需要改变时我们仅仅需要改变 这个文件,而不需要改变程序。
我们使用的这个数据文件没有特别的结构;它仅仅包含几行文本,包含一些数据让应用程序读取。它没有固定的结构。
因此我们把这种文件叫做扁平文件。以下是我们的货币文件可能的样子:
France
FRF 6.559570
Germany DEM1.955830
Italy ITL 1936.270020
Belgium BEF 40.339901
用于数据存储的扁平文件
扁平文件对于很多应用类型来说非常有用。由于文件保持在可管理大小,我们很容易改变它,扁平文件可能非常符合我
们的需求。
很多系统和程序,特别是 UNIX 平台,使用扁平文件存储数据或者进行数据交换。UNIX 的密码文件就是一个例子,它
2
通常情况下像以下的样子:
neil:*:500:100:Neil Matthew:/home/neil:/bin/bash
nick:*:501:100:Rick Stones:/home/rick:/bin/bash
这个例子包含一系列的信息和属性组成的记录。文件被设计成每行包含一个记录,整个文件则用来保存相关的记录到一
起。某些情形下,这种模式还不够用,因而,我们需要增加扩展的功能来配合应用程序完成必须的工作。
重复单元和其他问题
假设我们决定扩展货币汇率程序(本章前面介绍过)来记录每个国家的语言,以及人口和面积。在扁平文件中,我们需
要每行一个记录,每个记录由几个熟悉构成。记录中每个独立的属性总在固定的位置;例如货币符号总是第二个熟悉。所以
我们可以认为按列读数据,每列总是相同类型的信息。
为了添加某个国家使用的语言,我们可能会认为我们只需为每一行要添加一个新列。当我们发现一些国家有不止一种官
方语言时,我们碰到了一个问题。所以,在我们给 Belgium 的记录中,我们需要同时包含 Flemish 语和 French 语。对于
Switzerland,我们需要添加四种语言。扁平文件在这是应该看起来像这样:
FRF
France
547030
DEM
Germany
357021
301230
Italy
ITL
BEF
Belgium
10348276
Switzerland CHF
Italian
French
6.559570
German
1.955830
1936.270020 Italian
40.339901
Flemish
1.5255
German
60424213
82424609
58057477
French
French
这个问题被称为重复单元。我们现在的问题是重复的数据项(语言)是在记录内重复,也就是说数据可能会在列内重复,
30528
Romansch
7450867
41290
而不仅仅是记录。扁平的文件无法应付这类问题,因为它无法判断什么时候语言项列完了,开始下一个记录了。解决这个问
题唯一的方法是在文件内添加一些结构,但这种情况下这个文件不再是扁平文件了。
这里有另一个示例。记录 DVD 详细信息的程序可能需要记录生产的年份,导演,风格以及演员列表。我们可以设计一
个和 Windows 的.ini 文件格式差不多的文件,就像这样:
[2001: A Space Odyssey]
year=1968
director=Stanley Kubrick
genre=science fiction
starring=Keir Dullea
starring=Leonard Rossiter
…
[Toy Story]
…
我们通过引入描述每个记录类型的标记解决了重复单元的问题。但是,现在你的程序为了获得需要的数据,需要读取和
解析更复杂的文件。添加记录以及搜索这种结构就更困难了。我们怎样才能确保用于风格或分类的描述是从一个特定子集选
择的。我们怎样才能很容易的搜索到 Kubrick 导演的电影?
由于对数据的需求越来越复杂,我们被迫写越来越多的代码用来读取和存储我们的数据。如果我们扩展我们的 DVD 管
理程序,为 DVD 出租店主包含一些有用的信息——例如会员信息,租金,退租信息和预定信息,在扁平文件中管理所有这些
信息的希望渺茫。
另外一个通用问题就是大小的问题。虽然包含结构的文本文件可以通过暴力扫描回答复杂的查询类似于“告诉我所有的
最近三个月租过最少一次喜剧的会员”,不单代码很难编写,而且性能可能会很低。这是因为程序除了扫描整个文件来查找
任何一片信息外没有别的办法,即使是仅仅返回一条数据的问题“谁主演了 2001 年的 A Space Odyssey”。
我们需要的是一种在通用数据处理系统里用来存储和检索数据的方法,而不是总是发明一个个解决方案用于有点不同但
非常相似的问题。
我们需要的是一个数据库和一个数据库管理系统。
什么是数据库管理系统
韦氏在线字典(Merriam-Webster online dictionary,http://www.merriam-webster.com)定义数据库为一个为(通过计
算机)高速搜索和检索的而特别组织的大量数据集。
数据库管理系统(database management system,DBMS)通常是一套用来让程序开发人员从繁重的数据存储细节和
管理中解脱出来的程序库、应用程序以及工具套件。它还提供了用来搜索和更新记录的能力。多年来,数据库管理系统发展
出大量的特点用来解决各种各样特别的数据存储问题。
3