MySQL必知應會-第11章-使用資料處理函數

標籤：2.3   運算   系統   利用   運行   完整   tween   分享圖片   c11   

-第11章使用資料處理函數">第11章 使用資料處理函數

本章介紹什麼是函數， MySQL支援何種函數，以及如何使用這些函數。

-11.1函數">11.1 函數

與其他大多數電腦語言一樣， SQL支援利用函數來處理資料。函數一般是在資料上執行的，它給資料的轉換和處理提供了方便。在前一章中用來去掉串尾空格的RTrim()就是一個函數的例子。
函數沒有SQL的可移植性強 能運行在多個系統上的代碼稱為可移植的（ portable）。相對來說，多數SQL語句是可移植的，在SQL實現之間有差異時，這些差異通常不那麼難處理。而函數的可移植性卻不強。幾乎每種主要的DBMS的實現都支援其他實現不支援的函數，而且有時差異還很大。為了代碼的可移植，許多SQL程式員不贊成使用特殊實現的功能。雖然這樣做很有好處，但不總是利於應用程式的效能。如果不使用這些函數，編寫某些應用程式代碼會很艱難。必須利用其他方法來實現DBMS非常有效地完成的工作。如果你決定使用函數，應該保證做好代碼注釋，以便以後你（或其他人）能確切地知道所編寫SQL代碼的含義。

-11.2使用函數">11.2 使用函數

大多數SQL實現支援以下類型的函數。

• 用於處理文本串（如刪除或填儲值，轉換值為大寫或小寫）的文本函數。
• 用於在數值資料上進行算術操作（如返回絕對值，進行代數運算）的數值函數。
• 用於處理日期和時間值並從這些值中提取特定成分（例如，返回兩個日期之差，檢查日期有效性等）的日期和時間函數。
• 返回DBMS正使用的特殊資訊（如返回使用者登入資訊，檢查版本細節）的系統函數。

-11.2.1文本處理函數">11.2.1 文本處理函數

上一章中我們已經看過一個文本處理函數的例子，其中使用RTrim()函數來去除列值右邊的空格。下面是另一個例子，這次使用Upper()函數：

正如所見， Upper()將文本轉換為大寫，因此本例子中每個供應商都列出兩次，第一次為vendors表中儲存的值，第二次作為列vend_name_upcase轉換為大寫。
表11-1列出了某些常用的文本處理函數。

表11-1中的SOUNDEX需要做進一步的解釋。 SOUNDEX是一個將任何文本串轉換為描述其語音表示的字元模式的演算法。 SOUNDEX考慮了類似的發音字元和音節，使得能對串進行發音比較而不是字母比較。雖然SOUNDEX不是SQL概念，但MySQL（就像多數DBMS一樣）都提供對SOUNDEX的支援。下面給出一個使用Soundex()函數的例子。 customers表中有一個顧客Coyote Inc.，其聯絡名為Y.Lee。但如果這是輸入錯誤，此聯絡名實際應該是Y.Lie，怎麼辦？顯然，按正確的聯絡名搜尋不會返回資料，如下所示：

現在試一下使用Soundex()函數進行搜尋，它匹配所有發音類似於Y.Lie的聯絡名：

在這個例子中， WHERE子句使用Soundex()函數來轉換cust_contact列值和搜尋串為它們的SOUNDEX值。因為Y.Lee和Y.Lie發音相似，所以它們的SOUNDEX值匹配，因此WHERE子句正確地過濾出了所需的資料。

-11.2.2日期和時間處理函數">11.2.2 日期和時間處理函數

日期和時間採用相應的資料類型和特殊的格式儲存，以便能快速和有效地排序或過濾，並且節省實體儲存體空間。一般，應用程式不使用用來儲存日期和時間的格式，因此日期和時間函數總是被用來讀取、統計和處理這些值。由於這個原因，日期和時間函數在MySQL語言中具有重要的作用。
表11-2列出了某些常用的日期和時間處理函數。

