作者：罗寅兵日期：2012-11-07一、管理数据库
1、查询现有数据库
SELECT*FROMpg_database;
或psql接口的\\l元命或-l命令行选项令来列出现有的数据库。
在pg_database里面的两字段标志：datistemplate:设置为True该数据库可以由有CREATEDB权限的用户克隆；
设置为False只有超级用户和该数据库的所有者可以克隆它。
datallowconn:设置为False将不允许与该数据库发生任何新的连接。2、创建数据库
创建数据库实际上是通过拷贝一个现有的数据库进行工作的。缺省拷贝template1的标准系统数据库模板。（模版数据库必须是闲置的，即没有任何连接。）
CREATEDATABASE$dbname;--当前角色成为新数据库的所有者。CREATEDATABASE$dbnameTEMPLATE$template;--指定数据库模版。CREATEDATABASE$dbnameOWNER$rolename;--指定数据库的角色名。createdb-O$rolename$dbname-T$template#命令行工具3、删除数据库
DROPDATABASE$dbname;dropdb$dbname#命令行工具4、备份与恢复
pg_dump-h$host-U$user$dbname>$file#备份$dbname数据库至$file
Pg_dump-o$dbname>$filepsql$dbname<$file
pg_dump-hhost1dbname|psql-hhost2dbnamepg_dumpall|gzip>$filepsql-f$filepostgres
#把OID也导出来
#恢复（需要先创建数据库）#转存至另一台主机
#备份集群中的每一个数据#恢复集群中的每一个数据
二、管理表空间
表空间允许数据库管理员在文件系统里定义那些数据库对象的文件存放位置。创建表空间必须由超级用于进行。
1、查询现有表空间
SELECT*FROMpg_tablespace;2、创建表空间
CREATETABLESPACE$spaceLOCATION‘/var/fastData’;--创建一个表空间3、使用表空间
表、索引和数据库都可以放在特定的表空间里，只需要给CREATE传递相关命令：CREATEDATABASE$dbnameTABLESPACE$space;CREATETABLE$tabname(iint)TABLESPACE$space;
你还可以设置默认的表空间：SETdefault_tablespace=space1;
1
4、删除表空间
DROPTABLESPACE$space;
--必须清空所有对象后才能删除
三、数据库角色和权限
1、查询现有角色
SELECT*FROMpg_roles;2、创建角色和角色属性
CREATEROLE$rolenameLOGIN;CREATEUSER$rolename;createuser$rolename
--默认没有LOGIN；--默认有LOGIN；#命令行工具
角色的属性有：LOGIN、SUPERUSER、CREATEDB、CREATEROLE、PASSWORDCREATEROLE$rolenameLOGINCREATEDBPASSWORD‘pwdString’;修改角色属性可以使用ALTERROLE命令。3、删除角色
DROPROLE$rolename;dropuser$rolename#命令行工具3、角色权限（基于数据表）
权限列表：SELECT、INSERT、UPDATE、DELETE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE。
GRANTSELECT,INSERTON$tablenameTO$rolename;--赋予角色权限REVOKEALLON$tablenameFROM$rolename;--撤销角色权限4、角色权限的链接
把用户链接起来简化权限管理是个常用的便利方法：用这样的方法，权限可以赋予整个组，也可以对整个组撤消。
CREATEROLEjoeLOGININHERIT;--创建一个角色。
CREATEROLEadminNOINHERIT;--创建一个基类角色，通常不拥有登录属性。GRANTadminTOjoe;--拥有赋予joe的权限加上赋予admin的权限。
四、SQL-数据定义
1、查询已经建立的表
SELECT*FROMpg_tables;--除去pg_和sql_开头的表，就是用户建立的表SELECT*FROMpg_tablesWHERE
tablenameNOTLIKE'pg_%'ANDtablenameNOTLIKE'sql_%';
2、创建表
CREATETABLEproducts(
idSERIALPRIMARYKEY,namevarchar(64),pricenumericCONSTRAINTpositiveCHECK(price>0));
3、删除表
DROPTABLE$tablename;4、默认值定义
SERIAL在PostgreSQL中就是一种数据烈性（和MySql的自增属性一样）
2
DEFAULT$value定义缺省值CREATETABLEproducts(
idSERIALPRIMARYKEY,pricenumericDEFAULT9.99--price字段默认为9.99);
5、字段值约束声明和命名约束
CHECK($rol>$value)检查约束CREATETABLEproducts(
pricenumericCHECK(price>0));--price字段的值必须大于0CONSTRAINT$conname声明一个命名约束CREATETABLEproducts(
pricenumericCONSTRAINTpositive_priceCHECK(price>0));--声明了一个命名为positive_price的约束NOTNULL非空约束CREATETABLEproducts(
pricenumericDEFAULT9.99NOTNULL);--price字段的值不能为空UNIQUE($rol......)唯一约束CREATETABLEproducts(
idintegerUNIQUE,);--id字段的值不能重复CREATETABLEadmin(
namevarchar(32),identvarchar(64),
CONSTRAINTonly_nameEmailUNIQUE(name,email));--name和email的联合必须唯一PRIMARYKEY($rol......)主键(不能为空，不能重复)CREATETABLEproducts(
idintegerPRIMARYKEY,);--id字段定义为主键CREATETABLEadmin(
namevarchar(32),identvarchar(64),
CONSTRAINTonly_nameEmailPRIMARYKEY(name,email));--联合主键声明PEFERENCES$tabname($rol)外键约束（字段数据类型必须一致）CREATETABLEorders(
IdintegerPRIMARYKEY,product_idPEFERENCESproducts(id))--字段值必须匹配products表中的id字段值外键引用的限制和级联删除、限制和级联更新ONDELETE限制和级联删除ONUPDATE限制和级联更新
3
CREATETABLEitems(
Pro_idintegerREFERENCESproductsONDELETERESTRICT,Ord_idintegerREFERENCESordersONUPDATECASCADE,)--缺少引用的字段就会引用主键RESTRICT禁止删除被引用的行；NOACTION【缺省】在检查约束的时候还存在任何引用行，则抛出异常；CASCADE删除一个被引用的行所有引用它的行也会被自动删除掉；SETNULL删除一个被引用的行所有引用它的行设置为空；SETDEFAULT删除一个被引用的行所有引用它的行设置为默认。6、修改表
ALTERTABLE$tablenameADDCOLUMNnametext;--增加字段(nametext)ALTERTABLE$tablenameDROPCOLUMNnametext;--删除字段(nametext)ALTERTABLE$tablenameADDCHECK(name!=‘’);--增加约束
ALTERTABLE$tablenameALTERCOLUMEnameSETNOTNULL;--增加约束ALTERTABLE$tablenameALTERCOLUMNnameDROPDEFAULT;--删除约束ALTERTABLE$tablenameALTERCOLUMNpriceTYPEnumeric(8,2);--修改类型ALTERTABLE$tablenameRENAMECOLUMNnameTouser;--重命名字段名ALTERTABLE$tablenameRENAMETOitems;--重命名表名
五、SQL-数据操作
1、插入数据
INSERTINTOproductsVALUES(1,‘Cheese’,9.99);
INSERTINTOproducts(name,price,id)VALUES(‘Cheese’,9.99,1);INSERTINTOproducts(name,price,)VALUES(‘Cheese’,9.99);省略字段自动填充默认值
INSERTINTOproducts(id,name,price)VALUES
(1,'Cheese',9.99),(2,'Bread',1.99),(3,'Milk',2.99);--插入多行
2、更新数据
UPDATEproductsSETprice=10WHEREprice=5;！！！忽略WHERE语句将更新所有的行。3、删除数据
DELETEFROMproductsWHEREprice=5;！！！忽略WHERE语句将删除所有的行。
六、SQL-数据查询
1、普通查询
SELECT*FROM$tablename;--查询所有列和行，不推荐使用。SELECTname,(temp_hi+temp_lo)/2AStemp_avgFROM$tablename;
--在查询器中进行运算和设置字段别名
SELECT(3+4)*random()AStest;--查询器还是一个表达式的返回值2、去除重复行
SELECTDISTINCTname,tempFROM$tablename;--不显示重复的行
4
3、WHERE条件语句
SELECTnameFROM$tablenameWHEREtemp_hi<40ANDtemp_lo>10;
--只输出符合条件的行
4、ORDERBY行排序
SELECT*FROM$tablenameORDERBYid,nameASC;
--升序排列id,name，可以省略ASC
SELECT*FROM$tablenameORDERBYid,nameDESC;
--降序排列id,name
5、LIMIT和OFFSET
SELECT*FROM$tablenameLIMIT5;--只输出5行SELECT*FROM$tablenameOFFSET5;--忽略5行SELECT*FROM$tablenameLIMIT10OFFSET20;--组合使用6、GROUPBY行分组和HAVING选取分组
SELECTtemp_liFROM$tablenameGROUPBYtemp_li;--分组SELECTtemp_li,sum(temp_lo)FROM$tablenameGROUPBYtemp_li;
--带聚集函数的分组
SELECTx,sum(y)FROMtestGROUPBYxHAVINGsun(y)>3;SELECTx,sum(y)FROMtestGROUPBYxHAVINGx>3;
--按条件选取分组
7、按条件连接表
假设我们有tab1和tab2两个表，如下：
numnamenumvalue1a1xxx2b3yyy3
c
5
zzz
交叉连接$tab1CROSSJOIN$tab2等同于$tab1,$tab2SELECT*FROMtab1,tab2;num|name|num|value -----+------+-----+-------1|a|1|xxx1|a|3|yyy1|a|5|zzz2|b|1|xxx2|b|3|yyy2|b|5|zzz3|c|1|xxx3|c|3|yyy3|c|5|zzz(9rows)
内连接tab1INNERJOINtab2USING($col)
SELECT*FROMtab1INNERJOINtab2USING(num);num|name|value
5
-----+------+-------1|a|xxx3|c|yyy(2rows)
仅列2中，其他连接方式请参考用户手册。8、组合查询
组合查询就是对2个查询语句进行并集操作（并、交、差），这两个查询必须是“并集兼容”的，也就是说必须是同样数量的列，且对应的列的数据类型是兼容的。
query1UNION[ALL]query2将query2的结果附加到query1的结果上query1INTERSECT[ALL]query2返回query1和query2同时存在的行query1EXCEPT[ALL]query2返回query1和query2不同时存在的行
七、函数与表达式
1、逻辑操作符AND逻辑与2、比较操作符
OR逻辑或
NOT逻辑非
3、数学操作符
!只列出一部分
4、模式匹配（LIKE、POSIX正则）
LIKE使用%匹配0个或多个任意字符，使用_匹配单个字符。‘abc’LIKE‘abc’true‘abc’NOTLIKE‘a%’false‘abc’LIKE‘_b_’true
POSIX正则匹配：
6
'abc'~'abc''abc'~'^a''abc'~'(b|d)''abc'~'^(b|c)''truetruetruefalse
八、数据类型
1、数值类型
smallint,integer,bigint的别名是：int2,int4,int8numeric(X,Y)指数值的长度为X，小数位为YNumeric类型有一个特殊值：NaN
浮点数类型有几个特殊值：Infinity-InfinityNaN2、字符类型
（正无穷大、负无穷大、非数值）3、二进制数据类型
4、日期和时间
(p)设置秒域中的小数位数。
7
ISO-8601的日期和时间的写法(当然PostgreSQL支持广泛格式)：1999-01-0804:05:06(不带时区)1999-01-0804:05:06-8:00(带时区)5、布尔类型
boolean类型：只有两个值True和False。6、网络地址类型
7、位串类型
位串就是一串1和0的字符串。bit(n)n代表可以存储字符串的长度。8、数据类型Array
PostgreSQL允许将字段定义为定长或变长的一维或多维数组。数组类型可以是任何基本类型或用户自定义类型，但不支持复合类型和域。
变长数组：
integer[]text[][]
定长数组：
Integer[3][3]
数组类型的使用：
INSERTINTO$tableVALUES({{200,300,100},{200,300,100},{200,300,100}});SELECTnameFROM$tableWHEREid!=pay[2];
八、创建索引(B-tree)
索引可以让查询器更快的找到匹配的行，但大多数情况下使用单字段建立索引就足够了，而且还节约时间和空间，除非表的使用模式非常固定，否则超过3个字段的索引几乎没有任何用处。
如果一个表声明了唯一约束或者主键，那么PostgreSQL自动在组成主键或唯一约束的字段上创建唯一索引(可能是多字段索引)，以强迫这些约束。所以我们就不必要重复建立相关的索引了。
1、查询已经建立的索引
SELECT*FROMpg_indexesWHEREindexnameNOTLIKE'pg_%';2、建立索引
如果你的表大量使用下面的语句查询：SELECTnameFROMtestWHEREid=$x;
为了让数据库更快速的定位匹配行，通常需要把$table.id字段建立索引：CREATEINDEXtest_idONtest(id);
CREATEINDEX$indexnameON$table($col1,$col2)--多字段索引WHEREcol1=100ANDcol2=200可以利用多字段索引WHEREcol1=100ORcol2=200不可以利用多字段索引3、建立表达式索引
WHERE语句：SELECT*FROMtest1WHERElower(col1)='value';
8
建立相关索引：CREATEINDEXtest1_lower_col1ONtest1(lower(col1));4、删除索引
DROPINDEXtest_id;
九、事务
事务是所有数据库系统的一个基本概念。一次事务的要点就是把多个步骤捆绑成一个单一的、不成功则成仁的操作。其它并发的事务是看不到在这些步骤之间的中间状态的，并且如果发生了一些问题，导致该事务无法完成，那么所有这些步骤都完全不会影响数据库。
BEGIN;--开始事务SAVEPOINT$string;--保存点ROLLBACKTO$string;--回滚到保存点ROLLBACK--回滚事务COMMIT;--提交事务实例演示：BEGIN;
UPDATEaccountsSETbalance=balance-100.00
WHEREname='Alice';SAVEPOINTmy_savepoint;
UPDATEaccountsSETbalance=balance+100.00
WHEREname='Bob';--呀！加错钱了，应该用Wally的账号ROLLBACKTOmy_savepoint;
UPDATEaccountsSETbalance=balance+100.00
WHEREname='Wally';COMMIT;
十、查询规划和预备语句
PostgreSQL对每个查询产生一个查询规划，为匹配查询结构和数据属性选择正确的规划对性能绝对有关键性的影响。但对于大量相同模式的语句执行，产生查询规划也会被重复执行很多次。
PREPARE可以创建一个预备语句（SELECT、INSERT、UPDATE、DELETE、VALUES）。在执行PREPARE语句的时候，指定的查询被分析、重写、规划。当随后发出EXECUTE语句的时候，预备语句就只需要执行了。因此，分析、重写、规划阶段都只执行一次。
PREPAREfooplan(int,text,bool,numeric)AS
INSERTINTOfooVALUES($1,$2,$3,$4);--预备语句
EXECUTEfooplan(1,‘HunterValley’,‘t’,200.12);--根据所给参数执行语句
!!!作者寄语：写文档时只为备忘和加深记忆，想学习还请看PostgreSQL手册，使用本文档学习只会学到皮毛，且还会养成不好的习惯。9
因篇幅问题不能全部显示，请点此查看更多更全内容