PostgreSQL——表的基本操作

一、创建数据表

CREATE TABLE <表名>
(
	字段名1 数据类型 [列级别约束条件] [默认值],
	字段名2 数据类型 [列级别约束条件] [默认值],
	...
	[表级别约束条件]
);

• 要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等
• 数据表中如果创建多个列，每一个列（字段）的名称和数据类型都要用逗号隔开

CREATE TABLE tb_emp1
(
	id INT,
	name VARCHAR(25),
	deptId INT,
	salary FLOAT
);

1.1、主键约束

1.1.1、单字段主键

主键由一个字段组成，SQL语句分为以下两种情况：

1. 在定义列的同时指定主键：

字段名 数据类型 PRIMARY KEY

CREATE TABLE tb_emp2
(
	id INT PRIMARY KEY,
	name VARCHAR(25),
	deptId INT,
	salary FLOAT
);

2. 在定义完所有列之后指定主键：

[CONSTRAINT <约束名>] PRIMARY KEY 字段名

CREATE TABLE tb_emp3
(
	id INT,
	name VARCHAR(25),
	deptId INT,
	salary FLOAT,
	PRIMARY KEY(id)
);

1.1.2、多字段联合主键

PRIMARY KEY [字段1, 字段2, ..., 字段n]

CREATE TABLE tb_emp4
(
	name VARCHAR(25),
	deptId INT,
	salary FLOAT,
	PRIMARY KEY(name, deptId)
);

1.2、外键约束

外键用来在两个表的数据之间建立连接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键必须等于另一个表中主键的某个值。

[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [ , 字段名2, ...] REFERENCES <主表名> 主键列1 [ , 主键列2, ...]

定义数据表tb_emp5，并在tb_emp5表上创建外键约束，关联到tb_dept1的主键id。

CREATE TABLE tb_dept1
(
	id INT PRIMARY KEY,
	name VARCHAR(22) NOT NULL,
	location VARCHAR(50)
);

CREATE TABLE tb_emp5
(
	id INT PRIMARY KEY,
	name VARCHAR(25),
	deptId INT,
	salary FLOAT,
	CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

1.3、非空约束

非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时，没有指定值，数据库系统会报错。

字段名 数据类型 NOT NULL

CREATE TABLE tb_emp6
(
	id INT PRIMARY KEY,
	name VARCHAR(25) NOT NULL,
	deptId INT,
	salary FLOAT,
	CONSTRAINT fk_emp_dept2 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

1.4、唯一性约束

唯一性约束（Unique Constraint）要求添加该约束的列字段的值唯一，允许为空，但只能出现一个空值。唯一性约束可以确保一列或者几列不出现重复值。

1. 在定义完列之后指定指定唯一约束：

字段名 数据类型 UNIQUE

CREATE TABLE tb_dept2
(
	id INT PRIMARY KEY,
	name VARCHAR(22) UNIQUE,
	location VARCHAR(50)
);

2. 在定义完所有列之后指定唯一约束：

[CONSTRAINT <约束名>] UNIQUE (<字段名>)

CREATE TABLE tb_dept3
(
	id INT PRIMARY KEY,
	name VARCHAR(22),
	location VARCHAR(50),
	CONSTRAINT STH UNIQUE(name)
);

1.5、默认约束

默认约束（Default Constraint）指定某列的默认值。

字段名 数据类型 DEFAULT 默认值

CREATE TABLE tb_emp7
(
	id INT PRIMARY KEY,
	name VARCHAR(22) NOT NULL,
	deptId INT DEFAULT 1111,
	salary FLOAT,
	CONSTRAINT fk_emp_dept3 FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);

2、修改数据表

2.1、修改表名

ALTER TABLE <旧表名> RENAME TO <新表名>;

-- 将tb_dept3改名为tb_department3
ALTER TABLE tb_dept3 RENAME TO tb_department3;

2.2、修改字段的数据类型

ALTER TABLE <表名> ALTER COLUMN <字段名> TYPE <数据类型>

-- 将tb_dept1中name字段的数据类型由VARCHAR(22)修改成CARCHAR(30)
ALTER TABLE tb_dept1 ALTER COLUMN name TYPE VARCHAR(30);

2.3、修改字段名

ALTER TABLE <表名> RENAME <旧字段名> TO <新字段名> <新数据类型>;

-- 将tb_dept1中的location改名为loc，数据类型不变
ALTER TABLE tb_dept1 RENAME location TO loc;

2.4、添加字段

一般情况下，一个完整字段包括字段名、数据类型、完整性约束，语法如下：

ALTER TABLE <表名> ADD COLUMN <新字段名> <数据类型>

1. 添加无完整性约束的条件的字段：

-- 在tb_dept1中添加一个没有完整性约束的INT类型的managerId
ALTER TABLE tb_dept1 ADD COLUMN managerId INT;

2. 添加有完整性约束条件的字段：

-- 在tb_dept1中添加一个不为空的VARCHAR(12)类型的column1
ALTER TABLE tb_dept1 ADD COLUMN column1 VARCHAR(12) NOT NULL;

2.5、删除字段

ALTER TABLE <表名> DROP <字段名>;

-- 删除tb_dept1中的column2字段
ALTER TABLE tb_dept1 DROP column2;

2.6、删除表的外键约束

ALTER TABLE <表名> DROP CONSTRAINT <外键约束名>

-- 删除数据表tb_emp9中外键约束
CREATE TABLE tb_emp9
(
	id INT PRIMARY KEY,
	name VARCHAR(25),
	deptId INT,
	salary FLOAT,
	CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);

ALTER TABLE tb_emp9 DROP CONSTRAINT fk_emp_dept;

3、删除数据表

3.1、删除没有被关联的表

DROP TABLE [IF EXISTS] 表1, 表2, ..., 表n;

-- 删除数据表tb_dept2
DROP TABLE IF EXISTS tb_dept2;

3.2、删除被其他表关联的主表

数据表之间存在外键关联的情况，如果直接删除父表，结果会显示失败，原因是直接删除将破坏表的参照完整性。如果必须要删除，可以先删除与之关联的子表，再删除父表。但是这样同时删除了两个表中的数据。有的情况下可能要保留子表，这时要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表：

-- 父表
CREATE TABLE tb_dept2
(
	id INT PRIMARY KEY,
	name VARCHAR(22),
	location VARCHAR(50)
);

-- 子表
CREATE TABLE tb_emp
(
	id INT PRIMARY KEY,
	name VARCHAR(25),
	deptId INT,
	salary FLOAT,
	CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept2(id)
);

-- 直接删除，会报错
DROP TABLE tb_dept2;

-- 接触外键约束再删除
ALTER TABLE tb_emp DROP CONSTRAINT fk_emp_dept;
DROP TABLE tb_dept2;