PostgreSQL詳解

1、 PostgreSQL數(shù)據(jù)庫(kù)一:PostgreSQL介紹1.PostgreSQL是以加州大學(xué)伯克利分校計(jì)算機(jī)系開(kāi)發(fā)的 POSTGRES，現(xiàn)在已經(jīng)更名為POSTGRES，版本 4.2為基礎(chǔ)的對(duì)象關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)（ORDBMS）。PostgreSQL支持大部分 SQL標(biāo)準(zhǔn)并且提供了許多其他現(xiàn)代特性：復(fù)雜查詢(xún)、外鍵、觸發(fā)器、視圖、事務(wù)完整性、MVCC。同樣，PostgreSQL 可以用許多方法擴(kuò)展，比如， 通過(guò)增加新的數(shù)據(jù)類(lèi)型、函數(shù)、操作符、聚集函數(shù)、索引方法、過(guò)程語(yǔ)言。并且，因?yàn)樵S可證的靈活，任何人都可以以任何目的免費(fèi)使用、修改、和分發(fā) PostgreSQL，不管是私用、商用、還是學(xué)術(shù)研究使用

2、。2.PostgreSQL圖標(biāo)3. PostgreSQL優(yōu)點(diǎn)有目前世界上最豐富的數(shù)據(jù)類(lèi)型的支持支持,其中有些數(shù)據(jù)類(lèi)型可以說(shuō)連商業(yè)數(shù)據(jù)庫(kù)都不具備,具體類(lèi)型下文會(huì)說(shuō)明.PostgreSQL擁有一支非?；钴S的開(kāi)發(fā)隊(duì)伍，而且在許多黑客的努力下，PostgreSQL 的質(zhì)量日益提高PostgreSQL 對(duì)接口的支持也是非常豐富的，幾乎支持所有類(lèi)型的數(shù)據(jù)庫(kù)客戶(hù)端接口。這一點(diǎn)也可以說(shuō)是 PostgreSQL 一大優(yōu)點(diǎn)。4. PostgreSQL缺點(diǎn)首先，早期的 PostgreSQL 繼承了幾乎所有 Ingres, Postgres, Postgres95 的問(wèn)題：過(guò)于學(xué)院味，因?yàn)槭紫人哪康氖菙?shù)據(jù)庫(kù)研究，因

3、此不論在穩(wěn)定性， 性能還是使用方方面面，長(zhǎng)期以來(lái)一直沒(méi)有得到重視，直到 PostgreSQL 項(xiàng)目開(kāi)始以后，情況才越來(lái)越好，PostgreSQL 已經(jīng)完全可以勝任任何中上規(guī)模范圍內(nèi)的應(yīng)用范圍的業(yè)務(wù)其次，PostgreSQL 的確還欠缺一些比較高端的數(shù)據(jù)庫(kù)管理系統(tǒng)需要的特性，比如數(shù)據(jù)庫(kù)集群，更優(yōu)良的管理工具和更加自動(dòng)化的系統(tǒng)優(yōu)化功能 等提高數(shù)據(jù)庫(kù)性能的機(jī)制等。5. 目前官方最新版本:9.3.2二、windows下安裝過(guò)程1、開(kāi)始安裝： 2、 選擇程序安裝目錄： 注：安裝 PostgreSQL 的分區(qū)最好是 NTFS 格式的。PostgreSQL 首要任務(wù)是要保證數(shù)據(jù)的完整性，而 FAT 和 FA

4、T32 文件系統(tǒng)不能提供這樣的可靠性保障，而且 FAT 文件系統(tǒng)缺乏安全性保障，無(wú)法保證原始數(shù)據(jù)在未經(jīng)授權(quán)的情況下被更改。此外，PostgreSQL 所使用的"多分點(diǎn)"功能完成表空間的這一特征在FAT文件系統(tǒng)下無(wú)法實(shí)現(xiàn)。然而，在某些系統(tǒng)中，只有一種 FAT 分區(qū)，這種情況下，可以正常安裝 PostgreSQL，但不要進(jìn)行數(shù)據(jù)庫(kù)的初始化工作。安裝完成后，在 FAT 分區(qū)上手動(dòng)執(zhí)行 initdb.exe 程序即可，但不能保證其安全性和可靠性，并且建立表空間也會(huì)失敗。 3、 選擇數(shù)據(jù)存放目錄： 4、 輸入數(shù)據(jù)庫(kù)超級(jí)用戶(hù)和創(chuàng)建的OS用戶(hù)的密碼 注：數(shù)據(jù)庫(kù)超級(jí)用戶(hù)是一個(gè)非管理員賬戶(hù)，

