PostgreSQL教程(一)：數(shù)據(jù)表詳解

2020-03-12 23:53:34

字體：大中小

供稿：網(wǎng)友

這篇文章主要介紹了PostgreSQL教程(一)：數(shù)據(jù)表詳解表的定義、系統(tǒng)字段、表的修改、表的權(quán)限等4大部份內(nèi)容,內(nèi)容種包括表的創(chuàng)建、刪除、修改、字段的修改、刪除、主鍵和外鍵、約束添加修改刪除等,本文講解了,需要的朋友可以參考下

一、表的定義：

對(duì)于任何一種關(guān)系型數(shù)據(jù)庫(kù)而言，表都是數(shù)據(jù)存儲(chǔ)的最核心、最基礎(chǔ)的對(duì)象單元?，F(xiàn)在就讓我們從這里起步吧。
1. 創(chuàng)建表：

復(fù)制代碼代碼如下:

		
		    CREATE TABLE products (
		        product_no integer,
		        name text,
		        price numeric
		    );
		   

2. 刪除表：

復(fù)制代碼代碼如下:

DROP TABLE products;

3. 創(chuàng)建帶有缺省值的表：

復(fù)制代碼代碼如下:

		
		    CREATE TABLE products (
		        product_no integer,
		        name text,
		        price numeric DEFAULT 9.99 --DEFAULT是關(guān)鍵字，其后的數(shù)值9.99是字段price的默認(rèn)值。
		    );
		    
		    CREATE TABLE products (
		        product_no SERIAL,            --SERIAL類型的字段表示該字段為自增字段，完全等同于Oracle中的Sequence。
		        name text,
		        price numeric DEFAULT 9.99
		    );
		 

輸出為：

復(fù)制代碼代碼如下:

		
		    NOTICE:  CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no"
		   

4. 約束：
檢查約束是表中最為常見的約束類型，它允許你聲明在某個(gè)字段里的數(shù)值必須滿足一個(gè)布爾表達(dá)式。不僅如此，我們也可以聲明表級(jí)別的檢查約束。

復(fù)制代碼代碼如下:

		
		    CREATE TABLE products (
		        product_no integer,
		        name text,
		        --price字段的值必須大于0，否則在插入或修改該字段值是，將引發(fā)違規(guī)錯(cuò)誤。還需要說明的是，該檢查約束
		        --是匿名約束，即在表定義時(shí)沒有顯示命名該約束，這樣PostgreSQL將會(huì)根據(jù)當(dāng)前的表名、字段名和約束類型，
		        --為該約束自動(dòng)命名，如：products_price_check。
		        price numeric CHECK (price > 0) 
		    );
		  
		    CREATE TABLE products (
		        product_no integer,
		        name text,
		        --該字段的檢查約束被顯示命名為positive_price。這樣做好處在于今后維護(hù)該約束時(shí)，可以根據(jù)該名進(jìn)行直接操作。
		        price numeric CONSTRAINT positive_price CHECK (price > 0) 
		    );
		 

下面的約束是非空約束，即約束的字段不能插入空值，或者是將已有數(shù)據(jù)更新為空值。

復(fù)制代碼代碼如下:

		
		    CREATE TABLE products (
		        product_no integer NOT NULL,
		        name text NOT NULL,
		        price numeric
		    );
		 

如果一個(gè)字段中存在多個(gè)約束，在定義時(shí)可以不用考慮約束的聲明順序。

復(fù)制代碼代碼如下:

		
		    CREATE TABLE products (
		        product_no integer NOT NULL,
		        name text NOT NULL,
		        price numeric NOT NULL CHECK (price > 0)
		    );
		 

唯一性約束，即指定的字段不能插入重復(fù)值，或者是將某一記錄的值更新為當(dāng)前表中的已有值。

復(fù)制代碼代碼如下:

    CREATE TABLE products (
        product_no integer UNIQUE,
        name text,
        price numeric
    );

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric,
        UNIQUE (product_no)
    );

為表中的多個(gè)字段定義聯(lián)合唯一性。

