表

本章将讨论和表相关的知识，包括：创建表、修改表、清空（截断）表和删除表。

使用CREATE TABLE 创建新表，语法如下：

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1 数据类型 [字段约束] [DEFAULT 默认值],
    字段2 数据类型 [字段约束] [DEFAULT 默认值],
    ...,
    [表约束]
);

• 首先，在 CREATE TABLE 后面指定表名
• 其次，如果欲创建的表已经存在，则会报错。IF NOT EXISTS 选项用于指定当同名的表不存在时才创建，以避免报错。
• 第三，以逗号分隔，指定要表的字段
  • 每个字段由：字段名、数据类型、字段约束 和 默认值构成
  • 关于约束，我们会在后续章节进行详细说明，它是可选的
  • 你可以给字段设置默认值，语法是 DEFAULT 默认值。当然，不同的数据类型其默认值也不同，你指定的默认值要和数据类型相兼容。它也是可选的。
• 最后，你可以指定表约束，它也是可选的。很多约束可以在字段中定义，但有些约束只能在表约束里指定，比如复合约束。

• 每个字段由：字段名、数据类型、字段约束 和 默认值构成
• 关于约束，我们会在后续章节进行详细说明，它是可选的
• 你可以给字段设置默认值，语法是 DEFAULT 默认值。当然，不同的数据类型其默认值也不同，你指定的默认值要和数据类型相兼容。它也是可选的。

关于约束的详细内容我们会在后续章节详细进行说明，这里大概总结一下 PostgreSQL 的约束：

• NOT NULL：确保字段的值不能为 NULL
• UNIQUE：确保该字段的值，在本张表中是唯一的
• PRIMARY KEY：主键约束
  • 一张表只有有一个主键
  • 主键约束自动拥有了 NOT NULL 加上 UNIQUE 约束
• CHECK：确保数据必须满足条件，这个条件是一个布尔表达式
• FOREIGN KEY：外键约束
  • 确保字段的值存在于另一张表中
  • 一张表可有多个外键约束
• 表约束和字段约束类似，通常用于指定多个字段组成的复合约束

• 一张表只有有一个主键
• 主键约束自动拥有了 NOT NULL 加上 UNIQUE 约束

• 确保字段的值存在于另一张表中
• 一张表可有多个外键约束

创建表示例

我们将创建一张名为 accounts（账户） 的表，结构如下：

SQL 如下：

CREATE TABLE accounts (
	user_id serial PRIMARY KEY,
	username VARCHAR ( 50 ) UNIQUE NOT NULL,
	password VARCHAR ( 50 ) NOT NULL,
	email VARCHAR ( 255 ) UNIQUE NOT NULL,
	created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP 
);

然后我们创建 roles（角色）表：

SQL 如下：

CREATE TABLE roles(
   role_id serial PRIMARY KEY,
   role_name VARCHAR (255) UNIQUE NOT NULL
);

接下来创建 account_roles （账户-角色）表：

SQL 如下：

CREATE TABLE account_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  grant_date TIMESTAMP,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (role_id)
      REFERENCES roles (role_id),
  FOREIGN KEY (user_id)
      REFERENCES accounts (user_id)
);

• 它的主键由 user_id和 role_id构成的复合约束，像这种复合约束无法在某个字段上指定，所以通常在表约束指定：PRIMARY KEY (user_id, role_id)
• 因为 role_id 是一个引用用 roles 表中role_id 的外键，所以，使用 FOREIGN KEY (role_id) REFERENCES roles (role_id) 设置外键约束的同时，指定所引用的目标表和字段
• user_id 和 role_id 相同道理

基于查询结果创建新表

SELECT INTO 语句允许基于查询结果来创建新表，语法如下：

SELECT
    select_list
INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table_name
FROM
    table_name
WHERE
    search_condition;

示例：查询出评级为 R，且租赁期限在 5 天的结果，并基于此结果创建 film_r 新表：

SELECT
    film_id,
    title,
    rental_rate
INTO TABLE film_r
FROM
    film
WHERE
    rating = 'R'
AND rental_duration = 5
ORDER BY
    title;

基于查询结果创建新表(2)

通过 CREATE TABLE AS 也可以基于查询结果创建新表：

CREATE TABLE new_table_name
AS query;

示例：

CREATE TABLE action_film AS
SELECT
    film_id,
    title,
    release_year,
    length,
    rating
FROM
    film
INNER JOIN film_category USING (film_id)
WHERE
    category_id = 1;

修改表

• ADD COLUMN : 添加字段
• DROP COLUMN：删除字段
• RENAME COLUMN：重命名字段
• ALTER COLUMN：修改字段
• ADD CONSTRAINT：添加约束
• RENAME TO：修改表名
• ADD CHECK：添加 CHECK 约束

示例

示例数据：

DROP TABLE IF EXISTS links;

CREATE TABLE links (
   link_id serial PRIMARY KEY,
   title VARCHAR (512) NOT NULL,
   url VARCHAR (1024) NOT NULL
);

-- 添加字段 active，数据类型为 boolean
ALTER TABLE links
ADD COLUMN active boolean;

-- 删除字段 active
ALTER TABLE links 
DROP COLUMN active;

-- 将字段 title 重命名为 link_title
ALTER TABLE links 
RENAME COLUMN title TO link_title;

-- 添加字段 target，数据类型为 VARCHAR(10)
ALTER TABLE links 
ADD COLUMN target VARCHAR(10);

-- 修改字段 target，为其加上默认值 
ALTER TABLE links 
ALTER COLUMN target
SET DEFAULT '_blank';

-- 为 target 字段添加 CHECK 约束
ALTER TABLE links 
ADD CHECK (target IN ('_self', '_blank', '_parent', '_top'));

-- 为 url 字段添加 UNIQUE 约束
ALTER TABLE links 
ADD CONSTRAINT unique_url UNIQUE ( url );

-- 修改字段 target，数据类型为 VARCHAR(20)
ALTER TABLE links 
ALTER COLUMN target VARCHAR(20);

-- 将 links 表重命名为 urls
ALTER TABLE links 
RENAME TO urls;

使用 DROP TABLE 语句可以删除指定的表，语法：

DROP TABLE [IF EXISTS] 表名 
[CASCADE | RESTRICT];

• IF EXISTS：如果删除的表不存在，PostgreSQL 会报错。加上 IF EXISTS：如果表存在则删除，如果不存在则什么都不做
• CASCADE：删除的指定表的同时，出删除依赖该表的其它对象。
• RESTRICT：如果有任何其它对象依赖指定表，则无法删除指定表。这是默认值。

清空（截断）表

TRUNCATE TABLE 用于清空表。注意它和删除表DROP TABLE 的区别：

• 它的效率高于删除表
• 它只是清空表的数据，表本身还在；删除表的操作不但把数据清空，连表本身也一起删除了

它和 DELETE FROM 表名的区别：

• 它的效率高于 DELETE
• 它不会扫描表，而 DELETE 会对表里的数据做扫描
• 它可以重置序列（如果有的话），而 DELETE 不行

语法：

TRUNCATE TABLE 表名 
RESTART IDENTITY;

• RESTART IDENTITY：清空表的同时，重置相关联的系列