迄今為止，我們都是用比較數值和文本的WHERE子句過濾資料，但資料經常需要用日期進行過濾。用日期進行過濾需要注意一些別的問題和使用特殊的MySQL函數。首先需要注意的是MySQL使用的日期格式。無論你什麼時候指定一個日期，不管是插入或更新表值還是用WHERE子句進行過濾，日期必須為格式yyyy-mm-dd。因此， 2005年9月1日，給出為2005-09-01。雖然其他的日期格式可能也行，但這是首選的日期格式，因為它排除了多義性（如，04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或……）。
應該總是使用4位元字的年份 支援2位元字的年份， MySQL處理00-69為2000-2069，處理70-99為1970-1999。雖然它們可能是打算要的年份，但使用完整的4位元字年份更可靠，因為MySQL不必做出任何假定。因此，基本的日期比較應該很簡單：
![pic](MySQL必知應會-第11章-使用資料處理函數/Snipaste_2018-04-13_18-40-50

此SELECT語句正常運行。它檢索出一個訂單記錄，該訂單記錄的order_date為2005-09-01。但是，使用WHERE order_date = ‘2005-09-01’可靠嗎？ order_date的資料類型為datetime。這種類型儲存日期及時間值。範例表中的值全都具有時間值00:00:00，但實際中很可能並不總是這樣。如果用當前日期和時間儲存訂單日期（因此你不僅知道訂單日期，還知道下訂單當天的時間），怎麼辦？比如， 儲存的order_date 值 為2005-09-01 11:30:05，則WHERE order_date = ‘2005-09-01’失敗。即使給出具有該日期的一行，也不會把它檢索出來，因為WHERE匹配失敗。解決辦法是指示MySQL僅將給出的日期與列中的日期部分進行比較，而不是將給出的日期與整個列值進行比較。為此，必須使用Date()函數。 Date(order_date)指示MySQL僅提取列的日期部分，更可靠的SELECT語句為：

如果要的是日期，請使用Date() 如果你想要的僅是日期，則使用Date()是一個良好的習慣，即使你知道相應的列只包含日期也是如此。這樣，如果由於某種原因表中以後有日期和時間值，你的SQL代碼也不用改變。當然，也存在一個Time()函數，在你只想要時間時應該使用它。Date()和Time()都是在MySQL 4.1.1中第一次引入的。在你知道了如何用日期進行相等測試後，其他動作符（在第6章中介紹）的使用也就很清楚了。不過，還有一種日期比較需要說明。如果你想檢索出2005年9月下的所有訂單，怎麼辦？簡單的相等測試不行，因為它也要匹配月份中的天數。有幾種解決辦法，其中之一如下所示：

其中， BETWEEN操作符用來把2005-09-01和2005-09-30定義為一個要匹配的日期範圍。還有另外一種辦法（一種不需要記住每個月中有多少天或不需要操心閏年2月的辦法）：

Year()是一個從日期（或日期時間）中返回年份的函數。類似，Month()從日期中返回月份。因此， WHERE Year(order_date) = 2005 AND Month(order_date) = 9檢索出order_date為2005年9月的所有行。
MySQL的版本差異 MySQL 4.1.1中增加了許多日期和時間函數。如果你使用的是更早的MySQL版本，應該查閱具體的文檔以確定可以使用哪些函數。

-11.2.3數值處理函數">11.2.3 數值處理函數

數值處理函數僅處理數值資料。這些函數一般主要用於代數、三角或幾何運算，因此沒有串或日期—時間處理函數的使用那麼頻繁。具有諷刺意味的是，在主要DBMS的函數中，數值函數是最一致最統一的函數。
表11-3列出一些常用的數值處理函數。

-11.3小結">11.3 小結

本章介紹了如何使用SQL的資料處理函數，並著重介紹了日期處理函數。

MySQL必知應會-第11章-使用資料處理函數