表 - PostgreSQL快速入门 - AXUM中文网 - 带你使用 axum 构建企业应用

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

创建表

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

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

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

约束

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

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

创建表示例

我们将创建一张名为 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`	(无)

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 
);

字段	数据类型	约束
`role_id`	`serial`	`PRIMARY KEY`
`role_name`	`VARCHAR(255)`	`UNIQUE`和`NOT NULL`

SQL 如下：

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

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

字段	数据类型	约束
`user_id`	`INTEGER`	`NOT NULL`及`FOREIGN KEY`
`role_id`	`INTEGER`	`NOT NULL`及`FOREIGN KEY`
`grant_date`	`TIMESTAMP`	(无)
(表约束)		`user_id`和`role_id` 构成的复合 `PRIMARY KEY` 约束

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
    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;

修改表

通过 ALTER TABLE 表名操作 可以修改表。具体结果依据 操作 而异：

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：清空表的同时，重置相关联的系列