PostgreSQL_学习_1

最新推荐文章于 2021-08-12 21:47:58 发布

chongyi1654

最新推荐文章于 2021-08-12 21:47:58 发布

阅读量127

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/u/3238697/blog/3021871

版权

--1.3. 创建一个数据库
CREATE DATABASE db_shing_test WITH ENCODING='UTF8' CONNECTION LIMIT=-1;
--drop DATABASE db_shing_test;

--2.3. 创建新表 int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp,和 interval
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
);

--2.4. 向表中添加行请注意所有数据类型都使用了相当明了的输入格式。那些不是简单数字值的常量必需用单引号(')包围，就像在例子里一样。date类型实际上对可接收的格式相当灵活，不过在本教程里，我们应该坚持使用这里显示的格式
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
--你还可以使用COPY从文本文件中装载大量数据。这么干通常更快，因为COPY命令就是为这类应用优化的，只是比INSERT 少一些灵活性。比如：
--COPY t_shing_test FROM '/home/user/weather.txt';
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
--2.5. 查询一个表
select * from weather;
--可以要求查询的结果消除重复行的输出：在一些数据库系统里，包括老版本的PostgreSQL， DISTINCT自动对行进行排序，因此ORDER BY 是多余的。但是这一点并不是 SQL 标准的要求，并且目前的PostgreSQL 并不保证DISTINCT导致数据行被排序。
SELECT DISTINCT city FROM weather;

--2.6. 在表间连接
SELECT *
FROM weather, cities
WHERE city = name;
--到目前为止，这种类型的连接查询也可以用下面这样的形式写出来：
SELECT *
FROM weather INNER JOIN cities ON (weather.city = cities.name);

--现在我们将看看如何能把 Hayward 记录找回来。我们想让查询干的事是扫描 weather表，并且对每一行都找出匹配的cities 表里面的行。如果没有找到匹配的行，那么需要一些"空值"代替 cities表的字段。这种类型的查询叫外连接 (我们在此之前看到的连接都是内连接)
--这个查询是一个左外连接，因为连接操作符(LEFT OUTER JOIN) 左边的表中的行在输出中至少出现一次，而右边的表只输出那些与左边的表中的某些行匹配的行。如果输出的左表中的行没有右表中的行与其对应，那么右表中的字段将填充为 NULL
SELECT *
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

--我们也可以把一个表和它自己连接起来。这叫自连接。比如，假设我们想找出那些在其它天气记录的温度范围之外的天气记录。这样我们就需要拿weather表里每行的temp_lo和temp_hi 字段与weather表里其它行的temp_lo 和temp_hi字段进行比较。我们可以用下面的查询实现这个目标：
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;

--2.7. 聚合函数
--我们可以用下面的语句找出所有低温中的最高温度：
SELECT max(temp_lo) FROM weather;

--如果我们想知道该读数发生在哪个城市为聚合函数max不能用于WHERE 子句中。存在这个限制是因为WHERE子句决定哪些行可以进入聚合阶段；因此它必需在聚合函数之前计算
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

--聚合同样也常用于 GROUP BY子句。比如，我们可以获取每个城市低温的最高值
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
--这样每个城市一个输出。每个聚合结果都是在匹配该城市的行上面计算的。我们可以用HAVING过滤这些分组
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
--这样就只给出那些temp_lo值曾经有低于 40 度的城市。最后，如果我们只关心那些名字以"S"开头的城市，我们可以用
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%'
GROUP BY city
HAVING max(temp_lo) < 47;

--2.8. 更新
--你可以用UPDATE命令更新现有的行。假设你发现所有 11 月 28 日的温度计数都低了两度，那么你就可以用下面的方式更新数据
UPDATE weather
SET temp_hi = temp_hi-2,temp_lo = temp_lo-2
WHERE date >'1994-11-28';

--2.9. 删除
--数据行可以用DELETE命令从表中删除。假设你对 Hayward 的天气不再感兴趣，那么你可以用下面的命令把那些行从表中删除
--DELETE FROM weather WHERE city = 'Hayward';

--章 3. 高级特性
--3.2. 视图回头看看第 2.6 节里的查询。假设你的应用对天气记录和城市位置的组合列表特别感兴趣，而你又不想每次键入这些查询。那么你可以在这个查询上创建一个视图，它给这个查询一个名字，你可以像普通表那样引用它
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;

SELECT * FROM myview;
--3.3. 外键
CREATE TABLE cities (
city varchar(80)primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
INSERT INTO weather VALUES ('San Francisco', 45, 53, 0.0, '1994-11-28');

--3.4. 事务
--在PostgreSQL里，一个事务是通过把 SQL 命令用BEGIN和COMMIT 命令包围实现的。因此我们的银行事务实际上看起来像下面这样：
--如果在该事务的过程中，我们决定不做提交(可能是我们刚发现 Alice 的余额是负数)，那么我们可以发出ROLLBACK而不是COMMIT命令，那么到目前为止我们的所有更新都会被取消。
BEGIN;
UPDATE weather
SET temp_hi = temp_hi-2,temp_lo = temp_lo-2
WHERE date >'1994-11-28';
-- 等等
COMMIT;
--记得我们的银行数据库吗? 假设我们从 Alice 的帐户上消费 $100.00 ，然后给 Bob 的帐户进行加款，稍后我们发现我们应该给 Wally 的账号加款。那么我们可以像下面这样使用保存点：
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;
--这个例子当然是实在太简单了，但是通过使用保存点，我们可以对事务块有大量的控制。并且，ROLLBACK TO是除了事务全部回滚，重新来过之外，唯一可以用于重新控制一个因错误而被系统置于退出状态事务的方法。

3.5. 窗口函数（这个不是很清楚）

窗口函数在和当前行相关的一组表行上执行计算。这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数，使用的窗口函数不会导致行被分组到一个单一的输出行；行保留其独立的身份。在后台，窗口函数能够访问的不止查询结果的当前行。

这里是一个例子，说明如何比较每个员工的工资和在他或她的部门的平均工资：

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

3.6. 继承

继承是面向对象的数据库的概念。它开启了数据库设计的有趣的新的可能性。

让我们创建两个表：一个cities表和一个capitals表。自然，首府(capital)也是城市(cities)，因此在列出所有城市时你想要某种方法隐含地显示首府。如果你已经很高明了，那么你可能会创造类似下面这样的模式：

CREATE TABLE capitals (
  name       text,
  population real,
  altitude   int,    -- (单位是英尺)
  state      char(2)
);

CREATE TABLE non_capitals (
  name       text,
  population real,
  altitude   int     -- (单位是英尺)
);

CREATE VIEW cities AS
  SELECT name, population, altitude FROM capitals
    UNION
  SELECT name, population, altitude FROM non_capitals;

如果只是查询，那么这个方法运转得很好，但是如果你需要更新某几行，那这个方法就很难看了。

一种更好的方法是：

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (单位是英尺)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

在这个例子里，capitals继承了其父表 cities的所有字段(name,population 和altitude)。字段name的类型text是 PostgreSQL用于变长字符串的固有类型。州首府有一个额外的字段 state显示其所处的州。在PostgreSQL里，一个表可以从零个或者更多其它表中继承过来。

比如，下面的查询找出所有海拔超过 500 英尺的城市的名字，包括州首府：

SELECT name, altitude
  FROM cities
  WHERE altitude > 500;

另一方面，下面的查询找出所有不是州首府并且位于海拔超过500英尺的城市：

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

cities前面的ONLY指示系统只对cities 表运行查询，而不包括继承级别中低于cities的表。许多我们已经讨论过的命令—SELECT, UPDATE和 DELETE—都支持这个ONLY表示法。

转载于:https://my.oschina.net/u/3238697/blog/3021871

chongyi1654

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
PostgreSQL_学习_1

--1.3. 创建一个数据库 CREATE DATABASE db_shing_test WITH ENCODING='UTF8' CONNECTION LIMIT=-1; --drop DATABASE db_shing_test; --2.3. 创建新表 int, smallin...
复制链接

扫一扫