5、這是為了減少黑客利用在 PostgreSQL 發(fā)現(xiàn)的缺陷對(duì)系統(tǒng)造成損害，因此需要對(duì)數(shù)據(jù)庫(kù)超級(jí)用戶(hù)設(shè)置密碼，如下圖所示，安裝程序自動(dòng)建立的服務(wù)用戶(hù)的用戶(hù)名默認(rèn)為 postgres。 5、 設(shè)置服務(wù)監(jiān)聽(tīng)端口，默認(rèn)為5432 6、 選擇運(yùn)行時(shí)語(yǔ)言環(huán)境 注：選擇數(shù)據(jù)庫(kù)存儲(chǔ)區(qū)域的運(yùn)行時(shí)語(yǔ)言環(huán)境（字符編碼格式）。在選擇語(yǔ)言環(huán)境時(shí)，若選擇"default locale"可能會(huì)導(dǎo)致安裝不正確；同時(shí)，PostgreSQL 不支持 GBK 和 GB18030 作為字符集，如果選擇其它四個(gè)中文字符集：中文繁體 香港（ChineseTraditional, Hong Kong S.A.R.）、中文

6、簡(jiǎn)體 新加坡（ChineseSimplified, Singapore）、中文繁體 臺(tái)灣（ChineseTraditional, Taiwan）和中文繁體 澳門(mén)（ChineseTraditional, Marco S.A.R.），會(huì)導(dǎo)致查詢(xún)結(jié)果和排序效果不正確。建議選擇"C"，即不使用區(qū)域。-我選擇了default localt，安裝正確；建議選擇default localt。 7、 安裝過(guò)程（2分鐘） 8、 安裝完成注：多選框是安裝額外的驅(qū)動(dòng)和工具，可以不點(diǎn)。在安裝目錄可以看到其中：data存放數(shù)據(jù)文件、日志文件、控制文件、配置文件等。uninstall-postgres

7、ql.exe用于卸載已安裝的數(shù)據(jù)庫(kù)管理系統(tǒng)。pg_env.bat里配置了數(shù)據(jù)庫(kù)的幾個(gè)環(huán)境變量. 3、 圖形化界面pgAdmin（大象） 對(duì)于每種數(shù)據(jù)庫(kù)管理系統(tǒng)，都有相當(dāng)多的設(shè)計(jì)與管理工具（可視化界面管理工具），有的是數(shù)據(jù)庫(kù)廠(chǎng)商自己提供的（一般都至少有一個(gè)），有的是第三方公司開(kāi)發(fā)的，你甚至可以自己寫(xiě)一個(gè)簡(jiǎn)單易用的管理工具。例如Oracle的Oracle SQL Developer（自己開(kāi)發(fā)的）、PLSQL Developer（第三方公司開(kāi)發(fā)的）、SQL Server Management Studio（自己開(kāi)發(fā)的）、PostgreSQL就有好幾款流行的管理工具，例如：pgAdmin、navica

8、t_pgsql、phppgsql等。pgAdmin是一個(gè)針對(duì)PostgreSQL數(shù)據(jù)庫(kù)的設(shè)計(jì)和管理接口，可以在大多數(shù)操作系統(tǒng)上運(yùn)行。軟件用C+編寫(xiě)，具有很優(yōu)秀的性能。pgadmin 是與 Postgres 分開(kāi)發(fā)布的，可以從下載。目前裝個(gè)全功能的PostgreSQL數(shù)據(jù)庫(kù)，自帶該管理工具。打開(kāi)pgAdmin，可以看到在第一部分安裝的本地?cái)?shù)據(jù)庫(kù)的屬性，如下圖所示： 成功連接服務(wù)器后，如下圖所示：圖中可以看出，新安裝的PostgreSQL數(shù)據(jù)庫(kù)管理系統(tǒng)帶有一個(gè)數(shù)據(jù)庫(kù)postgres；已建好兩個(gè)表空間：pg_default、pg_global。initdb.exe初始化

