PostgreSQL数据查询

 2015年04月03日    2322     声明


查询就是从数据库中检索数据的过程,数据查询包括对数据的筛选,及按一定格式返回数据库中存储的符合条件的数据。 在SQL中 SELECT 命令用于声明查询,本文介绍PostgreSQL数据库SELECT查询语句的用法。


1.概述

PostgreSQL数据库中SELECT查询语句的基本格式如下:

SELECT select_list FROM table_expression [sort_specification]

SELECT 
      * | <字段列表>
      [ 
         FROM <表或视图>...
         [WHERE <表达式>]
         [GROUP BY <分组条件>]
         [HAVING <表达式> [{<操作><表达式>}...]]
         [ORDER BY <排序条件>] 
         [LIMIT [,] ] 
      ]


查询表students表所有行和字段,SQL语句如下:

SELECT * FROM students;

以上示列中,选择字段列表为全部字段,表表达式只有一个表。通常,表表达式可以是基本表,连接,和子查询的复杂构造。 也可以省略表表达式记录而用SELECT命令做一些计算。示例如下:

# select 7*8;                                                                                                                                     
 ?column? 
----------
       56
(1 row)


2.选择字段列表

选择字段

SELECT查询的选择的字段列表可以表表达示中的全部字段或部分字段。选择全部字段用”*“表示,部分字段用逗号分隔选择字段。查询部分字段示例如下:

SELECT a,b,c FROM ...

在上面的示例中,查询字段a,b,c来自表表达式,当查询表表达式中有相同名字的字段,查询时需要加上表名或表别名。示例如下:

SELECT table1.a, table2.a, table1.b FROM ...


字段别名

选择字段列表可以赋予新的别名(标签),用以进一步的处理。给查询字段添加别名使用AS关键字,AS关键字也可以省略。示例如下:

SELECT table1.a AS tbl1a, table2.a tbl2a, table1.b AS tbl1b FROM ...


DISTINCT过滤重复行

当查询表表达示中,如如相关联的表存在1对多的情况,查询时就可能会出现重复数据,可以使用DISTINT关键字过滤查询结果中的重复数据。示例如下:

SELECT DISTINCT a,b,c FROM ...


3.表表达式

表表达式包含一个引用查询对象(表或视图)FROM子句,该子句可以根据需要选用WHERE按条件筛选数据和使用GROUP BY、HAVING子句对查询结果进行相关转换。

FROM查询对象

FROM子句从一个用逗号分隔的表,表引用可以是一个表或视图或者是一个生成的表, 比如子查询,一个表连接,或者这些对象的复杂组合。如果在FROM子句中列出了多于一个表,那么它们被cross join形成一个派生表,该表可以进行WHERE,GROUP BY 和 HAVING子句的转换处理,并最后生成所有表表达式的结果。FROM子名的基本格式如下:

FROM table_reference [, table_reference [, ...]]


a.表连接

表连接是指按特定的连接类型的规则从两个其它表(真实表或生成表)中派生表。 PostgreSQL支持的表连接方式有:内连接,外连接和交叉连接类型。

内连接:内连接用INNER JOIN表示,需要被连接的两个表都满足连接条件的才会返回查询数据。查询格式如下:

table1 INNER JOIN table2 ON table1.tbl2_id = table2.id


外链接:外边接有两种形式,左连接(LEFT JOIN或LEFT OUTER JOIN)和(RIGHT JOIN 或RIGHT OUTER JOIN);外连接只需要连接表的一端有数据(左连接时左表有数据,右连接时右表有数据)就会返回查询结果。查询格式如下:

table1 LEFT JOIN table2 ON table1.tbl2_id = table2.id
table1 RIGHT JOIN table2 ON table1.tbl2_id = table2.id


交叉连接:交叉连接会对连接表的行组合进行组合, 假设现有表T1和T2,两个表分别有N和M行,连接成的表将有N*M行。查询如下:

T1 CROSS JOIN T2


示例:现有表T1和T2,表结构及数据如下:

表T1:
 id | value 
