postgresql14-表的管理（四）

表table

创建表

CREATE TABLE table_name --表名
(
 column_name data_type column_constraint, --字段名、字段类型、约束字段（可选）
 column_name data_type, --表级别约束字段
 ...,
 table_constraint
);
1
2
3
4
5
6
7

CREATE TABLE emp1 --创建表
AS
SELECT * FROM employees; --携带数据导入
-- WHERE 1=0; 不携带数据导入
1
2
3
4

SELECT *
INTO emp2
FROM employees;
1
2
3

字段类型举例

1、字符类型：定长字符串CHAR(n)、变长字符串VARCHAR(n)，更大长度的字符串TEXT；
2、数字类型：整数类型SMALLINT、INTEGER、BIGINT，精确数字NUMERIC (p, s)，浮点数 REAL、DOUBLE PRECISION；
3、时间类型：日期 DATE、时间TIME、时间戳TIMESTAMP；

/*
举例：表名departments
部门编号department_id，整型，非空，表的主键
部门名称department_name，字符串，可变长度，非空
*/
CREATE TABLE departments
 ( department_id INTEGER NOT NULL PRIMARY KEY
 , department_name CHARACTER VARYING(30) NOT NULL
 ) ;
1
2
3
4
5
6
7
8
9
10

约束

1、NOT NULL：非空约束，该字段的值不能为空（NULL）；

2、UNIQUE：唯一约束，该字段每一行的值不能重复。注意：PostgreSQL允许该字段存在
多个 NULL 值，并且将其看作不同的值。但SQL标准只允许UNIQUE字段中存在一个 NULL 值；

3、PRIMARY KEY：主键约束，包含 NOT NULL 约束和 UNIQUE 约束。注意：如果主键只包
含一个字段，可以通过列级约束进行定义；如果主键包含多个字段（复合主键）或者需要为主键指定一个自定义的名称，需要使用表级约束进行定义；

4、REFERENCES：外键约束，字段中的值必需已经在另一个表中存在。外键用于定义两
个表之间的参照完整性（referential integrity），例如：员工的部门编号字段必须是一个
已经存在的部门；

5、CHECK：检查约束，插入或更新数据时检查数据是否满足某个条件。例如，产品的价
格必需大于零；

6、DEFAULT：默认值，插入数据时，如果没有为这种列指定值，系统将会使用默认值代替；

--举例
CREATE TABLE employees
 ( employee_id INTEGER NOT NULL
 , first_name CHARACTER VARYING(20)
 , last_name CHARACTER VARYING(25) NOT NULL
 , email CHARACTER VARYING(25) NOT NULL
 , phone_number CHARACTER VARYING(20)
 , hire_date DATE NOT NULL
 , salary NUMERIC(8,2)
 , commission_pct NUMERIC(2,2)
 , manager_id INTEGER
 , department_id INTEGER
 , CONSTRAINT emp_emp_id_pk
 PRIMARY KEY (employee_id)
 , CONSTRAINT emp_salary_min
 CHECK (salary > 0) 
 , CONSTRAINT emp_email_uk
 UNIQUE (email)
 , CONSTRAINT emp_dept_fk
 FOREIGN KEY (department_id)
 REFERENCES departments(department_id)
 , CONSTRAINT emp_manager_fk
 FOREIGN KEY (manager_id)
 REFERENCES employees(employee_id)
 ) ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

模式搜索

CREATE TABLE schema_name.table_name; --创建表的完整语法
1

SHOW search_path; --查看搜索路径，返回参数：当前用户、模式
1

--修改默认的搜索路径
SET search_path=hr,public --先在hr模式中操作，后在public模式中操作

--比如
CREATE TABLE ceshibiao (id int);
1
2
3
4
5

修改表

查看

\d 表名
1

添加字段

-- 添加字段格式
ALTER TABLE table_name
ADD COLUMN column_name data_type column_constraint;

--如果没有default值，新增字段默认使用null值
ALTER TABLE products ADD COLUMN description text;
--新增字段指定默认值
ALTER TABLE products ADD COLUMN notes text DEFAULT 'new product' not 
null;
1
2
3
4
5
6
7
8
9

删除字段

--删除字段，同时删除数据、索引、约束，但是对象引用（外键引用、视图、存储过程）不会删除
ALTER TABLE table_name DROP COLUMN column_name;

--假设departments 表的 department_id 是 employees 表的外键引用列，无法直接删除
--级联删除格式
ALTER TABLE departments DROP COLUMN department_id CASCADE;
1
2
3
4
5
6

添加约束

--添加约束
ALTER TABLE table_name ADD table_constraint;

-- 添加检查约束
ALTER TABLE products ADD CONSTRAINT products_price_min CHECK (price > 0);
-- 添加唯一约束
ALTER TABLE products ADD CONSTRAINT products_name_uk UNIQUE (name);

-- 添加非空约束格式
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
1
2
3
4
5
6
7
8
9
10

删除约束

-- 删除约束格式
--RESTRICT为默认值，如果存在其他依赖于该约束的对象，需要使用CASCADE执行级联删除
ALTER TABLE table_name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]

--删除非空约束
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;
1
2
3
4
5
6

字段类型转换

--转换字段数据类型
--隐式转换：已有数据能兼容新数据类型
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_t;

--将产品表的 price 字段的类型修改为numeric(10,2)
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
1
2
3
4
5
6

--显式转换，使用USING
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type USING
expression

--增加一个可变字符串类型的字段level
ALTER TABLE products ADD COLUMN level VARCHAR(10);
--隐式报错
ALTER TABLE products ALTER COLUMN level TYPE INTEGER;
ERROR: column "level" cannot be cast automatically to type integer
HINT: You might need to specify "USING level::integer".

--改为显式转换
ALTER TABLE products ALTER COLUMN level TYPE INTEGER USING level::integer
1
2
3
4
5
6
7
8
9
10
11
12
13

重命名字段

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;
--举例
ALTER TABLE products
RENAME COLUMN product_no TO product_number
1
2
3
4
5

重命名表

ALTER TABLE table_name
RENAME TO new_name;
--举例
ALTER TABLE products
RENAME TO items;
1
2
3
4
5

删除表

--如果使用了 IF EXISTS，删除一个不存在的表不会产生错误
DROP TABLE [ IF EXISTS ] name [ CASCADE | RESTRICT ]
1
2