unvdb 基础之库表操作

SQL语句的库表操作的使用及解释

CREATE DATABASE 创建数据库

数据库是表的容器，一个数据库中可以包含多个表。要想在 unvdb 存储数据，你首先要创建一个数据库。

unvdb 允许您使用 CREATE DATABASE 语句创建一个新的数据库。

CREATE DATABASE 语法

要在 unvdb 服务器上创建一个数据库，您必须是超级用户或者具有 CREATEDB 权限。

请按照如下语法使用 CREATE DATABASE 语句：

CREATE DATABASE db_name
[ [ WITH ] [ OWNER [=] user_name ]
       [ TEMPLATE [=] template ]
       [ ENCODING [=] encoding ]
       [ LOCALE [=] locale ]
       [ LC_COLLATE [=] lc_collate ]
       [ LC_CTYPE [=] lc_ctype ]
       [ TABLESPACE [=] tablespace_name ]
       [ ALLOW_CONNECTIONS [=] allowconn ]
       [ CONNECTION LIMIT [=] connlimit ]
       [ IS_TEMPLATE [=] istemplate ] ]

看起来这是一个很复杂的语句，但是最常用的是下面的一行语句：

CREATE DATABASE db_name;

您可以是按照任意的顺序使用可选的选项。以下是对其中的参数的说明：

db_name

要创建的数据库的名字。
user_name

将拥有新数据库的用户的角色名称。您可以使用 DEFAULT 表示执行命令的用户。
template

用于创建新数据库的模板名称。您可以使用 DEFAULT 表示默认模板的模板名称 (template1)。
encoding

要在新数据库中使用的字符集编码。您可以指定一个字符串常量（例如，'SQL_ASCII'），或者一个整数编码号，或者 DEFAULT (模板数据库的编码)。
locale

这是一个设置 LC_COLLATE 和 LC_CTYPE 的快捷方式。如果指定此项，则不能指定其中任何一个参数。
lc_collate

要在新数据库中使用的整理顺序 (LC_COLLATE)。这会影响应用于字符串的排序顺序。
lc_ctype

要在新数据库中使用的字符分类 (LC_CTYPE)。这会影响字符的分类，例如小写、大写和数字。
tablespace_name

将与新数据库关联的表空间的名字。您可以使用 DEFAULT 以使用模板数据库的表空间的名称。
allowconn

是否允许连接到此数据库。如果为 false，则没有人可以连接到该数据库。默认值为 true，允许连接。
connlimit

连接数限制。-1（默认）表示没有限制。
istemplate

是否为模版数据库。如果为 true，则任何具有 CREATEDB 权限的用户都可以克隆此数据库；如果为 false（默认值），则只有超级用户或数据库所有者可以克隆它。

创建数据库实例

在本实例中，我们将在unvdb的命令行中创建一个数据库 test_1。

1、请用您自己的用户登录数据库服务器。

$ ud_sql -U unvdb 
ud_sql (2.4)
Type "help" for help.

2、然后用默认选项创建test_1数据库。

CREATE DATABASE test_1;

CREATE DATABASE

如果您输入了一个已经存在的数据库名称，unvdb 将返回一个错误：ERROR: database "test_1" already exists。

3、最后使用\c命令转换至刚刚创建的数据库。

\c test_1;

You are now connected to database "test_1" as user "unvdb".

现在，您就可以创建一个表，并进入[插入]、[修改]、[删除]、[查询]等操作了。

通常情况下，创建数据库是一个简单的操作。

DROP DATABASE 删除数据库

当我们不需要那个数据库时，可以使用DROP DATABASE语句来删除。

这里要注意的是：DROP DATABASE是永久删除数据库和其中的所有表数据，谨慎操作。

`DROP DATABASE` 语法

要执行 DROP DATABASE 语句，您需要是超级用户或者是数据库的所有者。

以下是语法

DROP DATABASE [IF EXISTS] database_name;

database_name 是要删除的数据库的名称。您需要提供一个已经存在的数据库的名称。
如果您提供了一个不存在的数据库名称，unvdb 将给出一个错误报告。您可以使用 IF EXISTS 选项防止输出此错误。 unvdb 对于不存在的数据库将给出一个通知。

DROP DATABASE 实例

创建一个数据库test_db 并删除它。

首先创建一个数据库

CREATE DATABASE test_2;
 
 CREATE DATABASE

如果您输入了一个已经存在的数据库名称， unvdb 将返回一个错误：ERROR: database "test_2" already exists

然后，使用 \c 命令切换到刚刚创建的 test_2 数据库:

\c test_2;

然后，尝试删除 test_2 数据库

DROP DATABASE test_2;
ERROR:  cannot drop the currently open database

也就是说，您不能删除当前打开的数据库。

保持刚刚的会话，打开一个新的会话并登陆，然后尝试删除：

DROP DATABASE test_2;
ERROR:  database "test_2" is being accessed by other users
DETAIL:  There is 1 other session using the database.

这里， unvdb 报告了一个错误：数据库 test_db 正在被其他用户访问。

关闭以前的会话，并回到这个会话，重新尝试删除：

DROP DATABASE test_db;

DROP DATABASE

此时，数据库已经被删除。

查看数据库的活动连接并关闭

有时候，并不是我们自己连接了要删除的数据库。我们可以从 pg_stat_activity 视图中查询数据库中的活动连接，如下：

SELECT
  pid,
  usename,
  application_name
FROM
  pg_stat_activity
WHERE
  datname = 'test_db';
 pid  | usename | application_name 
------+---------+------------------
 1631 | unvdb   | ud_sql
(2 rows)

然后，使用 pg_terminate_backend() 函数结束刚刚返回的活动连接：

SELECT pg_terminate_backend(1631);
 pg_terminate_backend
----------------------
 t
(1 row)

然后，使用 DROP DATABASE 删除数据库就可以了。

DROP DATABASE 语句用来删除已经存在的数据库。删除数据库是一个很危险的操作，请务必小心。

ALTER DATABASE 修改数据库

允许您使用 ALTER DATABASE 语句修改一个现有的数据库的信息。

`ALTER DATABASE` 语法

ALTER DATABASE 可以修改数据库的各种信息，包括名称、属性、所有者、表空间等。

要重命名一个数据库，请使用如下语法：

ALTER DATABASE name RENAME TO new_name;

要修改数据库的所有者，请使用如下语法：

ALTER DATABASE name OWNER TO
{ new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER };

要修改数据库的选项，请使用如下语法：

ALTER DATABASE name [WITH] ALLOW_CONNECTIONS { true | false};
ALTER DATABASE name [WITH] CONNECTION LIMIT connlimit;
ALTER DATABASE name [WITH] IS_TEMPLATE { true | false};

ALLOW_CONNECTIONS 是否允许连接到此数据库
CONNECTION LIMIT 连接数限制
IS_TEMPLATE 是否为模版数据库

要修改数据库的表空间，请使用如下语法：

ALTER DATABASE name SET TABLESPACE new_tablespace;

要修改数据库的配置参数，请使用如下语法：

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT };
ALTER DATABASE name SET configuration_parameter FROM CURRENT;

配置参数的新值将被写入到 unvdbsvr.conf 文件中，以在新会话中加载使用。

要重置数据库的配置参数的值，请使用如下语法：

ALTER DATABASE name RESET configuration_parameter;

要重置数据库所有的配置参数的值，请使用如下语法：

ALTER DATABASE name RESET ALL;

注意：要修改数据库的信息，您必须是超级用户或者数据库的所有者

`ALTER DATABASE` 实例

创建一个数据库test_db 用于演示:

CREATE DATABASE test_db;

重命名数据库

要将 test_db 重命名为 test_new_db，请使用下面的语句：

ALTER DATABASE test_db RENAME TO test_new_db;

修改数据库的表空间

数据库的默认的表空间为 pg_default，下面说明了如何将数据库的表空间修改为 test_tablespace。

首先，请使用下面的语句创建表空间：

CREATE TABLESPACE test_tablespace OWNER unvdb LOCATION '/data/soft';

创建完成后到此目录下查看，创建出来的表空间的目录名并不是test_tablesepace这样不利于我们分辨。解决方法，在你要放表空间的目录下，创建一个和你要创建的表空间名字相同的目录,然后再生成表空间。

以下操作

mkdir /data/soft/test_tablespace
CREATE TABLESPACE test_tablespace OWNER unvdb LOCATION '/data/soft/test_tablespace';

注意由于在数据库中执行的创建表空间的命令是由普通用户执行的，所以要么使用普通用户创建目录，要么直接root创建然后修改属主，组。

然后，使用下面的语句修改数据库的表空间：

ALTER DATABASE test_new_db
SET TABLESPACE test_tablespace;

删除表空间

DROP TABLESPACE test_tablespace;

请使用以下查询语句来检查名为’test_tablespace’的表空间是否存在：

SELECT spcname, pg_tablespace_location(oid) AS location FROM pg_tablespace WHERE spcname = 'test_tablespace';

修改数据库是否允许连接

要将数据库设置为不允许连接，请使用下面的语句：

ALTER DATABASE test_new_db ALLOW_CONNECTIONS false;

要设置数据库设置为允许连接，请使用下面的语句：

ALTER DATABASE test_new_db ALLOW_CONNECTIONS true;

修改数据库的连接数

要将此数据库的连接数限制为 10，请使用下面的语句：

ALTER DATABASE test_new_db CONNECTION LIMIT 10;

修改数据库是否为模板数据库

要设置数据库设置为模板数据库，请使用下面的语句：

ALTER DATABASE test_new_db IS_TEMPLATE true;

要设置数据库设置为不是模板数据库，请使用下面的语句：

ALTER DATABASE test_new_db IS_TEMPLATE false;

修改数据库的所有者

数据库 test_new_db 的所有者是 unvdb，下面说明了如何要将其所有者修改为 test。

首先，使用下面的语句创建 test 用户：

CREATE USER test PASSWORD '123456';

然后，使用下面的语句将数据库的所有者修改为 test：

ALTER DATABASE test_new_db OWNER TO test;

ALTER DATABASE 语句重命名数据库

unvdb 允许您使用 ALTER DATABASE ... RENAME 语句来重命名一个数据库。

重命名数据库的注意事项

重命名数据库是一个简单的动作，但是它可能会带来一些其他的问题。如果您没有同步修改那些用到此数据库的代码，则他们可能不能正常运行。

当您打算修改一个数据库名的时候，您需要首先从整体上进行评估。然后，再决定是否进行重命名数据库。一旦您决定了要重命名一个数据库，您需要把需要同步修改的地方整理清楚。

重命名数据库语法

要重命名一个数据库，请按照下面的语法使用 ALTER DATABASE ... RENAME 语句：

ALTER DATABASE name RENAME TO new_name;

解释说明：

name 是要重命名的数据库的名字。
new_name 是数据库的新名字。

您不能重命名仍有活动连接的数据库否则 unvdb 将给出一个错误。

如果您要重命名当前正在连接的数据库， unvdb 将给出以下错误：

ERROR:  current database cannot be renamed。

如果您要重命名的数据库仍有活动连接，unvdb 将给出以下错误：

ERROR:  database "testdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.

实例

创建一个数据库test_db 并将它重命名为 test_new_db。

创建数据库

CREATE DATABASE test_3
CREATE DAtABASE

如果您输入了一个已经存在的数据库名称，unvdb将返回一个错误：ERROR: database "test_db" already exists

然后，使用 \c 命令切换到刚刚创建的 test_db 数据库:

\c test_3
You are now connected to database "test_3" as user "unvdb".

然后，尝试将 test_3 数据库重命名为 test_new_db：

ALTER DATABASE test_3 RENAME TO test_new_db;
ERROR:  current database cannot be renamed

也就是说，您不能重命名当前打开的数据库。

保持刚刚的会话，打开一个新的会话并登陆，然后尝试重命名 test_db 数据库：

ALTER DATABASE test_3 RENAME TO test_new_db;
ERROR:  database "test_3" is being accessed by other users
DETAIL:  There is 1 other session using the database.

这里，unvdb 报告了一个错误：数据库 test_db 正在被其他用户访问。

关闭以前的会话，并回到这个会话，重新尝试重命名：

ALTER DATABASE test_db RENAME TO test_new_db;
DROP DATABASE

此时， test_3 数据库已经被重命名为 test_new_db。

查看数据库的活动连接并关闭

有时候，并不是我们自己连接了要删除的数据库。我们可以从 pg_stat_activity 视图中查询数据库中的活动连接，如下：

SELECT
  pid,
  usename,
  application_name
FROM
  pg_stat_activity
WHERE
  datname = 'test_db';
 pid  | usename | application_name 
------+---------+------------------
 1631 | unvdb   | ud_sql
(2 rows)

然后，使用 pg_terminate_backend() 函数结束刚刚返回的活动连接：

