本节使用的表
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)
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)
聚集函数
| 函数 | 说明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
AVG()函数
AVG()函数忽略列值为NULL的行。
SELECT
AVG(prod_price) AS avg_price
FROM
products
WHERE
vend_id = 'DLL01';
avg_price
--------------------
3.8650000000000000
(1 row)
COUNT()函数
COUNT()函数有两种使用方式：
COUNT(*)不管表列中包含的是空值还是非空值；COUNT(column)对特定列具有值的行进行计数，忽略NULL值。
SELECT
COUNT(*) AS num_cust
FROM
customers;
num_cust
----------
5
(1 row)
SELECT
COUNT(cust_email) AS num_cast
FROM
customers;
num_cast
----------
3
(1 row)
MAX()和MIN()函数
MAX()和MIN()一般用来找出数值或日期值。但许多（并非所有）DBMS允许用来返回任意列中的最大值或最小值。
在用于文本数据时，返回该列排序后的最后一行或最前面一行。忽略值为NULL的行。
SUM()函数
SUM()函数忽略列值为NULL的行。
SELECT
item_price,
quantity
FROM
orderitems
WHERE
order_num = 20005;
item_price | quantity
------------+----------
5.49 | 100
10.99 | 100
(2 rows)
SELECT
SUM(quantity) AS items_ordered
FROM
orderitems
WHERE
order_num = 20005;
items_ordered
---------------
200
(1 row)
SELECT
SUM(item_price * quantity) AS total_price
FROM
orderitems
WHERE
order_num = 20005;
total_price
-------------
1648.00
(1 row)
聚集不同值
DISTINCT只能用于COUNT()，不能用于COUNT(*)。
DISTINCT必须使用列名，不能用于计算或表达式。
SELECT
prod_price
FROM
products
WHERE
vend_id = 'DLL01';
prod_price
------------
3.49
3.49
3.49
4.99
(4 rows)
SELECT
AVG(prod_price) AS avg_price
FROM
products
WHERE
vend_id = 'DLL01';
avg_price
--------------------
3.8650000000000000
(1 row)
SELECT
AVG(DISTINCT prod_price) AS avg_price
FROM
products
WHERE
vend_id = 'DLL01';
avg_price
--------------------
4.2400000000000000
(1 row)
组合聚集函数
SELECT
prod_price
FROM
products;
prod_price
------------
5.99
8.99
11.99
3.49
3.49
3.49
4.99
9.49
9.49
(9 rows)
SELECT
COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM
products;
num_items | price_min | price_max | price_avg
-----------+-----------+-----------+--------------------
9 | 3.49 | 11.99 | 6.8233333333333333
(1 row)
扫一扫
724
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
