PostgreSQL 数据库常用命令

PostgreSQL 数据库常用命令

 

 

1.登录

 

psql -U username

psql -U username dbname

psql -U username -h 172.28.18.51 dbname

 

例子：psql -U postgres

输入用户 postgres 的口令：postgres

 

 

2.创建数据库

 

create database dbname;

 

例子：create database testdb;

  

 

3.列举数据库

 

\l 或者 \list

 

例子：

  

 

4.选择数据库

 

\c dbname

\c dbname username serverIP port

 

例子：\c testdb

  

 

5.创建数据表

 

create table tablename (field1 fieldType, field2 fieldType);

 

例子：

create table person (

id         integer,

name       varchar(20),

departmentId integer,

cityId       integer,

constraint zhongwc_pid_pk primary key(id),

constraint fk_xi9cjtmjwt0fhfd5ks69gbaw foreign key (departmentId) references department(id),

constraint fk_xi9cjtmjwt0fhfd5ks69gbax foreign key (cityId) references city(id)

);

 

create table department (

id   integer,

name varchar(20),

constraint department_did_pk primary key(id)

);

 

create table city (

id   integer,

name varchar(20),

constraint city_cid_pk primary key(id)

);

 

 
 

6.显示表结构

 

\d tablename

 

 

7.插入表数据

insert into tablename values (field1 fieldType, field2 fieldType);

 

例子：

insert into department values (1, '销售部');

insert into department values (2, '技术部');

insert into department values (3, '运维部');

 

insert into city values (1, '北京');

insert into city values (2, '上海');

insert into city values (3, '广州');

 

insert into person values (1, 'zhangsan', 1, 1);

insert into person values (2, 'lisi', 2, 1);

 

 

8.数据导入导出

 

导出库：pg_dump -U username dbname > f:\a.sql

导入库：psql -U username dbname < f:\a.sql

 

进入数据库：

导出表：COPY tablename TO   'f:\d.sql';

导入表：COPY tablename FROM 'f:\d.sql';

 

例子：

pg_dump -U postgres testdb > f:\a.sql

psql -U postgres testdb < f:\a.sql

 

COPY city TO   'f:\city.sql';

COPY city FROM 'f:\city.sql';

 

 

9.显示字符集

\encoding

 

 

10.退出psgl

\q

 

 

11.查看角色

\du

 

 

12.用户

 

1.创建普通用户

create role username login password '123456' createdb valid until 'infinity';

 

2.创建超级用户

create role username login password '123456' superuser valid until 'infinity';

 

3.创建组角色

create role username1 inherit; -- 继承除“超级用户权限”外的所有权限

 

grant username1 to username;