SQL • 無限: PostgreSQL 修改表格語法(一)

基本語法

查詢資料表中的資料 SQL 語法：

ALTER TABLE [ IF EXISTS ] [ ONLY ] 名稱 [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名稱 [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名稱 [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] 名稱
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] 名稱
    SET SCHEMA new_schema

其中 action 是:

    ADD [ COLUMN ] column_name data_type [ COLLATE 定序 ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE 定序 ] [ USING expression ]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name SET STATISTICS 整數
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITH OIDS
    SET WITHOUT OIDS
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OF type_name
    NOT OF
    OWNER TO new_owner
    SET TABLESPACE new_tablespace

並且 table_constraint_using_index 是:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

查詢資料庫內的資料表：

student=# \d
             relation 清單
 架構模式 |   名稱   | 型別  |  擁有者
----------+----------+-------+----------
 public   | baselist | table | postgres
(1 筆資料列)

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

新增欄位 age ，並且加入限制 0~200 條件：

student=# ALTER TABLE baselist ADD COLUMN age int CHECK (age >=0 AND age <= 200);
ALTER TABLE

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
 age     | integer                |
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)
檢查條件約束
    "baselist_age_check" CHECK (age >= 0 AND age <= 200)

故意加入一個超出範圍值的資料：

student=# UPDATE baselist SET age=201 WHERE id='S0001';
錯誤:  關係 "baselist" 的新資料列違反檢查限制 "baselist_age_check"
詳細資料: Failing row contains (S0001, Peter, 0912345678, Hd. Rd., No. 4321, 201).

加入一個合理範圍值的資料：

student=# UPDATE baselist SET age=20 WHERE id='S0001';
UPDATE 1
student=# SELECT * FROM baselist WHERE id='S0001';
  id   | name  |   phones   |      address      | age
-------+-------+------------+-------------------+-----
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321 |  20
(1 筆資料列)

修改表格欄位名稱 age ，成為 era ：

student=# ALTER TABLE baselist RENAME COLUMN age TO era;
ALTER TABLE
student=# \d baselist;
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
 era     | integer                |
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)
檢查條件約束
    "baselist_age_check" CHECK (era >= 0 AND era <= 200)

刪除檢查限制：

student=# ALTER TABLE baselist DROP CONSTRAINT "baselist_age_check";
ALTER TABLE

刪除 baselist 表格內的 era 欄位：

student=# ALTER TABLE baselist DROP COLUMN era;
ALTER TABLE

查詢 baselist 表格內容：

student=# SELECT * FROM baselist;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0002 | Jupiter | 0987654321 | Rd. Le, No. 123
 S0001 | Peter   | 0912345678 | Hd. Rd., No. 4321
(2 筆資料列)

修改表格 baselist 名稱，成為 baseData 表格名稱：

student=# ALTER TABLE baselist RENAME TO baseData;
ALTER TABLE
student=# \d baseData
          資料表 "public.basedata"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

2016年7月11日 星期一

PostgreSQL 修改表格語法(一)

2016年7月11日星期一