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

来源:http://www.cnblogs.com/wuhuacong/archive/2016/10/29/6012032.html
-Advertisement-
Play Games

最近準備下PostgreSQL資料庫開發的相關知識，本文把總結的PPT內容通過博客記錄分享，本隨筆的主要內容是介紹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層了。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • .NET4.5新特性之非同步編程（Async和Await）的使用
    一、簡介 首先來看看.net的發展中的各個階段的特性：NET 與C# 的每個版本發佈都是有一個“主題”。即：C#1.0托管代碼→C#2.0泛型→C#3.0LINQ→C#4.0動態語言→C#4.5非同步編程 所謂的非同步編程是利用CPU空閑時間和多核的特性，它所返回的Task或Task是對await的一個 ...
  • <<ABP框架>> OData 集成
    文檔目錄 本節內容： 簡介 安裝 安裝Nuget包 設置模塊依賴 配置你的實體 創建控制器 示例 獲取實體列表 請求 響應 獲取單個實體 請求 響應 獲取單個實體及導航屬性 請求 響應 查詢 請求 響應 創建一個新實體 請求 響應 獲取元數據 請求 響應 示例項目 安裝Nuget包 設置模塊依賴 配 ...
  • 關於Quartz.NET作業調度框架的一點小小的封裝，實現偽AOP寫LOG功能
    Quartz.NET是一個非常強大的作業調度框架，適用於各種定時執行的業務處理等，類似於WINDOWS自帶的任務計劃程式，其中運用Cron表達式來實現各種定時觸發條件是我認為最為驚喜的地方。 Quartz.NET主要用到下麵幾個類： IScheduler --調度器 IJobDetail --作業任 ...
  • ref、out
    using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ref_out { class Program { static void Method1(ref int ...
  • <<ABP框架>> 動態Web Api層
    文檔目錄 本節內容： 創建動態Web Api控制器 ForAll 方法 重寫 ForAll ForMethods Http 動詞 WithVerb 方法 HTTP 特性 命名約定 Api 瀏覽器 RemoteService 特性 動態Javascript代理 AJAX 參數 單獨服務腳本 Angul ...
  • 值類型，引用類型，棧，堆，ref，out
    在網上收集。。。 C#的值類型，引用類型，棧，堆，ref，out C# 的類型系統可分為兩種類型，一是值類型，一是引用類型，這個每個C#程式員都瞭解。還有托管堆，棧，ref，out等等概念也是每個C#程式員都會接觸到的概念，也是C#程式員面試經常考到的知識，隨便搜搜也有無數的文章講解相關的概念，貌似 ...
  • <<ABP文檔>> Web Api 控制器
    文檔目錄 本節內容： 簡介 AbpApiController 基類 本地化 其它 過濾 審計日誌 授權 防偽造過濾 工作單元 結果包裝和異常處理 結果緩存 驗證 模塊綁定器 本地化 其它 審計日誌 授權 防偽造過濾 工作單元 結果包裝和異常處理 結果緩存 驗證 簡介 通過Abp.Web.Api的nu ...
  • 如何配置Log4Net使用Oracle資料庫記錄日誌
    最近在做一個項目的時候，需要增加一個日誌的功能，需要使用Log4Net記錄日誌，把數據插入到Oracle資料庫，經過好久的研究終於成功了。把方法記錄下來，以備以後查詢。 直接寫實現方法，分兩步完成： 1、使用NuGet Manager管理工具，增加對Oracle.ManagedDataAccess. ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類