PostgreSQL介紹以及如何開發框架中使用PostgreSQL數據庫，框架postgresql

PostgreSQL介紹以及如何開發框架中使用PostgreSQL數據庫，框架postgresql

最近准備下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層了。