11. 使用MySQL之使用資料處理函式

hisun9發表於2024-10-25
MySql函式

1. 函式

與其他大多數計算機語言一樣，SQL支援利用函式來處理資料。

函式一般是在資料上執行的，它給資料的轉換和處理提供了方便。

在前一章中用來去掉串尾空格的RTrim()就是一個函式的例子。

補充：

函式沒有SQL的可移植性強

能執行在多個系統上的程式碼稱為可移植的（portable）。

相對來說，多數SQL語句是可移植的，在SQL實現之間有差異時，這些差異通常不那麼難處理。而函式的可移植性卻不強。幾乎每種主要的DBMS的實現都支援其他實現不支援的函式，而且有時差異還很大。

為了程式碼的可移植，許多SQL程式設計師不贊成使用特殊實現的功能。雖然這樣做很有好處，但不總是利於應用程式的效能。如果不使用這些函式，編寫某些應用程式程式碼會很艱難。必須利用其他方法來實現DBMS非常有效地完成的工作。

如果你決定使用函式，應該保證做好程式碼註釋，以便以後你（或其他人）能確切地知道所編寫SQL程式碼的含義。

2. 使用函式

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

  • 用於處理文字串（如刪除或填充值，轉換值為大寫或小寫）的文字函式。

  • 用於在數值資料上進行算術操作（如返回絕對值，進行代數運算）的數值函式。

  • 用於處理日期和時間值並從這些值中提取特定成分（例如，返回兩個日期之差，檢查日期有效性等）的日期和時間函式。

  • 返回DBMS正使用的特殊資訊（如返回使用者登入資訊，檢查版本細節）的系統函式。

2.1 文字處理函式

上一章中我們已經看過一個文字處理函式的例子，其中使用 RTrim()函式來去除列值右邊的空格。

下面是另一個例子，這次使用 Upper() 函式：

select vend_name, upper(vend_name) as vend_name_upcase
from vendors
order by vend_name;

輸出如下：

img

Upper()將文字轉換為大寫，因此本例子中每個供應商都列出兩次，第一次為vendors表中儲存的值，第二次作為列vend_name_upcase轉換為大寫。

下面列出了某些常用的文字處理函式。

img
img

  • 對SOUNDEX做進一步的解釋：

    SOUNDEX是一個將任何文字串轉換為描述其語音表示的字母數字模式的演算法。

    SOUNDEX考慮了類似的發音字元和音節，使得能對串進行發音比較而不是字母比較。

    雖然SOUNDEX不是SQL概念，但MySQL（就像多數DBMS一樣）都提供對
    SOUNDEX的支援。

    下面給出一個使用Soundex()函式的例子。

    customers表中有一個顧客Coyote Inc.，其聯絡名為Y Lee。但如果這是輸入錯誤，此聯絡名實際應該是Y Lie，怎麼辦？顯然，按正確的聯絡名搜尋不會返回資料，如下所示：

    select cust_name, cust_contact
from customers
where cust_contact = 'Y Lie';

    輸出如下：

    img

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

    select cust_name, cust_contact
from customers
where soundex(cust_contact) = soundex('Y Lie');

    輸出如下：

    img

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

  • 對Left()做進一步的解釋：

    在 MySQL 中，LEFT() 函式用於從字串的左邊提取指定長度的子字串。

    語法：

    LEFT(string, length)

    • string：要從中提取子字串的字串。

    • length：要提取的字元數。

    舉例：

    SELECT LEFT('Hello, World!', 5);  -- 輸出 'Hello'

    即從字串 'Hello, World!' 中提取從左邊開始的 5 個字元。

    再比如，如果你有一個儲存姓名的表 students，並且想要提取每個學生姓名的前兩個字元，你可以這樣寫：

    SELECT LEFT(name, 2) FROM students;

    這個查詢會返回每個姓名的前兩個字元。

    注意：

    在 MySQL 中，LEFT() 函式同樣可以處理中文字元。它會根據字元的個數（而不是位元組數）來提取中文字元。

    舉個例子：

    img

    第一條select語句的結果如下：

    img

    第二條select語句的結果如下：

    img

  • 對Right()做進一步的解釋：

    在 MySQL 中，RIGHT() 函式用於從字串的右邊提取指定長度的子字串。

    其餘的和Left()一樣，不贅敘

2.2 日期和時間處理函式

日期和時間採用相應的資料型別和特殊的格式儲存，以便能快速和有效地排序或過濾，並且節省物理儲存空間。

插入一個題外話：

