PostgreSQL基础教程之检索

悠悠 2024-03-25 12:44 35阅读 0赞

### postgresql数据检索 ###

##### 一个子查询也可以是一个VALUES列表 #####

select *
    FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
       AS names(first, last)

##### 表连接查询 #####

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
    USING ( join column list )  // USING 是 on 的简写  USING (a, b) 等于 ON T1.a = T2.a AND T1.b = T2.b。
    NATURAL JOIN T2             // NATURAL是 USING 的简写，两个表里都出现了的列名组成

##### T1表 #####

num | name
    -----+------
       1 | a
       2 | b
       3 | c

##### T2表 #####

num | value
    -----+-------
       1 | xxx
       3 | yyy
       5 | zzz

=> SELECT * FROM t1 CROSS JOIN t2;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       1 | a    |   3 | yyy
       1 | a    |   5 | zzz
       2 | b    |   1 | xxx
       2 | b    |   3 | yyy
       2 | b    |   5 | zzz
       3 | c    |   1 | xxx
       3 | c    |   3 | yyy
       3 | c    |   5 | zzz
    (9 rows)
    
    => SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       3 | c    |   3 | yyy
    (2 rows)
    
    => SELECT * FROM t1 INNER JOIN t2 USING (num);
     num | name | value
    -----+------+-------
       1 | a    | xxx
       3 | c    | yyy
    (2 rows)
    
    => SELECT * FROM t1 NATURAL INNER JOIN t2;
     num | name | value
    -----+------+-------
       1 | a    | xxx
       3 | c    | yyy
    (2 rows)
    
    => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       2 | b    |     |
       3 | c    |   3 | yyy
    (3 rows)
    
    => SELECT * FROM t1 LEFT JOIN t2 USING (num);
     num | name | value
    -----+------+-------
       1 | a    | xxx
       2 | b    |
       3 | c    | yyy
    (3 rows)
    
    => SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       3 | c    |   3 | yyy
         |      |   5 | zzz
    (3 rows)
    
    => SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       2 | b    |     |
       3 | c    |   3 | yyy
         |      |   5 | zzz
    (4 rows)

##### 一些常用的WHERE子句例子 #####

SELECT ... FROM fdt WHERE c1 > 5
    SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
    SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
    SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
    SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
    SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

##### GROUP BY例子 #####

=> SELECT * FROM test1;
     x | y
    ---+---
     a | 3
     c | 2
     b | 5
     a | 1
    (4 rows)
    
    => SELECT x FROM test1 GROUP BY x;
     x
    ---
     a
     b
     c
    (3 rows)
    
    => SELECT x, sum(y) FROM test1 GROUP BY x;
     x | sum
    ---+-----
     a |   4
     b |   5
     c |   2
    (3 rows)
    
    => SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
     x | sum
    ---+-----
     a |   4
     b |   5
    (2 rows)
    
    => SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
     x | sum
    ---+-----
     a |   4
     b |   5
    (2 rows)