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等继承层次更低的