4. 使用函数处理数据

本节使用的表

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)

orders

postgres=# \d orders

                    Table "public.orders"
   2Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_num  | integer       |           | not null |
 order_date | date          |           | not null |
 cust_id    | character(10) |           | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_num)
Foreign-key constraints:
    "fk_orders_customers" FOREIGN KEY (cust_id) REFERENCES customers(cust_id)
Referenced by:
    TABLE "orderitems" CONSTRAINT "fk_orderitems_orders" FOREIGN KEY (order_num) REFERENCES orders(order_num)

SELECT
    *
FROM
    orders;

 order_num | order_date |  cust_id
-----------+------------+------------
     20005 | 2012-05-01 | 1000000001
     20006 | 2012-01-12 | 1000000003
     20007 | 2012-01-30 | 1000000004
     20008 | 2012-02-03 | 1000000005
     20009 | 2012-02-08 | 1000000001
(5 rows)

文本处理函数

常用的文本处理函数：

函数	说明	备注
LEFT()	返回字符串左边的字符	或使用子字符串函数
LENGTH()	返回字符串的长度	也使用DATALENGTH()或len()
LOWER()	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()	返回字符串右边的字符	或使用子字符串函数
RTRIM()	去掉字符串右边的空格
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()	将字符串转换为大写

使用UPPER()函数：

SELECT
    vend_name,
    upper(vend_name) AS vend_name_upcase 
FROM
    vendors 
ORDER BY
    vend_name;

                     vend_name                      | vend_name_upcase
----------------------------------------------------+------------------
 Bear Emporium                                      | BEAR EMPORIUM
 Bears R Us                                         | BEARS R US
 Doll House Inc.                                    | DOLL HOUSE INC.
 Fun and Games                                      | FUN AND GAMES
 Furball Inc.                                       | FURBALL INC.
 Jouets et ours                                     | JOUETS ET OURS
(6 rows)

日期和时间处理函数

SELECT
    vend_name,
    upper(vend_name) AS vend_name_upcase 
FROM
    vendors 
ORDER BY
    vend_name;

 order_num | order_date
-----------+------------
     20005 | 2012-05-01
     20006 | 2012-01-12
     20007 | 2012-01-30
     20008 | 2012-02-03
     20009 | 2012-02-08
(5 rows)

数值处理函数

函数	说明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率
SIN()	返回一个角度的正弦
SQRT()	返回一个数的平方根
TAN()	返回一个角度的正切