PostgreSQL-数据库命令_psql连接数据库

PostgreSQL-数据库命令

介绍

一个数据库是一个或多个模式的集合，而模式包含表、函数等。因此，完整的逻辑组织结构层次是服务器实例（PostgreSQL Server）、数据库（Database）、模式（Schema）、表（Table），以及某些其他对象（如函数）。一个PostgreSQL服务器实例可以管理多个数据库。当应用程序连接到一个数据库时，一般只能访问该数据库中的数据。

数据库命令

database

连接数据库

-- 默认连接，连接postgres数据库
psql
-- 指定配置连接数据库
psql -h localhost -p 5432 -U postgress dbname
1
2
3
4

查看数据库

\l
1

创建数据库

语法

CREATE DATABASE name
[
  [WITH][OWNER[=]user_name]
 [TEMPLATE[=]template]
 [ENCODING[=]encoding]
 [LC_COLLATE[=]lc_collate]
 [LC_CTYPE[=]lc_ctype]
 [TABLESPACE[=]tablespace_name]
 [ALLOW_CONNECTIONS[=]allowconn]
 [CONNECTION LIMIT[=]connlimit]
 [IS_TEMPLATE[=]istemplate]
]
1
2
3
4
5
6
7
8
9
10
11
12

参数说明：

• name：要创建的数据库的名称
• user_name：要创建的数据库所属的用户。如果没有指定，则默认属于执行该命令的用户
• template：要创建的数据库所用的模版库，默认的模版库是template1
• encoding：要创建的数据库所使用的字符集。如果没有指定，则默认使用其模版库的字符集
• lc_collate：要创建的数据库所使用的collation顺序。这会影响在ORDER_BY语法中字符串类型列的顺序，也会影响text类型列的索引顺序。如果没有指定，则默认使用其模版库的collation顺序
• lc_ctype：要创建的数据库所使用的字符分类。如大小写和数字。如果没有指定，则默认使用其模版库的字符分类
• tablespace_name：要创建的数据库所使用的字符分类
• allowconn：是否可以连接该数据库，默认设置为true。如果设置为false，则任何用户都不能连接该数据库
• connlimit：允许并发连接该数据库的个数。默认设置为-1，即没有限制
• istemplate：是否是模版库，默认设置为false。如果设置为true，则任何具有创建数据库权限的用户均可以用其复制新的数据库；如果设置为false，则只有超级用户和该数据库的用户可以其复制新的数据库

示例

创建数据库，命名为db_test

CREATE DATABASE db_test;
1

创建数据库，名为为db_test01，所属用户为postgres，关联的表空间为test

CREATE DATABASE db_test01 OWNER postgres TABLESPACE test;
1

到表空间目录下，可以看到创建了新的文件夹

修改数据库

语法

ALTER DATABASE name[[WITH]option[...]]
1

参数说明：

• ALLOW_CONNECTIONS allowconn
• CONNECTION LIMIT connlimit
• IS_TEMPLATE istemplate
• ALTER DATABASE name RENAME TO new_name
• ALTER DATABASE name OWNER TO {new_owner|CURRENT_USER|SESSION_USER}
• ALTER DATABASE name SET TABLESPACE new_tablespace
• ALTER DATABASE name SET configuration_parameter{TO|=}{value|DEFAULT}
• ALTER DATABASE name SET configuration_parameter FROM CURRENT
• ALTER DATABASE name RESET configuration_parameter
• ALTER DATABASE name RESET ALL

将数据库“test”的名称改为“test01”

ALTER DATABASE test RENAME TO test01;
1

将数据库“test01”中的最大连接数改为“100”

ALTER DATABASE test01 CONNECTION LIMIT 100;
1

将数据库“test01”的索引扫描关闭，当用户连接到该数据库时就将enable_indexscan设置为off

ALTER DATABASE test01 SET enable_indexscan TO off;
1

删除数据库

语法

DROP DATABASE [IF EXISTS]name
1

加上“IF EXISTS”可选关键字，则在删除数据库时，如果该数据库不存在，不会报错，只会打印NOTICE信息

删除一个已经存在数据库“test01”

DROP DATABASE test01;
1

切换数据库

\c dbname
1

表格命令

table

创建表

普通表

create table company
(
    id      integer not null
        constraint company_pkey
            primary key,
    name    text    not null,
    age     integer not null,
    address char(50),
    salary  real
);

comment on column company.id is '唯一id';

comment on column company.name is '名称';

comment on column company.age is '年龄';

comment on column company.address is '地址';

comment on column company.salary is '薪水';

alter table company
    owner to postgres;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

空间表

创建带有空间属性字段的表

geom不指定坐标系和数据类型，则可以存储所有类型和坐标的数据，不过在查询的时候需要知道数据的坐标系

也可以指定坐标系和数据类型，如geom geometry(MultiPolygon,4490), geom geometry(Polygon,4326),

create table geo
(
    geom geometry
);

alter table geo
    owner to postgres;
1
2
3
4
5
6
7

自动增长（AUTO INCREMENT）

AUTO INCREMENT（自动增长） 会在新记录插入表中时生成一个唯一的数字。

PostgreSQL 使用序列来标识字段的自增长，数据类型有 smallserial、serial 和 bigserial 。这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7

id设置自增

create sequence company_id_seq;

alter table company alter column id set default nextval('public.company_id_seq');

alter sequence company_id_seq owned by company.id;
1
2
3
4
5

外键

级联删除

ALTER TABLE public.auth_role_permission
    ADD FOREIGN KEY (role_id)
    REFERENCES public.auth_role (id)
    ON UPDATE NO ACTION
    ON DELETE CASCADE
    NOT VALID;
CREATE INDEX auth_role_permission_role_id
    ON public.auth_role_permission(role_id);