SELECT pg_terminate_backend(1631);
 pg_terminate_backend
----------------------
 t
(1 row)

最后，使用 ALTER DATABASE ... RENAME 重命名数据库就可以了。

ALTER DATABASE ... RENAME 语句用来重命名数据库。您不能重命名有活动连接的数据库。

CREATE TABLE 创建表

表是关系数据库中数据存储的基本单位。您可以在表中存储结构化数据。一个数据库中可以包含多个表，一个表有行和列组成。表和表之间拥有一些关系，比如一对一，一对多，多对多等。

unvdb 允许您使用 CREATE TABLE 语句创建新表。

CREATE TABLE 语法

你需要在表定义中声明表中的列，以及表上的约束。请按照如下语法使用 CREATE TABLE 语句：

CREATE TABLE [IF NOT EXISTS] table_name (column_name data_type column_contraint [, ...] table_constraint);

说明：

table_name 是要创建的表的名字。表名应该符合以下规则：
- 表名可由字母、数字、下划线和美元符号组成，表名最大长度为 63 个字符。
- 表名在一个数据库中是唯一的。
IF NOT EXISTS 指示只有给定的表不存在的时候才进行创建。它是可选的。如果你给定一个已经存在的表名，又没有使用 IF NOT EXISTS 子句，服务器会返回一个错误。
column_name 是该列的名字。列名应该符合以下规则：
- 列名可由字母、数字、下划线和美元符号组成，列名最大长度为 63 个字符。
- 列名在一个表中是唯一的。
data_type 是该列要存储的数据的数据类型，比如： VARCHAR, INTEGER, BOOLEAN, DATE, TIME, TIMESTAMP, ARRAY, JSON 等。
column_contraint 是该列的约束，比如:
- PRIMARY KEY
- FOREIGN KEY
- NOT NULL
- UNIQUE
- CHECK
- 生成列
- 标识列
column_name data_type column_contraint 为一个列的定义。您可以在表中定义多个列，多个列定义使用逗号分隔。
table_constraint 是表上的约束，包括：PRIMARY KEY, FOREIGN KEY， UNIQUE 和 CHECK
; 不是语句的一部分，它只是表示语句的结束。

CREATE TABLE 实例

在下面的示例中，我们将在 test_3 数据库中创建 users 和 user_hobbies 两个表。其中， users 表用来存储用户的名称，性别，年龄等信息。 user_hobbies 表用来存储用户的业余爱好。

请按照以下步骤操作：

选择test_3数据库。

\c test_3

如果还未创建数据库，请先运行如下语句：

CREATE DATABASE test_3;

使用以下语句创建 users 表：

CREATE TABLE users (user_id INTEGER NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL,age INTEGER,locked BOOLEAN NOT NULL DEFAULT false,created_at TIMESTAMP NOT NULL);

这里创建的 users 表有 5 个字段：

user_id 列的数据类型是 INTEGER，它不能为 NULL，并且它是主键。
name 列的数据类型是 VARCHAR，它最多为 45 个字符。它不能为 NULL。
age 列的数据类型是 INTEGER。它可以是 NULL。
locked 列的数据类型是 BOOLEAN。它不能为 NULL，但是它有默认值 false。
created_at 列的数据类型是 TIMESTAMP。它不能为 NULL。

使用以下语句创建 user_hobbies 表：

CREATE TABLE user_hobbies (hobby_id SERIAL NOT NULL,user_id INTEGER NOT NULL,hobby VARCHAR(45) NOT NULL,created_at TIMESTAMP NOT NULL,PRIMARY KEY (hobby_id),CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE RESTRICT);

这里创建的 user_hobbies 表有 4 个字段：
- hobby_id 列的数据类型是 INTEGER。它不能为 NULL，并且它是一个自增序列。
- user_id 列的数据类型是 INTEGER。它不能为 NULL。它通过外键指向了 users 表的 user_id 列。
- hobby 列的数据类型是 VARCHAR，它最多为 45 个字符。它不能为 NULL。
- created_at 列的数据类型是 TIMESTAMP。它不能为 NULL。
user_hobbies 表的约束有：
- PRIMARY KEY (hobby_id) 子句表明 hobby_id 列是主键。
- CONSTRAINT fk_user 定义了一个外键约束。这个外键将 user_id 列引用了 users 表的 user_id 列

使用如下语句向 users 表中插入数据行：

INSERT INTO users (user_id, name, age, created_at)VALUES (1, 'Jim', 18, NOW());

使用 SELECT 语句检查 users 表中的数据行：

SELECT * FROM users;

 user_id | name | age | locked |         created_at
---------+------+-----+--------+----------------------------
       1 | Jim  |  18 | f      | 2022-08-10 16:11:59.497166
(1 rows)

使用如下语句向 user_hobbies 表中插入数据行：

INSERT INTO user_hobbies (user_id, hobby, created_at)
VALUES (1, 'Football', NOW()), (1, 'Swimming', NOW());

使用 SELECT 语句检查 user_hobbies 表中的数据行：

SELECT * FROM user_hobbies;

 hobby_id | user_id |  hobby   |         created_at
----------+---------+----------+----------------------------
        1 |       1 | Football | 2022-08-10 16:13:25.815005
        2 |       1 | Swimming | 2022-08-10 16:13:25.815005
(2 rows)

从一个已有的表创建一个新表

您可以使用 CREATE TABLE 语句从一个已有的表创建一个新表，请参照如下语法：

CREATE TABLE [IF NOT EXISTS] table_name
AS TABLE existing_table_name
[WITH NO DATA];

这里，

table_name 是要创建的表的名字。
existing_table_name 是已存在的表的名字。
WITH NO DATA 指示只创建表而不拷贝数据。它是可选的。如果省略它，则即创建表又拷贝原表中的数据。

注意，原表中的索引和约束不会被复制到新表。

以下展示了一些实例：

根据 users 表创建 users_copy 表:

CREATE TABLE users_copy AS TABLE users;

只创建 users_copy 表，不拷贝 users 表中的数据行：

CREATE TABLE users_copy AS TABLE users WITH NO DATA;

从结果集创建一个新表

您可以使用 CREATE TABLE ... AS 语句从一个 SELECT 语句返回的结果集创建一个新表，请参照如下语法：

CREATE TABLE [IF NOT EXISTS] table_name AS SELECT ...;

如果复制一个表中的所有的列，您可以使用 SELECT * FROM original_table。

如果复制一个表中的指定的列，您可以使用 SELECT column1, column2, ... FROM original_table。

注意，原表中的索引和约束不会被复制到新表。

以下展示了一些实例：

根据 users 表创建 users_copy 表:

CREATE TABLE users_copy AS SELECT * FROM users;

只创建 users_copy 表，不拷贝 users 表中的数据行：

CREATE TABLE users_copy AS SELECT * FROM users WHERE false;

根据 users 表中的部分列创建 users_copy 表:

CREATE TABLE users_copy AS SELECT user_id, name FROM users;

根据一个单纯的结果集创建一个表：

CREATE TABLE test_1 AS SELECT 1 x;

这里创建了一个只有 x 列的 test_1 表。

此外，您可以使用 SELECT INTO语句从一个结果集创建一个表。

对于已有的表，您还可以进行一些操作:

重命名一个表
向表中添加列
从表中删除列
修改表的定义

DROP TABLE 删除表

当我们不需要某个表的时候，我们可以将此表删除。unvdb 允许使用 DROP TABLE 语句删除一个或者多个表.

注意：DROP TABLE 语句将永久删除表和表中的数据，请谨慎操作。

删除表注意事项

删除表是一个很危险的操作。一个被删除后，您很难再恢复其中的数据。

删除列表之前一定要确定此操作的必要性。

如果您决定了要删除一个表，删除之前请一定要备份表和表中的数据。

`DROP TABLE` 语法

要从数据库中删除表，您应该是超级用户或者表的所有者。请按照如下语法使用 DROP TABLE 语句：

DROP TABLE [ IF EXISTS ] table_name [, ...]
[ CASCADE | RESTRICT ];

table_name 是要删除的表的名称。
您可以在一个 DROP TABLE 语句中删除多个表，请使用逗号分隔表名。
IF EXISTS 选项是可选的，它可以避免由于输入的表名 table_name 不存在引发的错误。
CASCADE | RESTRICT 是可选的，它指示了如果有其他对象（比如外键、视图、触发器、存储过程等）引用了要删除的表的处理策略。其中：
- CASCADE - 允许删除指定的表和引用此表的对象。
- RESTRICT - 如果有对象引用此表，拒绝删除此表，并给出错误。它是默认的选项。

`DROP TABLE` 示例

这个实例演示了如何在 unvdb中删除一个表。

我们将在 testdb 数据库中创建 users 和 user_hobbies 两个表。其中， users 表用来存储用户的名称，性别，年龄等信息。 user_hobbies 表用来存储用户的业余爱好。

使用以下语句创建 users 表：

CREATE TABLE users (user_id INTEGER NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL,age INTEGER,locked BOOLEAN NOT NULL DEFAULT false,created_at TIMESTAMP NOT NULL);

使用以下语句创建 user_hobbies 表：

CREATE TABLE user_hobbies (hobby_id SERIAL NOT NULL,user_id INTEGER NOT NULL,hobby VARCHAR(45) NOT NULL,created_at TIMESTAMP NOT NULL,PRIMARY KEY (hobby_id),CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE RESTRICT);

使用以下语句删除 users 表

DROP TABLE users;

ERROR:  cannot drop table users because other objects depend on it
DETAIL:  constraint fk_user on table user_hobbies depends on table users
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

这里，删除 users 表失败了， unvdb 给出一个错误提示。因为 user_hobbies 表的外键引用了 users 表，如果删除了 users 表， user_hobbies 表中的数据将是无意义的。

如果要强制删除此表，请使用 CASCADE 选项，如下：

DROP TABLE users CASCADE;
NOTICE:  drop cascades to constraint fk_user on table user_hobbies
DROP TABLE

这里， users 表被删除了，并且 user_hobbies 表上的外键约束 fk_user 也被级联删除了。

我们可以通过 \d 命令查看表定义以验证是否外键是否被删除，如下：

\d user_hobbies
                                           Table "public.user_hobbies"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+-----------
 hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
 user_id    | integer                     |           | not null |
 hobby      | character varying(45)       |           | not null |
 created_at | timestamp without time zone |           | not null |
Indexes:
    "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)

删除一个不存在的表

如果在 DROP TABLE 输入了一个不存在的表名， unvdb 将会给出一个错误，如下：

DROP TABLE x;
ERROR:  table "x" does not exist

您可以使用 IF EXISTS 选项以避免这个错误，如下：

DROP TABLE IF EXISTS x;
NOTICE:  table "x" does not exist, skipping
DROP TABLE

这里，该语句执行通过，unvdb 给出了一个通知而不是错误。

DROP TABLE 关键字后面是要删除的表名。如果要删除多个表，请使用逗号分隔表名。
IF EXISTS 选项避免了删除不存在的表时发生的错误。它是可选的。
CASCADE 选项可以强制删除被引用的表。

始终需要注意的是：`DROP TABLE` 语句会物理删除表和表中的数据。这个操作不能撤销，请谨慎操作。在操作之前请务必备份要删除的表，或者备份整个数据库。

ALTER TABLE 修改表

ALTER TABLE 语句用来修改一个现有的表，包括：重命名表、添加列、删除列、修改列、添加约束、删除约束等。

`ALTER TABLE` 用法概述

由于 ALTER TABLE 语句能修改一个表的方方面面，因此它的它的语法非常复杂。

下面说明了该 ALTER TABLE 语句的基本语法：

ALTER TABLE [IF EXISTS] table_name
   [alter_action options]
   [, ...];

table_name 是要修改的表的名字。 IF EXISTS 是可选的，

其中 alter_action 是一个修改动作，主要包括以下关键字：

ADD 关键字可用来添加列和约束。
DROP 关键字可用来删除列和约束。
ALTER 关键字可用来修改现有的列和约束。
RENAME 关键字可用来重命名表、列、和约束。
SET 关键字可用来修改表的架构、表空间。
ENABLE 关键字可用来启用触发器、规则、和行安全策略。
DISABLE 关键字可用来禁用触发器、规则、和行安全策略.

重命名表

要重命名一个表，请使用以下语法：

ALTER TABLE table_name
  RENAME TO new_name

修改表架构

要修改一个表的架构，请使用以下语法：

ALTER TABLE table_name
  SET SCHEMA new_schema

修改表空间

要修改一个表的表空间，请使用以下语法：

ALTER TABLE table_name
  SET TABLESPACE new_tablespace

添加列

要向一个表中添加一个列，请使用以下语法：

ALTER TABLE table_name
  ADD [COLUMN] [IF NOT EXISTS] column_name data_type [ column_constraint [ ... ] ]

删除列

要从表中删除一个列，请使用以下语法：

