創(chuàng)建計(jì)算字段

計(jì)算字段

存儲(chǔ)在數(shù)據(jù)庫(kù)表中的數(shù)據(jù)不一定是應(yīng)用程序需要的數(shù)據(jù)，我們需要直接從數(shù)據(jù)庫(kù)中檢索出轉(zhuǎn)換续语、計(jì)算或格式化過(guò)的數(shù)據(jù)，而不是檢索出數(shù)據(jù)，然后再在客戶端應(yīng)用程序中重新格式化

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

拼接字段

假如要生成一個(gè)供應(yīng)商報(bào)表玷禽，需要在格式化的名稱vend_name中 列出供應(yīng)商的位置赫段，則需要將vend_name字段和vend_country字段拼接組合起來(lái)
在MySQL中用法如下：

select concat(Rtrim(vend_name), ' (', Rtrim(vend_country), ')')
from vendors
order by vend_name;

結(jié)果如下圖：

圖7-1

字符拼接在不同的DBMS中實(shí)現(xiàn)不盡相同：SQL server使用 + 號(hào)糯笙，DB2、Oracle撩银、postgreSQL和SQLite使用 ||给涕，MySQL和MariaDB使用Concat()函數(shù)

使用別名
從圖7-1可以看到,select語(yǔ)句可以很好地拼接地址字段。但是额获，這個(gè)新計(jì)算列實(shí)際上沒(méi)有名字够庙，它只是一個(gè)值。如果僅在SQL查詢工具中查看一下結(jié)果抄邀，這樣沒(méi)有什么不好耘眨。但是，一個(gè)未命名的列不能用于客戶端應(yīng)用中境肾，因?yàn)?strong>客戶端沒(méi)有辦法引用它剔难。
這里我們可以使用列別名。別名（alias）是一個(gè)字段或值的替換名奥喻，用 as 關(guān)鍵字賦予偶宫。在MySQL代碼中添加列別名：

select concat(Rtrim(vend_name), ' (', Rtrim(vend_country), ')')
as vend_title
from vendors
order by vend_name;

結(jié)果圖如下：

圖7-2

別名最常見的使用是將多個(gè)單詞的列名重命名為一個(gè)單詞的名字楔绞，而不是多個(gè)單詞組合的字符串（盡管多單詞的名字可讀性高结闸，但這會(huì)給客戶端應(yīng)用帶來(lái)各種問(wèn)題）

提示：如何測(cè)試計(jì)算
select語(yǔ)句為測(cè)試、檢驗(yàn)函數(shù)和計(jì)算提供了很好的方法酒朵。雖然select通常用于從表中檢索數(shù)據(jù)桦锄，但是省略了from子句后就是簡(jiǎn)單的訪問(wèn)和處理表達(dá)式，例如 select 3*2 將返回6蔫耽，select trim(' abc ') 將返回abc结耀，select curdate() 將返回當(dāng)前日期和時(shí)間留夜。

我們可以根據(jù)需要使用select語(yǔ)句進(jìn)行檢驗(yàn)

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

SQL函數(shù)

與大多數(shù)其他計(jì)算機(jī)語(yǔ)言一樣，SQL也可以用函數(shù)來(lái)處理數(shù)據(jù)图甜。函數(shù)一般是在數(shù)據(jù)上執(zhí)行的碍粥，為數(shù)據(jù)的轉(zhuǎn)換和處理提供了方便。

不過(guò)使用SQL函數(shù)存在一些問(wèn)題：不同于DBMS等同地支持SQL語(yǔ)句(如select)黑毅，每一個(gè)DBMS都有其特定的函數(shù)嚼摩。事實(shí)上，只有少數(shù)幾個(gè)函數(shù)被所有主要的DBMS等同地支持矿瘦。雖然所有類型的函數(shù)一般都可以在每個(gè)DBMS中使用枕面，但各個(gè)函數(shù)的名稱和用法可能及其不同

所以，與SQL語(yǔ)句不同缚去，SQL函數(shù)不是可移植的潮秘。這意味著為特定SQL實(shí)現(xiàn)編寫的代碼在其他實(shí)現(xiàn)中可能不能用易结。

