第7章 創(chuàng)建計算字段

7.1 計算字段

存儲在數(shù)據(jù)庫表中的數(shù)據(jù)一般不是應(yīng)用程序所需要的格式，下面舉幾個例子。

• 需要顯示公司名，同時還需要顯示公司的地址，但這兩個信息存儲在不同的表列中。
• 城市、州和郵政編碼存儲在不同的列中，但郵件標(biāo)簽打印程序需要把它們作為一個有恰當(dāng)格式的字段檢索出來。
• 列數(shù)據(jù)是大小寫混合的，但報表程序需要把所有數(shù)據(jù)按大寫表示出來。
• 物品訂單表存儲物品的價格和數(shù)量，不存儲每個物品的總價格。但為打印發(fā)票，需要物品的總價格。
• 需要根據(jù)表數(shù)據(jù)進行諸如總數(shù)、平均數(shù)的計算。

上述例子中，存儲在表中的數(shù)據(jù)都不是應(yīng)用程序所需要的。我們需要直接從數(shù)據(jù)庫中檢索出轉(zhuǎn)換、計算或格式化過的數(shù)據(jù)，而不是檢索出數(shù)據(jù)，然后再在客戶端應(yīng)用程序中重新格式化。

字段（field）
基本上與列（column）的意思相同，經(jīng)?；Q使用，不過數(shù)據(jù)庫列一般稱為列，而術(shù)語字段通常與計算字段一起使用。計算字段并不實際存在于數(shù)據(jù)庫表中，是運行時在 SELECT 語句內(nèi)創(chuàng)建的。

注意
只有數(shù)據(jù)庫知道 SELECT 語句中哪些列是實際的表列，哪些列是計算字段。從客戶端（如應(yīng)用程序）來看，計算字段的數(shù)據(jù)與其他列的數(shù)據(jù)的返回方式相同。

提示：客戶端與服務(wù)器的格式
在 SQL 語句內(nèi)可完成的許多轉(zhuǎn)換和格式化工作都可以直接在客戶端應(yīng)用程序內(nèi)完成。但一般來說，在數(shù)據(jù)庫服務(wù)器上完成這些操作比在客戶端中完成要快得多。

7.2 拼接字段

例子，創(chuàng)建由兩列組成的標(biāo)題。Vendors 表包含供應(yīng)商名和地址信息。假如要生成一個供應(yīng)商報表，需要在格式化的名稱（位置）中列出供應(yīng)商的位置。

此報表需要一個值，而表中數(shù)據(jù)存儲在兩個列 vend_name 和 vend_country 中。此外，需要用括號將 vend_country 括起來，這些東西都沒有存儲在數(shù)據(jù)庫表中。

拼接（concatenate）
將值聯(lián)結(jié)到一起（將一個值附加到另一個值）構(gòu)成單個值。
解決辦法：把兩個列拼接起來。在 SQL 中的 SELECT 語句中，用特殊操作符拼接兩個列。根據(jù) DBMS，此操作符可用加號（+）或兩個豎杠（||）表示。

• 在 MySQL 和 MariaDB 中，必須使用特殊的函數(shù)。
• Access 和 SQL Server 使用+號。
• DB2、Oracle、PostgreSQL、SQLite 和 Open Office Base 使用||。

下面是使用加號的例子（多數(shù) DBMS 使用這種語法）：

SELECT vend_name + '(' + vend_country + ')' FROM Vendors
ORDER BY vend_name;

屏幕快照 2018-05-27 13.25.53.png

下面是使用 MySQL 或 MariaDB 時需要使用的語句：

SELECT Concat(vend_name, '(', vend_country, ')') FROM Vendors
ORDER BY vend_name;

分析
上面兩個 SELECT 語句拼接以下元素：

• 存儲在 vend_name 列中的名字；
• 包含一個空格和一個左圓括號的字符串；
• 存儲在 vend_country 列中的國家；
• 包含一個右圓括號的字符串。

從輸出中看到，SELECT 語句返回包含上述四個元素的一個列（計算字段）。

上述 SELECT 語句返回的輸出，結(jié)合成一個計算字段的兩個列用空格填充。許多數(shù)據(jù)庫保存填充為列寬的文本值，而實際上要的結(jié)果不需要這些空格。為正確返回格式化的數(shù)據(jù)，必須去掉這些空格，使用 SQL 的 RTRIM()函數(shù)來完成：

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
FROM Vendors ORDER BY vend_name;

屏幕快照 2018-05-27 14.09.22.png

RTRIM()函數(shù)去掉值右邊的所有空格，通過使用 RTRIM()，各個列都進行了整理。

• RTRIM()：去掉字符串右邊的空格
• LTRIM()：去掉字符串左邊的空格
• TRIM()：去掉字符串左右兩邊的空格

使用別名
從輸出可以看到，SELECT 語句可以很好地拼接地址字段。但是，這個新計算列沒有名字，它只是一個值。
如果僅在 SQL 查詢工具中查看一下結(jié)果，這樣沒有什么不好。但是，一個未命名的列不能用于客戶端應(yīng)用中，因為客戶端沒有辦法引用它。

為了解決這個問題，SQL 支持列別名。別名（alias）是一個字段或值的替換名，別名用 AS 關(guān)鍵字賦予：

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
       AS vend_title
FROM Vendors ORDER BY vend_name;

屏幕快照 2018-05-27 14.20.09.png

下面是 MySQL 和 MariaDB 中使用的語句：

SELECT Concat(vend_name, '(', vend_cpuntry, ')')
       AS vend_title