ALTER TABLE table_name
  DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

重命名列

要重命名一个列，请使用以下语法：

ALTER TABLE table_name
  RENAME [ COLUMN ] column_name TO new_column_name

修改列类型

要修改一个列的数据类型，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type

为列设置默认值

要修改一个列的默认值，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name SET DEFAULT expression

删除列默认值

要删除一个列的默认值，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name DROP DEFAULT

为列添加 `NOT NULL`

要为一个列添加 NOT NULL 约束，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name SET NOT NULL

删除列 `NOT NULL`

要从一个列删除 NOT NULL 约束，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name DROP NOT NULL

标识列

要修改一个列为标识列，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT }
      AS IDENTITY [ ( sequence_options ) ]

要将一个标识列修改为普通列，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]

添加约束

要向一个表中添加一个约束，请使用以下语法：

ALTER TABLE table_name
  ADD [ 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 operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
        FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
          [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
      [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

删除约束

要从表中删除一个约束，请使用以下语法：

ALTER TABLE table_name
  DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]

重命名约束

要重命名一个约束，请使用以下语法：

ALTER TABLE table_name
  RENAME CONSTRAINT constraint_name TO new_constraint_name

重命名表

有时候，您会由于一些原因修改一个现有的表的名称。比如：

您在创建表的时候使用了错误的表名。
您需要将表名更改为一个更有意义的名称。
产品的需求发生变化，需要将表名更改以适应新的业务。
您所在的团队使用了新的命名规则，您需要重命名那些不符合新规则的表。

在 unvdb 中，您可以使用 ALTER TABLE 语句重命名一个表。

unvdb 重命名表语法

要重命名一个表，请按照如下语法使用 ALTER TABLE 语句：

ALTER TABLE [IF EXISTS] table_name
RENAME TO new_table_name;

解释说明：

table_name 是要重命名的表。
new_table_name 是新的表名。
IF EXISTS 选项可以避免由于输入的表名 table_name 不存在引发的错误。它是可选的。

当您重名一个表时，unvdb 会自动更新那些依赖此表的对象。

重命名表示例

我们将在 test_3 数据库中创建 users 和 user_hobbies 两个表。其中， users 表用来存储用户的名称，性别，年龄等信息。 user_hobbies 表用来存储用户的业余爱好。

使用以下语句创建 users 表：

CREATE TABLE users (
  user_id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INTEGER,
  locked BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL
);

使用以下语句创建 user_hobbies 表：

CREATE TABLE user_hobbies (
  hobby_id SERIAL NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (hobby_id),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT);

要查看 user_hobbies 表的定义结构，请使用 \d 命令：

\d user_hobbies;

 Table "public.user_hobbies"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
 user_id    | integer                     |           | not null |
 hobby      | character varying(45)       |           | not null |
 created_at | timestamp without time zone |           | not null |
Indexes:
    "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
    "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

这里， user_hobbies 表具有一个引用 users 表的外键。

假设，您所在的团队制定了新的命名规则，所有的表都需要一个 t_ 开头，因此您需要将 users 表重命名为 t_users，将 user_hobbies 表重命名为 t_user_hobbies。

要将 users 表重命名为 t_users，请使用下面的语句：

ALTER TABLE users RENAME TO t_users;

再次使用 \d 命令查看 user_hobbies 表的定义：

\d user_hobbies;
                                           Table "public.user_hobbies"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
 user_id    | integer                     |           | not null |
 hobby      | character varying(45)       |           | not null |
 created_at | timestamp without time zone |           | not null |
Indexes:
    "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
    "fk_user" FOREIGN KEY (user_id) REFERENCES t_users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

从输出中可以清楚地看到，外键约束已更新并改为引用 t_users 表。

要将 user_hobbies 表重命名为 t_user_hobbies，请使用下面的语句：

ALTER TABLE user_hobbies RENAME TO t_user_hobbies;

最后，使用 \dt 命令查看一下当前数据库中的所有的表以验证结果：

\dt
             List of relations
 Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
 public | t_user_hobbies | table | unvdb
 public | t_users        | table | unvdb

4.9.ALTER COLUMN 语句向表在添加列

有时候，您需要修改一个已有的表中的一个已有的列。比如，修改列的名称，类型，约束，默认值等。

`ADD COLUMN` 语法

要将新列添加到现有表，请按照如下语法使用 ALTER TABLE ... ADD COLUMN 语句：

ALTER TABLE table_name ALTER [COLUMN] column_name alter_action [, ALTER [COLUMN] ...];

table_name 是要在其中添加列的表。
ALTER [COLUMN] column_name alter_action 子句用来修改由列名 column_name 指定的列。其中 COLUMN 关键字是可以省略的。
alter_action 是修改动作，您可以使用以下动作之一：
- 修改列的数据类型: [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
- 修改列的默认值：SET DEFAULT expression
- 删除列的默认值：DROP DEFAULT
- 设置或删除不能为 NULL: { SET | DROP } NOT NULL
- 将生成列转为普通列: DROP EXPRESSION [ IF EXISTS ]
- 修改列为标识列: ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
- 修改标识列的生成策略: { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
- 将标识列转为普通列: DROP IDENTITY [ IF EXISTS ]
- 设置列的统计信息手机目标: SET STATISTICS integer
- 设置属性选项: SET ( attribute_option = value [, ... ] )
- 重置属性: RESET ( attribute_option [, ... ] )
- 设置列的存储模式: SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
- 设置列的压缩方法: SET COMPRESSION compression_method

以下是几个常用的操作完整语法。

修改列类型

要修改一个列的数据类型，请使用以下语法：

ALTER TABLE table_name ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ USING expression ]

在 ALTER TABLE 关键字后指定要更改的列的表名。
在 ALTER COLUMN 子句后指定要更改数据类型的列的名称。
为 TYPE 关键字后的列提供新的数据类型。该 SET DATA TYPE 和 TYPE 是等价的。
unvdb 允许您通过添加 USING 子句在修改数据类型时将列的值转换为新的值。

为列设置默认值

要修改一个列的默认值，请使用以下语法：

ALTER TABLE table_name ALTER [ COLUMN ] column_name SET DEFAULT expression

删除列默认值

要删除一个列的默认值，请使用以下语法：

ALTER TABLE table_name ALTER [ COLUMN ] column_name DROP DEFAULT

为列添加 `NOT NULL`

要为一个列添加 NOT NULL 约束,请使用以下语法：

ALTER TABLE table_name ALTER [ COLUMN ] column_name SET NOT NULL

删除列 `NOT NULL`

要从一个列删除 NOT NULL 约束，请使用以下语法：

ALTER TABLE table_name ALTER [ COLUMN ] column_name DROP NOT NULL

标识列

要修改一个列为标识列，请使用以下语法：

ALTER TABLE table_name ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

要将一个标识列修改为普通列，请使用以下语法：

ALTER TABLE table_name ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]

更改列类型示例

让我们创建一个新表,命名 orders 并插入一些行。

CREATE TABLE orders (id serial PRIMARY KEY,order_no VARCHAR NOT NULL);

INSERT INTO orders(order_no) VALUES('10001'), ('10002');

要将 order_no 列的数据类型更改为 INT，请使用以下语句：

ALTER TABLE orders ALTER COLUMN order_no TYPE INT;

发出了一个错误和一个非常有用的提示：

ERROR:  column "order_no" cannot be cast automatically to type integer
HINT:  You might need to specify "USING order_no::integer".

以下语句将 USING 子句添加到上述语句中：

ALTER TABLE orders ALTER COLUMN order_no TYPE INT USING order_no::integer;

修改成功。

ALTER TABLE 语句向表在添加列

有时候，您需要向一个已有的表中添加一个新的列以保存一些必要的信息。

假设，您有一个用户表，它用来存储用户名，邮件，密码等信息。但是随着系统的需求的变动，您需要在用户表中保存手机号码。要做到这些，您不需要重新创建一个表，只需要在现有的表上添加一个列即可。

unvdb 允许您使用 ALTER TABLE 语句来修改一个现有的表。要向一个表中添加一列或多列，请使用 ALTER TABLE ... ADD COLUMN 语句。

`ADD COLUMN` 语法

要将新列添加到现有表，请按照如下语法使用 ALTER TABLE ... ADD COLUMN 语句：

ALTER TABLE table_name ADD [COLUMN] [IF NOT EXISTS] column_name data_type column_contraint [, ADD [COLUMN] ...];

table_name 是要在其中添加列的表。
ADD [COLUMN] ... 子句用来添加一个列。其中 COLUMN 关键字是可以省略的。如果要在一个语句中添加多个列，请使用多个逗号分隔的 ADD [COLUMN] ... 子句。
column_name 是要添加的列的名字。列名应该符合以下规则：
- 列名可由字母、数字、下划线和美元符号组成，列名最大长度为 63 个字符。
- 列名在一个表中是唯一的。
data_type 是要添加的列要存储的数据的数据类型，比如： VARCHAR, INTEGER, BOOLEAN, DATE, TIME, TIMESTAMP, ARRAY, JSON等。
column_contraint 是要添加的列的约束，比如 NOT NULL(不能为空), UNIQUE(唯一约束), PRIMARY KEY(主键), FOREIGN KEY(外键) 和 CHECK(检查) 等。
IF NOT EXISTS 可以避免因为给出的重复的列名而导致的错误。它是可选的。

新的列将会被添加到表的末尾。您不能为新的列指定位置。

如果表中已有一些行，新的列的约束可能会导致错误，您可以在列定义上添加默认值，或者通过以下步骤解决：

添加不带约束的列。
更新新加的列的数据。
为新的列添加约束。

查看表中所有的列

在向表中添加一个列之前，您可以需要首先确定此表中是否存在同名的列。

在 unvdb 中，要查看一个表中的所有列的信息，您可以使用 \d 命令列出表的定义，或者从 information_schema.columns 表中查找出来。

\d 命令的用法如下：

\d table_name

要从 information_schema.columns 表中查找一个表中所有的列，请使用下面的语句：

SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'table_name';

`ADD COLUMN` 实例

这个实例演示了如何使用 ALTER TABLE ... ADD COLUMN 语句向表中添加一个或两个列。

假设，有一个用户表，其中有 ID 和用户名两列。

使用以下语句在 testdb 数据库中创建一个表users 用以存储用户信息

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

如果您没有 testdb 数据库，请先使用如下语句创建数据库并切换到数据库：

CREATE DATABASE testdb;
\c testdb;

创建表后，您可以使用 \d 命令查看此表中的所有列：

\d users;
                                    Table "public.users"
 Column |          Type          | Collation | Nullable |              Default
--------+------------------------+-----------+----------+-----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(100) |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

向用户表中插入一行数据，如下；

INSERT INTO users (name) values ('Tim');

`ADD COLUMN` 向表中添加一列

要想在 users 表保存用户的年龄，你需要使用以下语句向 users 表中添加 age 列，

ALTER TABLE users
ADD COLUMN age INTEGER NOT NULL;
ERROR:  column "age" contains null values

这里，unvdb 给出了一个错误。这是因为表不是一个空表，它已经有了一行。要添加的 age 列是 NOT NULL 的导致了这个错误的发生。要避免这个错误，您可以为 age 列指定一个默认值，如下：

ALTER TABLE users ADD COLUMN age INTEGER NOT NULL DEFAULT 18;

这里，我们添加了一个 age 列，它的类型是 INTEGER，并且是非空列，默认值是 18。

添加了 age 列后，原有行中的 age 列的值都是 18。下面语句检索了 users 表中的所有的行：

SELECT * FROM users;
 id | name | age
----+------+-----
  1 | Tim  |  18
(1 row)

`ADD COLUMN` 向表中添加二列

随着系统的发展，您可能需要在用户表中保存用户的电子邮件和手机号信息。这几乎已经是用户的必备信息。

要向 users 表中添加 email 和 cellphone 列，请使用以下语句：

ALTER TABLE users ADD COLUMN email VARCHAR(100),ADD COLUMN cellphone VARCHAR(100);

这里，我们向 users 表中添加了两个列： email 用于电子邮件地址和 cellphone 用于手机号码。

让我们检查 users 表的行：

SELECT * FROM users;
 id | name | age | email | cellphone
----+------+-----+-------+-----------
  1 | Tim  |  18 |       |
(1 row)

最后，让我们使用 \d 命令查看此表中的所有列：

\d users;
                                     Table "public.users"
  Column   |          Type          | Collation | Nullable |              Default
-----------+------------------------+-----------+----------+-----------------------------------
 id        | integer                |           | not null | nextval('users_id_seq'::regclass)
 name      | character varying(100) |           | not null |
 age       | integer                |           | not null | 18
 email     | character varying(100) |           |          |
 cellphone | character varying(100) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

DROP COLUMN 语句从表中删除列

有时候，您可能因为以下原因需要从现有的表中删除一个或多个列：

此列是多余的。
此列数据类型已经发生变化，需要先删除列后再重新创建列并导入数据。
此列已经被其他的列代替了。

假设，您有一个用户表，它有用户名，邮件，密码等信息。但是为了安全性，您需要将密码列迁移到另外的一个表中，然后删除掉用户表中的密码列。

unvdb 允许您使用 ALTER TABLE 语句来修改一个现有的表。要从一个表中删除一列或多列，请使用 ALTER TABLE ... DROP COLUMN 语句。

`DROP COLUMN` 语法

要从一个表删除一个或者多个列，请按照如下语法使用 ALTER TABLE ... DROP COLUMN 语句：

ALTER TABLE table_name
DROP [COLUMN] [IF EXISTS] column_name [RESTRICT | CASCADE]
[, DROP [COLUMN] ...];

table_name 是要在其中添加列的表。
DROP [COLUMN] ... 子句用来删除一个列。其中 COLUMN 关键字是可以省略的。如果要在一个语句中删除多个列，请使用多个逗号分隔的 ADD [COLUMN] ... 子句。
IF EXISTS 是可选的，它可以避免因为给出的列名不存在而导致的错误。
column_name 是要删除的列的名字。
CASCADE | RESTRICT 是可选的，它指示了如果有其他对象（比如外键、视图、触发器、存储过程等）引用了要删除的列的处理策略。其中：
- CASCADE - 允许删除此列和引用此列的对象。
- RESTRICT - 如果有对象引用此列，拒绝删除此列，并给出错误。它是默认的选项。

当您从表中删除一列时，unvdb 将自动删除所有涉及删除列的索引和约束。

`DROP COLUMN` 示例

这个实例演示了如何在 unvdb 中删除一个列或多个列。

我们将在 testdb 数据库中创建 users 和 user_hobbies 两个表。其中， users 表用来存储用户的名称，性别，年龄等信息。 user_hobbies 表用来存储用户的业余爱好。

使用以下语句创建 users 表：

CREATE TABLE users (user_id INTEGER NOT NULL PRIMARY KEY,name VARCHAR(45) NOT NULL,age INTEGER,locked BOOLEAN NOT NULL DEFAULT false,created_at TIMESTAMP NOT NULL);

使用以下语句创建 user_hobbies 表：

CREATE TABLE user_hobbies (hobby_id SERIAL NOT NULL,user_id INTEGER NOT NULL,hobby VARCHAR(45) NOT NULL,created_at TIMESTAMP NOT NULL,PRIMARY KEY (hobby_id), CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE RESTRICT);

使用 \d 命令查看 user_hobbies 表的定义：

\d user_hobbies

\d user_hobbies
                                           Table "public.user_hobbies"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
 user_id    | integer                     |           | not null |
 hobby      | character varying(45)       |           | not null |
 created_at | timestamp without time zone |           | not null |
Indexes:
    "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
    "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

我们看到， user_hobbies 表中的外键 fk_user 引用了 users 表中的 user_id 列。

下面的语句用来要删除 users 表中的 user_id 列：

ALTER TABLE users DROP COLUMN user_id;

ERROR:  cannot drop column user_id of table users because other objects depend on it
DETAIL:  constraint fk_user on table user_hobbies depends on column user_id of table users
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

由于，user_hobbies 表中的外键 fk_user 引用了 users 表中的 user_id 列，您不能删除此列， unvdb 给出一个错误提示。

如果要强制删除此列，请使用 CASCADE 选项，如下：

ALTER TABLE users DROP COLUMN user_id CASCADE;

NOTICE:  drop cascades to constraint fk_user on table user_hobbies
ALTER TABLE

这里， user_id 列被删除了，并且 user_hobbies 表上的外键约束 fk_user 也被级联删除了。

我们可以通过 \d 命令查看 user_id 列否被删除，如下：

\d users
                           Table "public.users"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 name       | character varying(45)       |           | not null |
 age        | integer                     |           |          |
 locked     | boolean                     |           | not null | false
 created_at | timestamp without time zone |           | not null |

我们可以通过 \d 命令查看表定义以验证是否外键是否被删除，如下：

\d user_hobbies
                                           Table "public.user_hobbies"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
 user_id    | integer                     |           | not null |
 hobby      | character varying(45)       |           | not null |
 created_at | timestamp without time zone |           | not null |
Indexes:
    "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)

RENAME COLUMN 重命名一个列

unvdb 允许您使用 ALTER TABLE RENAME COLUMN 语句来重命名一个已有的列。

`RENAME COLUMN` 语法

要重命名一个表中的一个列，请按照如下语法使用 ALTER TABLE ... RENAME COLUMN 语句

ALTER TABLE table_name RENAME [COLUMN] column_name to new_column_name;

table_name 是要在其中重命名列的表。
RENAME [COLUMN] ... TO ... 子句用重命名一个列。其中 COLUMN 关键字是可以省略的。
column_name 是要重命名的列的名字。 new_column_name 是列的新名字。

当您要重命名一个列时，如果有其他的数据库对象（比如外键、视图、触发器、存储过程等）引用了此列，unvdb 将自动更改那些依赖对象中的列名。

如果您输入一个不存在列名， unvdb 将给出一个错误： ERROR: column “x” does not exist。

一个 ALTER TABLE ... RENAME COLUMN 语句只能重命名一个列。如果您想重命名多个列，请使用多个语句。

`RENAME COLUMN` 示例

这个实例演示了如何在 unvdb 中重命名一个表中的一个列。

我们将在 testdb 数据库中创建 users 和 user_hobbies 两个表。其中， users 表用来存储用户的名称，性别，年龄等信息。 user_hobbies 表用来存储用户的业余爱好。

使用以下语句创建 users 表：

DROP TABLE IF EXISTS users;

CREATE TABLE users (user_id INTEGER NOT NULL PRIMARY KEY,name VARCHAR(45) NOT NULL,age INTEGER,locked BOOLEAN NOT NULL DEFAULT false,created_at TIMESTAMP NOT NULL);

使用以下语句创建 user_hobbies 表：

DROP TABLE IF EXISTS user_hobbies;

CREATE TABLE user_hobbies (hobby_id SERIAL NOT NULL,user_id INTEGER NOT NULL,hobby VARCHAR(45) NOT NULL,created_at TIMESTAMP NOT NULL,PRIMARY KEY (hobby_id),CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE RESTRICT);

使用 \d 命令查看 users 表的定义：

\d users
                           Table "public.users"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 user_id    | integer                     |           | not null |
 name       | character varying(45)       |           | not null |
 age        | integer                     |           |          |
 locked     | boolean                     |           | not null | false
 created_at | timestamp without time zone |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
Referenced by:
    TABLE "user_hobbies" CONSTRAINT "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

使用 \d 命令查看 user_hobbies 表的定义：

\d user_hobbies
                                           Table "public.user_hobbies"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
 user_id    | integer                     |           | not null |
 hobby      | character varying(45)       |           | not null |
 created_at | timestamp without time zone |           | not null |
Indexes:
    "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
    "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

我们看到， user_hobbies 表中的外键 fk_user 引用了 users 表中的 user_id 列。

下面的语句用来要将 users 表 user_id 列重命名为 id：

ALTER TABLE users
RENAME COLUMN user_id TO id;

由于 user_hobbies 表中的外键 fk_user 引用了 users 表中的 user_id 列，因此 unvdb自动更新了这个外键依赖的列名。

我们可以通过 \d 命令查看表定义以验证 user_hobbies 表中外键是否被更新，如下：

\d user_hobbies
                                           Table "public.user_hobbies"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
 user_id    | integer                     |           | not null |
 hobby      | character varying(45)       |           | not null |
 created_at | timestamp without time zone |           | not null |
Indexes:
    "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
    "fk_user" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE CASCADE

从最后一行的 users(id)，我们看到了外键中依赖的列名被自动更新了。

PRIMARY KEY 主键

在关系数据库中，主键是能够唯一标识表中的每一行的一个列或者多个列的组合。

主键规则

在 unvdb 中，主键需要遵循以下规则：

主键是定义在表上的。一个表不强制定义主键，但最多只能定义一个主键。
主键可以包含一个列或者多个列。
主键列的值必须是唯一的。如果主键包含多个列，则这些列的值组合起来必须是唯一的。
主键列中不能包含 NULL 值。

主键相当于 UNION(唯一约束) 约束和 NOT NULL(不能为空) 约束的组合。

如果不遵循上面的规则，则可能会引发以下的错误。

如果要定义了多个主键，会返回错误：ERROR 1068 (42000): Multiple primary key defined。
如果插入或者更新时有重复的主键值，则会返回类似的错误：ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'。
如果插入了 NULL 值，则会返回类似的错误：ERROR 1048 (23000): Column 'id' cannot be null。

定义主键

我们可以在创建表时定义主键。如下：

CREATE TABLE users (id INTEGER PRIMARY KEY,name VARCHAR(45));

这里定义了 id 列为主键。

上面这种方式适合只有一列作为主键的情况，如果主键包含多列，请使用下面的方式:

CREATE TABLE users (com_id INTEGER,user_number INTEGER,name VARCHAR(45),PRIMARY KEY(com_id, user_number));

这里定义一个主键，它包含 com_id 和 user_number 2 列。

添加主键

如果我们创建表的时候没有设置主键，而我们想为其添加一个主键，请使用下面的方式：

ALTER TABLE users ADD PRIMARY KEY(id);

这里为 users 表添加了主键，该主键包括 id 列。

删除主键

如果我们想删除一个表上主键，请执行以下的步骤：

使用 \d 命令查找主键约束的名称：

\d users

                      Table "public.users"
Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id     | integer               |           | not null |
name   | character varying(45) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

你可以在最后一行发现主键约束的名称是 users_pkey。

使用以下语句删除主键约束：

ALTER TABLE users DROP CONSTRAINT users_pkey;

如何产生主键值

通常在业务系统中，我们不使用业务列作为主键，虽然它们也是唯一的。我们一般使用单独的列作为主键，这主要是出于以下两方面的原因：

保护业务数据
方便这些业务列的修改

为了生成唯一的主键值，我们通常采用以下方法：

将主键列设置为 SERIAL(自增键)。

声明为 SERIAL 的列会自动生成连续的整数值。以下语句使用 SERIAL 创建一个主键列。

CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(45));

将主键列设置为 UUID类型。

CREATE TABLE users (id UUID PRIMARY KEY DEFAULT gen_random_uuid(),name VARCHAR(45));

这里，将主键列设置的数据类型设置为 UUID，并为它自定默认值 gen_random_uuid()。

主键 vs 唯一索引

主键和唯一索引都要求值是唯一的，但它们之间存在一些不同：

一个表中只能定义一个主键，但是能定义多个唯一索引。
主键中的值不能为 NULL，而索引中的值可以为 NULL。

要点：

一个表最多只能定义一个主键。
主键可以包含一个列或者多个列。
主键列的值必须是唯一的。如果主键包含多个列，则这些列的值组合起来必须是唯一的。
主键列中不能包含 NULL 值。

FOREIGN KEY 外键

在关系数据库中，外键用来定义两个实体之间的约束关系。外键对保证数据的完整性很有用。

什么是外键

外键相对于主键而言，用来引用其他表。外键在子表中定义，它将子表的一个或多个列对应到父表的主键或唯一键值，将子表的行和父表行建立起关联

以下是 country 表中的部分数据：

SELECT *
FROM country
WHERE country_id = 23;
 country_id | country |     last_update
------------+---------+---------------------
         23 | China   | 2006-02-15 04:44:00
(1 row)

以下是 city 表中的部分数据：

SELECT *
FROM city
WHERE country_id = 23;
 city_id |     city      | country_id |     last_update
---------+---------------+------------+---------------------
      46 | Baicheng      |         23 | 2006-02-15 04:45:25
      47 | Baiyin        |         23 | 2006-02-15 04:45:25
      80 | Binzhou       |         23 | 2006-02-15 04:45:25
     109 | Changzhou     |         23 | 2006-02-15 04:45:25
     136 | Datong        |         23 | 2006-02-15 04:45:25
...
(53 rows)

由此我们看出，country 表和 city 表是一对多的关系。一个国家中可以有多个城市，一个城市只能位于一个国家。

如果一个国家已经有了城市，那么你就不能轻易的从 country 表删除国家，否则就会造成对应的城市数据的不完整。你也不能为一个城市设定一个不存在的 country_id，否则这个城市数据就是错误的。

外键约束能保证数据的完整和正确。

通常，外键所属的表被称作子表，被外键引用的表被称作父表。

外键的语法

创建表时添加外键

要在创建表时添加外键，请使用以下语法：

CREATE TABLE table_name (column_defination_1,... [CONSTRAINT foreign_key_name] FOREIGN KEY (column) REFERENCES parent_table_name (column) ON UPDATE ... ON DELETE ...;);

解释说明：

foreign_key_name 是外键约束的名字。 CONSTRAINT foreign_key_name 是可选的。
FOREIGN KEY (column) 指明了表中的 column 列是外键。
REFERENCES parent_table_name (column) 指明了外键引用了 parent_table_name 表中的 column 列。
ON DELETE 和 ON UPDATE 指定了删除或更新父表中的行时要采取的约束策略。你可以使用以下 5 个策略中的一个：
- NO ACTION: 这是默认的策略。
- RESTRICT: 如果父表中的一行在该表中有匹配的行，试图删除或更新父表中行时会引发 unvdb 错误。
- CASCADE：如果父表中的一行被删除或更新，该表中匹配行的值会自动删除或更新。
- SET NULL：如果父表中的一行被删除或更新，该表中匹配行的值设置为 NULL。
- SET DEFAULT: 如果父表中的一行被删除或更新，该表中匹配行的值设置为默认值。

让我们看一下 city 表定义的外键约束：

\d city
                                           Table "public.city"
   Column    |            Type             | Collation | Nullable |                Default
-------------+-----------------------------+-----------+----------+---------------------------------------
 city_id     | integer                     |           | not null | nextval('city_city_id_seq'::regclass)
 city        | character varying(50)       |           | not null |
 country_id  | smallint                    |           | not null |
 last_update | timestamp without time zone |           | not null | now()
Indexes:
    "city_pkey" PRIMARY KEY, btree (city_id)
    "idx_fk_country_id" btree (country_id)
Foreign-key constraints:
    "city_country_id_fkey" FOREIGN KEY (country_id) REFERENCES country(country_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "address" CONSTRAINT "address_city_id_fkey" FOREIGN KEY (city_id) REFERENCES city(city_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON city FOR EACH ROW EXECUTE FUNCTION last_updated()

注意其中外键的部分：

Foreign-key constraints:
    "city_country_id_fkey" FOREIGN KEY (country_id) REFERENCES country(country_id) ON UPDATE CASCADE ON DELETE RESTRICT

添加外键语法

如果建表的时候没有定义外键，你也可以后来通过以下语法添加外键：

ALTER TABLE child_table_name
ADD [CONSTRAINT foreign_key_name]
  FOREIGN KEY (column)
    REFERENCES parent_table_name (column)
    ON UPDATE ...
    ON DELETE ...
;

使用 ALTER TABLE 语句修改表的定义。
使用 ADD [CONSTRAINT foreign_key_name] 添加一个名为 foreign_key_name 的约束。[CONSTRAINT foreign_key_name] 是可选的。
使用 FOREIGN KEY (column)) REFERENCES parent_table_name (column) 定义了外键。

删除外键语法

要删除表上外键，可以采用下面的语法：

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

使用 ALTER TABLE 语句修改表的定义。
DROP CONSTRAINT 后面指定约束名。它可以通过名字删除任何约束，并不仅仅是外键。

FOREIGN KEY 实例

以下实例将在 testdb 数据库中创建 users 和 user_hobbies 两个表。其中，user_hobbies 表中使用外键引用 users 表。下面先创建 users 表，user_hobbies 表将在后面的实例中根据各自的情况再创建。请按照如下步骤执行：

用以下语句连接 testdb 数据库：
```
\c testdb
```
如果还未创建数据库，请先运行如下语句：
```
CREATE DATABASE testdb;
```

使用以下语句创建 users 表：

CREATE TABLE users (user_id INTEGER NOT NULL,name VARCHAR(45) NOT NULL,PRIMARY KEY (user_id));

至此，我们创建了 users 表

CASCADE 策略实例

如果外键的 ON DELETE 和 ON UPDATE 使用了 CASCADE 策略：

当父表的行被删除的时候，子表中匹配的行也会被删除。
当父表的行的键值更新的时候，子表中匹配的行的字段也会被更新。

使用以下 SQL 创建 user_hobbies 表，它的外键采用 CASCADE 策略。

DROP TABLE IF EXISTS user_hobbies;
CREATE TABLE user_hobbies (hobby_id INTEGER NOT NULL,user_id INTEGER NOT NULL,hobby VARCHAR(45) NOT NULL,PRIMARY KEY (hobby_id),FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE);

向两个表中插入数据：

DELETE FROM users;

DELETE FROM user_hobbies;

INSERT INTO users (user_id, name) VALUES (1, 'Tim');

INSERT INTO user_hobbies (hobby_id, user_id, hobby) VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

此时 user_hobbies 表中的数据：

 hobby_id | user_id |  hobby
----------+---------+----------
        1 |       1 | Football
        2 |       1 | Swimming
(2 rows)

让我们看一下对父表进行 UPDATE 和 DELETE 操作引起的子表的关联操作：

对父表进行 UPDATE 操作

我们将父表 users 中的键 user_id 的值从 1 修改为 100：

UPDATE users
SET user_id = 100
WHERE user_id = 1;

此时 user_hobbies 表中的数据：

 hobby_id | user_id |  hobby
----------+---------+----------
        1 |     100 | Football
        2 |     100 | Swimming
(2 rows)

我们发现，user_hobbies 表中与 users 表中 user_id 列中的 1 被自动修改为 100。

对父表进行 DELETE 操作
```
DELETE FROM users
WHERE user_id = 100;
```
此时 user_hobbies 表中的数据：
```
 hobby_id | user_id | hobby
----------+---------+-------
(0 rows)
```
我们发现，user_hobbies 表中与 users 表中 user_id = 100 的那些行都被删除了。

RESTRICT 策略

如果外键的 ON DELETE 和 ON UPDATE 使用了 RESTRICT 策略：

当父表的行被删除的时候，子表中匹配的行的列的值被设置为 NULL。
当父表的行的键值被更新的时候，子表中匹配的行的列的值被设置为 NULL。

使用以下 SQL 创建 user_hobbies 表，它的外键采用 SET NULL 策略。

DROP TABLE IF EXISTS user_hobbies;
CREATE TABLE user_hobbies (hobby_id INTEGER NOT NULL,user_id INTEGER,hobby VARCHAR(45) NOT NULL,PRIMARY KEY (hobby_id),CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE SET NULL ON UPDATE SET NULL);

向两个表中插入数据：

DELETE FROM users;

DELETE FROM user_hobbies;

INSERT INTO users (user_id, name) VALUES (1, 'Tim');

INSERT INTO user_hobbies (hobby_id, user_id, hobby) VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

让我们看一下对父表进行 UPDATE 和 DELETE 操作引起的子表的关联操作：

对父表进行 UPDATE 操作

UPDATE users
SET user_id = 100
WHERE user_id = 1;

此时 user_hobbies 表中的数据：

 hobby_id | user_id |  hobby
----------+---------+----------
        1 |  <null> | Football
        2 |  <null> | Swimming
(2 rows)

更新父表中的 user_id 列的值后，user_hobbies 表中那些对应的行的 user_id 列的值被设置为 NULL。

对父表进行 DELETE 操作

由于上面实例将表的数据修改了，我们重新初始化两个表的数据：

DELETE FROM users;
DELETE FROM user_hobbies;
INSERT INTO users (user_id, name)
VALUES (1, 'Tim');
INSERT INTO user_hobbies (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

DELETE FROM users
WHERE user_id = 1;

此时 user_hobbies 表中的数据：

 hobby_id | user_id |  hobby
----------+---------+----------
        1 |  <null> | Football
        2 |  <null> | Swimming
(2 rows)

删除父表中的 user_id 列的值后，user_hobbies 表中那些对应的行的 user_id 列的值被设置为 NULL。

自引用外键

有时，子表和父表可能是同一个表。这种表中的外键被称为自引用外键。

通常，自引用外键定义在表示树形数据结构的表中。比如一个代表分类的表：

CREATE TABLE category (category_id INTEGER PRIMARY KEY,category_name VARCHAR(45),parent_category_id INTEGER,CONSTRAINT fk_category FOREIGN KEY (parent_category_id) REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADE);

在这个表中，parent_category_id 列是一个外键。它引用了 category 表的 category_id 列。

这个表实现了一个无限层级的分类树。一个分类可以有多个子分类，一个分类可以有 0 个或者 1 个父类；

要点：

外键用来定义两个实体之间的约束关系。外键对保证数据的完整性很有帮助。
定义外键的表被称作子表，被外键引用的表被称作父表。
外键引用的是父表的主键或者唯一键值列。
ALTER TABLE ... ADD FOREIGN KEY ... 语句可以用来添加外键。
ALTER TABLE ... DROP CONSTRAINT ... 语句可以用来删除外键。
自引用外键引用的是当前表自身。这可以实现树形数据结构。

NOT NULL

在 unvdb 中，NOT NULL 是列上的约束，它用来约束列中的值不能为 NULL 值。

注意， NULL 不是空串，也不是 0，它表示什么都没有。您可以使用 IS NULL操作符判断一个值是否是 NULL。

`OT NULL` 语法

要定义一个 NOT NULL 的列，请使用以下方法

定义 NOT NULL 列

请使用以下语法定义一个 NOT NULL 列：

CREATE TABLE table_name (...,column_name data_type NOT NULL ...,...);

添加 NOT NULL 约束

要向现有的列添加 NOT NULL 约束，请使用以下语法修改列的定义：

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

column_name 是要添加 NOT NULL 约束的列的名字。

删除 NOT NULL 约束

要删除列上的 NOT NULL 约束，请使用以下语法修改列的定义：

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

column_name 是要从中删除 NOT NULL 约束的列的名字。

NOT NULL 实例

让我们通过一个例子了解 NOT NULL 的作用。请按照以下步骤进行操作：

通过以下语句创建 user_hobby 表：

DROP TABLE IF EXISTS user_hobby;

CREATE TABLE user_hobby (hobby_id SERIAL PRIMARY KEY,user_id INTEGER NOT NULL,hobby VARCHAR(45) NOT NULL);

这里，user_id 和 hobby 列中不接受 NULL 值。

插入几行数据

INSERT INTO user_hobby (user_id, hobby) VALUES (1, 'Football'), (1, 'Swimming');

很显然能插入成功。

向 hobby 列中插入 NULL 值:

INSERT INTO user_hobby (user_id, hobby) VALUES (1, NULL);

ERROR:  null value in column "hobby" violates not-null constraint
DETAIL:  Failing row contains (3, 1, null).

unvdb 服务器会返回了上面的错误。因为 hobby 列不能为空。

将现有列修改为不能为空

如果你想将现有的一个允许 NULL 值的列修改为不允许 NULL 值，请先将该列中的 NULL 值修改为非 NULL 值，否则可能遇到错误。

假设，我们有下面的一个表：

DROP TABLE IF EXISTS user_hobby;

CREATE TABLE user_hobby (hobby_id SERIAL PRIMARY KEY,user_id INTEGER NOT NULL,hobby VARCHAR(45));

这里，hobby 列中是可以有 NULL 值的。

现在我们插入几行测试数据：

INSERT INTO user_hobby (user_id, hobby)
VALUES (1, 'Football'), (1, NULL);

现在我们看以下表中的数据：

SELECT * FROM user_hobby;
 
 hobby_id | user_id |  hobby
----------+---------+----------
        1 |       1 | Football
        2 |       1 |
(2 rows)

运行如下语句为 hobby 列添加 NOT NULL 约束：

ALTER TABLE user_hobby ALTER hobby SET NOT NULL;

ERROR:  column "hobby" contains null values

unvdb 会返回了上面的错误。这是因为其中一行的 hobby 列中的值为 NULL。

我们应该首先将 hobby 列中 NULL 值改为非 NULL 值：

UPDATE user_hobby SET hobby = 'NOTHING' WHERE hobby IS NULL;

然后我们再为 hobby 列添加 NOT NULL 约束：

ALTER TABLE user_hobby ALTER hobby SET NOT NULL;

现在已经成功的为 hobby 列添加了 NOT NULL 约束。

UNIQUE 唯一约束

在 unvdb 中，我们可以在一个表上定义很多约束，比如主键约束、外键约束。唯一约束也是一个常用的约束，用来保证表中的一列或几列的中的值是唯一的。

我们在很多系统中都用到唯一约束约束，例如：

用户表中有登录名或电子邮件列是唯一的。
产品表中的产品编号列是唯一的。
订单表中有订单编号列是唯一的。
每天的统计报表中将年、月、日三个列作为组合唯一约束。

与主键相比，主键用于表示一个行的唯一性，主键的一般采用一个与业务无关的值，比如自增值，UUID 等。而唯一约束一般用于约束与业务相关的数据的唯一性。

主键列不能包含 NULL 值，而唯一约束列可以包含 NULL 值。

在 unvdb 中，一个唯一约束对应了一个唯一索引。

UNIQUE 语法

要定义唯一约束，请使用 UNIQUE 关键字。您可以在创建表的时候定义唯一约束或者创建表后通过修改表增加一个唯一约束。

定义一列为唯一约束

这里是创建表时定义一列为唯一列的语法：

CREATE TABLE table_name(...,column_name data_type UNIQUE,...);

定义多列为唯一约束

如果唯一约束包含多个列，请使用如下语法：

CREATE TABLE table_name(
   column_name1 column_definition,
   column_name2 column_definition,
   ...,
   [CONSTRAINT constraint_name]
      UNIQUE(column_name1, column_name2)
);

这里：

CONSTRAINT constraint_name 用于定义一个约束的名称。它是可选的。如果不定义约束名称， unvdb 会自动为他生成一个。
UNIQUE 关键子后的括号中包含了逗号分隔的多个列。

添加唯一约束语法

我们也可以向已有的表中添加一个唯一约束。这是添加唯一约束的语法：

ALTER TABLE table_name ADD [CONSTRAINT constraint_name] UNIQUE (column_list);

注意，如果现有表中的要设置为唯一约束的列中已有重复数据，unvdb 服务器会返回错误。

删除唯一约束语法

要从表上删除唯一约束，你可以使用 ALTER TABLE 语句：

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

`UNIQUE` 实例

让我们看一些实际的例子来了解唯一约束约束的用法。

首先，我们首先创建一个演示表 user_hobby，它有一个包含了 2 列的唯一约束。也就是说，同一个用户不能有两个相同的业余爱好。

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE user_hobby (hobby_id SERIAL NOT NULL,user_id INTEGER NOT NULL,hobby VARCHAR(45) NOT NULL,PRIMARY KEY (hobby_id),CONSTRAINT unique_user_hobby UNIQUE(user_id, hobby));

这里，我们定义了一个名字为 unique_user_hobby 唯一约束，它包含了 user_id 和 hobby 2 列。

然后，我们插入两行测试数据：

INSERT INTO user_hobby (user_id, hobby) VALUES (1, 'Football'), (1, 'Swimming');

现在我们查看一下表中的数据：

SELECT * FROM user_hobby;
 hobby_id | user_id |  hobby
----------+---------+----------
        1 |       1 | Football
        2 |       1 | Swimming
(2 rows)

唯一约束

让我们再插入一行和已有 user_id 和 hobby 列相同的数据

INSERT INTO user_hobby (user_id, hobby) VALUES (1, 'Football');

ERROR:  duplicate key value violates unique constraint "unique_user_hobby"
DETAIL:  Key (user_id, hobby)=(1, Football) already exists.

unvdb 返回了上述错误。唯一约束约束避免了插入重复的数据。

删除唯一约束

让我们通过下面的语句删除唯一约束：

ALTER TABLE user_hobby DROP CONSTRAINT unique_user_hobby;

你需要提供约束名称以供删除。如果你不知道它的名字或者创建唯一约束的时候未指定约束名，请使用 \d 命令显示一个表中的所有的索引名：

\d user_hobby
                                       Table "public.user_hobby"
  Column  |         Type          | Collation | Nullable |                   Default
----------+-----------------------+-----------+----------+----------------------------------------------
 hobby_id | integer               |           | not null | nextval('user_hobby_hobby_id_seq'::regclass)
 user_id  | integer               |           | not null |
 hobby    | character varying(45) |           | not null |
Indexes:
    "user_hobby_pkey" PRIMARY KEY, btree (hobby_id)
    "unique_user_hobby" UNIQUE CONSTRAINT, btree (user_id, hobby)

唯一约束与 NULL 值

不像主键，唯一约束允许其中的列接受 NULL 值。但是，NULL 值会破坏唯一约束约束。也就是唯一约束对 NULL 值无效。让我们看一下下面的例子。

现在我们修改一下刚刚的建表语句，其中允许 hobby 列为 NULL:

DROP TABLE IF EXISTS user_hobby;

CREATE TABLE user_hobby (hobby_id SERIAL NOT NULL,user_id INTEGER NOT NULL,hobby VARCHAR(45),PRIMARY KEY (hobby_id),CONSTRAINT unique_user_hobby UNIQUE(user_id, hobby));

让我们插入两行一样的数据：

INSERT INTO user_hobby (user_id, hobby) VALUES (1, NULL), (1, NULL);

然后让我们看一下表中的数据：

SELECT * FROM user_hobby;
 
 hobby_id | user_id | hobby
----------+---------+--------
        1 |       1 | <null>
        2 |       1 | <null>
(2 rows)

我们看到了唯一约束的两列出现了重复的 NULL 值。NULL 让唯一约束失效了。

要点

唯一约束用来保证表中的一列或几列的中的值的唯一性。
请使用 UNIQUE 关键字定义唯一约束。
唯一约束列允许 NULL 值，而主键不可以为 NULL。

GENERATED COLUMN 生成列

在 unvdb 中，生成列（GENERATED COLUMN）是一个特殊的列，它的值会根据列定义中的表达式自动计算得出。并且，你不能直接写入或更新生成列的值。生成列是定义在列上的约束。

生成列有 2 种类型：

虚拟生成列：列值不会被存储下来。当读取该列时，该列的值会被计算。
存储生成列：列值会被存储下来。当插入或修改数据时，该列的值会被重新计算并存储在磁盘上。

虚拟生成列语法

在 unvdb 中，可以使用计算列（也称为虚拟列）来动态计算和显示数据。计算列不存储在表中，而是在查询时根据指定的计算表达式生成。

要创建虚拟列，可以使用 GENERATED ALWAYS AS 子句。以下是虚拟列的语法示例：

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    virtual_column column_type GENERATED ALWAYS AS (expression) STORED/VIRTUAL
);

table_name 是要创建的表的名称
column1 和 column2 是表的其他列，
virtual_column 是要创建的虚拟列的名称，
column_type 是虚拟列的数据类型（可以是任何合法的数据类型），
expression 是计算虚拟列值的表达式。

在 GENERATED ALWAYS AS 子句中，还可以选择 STORED 或 VIRTUAL 关键字来指定虚拟列是存储的还是虚拟的。存储的虚拟列在表中实际占用存储空间，而虚拟列只在查询时计算。

虚拟生成列示例

演示如何在表中创建一个虚拟列：

CREATE TABLE testdb (id serial PRIMARY KEY,first_name varchar(50),last_name varchar(50),full_name varchar(100) GENERATED ALWAYS AS (first_name || ' ' ||last_name) STORED);

在上面的示例中，我们在 testdb 表中创建了一个名为 full_name 的虚拟列，它的值是由 first_name 和 last_name 列的值拼接而成的。

请注意，虚拟列的值不会自动更新。如果在创建虚拟列后更改了相关列的值，虚拟列的值将保持不变。要实现自动更新虚拟列的值，可以使用触发器。

以下是一个示例，演示如何创建一个触发器，用于在插入新行时自动计算虚拟列的值：

CREATE FUNCTION calculate_full_name() RETURNS TRIGGER AS $$ BEGIN
    NEW.full_name := NEW.first_name || ' ' || NEW.last_name;
   RETURN NEW;
END; 
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_full_name BEFORE INSERT ON testdb FOR EACH ROW EXECUTE FUNCTION calculate_full_name();

在上面的示例中，我们创建了一个名为 calculate_full_name() 的函数，它在每次插入操作之前触发。函数内部将计算 full_name 的值，并将其赋给 NEW 记录的相应字段。然后，我们创建了一个触发器 update_full_name，将其关联到 testdb 表上，并指定它在每次插入操作之前触发执行，并执行 calculate_full_name() 函数。

当我们插入新的行到 employees 表时，触发器将自动计算并更新 full_name 虚拟列的值。

请注意，触发器可以用于执行各种其他操作，如验证和约束检查、数据转换、数据审计等。在实际应用中，可以根据需要使用触发器来满足特定的业务逻辑需求。

存储生成列示例

要创建生成列，请使用以下语法：

col_name data_type
  GENERATED ALWAYS AS (expr) STORED

请注意第二行的 GENERATED ALWAYS AS (expr) STORED：

GENERATED ALWAYS AS 关键字指示此列是一个生成列。
expr 是用于生成此列的值的表达式。
STORED 只是此列是一个存储生成列。

你可以在通过 CREATE TABLE 语句创建表时定义生成列，或者通过 ALTER TABLE语句添加一个生成列。

假设，你有个订单明细表，其存储了订单销售的商品的价格和数量。使用以下语句创建 order_item 表：

CREATE TABLE order_item (
  order_item_id SERIAL PRIMARY KEY,
  goods VARCHAR(45) NOT NULL,
  price DECIMAL NOT NULL,
  quantity INTEGER NOT NULL
);

使用以下语句插入 2 行测试数据：

INSERT INTO order_item (goods, price, quantity)
VALUES ('Apple', 5, 3), ('Peach', 4, 4);

使用以下语句查找 order_item 表中的所有行：

SELECT * FROM order_item;
 order_item_id | goods | price | quantity
---------------+-------+-------+----------
             1 | Apple |     5 |        3
             2 | Peach |     4 |        4
(2 rows)

假设，您需要查找每个订单项目的总金额，请使用如下 SQL 语句：

SELECT
  goods,
  price,
  quantity,
  (price * quantity) AS total_amount
FROM order_item;
 goods | price | quantity | total_amount
-------+-------+----------+--------------
 Apple |     5 |        3 |           15
 Peach |     4 |        4 |           16
(2 rows)

上面的语句使用 price * quantity 计算总金额，并使用了 total_amount 作为列别名。

毫无疑问，这是正确的。

unvdb 生成列可以简化我们的工作，让你不用写这么复杂的 SELECT 语句。现在我们要通过以下语句添加一个生成列：

ALTER TABLE order_item
  ADD COLUMN total_amount DECIMAL
    GENERATED ALWAYS AS (price * quantity) STORED;

这里，我们添加了一个 total_amount 列。它是一个生成列，它的计算表达式是 price * quantity。

使用 \d 命令看一下 order_item 的结构：

\d order_item
                                               Table "public.order_item"
    Column     |         Type          | Collation | Nullable |                        Default
---------------+-----------------------+-----------+----------+--------------------------------------------------------
 order_item_id | integer               |           | not null | nextval('order_item_order_item_id_seq'::regclass)
 goods         | character varying(45) |           | not null |
 price         | numeric               |           | not null |
 quantity      | integer               |           | not null |
 total_amount  | numeric               |           |          | generated always as (price * quantity::numeric) stored
Indexes:
    "order_item_pkey" PRIMARY KEY, btree (order_item_id)

您可以发现从上面的输出发现 total_amount 是一个存储生成列。

通过下面的语句查询表中的所有行：

SELECT
  goods,
  price,
  quantity,
  total_amount
FROM order_item;
 goods | price | quantity | total_amount
-------+-------+----------+--------------
 Apple |     5 |        3 |           15
 Peach |     4 |        4 |           16
(2 rows)

现在，相比较以前，您不需要写那么复杂的 SQL 语句了。

更新生成列

你不能直接写入或者更新生成列的值。这会引发错误。我们尝试一下，看看究竟会发生什么。

让我们先试着插入一个带有生成列值的数据：

INSERT INTO order_item (goods, price, quantity, total_amount)
VALUES ('Banana', 6, 4, 24);
ERROR:  cannot insert into column "total_amount"
DETAIL:  Column "total_amount" is a generated column.

让我们再尝试修改生成列的值：

UPDATE order_item
SET total_amount = 30
WHERE goods = 'Apple';
ERROR:  column "total_amount" can only be updated to DEFAULT
DETAIL:  Column "total_amount" is a generated column.

生成列是一个其值可以自动被计算得出的列。
生成列有两种类型：VIRTUAL 和 STORED。
生成列的值不能被写入或者修改。

IDENTITY COLUMN 标识列

在 unvdb 中，标识列是一个特殊的生成列，该列的值能自动生成并且是唯一的。标识列是定义在列上的约束。

标识列和 SERIAL类似，他们的内部都使用 SEQUENCE 实现。

不像主键，一个表中允许存在多个标识列，并且标识列中允许存在重复的值。

标识列的语法

要创建标识列，请使用以下语法：

col_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

标识列的数据类型可以是：SMALLINT, INT，或 BIGINT。
GENERATED ALWAYS AS IDENTITY 列的值不能被写入。向此列中插入（或更新）值将会导致一个错误。有一个特殊情况，就在 INSERT 语句中使用 OVERRIDING SYSTEM VALUE 指令可以向此列插入值。
GENERATED BY DEFAULT AS IDENTITY 列的值能被写入，包括插入和更新。
sequence_options 指明了标识列内部使用的序列的选项。它是可选的，如果不指定，unvdb 使用默认值产生一个序列。

你可以在通过 CREATE TABLE 语句创建表时定义标识列，或者通过 ALTER TABLE语句添加一个标识列。

在创建表时定义标识列

要在创建表时添加标识列，请使用如下语法：

CREATE TABLE table_name (column_definition,...,col_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ],...);

添加标识列

要在创建表后添加一个标识列，请使用 ALTER TABLE 语句：

ALTER TABLE table_name ADD COLUMN col_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ];

