PostgreSQL11 | pgsql建表、改表与删表

上一篇文章

postgresql11 | pgadmin4基本使用http://t.csdn.***/IFZpx已经讲解了最简单的pgadmin的数据库创建、外键等可视化的操作，以及对应的pgsql语句

这一篇文章将讲解基础的pgsql语句

建表、改表与删表

目录

建表、改表与删表

创建数据表

单字段主键

多字段联合主键

外键约束

非空约束(Not Null Constraint)

唯一性约束(Unique Constraint)

默认约束(Default Constraint)

修改数据表

修改表名

修改字段的数据类型

修改字段名

添加字段

删除字段

删除表的外键约束

删除数据表

删除没有关联的表

IF EXISTS用法

删除被其他表关联的主表

postgresql11版本的新特性

创建数据表

创建数据表的关键字是CREATE

在为表名起名时，不区分大小写、不能起sql语句关键字（例如：DROP，CREATE等）

在为数据表中每一个列起名时，每个列之间使用英文逗号隔开

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

    [表级别约束条件]
（

例：在pgtest数据库中创建员工表tb_emp1

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

单字段主键

还可以在创建数据库表的时候对某一属性作主键约束，使得每条数据该属性的值表内唯一且不能为空，这与唯一约束不同。

创建一个数据表tb_emp2，id作为主键

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

 还可以在字段全部定义完，在最后加上主键指定

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

多字段联合主键

当我想设计一个表中，name字段和deptid字段的值同步唯一的时候，就可以一次设置两个字段作为联合主键

create table tb_emp4
(
	name varchar(25),
	deptid int,
	salary float,
	primary key(name,deptid)
);

我们可以先存入一条数据

insert into tb_emp4 values ('小明',1,2000);

 通过pgadmin查表按钮之间查出表以便于我们快速输入测试数据看效果

 然后我们直接插入一条新数据，名字重复但部门id不重复

提示我们存储是成功的，反过来也是一样的，但如果当我们两个值全部与已存数据重复之后

 就会报错，所以当开发过程中有这种两个值做主键的需求时就会用到联合唯一

外键约束

用途：外键用来在两张表之间的数据建立链接，可以是一列或者多列。

定义：表中的一个字段，可以不是本表的主键，但必须是应对的另一表的主键。

对于外键来说，两张链接起来的表是有关联关系的

主表（父表）：对于两个具有关联关系的表而言，相关联的字段中主键所在的哪个表就是主键。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的哪个表就是从表。

对于图形化工具pgadmin来说，只需要图形化操作即可，这一部分为了节省篇幅，请读者购买原教材（《postgresql11从入门到精通》清华大学出版社）对照第49页操作，我这里只总结sql语句操作。

先创建一个主表

create table tb_dept1
(
	id int primary key,
	name varchar(22) not null,
	location varchar(50)
);

之后再创建从表，从表中需要员工id、员工姓名、薪资之外，还有用于存储外键id的部门id字段

这个部门id字段要与主表中的主键(id)关联依赖，在建表时从表的建表sql如下

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

关联公式解析：

constraint fk_emp_dept1 foreign key(deptid) references tb_dept1(id)
   约束      <约束名>       外键（字段名）      参照(依赖)  <主表表名>（主表主键字段）
                     （从表中存储主表id的字段）          

非空约束(Not Null Constraint)

用途：如果一张表中没有指定任何一个字段非空约束的话，有可能在存储的时候存储大量的全空的废数据，我们需要对表接收的数据进行约束，每条数据至少有一个值是存在的，这样存储的数据才有意义，所以

定义：被指定的字段的值不能为空

创建一个新表tb_emp6并指定员工的名称不能为空

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

当我们再次存储数据的时候，如果不给名字的值就会报错

唯一性约束(Unique Constraint)

定义：唯一性约束要求添加该约束的列字段的值唯一，允许为空，但只能出现一个空值。唯一性约束可以确保一列或多列不出现重复值。

唯一性约束(unique)与主键(primary key)的区别：一个表中可以有多个字段声明为unique，但只能由一个primary key声明；声明为primary key的列不允许有空值，但是声明为unique的字段允许有空值(NULL)的存在。

可视化操作在原教材书中第51页图片指导。

创建一个新的部门表tb_dept2并指定部门名称为唯一约束

有两种定义方式：

第一种，定义完列直接指定

create table tb_dept2
(
	id int primary key,
	name varchar(22) unique,
	location varchar(50)
);

<字段名> 数据类型 unique

第二种，定义完所有列后指定唯一约束

create table tb_dept3
(
	id int primary key,
	name varchar(22),
	location varchar(50),
	constraint sth unique(name)
);

constraint <约束名> unique(<字段名>)

默认约束(Default Constraint)

定义：默认约束指定某列的默认值

被指定默认约束的列，在插入或修改数据的时候，如果新结果没有传入用户的值，则会默认保存建表时指定的默认值，这个默认值可以通过修改数据表来修改默认值

定义一个新表tb_emp7，指定员工的部门编号默认为1111，为了方便展示这里与原教材不同，不再做主从表的关联

create table tb_emp7
(
	id int primary key,
	name varchar(25) not null,
	deptid int default 1111,
	salary float
);

创建表后插入一条只传id和name值的数据，其他两个值为空