9、的兩個(gè)默認(rèn)表空間pg_global、pg_default。數(shù)據(jù)庫(kù)默認(rèn)的表空間pg_default 是用來(lái)存儲(chǔ)系統(tǒng)目錄對(duì)象、用戶(hù)表、用戶(hù)表index、和臨時(shí)表、臨時(shí)表index、內(nèi)部臨時(shí)表的默認(rèn)空間，他是模板數(shù)據(jù)庫(kù)template0和template1的默認(rèn)表空間。initdb.exe初始化的兩個(gè)默認(rèn)表空間pg_global、pg_default。數(shù)據(jù)庫(kù)默認(rèn)的表空間pg_global是用來(lái)存儲(chǔ)共享系統(tǒng)目錄的默認(rèn)空間。pg_default 為 PostgreSQL也可以理解成系統(tǒng)表空間，它對(duì)應(yīng)的物理位置為 $PGDATA/base目錄。在PostgreSQL（pg_catalog）下可以看到pos

10、tgers數(shù)據(jù)庫(kù)的一些數(shù)據(jù)字典和數(shù)據(jù)字典視圖。新建一個(gè)服務(wù)器連接，連接遠(yuǎn)程服務(wù)器上的PostgreSQL數(shù)據(jù)庫(kù)（假設(shè)已有遠(yuǎn)程服務(wù)器上已安裝好PostgreSQL數(shù)據(jù)庫(kù)管理系統(tǒng)）： 4、 Pgsql 對(duì)于每種數(shù)據(jù)庫(kù)管理系統(tǒng)，都會(huì)提供一個(gè)命令行管理接口，例如Oracle的sqlplus，SQL Server的isql和osql等。凡是用圖形管理界面可以實(shí)現(xiàn)的功能原則上都可以通過(guò)命令行界面命令實(shí)現(xiàn)。兩者各有優(yōu)缺點(diǎn)，使用場(chǎng)合不同。在windows下當(dāng)然常用圖形管理界面，因?yàn)樵趫D像管理界面中往往都嵌有命令行工具，而在unix和linux下，當(dāng)然就常用命令行工具了，除了我們?cè)陬?lèi)unix下主要使用字符界面的

11、原因外，還因?yàn)榇蟛糠智闆r下我們只能通過(guò)telnet或ssh工具遠(yuǎn)程連接服務(wù)器進(jìn)行操作，此時(shí)也只能使用命令行了。從開(kāi)始目錄打開(kāi)SQL shell（pgsql）輸入密碼得到如下圖界面： 5、 PostgreSQL常用數(shù)據(jù)類(lèi)型一、數(shù)值類(lèi)型1. 整數(shù)類(lèi)型： 類(lèi)型smallint、integer和bigint存儲(chǔ)各種范圍的全部是數(shù)字的數(shù)，也就是沒(méi)有小數(shù)部分的數(shù)字。試圖存儲(chǔ)超出范圍以外的數(shù)值將導(dǎo)致一個(gè)錯(cuò)誤。常用的類(lèi)型是integer，因?yàn)樗峁┝嗽诜秶?、存?chǔ)空間和性能之間的最佳平衡。一般只有在磁盤(pán)空間緊張的時(shí)候才使用smallint。而只有在integer的范圍不夠的時(shí)候才使用bigint，因?yàn)榍罢?in

12、teger)絕對(duì)快得多。2. 任意精度數(shù)值： 類(lèi)型numeric可以存儲(chǔ)最多1000位精度的數(shù)字并且準(zhǔn)確地進(jìn)行計(jì)算。因此非常適合用于貨幣金額和其它要求計(jì)算準(zhǔn)確的數(shù)量。不過(guò)，numeric類(lèi)型上的算術(shù)運(yùn)算比整數(shù)類(lèi)型或者浮點(diǎn)數(shù)類(lèi)型要慢很多。 numeric字段的最大精度和最大比例都是可以配置的。要聲明一個(gè)類(lèi)型為numeric的字段，你可以用下面的語(yǔ)法： NUMERIC(precision,scale) 比如數(shù)字23.5141的精度為6，而刻度為4。 在目前的PostgreSQL版本中，decimal和numeric是等效的。3. 浮點(diǎn)數(shù)類(lèi)型： 數(shù)據(jù)類(lèi)型real和double是不準(zhǔn)確的、犧牲精度的數(shù)