将一个列设置表标识列

要将一个已有的列设置为标识列，请使用 ALTER TABLE 语句：

ALTER TABLE table_name ALTER COLUMN col_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ];

或者

ALTER TABLE table_name ALTER COLUMN col_name SET GENERATED { ALWAYS | BY DEFAULT };

取消一个标识列

要取消一个标识列，请使用 ALTER TABLE 语句：

ALTER TABLE table_name ALTER COLUMN col_name DROP IDENTITY [ IF EXISTS ];

标识列实例

创建标识列实例

使用下面的语句创建一个表，其中 id 列是标识列：

CREATE TABLE test_identity (id INTEGER GENERATED ALWAYS AS IDENTITY,txt VARCHAR(45) NOT NULL);

您可以通过 \d 命令查看 test_identity 中标识列的情况：

\d test_identity
                             Table "public.test_identity"
 Column |         Type          | Collation | Nullable |           Default
--------+-----------------------+-----------+----------+------------------------------
 id     | integer               |           | not null | generated always as identity
 txt    | character varying(45) |           | not null |

使用以下语句插入 2 行测试数据：

INSERT INTO test_identity (txt) VALUES ('Apple'), ('Peach');

使用以下语句查找 test_identity 表中的所有行：

SELECT * FROM test_identity;

 id |  txt
