postgreSQL学习笔记

安装postgreSQL

碰到的问题，安装时由于防火墙，导致连接不到主机？Failed to load sql modules into the database cluster during PostgreSQL Installation。关闭防火墙才安装成功

基本数据类型

名字描述uuid通用唯一标识符bigint有符号8字节整数integer有符号4字节整数smallint有符号2字节整数bigserial自增8字节整数serial自增4字节数real单精度浮点数double precision双精度浮点数字bit [(n)]定长位串bit varying [ (n) ]变长位串boolean逻辑布尔量 （真/假）point平面中的点line平面中的无限长直线lseg平面中的线段box平面中的长方形circle平面中的圆polygon平面多边形bytea二进制数据（“字节数组”）varchar(n)变长字符串text变长字符串char(n)定长字符串cidr 或 inetIPv4 或者 IPv6 网络地址macaddrMAC 地址date日期（年，月，日）time( p ) [ without time zone ]时间，不包括时区timestamp( p ) with time zone日期和时间interval( p )时间间隔xmlXML数据

一些基本操作和命令

要创建一个新的数据库，在我们这个例子里叫mydb

$ createdb mydb $ dropdb mydb #删除 $ psql mydb #启动，激活？ $ psql -s mydb #-s选项把你置于单步模式 mydb=> SELECT version(); mydb=> SELECT current_date; mydb=> \h #语法帮助，可以+具体命令 mydb=> \q #退出 mydb=> \i basics.sql # \i命令从指定的文件中读取命令

创建一个新表

CREATE TABLE weather ( city varchar(80), #存储最长80个字符的任意字符串 temp_lo int, -- 最低温度 temp_hi int, -- 最高温度 prcp real, -- 湿度，real是存储单精度浮点数的类型 date date ); # weather表 CREATE TABLE cities ( name varchar(80), location point ); # cities表

PostgreSQL支持标准的SQL类型int、smallint、real、double、precision、char(N)、varchar(N)、date、time、timestamp和interval，还支持其他的通用功能的类型和丰富的几何类型，point、line等。

在表中增加行

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); #注，此处插入的数值要符合默认顺序，否则在VALUES前自己要明确列出列顺序(date, city, ......) INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)'); COPY weather FROM '/home/user/weather.txt'; #从文件中装载数据 # 这里的文件名必须在运行后端进程的机器上可用， 而不是在客户端上

查询一个表

SELECT * FROM weather; #等效于SELECT city, temp_lo, temp_hi, prcp, date FROM weather; 事实上限定所有列名是更好的方式。 #列表可以写任意表达式，如(temp_hi+temp_lo)/2 AS temp_avg #这里的AS子句是如何给输出列重新命名的（AS子句是可选的） SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0; #增加条件 SELECT * FROM weather ORDER BY city; #要求返回的结果是排好序的，city后面还能再加参数 SELECT DISTINCT city FROM weather; #消除城市名重复的行 SELECT * FROM tt ORDER BY n1 DESC,n2 ASC; #先按n1降序排列，再按n2升序排列

表之间连接

  有时候查询需要一次访问多个表

SELECT * FROM weather, cities WHERE city = name; # 选择两个表中city=name的行，条件语句还可有AND、OR # 当两个表的列有重名的时候，需要加前缀限定，如weather.city， cities.name (一种缩写)： SELECT * FROM weather w, cities c WHERE w.city = c.name; #等效于： SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name); #左外连接： SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

聚集函数

SELECT max(temp_lo) FROM weather; #选极大值 SELECT city FROM weather #这个极大值是哪个城市 WHERE temp_lo = (SELECT max(temp_lo) FROM weather); SELECT city, max(temp_lo) FROM weather GROUP BY city; HAVING max(temp_lo) < 40; #用having过滤 #group by用于结合聚合函数，根据列对结果集进行分组 #这样给每个城市一个输出。每个聚集结果都是在匹配该城市的表行上面计算的。

注意，WHERE在分组和聚集之前选取分组行，HAVING在分组和聚集之后选取分组行。所以where子句不能包含聚集函数，having可以。

