postgres9.3手册-I部分-笔记7章

原创
2016/01/27 16:16
阅读数 16

从数据库中检索数据的过程或命令叫做查询。

表引用可以是一个表名字或者一个生成的表,比如子查询,表连接,或者这些东西的复杂组合。

如果一个表引用的是一个简单的父表名字,那么包括其后代子表的行,除非在表名前加ONLY。

内连接,外连接,交叉连接。

交叉连接就是笛卡尔积。T1 CROSS JOIN T2  等效于T1 INNER JOIN T2 ON TRUE

INNER,OUTER 对所有连接类型都是可选的,INNER 为缺省,LEFT,RIGHT,FULL隐含外连接。

连接条件在ON或者USING子句里声明,或者用关键字NATURAL隐含声明。连接条件判断来自两个表中的哪些行是匹配的。

USING(a,b,c)等效于ON(t1.a=t2.a and t1.b=t2.b and t1.c=t2.c)

那么在结果里 a,b和c字段都会有两个,而用USING的时候就只会有一个。

NATURAL是USING的缩写形式,它自动形成一个由两个表中同名的字段组成的USING列表,如果没有同名字段,NATURAL的行为会像CROSS JOIN。

 条件连接的类型

inner join 对于T1的每一行R1 ,如果能在T2中找到一个或者多个满足连接条件的行,那么这些满足条件的每一行都在连接表中生成一行,(完整匹配)

 left outer join 首先执行一次内连接,然后为每一个T1中无法在T2中找到匹配的行生成一行,改行中对应T2的列用NULL补齐,包含T1的所有行,

right outer join  首先执行一次内连接,然后为T2中无法在T1中找到匹配的行生成一行。改行中对应T1的列用NULL补齐,包含T2的所有行,

full outer join全连接,首先执行一次内连接,然后为每个T1,T2中找不到匹配的生成一行,该行中无法匹配的列用NULL补齐。包含T1.T2的所有行。

限制放在ON子句中时是先于连接处理的,而限制放在where子句中时是后于连接处理的。

AS 关键字没啥特别的含义,alias可以是任意标识符

取了别名之后就不允许使用最初的名字了

表别名对于自连接是必需的。

子查询的结果(派生表)必须包围在圆括弧里并且赋予一个别名

表函数是那些生成一个行集合的函数,这个集合可以是由基本数据类型组成,也可以是由复合数据类型组成。他们的用法类似一个表,视图,或者from子句里的子查询,表函数返回的字段可以是一个表,视图,或者子查询字段那样包含在SELECT ,JOIN ,WHERE子句里。

from子句中出现的子查询可以放在关键字LATERAL 之前,这样就允许它们引用通过前置FROM 条目提供字段,如果没有LATERAL,那么每个子查询都被认为是独立的并且不能交叉引用任何其他的FROM 条目。

from 中出现的表函数也可以出现在关键字LETERAL之前,但是对于函数来说,这个关键字是可选的,函数的参数在任何情况下都可以包含通过前置from条目提供的字段,

LATERAL条目可以出现在from列表的顶级,或者在join树中,在后者的情况下,它在join右侧时也可以参考左侧的条目

select * from tb10 a inner join (select id,name2 from tb11) b on a.id=b.id;

select * from tb10 a inner join lateral(select id,name2,a.name1 from tb11)b on a.id=b.id;

在通过了where过滤器后,生成的输入表可以继续用group by子句进行分组,然后用having子句选取一些分组行

 group by子句用于将一个表中所有列出的字段值相等的行分成一组,字段列出的顺序没什么关系,效果是将每个拥有相同的行集合并为一组,代表改组中的所有行,这样就可以删除输出里的重复,和或计算应用于这些组的聚合函数。

如果查询包含窗口函数,这些函数执行了分组,聚合和HAVING过滤之后被评估,也就是说,如果查询使用任何聚合,GROUP BY 或者HAVING,那么由窗口函数发现的行是该组行而不是从FROM /WHERE 得到的原始表行。

当多个窗口函数被使用的时候,所有在他们窗口定义里依照语法等效于PARTITION BY 和ORDER BY 子句的窗口函数保证在同一个过去的暑假里被评估。