----+-------
  1 | Apple
  2 | Peach
(2 rows)

从输出可以看到， id 列中的自动生成的值是一个自增的序列。

向标识列中插入值实例

下面的语句尝试使用 INSERT 向标识列中插入一个值：

INSERT INTO test_identity (id, txt) VALUES (1, 'Banana') RETURNING *;

ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

这里，由于 id 列带有 GENERATED ALWAYS AS IDENTITY 约束，因此不能直接插入值。按照 unvdb 的提示，可以使用 OVERRIDING SYSTEM VALUE 强制插入，如下：

INSERT INTO test_identity (id, txt) OVERRIDING SYSTEM VALUE VALUES (1, 'Banana') RETURNING *;
 
 id |  txt
----+--------
  1 | Banana
(1 row)

同时，我们也可以看出，标识列是允许重复值的。

多个标识列实例

一个表中可以存在多个标识列，下面的语句添加一个标识列 id_2，并且起始值为 10，每次自增 10：

ALTER TABLE test_identity ADD COLUMN id_2 INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 10);

让我们看一下表中的数据：

SELECT * FROM test_identity;
 
 id |  txt   | id_2
----+--------+------
  1 | Apple  |   10
  2 | Peach  |   20
  1 | Banana |   30
(3 rows)

您可以发现，添加一个标识列后，已有的行中的标识列中的值已经自动生成。