13、字類(lèi)型。不準(zhǔn)確意味著一些數(shù)值不能準(zhǔn)確地轉(zhuǎn)換成內(nèi)部格式并且是以近似的形式存儲(chǔ)的，因此存儲(chǔ)后再把數(shù)據(jù)打印出來(lái)可能顯示一些缺失。4. Serial(序號(hào))類(lèi)型： serial和bigserial類(lèi)型不是真正的類(lèi)型，只是為在表中設(shè)置唯一標(biāo)識(shí)做的概念上的便利。 CREATE TABLE tablename ( colname SERIAL ); 等價(jià)于 CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename( colname integer DEFAULT nextval('tablename_colname_seq')

14、 NOT NULL ); 這樣，我們就創(chuàng)建了一個(gè)整數(shù)字段并且把它的缺省數(shù)值安排為從一個(gè)序列發(fā)生器取值。應(yīng)用了一個(gè)NOT NULL約束以確?？罩挡粫?huì)被插入。在大多數(shù)情況下你可能還希望附加一個(gè)UNIQUE或者PRIMARY KEY約束避免意外地插入重復(fù)的數(shù)值，但這個(gè)不是自動(dòng)發(fā)生的。因此，如果你希望一個(gè)序列字段有一個(gè)唯一約束或者一個(gè)主鍵，那么你現(xiàn)在必須聲明，就像其它數(shù)據(jù)類(lèi)型一樣。 還需要另外說(shuō)明的是，一個(gè)serial類(lèi)型創(chuàng)建的序列在其所屬字段被刪除時(shí)，該序列也將被自動(dòng)刪除，但是其它情況下是不會(huì)被刪除的。因此，如果你想用同一個(gè)序列發(fā)生器同時(shí)給幾個(gè)字段提供數(shù)據(jù)，那么就應(yīng)該以獨(dú)立對(duì)象的方式創(chuàng)建該序列發(fā)生器

15、。2、 字符類(lèi)型SQL 定義了兩種基本的字符類(lèi)型，varchar(n)和char(n)，這里的n是一個(gè)正整數(shù)。兩種類(lèi)型都可以存儲(chǔ)最多n個(gè)字符長(zhǎng)的字串，試圖存儲(chǔ)更長(zhǎng)的字串到這些類(lèi)型的字段里會(huì)產(chǎn)生一個(gè)錯(cuò)誤，除非超出長(zhǎng)度的字符都是空白，這種情況下該字串將被截?cái)酁樽畲箝L(zhǎng)度。如果沒(méi)有長(zhǎng)度聲明，char等于char(1)，而varchar則可以接受任何長(zhǎng)度的字串。這里需要注意的是，如果是將數(shù)值轉(zhuǎn)換成char(n)或者varchar(n)，那么超長(zhǎng)的數(shù)值將被截?cái)喑蒼個(gè)字符，而不會(huì)拋出錯(cuò)誤。最后需要提示的是，這三種類(lèi)型之間沒(méi)有性能差別，只不過(guò)是在使用char類(lèi)型時(shí)增加了存儲(chǔ)尺寸。雖然在某些其它的數(shù)據(jù)庫(kù)系統(tǒng)里，

16、char(n)有一定的性能優(yōu)勢(shì)，但在PostgreSQL里沒(méi)有。在大多數(shù)情況下，應(yīng)該使用text或者varchar。3、 日期/時(shí)間類(lèi)型1. 日期/時(shí)間輸入: 任何日期或者時(shí)間的文本輸入均需要由單引號(hào)包圍，就象一個(gè)文本字符串一樣。日期格式時(shí)間格式時(shí)間戳類(lèi)型的有效輸入由一個(gè)日期和時(shí)間的聯(lián)接組成，后面跟著一個(gè)可選的時(shí)區(qū)。因此，1999-01-08 04:05:06和1999-01-08 04:05:06 -8:00都是有效的數(shù)值。2，示例在插入數(shù)據(jù)之前先查看datestyle系統(tǒng)變量的值：Sql: 輸出窗口5、 布爾類(lèi)型：PostgreSQL支持標(biāo)準(zhǔn)的SQL boolean數(shù)據(jù)類(lèi)型。boolean