1
2
3
4
5
6
7
8

约束

常见约束

• NOT NULL：指示某列不能存储NULL值
• UNIQUE：确定某列的值都是唯一的
• PRIMARY Key：NOT NULL和UNIQUE的结合确保某列（或两个列多个列的结合）有唯一标示
• FOREIGN Key：保证一个表中的数据匹配另一表中的值的参照完整性
• CHECK：保证列中的值符合指定的条件
• EXCLUSION：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回false或空值

使用EXCLUSION约束

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,  -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
   AGE WITH <>)   -- 其比较的结果是如果整个表边式返回 true，则不允许插入，否则允许
);
1
2
3
4
5
6
7
8
9
10

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );  
-- 此条数据的 NAME 与第一条相同，且 AGE 与第一条也相同，故满足插入条件
INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );
-- 此数据与上面数据的 NAME 相同，但 AGE 不相同，故不允许插入
1
2
3
4
5

前面两条顺利添加的 COMPANY7 表中，但是第三条则会报错：

ERROR:  duplicate key value violates unique constraint "company7_pkey"
DETAIL:  Key (id)=(3) already exists.
1
2

删除约束

删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 \d 表名 可以找到这些信息。

通用语法如下：

ALTER TABLE table_name DROP CONSTRAINT some_name;
1

编辑表结构

在 PostgreSQL 中，ALTER TABLE 命令用于添加，修改，删除一张已经存在表的列。

另外你也可以用 ALTER TABLE 命令添加和删除约束。

新增表字段

alter table company
	add mark varchar;

comment on column company.mark is '备注';
1
2
3
4

删除表字段

alter table company drop column mark;
1

修改表字段

alter table company rename column mark to remark;

comment on column company.remark is '备注1';
1
2
3

语法

用 ALTER TABLE 在一张已存在的表上添加列的语法如下：

ALTER TABLE table_name ADD column_name datatype;
1

在一张已存在的表上 DROP COLUMN（删除列），语法如下：

ALTER TABLE table_name DROP COLUMN column_name;
1

修改表中某列的 DATA TYPE（数据类型），语法如下：

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
1

给表中某列添加 NOT NULL 约束，语法如下：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
1

给表中某列 ADD UNIQUE CONSTRAINT（ 添加 UNIQUE 约束），语法如下：

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
1
2

给表中 ADD CHECK CONSTRAINT（添加 CHECK 约束），语法如下：

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
1
2

给表 ADD PRIMARY KEY（添加主键），语法如下：

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
1
2

DROP CONSTRAINT （删除约束），语法如下：

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;
1
2

如果是 MYSQL ，代码是这样：

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;
1
2

DROP PRIMARY KEY （删除主键），语法如下：

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;
1
2

如果是 MYSQL ，代码是这样：

ALTER TABLE table_name
DROP PRIMARY KEY;
1
2

查看数据库中的表

\d
1

查看表的详细信息

\d tablename
1

删除表

DROP TABLE table_name;
1

数据清理

PostgreSQL 中 TRUNCATE TABLE 用于删除表的数据，但不删除表结构。

也可以用 DROP TABLE 删除表，但是这个命令会连表的结构一起删除，如果想插入数据，需要重新建立这张表。

TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。 此外，TRUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大型表上非常有用。

PostgreSQL VACUUM 操作用于释放、再利用更新/删除行所占据的磁盘空间。

TRUNCATE TABLE  table_name;
1

清空表并保留表结构

一般情况下，我们使用delete删除表中数据，但是delete是一条数据一条数据来删除表中的数据，直至表清空（保留表结构），但是当数据量很大时，它耗时较久。
其实，删除表数据但保留表结构使用truncate更快速安全，使用方法为：

#当表没有其他关系时
TRUNCATE TABLE tablename;
#当表中有外键时，要用级联方式删所有关联的数据
TRUNCATE TABLE tablename CASCADE;
1
2
3
4

清空数据库还原数据库为新建时的状态

在postgresql中，创建数据库时会自动创建public模式，一般我们把表都保存在该模式中，因此直接删除该模式再重新创建该模式。
若数据在其他模式中，则把public换为数据表所在模式即可。

#删除public模式以及模式里面所有的对象
DROP SCHEMA public CASCADE;
#创建public模式
CREATE SCHEMA public;
1
2
3
4

模式命令

创建模式

create schema myschema;
1

在指定模式创建表

reate table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
1
2
3
4
5
6
7
8

删除模式

删除一个为空的模式，其中的所有对象已经被删除

DROP SCHEMA myschema;
1

删除一个模式以及其中包含的所有对象：

DROP SCHEMA myschema CASCADE;
1

权限（PRIVILEGES）

无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。

对于大多数类型的对象，初始状态是只有所有者(或超级用户)才能修改或删除对象。要允许其他角色或用户使用它，必须为该用户设置权限。

在 PostgreSQL 中，权限分为以下几种：

• SELECT
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• REFERENCES
• TRIGGER
• CREATE
• CONNECT
• TEMPORARY
• EXECUTE
• USAGE

根据对象的类型(表、函数等)，将指定权限应用于该对象。

要向用户分配权限，可以使用 GRANT 命令。

GRANT 语法

GRANT 命令的基本语法如下：

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }
1
2
3

• privilege − 值可以为：SELECT，INSERT，UPDATE，DELETE， RULE，ALL。
• object − 要授予访问权限的对象名称。可能的对象有： table， view，sequence。
• PUBLIC − 表示所有用户。
• GROUP group − 为用户组授予权限。
• username − 要授予权限的用户名。PUBLIC 是代表所有用户的简短形式。

GRANT ALL ON COMPANY TO runoob;
REVOKE ALL ON COMPANY FROM runoob;
DROP USER runoob;
1
2
3