PostgreSQL基础知识--SQL 语言和高级特性

1. 创建表

create table weather (

    city varchar(80),

    temp_lo int, -- 最低温度

    temp_hi int, -- 最高温度

    prcp real, -- 湿度

    date date

);

create table cities (

    city varchar(80),

    location point

);

2. 插入数据

insert into weather values('San Francisco',46,50,0.25,'2021-04-12');

insert into cities values('San Francisco','(-194.0,53.0)');

3.表连接

3.1 内连接

select * from weather,cities where city=name;

select * from weather inner join cities on (weather.city=cities.name);

3.2 左外连接

select * from weather left outer join cities on (weather.city=cities.name);

3.3 右外连接

select * from weather right outer join cities on (weather.city=cities.name);

4. 更新数据

update weather set temp_hi = temp_hi-2, temp_lo = temp_lo-2 where date = '2021-04-12';

5. 删除数据

delete from weather where city='Hayward';

6. 创建视图

create view mytestview as select city temp_lo,temp_hi,prcp,date,location from weather,cities where city = name;

select * from mytestview;

7. 聚集函数

一个聚集函数从多个输入行中计算出一个结果，比如，count, sum, avg, max, min的函数。聚集函数可以用group by进行分组后查询。每个聚集结果都是在匹配分组条件的表行上进行计算的。可以用HAVING过滤这些被分组的行。

select city, max(temp_lo) from weather where city like 'S%' group by city;

my test

select city, max(temp_lo) from weather where city like 'S%' group by city having max(temp_lo) < 80;

my test

注意：

WHERE和HAVING的基本区别：WHERE在分组和聚集计算之前选取输入行，即，它控制哪些行进入聚集计算。而HAVING在分组和聚集之后选取分组行。因此，where子句不能包含聚集函数，HAVING子句总是包含聚集函数。

8. 外键

外键的作用是维持数据的引用完整性。关键字：references

create table working.cities (

    city varchar(80) primary key,

    location point

);

create table working.weather (

    city varchar(80) references working.cities(city),

    temp_lo int, -- 最低温度

    temp_hi int, -- 最高温度

    prcp real,-- 湿度

    date date

);

9. 事务

事务是所有数据库系统的基础概念。事务最重要的一点是它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作。步骤之间的中间状态对于其他并发事务是不可见的，并且如果有某些错误发生导致事务不能完成，则其中任何一个步骤都不会对数据库造成影响。

BEGIN;

    UPDATE accounts SET balance = balance - 100.00

    WHERE name = 'Alice';

-- etc etc

COMMIT;

也可以利用保存点来以更细的粒度来控制一个事务中的语句。保存点允许我们有选择性地放弃事务的一部分而提交剩下的部分。在使用SAVEPOINT定义一个保存点后，我们可以在必要时利用ROLLBACK TO回滚到该保存点。该事务中位于保存点和回滚点之间的数据库修改都会被放弃，但是早于该保存点的修改则会被保存。

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;

--------------------------------！！！关于事务的测试未成功！！！----------------------------------------------------------

10.窗口函数

窗口函数的调用，总是包含一个窗口函数名（及其参数），后跟着一个OVER子句。这使得它从句法上和一个普通函数或非窗口函数区分开来。

原表数据：

my test

用窗口函数统计各部门的平均工资

my test

也可以通过OVER上的ORDER BY控制窗口函数处理行的顺序

my test

行的顺序不重要时ORDER BY可以忽略。PARTITION BY同样也可以被忽略，在这种情况下会产生一个包含所有行的分区。

my test

这里有一个与窗口函数相关的重要概念：对于每一行，在它的分区中的行集被称为它的窗口帧。 一些窗口函数只作用在窗口帧中的行上，而不是整个分区。默认情况下，如果使用ORDER BY，则帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子句上相等的行。如果ORDER BY被忽略，则默认帧包含整个分区中所有的行。

my test

窗口函数只允许出现在查询的SELECT列表和ORDER BY子句中。它们不允许出现在其他地方，例如GROUP BY、HAVING和WHERE子句中。这是因为窗口函数的执行逻辑是在处理完这些子句之后。另外，窗口函数在非窗口聚集函数之后执行。这意味着可以在窗口函数的参数中包括一个聚集函数，但反过来不行。

在窗口计算执行后进行过滤或者分组，可以使用子查询

my test

当一个查询涉及到多个窗口函数时，可以将每一个分别写在一个独立的OVER子句中。但如果多个函数要求同一个窗口行为时，这种做法是冗余的而且容易出错的。替代方案是，每一个窗口行为可以被放在一个命名的WINDOW子句中，然后在OVER中引用它。

my test

11. 表添加列

my test

12. 继承

继承是面向对象数据库中的概念。关键字：INHERITS

父表

my test

创建子表

my test my test

查询父表时，会将子表中的数据也显示出来；查询子表时，不会显示父表中的数据。

查询子表

my test

查询父表

my test

也可以单独查询父表的数据-----ONLY。SELECT、UPDATE 和DELETE都支持这个ONLY记号。

my test

删除父表数据时，不加only，会把子表中符合条件的数据也删除

my test

父表

my test

子表

my test

删除时，加only，只删除主表数据

my test

父表

my test

子表

my test

 注：1.父表的检查约束和非空约束会被子表继承。其他约束（如唯一约束，主键，外键）则不会被继承。

        2.一个子表可以从多个父表继承，当同名字段出现在多个父表中（或者父表和子表中），这些字段会被融合（此时字段类型必须相同，否则会抛出一个错误）。

增加继承：

一个已经被创建的表可以增加继承关系，但是新的子表必须已经包括和父表相同名称和数据类型的列。子表还必须包括和父表相同的检查约束和检查表达式。

增加继承关系失败

my test

子表增加列后，继承关系修改成功

my test

子表

my test

新增父表

my test my test

移除继承：

my test

父表

my test