本节使用的表
vendors
postgres=# \d vendors
Table "public.vendors"
Column | Type | Collation | Nullable | Default
--------------+---------------+-----------+----------+---------
vend_id | character(10) | | not null |
vend_name | character(50) | | not null |
vend_address | character(50) | | |
vend_city | character(50) | | |
vend_state | character(5) | | |
vend_zip | character(10) | | |
vend_country | character(50) | | |
Indexes:
"vendors_pkey" PRIMARY KEY, btree (vend_id)
Referenced by:
TABLE "products" CONSTRAINT "fk_products_vendors" FOREIGN KEY (vend_id) REFERENCES vendors(vend_id)
SELECT
*
FROM
vendors;
vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country
---------+---------------+-------------------+------------+------------+----------+------------------
BRS01 | Bears R Us | 123 Main Street | Bear Town | MI | 44444 | USA
BRE02 | Bear Emporium | 500 Park Street | Anytown | OH | 44333 | USA
DLL01 | Doll House Inc. | 555 High Street | Dollsville | CA | 99999 | USA
FRB01 | Furball Inc. | 1000 5th Avenue | New York | NY | 11111 | USA
FNG01 | Fun and Games | 42 Galaxy Road | London | | N16 6PS | England
JTS01 | Jouets et ours | 1 Rue Amusement | Paris | | 45678 | France
(6 rows)
orderitems
postgres=# \d orderitems
Table "public.orderitems"
Column | Type | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
order_num | integer | | not null |
order_item | integer | | not null |
prod_id | character(10) | | not null |
quantity | integer | | not null |
item_price | numeric(8,2) | | not null |
Indexes:
"orderitems_pkey" PRIMARY KEY, btree (order_num, order_item)
Foreign-key constraints:
"fk_orderitems_orders" FOREIGN KEY (order_num) REFERENCES orders(order_num)
"fk_orderitems_products" FOREIGN KEY (prod_id) REFERENCES products(prod_id)
SELECT
*
FROM
orderitems;
order_num | order_item | prod_id | quantity | item_price
-----------+------------+------------+----------+------------
20005 | 1 | BR01 | 100 | 5.49
20005 | 2 | BR03 | 100 | 10.99
20006 | 1 | BR01 | 20 | 5.99
20006 | 2 | BR02 | 10 | 8.99
20006 | 3 | BR03 | 10 | 11.99
20007 | 1 | BR03 | 50 | 11.49
20007 | 2 | BNBG01 | 100 | 2.99
20007 | 3 | BNBG02 | 100 | 2.99
20007 | 4 | BNBG03 | 100 | 2.99
20007 | 5 | RGAN01 | 50 | 4.49
20008 | 1 | RGAN01 | 5 | 4.99
20008 | 2 | BR03 | 5 | 11.99
20008 | 3 | BNBG01 | 10 | 3.49
20008 | 4 | BNBG02 | 10 | 3.49
20008 | 5 | BNBG03 | 10 | 3.49
20009 | 1 | BNBG01 | 250 | 2.49
20009 | 2 | BNBG02 | 250 | 2.49
20009 | 3 | BNBG03 | 250 | 2.49
(18 rows)
拼接字段
SELECT
vend_name || ' (' || vend_country || ' )'
FROM
vendors
ORDER BY
vend_name;
?column?
--------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)
(6 rows)
这个新计算列没有名字,它只是一个值。一个未命名的列不能用于客户端应用中,因为客户端没有办法引用它。
为了解决这个问题,SQL支持列别名。
SELECT
vend_name || ' (' || vend_country || ' )' AS vendor_title
FROM
vendors
ORDER BY
vend_name;
vendor_title
-------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)
(6 rows)
执行算术计算
SELECT
prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price
FROM
orderitems
WHERE
order_num = 20008;
prod_id | quantity | item_price | expanded_price
------------+----------+------------+----------------
RGAN01 | 5 | 4.99 | 24.95
BR03 | 5 | 11.99 | 59.95
BNBG01 | 10 | 3.49 | 34.90
BNBG02 | 10 | 3.49 | 34.90
BNBG03 | 10 | 3.49 | 34.90
(5 rows)