MySQL 提供了幾種專門用於日期和時間的資料型別，能夠以高效的方式儲存和操作這些資料。以下是主要的資料型別以及它們的格式：

  1. DATE 型別

    用於儲存日期（不包括時間部分），格式為 'YYYY-MM-DD'。

    儲存大小：3 位元組。

    範圍：1000-01-01 到 9999-12-31。

    比如：

    DATE '2024-10-24'

  2. TIME 型別

    用於儲存時間（不包括日期部分），格式為 'HH:MM:SS'。

    儲存大小：3 位元組。

    範圍：'-838:59:59' 到 '838:59:59'（支援負時間表示持續時間的情況）

    比如：

    TIME '14:30:45'

  3. DATETIME 型別

    用於儲存日期和時間的組合，格式為 'YYYY-MM-DD HH:MM:SS'。

    儲存大小：8 位元組。

    範圍：1000-01-01 00:00:00 到 9999-12-31 23:59:59。

    比如：

    DATETIME '2024-10-24 14:30:45'

  4. TIMESTAMP 型別

    用於儲存自 1970-01-01 00:00:00 UTC（Unix Epoch 時間）以來的秒數。與 DATETIME 類似，格式為 'YYYY-MM-DD HH:MM:SS'。

    儲存大小：4 位元組（比 DATETIME 佔用空間更小）。

    範圍：1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。

    TIMESTAMP 可以隨著時區的不同而自動調整，因此常用於記錄操作的時間戳（例如記錄插入或更新時的時間）。

    比如：

    TIMESTAMP '2024-10-24 14:30:45'

  5. YEAR 型別

    用於儲存年份，格式為 'YYYY'。

    儲存大小：1 位元組。

    範圍：1901 到 2155。

    比如：

    YEAR '2024'

    一個好玩的

    如果是year '2156'呢？MySQL會做什麼？

    在 MySQL 中，YEAR 資料型別的有效範圍是 1901 到 2155，因此 YEAR '2156' 是超出有效範圍的。如果你嘗試將 '2156' 儲存在 YEAR 型別的欄位中，會導致錯誤或資料被截斷。

    處理超出範圍的值：

    • 如果你嘗試插入 YEAR '2156'，MySQL 會產生一個警告或錯誤，具體行為取決於 SQL 模式的設定。如果是嚴格模式（STRICT 模式），則會丟擲錯誤並阻止插入。如果不是嚴格模式，MySQL 可能會插入一個預設值（通常為 0000），併發出警告。

    解決方案：

    • 保持在範圍內：在使用 YEAR 型別時，確保年份在 1901 到 2155 之間。如果需要儲存 2156 或更大的年份，你可以考慮使用其他資料型別，如 INT 或 VARCHAR，以手動儲存年份。

    • 使用 DATETIME 或 TIMESTAMP：如果你需要儲存更大範圍的日期和時間，可以考慮使用 DATETIME 資料型別，雖然它佔用更多的儲存空間，但它能表示更大範圍的年份（1000 到 9999）。

  6. 資料型別和格式的優勢：

    • 高效的儲存：

      這些資料型別經過最佳化，採用內部的二進位制表示方式，從而節省儲存空間。例如，DATE 只佔 3 個位元組，而一個字元型字串表示同樣的日期需要更多的空間。

    • 高效的排序和過濾：

      使用內建的日期和時間資料型別，可以高效地進行排序和過濾操作，而不需要額外的轉換。

    • 特殊的格式：

      MySQL 在後臺使用一種二進位制格式來儲存日期和時間，使得它們可以快速解析和計算。例如，雖然 DATETIME 看起來像 'YYYY-MM-DD HH:MM:SS'，但它實際是以壓縮的方式儲存，並且能夠快速比較和操作。

迴歸正題：

一般，應用程式不使用用來儲存日期和時間的格式，因此日期和時間函式總是被用來讀取、統計和處理這些值。由於這個原因，日期和時間函式在MySQL語言中具有重要的作用。

表11-2列出了某些常用的日期和時間處理函式。

img

迄今為止，我們都是用比較數值和文字的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不必做出任何假定。

因此，基本的日期比較應該很簡單：

select cust_id, order_num
from orders
where order_date = '2005-09-01';

輸出如下：

img

此SELECT語句正常執行。它檢索出一個訂單記錄，該訂單記錄的order_date為2005-09-01。

但是，使用WHERE order_date = '2005-09-01'可靠嗎？

order_ date的資料型別為datetime。這種型別儲存日期及時間值。樣例表中的值全都具有時間值00:00:00。如下圖：

img

但實際中很可能並不總是這樣。如果用當前日期和時間儲存訂單日期（因此你不僅知道訂單日期，還知道下訂單當天的時間）， 怎麼辦 ？

比如，儲存的 order_date 值為 2005-09-01 11:30:05，則 WHERE order_date = '2005-09-01'失敗。即使給出具有該日期的一行，也不會把它檢索出來，因為WHERE匹配失敗。

