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
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類