PostgreSQL 新增、刪除資料表

基本語法

• 建立資料表基本 SQL 語法：
  

  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name (
   [
    { column_name data_type [ COLLATE 定序 ] [ column_constraint [ ... ] ]
      | table_constraint
      | LIKE source_table [ like_option ... ] 
     }
      [, ... ]
   ] )
  
  [ INHERITS ( parent_table [, ... ] ) ]
  [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
  [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
  [ TABLESPACE tablespace_name ]
  
  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
      OF type_name [ (
    { column_name WITH OPTIONS [ column_constraint [ ... ] ]
      | table_constraint }
      [, ... ]
  ) ]
  
  [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
  [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
  [ TABLESPACE tablespace_name ]
  
  其中 column_constraint 是:
  
  [ CONSTRAINT constraint_name ]
  { NOT NULL |
    NULL |
    CHECK ( expression ) [ NO INHERIT ] |
    DEFAULT default_expr |
    UNIQUE index_parameters |
    PRIMARY KEY index_parameters |
    REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
  [ ON DELETE action ] [ ON UPDATE action ] }
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  
  並且 table_constraint 是:
  
  [ CONSTRAINT constraint_name ]
  { CHECK ( expression ) [ NO INHERIT ] |
    UNIQUE ( column_name [, ... ] ) index_parameters |
    PRIMARY KEY ( column_name [, ... ] ) index_parameters |
    EXCLUDE [ USING index_method ] ( exclude_element WITH 運算子 [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
    FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
  ] ) ]
      [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  
  並且 like_option 是:
  
  { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }
  
  UNIQUE, PRIMARY KEY, and EXCLUDE 條件約束的 index_parameters 是:
  
  [ WITH ( storage_parameter [= value] [, ... ] ) ]
  [ USING INDEX TABLESPACE tablespace_name ]
  
  EXCLUDE 約束條件的 exclude_element 是:
  
  { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
  

• 刪除資料表基本 SQL 語法：
  

  DROP TABLE [ IF EXISTS ] 名稱 [, ...] [ CASCADE | RESTRICT ]
  

操作練習

1. 登入 psql 操作介面：

   #su - postgres
   $psql
   #postgres=
   

2. 新增 test 資料表：

   postgres=# CREATE TABLE test(
   postgres(# id varchar(10),
   postgres(# name varchar(100),
   postgres(# score int
   postgres(# );
   
   (請注意 "=" 與 '(' 的不同)
   

3. 新增完之後，可查詢資料表：

   postgres=# \d
   
   postgres=# \d test
   

4. 刪除 test 資料表：

   postgres=# \d test
   

5. 新增 score 資料表：

   student=# CREATE TABLE score(
   student(#student_id varchar(11),
   student(#subject_id varchar(11),
   student(#score int CONSTRAINT score_check CHECK (score >=0 AND score <=100));
   
   CREATE TABLE
   student=#
   

參考文獻：

1. http://netkiller.sourceforge.net/postgresql/key.html