----+-------
  1 | t1a
  2 | t1b
  3 | t1c
(3 rows)

表T2:
 id | name 
----+------
  1 | aaa
  2 | bbb
  5 | ccc
(3 rows)

不同类型连接查询返回结果如下:

# SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
 id | value | id | name 
----+-------+----+------
  1 | t1a   |  1 | aaa
  2 | t1b   |  2 | bbb
(2 rows)

# SELECT * FROM t1 INNER JOIN t2 USING (id);
 id | value | name 
----+-------+------
  1 | t1a   | aaa
  2 | t1b   | bbb
(2 rows)

# SELECT * FROM t1 NATURAL INNER JOIN t2;
 id | value | name 
----+-------+------
  1 | t1a   | aaa
  2 | t1b   | bbb
(2 rows)

#  SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
 id | value | id | name 
----+-------+----+------
  1 | t1a   |  1 | aaa
  2 | t1b   |  2 | bbb
  3 | t1c   |    | 
(3 rows)

#  SELECT * FROM t1 LEFT JOIN t2 USING(id);
 id | value | name 
----+-------+------
  1 | t1a   | aaa
  2 | t1b   | bbb
  3 | t1c   | 
(3 rows)


# SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;
 id | value | id | name 
----+-------+----+------
  1 | t1a   |  1 | aaa
  2 | t1b   |  2 | bbb
    |       |  5 | ccc
(3 rows)

# SELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.id;
 id | value | id | name 
----+-------+----+------
  1 | t1a   |  1 | aaa
  2 | t1b   |  2 | bbb
  3 | t1c   |    | 
    |       |  5 | ccc
(4 rows)

#  SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id AND t1.value = 't1a';
 id | value | id | name 
----+-------+----+------
  1 | t1a   |  1 | aaa
  2 | t1b   |    | 
  3 | t1c   |    | 
(3 rows) 

# SELECT * FROM t1 CROSS JOIN t2;
 id | value | id | name 
----+-------+----+------
  1 | t1a   |  1 | aaa
  1 | t1a   |  2 | bbb
  1 | t1a   |  5 | ccc
  2 | t1b   |  1 | aaa
  2 | t1b   |  2 | bbb
  2 | t1b   |  5 | ccc
  3 | t1c   |  1 | aaa
  3 | t1c   |  2 | bbb
  3 | t1c   |  5 | ccc
(9 rows)


b.表别名

在查询中可以给一个表或复杂表定义一个临时的名字,以便在下一步查询中引用。 这样做叫做表别名。创建表别名语法格式如下:

FROM table_reference AS alias
或
FROM table_reference alias

表别名应用最多的情况是,给名字比较长的表添加短别名,以方便引用和易读。示例如下:

 SELECT * FROM a_long_table_name a JOIN another_long_name n ON a.id = n.a_id;

添加表别名后,只能通过别引用表,引用原表名将报错。示例如下:

# SELECT * FROM students s WHERE students.name = 'litbilu';
ERROR:  invalid reference to FROM-clause entry for table "students"
LINE 1: SELECT * FROM students s WHERE students.name = 'litbilu';
                                       ^
HINT:  Perhaps you meant to reference the table alias "s".


c.子查询

子查询指,将一个查询结果做为表引用,子查询必须包圆括号内并且必须赋予一个别名。子查询效率较低,能通表连接实现的查询不建议使用子查询 。示列如下:

SELECT t2.* FROM (SELECT id FROM students) AS t1 JOIN t2 ON t1.id = t2.id;


WHERE筛选条件

WHERE筛选条件语法格式为:

WHERE search_condition

search_condition是一个或一组筛选条件,每个筛选条件是一个返回boolean值的表达式。在完成对FROM子句的处理之后,生成的每一行都会按WHERE筛选搜件进行检查。 如果该条件的结果是真,那么该行输出到输出表中,否则就被抛弃。对于内连接的表,以下查询语句是等效的:

SELECT * FROM a, b WHERE a.id = b.id AND b.val > 5
或
SELECT * FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
或
SELECT * FROM a NATURAL JOIN b WHERE b.val > 5