17、只能有兩個(gè)狀態(tài)之一：真(True)或 假(False)。該類(lèi)型占用1個(gè)字節(jié)。"真"值的有效文本值是：TRUE ，'t' ，'true'， 'y' ，'yes' ，'1'而對(duì)于"假"而言，你可以使用下面這些：FALSE ，'f' ，'false' ，'n' ，'no' ，'0'6、 位串類(lèi)型：位串就是一串1和0的字串。它們可以用于存儲(chǔ)和視覺(jué)化位掩碼。我們有兩種類(lèi)型的SQL位類(lèi)型：bit(n)和bit

18、 varying(n); 這里的n是一個(gè)正整數(shù)。bit類(lèi)型的數(shù)據(jù)必須準(zhǔn)確匹配長(zhǎng)度n; 試圖存儲(chǔ)短些或者長(zhǎng)一些的數(shù)據(jù)都是錯(cuò)誤的。類(lèi)型bit varying數(shù)據(jù)是最長(zhǎng)n的變長(zhǎng)類(lèi)型；更長(zhǎng)的串會(huì)被拒絕。寫(xiě)一個(gè)沒(méi)有長(zhǎng)度的bit等效于bit(1)，沒(méi)有長(zhǎng)度的bit varying相當(dāng)于沒(méi)有長(zhǎng)度限制。 針對(duì)該類(lèi)型，最后需要提醒的是，如果我們明確地把一個(gè)位串值轉(zhuǎn)換成bit(n)，那么它的右邊將被截?cái)嗷蛘咴谟疫呇a(bǔ)齊零，直到剛好n位，而不會(huì)拋出任何錯(cuò)誤。類(lèi)似地，如果我們明確地把一個(gè)位串?dāng)?shù)值轉(zhuǎn)換成bit varying(n)，如果它超過(guò)n位，那么它的右邊將被截?cái)唷?見(jiàn)如下具體使用方式：這句sql執(zhí)行時(shí)就會(huì)報(bào)錯(cuò),因?yàn)?/p>

19、.a的數(shù)據(jù)的長(zhǎng)度是3,如果所插數(shù)據(jù)長(zhǎng)度不為三則報(bào)錯(cuò).注:(:)為轉(zhuǎn)換符7、 數(shù)組類(lèi)型：1. 數(shù)組類(lèi)型聲明： 創(chuàng)建字段含有數(shù)組類(lèi)型的表。 插入數(shù)組數(shù)據(jù)2查詢(xún)數(shù)組數(shù)據(jù)和其他語(yǔ)言一樣，PostgreSQL中數(shù)組也是通過(guò)下標(biāo)數(shù)字(寫(xiě)在方括弧內(nèi))的方式進(jìn)行訪(fǎng)問(wèn)，只是PostgreSQL中數(shù)組元素的下標(biāo)是從1開(kāi)始n結(jié)束。PostgreSQL中還提供了訪(fǎng)問(wèn)數(shù)組范圍的功能，即ARRAY腳標(biāo)下界:腳標(biāo)上界。3. 修改數(shù)組數(shù)據(jù)代替全部數(shù)組值：更新數(shù)組中某一元素：更新數(shù)組某一范圍的元素：直接賦值擴(kuò)大數(shù)組：8、 復(fù)合類(lèi)型PostgreSQL中復(fù)合類(lèi)型有些類(lèi)似于C語(yǔ)言中的結(jié)構(gòu)體，也可以被視為Oracle中的記錄類(lèi)型，但