使用以下语句插入 2 行测试数据：

INSERT INTO test_identity (txt) VALUES ('Pear'), ('Cherry') RETURNING *;
 
 id |  txt   | id_2
----+--------+-----
  3 | Pear   |  40
  4 | Cherry |  50
(2 rows)

CHECK 约束来保证数据的正确性

任何应用都对数据的正确性有要求。比如，用户的年龄必须是大于零的，用户的登录名中不能包含空格，用户的密码必须满足一定的复杂度，等等。

对于这些要求，虽然我们可以在应用界面来对用户输入的数据进行验证，但是这并不能替代数据库层面的数据验证。这能增加应用的安全性。

unvdb 提供了 CHECK 约束来保证写入到表中的数据是符合你的要求的。不符合 CHECK 约束的数据会被 unvdb 拒绝。

`CHECK` 语法

下面是 unvdb CHECK 的语法：

[CONSTRAINT constraint_name]
CHECK(expr)

解释说明：

constraint_name 是约束的名字。 CONSTRAINT constraint_name 是可选的，只有您需要指定约束名称的时候，才使用此子句。
expr 是一个布尔表达式。如果表达式结算结果为真，则 unvdb 允许将输入写入到表中，否则 unvdb 拒绝写入数据。

您可以在一个列上或者一个表上使用 CHECK 约束。如果你为一个列使用 CHECK 约束，则 CHECK 表达式只能使用此列。如果你为一个表使用 CHECK 约束，则 CHECK 表达式可以使用表上的所有列。

