postgresql 基础

深碍√TFBOYSˉ_ 2022-04-22 02:14 182阅读 0赞

## PostgreSQL 基本语法 ##

这里记录些PostgreSQL的基本语法，内容基本来自[ \[PostgreSQL 9.5.3 中文手册\]][_PostgreSQL 9.5.3]

### 创建数据库 ###

CREATE DATABASE mydb;
     
      
       1

### 删除数据库 ###

DROP DATABASE mydb;
     
      
       1

### 创建表 ###

CREATE TABLE weather ( city varchar(80), temp_lo int, -- 最低温度 temp_hi int, -- 最高温度 prcp real, -- 湿度 real是一种用于存储单精度浮点数的类型 date date );
    CREATE TABLE cities ( name varchar(80), location point );
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11

### 删除表 ###

DROP TABLE cities;
    DROP TABLE weather
     
      
       1
       2

### 在表中增加行 ###

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 (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);
     
      
       1
       2
       3
       4
       5
       6

### 查询 ###

SELECT * FROM weather;
    SELECT * FROM cities;
    SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
    --可以在选择列表中写任意表达式
    SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
    --检索旧金山的下雨天的天气：
    SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
    --要求返回的查询结果是排好序的
    SELECT * FROM weather ORDER BY city;
    SELECT * FROM weather ORDER BY city, temp_lo;
    --消除重复的行：
    SELECT DISTINCT city FROM weather;
    SELECT DISTINCT city FROM weather ORDER BY city;
    --连接查询,一个同时访问同一个或者不同表的多个行的查询叫连接查询。
    SELECT * FROM weather, cities WHERE city = name;
    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;
    --使用内连接查询
    SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
    --观察结果集没有城市Hayward的结果行。这是因为连接查询会忽略各表中不匹配行
    --如果需要显示表中不匹配行，那就使用外连接查询
    SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
    --这个查询是一个左外连接， 因为在连接操作符左部的表中的行在输出中至少要出现一次， 而在右部的表的行只有在能找到匹配的左部表行是才被输出。如果输出的左部表的行没有对应匹配的右部表的行，那么右部表行的列将填充空值（null）。
    --我们也可以把一个表和自己连接起来。这叫做自连接
    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;
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
       21
       22
       23
       24
       25
       26
       27
       28
       29
       30
       31
       32
       33
       34

### 聚集函数 ###

在一个行集合上计算count（计数）、sum（和）、avg（均值）、max（最大值）和min（最小值）的函数。

--查询所有记录中最低温度中的最高温度
    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 过滤这些被分组的行
    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) < 40; 
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17

WHERE和HAVING的基本区别如下：WHERE在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）。因此，WHERE子句不能包含聚集函数； 因为试图用聚集函数判断哪些行应输入给聚集运算是没有意义的。相反，HAVING子句总是包含聚集函数（严格说来，你可以写不使用聚集的HAVING子句， 但这样做很少有用。同样的条件用在WHERE阶段会更有效）。

### 更新 ###

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

### 删除 ###

DELETE FROM weather WHERE city = 'Hayward';
    DELETE FROM tablename;
     
      
       1
       2

如果没有一个限制，DELETE将从指定表中删除所有行，把它清空。做这些之前系统不会请求你确认！

### 视图 ###

--创建视图
    CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
    --查看视图
    SELECT * FROM myview;
    --删除视图
    DROP VIEW myview
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9

### 外键 ###

为了维持数据的引用完整性

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 ('Berkeley', 45, 53, 0.0, '1994-11-28');
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13

### 事务 ###

事务最重要的一点是它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作,一个事务被称为是原子的。  
步骤之间的中间状态对于其他并发事务是不可见的。  
在PostgreSQL中，开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来。因此我们的银行事务看起来会是这样：

BEGIN;
    UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';
    COMMIT;
     
      
       1
       2
       3
       4

如果，在事务执行中我们并不想提交，我们可以发出ROLLBACK命令而不是COMMIT命令，这样所有目前的更新将会被取消。  
PostgreSQL实际上将每一个SQL语句都作为一个事务来执行。如果我们没有发出BEGIN命令，则每个独立的语句都会被加上一个隐式的BEGIN以及（如果成功）COMMIT来包围它。一组被BEGIN和COMMIT包围的语句也被称为一个事务块。  
也可以利用保存点来以更细的粒度来控制一个事务中的语句。保存点允许我们有选择性地放弃事务的一部分而提交剩下的部分。在使用SAVEPOINT定义一个保存点后，我们可以在必要时利用ROLLBACK TO回滚到该保存点。该事务中位于保存点和回滚点之间的数据库修改都会被放弃，但是早于该保存点的修改则会被保存。  
在回滚到保存点之后，它的定义依然存在，因此我们可以多次回滚到它。反过来，如果确定不再需要回滚到特定的保存点，它可以被释放以便系统释放一些资源。记住不管是释放保存点还是回滚到保存点都会释放定义在该保存点之前的所有其他保存点。  
所有这些都发生在一个事务块内，因此这些对于其他数据库会话都不可见。当提交整个事务块时，被提交的动作将作为一个单元变得对其他会话可见，而被回滚的动作则永远不会变得可见。  
现在假设我们从Alice的账户扣款100美元，然后存款到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';
    -- oops ... forget that and use Wally's account
    ROLLBACK TO my_savepoint;
    UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Wally';
    COMMIT;
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11

–注：ROLLBACK TO是唯一的途径来重新控制一个由于错误被系统置为中断状态的事务块，而不是完全回滚它并重新启动

### 窗口函数 ###

先来一个小例子

SELECT city,temp_lo,avg(temp_lo) OVER(PARTITION BY city) FROM weather
     
      
       1

[_PostgreSQL 9.5.3]: http://www.postgres.cn/docs/9.5/index.html