postgresql 学习 · RainZhao

创建db
createdb mydb;
访问数据库
psql mydb;
查看语法
\h
退出psql
\q

创建数据库

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低温度
    temp_hi         int,           -- 最高温度
    prcp            real,          -- 湿度
    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');

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

-- 也可以明确列出哪些列需要插入:

INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);

查询数据

SELECT * FROM weather;

-- 可以在选择列表中写任意表达式，而不仅仅是列的列表
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
-- AS子句是如何给输出列重新命名的（AS子句是可选的）

-- 一个查询可以使用WHERE子句“修饰”，它指定需要哪些行。WHERE子句包含一个布尔（真值）表达式，只有那些使布尔表达式为真的行才会被返回。在条件中可以使用常用的布尔操作符（AND、OR和NOT）。

SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;

-- 可以要求返回的查询结果是排好序的
SELECT * FROM weather ORDER BY city;

-- 在查询的结果中消除重复的行
SELECT DISTINCT city FROM weather;

表之间的连接

SELECT * FROM weather, cities 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)

-- 指定明确的列

SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;

-- 指定明确的表，防止重名的列导致查询失败
SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    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)

这样查询出来只会有两个表中name相同的列会被查询出来，
使用左外连接，在连接操作符左部的表中的行在输出中至少要出现一次， 而在右部的表的行只有在能找到匹配的左部表行时才被输出。
如果输出的左部表的行没有对应匹配的右部表的行，那么右部表行的列将填充空值（null）。

--- 左外连接
SELECT * 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)
 Hayward       |      37 |      54 |      | 1994-11-29 |               | 
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)

--- 右外连接
     city      | temp_lo | temp_hi | prcp |    date    |     name      | location  
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)

--- 自连接
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    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

连接操作		含义
内连接 INNER JOIN		只连接匹配的行
左外连接 LEFT OUTER JOIN		包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
右外连接 RIGHT OUTER JOIN		包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
全外连接 FULL OUTER JOIN		包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

聚集操作

聚集max不能被用于WHERE子句中（存在这个限制是因为WHERE子句决定哪些行可以被聚集计算包括；因此显然它必需在聚集函数之前被计算）

1	SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

更新操作

1
2
3

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

删除

DELETE FROM weather WHERE city = 'Hayward';

--- 如果没有where限制就会删除所有行的数据
DELETE FROM tablename;