PostgreSQL-数据库命令
介绍
一个数据库是一个或多个模式的集合,而模式包含表、函数等。因此,完整的逻辑组织结构层次是服务器实例(PostgreSQL Server)、数据库(Database)、模式(Schema)、表(Table),以及某些其他对象(如函数)。一个PostgreSQL服务器实例可以管理多个数据库。当应用程序连接到一个数据库时,一般只能访问该数据库中的数据。
数据库命令
database
连接数据库
-- 默认连接,连接postgres数据库
psql
-- 指定配置连接数据库
psql -h localhost -p 5432 -U postgress dbname
查看数据库
\l
创建数据库
语法
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]
]
参数说明:
- 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;
创建数据库,名为为db_test01,所属用户为postgres,关联的表空间为test
CREATE DATABASE db_test01 OWNER postgres TABLESPACE test;
到表空间目录下,可以看到创建了新的文件夹
修改数据库
语法
ALTER DATABASE name[[WITH]option[...]]
参数说明:
- 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;
将数据库“test01”中的最大连接数改为“100”
ALTER DATABASE test01 CONNECTION LIMIT 100;
将数据库“test01”的索引扫描关闭,当用户连接到该数据库时就将enable_indexscan设置为off
ALTER DATABASE test01 SET enable_indexscan TO off;
删除数据库
语法
DROP DATABASE [IF EXISTS]name
加上“IF EXISTS”可选关键字,则在删除数据库时,如果该数据库不存在,不会报错,只会打印NOTICE信息
删除一个已经存在数据库“test01”
DROP DATABASE test01;
切换数据库
\c dbname
表格命令
table
创建表
普通表
create table company
(id integer not nullconstraint company_pkeyprimary 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 companyowner to postgres;
空间表
创建带有空间属性字段的表
geom不指定坐标系和数据类型,则可以存储所有类型和坐标的数据,不过在查询的时候需要知道数据的坐标系
也可以指定坐标系和数据类型,如geom geometry(MultiPolygon,4490), geom geometry(Polygon,4326),
create table geo
(geom geometry
);alter table geoowner to postgres;
自动增长(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;
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;
外键
级联删除
ALTER TABLE public.auth_role_permissionADD FOREIGN KEY (role_id)REFERENCES public.auth_role (id)ON UPDATE NO ACTIONON DELETE CASCADENOT VALID;
CREATE INDEX auth_role_permission_role_idON public.auth_role_permission(role_id);
约束
常见约束
- 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,则不允许插入,否则允许
);
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 不相同,故不允许插入
前面两条顺利添加的 COMPANY7 表中,但是第三条则会报错:
ERROR: duplicate key value violates unique constraint "company7_pkey"
DETAIL: Key (id)=(3) already exists.
删除约束
删除约束必须知道约束名称,已经知道名称来删除约束很简单,如果不知道名称,则需要找到系统生成的名称,使用 \d 表名 可以找到这些信息。
通用语法如下:
ALTER TABLE table_name DROP CONSTRAINT some_name;
编辑表结构
在 PostgreSQL 中,ALTER TABLE 命令用于添加,修改,删除一张已经存在表的列。
另外你也可以用 ALTER TABLE 命令添加和删除约束。
新增表字段
alter table companyadd mark varchar;comment on column company.mark is '备注';
删除表字段
alter table company drop column mark;
修改表字段
alter table company rename column mark to remark;comment on column company.remark is '备注1';
语法
用 ALTER TABLE 在一张已存在的表上添加列的语法如下:
ALTER TABLE table_name ADD column_name datatype;
在一张已存在的表上 DROP COLUMN(删除列),语法如下:
ALTER TABLE table_name DROP COLUMN column_name;
修改表中某列的 DATA TYPE(数据类型),语法如下:
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
给表中某列添加 NOT NULL 约束,语法如下:
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
给表中某列 ADD UNIQUE CONSTRAINT( 添加 UNIQUE 约束),语法如下:
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
给表中 ADD CHECK CONSTRAINT(添加 CHECK 约束),语法如下:
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
给表 ADD PRIMARY KEY(添加主键),语法如下:
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
DROP CONSTRAINT (删除约束),语法如下:
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;
如果是 MYSQL ,代码是这样:
ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;
DROP PRIMARY KEY (删除主键),语法如下:
ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;
如果是 MYSQL ,代码是这样:
ALTER TABLE table_name
DROP PRIMARY KEY;
查看数据库中的表
\d
查看表的详细信息
\d tablename
删除表
DROP TABLE table_name;
数据清理
PostgreSQL 中 TRUNCATE TABLE 用于删除表的数据,但不删除表结构。
也可以用 DROP TABLE 删除表,但是这个命令会连表的结构一起删除,如果想插入数据,需要重新建立这张表。
TRUNCATE TABLE 与 DELETE 具有相同的效果,但是由于它实际上并不扫描表,所以速度更快。 此外,TRUNCATE TABLE 可以立即释放表空间,而不需要后续 VACUUM 操作,这在大型表上非常有用。
PostgreSQL VACUUM 操作用于释放、再利用更新/删除行所占据的磁盘空间。
TRUNCATE TABLE table_name;
清空表并保留表结构
一般情况下,我们使用delete删除表中数据,但是delete是一条数据一条数据来删除表中的数据,直至表清空(保留表结构),但是当数据量很大时,它耗时较久。
其实,删除表数据但保留表结构使用truncate更快速安全,使用方法为:
#当表没有其他关系时
TRUNCATE TABLE tablename;
#当表中有外键时,要用级联方式删所有关联的数据
TRUNCATE TABLE tablename CASCADE;
清空数据库还原数据库为新建时的状态
在postgresql中,创建数据库时会自动创建public模式,一般我们把表都保存在该模式中,因此直接删除该模式再重新创建该模式。
若数据在其他模式中,则把public换为数据表所在模式即可。
#删除public模式以及模式里面所有的对象
DROP SCHEMA public CASCADE;
#创建public模式
CREATE SCHEMA public;
模式命令
创建模式
create schema myschema;
在指定模式创建表
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)
);
删除模式
删除一个为空的模式,其中的所有对象已经被删除
DROP SCHEMA myschema;
删除一个模式以及其中包含的所有对象:
DROP SCHEMA myschema CASCADE;
权限(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 }
- 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;