ORDER BY 不唯一确定一个排序。

SELECT 命令汇总的表表达式通过组合表,视图,删除行,分组等构造了一个中介性的虚拟表,这个表最后传递给选择列表处理。选择列表最终实际输出虚拟表的那些字段。

如果将值表达式用于选择列表,那么它在概念上向返回的表中增加了一个新的虚拟字段。值表达式为结果中的每一行进行一次计算。计算之前用改行的数值替换任何表达式里引用的字段。不过选择列表中的这个表达式并非一定要引用来自from子句中表表达式里面的字段。

为了防止和未来补充的关键字发生冲突,建议要么写AS 要么为输出列名加双引号标记。

输出字段名的命名和在from子句里的命名是不一样的,这样就允许你对同一个字段命名两次。from子句里的名字将被选择列表使用,而选择列表中新取的名字将被最终输出。

在处理完选择列表后,生成的表可以删除重复行,直接在SELECT 后面写上DISTINCT关键字即可。

DISTINCT中NULL被认为是相同的。

distinct on 以某个字段分组,然后去重, select distinct on(a) a,b from t group by a,b

组合查询

UNION把query2的结果附加到query1的结果上,并且像distinct那样删除结果中所有重复的行,除非声明了UNION ALL

INTERSECT返回那些同时存在与query1和query2中的行,除非声明了INTERSECT ALL 否则所有重复行都被删除。

EXCEPTF返回所有在query结果汇总但是不再query2结果中的行,除非声明了EXCEPT ALL否则所有重复行都被删除。

在查询生成输出表之后,也就是在处理完选择列表之后,还可以对输出表进行排序,如果没有排序,那么行将会以不可以预测的顺序返回(实际顺序将取决于扫描和连接规划类型和在磁盘上的顺序,但是肯定不能依赖这些东西)。

确定的顺序只能在明确的使用了排序步骤之后才能保证。

NULLS FIRST和NULLS LAST选项可以决定在排序操作中在non-null之前还是之后,默认情况下,空值大于任何非空值,

排序选项对于每个排序列是相对独立的,order by x,y desc意思是说order by a asc,y desc

输出的字段名必须是独立的,不允许在表达式中使用。也就是说,order by 子句在select子句中是一个表达式,那么在order by 子句中不能是表达式了,在该基础上不能在参与运算。

PostgreSQL使用默认的B-tree操作符类为表达式的数据类型确定ASC和DESC排序顺序。一般来说,数据类型一般转换为适合<和>操作符进行排序。

limit all和省略limit是一样的,

limit 和offset都出现了,那么在计算返回的limit之前先忽略offset指定的行数。

offset指明在开始返回行之前忽略多少行。

使用limit的同时使用order by子句把结果行约束成一个唯一的顺序是一个好主意,否则就会得到一个不可预料的子集。

查询优化器在生成查询规划的时候会考虑LIMIT,因此如果你给LIMIT和OFFSET的值不同,那么你很可能得到不同的规划,因此,使用不同的LIMIT/OFFSET值选择不同的子集将生成不同的结果,

OFFSET子句忽略的行仍然需要在服务器内部计算,因此一个很大的OFFSET还是不够有效率。

每个WITH子句中的辅助语句可以是一个SELECT,INSERT,UPDATE,DELETE,

 WITH中的SELECT的本意是为了将复杂的查询分解为更加简单的部分。当牵涉到多级嵌套的时候,可以考虑用WITH

RECURSIVE修饰符将WITH从一个单纯的语法方面改变为一个SQL标准中不可能实现的功能。使用RECURSIVE,一个WITH查询可以引用它自己的输出。

一个递归WITH查询的一般形式总是一个non-recursive term 然后你union all,然后一个 recursive term,其中只有递归的术语可以包含一个对查询自己输出的引用,

当使用递归查询的时候,确保查询的递归部分最终不会返回元组是很重要的,否则查询将会无限的循环下去,

有时通过UNION替代UNION ALL去除掉与前面输出重复的行可以实现。








展开阅读全文
打赏
1
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
1
分享
返回顶部
顶部