postgresql14-表的管理(四)

表table

创建表

CREATE TABLE table_name --表名
(
 column_name data_type column_constraint, --字段名、字段类型、约束字段(可选)
 column_name data_type, --表级别约束字段
 ...,
 table_constraint
);
CREATE TABLE emp1 --创建表
AS
SELECT * FROM employees; --携带数据导入
-- WHERE 1=0; 不携带数据导入
SELECT *
INTO emp2
FROM employees;

字段类型举例

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

模式搜索

CREATE TABLE schema_name.table_name; --创建表的完整语法
SHOW search_path; --查看搜索路径,返回参数:当前用户、模式

在这里插入图片描述

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

--比如
CREATE TABLE ceshibiao (id int);

在这里插入图片描述

修改表

查看

d 表名

添加字段

-- 添加字段格式
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;

删除字段

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

--假设departments 表的 department_id 是 employees 表的外键引用列,无法直接删除
--级联删除格式
ALTER TABLE departments DROP COLUMN department_id CASCADE;

添加约束

--添加约束
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;

删除约束

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

--删除非空约束
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

字段类型转换

--转换字段数据类型
--隐式转换:已有数据能兼容新数据类型
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);
--显式转换,使用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

重命名字段

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;
--举例
ALTER TABLE products
RENAME COLUMN product_no TO product_number

重命名表

ALTER TABLE table_name
RENAME TO new_name;
--举例
ALTER TABLE products
RENAME TO items;

删除表

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