更新

UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28'; DELETE FROM tablename; # 删除所有

视图

CREATE VIEW myview AS # 给该查询一个名字myview SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name; SELECT * FROM myview; # myview就相当于一个视图、接口

主键外键

CREATE TABLE cities ( city varchar(80) primary key, # 主键只有一个，用于标识 location point ); CREATE TABLE weather ( city varchar(80) references cities(city), # 外键是其他表的主键，可有多个 temp_lo int, ...... ); --添加主键 alter table cities add PRIMARY KEY(name); --添加外键 alter table weather add CONSTRAINT FK_B FOREIGN key(city) REFERENCES cities(name) # on update CASCADE on DELETE CASCADE; # on update cascade: 被引用行更新时，引用行自动更新； # on update restrict: 被引用的行禁止更新； # on delete cascade: 被引用行删除时，引用行也一起删除； # on dellete restrict: 被引用的行禁止删除； --删除外键 alter table orders drop constraint orders_goods_id_fkey; --添加唯一约束 alter table goods add constraint unique_goods_sid unique(sid); --删除默认值 alter table goods alter column sid drop default; --修改字段的数据类型 alter table goods alter column sid type character varying; --重命名字段 alter table goods rename column sid to ssid;

表之间的连接

还有半连接，常见于子查询，如：

SELECT * FROM D WHERE EXISTS ( SELECT * FROM E WHERE D.id= E.id AND E.s > 2500) ORDER BY x SELECT * FROM E # 反半连接 WHERE id NOT IN (SELECT id FROM D WHERE lid=1700) ORDER BY id

关于子查询：PostgreSQL 的子查询 相关的知识 ，exists,any,all

事务

  事务是原子的。比如A转100元给B，需要整个同时发生，并被数据库永久记录。事务所做的更新在它完成之前对于其他事务是不可见的   开启一个事务需要BEGIN和COMMIT命令包围起来。事务执行中若不想提交，用ROLLBACK取消更新。SAVEPOINT可以定义回滚的保存点。

-- 简化的例子： BEGIN; UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'; SAVEPOINT my_savepoint; UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob'; -- 搞错了，应该存到wally的账户 ROLLBACK TO my_savepoint; UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Wally'; COMMIT;

窗口函数

 一个窗口函数在一系列与当前行有某种关联的表行上执行一种计算，但是并不会使多行被聚集成一个单独的输出行，而是保留各行独立的标识。(partition & group的区别)

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

窗口函数所考虑的行属于那些通过查询的FROM子句产生并通过WHERE、GROUP BY、HAVING过滤的“虚拟表”。被过滤的行不会被窗口函数所见。

对于每一行，在它的分区中的行集被称为它的窗口帧。 一些窗口函数只作用在窗口帧中的行上，而非整个分区。默认当使用ORDER BY，则帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子句上相等的行。如果ORDER BY被忽略，则默认帧包含整个分区所有的行。

窗口函数只允许出现在查询的SELECT列表和ORDER BY子句中。不许出现在其他地方，例如GROUP BY、HAVING和WHERE子句中。这是因为窗口函数的执行逻辑是在处理完这些子句之后。另外，窗口函数在非窗口聚集函数之后执行。这意味着可以在窗口函数的参数中包括一个聚集函数，但反过来不行。

如果需要在窗口计算执行后进行过滤或者分组，我们可以使用子查询。例如

SELECT depname, empno, salary, enroll_date FROM (SELECT depname, empno, salary, enroll_date, rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos FROM empsalary ) AS ss WHERE pos < 3;

多个函数要求同一个窗口行为时，每个窗口行为可以被放在一个命名的WINDOW子句中，然后在OVER中引用它

SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

继承

CREATE TABLE cities ( name text, population real, altitude int ); CREATE TABLE capitals ( state char(2) ) INHERITS (cities); # capitals的行从它的父亲cities继承了所有列, 还有附加列state SELECT name, altitude FROM cities # 若为from only cities WHERE altitude > 500; # 则不会包括capitals等继承层次更低的