insert into tb_emp7(id,name) values (1,'小明');

 可以看到虽然并没有传入deptid的值，但由于设置了默认约束，所以pgsql自动存储的预设的默认值。

修改数据表

修改数据表是指修改数据库中已经存在的数据库表的表结构。

常用的修改表的操作有：修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

修改表名

alter table <旧表名> rename to <新表名>;

例如我希望把前面创建的表tb_dept3修改成

alter table tb_dept3 rename to tb_department3;

修改字段的数据类型

对于创建好的字段，也可以修改它的数据类型。

alter table <表名> alter column <字段名> type <数据类型>;

例如，将表tb_dept1中的name字段的数据类型varchar(22)修改为text类型

alter table tb_dept1 alter column name type text;

但当表中该字段已经存有数据的时候，不可轻易修改数据类型。

举一个便于理解的例子。

我们可以对表中插入一条数据

insert into tb_dept1(id,name,location) values (1,'小王','shanghai');

当前name类型是text类型的，如果我们尝试修改回varchar(22)，是没有问题的，因为值是在新类型的允许范围内的。

alter table tb_dept1 alter column name type varchar(22);

我们存储的数据是在varchar(22)的范围内的数据，但假如对已存数据不了解，贸然修改类型

返回上一状态，我们将名字修改成一串话

 再把数据类型修改为varchar(4)

alter table tb_dept1 alter column name type varchar(4);

对于varchar(4) 来说，已存在的值是超过范围的。

所以，不是不允许有值条件修改数据类型，而是需要对数据类型范围和已存值数据都了解的前提下修改，或者将数据清空后再修改字段的类型。有一些软件框架为了安全开发甚至直接不允许有值改类型的操作，所以最好是清理数据后改类型。

修改字段名

使用图形工具修改很简单，但下面讲如何通过sql来修改

alter table <表名> rename <待改名字段> to <新字段名><新数据类型,不写默认原类型>;

如果我想把表tb_dept1中的location修改字段名为loc，但数据类型不变

alter table tb_dept1 rename location to loc;

添加字段

表中的字段增减是正常的，现展示已存表增加字段的方法

alter table <表名> add column <新字段名，必填> <数据类型，必填>;

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

假如，现在对表tb_dept1增加新字段，一个没有完整性约束的int类型字段managerid（部门经理编号），sql如下

alter table tb_dept1 add column managerid int;

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

在增加一个完整性约束条件的字段时，分为两个情况，一个是有默认值的一个是无默认值的。

假设再增加一个完整性约束的字段column1的话，sql如下

alter table tb_dept1 add column column1 varchar(12) not null default 'foo';

 这里需注意，我所展示的sql与原教材上的sql不同，完整性约束要求的字段在定义时有默认值，这个默认值在当前表中已有数据，但我们后加了新的完整性约束字段的话就会出现以下报错

 若我们不希望后续添加的完整性约束字段有默认值，就必须先将表中数据清理后再增加这一字段

delete from tb_dept1;

然后再运行这一sql

alter table tb_dept1 add column column2 varchar(12) not null;

删除字段

将表中的无用字段进行删除

alter table <表名> drop <字段名>;

例如将上面增加的字段managerid删掉，sql如下

alter table tb_dept1 drop managerid;

删除表的外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主从表的关联关系。

alter table <表名> drop constraint <外键名>;

先新建一个新表tb_emp9，然后在该表以deptid字段创建一个外键关联tb_dept1表中的主键id

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;

删除数据表

删除数据表是指将数据库中已经存在的表从数据库中删除。

删除没有关联的表

drop table <表名>;

例如，我要删除tb_emp2表

drop table tb_emp2;

IF EXISTS用法

在删除表时也有删除了一个不存在的表的时候，对于错误的sql，pgsql有一个提示警告而并非中断运行报错的方法

用法：

drop table if exists <表名>;

例如，同样的我们都是删除一个不存在的表xiaoming，使用前后

drop table xiaoming;

drop table if exists xiaoming;

删除被其他表关联的主表

在数据表中存在关联的主从表，如果直接删除主表的话，就会提示报错，这是因为主表的消失会破坏子表与主表的关联关系，如果必须删除主表则必须先删除所有有关联的子表外键之后再删除主表。

例如如下例子：

先创建一个表tb_dept3

create table tb_dept3
(
	id int primary key,
	name varchar(22),
	location varchar(50)
);

再创建子表

create table tb_emp10
(
	id int primary key,
	name varchar(25),
	deptid int,
	salary float,
	constraint fk_emp_dept foreign key(deptid) references tb_dept3(id)
);

如果这时贸然去删除主表则会报错

 就是因为子表外键导致的，所以要想要删掉主表就必须先删除子表的外键

alter table tb_emp10 drop constraint fk_emp_dept;

 然后再删除主表即可

drop table tb_dept3;

postgresql11版本的新特性

有关postgresql11版本的新特性——新增带默认值的字段不再重写数据表的相关内容请通过原教材《postgresql11从入门到精通》（清华大学出版社）第63页开始了解，属于11版本的新增功能，为了文章有普适性该部分作为自由了解范围，感谢理解。

作者的话（Alvin）：

以上是有关原书第四章的总结与拓展，为提问与解答可以帮助更多人，本博客模拟GitHub的issue方案，所以私信已关，有问题请在评论区直接指正与提问，允许转发、复制或引用本文章，必须遵守开源法则注释来源与作者，感谢您的阅读。