PostgreSQL11 | pgsql建表、改表与删表
阿里云国内75折 回扣 微信号:monov8 |
阿里云国际,腾讯云国际,低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号:monov8 飞机:@monov6 |
上一篇文章
PostgreSQL11 | pgadmin4基本使用http://t.csdn.cn/IFZpx已经讲解了最简单的pgadmin的数据库创建、外键等可视化的操作以及对应的pgsql语句
这一篇文章将讲解基础的pgsql语句
建表、改表与删表
目录
创建数据表
创建数据表的关键字是CREATE
在为表名起名时不区分大小写、不能起sql语句关键字例如DROPCREATE等
在为数据表中每一个列起名时每个列之间使用英文逗号隔开
CREATE TABLE <表名>
字段名1 数据类型 [列级别约束条件][默认值],
字段名2 数据类型 [列级别约束条件][默认值],
.....
[表级别约束条件]
例在pgtest数据库中创建员工表tb_emp1
字段名称 | 数据类型 | 备注 |
---|---|---|
id | INT | 员工编号 |
name | VARCHAR(25) | 员工名称 |
deptid | INT | 所在部门编号 |
salary | FLOAT | 工资 |
CREATE TABLE tb_emp1
(
id INT,
name VARCHAR(25),
deptid INT,
salary FLOAT
);
单字段主键
还可以在创建数据库表的时候对某一属性作主键约束使得每条数据该属性的值表内唯一且不能为空这与唯一约束不同。
创建一个数据表tb_emp2id作为主键
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 | int | 部门id |
name | varchar(22) | 部门名称 |
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用法
在删除表时也有删除了一个不存在的表的时候对于错误的sqlpgsql有一个提示警告而并非中断运行报错的方法
用法
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方案所以私信已关有问题请在评论区直接指正与提问允许转发、复制或引用本文章必须遵守开源法则注释来源与作者感谢您的阅读。