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; |