復(fù)制代碼代碼如下:

		
		    CREATE TABLE example (
		        a integer,
		        b integer,
		        c integer,
		        UNIQUE (a, c)
		    );
		 

為唯一性約束命名。

復(fù)制代碼代碼如下:

		
		    CREATE TABLE products (
		        product_no integer CONSTRAINT must_be_different UNIQUE,
		        name text,
		        price numeric
		    );
		 

    在插入數(shù)據(jù)時(shí)，空值(NULL)之間被視為不相等的數(shù)據(jù)，因此對(duì)于某一唯一性字段，可以多次插入空值。然而需要注意的是，這一規(guī)則并不是被所有數(shù)據(jù)庫(kù)都遵守，因此在進(jìn)行數(shù)據(jù)庫(kù)移植時(shí)可能會(huì)造成一定的麻煩。

    5. 主鍵和外鍵：
    從技術(shù)上來講，主鍵約束只是唯一約束和非空約束的組合。

復(fù)制代碼代碼如下:

		
		    CREATE TABLE products (
		        product_no integer PRIMARY KEY,  --字段product_no被定義為該表的唯一主鍵。
		        name text,
		        price numeric
		    );
		 

和唯一性約束一樣，主鍵可以同時(shí)作用于多個(gè)字段，形成聯(lián)合主鍵：

復(fù)制代碼代碼如下:

		
		    CREATE TABLE example (
		        a integer,
		        b integer,
		        c integer,
		        PRIMARY KEY (b, c)
		    );
		 

外鍵約束聲明一個(gè)字段（或者一組字段）的數(shù)值必須匹配另外一個(gè)表中某些行出現(xiàn)的數(shù)值。我們把這個(gè)行為稱做兩個(gè)相關(guān)表之間的參考完整性。

復(fù)制代碼代碼如下:

		
		    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,
		        --該外鍵的字段數(shù)量和被引用表中主鍵的數(shù)量必須保持一致。
		        FOREIGN KEY (b, c) REFERENCES example (b, c)
		    );   
		 

當(dāng)多個(gè)表之間存在了主外鍵的參考性約束關(guān)系時(shí)，如果想刪除被應(yīng)用表(主鍵表)中的某行記錄，由于該行記錄的主鍵字段值可能正在被其引用表(外鍵表)中某條記錄所關(guān)聯(lián)，所以刪除操作將會(huì)失敗。如果想完成此操作，一個(gè)顯而易見的方法是先刪除引用表中和該記錄關(guān)聯(lián)的行，之后再刪除被引用表中的該行記錄。然而需要說明的是，PostgreSQL為我們提供了更為方便的方式完成此類操作。

復(fù)制代碼代碼如下:

		
		    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, --限制選項(xiàng)
		        order_id integer REFERENCES orders ON DELETE CASCADE, --級(jí)聯(lián)刪除選項(xiàng)
		        quantity integer,
		        PRIMARY KEY (product_no, order_id)
		    );
		 

限制和級(jí)聯(lián)刪除是兩種最常見的選項(xiàng)。RESTRICT 禁止刪除被引用的行。 NO ACTION 的意思是如果在檢查約束的時(shí)候，如果還存在任何引用行，則拋出錯(cuò)誤；如果你不聲明任何東西，那么它就是缺省的行為。(這兩個(gè)選擇的實(shí)際區(qū)別是，NO ACTION 允許約束檢查推遲到事務(wù)的晚些時(shí)候，而 RESTRICT 不行。) CASCADE聲明在刪除一個(gè)被引用的行的時(shí)候，引用它的行也會(huì)被自動(dòng)刪除掉。在外鍵字段上的動(dòng)作還有兩個(gè)選項(xiàng)： SET NULL 和 SET DEFAULT。這樣會(huì)導(dǎo)致在被引用行刪除的時(shí)候，引用它們的字段分別設(shè)置為空或者缺省值。請(qǐng)注意這些選項(xiàng)并不能讓你逃脫被觀察和約束的境地。比如，如果一個(gè)動(dòng)作聲明 SET DEFAULT，但是缺省值并不能滿足外鍵，那么動(dòng)作就會(huì)失敗。類似ON DELETE，還有ON UPDATE 選項(xiàng)，它是在被引用字段修改(更新)的時(shí)候調(diào)用的?？捎玫膭?dòng)作是一樣的。