20、是還是感覺(jué)復(fù)合類(lèi)型這個(gè)命名比較貼切。它實(shí)際上只是一個(gè)字段名和它們的數(shù)據(jù)類(lèi)型的列表。PostgreSQL允許像簡(jiǎn)單數(shù)據(jù)類(lèi)型那樣使用復(fù)合類(lèi)型。比如，表字段可以聲明為一個(gè)復(fù)合類(lèi)型。CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric );和聲明一個(gè)數(shù)據(jù)表相比，聲明類(lèi)型時(shí)需要加AS關(guān)鍵字，同時(shí)在聲明TYPE時(shí)不能定義任何約束。下面我們看一下如何在表中指定復(fù)合類(lèi)型的字段，如：CREATE TABLE on_hand ( item inventory_item, count integer);最后需要指出

21、的是，在創(chuàng)建表的時(shí)候，PostgreSQL也會(huì)自動(dòng)創(chuàng)建一個(gè)與該表對(duì)應(yīng)的復(fù)合類(lèi)型，名字與表字相同，即表示該表的復(fù)合類(lèi)型。復(fù)合類(lèi)型值輸入： 我們可以使用文本常量的方式表示復(fù)合類(lèi)型值，即在圓括號(hào)里包圍字段值并且用逗號(hào)分隔它們。你也可以將任何字段值用雙引號(hào)括起，如果值本身包含逗號(hào)或者圓括號(hào)，那么就用雙引號(hào)括起，對(duì)于上面的inventory_item復(fù)合類(lèi)型的輸入如下：'("fuzzy dice",42,1.99)'如果希望類(lèi)型中的某個(gè)字段為NULL，只需在其對(duì)應(yīng)的位置不予輸入即可，如下面的輸入中price字段的值為NULL， '("fuzzy di

22、ce",42,)' 如果只是需要一個(gè)空字串，而非NULL，寫(xiě)一對(duì)雙引號(hào)，如： '("",42,)'在更多的場(chǎng)合中PostgreSQL推薦使用ROW表達(dá)式來(lái)構(gòu)建復(fù)合類(lèi)型值，使用該種方式相對(duì)簡(jiǎn)單，無(wú)需考慮更多標(biāo)識(shí)字符問(wèn)題，如： ROW('fuzzy dice', 42, 1.99) ROW('', 42, NULL) 注：對(duì)于ROW表達(dá)式，如果里面的字段數(shù)量超過(guò)1個(gè)，那么關(guān)鍵字ROW就可以省略，因此以上形式可以簡(jiǎn)化為： ('fuzzy dice', 42, 1.99) ('', 4

23、2, NULL)訪(fǎng)問(wèn)復(fù)合類(lèi)型： 訪(fǎng)問(wèn)復(fù)合類(lèi)型中的字段和訪(fǎng)問(wèn)數(shù)據(jù)表中的字段在形式上極為相似，只是為了對(duì)二者加以區(qū)分，PostgreSQL設(shè)定在訪(fǎng)問(wèn)復(fù)合類(lèi)型中的字段時(shí)，類(lèi)型部分需要用圓括號(hào)括起，以避免混淆，如： SELECT (item).name FROM on_hand WHERE (item).price > 9.99; 如果在查詢(xún)中也需要用到表名，那么表名和類(lèi)型名都需要被圓括號(hào)括起，如： SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;修改復(fù)合類(lèi)型： 見(jiàn)如下幾個(gè)示例： -直接插入

24、復(fù)合類(lèi)型的數(shù)據(jù)，這里是通過(guò)ROW表達(dá)式來(lái)完成的。 INSERT INTO on_hand(item) VALUES(ROW("fuzzy dice",42,1.99); -在更新操作中，也是可以通過(guò)ROW表達(dá)式來(lái)完成。 UPDATE on_hand SET item = ROW("fuzzy dice",42,1.99) WHERE count = 0; -在更新復(fù)合類(lèi)型中的一個(gè)字段時(shí)，我們不能在SET后面出現(xiàn)的字段名周?chē)訄A括號(hào)， 但是在等號(hào)右邊的表達(dá)式里引用同一個(gè)字段時(shí)卻需要圓括號(hào)。 UPDATE on_hand SET item.price = (item).price + 1 WHERE count = 0; -可以在插入中，直接插入復(fù)合類(lèi)型中字段。 INSERT INTO on_hand (item.supplier_id, item.price) VALUES(100, 2.2);九 postgreS