下面以 age 列需要大于 0 为例，使用不同的方法添加此约束：

在创建表的时候在列定义中使用 CHECK 约束

CREATE TABLE users (id INTEGER PRIMARY KEY,age INTEGER NOT NULL CHECK(age > 0));

在创建表的时候在约束定义中使用 CHECK 约束

CREATE TABLE users (id INTEGER PRIMARY KEY,age INTEGER NOT NULL,CONSTRAINT users_age_check CHECK(age > 0));

使用修改表语句的添加 CHECK 约束

ALTER TABLE users ADD CONSTRAINT users_age_check CHECK(age > 0);

`CHECK` 约束实例

假设，您需要一个 users 表存储用户的名称，登录名，密码，且需要符合以下要求：

用户的名称不能为空。
登录名的长度不少于 4 个字符。
密码的长度不少于 8 个字符。
密码不能和登录名相同。

注意，在实际的应用中，您不应该将密码的明文存放在数据库中，这是不安全的。

使用以下的 CREATE TABLE 语句创建表：

CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(45) NOT NULL,login_name VARCHAR(45) NOT NULL CHECK(length(login_name) >= 4),password VARCHAR(45) NOT NULL CHECK(length(password) >= 8),CHECK(login_name <> password));

这里，在 CREATE TABLE 语句中有 3 个 CHECK 约束：

在 login_name 列定义中的 CHECK(length(login_name) >= 4) 保证登录名的长度不小于 4。
在 password 列定义中的 CHECK(length(password) >= 8) 保证登录名的长度不小于 8。
在表上的约束 CHECK(login_name <> password) 保证密码不能和登录名相同。

通过以下 \d 命令查看表 users 上的约束：

\d users
                                     Table "public.users"
   Column   |         Type          | Collation | Nullable |              Default
------------+-----------------------+-----------+----------+-----------------------------------
 id         | integer               |           | not null | nextval('users_id_seq'::regclass)
 name       | character varying(45) |           | not null |
 login_name | character varying(45) |           | not null |
 password   | character varying(45) |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "users_check" CHECK (login_name::text <> password::text)
    "users_login_name_check" CHECK (length(login_name::text) >= 4)
    "users_password_check" CHECK (length(password::text) >= 8)

您能够在上面的输出中发现， users 表中有 3 个 CHECK 约束。约束的名称都是 unvdb 按默认规则生成的。

注意， NOT NULL 也是一种约束。

要验证登录名列的 CHECK 约束是否生效，请使用下面的 INSERT 语句尝试插入一行：

INSERT INTO users (name, login_name, password) VALUES ('Tim', 'tim', 'timisok');

ERROR:  new row for relation "users" violates check constraint "users_login_name_check"
DETAIL:  Failing row contains (1, Tim, tim, timisok).

由于上面语句中给出的登录名 tim 的长度小于 4，因此 unvdb 给出了上面的错误。

要验证密码列的 CHECK 约束是否生效，请使用下面的 INSERT 语句尝试插入一行：

INSERT INTO users (name, login_name, password) VALUES ('Tim', 'tim1', 'timisok');

ERROR:  new row for relation "users" violates check constraint "users_password_check"
DETAIL:  Failing row contains (2, Tim, tim1, timisok).

由于上面语句中给出的密码 timisok 的长度小于 8，因此 unvdb 给出了上面的错误。

要验证密码不能和登录名相同的 CHECK 约束是否生效，请使用下面的 INSERT 语句尝试插入一行：

INSERT INTO users (name, login_name, password) VALUES ('Tim', 'timisgood', 'timisgood');

ERROR:  new row for relation "users" violates check constraint "users_check"
DETAIL:  Failing row contains (3, Tim, timisgood, timisgood).

由于上面语句中给出的登录名和密码都是 timisgood，因此unvdb 给出了上面的错误。

使用下面的语句插入一个完全符合 CHECK 约束的行。

INSERT INTO users (name, login_name, password) VALUES ('Tim', 'hitim', 'timisgood');

这一行成功插入到了 users 表中。

CHECK 约束同样适用于 UPDATE 语句，比如：

UPDATE users SET login_name = 'tim' WHERE name = 'Tim';

ERROR:  new row for relation "users" violates check constraint "users_login_name_check"
DETAIL:  Failing row contains (4, Tim, tim, timisgood).

SERIAL - 创建一个自增列

SERIAL 是一种特殊的数据类型，以帮助您在表中创建一个自增列。

SERIAL 类型的列和标识列很类似，其内部都使用了序列。但它不是 SQL 标准的一部分，而标识列则符合 SQL 标准。因此，推荐使用标识列。

创建`SERIAL` 列

要定义一个 SERIAL 列，请使用以下语法：

column_name SERIAL

这里， SERIAL 作为一个数据类型跟在列名的后面。

注意， SERIAL 列将自动添加 NOT NULL 约束，但不会自动成为主键列，但是您可以在列定义中使用 PRIMARY KEY 使该列成为主键列。

要在创建表时定义一个 SERIAL 列，请使用以下 CREATE TABLE 语句：

CREATE TABLE table_name (column_name SERIAL,...);

要在已有表中添加一个 SERIAL 列，请使用以下 ALTER TABLE 语句：

ALTER TABLE table_name ADD COLUMN column_name SERIAL;

`SERIAL` 种类

您可以使用三种类型的 SERIAL 类型以应对不同的数据范围： SMALLSERIAL, SERIAL, BIGSERIAL。

姓名	存储大小	取值范围	对应的数据类型
`SMALLSERIAL`	2 bytes(字节)	1 - 32767	`SMALLINT`
`SERIAL`	4 bytes(字节)	1 - 2147483647	`INTEGER`
`BIGSERIAL`	8 bytes(字节)	1 - 9223372036854775807	`BIGINT`

`SERIAL` 实例

以下语句创建以 id 列为 SERIAL 列的 test_serial 表：

CREATE TABLE test_serial(id SERIAL PRIMARY KEY,notes VARCHAR NOT NULL);

要查看表的定义，请使用以下 \d 命令：

\d test_serial
                                 Table "public.test_serial"
 Column |       Type        | Collation | Nullable |                 Default
--------+-------------------+-----------+----------+-----------------------------------------
 id     | integer           |           | not null | nextval('test_serial_id_seq'::regclass)
 notes  | character varying |           | not null |
Indexes:
    "test_serial_pkey" PRIMARY KEY, btree (id)

这里：

id 列的数据类型为 INTEGER。
id 列是一个 NOT NULL 列。
id 列具有默认值 nextval('test_serial_id_seq')。 test_serial_id_seq 是内部序列的名称，这是由 unvdb 自动生成的。

您可以通过 pg_get_serial_sequence() 函数获取此列对应的内部序列名：

SELECT pg_get_serial_sequence('test_serial', 'id');
  
  pg_get_serial_sequence
---------------------------
 public.test_serial_id_seq
(1 row)

以下语句向 test_serial 表中插入几个新行，其中 id 是 SERIAL 列，它的值会自动生成：

INSERT INTO test_serial (notes) VALUES ('A'), ('B'), ('C');

要查看 test_serial 表中的行，请使用以下语句：

SELECT * FROM test_serial;
 
 id | notes
----+-------
  1 | A
  2 | B
  3 | C
(3 rows)

获取 `SERIAL` 列的当前的值

要获取 unvdb SERIAL 列的当前的值，您可以使用 currval() 函数。

currval() 函数序列的名称作为参数，因此您需要先获取 SERIAL 列对应的内部序列的名称。通常情况下， unvdb 会按照如下格式为 SERIAL 列创建内部序列：

{table_name}_{column_name}_seq

或者您可以通过上面实例中使用的 \d 命令或者 pg_get_serial_sequence() 函数来获取此列对应的内部序列名。

然后，使用如下 currval() 函数获取 SERIAL 列的当前的值：

SELECT currval('test_serial_id_seq');
 
 currval
---------
       3
(1 row)

或者，您直接使用以下一个语句来获取 SERIAL 列的当前的值：

SELECT currval(pg_get_serial_sequence('test_serial', 'id'));
 
 currval
---------
       3
(1 row)

SEQUENCE 序列生成器

SEQUENCE 用于生成一个有序的数字序列。它被称为序列、序列对象或者序列生成器。

您可以使用 CREATE SEQUENCE 语句创建一个序列，使用 DROP SEQUENCE 删除一个序列。

SERIAL 列和标识列在内部都使用了序列。

`CREATE SEQUENCE` 语法