二、系統(tǒng)字段：

    PostgreSQL的每個(gè)數(shù)據(jù)表中都包含幾個(gè)隱含定義的系統(tǒng)字段。因此，這些名字不能用于用戶定義的字段名。這些系統(tǒng)字段的功能有些類似于Oracle中的rownum和rowid等。
    oid: 行的對(duì)象標(biāo)識(shí)符(對(duì)象ID)。這個(gè)字段只有在創(chuàng)建表的時(shí)候使用了WITH OIDS，或者是設(shè)置了配置參數(shù)default_with_oids時(shí)出現(xiàn)。這個(gè)字段的類型是oid(和字段同名)。
    tableoid: 包含本行的表的OID。這個(gè)字段對(duì)那些從繼承層次中選取的查詢特別有用，因?yàn)槿绻麤]有它的話，我們就很難說明一行來自哪個(gè)獨(dú)立的表。tableoid可以和pg_class的oid字段連接起來獲取表名字。
    xmin: 插入該行版本的事務(wù)的標(biāo)識(shí)(事務(wù)ID)。
    cmin: 在插入事務(wù)內(nèi)部的命令標(biāo)識(shí)(從零開始)。
    xmax: 刪除事務(wù)的標(biāo)識(shí)(事務(wù)ID)，如果不是被刪除的行版本，那么是零。
    cmax: 在刪除事務(wù)內(nèi)部的命令標(biāo)識(shí)符，或者是零。
    ctid: 一個(gè)行版本在它所處的表內(nèi)的物理位置。請(qǐng)注意，盡管ctid可以用于非?？焖俚囟ㄎ恍邪姹荆看蜼ACUUM FULL之后，一個(gè)行的ctid都會(huì)被更新或者移動(dòng)。因此ctid是不能作為長(zhǎng)期的行標(biāo)識(shí)符的。
    OID是32位的量，是在同一個(gè)集群內(nèi)通用的計(jì)數(shù)器上賦值的。對(duì)于一個(gè)大型或者長(zhǎng)時(shí)間使用的數(shù)據(jù)庫(kù)，這個(gè)計(jì)數(shù)器是有可能重疊的。因此，假設(shè)OID是唯一的是非常錯(cuò)誤的，除非你自己采取了措施來保證它們是唯一的。如果你需要標(biāo)識(shí)表中的行，我們強(qiáng)烈建議使用序列號(hào)生成器。

三、表的修改：

1. 增加字段：

復(fù)制代碼代碼如下:

ALTER TABLE products ADD COLUMN description text;

新增的字段對(duì)于表中已經(jīng)存在的行而言最初將先填充所給出的缺省值(如果你沒有聲明DEFAULT子句，那么缺省是空值)。
在新增字段時(shí)，可以同時(shí)給該字段指定約束。

復(fù)制代碼代碼如下:

ALTER TABLE products ADD COLUMN description text CHECK(description <> '');

2. 刪除字段：

復(fù)制代碼代碼如下:

ALTER TABLE products DROP COLUMN description;

如果該表為被引用表，該字段為被引用字段，那么上面的刪除操作將會(huì)失敗。如果要想在刪除被引用字段的同時(shí)級(jí)聯(lián)的刪除其所有引用字段，可以采用下面的語(yǔ)法形式。

復(fù)制代碼代碼如下:

ALTER TABLE products DROP COLUMN description CASCADE;

3. 增加約束：

復(fù)制代碼代碼如下:

		
		    ALTER TABLE products ADD CHECK(name <> '');  --增加一個(gè)表級(jí)約束
		    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; --增加一個(gè)非空約束。
		   

4. 刪除約束：

復(fù)制代碼代碼如下:

ALTER TABLE products DROP CONSTRAINT some_name;