示例:

SELECT * FROM t1 WHERE id > 5
--IN查询
SELECT * FROM t1 WHERE id IN (1, 2, 3)
SELECT * FROM t1 WHERE id IN (SELECT id2 FROM t2)
SELECT * FROM t1 WHERE id IN (SELECT id3 FROM t2 WHERE  = t1.id + 10)
--BETWEEN
SELECT * FROM t1 WHERE c1 BETWEEN (SELECT id3 FROM t2 WHERE id2 = t1.id + 10) AND 100
--EXISTS
SELECT * FROM t1 WHERE EXISTS (SELECT c1 FROM t2 WHERE id2 > t1.id)


GROUP BY和HAVING

完成WHERE筛选后,生成的输出结果表可以继续使用GROUP BY子句进行分组,然后用HAVING子句删除一些分组行。示例如下:

# SELECT * FROM t1;
 id | value 
----+-------
  1 | t1a
  2 | t1b
  3 | t1c
  4 | t1c
  5 | t1b
(5 rows)

# SELECT value FROM t1 GROUP BY value;
 value 
-------
 t1c
 t1a
 t1b
(3 rows)

如果一个表被分了组,那么没有在分组中引用的字段都不能引用。如,上面的示例中只有分组字段value能在查询字段中引用。<、p>

分组查询一般需要配合聚合函数使用,以统计相关分组数据。示例如下:

# SELECT value, count(*) FROM t1 GROUP BY value;
 value | count 
-------+-------
 t1c   |     2
 t1a   |     1
 t1b   |     2
(3 rows)

上面的示例中统计了分组后相同value的个数。如果需要对分组结果进一步过滤,则使用HAVING。示例如下:

# SELECT value, count(*) FROM t1 GROUP BY value HAVING COUNT(*) > 1;
 value | count 
-------+-------
 t1c   |     2
 t1b   |     2
(2 rows)


结果排序

在一个查询生成一个输出表之后,可以对输出结果进行排序。如果没有选择排序,那么行将以未声明的顺序返回。 这时候的实际顺序将取决于扫描和连接规划类型和在磁盘上的顺序。PostgreSQL中的排序使用ORDER BY,语句格式如下:

SELECT select_list FROM table_expression ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...]

使用多个排序字段时,会优先按第1个字段排序,再使用第二个…。在PostgreSQL中,排序字段不用出现在查询字段中。示例如下:

# SELECT value FROM t1 ORDER BY id DESC, value ASC;
 value 
-------
 t1b
 t1c
 t1c
 t1b
 t1a
(5 rows)

作为对标准SQL的扩展,PostgreSQL还允许使用表达式进行排序:

SELECT a,b FROM t1 ORDER BY a+b;

LIMIT和OFFSET

在实际使用中,有时我们并只需要返回部分数据。如对查询结果分页显示时。这时我们LIMIT和OFFSET子句,语句格式如下:

SELECT select_list
	FROM table_expression
	[LIMIT { number | ALL }] [OFFSET number]

LIMIT指返回结果的条数,OFFSET说明在忽略多少行数据。示例如下:

# SELECT * FROM t1 ORDER BY id ASC LIMIT 2 OFFSET 1;
 id | value 
----+-------
  2 | t1b
  3 | t1c
(2 rows)


组合查询

两个查询的结果可以用集合操作并,交,差进行组合,语句格式如下:

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

示例如下:

# SELECT * FROM t1 UNION SELECT * FROM t2;
 id | value 
----+-------
  1 | t1a
  5 | t1b
  2 | t1b
  2 | bbb
  1 | aaa
  3 | t1c
  4 | t1c
  5 | ccc
(8 rows)

# SELECT * FROM t1 INTERSECT SELECT * FROM t2;
 id | value 
----+-------
(0 rows)

# SELECT * FROM t1 EXCEPT SELECT * FROM t2;
 id | value 
----+-------
  5 | t1b
  1 | t1a
  2 | t1b
  3 | t1c
  4 | t1c
(5 rows)