要在 unvdb 中创建一个序列，请按照如下语法使用 CREATE SEQUENCE 语句：

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] seq_name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ]
    [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ]
    [ CACHE cache ]
    [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

TEMPORARY | TEMP

指定当前序列是一个临时序列。您只能在当前会话中使用。它会在会话结束时被自动删除。它是可选的。

IF NOT EXISTS

如果指定，如果存在同名的序列，则不会引发错误。

seq_name

要创建的序列的名称。

data_type

它指示了这个序列的数据类型。可用值： smallint, integer, 和 bigint，并且 bigint 是默认值。

INCREMENT [ BY ] increment

序列增长的步幅，可以是整数或者负数。正值将产生一个升序序列，负值将产生一个降序序列。默认是值 1。

MINVALUE minvalue | NO MINVALUE

minvalue 是序列的最小值。 NO MINVALUE 意味着使用数据类型的最小值。升序序列的默认值是 1, 降序序列的默认值是数据类型的最小值。

MAXVALUE maxvalue | NO MAXVALUE

maxvalue 是序列的最大值。 NO MAXVALUE 意味着使用数据类型的最大值。降序序列的默认值是 1, 升序序列的默认值是数据类型的最大值。

START [ WITH ] start

start 是此序列的起始值。升序序列的默认值是 minvalue，降序序列的默认值是 maxvalue。

CACHE cache

cache 是缓存在内存中的序列值的数量，以便可以快速访问。默认值是 1， 1 也是可接受的最小值。

[ NO ] CYCLE

指示序列是否可循环的。如果指定 NO CYCLE，那么序列到结尾的时候，再取值则会发生错误。

OWNED BY { table_name.column_name | NONE }

将此序列与指定表的列关联起来。如果此列被删除，此序列会被自动删除。

`DROP SEQUENCE` 语法

要在 unvdb 中删除一个或者多个序列，请按照如下语法使用 DROP SEQUENCE 语句：

DROP SEQUENCE [ IF EXISTS ] seq_name [, ...] [ CASCADE | RESTRICT ];

解释说明：

IF EXISTS

如果指定的序列不存在不会抛出一个错误。
seq_name

要删除的序列的名称。
CASCADE

自动删除依赖于此序列的对象，并依次删除依赖这些对象的对象。
RESTRICT

如果任何对象依赖此序列，则拒绝删除它。这是默认值。

访问 unvdb 序列

您可以使用以下三个函数操作 unvdb 序列：

nextval: 获取并返回序列的下一个值。
currval: 返回当前会话中指定序列的当前值。
setval: 重置指定序列的当前值。

您可以使用以下语句查看指定的序列：

SELECT * FROM seq_name;

序列实例

创建一个从 10 开始步长为 10 的升序序列：

CREATE SEQUENCE asc_seq
INCREMENT 10
START 10;

使用以下 nextval() 函数从序列中获取下一个值：

SELECT nextval('asc_seq');
 nextval
---------
      10
(1 row)

再次使用以下 nextval() 函数从序列中获取下一个值：

SELECT nextval('asc_seq');
 nextval
---------
      20
(1 row)

使用 currval() 函数获取序列的当前的值：

SELECT currval('asc_seq');
 currval
---------
      20
(1 row)

使用 setval() 函数设置序列的当前值：

SELECT setval('asc_seq', 50);
 setval
--------
     50
(1 row)

再次使用以下 nextval() 函数从序列中获取下一个值：

SELECT nextval('asc_seq');
 nextval
---------
      60
(1 row)

要查看此序列，请使用以下 SELECT 语句：

SELECT * FROM asc_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
         60 |      32 | t
(1 row)

CREATE TEMPORARY TABLE 临时表

临时表是一种特殊的表，他们只在会话期间存在。临时表会在会话结束的时候被自动删除。

创建 unvdb 临时表

您可以使用 CREATE TEMPORARY TABLE 语句像创建普通表一样创建临时表，如下：

CREATE { TEMPORARY | TEMP } TABLE temp_table_name (
   column_name data_type column_contraint
   [, ...]
   table_constraint
);

相比于常规的 CREATE TABLE语句，它只是多了一个 TEMPORARY 或者 TEMP 关键字，以指示当前创建的表是一个临时表。

您可以为临时表使用一个和常规表相同的表名，但是在临时表存在期间，您不能访问常规表。

删除 unvdb 临时表

要删除一个临时表，您可以使用 DROP TABLE语句，如下：

DROP TABLE temp_table_name;

这与删除常规表完全一样。

或者，您可以通过退出会话让 unvdb 自动删除临时表。

临时表实例

创建临时表的实例

以下语句创建了一个名为 test_temp 的临时表：

CREATE TEMP TABLE test_temp (
  id SERIAL PRIMARY KEY,
  notes VARCHAR
);

使用 \dt 命令查看数据库中的表：

\dt
                List of relations
  Schema   |       Name        | Type  |  Owner
-----------+-------------------+-------+----------
 pg_temp_4 | test_temp         | table | unvdb
 public    | test_serial       | table | unvdb
 public    | test_serial_big   | table | unvdb
 public    | test_serial_small | table | unvdb
 public    | user_hobbies      | table | unvdb
 public    | users             | table | unvdb
(6 rows)

注意，临时表的 Schema 值是 pg_temp_4，而常规表的 Schema 值是 public。

创建与常规表同名的临时表的实例

在创建临时表 users 之前，我们先看以下常规表 users 的结构：

\d users
                           Table "public.users"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 id         | integer                     |           | not null |
 name       | character varying(45)       |           | not null |
 age        | integer                     |           |          |
 locked     | boolean                     |           | not null | false
 created_at | timestamp without time zone |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "user_hobbies" CONSTRAINT "fk_user" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE CASCADE

以下语句创建一个名称为 users 的临时表，为了区分，临时表 users 只由一个 id 列:

CREATE TEMP TABLE users (id SERIAL PRIMARY KEY);

使用 \dt 命令查看数据库中的表：

\dt
                List of relations
  Schema   |       Name        | Type  |  Owner
-----------+-------------------+-------+----------
 pg_temp_4 | test_temp         | table | unvdb
 pg_temp_4 | users             | table | unvdb
 public    | test_serial       | table | unvdb
 public    | test_serial_big   | table | unvdb
 public    | test_serial_small | table | unvdb
 public    | user_hobbies      | table | unvdb
(6 rows)

现在，常规表 users 已经找不到了。

以下语句使用 \d 查看 users 的表结构：

\d users
                           Table "pg_temp_4.users"
 Column |  Type   | Collation | Nullable |              Default
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('users_id_seq'::regclass)
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

这里是临时表 users 的结构。

删除临时表 users：

DROP TABLE users;

然后，再次使用 \dt 命令查看数据库中的表：

\dt
                List of relations
  Schema   |       Name        | Type  |  Owner
-----------+-------------------+-------+----------
 pg_temp_4 | test_temp         | table | unvdb
 public    | test_serial       | table | unvdb
 public    | test_serial_big   | table | unvdb
 public    | test_serial_small | table | unvdb
 public    | user_hobbies      | table | unvdb
 public    | users             | table | unvdb
(6 rows)

临时表 users 不见了，常规表 users 又出现在了上面的列表中。

SELECT INTO - 从结果集创建一个新表

SELECT INTO 语句允许您从一个查询的结果创建一个新表并将结果集插入到新表，它与 CREATE TABLE ... AS 语句功能相同。

`SELECT INTO` 语法

要使用 unvdb SELECT INTO 语句创建一个新表，请遵循以下语法：

SELECT column_list INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table [other_clauses]

解释说明：

column_list 是查询语句要返回的中的列或者表达式的列表。这些列最终成为新表中的列。您可以使用 DISTINCT。
INTO 之后的 new_table 是要创建的表的名字。 TEMPORARY 或者 TEMP 代表新表是一个临时表。 TABLE 关键字是可以省略。
other_clauses 是 SELECT 语句中可用的子句，这包括：
- FORM 子句
- WHERE 子句
- ORDER BY 子句
- GROUP BY 子句
- HAVING 子句
- LIMIT| 子句
- FETCH 子句
- OFFSET 子句
- 集合操作： UNION, INTERSECT，或者 EXCEPT
- 表连接

`SELECT INTO` 示例

使用 `SELECT INTO` 完整复制影片表

要将 film 表的所有的行复制到一个新表 film_copy，请使用下面的语句：

SELECT * INTO TABLE film_copy FROM film;

SELECT 1000

以下语句从新表中查询数据已验证表是否创建成功：

SELECT count(*) FROM film_copy;
 
 count
-------
  1000
(1 row)

使用 `SELECT INTO` 完整复制部分行和列

要将 film 表所有评级为 G 的影片的标题复制到一个新表 film_ranting_g_title，请使用下面的语句：

SELECT title INTO TABLE film_ranting_g_title FROM film WHERE rating = 'G';

SELECT 178

以下语句从新表中查询数据已验证表是否创建成功：

SELECT * FROM film_ranting_g_title;
           
           title
---------------------------
 ACE GOLDFINGER
 AFFAIR PREJUDICE
 AFRICAN EGG
 ALAMO VIDEOTAPE
 AMISTAD MIDSUMMER
 ANGELS LIFE
 ANNIE IDENTITY
 ARMAGEDDON LOST
 ATLANTIS CAUSE
 AUTUMN CROW
 BAKED CLEOPATRA
 ...
(178 row)

unvdb 基础之库表操作

SQL语句的库表操作的使用及解释

CREATE DATABASE 创建数据库

CREATE DATABASE 语法

创建数据库实例

DROP DATABASE 删除数据库

DROP DATABASE 语法

DROP DATABASE 实例

查看数据库的活动连接并关闭

ALTER DATABASE 修改数据库

ALTER DATABASE 语法

ALTER DATABASE 实例

重命名数据库

修改数据库的表空间

修改数据库是否允许连接

修改数据库的连接数

修改数据库是否为模板数据库

修改数据库的所有者

ALTER DATABASE 语句重命名数据库

重命名数据库的注意事项

重命名数据库语法

实例

查看数据库的活动连接并关闭

CREATE TABLE 创建表

CREATE TABLE 语法

CREATE TABLE 实例

从一个已有的表创建一个新表

从结果集创建一个新表

DROP TABLE 删除表

删除表注意事项

DROP TABLE 语法

DROP TABLE 示例

删除一个不存在的表

始终需要注意的是：DROP TABLE 语句会物理删除表和表中的数据。这个操作不能撤销，请谨慎操作。在操作之前请务必备份要删除的表，或者备份整个数据库。

ALTER TABLE 修改表

ALTER TABLE 用法概述

重命名表

修改表架构

修改表空间

添加列

删除列

重命名列

修改列类型

为列设置默认值

删除列默认值

为列添加 NOT NULL

删除列 NOT NULL

标识列

添加约束

删除约束

重命名约束

重命名表

unvdb 重命名表语法

重命名表示例

4.9.ALTER COLUMN 语句向表在添加列

ADD COLUMN 语法

修改列类型

为列设置默认值

删除列默认值

为列添加 NOT NULL

删除列 NOT NULL

标识列

更改列类型示例

ALTER TABLE 语句向表在添加列

ADD COLUMN 语法

查看表中所有的列

ADD COLUMN 实例

ADD COLUMN 向表中添加一列

ADD COLUMN 向表中添加二列

DROP COLUMN 语句从表中删除列

DROP COLUMN 语法

DROP COLUMN 示例

RENAME COLUMN 重命名一个列

RENAME COLUMN 语法

RENAME COLUMN 示例

PRIMARY KEY 主键

主键规则

定义主键

添加主键

删除主键

`DROP DATABASE` 语法

`ALTER DATABASE` 语法

`ALTER DATABASE` 实例

`DROP TABLE` 语法

`DROP TABLE` 示例

始终需要注意的是：`DROP TABLE` 语句会物理删除表和表中的数据。这个操作不能撤销，请谨慎操作。在操作之前请务必备份要删除的表，或者备份整个数据库。

`ALTER TABLE` 用法概述

为列添加 `NOT NULL`

删除列 `NOT NULL`

`ADD COLUMN` 语法

为列添加 `NOT NULL`

删除列 `NOT NULL`

`ADD COLUMN` 语法

`ADD COLUMN` 实例

`ADD COLUMN` 向表中添加一列

`ADD COLUMN` 向表中添加二列

`DROP COLUMN` 语法

`DROP COLUMN` 示例

`RENAME COLUMN` 语法

`RENAME COLUMN` 示例

`OT NULL` 语法

`UNIQUE` 实例

`CHECK` 语法

`CHECK` 约束实例

创建`SERIAL` 列

`SERIAL` 种类

`SERIAL` 实例

获取 `SERIAL` 列的当前的值

`CREATE SEQUENCE` 语法

`DROP SEQUENCE` 语法

`SELECT INTO` 语法

`SELECT INTO` 示例

使用 `SELECT INTO` 完整复制影片表

使用 `SELECT INTO` 完整复制部分行和列