本节使用的表
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)