為了代碼的可移植枕荞，許多SQL程序員不贊成使用特定于實(shí)現(xiàn)的功能。雖然這樣做很有好處衬衬，但有時(shí)候并不利于應(yīng)用程序的性能买猖。如果不使用這些函數(shù)，編寫某些應(yīng)用程序代碼會(huì)很難滋尉。必須利用其他方法來(lái)實(shí)現(xiàn)DBMS可以非常有效完成的工作

提示：是否應(yīng)該使用函數(shù)玉控？
現(xiàn)在，你面臨是否應(yīng)該使用函數(shù)的選擇狮惜。決定權(quán)在你高诺，使用或是不使用也沒(méi)有對(duì)錯(cuò)之分。如果你決定使用函數(shù)碾篡，應(yīng)該確保做好代碼注釋虱而，以便以后你自己（或其他人）能夠確切地知道這些SQL代碼的含義

soundex()函數(shù)：返回字符串的soundex值，是一個(gè)將任何文本串轉(zhuǎn)換為描述其語(yǔ)音表示的字母數(shù)字模式的算法开泽。soundex考慮了類似的發(fā)音字符和音節(jié)牡拇，使得能對(duì)字符串進(jìn)行發(fā)音比較而不是字母比較。雖然soundex不是SQL概念穆律，但多數(shù)DBMS都提供對(duì)soundex的支持惠呼。

其使用如下：
customers表中有一個(gè)顧客 kids Place，其聯(lián)系名為Michelle Green峦耘。但是如果這是錯(cuò)誤的輸入剔蹋，此聯(lián)系名實(shí)際上應(yīng)該是Michael Green 那該怎么辦？顯然按正確的聯(lián)系名搜索不會(huì)返回?cái)?shù)據(jù)辅髓，我們可以使用soundex()函數(shù)進(jìn)行搜索泣崩，它匹配所有發(fā)音類似于Michael Green的聯(lián)系名：

select cust_name, cust_contact
from customers
where soundex(cust_contact) = soundex('Michael Green');

結(jié)果如下圖：

圖8-1

匯總數(shù)據(jù)

聚集數(shù)據(jù)

AVG()函數(shù)
通過(guò)對(duì)表中行數(shù)計(jì)數(shù)并計(jì)算其列值之和少梁，求得該列的平均值
注意：AVG()函數(shù)只能用來(lái)確定特定數(shù)值列的平均值，而且列名必須作為函數(shù)參數(shù)給出矫付。為了獲取多個(gè)列的平均值凯沪，必須使用多個(gè)AVG()函數(shù)。*****************
說(shuō)明：AVG()函數(shù)忽略值為NULL的行买优。

COUNT()函數(shù)
用來(lái)確定表中行的數(shù)目或符合特定條件的行的數(shù)目著洼。
有兩種使用方式：

• 使用 count(*) 對(duì)表中行的數(shù)目進(jìn)行計(jì)數(shù)，不管表列中包含的是空值（NULL）還是非空值

select count(*)
as num_cust 
from Customers;

圖9-1

• 使用 count(column) 對(duì)特定列中具有值的行進(jìn)行計(jì)數(shù)而叼，忽略NULL值

select count(cust_email)
as num_cust 
from Customers;

圖9-2

SUM()函數(shù)
SUM()用來(lái)返回指定列值的和（總計(jì)）
也可以用來(lái)合計(jì)計(jì)算值。

select sum(item_price*quantity) as total_price
from orderitems
where order_num=20005;

圖9-3

如上例所示豹悬，利用標(biāo)準(zhǔn)的算術(shù)操作符葵陵，所有聚集函數(shù)都可用來(lái)執(zhí)行多個(gè)列上的計(jì)算

聚集不同值

聚集函數(shù)可以如下使用：

• 對(duì)所有行執(zhí)行計(jì)算，指定 all 參數(shù)或不指定參數(shù)（因?yàn)閍ll是默認(rèn)行為）
• 只包含不同的值瞻佛，指定 distinct 參數(shù)

select avg(distinct prod_price) as avg_dist_price
from products
where vend_id='DLL01';

圖9-4

在使用了 distinct 之后脱篙，結(jié)果中平均價(jià)格比較高。因?yàn)橛卸鄠€(gè)物品具有相同的較低價(jià)格伤柄。排除它們提升了平均價(jià)格绊困。

