postgresql 初學筆記

2019-11-08 20:46:56

字體：大中小

來源：轉載

供稿：網友

一、數據庫1.查看用戶所有的數據庫 /l2 創建數據庫 create database 數據庫名3.選擇數據庫 /c [數據庫名]4.刪除數據庫 drop database [數據庫名]一、表的定義：1. 創建表： CREATE TABLE PRoducts ( product_no SERIAL paimary key, --SERIAL類型的字段表示該字段為自增字段，完全等同于Oracle中的Sequence。 name text, price numeric DEFAULT 9.99 );2. 刪除表：DROP TABLE products;3. 約束： 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)之間被視為不相等的數據，因此對于某一唯一性字段，可以多次插入空值。然而需要注意的是，這一規則并不是被所有數據庫都遵守，因此在進行數據庫移植時可能會造成一定的麻煩。 3. 主鍵和外鍵：從技術上來講，主鍵約束只是唯一約束和非空約束的組合。 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 選項，它是在被引用字段修改(更新)的時候調用的?？捎玫膭幼魇且粯拥摹６⑾到y字段： 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可以用于非?？焖俚囟ㄎ恍邪姹荆看蜼ACUUM 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撤銷。五、創建用戶（create role 和 create user是一樣的）：創建角色（默認沒有登錄權限）create role xxx;create role xxx with superuser;創建角色（默認有登錄權限）create user csm with superuser passWord 'csm'; -- 創建超級用戶create user csm_ca with password 'csm_ca';-- 創建普通用戶六、數據庫的導出導入導出：pg_dump -U postgres(用戶名) -t 表名數據庫名(缺省時同用戶名) > c:/fulldb.sql -- 導出某個表pg_dump -U postgres(用戶名) 數據庫名(缺省時同用戶名) > c:/fulldb.sql -- 導出庫導入：psql -d 數據庫名 -U postgres(用戶名) -f E:/dump.sql(文件地址)psql -U postgres(用戶名) 數據庫名(缺省時同用戶名) < /data/dum.sql七、psql常用內部命令/q 退出 psql/h [名稱] SQL命令語法上的說明，用*顯示全部命令的語法說明/w 文件將查詢緩存區的內容寫入文件/d[S+] 列出表,視圖和序列/d[S+] 名稱描述表，視圖，序列，或索引/da[S] [模式] 列出聚合函數/db[+] [模式] 列出表空間/dc[S+] [PATTERN] 列表轉換/dC[+] [PATTERN] 列出類型強制轉換/dd[S] [PATTERN] 顯示沒有在別處顯示的對象描述/ddp [模式] 列出默認權限/dg[+] [PATTERN] 列出角色/di[S+] [模式] 列出索引/dl 列出大對象，功能與/lo_list相同/dL[S+] [PATTERN] 列出所有過程語言/dm[S+] [PATTERN] 列出所有物化視圖/dn[S+] [PATTERN] 列出所有模式/do[S] [模式] 列出運算符/dO[S+] [PATTERN] 列出所有校對規則/dp [模式] 列出表，視圖和序列的訪問權限/drds [模式1 [模式2]] 列出每個數據庫的角色設置/ds[S+] [模式] 列出序列/dt[S+] [模式] 列出表/dT[S+] [模式] 列出數據類型/du[+] [PATTERN] 列出角色/dv[S+] [模式] 列出視圖/dE[S+] [PATTERN] 列出引用表/dx[+] [PATTERN] 列出擴展/dy [PATTERN] 列出所有事件觸發器/l[+] [PATTERN] 列出所有數據庫

上一篇：數據庫SQL優化大總結之百萬級數據庫優化方案

下一篇：MySQL性能優化的最佳20多條經驗分享