PostgreSQL 基本操作

前言

最近在使用Postgresql,基本操作与MysqL略有不同,故记录下以备查阅.

@Author duangr

@Websitehttp://my.oschina.net/duangr/blog/182212

1.相关环境

Host Name IP OS Arch
duangr-1
192.168.56.10 CentOS 6.4 x86_64

Postgresql 版本: 9.3.1

2.基本操作

2.1 登陆

超级用户登陆

su-postgres
psqltemplate1--登陆template1数据库

普通用户登陆

psql -U duangr -d template1   -- 使用duangr登陆template1数据库

2.2 基本查询

\l--查看有哪些数据库
\q--退出
\cduangr--从之前的库切换到duangr库
\dtable_name--查看表结构
\dt--查看有哪些表
\di--查看索引
\du--查看角色

通过sql查看

--查询系统有哪些数据库
selectoid,datnamefrompg_database;
--查询系统有哪些用户
selectusenamefrompg_user;
--查询系统有哪些角色
selectrolnamefrompg_roles;

2.3 用户与角色

用户和角色类似,用户就是可以登陆的角色.

--创建用户(duangr)
CREATEUSERduangrWITHPASSWORD'${passwd}';
--在db中切换用户
\!psql-Uduangr-dtemplate1
--删除用户(duangr)
DROPUSERduangr;

--创建角色(dgr_role)
CREATEroledgr_role;
--删除角色
DROPROLE[IFEXISTS]name[,...]
--将角色赋给其他角色或用户
GRANTrole_name[,...]TOrole_or_user_name[,...]
--回收角色
REVOKErole[,...]FROMrole_or_user_name[,...]

授予权限

GRANT{{SELECT|INSERT|UPDATE|DELETE|REFERENCES|TRIGGER}[,...]|ALL[PRIVILEGES]}
ON[TABLE]tablename[,...]
TO{rolename|PUBLIC}[,...][WITHGRANTOPTION]


GRANT{{USAGE|SELECT|UPDATE}[,...]|ALL[PRIVILEGES]}

ONSEQUENCEsequencename[,...]
TO{role_or_user_name|PUBLIC}[,...][WITHGRANTOPTION]

GRANT{{CREATE|CONNECT|TEMPORARY|TEMP}[,...]|ALL[PRIVILEGES]}
ONDATABASEdbname[,...][WITHGRANTOPTION]

GRANT{EXECUTE|ALL[PRIVILEGES]}
ONFUNCTIONfuncname([[argmode][argname]argtype[,...]])[,...][WITHGRANTOPTION]

GRANT{USAGE|ALL[PRIVILEGES]}
ONLANGUAGElangname[,...][WITHGRANTOPTION]

GRANT{{CREATE|USAGE}[,...]|ALL[PRIVILEGES]}
ONSCHEMAschemaname[,...][WITHGRANTOPTION]

GRANT{CREATE|ALL[PRIVILEGES]}
ONTABLESPACEtablespacename[,...][WITHGRANTOPTION]

回收权限

REVOKE[GRANTOPTIONFOR]
{{SELECT|INSERT|UPDATE|DELETE|REFERENCES|TRIGGER}[,...]
FROM{role_or_user_name|PUBLIC}[,...]
[CASCADE|RESTRICT]

REVOKE[GRANTOPTIONFOR]
{{USAGE|SELECT|UPDATE}[,...]|ALL[PRIVILEGES]}
ONSEQUENCEsequencename[,...]
FROM{[role_or_user_name|PUBLIC}[,...]
[CASCADE|RESTRICT]

REVOKE[GRANTOPTIONFOR]
{{CREATE|CONNECT|TEMPORARY|TEMP}[,...]
[CASCADE|RESTRICT]

REVOKE[GRANTOPTIONFOR]
{EXECUTE|ALL[PRIVILEGES]}
ONFUNCTIONfuncname([[argmode][argname]argtype[,...]
[CASCADE|RESTRICT]

REVOKE[GRANTOPTIONFOR]
{USAGE|ALL[PRIVILEGES]}
ONLANGUAGElangname[,...]
[CASCADE|RESTRICT]

REVOKE[GRANTOPTIONFOR]
{{CREATE|USAGE}[,...]
[CASCADE|RESTRICT]

REVOKE[GRANTOPTIONFOR]
{CREATE|ALL[PRIVILEGES]}
ONTABLESPACEtablespacename[,...]
[CASCADE|RESTRICT]

2.4 数据库操作

--创建db(duangr)
createdatabaseduangr

--删除db(duangr)
dropdatabaseduangr

--建表
createtable(
[field1][type1]<references关联表名(关联的字段名)>,[field2][type2],......,primarykey([field1],[field2],...)
);
--改表名
altertable[table_A]renameto[table_B];
--删表
droptable[table_name]

--修改表
--加字段
altertable[table_name]addcolumn[field_name][type];
--删字段
altertable[table_name]dropcolumn[field_name];
--重命名字段
altertable[table_name]renamecolumn[field_A]to[field_B];
--设置字段缺省值
altertable[table_name]altercolumn[field_name]setdefault[value];
--删除缺省值
altertable[table_name]altercolumn[field_name]dropdefault;

--记录增删改
insertinto[table_name](field1,field2,...)values(...)
update[table_name]setfield=?where...
deletefrom[table_name]where...

2.5 数据清理

--对数据库进行清理、收回磁盘空间并更新统计信息
vaccumdb-d
-a对所有的数据库操作
-z保证不断地删除失效的行,节约磁盘空间,将统计信息更新为最近的状态

2.6 备份数据库

--备份duangr库
pg_dumpduangr>/export/home/duangr/pgsql/backup/duangr_20131206.bak

2.7 导入数据

--将文件中的数据导入到表中(字段分隔符可以自定义,与数据文件中保持一致.数据文件字段与表中列相对应)
copytable_namefrom'/tmp/etl/table_name.dat'withdelimiter'^A'

P.S. 此处使用ASCII字符 0x01 作为文件中字段分隔符,目的是避免与数据字段内容冲突.

^A 需要通过 "Ctrl+V" + "Ctrl+A" 的方式输入.

3. 相关链接

相关文章

来源:http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键词必须以一个...
来源:http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的整数以及4或8...
来源:http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。当一个表被读...
来源:http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行的操作过程中...
来源:http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是PostgreSQL中的...
来源:http://www.postgres.cn/docs/11/ 9.7.&#160;模式匹配 PostgreSQL提供了三种独立的实现模式匹...