pg学习_数据查询

数据查询

1、select命令语法
highgo=# \h select
Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

2、select子句的使用
查询全部字段
highgo=# select * from test;
 no | name 
----+------
  1 | adam
(1 row)
查询指定字段
highgo=# select no from test;
 no 
----
  1
(1 row)
查询指定字段并改变字段标示
highgo=# select name as 姓名 from test;
 姓名 
------
 adam
(1 row)

3、where子句的使用
highgo=# select * from test where name ='adam';
 no | name 
----+------
  1 | adam
(1 row)

4、order by子句的使用
highgo=# select * from test order by no;
 no |  name  
----+--------
  1 | adam
  2 | lilith
  3 | eva
(3 rows)

highgo=# select * from test order by no asc;
 no |  name  
----+--------
  1 | adam
  2 | lilith
  3 | eva
(3 rows)

highgo=# select * from test order by no desc;
 no |  name  
----+--------
  3 | eva
  2 | lilith
  1 | adam
(3 rows)

5、limit子句的使用
Limit 语句在编写分页程序的时候会经常被用到。
select * from test limit n;表示查询表 test 中的所有字段，但只输出前 n 行的值。
highgo=# select * from test limit 2;
 no |  name  
----+--------
  1 | adam
  2 | lilith
(2 rows)
limit m offset n 表示只输出第 n 行后面的 m 行数值。
highgo=# select * from test limit 2 offset 1;
 no |  name  
----+--------
  2 | lilith
  3 | eva
(2 rows)

6、group by子句的使用
highgo=# select * from test;
 no |  name  
----+--------
  1 | adam
  2 | lilith
  3 | eva
  1 | lilith
  1 | eva
  2 | adam
  3 | lilith
(7 rows)
highgo=# select no,count(*) from test group by no;
 no | count 
----+-------
  1 |     3
  3 |     2
  2 |     2
(3 rows)
注意:如果 select 目标列表中出现了一个聚集表达式，那么其他目标要么是另外的聚集 表达式，要么是 group by 子句中的字段，否则会导致语法错误。

7、having子句的使用
如果要在查询中对聚集结果进行条件判断和控制，可以使用 having 子句。
highgo=# select no,count(*) from test group by no having no <3;
 no | count 
----+-------
  1 |     3
  2 |     2
(2 rows)

8、复杂条件查询
And 逻辑和
Or 逻辑或
Not 逻辑非
Like 模糊查询 编程时候尽量少用，查询速度较慢 
In 范围查询 编程时候尽量少用，查询速度较慢 
Between and 范围查询 包含临界值
highgo=# select * from test where no=1 and name='adam';
 no | name 
----+------
  1 | adam
(1 row)

highgo=# select * from test where no=1 or name='adam';
 no |  name  
----+--------
  1 | adam
  1 | lilith
  1 | eva
  2 | adam
(4 rows)

highgo=# select * from test where not no=1;
 no |  name  
----+--------
  2 | lilith
  3 | eva
  2 | adam
  3 | lilith
(4 rows)

highgo=# select * from test where name like '%a%';
 no | name 
----+------
  1 | adam
  3 | eva
  1 | eva
  2 | adam
(4 rows)

highgo=# select * from test where no in (1,2);
 no |  name  
----+--------
  1 | adam
  2 | lilith
  1 | lilith
  1 | eva
  2 | adam
(5 rows)

highgo=# select * from test where no between 1 and 2;
 no |  name  
----+--------
  1 | adam
  2 | lilith
  1 | lilith
  1 | eva
  2 | adam
(5 rows)

9、规则表达式查询
编程开发，最好不要使用规则表达式查询，避免引起错乱字符。
^ 以指定的字符串开头
$ 以指定的字符串结束
highgo=# select * from test where name ~'^a';
 no | name 
----+------
  1 | adam
  2 | adam
(2 rows)

highgo=# select * from test where name ~'a$';
 no | name 
----+------
  3 | eva
  1 | eva
(2 rows)