2. 过滤数据

本节使用的表

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)

customers

postgres=# \d customers

                    Table "public.customers"
    Column    |      Type      | Collation | Nullable | Default
--------------+----------------+-----------+----------+---------
 cust_id      | character(10)  |           | not null |
 cust_name    | character(50)  |           | not null |
 cust_address | character(50)  |           |          |
 cust_city    | character(50)  |           |          |
 cust_state   | character(5)   |           |          |
 cust_zip     | character(10)  |           |          |
 cust_country | character(50)  |           |          |
 cust_contact | character(50)  |           |          |
 cust_email   | character(255) |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (cust_id)
Referenced by:
    TABLE "orders" CONSTRAINT "fk_orders_customers" FOREIGN KEY (cust_id) REFERENCES customers(cust_id)

SELECT
    *
FROM
    customers;

 cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact
   | cust_email
------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------
---+----------------------------
 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith
   | sales@villagetoys.com
 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green
   |
 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones
   | jjones@fun4all.com
 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens
   | dstephens@fun4all.com
 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard
   |
(5 rows)

空值检查

SELECT
    cust_name,
    cust_email
FROM
    customers
WHERE
    cust_email IS NULL;

                     cust_name                      | cust_email
----------------------------------------------------+------------
 Kids Place                                         |
 The Toy Store                                      |
(2 rows)

组合where子句

SELECT
    prod_price,
    prod_name
FROM
    products
WHERE
    (
        vend_id = 'DLL01'
        OR vend_id = 'BRS01'
    )
    AND prod_price >= 10;

 prod_price | prod_name
------------+------------------------------
      11.99 | 18 inch teddy bear
(1 row)

IN操作符

SELECT
    prod_name,
    prod_price,
    vend_id 
FROM
    products 
WHERE
    vend_id IN ('DLL01', 'BRS01') 
ORDER BY
    prod_name;

  prod_name          | prod_price | vend_id
---------------------+------------+------------
 12 inch teddy bear  |       8.99 | BRS01
 18 inch teddy bear  |      11.99 | BRS01
 8 inch teddy bear   |       5.99 | BRS01
 Bird bean bag toy   |       3.49 | DLL01
 Fish bean bag toy   |       3.49 | DLL01
 Rabbit bean bag toy |       3.49 | DLL01
 Raggedy Ann         |       4.99 | DLL01
(7 rows)

NOT操作符

NOT用来否定其后的条件。

SELECT
    prod_name,
    vend_id 
FROM
    products 
WHERE
    NOT vend_id = 'DLL01' 
ORDER BY
    prod_name;

  prod_name        |  vend_id
-------------------+------------
 12 inch teddy bear| BRS01
 18 inch teddy bear| BRS01
 8 inch teddy bear | BRS01
 King doll         | FNG01
 Queen doll        | FNG01
(5 rows)

通配符

在需要使用通配符时，尽量不要用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。

SELECT
    prod_id,
    prod_name 
FROM
    products 
WHERE
    prod_name LIKE 'F%y';

  prod_id | prod_name
---------+-----------
(0 rows)

如果某列有50个字符，而存储的文本为Fish bean bag toy（17个字符），则为填满该列，需要在文本后附加33个空格。

简单的办法就是给搜索模式再增加一个%：'F%y%'还匹配y之后的字符（或空格）。

SELECT
    prod_id,
    prod_name 
FROM
    products 
WHERE
    prod_name LIKE 'F%y%';

  prod_id   | prod_name
------------+------------------------
 BNBG01     | Fish bean bag toy
(1 row)