### [PostgreSQL學習手冊(數據表)](http://www.cnblogs.com/stephen-liu74/archive/2012/04/23/2290803.html) Posted on?2012-04-23 07:42?[Stephen_Liu](http://www.cnblogs.com/stephen-liu74/)?閱讀(2248) 評論(9)?[編輯](http://www.cnblogs.com/stephen-liu74/admin/EditPosts.aspx?postid=2290803)?[收藏](http://www.cnblogs.com/stephen-liu74/archive/2012/04/23/2290803.html#)? **一、表的定義：** ?? ?對于任何一種關系型數據庫而言，表都是數據存儲的最核心、最基礎的對象單元。現在就讓我們從這里起步吧。 ?? ?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 ?? ?); ?? ?輸出為： ?? ?NOTICE:? CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no" ???? ?? ?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 ?? ?); ?? ?如果一個字段中存在多個約束，在定義時可以不用考慮約束的聲明順序。 **?? ?CREATE TABLE**?products ( ?? ???? product_no integer?**NOT NULL**, ?? ???? name text?**NOT NULL**, ?? ???? price numeric?**NOT NULL CHECK**?(price > 0) ?? ?); ?? ?唯一性約束，即指定的字段不能插入重復值，或者是將某一記錄的值更新為當前表中的已有值。 **?? ?CREATE TABLE**?products ( ?? ???? product_no integer?**UNIQUE**, ?? ???? name text, ?? ???? price numeric ?? ?); **?? ?CREATE TABLE**?products ( ?? ???? product_no integer, ?? ???? name text, ?? ???? price numeric, **?? ???? UNIQUE**?(product_no) ?? ?); ?? ?為表中的多個字段定義聯合唯一性。 **?? ?CREATE TABLE**?example ( ?? ???? a integer, ?? ???? b integer, ?? ???? c integer, **?? ???? UNIQUE**?(a, c) ?? ?); ?? ?為唯一性約束命名。 **?? ?CREATE TABLE**?products ( ?? ???? product_no integer?**CONSTRAINT**?must_be_different?**UNIQUE**, ?? ???? name text, ?? ???? price numeric ?? ?); ?? ?在插入數據時，空值(NULL)之間被視為不相等的數據，因此對于某一唯一性字段，可以多次插入空值。然而需要注意的是，這一規則并不是被所有數據庫都遵守，因此在進行數據庫移植時可能會造成一定的麻煩。 ???? ?? ?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 ?? ?); ???? **?? ?CREATE TABLE**?t1 ( ?? ??? ?a integer?**PRIMARY KEY**, ?? ??? ?b integer, ?? ??? ?c integer, *?? ??? ?--該外鍵的字段數量和被引用表中主鍵的數量必須保持一致。* **?? ??? ?FOREIGN KEY**?(b, c)?**REFERENCES**?example (b, c) ?? ?);??? ?? ?當多個表之間存在了主外鍵的參考性約束關系時，如果想刪除被應用表(主鍵表)中的某行記錄，由于該行記錄的主鍵字段值可能正在被其引用表(外鍵表)中某條記錄所關聯，所以刪除操作將會失敗。如果想完成此操作，一個顯而易見的方法是先刪除引用表中和該記錄關聯的行，之后再刪除被引用表中的該行記錄。然而需要說明的是，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 選項，它是在被引用字段修改(更新)的時候調用的。可用的動作是一樣的。 **二、系統字段：** ?? ?PostgreSQL的每個數據表中都包含幾個隱含定義的系統字段。因此，這些名字不能用于用戶定義的字段名。這些系統字段的功能有些類似于Oracle中的rownum和rowid等。 ?? ?**oid:**?行的對象標識符(對象ID)。這個字段只有在創建表的時候使用了WITH OIDS，或者是設置了配置參數default_with_oids時出現。這個字段的類型是oid(和字段同名)。? ?? ?**tableoid:**?包含本行的表的OID。這個字段對那些從繼承層次中選取的查詢特別有用，因為如果沒有它的話，我們就很難說明一行來自哪個獨立的表。tableoid可以和pg_class的oid字段連接起來獲取表名字。? ?? ?**xmin:**?插入該行版本的事務的標識(事務ID)。 ?? ?**cmin:**?在插入事務內部的命令標識(從零開始)。? ?? ?**xmax:**?刪除事務的標識(事務ID)，如果不是被刪除的行版本，那么是零。 ?? ?**cmax:**?在刪除事務內部的命令標識符，或者是零。? ?? ?**ctid:**?一個行版本在它所處的表內的物理位置。請注意，盡管ctid可以用于非常快速地定位行版本，但每次VACUUM FULL之后，一個行的ctid都會被更新或者移動。因此ctid是不能作為長期的行標識符的。???? ?? ?OID是32位的量，是在同一個集群內通用的計數器上賦值的。對于一個大型或者長時間使用的數據庫，這個計數器是有可能重疊的。因此，假設OID是唯一的是非常錯誤的，除非你自己采取了措施來保證它們是唯一的。如果你需要標識表中的行，我們強烈建議使用序列號生成器。 ?? ? ???? **三、表的修改：** ?? ?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**?some_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。一個例子是某個外鍵約束依賴被引用字段上的唯一約束或者主鍵約束。如： ???*?MyTest=# \d products* ?? ????? Table "public.products" ?? ??? Column???? |? Type?? | Modifiers ?? ? ------------+---------+----------- ?? ? product_no | integer | ?? ? name????? ? ? | text??? | ?? ? price?? ? ??? ? | numeric | ?? ? Check constraints: ?? ?????*"positive_price"*?CHECK (price > 0::numeric) ?? ?和其他約束不同的是，非空約束沒有名字，因此只能通過下面的方式刪除： ?**? ?ALTER TABLE**?products**?ALTER COLUMN**?product_no?**DROP NOT NULL**; ?? ? ?? ?5. 改變字段的缺省值: ?? ?在為已有字段添加缺省值時，不會影響任何表中現有的數據行， 它只是為將來INSERT命令改變缺省值。 **?? ?ALTER TABLE**?products?**ALTER COLUMN**?price?**SET DEFAULT**?7.77; ?? ?下面為刪除缺省值： ???**?ALTER TABLE**?products?**ALTER COLUMN**?price?**DROP DEFAULT** ???? ????6. 修改字段的數據類型： ?? ?只有在字段里現有的每個項都可以用一個隱含的類型轉換轉換成新的類型時才可能成功。比如當前的數據都是整型，而轉換的目標類型為numeric或varchar，這樣的轉換一般都可以成功。與此同時，PostgreSQL還將試圖把字段的缺省值（如果存在）轉換成新的類型， 還有涉及該字段的任何約束。但是這些轉換可能失敗，或者可能生成奇怪的結果。 在修改某字段類型之前，你最好刪除那些約束，然后再把自己手工修改過的添加上去。? **?? ?ALTER TABLE**?products?**ALTER COLUMN**?price?**TYPE**?numeric(10,2); ?? ?? ? ?? ?7. 修改字段名： **?? ?ALTER TABLE**?products?**RENAME COLUMN**?product_no?**TO**?product_number; ?? ? ?? ?8. 修改表名： **?? ?ALTER TABLE**?products**?RENAME TO**?items; ???? **四、權限：** ?? ?只有表的所有者才能修改或者刪除表的權限。要賦予一個權限，我們使用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"**權限，這樣就給接受權限的人以授予該權限給其它人的權限。如果授予選項后來被撤銷，那么所有那些從這個接受者接受了權限的用戶(直接或者通過級連的授權)都將失去該權限。 ?*? ?這里需要特別說明的是，該博客中的大部分案例和段落均取自于PostgreSQL中文文檔，如轉載本系列博客，請同樣注明該出處。* 分類:?[PostgreSQL](http://www.cnblogs.com/stephen-liu74/category/343171.html)