1. 检索数据

本节使用的表

products

postgres=# \d products

                        Table "public.products"
   Column   |          Type           | Collation | Nullable | Default
------------+-------------------------+-----------+----------+---------
 prod_id    | character(10)           |           | not null |
 vend_id    | character(10)           |           | not null |
 prod_name  | character(255)          |           | not null |
 prod_price | numeric(8,2)            |           | not null |
 prod_desc  | character varying(1000) |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (prod_id)
Foreign-key constraints:
    "fk_products_vendors" FOREIGN KEY (vend_id) REFERENCES vendors(vend_id)
Referenced by:
    TABLE "orderitems" CONSTRAINT "fk_orderitems_products" FOREIGN KEY (prod_id) REFERENCES products(prod_id)

SELECT
    * 
FROM
    products;

  prod_id   |  vend_id   |  prod_name         | prod_price |       prod_desc
------------+------------+--------------------+------------+------------------------------
 BR01       | BRS01      | 8 inch teddy bear  |       5.99 | 8 inch teddy bear, comes with cap and jacket
 BR02       | BRS01      | 12 inch teddy bear |       8.99 | 12 inch teddy bear, comes with cap and jacket
 BR03       | BRS01      | 18 inch teddy bear |      11.99 | 18 inch teddy bear, comes with cap and jacket
 BNBG01     | DLL01      | Fish bean bag toy  |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it
 BNBG02     | DLL01      | Bird bean bag toy  |       3.49 | Bird bean bag toy, eggs are notincluded
 BNBG03     | DLL01      | Rabbit bean bag toy|       3.49 | Rabbit bean bag toy, comes withbean bag carrots
 RGAN01     | DLL01      | Raggedy Ann        |       4.99 | 18 inch Raggedy Ann doll
 RYL01      | FNG01      | King doll          |       9.49 | 12 inch king doll with royal garments and crown
 RYL02      | FNG01      | Queen doll         |       9.49 | 12 inch queen doll with royal garments and crown
(9 rows)

检索不同值

SELECT 
    vend_id 
FROM 
    products;

  vend_id 
------------
 BRS01
 BRS01
 BRS01
 DLL01
 DLL01
 DLL01
 DLL01
 FNG01
 FNG01
(9 rows)

SELECT
    DISTINCT vend_id
FROM
    products;

  vend_id 
------------ 
 FNG01 
 BRS01 
 DLL01 
(3 rows)

限制结果

SELECT
    prod_name
FROM
    products;

  prod_name
-------------------------------
 8 inch teddy bear
 12 inch teddy bear
 18 inch teddy bear
 Fish bean bag toy
 Bird bean bag toy
 Rabbit bean bag toy
 Raggedy Ann
 King doll
 Queen doll
(9 rows)

SELECT
    prod_name
FROM
    products
LIMIT 5;

  prod_name 
----------------------------------------------
 8 inch teddy bear 
 12 inch teddy bear  
 18 inch teddy bear  
 Fish bean bag toy  
 Bird bean bag toy 
(5 rows)

SELECT
    prod_name
FROM
    products
LIMIT 5 OFFSET 5;

  prod_name 
------------------------------------------------- 
 Rabbit bean bag toy  
 Raggedy Ann  
 King doll  
 Queen doll 
(4 rows)

排序

仅在多个行具有相同的prod_price时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

SELECT
    prod_id,
    prod_price,
    prod_name
FROM
    products
ORDER BY
    prod_price,
    prod_name;  

  prod_id   | prod_price | prod_name
------------+------------+-----------------------------------------------
 BNBG02     |       3.49 | Bird bean bag toy
 BNBG01     |       3.49 | Fish bean bag toy
 BNBG03     |       3.49 | Rabbit bean bag toy
 RGAN01     |       4.99 | Raggedy Ann
 BR01       |       5.99 | 8 inch teddy bear
 BR02       |       8.99 | 12 inch teddy bear
 RYL01      |       9.49 | King doll
 RYL02      |       9.49 | Queen doll
 BR03       |      11.99 | 18 inch teddy bear
(9 rows)