注意：DISTINCT不能用于COUNT(*)
如果指定列名，則DISTINCT只能用于COUNT()适刀。DISTINCT不能用于COUNT(*)秤朗。類似地，DISTINCT必須使用列名笔喉，不能用于計(jì)算或表達(dá)式取视。

分組數(shù)據(jù)

創(chuàng)建分組

分組是使用select語(yǔ)句的 group by 子句建立的

select vend_id, count(*) as num_prods
from products
group by vend_id;

結(jié)果如圖：

圖10-1

過(guò)濾分組

SQL允許過(guò)濾分組锐秦，規(guī)定包括哪些分組，排除哪些分組那先。例如农猬，想要列出至少有兩個(gè)訂單的所有顧客。為此售淡，必須基于完整的分組而不是個(gè)別的行進(jìn)行過(guò)濾斤葱。

我們已經(jīng)知道了where子句的作用慷垮。但是，在這個(gè)例子中where不能完成任務(wù)揍堕，因?yàn)閣here過(guò)濾指定的是行而不是分組料身。事實(shí)上，where沒(méi)有分組的概念衩茸。

那么芹血，不使用where使用什么呢？SQL為此提供了另一個(gè)子句：having 子句楞慈。

having非常類似于where幔烛。事實(shí)上，目前為止所學(xué)過(guò)的所有類型的where子句都可以用having來(lái)替代囊蓝。唯一的差別就是饿悬，where過(guò)濾行，而having過(guò)濾分組聚霜。

提示：having支持所有的where操作符狡恬。它們的句法是相同的，只是關(guān)鍵字有差別蝎宇。

過(guò)濾分組：

select cust_id, count(*) as orders
from orders
group by cust_id
having count(*)>=2;

圖10-2

最后一行的having子句弟劲，它過(guò)濾count(*)>=2（兩個(gè)或以上訂單）的那些分組几蜻。

說(shuō)明：having和where的差別
這里有另一種理解方法钦扭，where在數(shù)據(jù)分組前進(jìn)行過(guò)濾，having在數(shù)據(jù)分組后進(jìn)行過(guò)濾凄敢。這是一個(gè)重要的區(qū)別凉唐，where排除的行不包括在分組中报嵌。這可能會(huì)改變計(jì)算值，從而影響having子句中基于這些值過(guò)濾掉的分組熊榛。

考慮需要在一條語(yǔ)句中同時(shí)使用where和having子句的情況：返回過(guò)去12個(gè)月內(nèi)具有兩個(gè)以上訂單的顧客锚国。為此，可以增加一條where子句玄坦，過(guò)濾出過(guò)去12個(gè)月內(nèi)下過(guò)的訂單血筑，然后再增加having子句過(guò)濾出具有兩個(gè)以上訂單的分組。

為了更好理解煎楣，考慮下面的例子：它列出具有兩個(gè)以上產(chǎn)品且其價(jià)格大于等于4的供應(yīng)商：

select vend_id, count(*) as num_prods
from products
where prod_price>=4
group by vend_id
having count(*)>=2;

圖10-3

where子句過(guò)濾所有prod_price至少為4的行豺总，然后按vend_id分組數(shù)據(jù)，having子句過(guò)濾計(jì)數(shù)為2或以上的分組择懂。如果沒(méi)有where子句喻喳，就會(huì)多檢索出一行（供應(yīng)商DLL01，銷售4個(gè)產(chǎn)品困曙，價(jià)格都在4以下）：

select vend_id, count(*) as num_prods
from products
group by vend_id
having count(*)>=2;

圖10-4

使用 having 時(shí)應(yīng)該結(jié)合 group by 子句表伦，而 where 子句用于標(biāo)準(zhǔn)的行級(jí)過(guò)濾谦去。

你以某種方式分組數(shù)據(jù)（獲得特定的分組聚集值），并不表示你需要以相同的方式排序輸出蹦哼。應(yīng)該提供明確的 order by 子句鳄哭，即使效果等同于 group by 子句。

一般在使用 group by 子句時(shí)纲熏，應(yīng)該也給出 order by 子句妆丘。這是保證數(shù)據(jù)正確排序的唯一方法。千萬(wàn)不要僅依賴group by排序數(shù)據(jù)局劲。