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 或 inet | IPv4 或者 IPv6 网络地址 |
| macaddr | MAC 地址 |
| date | 日期（年，月，日） |
| time( p ) [ without time zone ] | 时间，不包括时区 |
| timestamp( p ) with time zone | 日期和时间 |
| interval( p ) | 时间间隔 |
| xml | XML数据 |
一些基本操作和命令
要创建一个新的数据库，在我们这个例子里叫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等继承层次更低的
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
