开发者学堂课程【PostgreSQL快速入门：3 PostgreSQL psql的使用，SQL语法，数据类型，递归SQL用法（四）】学习笔记与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/16/detail/89
3 PostgreSQL psql的使用，SQL语法，数据类型，递归SQL用法（四）
内容介绍：
八、DML
九、Query
十、练习
八、DML
INSERT
UPDATE
DELETE
一个事务最大2^32条SQL(因为cmin,cmax的长度是4Bytes)一个事务中可以执行40条sql语句，cmin,cmax相当于事务中某一条sql语句的编号，四个字节存储。
PostgreSQL一个事务中可以包含DML, DDL, DCL。
Begin；
Drop table t；
Rollback；
t表还在，可以放在事务里面。
除了以下
create tablespace
create database
使用concurrently并行创建索引
其他未尽情况略
(Oracle执行DDL前自动将前面的未提交的事务提交,所以Oracle不支持在事务中执行DDL语句)
九、Query
1、JOIN
T1 CROSS JOIN T2 (T1 INNER JOIN T2 ON TRUE )
T1 [INNER] LEFT RIGHT FULL ; [OUTER] , JOIN T2 ON boolean_ expression支持内部join还支持外部join
T1 [INNER] LEFT RIGHT FULL ; [OUTER] , JOIN T2 USING ( join column list )
T1 NATURAL [INNER] LEFT RIGHT FULL , [OUTER] JOIN T2
The words INNER and OUTER are optional in all forms. INNER is the default; LEFT, RIGHT,and FULL imply an outer join.
减少sql的写法，比如两个表的字段名相同，类型相同的情况下，可以用NATURAL join，NATURAL列刚好相同的字段时可以用NATURAL join
In the join只有两个表完全匹配时join才有输出，left join左边的表跟右边的表没有任何匹配的情况下，记录也可以输出。
CROSS JOIN产生笛卡尔积。不带任何条件。两个表之间每一条都会有相互关系。
当关联的两个表有两列同名同类型同长度,以下三种写法等同。
Natural inner join t1表和t2表有一个字段是相同的并且同名同类型就可以省掉 join的字段。num跟Natural join一样。
左或右连接,不满足条件的右或左表的值。
Left join t2 on t1，t1表的id等于2跟右边的number等于2没有匹配，因为右边的表只有1和3，左边的表有123，关联时左边的表也会输出。Right join也是一样，t2表所有的字段跟t1表没有匹配时也有输出。
全关联,不满足条件的值都置空。匹配的可以输出，不匹配也能输出。
2、ALIAS
table alias: 表的别名
FROM table_reference AS alias
FROM table_reference alias
colum alias:列的别名
SELECT expression [[ AS ] output_ name ]
subquery alias:只查询的别名
FROM (SELECT * FROM table1) AS alias_ name
3、Table as Function's Return data type作为表的返回类型
表或者函数返回的数据类型，把一个表名作为函数的返回类型。
return table's row type
create table t1 (id int,name text,crt_ time timestamp(o));创建表是t1表
create or replace function f_ t1 (i_ id int) returns setof t1 as $$返回类型也可以指定t1，在创建一个表时，默认创建跟表结构一样的复合数据类型
declare
begin
return query select * from t1 where id=i_id;直接把表返回也可以
return;
end;
$$ language plpgsql;
insert into t1 values(1,'digoal',now0);
insert into t1 values(1,'DIGOAL',now0);
select * fromf _t1(1);
id] nameI crt_ time
1 digoal I 2012-04-26 08:15:09
1 DIGOALI 2012-04-26 08:15:15
return composite type返回复合类型
create type type1 as (id int,name text,crt_time timestamp(o));创建复合类型跟t1表一样
create or replace function f_ type1 (i_id int) returns setof type1 as $$
declare
begin
return query select * from t1 where id=i_id;
return;
end;
$$ language plpgsql;
select * from f_ type1(1);
id I name I crt_ time
1 I digoal I 2012-04-26 08:15:09
1 I DIGOAL I 2012-04-26 08:15:15
return record 弹性数据类型，可能有一个字段，也可能有两个字段，在弹性类型里面返回的时候输出的是什么结构
create or replace function f_ record1 (i_ id int) returns setof record as $$
declare
begin
return query select * from t1 where id=i _id;
return;
end;
$$ language plpgsql;
输出的是t1的数据类型，写上对应t的框架
select * from f_ record1(1) as (id int,name text,crt_time timestamp(o));
id I Name I crt_ time
1 I digoal I2012-04-26 08:15:09
1 I DIGOAL I 2012-04-26 08:15:15
4、GROUP BY [ HAVING ]
5、DISTINCT
SELECT DISTINCT setect_ list ... (NULL/EDISTINCT [ON]中视为相等)
postgres= # select * from t1 ;
id name crt_ time
1 I digoal I 2012-04-26 08:15:09
1 I DIGOAL I 2012-04-26 08:15:15
postgres= # select distinct id from t1;
id
1
SELECT DISTINCT ON (expression [, expression .. ])select_ list ... -- ON()里面必须出现在order by中作为前导列，输出跟前导列相关的DISTINCT ON，同时输出其它的查询列，可以简化使用窗口函数
Here expression is an arbitrary value expression that is evaluated for all rows. A set of rows for which all the expressions are equal are considered duplicates, and only the first row of the set is kept in the output. Note that the "first row" of a set is unpredictable unless the query is sorted on enough columns to guarantee a unique ordering of the rows arriving at the DISTINCT filter. (DISTINCT ON processing occurs after ORDER BY sorting.)
postgres= =# select distinct on (id)-id,name,crt_ time from t1 order by id,crt_ time;输出id是唯一的，id只输出一个，按照顺序进行排序，只要拿到一条就返回，digoal查处消息排在第一位，当id=1取到两条，只会要第一条。
Id name crt_ time
1 digoal 2012-04-26 08:15:09
postgres= =# select distinct on (id) id,name,crt_ time from tl order by id,crt_ time desc;必须在前导列里
Id name crt_ time
1 DIGOAL 2012-04-2608:15:156、COMBINING QUERY
Id name也是一样，要放在前面
使用DISTINCT ON实现用窗口函数实现的取第一名的功能
postgres=# CREATE TABLE window_ test(id int, name text, subject text, score numeric);创建表，记录学生每一科的考试成绩，插入15条记录，每个学科取一个，取出id，取出name，分数，科目
postgres=# INSERT INTO window_ test VALUES (,digoal,'数学',99.5), (2,digoal,;语文',89.5),
(3,'digoal',英语',79.5), (4,digoal",;物理' ,9.5), (5,digoal;'化学',98.5),
(6,刘德华';数学,89.5), (7,刘德华',语文.,99.5), (8,刘德华,英语',79.5),
(9,刘德华,物理,89.5), (10,刘德华;化学',69.5),
(11,张学友，数学",89.5), (12,张学友,语文",91.5), (13,张学友,英语',92.5),
(14,张学友，物理",93.5), (15,;张学友，化学,94.5);
取出每门课程的第一名。
postgres=# select distinct on (subject) jid,name,subject,score from window_test order by subject,score desc;排序按照倒叙
5 Digoal 化学98.5
1 Digoal 数学99.5
4 Digoal 物理99.5
13 张学友 英语92.5
7 刘德华 语文99.5
与使用窗口函数得到的结果一致, 并且写法更简洁。
select = from <select row_number() over < partition by subject order by score desc> as rn, * from window test> as t where rn
=1;
5 Digoal 化学98.5
1 Digoal 数学99.5
4 Digoal 物理99.5
13 张学友 英语92.5
7 刘德华 语文99.5
合并查询，两个查询的结果集合起来，两个查询的结果集算出相交的部分，第一个查询结果集排除掉第二个查询的结果集的一些结果，
queryl UNION [ALL] query2 两个查询结果集合并并且去重
queryl INTERSECT [ALL] query2 两个结果集合并不用去重
queryl EXCEPT [ALL] query2
5"
UNION effectively appends the result of query2 to the result of 'queryl (although there is no guarantee that this is the order in which the rows are actually returned)
INTERSECT returns all rows that are both in the result of queryl and in the result of query2.两个查询共有的部分，相交的部分输出并且去重，all就不去重
EXCEPT returns all rows that are in the result of queryl but not in the result of 'query2.
Combining Query eliminates duplicate rows from its result, in the same way as DISTINCT,
I unless ALL is used
queryl and query2 must return the same number of columns and the corresponding columns
have compatible data types.
8、LIMIT [ OFFSET ]
SELECT select_ list
FROM table_ expression
ORDER BY sort_ expressionl [ASCIDESC] [NULLS { FIRST[LAST} ,]空放在最前面还是最后面
[, sort_ expression2 [ASC I DESC] [NULLS i FIRST LAST ]...]
限制多少行，比如表只输出一行，就只有一个，offset10扫描到第十行之后只输出一行。
9、函数三态简介
非常重要的函数三态
在prostgre里面函数有三个状态，第一个跟函数本身有关，第二个函数的三个状态对prostgre执行计划的优化器会拿来用，优化器如果看到函数是超级稳定的状态，比如immutable，在执行时对于同样的参数传入在sql语句里面，即使扫描了多行，也只会处理一遍，immutable类似于常量，只会同样的参数传入，只会传入一遍返回结果，sql语句在执行时会大大减少运算量。
immutable最稳定的状态
Stable第二稳定
Volatile最不稳定，即使传入的参数是一样的，输出的结果，多次调用输出的结果可能不一样，比如select-now函数，多次调用，输出的结果不一样，对于常量的函数，如果是Stable和Volatile在一次事务中多次执行的结果可能是一样的。
创建一个序列seq
digoal=# create sequence seq;
CREATE SEQUENCE
取序列的下一个值，最不稳定
digoal=# select provolatile from pg_ proc where proname = 'nextval';
在一次事务中不停的调用它，得到的值是不一样的，即使是在sql语句中调用它也是不一样的，比如调用15条记录，得到的值都是不一样的。
provolatile
V
(1 row)
digoal=# select nextval('seq'::regclass) from generate_ _series(1,3);
Nextval
1
2
3
(3 rows)
改成immutable后只会执行一次，优化器在解析sql时只会执行一次，不管有多少条记录30条记录也只会执行一次。
digoal=# alter function nextval(regclass) immutable;
ALTER FUNCTION
digoal=# select nextval('seq'::regclass) from generate_ series(1,3);
nextval
4
4
4
(3 rows)
digoal=#alter function nextval(regclass) stable；执行一次，只不过放在查询里面和放在后面不一样
ALTER FUNCTION
digoal=# select nextval'seq':regclass) from generate_ series(1,3);
nextval
5
6
7
(3 rows)
在select子句和在where子句中有差别。
例如某个表有5条记录:
digoal=# select *from t;
Id
3
3
3
4
5
(5 rows)
对于stable态,它在select中，每条tuple都要运算到这个函数，但是在where子句中,只运算一次。
select nextval<' seq' >from t;
select from t where nextval<' seq' >=138;只执行一遍，三条记录都返回，执行多遍只返回一条记录
select nextval<’seq'>;
nextval
142
在where子句中，在selec字句中，stable有一定的区别，跟解析有关系。
stable和immutable的不同之处,immutable两遍都是一样的，where字句中和查询字句中结果一样。
stable函数在execute时执行。
immutable函数在plan时执行。
所以stable和immutable仅仅当使用planed query时才有区别。
如果使用执行器绑定变量的查询，Planed query属于一次plan，多次execute，immutable在Planed query中也只会执行一次，就是常量，而stable函数是在execute执行的，在这种场景中是会执行多次的。
immutable稳定性>stable>volatile
另一些需要注意的:
函数内有多条SQL时,例如
digoal=# Create or replace function f10 returns setof int as$$
declare
v_ id int;
begin
Forv_ id in select id from t loop
return next V_ id;
End loop;
在这之间如果t发生了变更并提交了，volatile函数能看到这个变更。
perform pg_sleep(10);在执行过程中每一个sql语句看到的不是一个
For v_ id in select id from t loop
return next v_id;
End loop;
Return;
End;
$$ language plpgsql strict volatile;
volatile处理每个SQL都是一个新的snapshot. Stable和immutable将调用函数时作为这个snapshot。
创建函数，中间休息十秒钟，返回int类型，来自于t表，休息十秒钟，把t表id全部储存，如果期间有过变更，当前123，三条记录，第一次取出的是前面几条，第二次取出后面几条，如果是在执行过程中delete就只会返回三条记录，后面的变更也会看到，t表已经没了，如果把函数改成stable，再进行查询，比如插入几条数据，都看不到，0 rows，使用volatile函数在整个里面不是镜像，而是每一条sql语句都会产生pg_sleep隔离级别的类似的查询，对于Stable和immutable函数，停了10秒钟再查询，看到的是调用函数的。
Select=from f1() ;
多次调用和单词调用有区别，当前状态改成immutable。
function f1<>immutable；
immutable函数只调用一次，只会select10秒，十秒之后就可以看到结果，如果改成volatile，每一条就都会执行，执行十次，一百秒之后才会看到结果，pg_sleep默认volatile执行，三条记录会执行3遍，如果改成immutable就只会执行一遍，只需要一秒出结果。对于大运算量的函数,且一条sQL如果返回多值,并且传入的参数一致的情况下. 调用一次显然比调用多次耗费小。
实际使用过程中选择好三态是非常重要的。
10、WITH
as把查询作为一个表，对两个表进行关联查询
WITH regional_ sales AS (
SELECT region, SUM(amount) AS total_ sales
FROM orders
GROUP BY region
),top_ regions AS ( 也可以是update查询，数据后面再进行查询也可以
SELECT region
FROM regional sales
WHERE total_ sales > (SELECT SUM(total_ sales)/10 FROM regional_sales)
）
SELECT region,
product,
SUM(quantity) AS product_ units,
SUM(amount) AS product_ sales
FROM orders
WHERE region IN (SELECT region FROM(top_ regions)
GROUP BY region, product;
如果是递归查询使用valus
Valus（1）非递归字句
Union all 不去重，Union去重
SELECT n+1 FROMt WHERE n< 100 递归字句
）
SELECT sum(n) FROM t;输出的结果做查询，中间会产生临时存放的对象
UNION ALL去重复(去重复时NULL视为等同)
图中所有输出都涉及UNION[ALL]的操作,包含以往返回的记录和当前返回的记录
图表示如何做循环的，从非递归子句开始，输出放在output里面，递归字句读取output，同时输出到TEMP Working TABLe临时工作表里面，会有判断，如果临时工作表里面有数据，它会进入递归，再次调用递归子句，递归子句会先把临时工作表的数据读出来进行关联，递归子句里面有关联，t是工作表，where条件，再次查询，输出到TEMP immutable table替换掉twt的内容后清空自己，递归子句执行后TEMP Working TABLe已经刷新了，已经从TEMP immutable table刷到TEMP Working TABLe中，表已经清掉，如果TEMP Working TABLe中有数据，又会进入循环，如果没有数据就直接输出了，在循环里面有第六步还做了输出，递归子句从TEMP Working TABLe读数据同时会产生输出，输出到output，有合并的动作，如果是UNION就会去重，如果是UNION all就直接扔到output中。如果在TEMP Working TABLe中一直有数据，就会进入无限的循环。
TEMP Working Table 没有ctid, cmin, cmax, xmin, xmax, tableoid虚拟字段，如果有会报错
postgres=# create table test (id int,name text);
postgres=# insert into test values (1,'digoall),(2,'digoal2');
postgres=# begin;
postgres=# with t1 as (update test set name= ='DIGOAL2' where id=2 returning *)
select ctid from t1;
ERROR: column "ctid" does not exist
LINE 2: select ctid from t1;
postgres =# rollback;
其它字段(cmin,cmax,xmin,xmax,tableoid)同样错误
递归查询输出产品部以及该部门的所有子部门信息。
然后输出各个子部门以及各个子部门的人数。
第一次取出的表隐式使用，中间表
WITH RECURSIVE included_ parts(sub_ part, part, quantity) AS (开头，非递归子句
初始输出
SELECT sub_part, part, quantity FROM parts WHERE part=产品部’。子部门，部门id，人数
UNION ALL不去重
把第一个sql取到的值作关联，
SELECT p.sub_ part, B:part, p.quantity循环sql
第一步时读取的是初始输出，后面都是TEMP Working TABLE
FROM included parts pr, parts p进行关联
WHERE p.part = pr.sub_ part部门等于子部门，输出，只要产品部输出之后就能关联到子部门sub，就是r表，父表，父表的子部门等于p表的部门id，输出第一个子部门，输出完之后会放到临时工作表，相当于产品部第一个子部门，第一步循环到达，再进行循环，一直到两个字段不匹配，把所有的子部门都输出来，在递归循环后面再做操作
)
SELECT sub_ part, SUM(quantity) as total quantity输出每一子部门的部门id和总人数
FROM included_ parts
GROUP BY sub_ part
死循环
每次递归输出的记录与以往的记录都不一样,TEMP Working Table永远都有记录,因此无限循环。
规避上一个死循环的方法
让递归SQL有机会没记录输出
WITH RECURSIVE search_ graph(id, link, data, depth, path, cycle) AS (
SELECT g.id, g.link, g.data, 1,
ARRAY[g.id],
False 变成ture
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth+ 1,
Path｜｜g.id,
g.id = ANY(path)
FROM graph g, search_ graph sg
WHERE g.id = sg.link AND NOT cycle判断，变成flase
）
SELECT * FROM search_ graph;
多值比较需使用ROW类型的ARRAY。
WITH RECURSIVE search_ graph(id, link, data, depth, path, cycle) AS (
SELECT g.id, g.link, g.data, 1,
ARRAY[ROW(g.f1, g.f2)],
false
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth+1,
path｜｜ROW(g.f1, g.f2),
ROW(g.f1, g.f2)= ANY(path)相当于数组
FROM graph g, search_graph sg
WHERE g.id = sg.link AND NOT cycle
）
SELECT * FROM search_ graph;
还有什么情况可以跳出循环
WITH RECURSIVE t(n) AS(
SELECT 1
UNION ALL
SELECT n+1FROM t
SELECT n FROM t LIMIT 100;
注意如果t表在递归子句的外围被join了然后再limit的，还死循环
使用递归查询注意防止死循环。
把属于产品部以及它的子部门的记录删除。跟第一个例子差不多。
WITH RECURSIVE included_ parts(sub_ part, part) AS (
SELECT sub part, part FROM parts WHERE part = '产品部'
UNION ALL
SELECT p.sub_ part, p.part
FROM included_ parts pr, parts p
WHERE p.part = pr.sub_ part
)
DELETE FROM parts
WHERE part IN (SELECT part FROM included_ parts);
WITH的所有子句包括MAIN子句查看到的是一个SNAPSHOT。拆成很多个sql语句，看到的是同一个SNAPSHOT。
各个子句对记录的变更相互看不到，如果要看到变更的数据需使用RETURNING子句。比如update一些记录，对于查询语句，它看到的是update之前的记录，并不是update之后的，只有RETURNING *才会查产品表，查t表，看到更新后的记录，否则看到的就是更新前的记录，
WITH t AS(
UPDATE products SET price = price * 1.05 WHERE id= 10
RETURNING *
SELECT * FROM products WHERE id= 10;
WITH t AS (
UPDATE products SET price= price * 1.05 WHERE id= 10
RETURNING *
）
SELECT* FROM t;
测试表
postgres =# create table test (id int,name text);
CREATE TABLES
postgres= =# insert into test values(1,'digoall),(2,'digoal2');
这样会看到老数据
postgres= =# with t1 as (update test set name ='NEW' where id=2 returning *)
postgres-# select * from test where name ='NEW";
id name
(0 rows)
postgres= =# with t1 as (update test set name= ='NEWNEW" where id= 2 returning *)
postgres-# select * from t1 where name ='NEWNEW";
id name
2 I NEWNEW
(1 row)
十、练习
1、psqI的使用熟悉。特别是psql的帮助，对于使用会带来很大的便利性，查看表结构，查看表大小，都可以查询，比如创建extensions，外部表。
2、数据库基本操作练习,例如建表,表的操作,建立测试模型,使用pgbench测试。
