文献参考:https://www.postgresql.org/docs/12/tutorial.html
这个文档，基本对PG的使用有一个感性认识。
数据库
创建数据库
[postgres@db ~]$ createdb zsddb
进入数据库
$ psql zsddb
查看版本
zsddb=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)
查看当前时间日期
zsddb=# SELECT current_date;
current_date
--------------
2019-12-02
(1 row)
简单的select
zsddb=# SELECT 2 + 2;
?column?
----------
4
(1 row)
获得帮助命令
zsddb=# \h
退出psql客户端
两种方式，如下:
zsddb=# quit
zsddb=# \q
创建表
weather和cities表的创建
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);
删除表
DROP TABLE tablename;
插入数据
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
The point type requires a coordinate pair as input, as shown here:
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
数据库导出成csv文本文件
zsddb=# copy weather to '/home/postgres/weather.csv' delimiter ',' csv header;
COPY 3
zsddb=# exit
[postgres@db ~]$ cat /home/postgres/weather.csv
city,temp_lo,temp_hi,prcp,date
San Francisco,46,50,0.25,1994-11-27
San Francisco,43,57,0,1994-11-29
Hayward,37,54,,1994-11-29
查询表的语句
查询所有的数据
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
查看指定列
zsddb=# SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
查看平均温度
zsddb=# SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
city | temp_avg | date
---------------+----------+------------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)
查看城市是'San Francisco',降水率大于0的数据。
zsddb=# SELECT * FROM weather
zsddb-# WHERE city = 'San Francisco' AND prcp > 0.0;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)
对城市排序
zsddb=# SELECT * FROM weather
zsddb-# ORDER BY city;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
(3 rows)
城市排序后，对温度再排序
zsddb=# SELECT * FROM weather
zsddb-# ORDER BY city, temp_lo;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27
城市去重DISTINCT
zsddb=# SELECT DISTINCT city
zsddb-# FROM weather;
city
---------------
Hayward
San Francisco
(2 rows)
去重+排序
SELECT DISTINCT city
FROM weather
ORDER BY city;
表连接查询
查看zsddb数据库下的表名称
zsddb=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | cities | table | postgres
public | weather | table | postgres
(2 rows)
查看表结构
zsddb=# \d weather
Table "public.weather"
Column | Type | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
city | character varying(80) | | |
temp_lo | integer | | |
temp_hi | integer | | |
prcp | real | | |
date | date | | |
zsddb=# \d cities
Table "public.cities"
Column | Type | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
name | character varying(80) | | |
location | point | | |
weather.city和cities.name做表连接查询。
zsddb=# SELECT *
zsddb-# FROM weather, cities
zsddb-# WHERE city = name;
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)
一般来说，你可能会输出你想要的列而不是"*"
zsddb=# SELECT city, temp_lo, temp_hi, prcp, date, location
zsddb-# FROM weather, cities
zsddb-# WHERE city = name;
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53)
虽然pg的parser会自动分析city和name是属于哪个表的列。但是如果有同名的列话，还需手动指定，如下:
zsddb=# SELECT weather.city, weather.temp_lo, weather.temp_hi,
zsddb-# weather.prcp, weather.date, cities.location
zsddb-# FROM weather, cities
zsddb-# WHERE cities.name = weather.city;
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53)
(2 rows)
上述的写法被广泛认为是good style ，还有一种替代写法如下:
zsddb=# SELECT *
zsddb-# FROM weather INNER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)
其实为了说明，上面的连接其实是inner join的表连接。
现在我们要把 Hayward那条记录找回来，我们需要对weather 表中每条记录与cities的表的记录相匹配，如果
匹配不到，就用"empty value"来显示，这个就需要用到outer join。如下:
zsddb=# SELECT *
zsddb-# FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
Hayward | 37 | 54 | | 1994-11-29 | |
(3 rows)
当然还有right outer joins and full outer joins，这里不演示了。
我们还可以对表自身做一个表连接，叫做self join,如下:
zsddb=# SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
zsddb-# W2.city, W2.temp_lo AS low, W2.temp_hi AS high
zsddb-# FROM weather W1, weather W2
zsddb-# WHERE W1.temp_lo < W2.temp_lo
zsddb-# AND W1.temp_hi > W2.temp_hi;
city | low | high | city | low | high
---------------+-----+------+---------------+-----+------
San Francisco | 43 | 57 | San Francisco | 46 | 50
Hayward | 37 | 54 | San Francisco | 46 | 50
(2 rows)
当然，以后通过对表一个别名，减少select打query的数量，如下:
zsddb=# SELECT *
zsddb-# FROM weather w, cities c
zsddb-# WHERE w.city = c.name;
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
Aggregate Functions 聚合函数
天气最大值
zsddb=# SELECT max(temp_lo) FROM weather;
max
-----
46
(1 row)
如果想知道temp_lo最大值对应的城市
zsddb=# SELECT city FROM weather
zsddb-# WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city
---------------
San Francisco
(1 row)
使用聚合函数，需要group by来进行分组。
zsddb=# SELECT city, max(temp_lo)
zsddb-# FROM weather
zsddb-# GROUP BY city;
city | max
---------------+-----
Hayward | 37
San Francisco | 46
(2 rows)
聚合函数，需要group by，再加where条件，挑选出小于40的。
zsddb=# SELECT city, max(temp_lo)
zsddb-# FROM weather
zsddb-# GROUP BY city
zsddb-# HAVING max(temp_lo) < 40;
city | max
---------+-----
Hayward | 37
(1 row)
update语句的应用
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
zsddb=# UPDATE weather
zsddb-# SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
zsddb-# WHERE date > '1994-11-28';
UPDATE 2
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29
delete语句的应用
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29
(3 rows)
zsddb=# DELETE FROM weather WHERE city = 'Hayward';
DELETE 1
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
(2 rows)
视图
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
zsddb=# SELECT * FROM myview;
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 41 | 55 | 0 | 1994-11-29 | (-194,53)
(2 rows)
事务
PostgreSQL通过BEGIN和COMMIT这一对代表事务的开启。
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;
