13. 创建和操纵表

创建表

CREATE TABLE products_test (
    prod_id       char(10)         NOT NULL,
    vend_id       char(10)         NOT NULL,
    prod_name     char(255)        NOT NULL,
    prod_price    decimal(8, 2)	   NOT NULL,
    prod_desc     varchar(1000)    NULL
);

postgres=# \d products_test

                     Table "public.products_test"
   Column   |          Type           | Collation | Nullable | Default
------------+-------------------------+-----------+----------+---------
 prod_id    | character(10)           |           | not null |
 vend_id    | character(10)           |           | not null |
 prod_name  | character(255)          |           | not null |
 prod_price | numeric(8,2)            |           | not null |
 prod_desc  | character varying(1000) |           |          |

指定默认值：

CREATE TABLE orderitems_test (
    order_num     int              NOT NULL,
    order_item    int              NOT NULL,
    prod_id       char(10)         NOT NULL,
    quantity      int              NOT NULL    DEFAULT 1,
    item_price    decimal(8, 2)    NOT NULL
);

postgres=# \d orderitems_test

               Table "public.orderitems_test"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_num  | integer       |           | not null |
 order_item | integer       |           | not null |
 prod_id    | character(10) |           | not null |
 quantity   | integer       |           | not null | 1
 item_price | numeric(8,2)  |           | not null |

默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。

DBMS	函数/变量
Access	NOW()
DB2	CURRENT_DATE
MySQL	CURRENT_DATE()
Oracle	SYSDATE
PostgreSQL	CURRENT_DATE
SQL Server	GETDATE()
SQLITE	date(‘now’)

更新表

以下是使用ALTER TABLE时需要考虑的事情：

• 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求；
• 所有DBMS都允许给现有的表增加列，不过对所增加列的数据类型（以及NULL和DEFAULT的使用）有所限制；
• 许多DBMS不允许删除或更改表中的列；
• 多数DBMS允许重命名表中的列；
• 许多DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

增加列：

ALTER TABLE
    products_test 
ADD
    prod_weight char(20);

postgres=# \d products_test

                      Table "public.products_test"
   Column    |          Type           | Collation | Nullable | Default
-------------+-------------------------+-----------+----------+---------
 prod_id     | character(10)           |           | not null |
 vend_id     | character(10)           |           | not null |
 prod_name   | character(255)          |           | not null |
 prod_price  | numeric(8,2)            |           | not null |
 prod_desc   | character varying(1000) |           |          |
 prod_weight | character(20)           |           |          |

删除列：

ALTER TABLE 
   products_test 
DROP COLUMN
   prod_weight;

复制的表结构更改一般需要手动删除过程，涉及以下步骤：

1. 用新的列布局创建一个新表；
2. 使用INSERT SELECT语句从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
3. 检验包含所需数据的新表；
4. 重命名旧表，或者删除；
5. 用旧表原来的名字重命名新表；
6. 根据需要，重新创建触发器、存储过程、索引和外键。

删除表

DROP TABLE
    products_test;

重命名表

ALTER TABLE
    orderitems_test
RENAME TO
    oi_test;