FROM Vendors ORDER BY vend_name;

SELECT 語句的計算字段之后跟了文本 AS vend_title。它指示 SQL 創(chuàng)建一個包含指定計算結(jié)果的名為 vend_title 的計算字段。
從輸出看到，結(jié)果與以前的相同，但現(xiàn)在列名為 vend_title，任何客戶端應(yīng)用都可以按名稱引用這個列，就像它是一個實際的表列一樣。

在很多 DBMS 中，AS 關(guān)鍵字是可選的，不過最好使用它。
別名還有其他用途，包括在實際的表列名包含不合法的字符（如空格）時重新命名它，在原來的名字含混或容易誤解時擴充它。

7.3 執(zhí)行算數(shù)計算

計算字段的另一常見用途是對檢索出的數(shù)據(jù)進行算術(shù)計算。
例子，Orders 表包含收到的所有訂單，OrderItems 表包含每個訂單中的各項物品。下面的 SQL 語句檢索訂單號 20008 中的所有物品：

SELECT prod_id, quantity, item_price FROM OrderItems
WHERE order_num = 20008;

屏幕快照 2018-05-27 14.33.24.png

item_price 列包含訂單中每項物品的單價。如下匯總物品的價格（單價乘以訂購數(shù)量）：

SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM OrderItems WHERE order_num = 20008;

屏幕快照 2018-05-27 14.37.03.png

輸出中的 expanded_price 列是一個計算字段，此計算為quantity* item_price?？蛻舳藨?yīng)用現(xiàn)在可以使用這個新計算列，就像使用其他列一樣。

第8章 使用函數(shù)處理數(shù)據(jù)

8.1 函數(shù)

函數(shù)在數(shù)據(jù)上執(zhí)行，為數(shù)據(jù)的轉(zhuǎn)換和處理提供方便。但是 SQL 函數(shù)會帶來一些問題：
每一個 DBMS 都有特定的函數(shù)，只有少數(shù)幾個函數(shù)被所有主要的 DBMS 支持。雖然所有類型的函數(shù)一般都可以在每個 DBMS 中使用，但各個函數(shù)的名稱和語法可能極其不同。

為了說明可能存在的問題，列出 3 個常用的函數(shù)及其在各個 DBMS 中的語法：

屏幕快照 2018-05-27 14.44.38.png

8.2 使用函數(shù)

大多數(shù) SQL 實現(xiàn)支持以下類型的函數(shù)：

• 用于處理文本字符串（如刪除或填充值，轉(zhuǎn)換值為大寫或小寫）的文本函數(shù)。
• 用于在數(shù)值數(shù)據(jù)上進行算術(shù)操作（如返回絕對值，進行代數(shù)運算）的數(shù)值函數(shù)。
• 用于處理日期和時間值并從這些值中提取特定成分（如返回兩個日期之差，檢查日期有效性）的日期和時間函數(shù)。
• 返回 DBMS 正使用的特殊信息（如返回用戶登錄信息）的系統(tǒng)函數(shù)。

文本處理函數(shù)
UPPER()將文本轉(zhuǎn)換為大寫

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors ORDER BY vend_name;

屏幕快照 2018-05-27 16.59.53.png

表中的 SOUNDEX 需要注意，SOUNDEX 是一個將任何文本串轉(zhuǎn)換為描述其語音表示的字母數(shù)字模式的算法。SOUNDEX 考慮類似的發(fā)音字符和音節(jié)，使得能對字符串進行發(fā)音比較而不是字母比較。

日期和時間處理函數(shù)
日期和時間采用相應(yīng)的數(shù)據(jù)類型存儲在表中，每種 DBMS 都有自己的特殊形式。日期和時間值以特殊的格式存儲，以便能快速和有效地排序或過濾，并且節(jié)省物理存儲空間。

應(yīng)用程序一般不使用日期和時間的存儲格式，因此日期和時間函數(shù)總是用來讀取、統(tǒng)計和處理這些值。

Orders 表中的訂單都帶有訂單日期，在 SQL Server 中檢索2012年的所有訂單：

SELECT order_num FROM Orders
WHERE DATEPART(yy, order_date) = 2012;

屏幕快照 2018-05-27 17.50.16.png

SQL Server 使用 DATEPART() 函數(shù)，此函數(shù)返回日期的某一部分。DATEPART()函數(shù)有兩個參數(shù)，分別是返回的成分和從中返回成分的日期。
例子中，DATEPART()只從 order_date 列中返回年份。通過與2012比較，WHERE 子句只過濾出此年份的訂單。

PostgreSQL 版本的DATE_PART()函數(shù)：

SELECT order_num FROM Orders
WHERE DATE_PART('year', order_date) = 2012;

MySQL 和 MariaDB 具有各種日期處理函數(shù)，但沒有 DATEPART() 。MySQL 和 MariaDB 用戶可使用名為YEAR()的函數(shù)從日期中提取年份：

SELECT order_num FROM Orders
WHERE YEAR(order_date) = 2012;

這里給出的例子提取和使用日期的成分（年）。按月份過濾，可以進行相同的處理，使用 AND 操作符可以進行年和月份的比較。

大多數(shù) DBMS 具有比較日期、執(zhí)行基于日期的運算、選擇日期格式等的函數(shù)。但是，不同 DBMS 的日期-時間處理函數(shù)可能不同。

數(shù)值處理函數(shù)
數(shù)值處理函數(shù)僅處理數(shù)值數(shù)據(jù)，用于代數(shù)、三角或幾何運算，因此不像字符串或日期-時間處理函數(shù)使用那么頻繁。