PostgreSQL介紹以及如何開發架構中使用PostgreSQL資料庫

標籤：current   不能   文法   gre   ast   extension   images   開發架構   com   

最近準備下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資料庫