解決辦法是指示MySQL僅將給出的日期與列中的日期部分進行比較，而不是將給出的日期與整個列值進行比較。為此，必須使用 Date()函式。Date(order_date)指示MySQL僅提取列的日期部分。

更可靠的SELECT語句為：

select cust_id, order_num
from orders
where date(order_date) = '2005-09-01';

輸出如下：

img

插一句題外話

自己試了下DateDiff()的寫法

img

補充：

如果要的是日期，請使用Date()：

如果你想要的僅是日期，則使用Date()是一個良好的習慣，即使你知道相應的列只包含日期也是如此。這樣，如果由於某種原因表中以後有日期和時間值，你的SQL程式碼也不用改變。當然，也存在一個Time()函式，在你只想要時間時應該使用它。 Date()和Time()都是在MySQL 4.1.1中第一次引入的。

不過，還有一種日期比較需要說明。

如果你想檢索出2005年9月下的所有訂單，怎麼辦？簡單的相等測試不行，因為它也要匹配月份中的天數。

有幾種解決辦法，其中之一如下所示：

select cust_id, order_num
from orders
where date(order_date) between '2005-09-01' and '2005-09-30';

輸出如下：

img

其中，BETWEEN運算子用來把2005-09-01和2005-09-30定義為一個要匹配的日期範圍。

還有另外一種辦法（一種不需要記住每個月中有多少天或不需要操心閏年2月的辦法）：

select cust_id, order_num
from orders
where year(order_date) = 2005 and month(order_date) = 9;

輸出如下：

img

Year()是一個從日期（或日期時間）中返回年份的函式。類似，Month()從日期中返回月份。因此，WHERE Year(order_date)= 2005 AND Month(order_date) = 9檢索出order_date為2005年9月的所有行。

補充：

  • MySQL的版本差異：

    MySQL 4.1.1中增加了許多日期和時間函式。如果你使用的是更早的MySQL版本，應該查閱具體的文件以確定可以使用哪些函式。

  • DATE_SUB() 和 DATEDIFF()的區別：

    DATE_SUB() 和 DATEDIFF() 都是 MySQL 中用於操作日期的函式，但它們的功能完全不同：

    • DATE_SUB() 用於從日期中減去一個時間間隔（如天、月、年等），返回一個新的日期。

      • 語法：

        DATE_SUB(date, INTERVAL expr unit)

        • date: 原始日期。

        • expr: 時間間隔的數量。

        • unit: 時間單位（如 DAY, MONTH, YEAR 等）。

      • 功能：從日期減去指定的時間間隔，返回一個新的日期。

      • 示例： 從當前日期減去 7 天：

        select date_sub(curdate(), interval 7 day);

        輸出如下：

        img

    • DATEDIFF() 用於計算兩個日期之間的天數差，返回一個整數值，表示兩個日期之間相差多少天。

      • 語法：

        DATEDIFF(date1, date2)

        • date1 和 date2: 兩個日期，用於計算相差的天數。

        • 結果為 date1 - date2，即 date1 減去 date2。

      • 功能：計算兩個日期之間的天數差，並返回整數結果。

      • 示例： 計算兩個日期之間相差多少天：

        select datediff('2024-10-25', '2024-10-20')

        輸出如下：

        img

  • ADDDATE() 和 DATE_ADD() 的區別：

    在 MySQL 中，ADDDATE() 和 DATE_ADD() 這兩個函式都用於向日期新增時間間隔，功能非常相似，主要的區別在於語法上的細微差異。

    • ADDDATE() 用於向日期新增天數或指定的時間間隔。該函式有兩種不同的用法：

      • 可以直接新增天數。

      • 也可以與 INTERVAL 一起使用，新增年、月、天等不同的時間單位。

        語法：

        1. 直接新增天數：

          ADDDATE(date, number_of_days)

          • date: 要修改的日期。

          • number_of_days: 新增的天數，必須是整數

        2. 使用 INTERVAL：

          ADDDATE(date, INTERVAL expr unit)

          • date: 要修改的日期。

          • expr: 時間間隔的數量。

          • unit: 時間單位（如 DAY, MONTH, YEAR 等）。

    • DATE_ADD() 函式與 ADDDATE() 的功能相同，但只能與 INTERVAL 結合使用，語法結構與 ADDDATE() 的第二種形式一致。

2.3 數值處理函式

數值處理函式僅處理數值資料。這些函式一般主要用於代數、三角或幾何運算，因此沒有串或日期—時間處理函式的使用那麼頻繁。

具有諷刺意味的是，在主要DBMS的函式中，數值函式是最一致最統一的函式。

img

相關文章