目录
前言
上一篇PostgreSQL入门基本语法之DDL-(user、database、schema)，介绍了user、database、schema对象的DDL基本操作，本篇章内容介绍一下table对象的DDL基本语法。
4、表对象操作
4.1 创建表
4.1.1 语法
命令： CREATE TABLE
描述： 建立新的数据表
语法：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] 表名 ( [
{ 列名称 数据_类型 [ COLLATE 校对规则 ] [ 列约束 [ ... ] ]
| 表约束
| LIKE 源表 [ like选项 ... ] }
[, ... ]
] )
[ INHERITS ( 父表 [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { 列名称 | ( 表达式 ) } [ COLLATE 校对规则 ] [ 操作符类型的名称 ] [, ... ] ) ]
[ WITH ( 存储参数 [= 值] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE 表空间的名称 ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] 表名
OF 类型名称 [ (
{ 列名称 [ WITH OPTIONS ] [ 列约束 [ ... ] ]
| 表约束 }
[, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { 列名称 | ( 表达式 ) } [ COLLATE 校对规则 ] [ 操作符类型的名称 ] [, ... ] ) ]
[ WITH ( 存储参数 [= 值] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE 表空间的名称 ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] 表名
PARTITION OF 父表 [ (
{ 列名称 [ WITH OPTIONS ] [ 列约束 [ ... ] ]
| 表约束 }
[, ... ]
) ] FOR VALUES partition_bound_spec
[ PARTITION BY { RANGE | LIST | HASH } ( { 列名称 | ( 表达式 ) } [ COLLATE 校对规则 ] [ 操作符类型的名称 ] [, ... ] ) ]
[ WITH ( 存储参数 [= 值] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE 表空间的名称 ]
列的约束是:
[ CONSTRAINT 约束名称 ]
{ NOT NULL |
NULL |
CHECK ( 表达式 ) [ NO INHERIT ] |
DEFAULT 默认_表达式 |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
UNIQUE 索引参数 |
PRIMARY KEY 索引参数 |
REFERENCES 所引用的表 [ ( 所引用的列 ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE 操作 ] [ ON UPDATE 操作 ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
表约束是:
[ CONSTRAINT 约束名称 ]
{ CHECK ( 表达式 ) [ NO INHERIT ] |
UNIQUE ( 列名称 [, ... ] ) 索引参数 |
PRIMARY KEY ( 列名称 [, ... ] ) 索引参数 |
EXCLUDE [ USING 访问索引的方法 ] ( 排除项 WITH 运算子 [, ... ] ) 索引参数 [ WHERE ( 述词 ) ] |
FOREIGN KEY ( 列名称 [, ... ] ) REFERENCES 所引用的表 [ ( 所引用的列 [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE 操作 ] [ ON UPDATE 操作 ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
like_选项是
{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
and partition_bound_spec is:
IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
在UNIQUE, PRIMARY KEY和EXCLUDE中的索引参数是：
[ WITH ( 存储参数 [= 值] [, ... ] ) ]
[ USING INDEX TABLESPACE 表空间的名称 ]
在EXCLUDE约束中的排除项是：
{ 列名称 | ( 表达式 ) } [ 操作符类型的名称 ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
4.1.2 常用例子
例一：创建表
create table t_test1(id serial,name varchar,age int);
例二：创建表时指定主键
create table t_test2(id serial primary key,name varchar,age int);
PostgreSQL中，主键默认会创建一条主键索引。
例三：创建表时设置列非空约束
create table t_test3(id int not null,name varchar,age int);
4.2 修改表
4.2.1 语法
命令： ALTER TABLE
描述： 更改数据表的定义
语法：
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名称 [ * ]
操作 [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名称 [ * ]
RENAME [ COLUMN ] 列名称 TO new_column_name(新列名)
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名称 [ * ]
RENAME CONSTRAINT 约束名称 TO new_constraint_name(新约束名)
ALTER TABLE [ IF EXISTS ] 名称
RENAME TO 新的名称
ALTER TABLE [ IF EXISTS ] 名称
SET SCHEMA 新的模式
ALTER TABLE ALL IN TABLESPACE 名称 [ OWNED BY 角色名称 [, ... ] ]
SET TABLESPACE 新的表空间 [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] 名称
ATTACH PARTITION partition_name FOR VALUES partition_bound_spec
ALTER TABLE [ IF EXISTS ] 名称
DETACH PARTITION partition_name
操作可以是下列选项之一
ADD [ COLUMN ] [ IF NOT EXISTS ] 列名称 数据_类型 [ COLLATE 校对规则 ] [ 列约束 [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] 列名称 [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] 列名称 [ SET DATA ] TYPE 数据_类型 [ COLLATE 校对规则 ] [ USING 表达式 ]
ALTER [ COLUMN ] 列名称 SET DEFAULT 表达式
ALTER [ COLUMN ] 列名称 DROP DEFAULT
ALTER [ COLUMN ] 列名称 { SET | DROP } NOT NULL
ALTER [ COLUMN ] 列名称 ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
ALTER [ COLUMN ] 列名称 { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] 重新启动 后的序列值 ] } [...
ALTER [ COLUMN ] 列名称 DROP IDENTITY [ IF EXISTS ]
ALTER [ COLUMN ] 列名称 SET STATISTICS 整数
ALTER [ COLUMN ] 列名称 SET ( 属性选项 = 值 [, ... ] )
ALTER [ COLUMN ] 列名称 RESET ( 属性选项 [, ... ] )
ALTER [ COLUMN ] 列名称 SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD 表约束 [ NOT VALID ]
ADD table_constraint_using_index(表约束使用索引)
ALTER CONSTRAINT 约束名称 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
VALIDATE CONSTRAINT 约束名称
DROP CONSTRAINT [ IF EXISTS ] 约束名称 [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ 触发器_名称 | ALL | USER ]
ENABLE TRIGGER [ 触发器_名称 | ALL | USER ]
ENABLE REPLICA TRIGGER 触发器_名称
ENABLE ALWAYS TRIGGER 触发器_名称
DISABLE RULE 重写规则名称
ENABLE RULE 重写规则名称
ENABLE REPLICA RULE 重写规则名称
ENABLE ALWAYS RULE 重写规则名称
DISABLE ROW LEVEL SECURITY
ENABLE ROW LEVEL SECURITY
FORCE ROW LEVEL SECURITY
NO FORCE ROW LEVEL SECURITY
CLUSTER ON 索引名称
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
SET TABLESPACE 新的表空间
SET { LOGGED | UNLOGGED }
SET ( 存储参数 = 值 [, ... ] )
RESET ( 存储参数 [, ... ] )
INHERIT 父表
NO INHERIT 父表
OF 类型名称
NOT OF
OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
REPLICA IDENTITY { DEFAULT | USING INDEX 索引名称 | FULL | NOTHING }
table_constraint_using_index 是:
[ CONSTRAINT 约束名称 ]
{ UNIQUE | PRIMARY KEY } USING INDEX 索引名称
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
4.2.2 常用例子
例一：修改表名
alter table t_test1 rename to t_test;
重命名表t_test1为t_test。
例二：新增/删除字段
新增
alter table t_test add column code numeric;
给表t_test 新增一个名为code，类型为numeric的表字段。
删除
alter table t_test drop column code ;
删除表t_test的名为code的表字段。
例三：修改字段
alter table t_test alter column age type bigint using age::bigint ;
修改表t_test的字段age的类型为bigint。使用using age::bigint的好处是使得该列的数据进行强制转换为bigint类型。
例四：修改约束
添加约束
alter table t_test add constraint test_age_check check(age<20);
为表t_test的age字段添加一个检查约束
删除约束
alter table t_test drop constraint test_age_check;
删除名为test_age_check的约束
4.3 删除表
4.3.1 语法
命令： DROP TABLE
描述： 移除数据表
语法：
DROP TABLE [ IF EXISTS ] 名称 [, ...] [ CASCADE | RESTRICT ]
4.3.2 常用例子
drop table if exists t_test;
删除表t_test。
5、表数据操作
4.1 插入数据
4.1.1 语法
命令： INSERT
描述： 在表中创建新数据行
语法：
[ WITH [ RECURSIVE ] with查询语句(with_query) [, ...] ]
INSERT INTO 表名 [ AS 别名 ] [ ( 列名称 [, ...] ) ]
[ OVERRIDING { SYSTEM | USER} VALUE ]
{ DEFAULT VALUES | VALUES ( { 表达式 | DEFAULT } [, ...] ) [, ...] | 查询 }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | 输出表达式 [ [ AS ] 输出名称 ] [, ...] ]
这里conflict_target可以是下列之一：
( { index_column_name | ( index_expression ) } [ COLLATE 校对规则 ] [ 操作符类型的名称 ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT 约束名称
并且conflict_action是下列之一：
DO NOTHING
DO UPDATE SET { 列名称 = { 表达式 | DEFAULT } |
( 列名称 [, ...] ) = [ ROW ] ( { 表达式 | DEFAULT } [, ...] ) |
( 列名称 [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE 条件 ]
5.1.2 常用例子
例一：单条插入
insert into t_test2(name,age) values('darion',15);
向表中插入一行数据，由于id列为自增列，因此不手动插入数据。
例二：批量插入
insert into t_test2(name,age) values('duke',16),('张三疯',17),('渣渣辉',18);
向表中插入3行数据（多行可以在values后面使用”(值1),(值2)...(值n);”进行罗列，然后做一次插入）
5.2 更新数据
5.2.1 语法
命令： UPDATE
描述： 更新数据表中的数据列
语法：
[ WITH [ RECURSIVE ] with查询语句(with_query) [, ...] ]
UPDATE [ ONLY ] 表名 [ * ] [ [ AS ] 别名 ]
SET { 列名称 = { 表达式 | DEFAULT } |
( 列名称 [, ...] ) = [ ROW ] ( { 表达式 | DEFAULT } [, ...] ) |
( 列名称 [, ...] ) = ( sub-SELECT )
} [, ...]
[ FROM from列表(from_list) ]
[ WHERE 条件 | WHERE CURRENT OF 游标名称 ]
[ RETURNING * | 输出表达式 [ [ AS ] 输出名称 ] [, ...] ]
5.2.2 常用例子
update t_test2 set name='欧阳疯' where id=4;
将表t_test2的Id列值为4的数据中的name字段值更新为 欧阳疯。
5.3 删除数据
5.3.1 语法
命令： DELETE
描述： 删除数据表中的数据列
语法：
[ WITH [ RECURSIVE ] with查询语句(with_query) [, ...] ]
DELETE FROM [ ONLY ] 表名 [ * ] [ [ AS ] 别名 ]
[ USING USING列表(using_list) ]
[ WHERE 条件 | WHERE CURRENT OF 游标名称 ]
[ RETURNING * | 输出表达式 [ [ AS ] 输出名称 ] [, ...] ]
5.3.2 常用例子
delete from t_test2 where id=4;
删除表t_test2中id列值为4 的数据。
5.4 删除表数据基本注意事项
注意：
5.4.1 注意点一，delete时带条件
使用delete时不带where条件,则会将表中的数据全部删除。
5.4.2 注意点二：删除全部数据后考虑取序列值的列
使用delete删除全部数据，继续往表中插入数据时，自增列的***不会重置，而是继续+1。
使用truncate清空表数据，自增列***也不会重置。
解决序列值重置问题
如果清空表数据库后，需要重置序列值，则可使用以下语法
alter sequence 序列名称 restart; --将该序列取值置为初始值
alter sequence 序列名称 restart with 100; 将下一个取值置为100
