创建表
创建表
表是关系数据库中数据存储的基本单位。您可以在表中存储结构化数据。一个数据库中可以包含多个表,一个表有行和列组成。表和表之间拥有一些关系,比如一对一,一对多,多对多等
PostgreSQL 允许您使用 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 是该列的约束,比如:
column_name data_type column_contraint 为一个列的定义。您可以在表中定义多个列,多个列定义使用逗号分隔
table_constraint 是表上的约束,包括:PRIMARY KEY,FOREIGN KEY,UNIQUE 和 CHECK
; 不是语句的一部分,它只是表示语句的结束
CREATE TABLE 示例
在下面的示例中,我们将在 testdb 数据库中创建 users 和 user_hobbies 两个表。其中,users 表用来存储用户的名称,性别,年龄等信息。user_hobbies 表用来存储用户的业余爱好
使用 postgres 用户登录 PostgreSQL 服务器:
C:\Users\MW>psql -U postgres 用户 postgres 的口令: psql (15.2) 输入 "help" 来获取帮助信息. postgres=#
注意:您也可以使用其他任何具有相应的数据库权限的用户登录
使用以下语句选择 testdb 数据库:
\c testdb;
如果还未创建数据库,请先运行如下语句:
CREATE DATABASE testdb;
使用以下语句创建 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 );
这里创建的 users 表有 5 个字段:
user_id 列的数据类型是
INTEGER
,它不能为 NULL,并且它是主键name 列的数据类型是
VARCHAR
,它最多为 45 个字符。它不能为 NULLage 列的数据类型是
INTEGER
。它可以是 NULLlocked 列的数据类型是
BOOLEAN
。它不能为 NULL,但是它有默认值 falsecreated_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 个字符。 它不能为 NULLcreated_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
使用如下语句向 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
从一个已有的表创建一个新表
您可以使用 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 语句从一个结果集创建一个表