對(duì)于顯示命名的約束，可以根據(jù)其名稱直接刪除，對(duì)于隱式自動(dòng)命名的約束，可以通過psql的/d tablename來獲取該約束的名字。和刪除字段一樣，如果你想刪除有著被依賴關(guān)系地約束，你需要用CASCADE。一個(gè)例子是某個(gè)外鍵約束依賴被引用字段上的唯一約束或者主鍵約束。如：

復(fù)制代碼代碼如下:

		
		    MyTest=# /d products
		         Table "public.products"
		       Column     |  Type   | Modifiers
		     ------------+---------+-----------
		     product_no | integer |
		     name          | text    |
		     price           | numeric |
		     Check constraints:
		        "positive_price" CHECK (price > 0::numeric)

和其他約束不同的是，非空約束沒有名字，因此只能通過下面的方式刪除：

復(fù)制代碼代碼如下:

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

5. 改變字段的缺省值:
在為已有字段添加缺省值時(shí)，不會(huì)影響任何表中現(xiàn)有的數(shù)據(jù)行，它只是為將來INSERT命令改變?nèi)笔≈怠?br />

復(fù)制代碼代碼如下:

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

下面為刪除缺省值：

復(fù)制代碼代碼如下:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT

    6. 修改字段的數(shù)據(jù)類型：
    只有在字段里現(xiàn)有的每個(gè)項(xiàng)都可以用一個(gè)隱含的類型轉(zhuǎn)換轉(zhuǎn)換成新的類型時(shí)才可能成功。比如當(dāng)前的數(shù)據(jù)都是整型，而轉(zhuǎn)換的目標(biāo)類型為numeric或varchar，這樣的轉(zhuǎn)換一般都可以成功。與此同時(shí)，PostgreSQL還將試圖把字段的缺省值（如果存在）轉(zhuǎn)換成新的類型，還有涉及該字段的任何約束。但是這些轉(zhuǎn)換可能失敗，或者可能生成奇怪的結(jié)果。在修改某字段類型之前，你最好刪除那些約束，然后再把自己手工修改過的添加上去。

復(fù)制代碼代碼如下:

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

7. 修改字段名：

復(fù)制代碼代碼如下:

ALTER TABLE products RENAME COLUMN product_no TO product_number;

8. 修改表名：

復(fù)制代碼代碼如下:

ALTER TABLE products RENAME TO items;

四、權(quán)限：

只有表的所有者才能修改或者刪除表的權(quán)限。要賦予一個(gè)權(quán)限，我們使用GRANT命令，要撤銷一個(gè)權(quán)限，使用REVOKE命令。
需要指出的是，PUBLIC是特殊"用戶"可以用于將權(quán)限賦予系統(tǒng)中的每一個(gè)用戶。在聲明權(quán)限的位置寫ALL則將所有的與該對(duì)象類型相關(guān)的權(quán)限都賦予出去。

復(fù)制代碼代碼如下:

		
		    GRANT UPDATE ON table_name TO user;  --將表的更新權(quán)限賦予指定的user。
		    GRANT SELECT ON table_name TO GROUP group; --將表的select權(quán)限賦予指定的組。
		    REVOKE ALL ON table_name FROM PUBLIC; --將表的所有權(quán)限從Public撤銷。
		 

最初，只有對(duì)象所有者(或者超級(jí)用戶)可以賦予或者撤銷對(duì)象的權(quán)限。但是，我們可以賦予一個(gè)"with grant option"權(quán)限，這樣就給接受權(quán)限的人以授予該權(quán)限給其它人的權(quán)限。如果授予選項(xiàng)后來被撤銷，那么所有那些從這個(gè)接受者接受了權(quán)限的用戶(直接或者通過級(jí)連的授權(quán))都將失去該權(quán)限。

這里需要特別說明的是，該博客中的大部分案例和段落均取自于PostgreSQL中文文檔，如轉(zhuǎn)載本系列博客，請(qǐng)同樣注明該出處。

上一篇：PostgreSQL教程(二)：模式Schema詳解

下一篇：mysql、mssql及oracle分頁(yè)查詢方法詳解