如何使用 SQL 函數處理數據

来源:https://www.cnblogs.com/vin-c/archive/2022/05/12/16261875.html
-Advertisement-
Play Games

本文介紹什麼是函數,DBMS 支持何種函數,以及如何使用這些函數;還將講解為什麼 SQL 函數的使用可能會帶來問題。 一、函數 與大多數其他電腦語言一樣,SQL 也可以用函數來處理數據。函數一般是在數據上執行的,為數據的轉換和處理提供了方便。 SQL 如何創建計算欄位 中用來去掉字元串尾的空格的 ...


目錄

本文介紹什麼是函數,DBMS 支持何種函數,以及如何使用這些函數;還將講解為什麼 SQL 函數的使用可能會帶來問題。

一、函數

與大多數其他電腦語言一樣,SQL 也可以用函數來處理數據。函數一般是在數據上執行的,為數據的轉換和處理提供了方便。

SQL 如何創建計算欄位 中用來去掉字元串尾的空格的 RTRIM() 就是一個函數。

1.1 函數帶來的問題

在繼續閱讀之前,你應該瞭解使用 SQL 函數所存在的問題。

與幾乎所有 DBMS 都等同地支持 SQL 語句(如 SELECT)不同,每一個 DBMS 都有特定的函數。

事實上,只有少數幾個函數被所有主要的 DBMS 等同地支持。雖然所有類型的函數一般都可以在每個 DBMS 中使用,但各個函數的名稱和語法可能極其不同。

為了說明可能存在的問題,表 1 列出了 3 個常用的函數及其在各個 DBMS 中的語法:

表 1 DBMS 函數的差異

函數 語法
提取字元串的組成部分 DB2、Oracle、PostgreSQL 和 SQLite 使用 SUBSTR();MariaDB、MySQL 和 SQL Server 使用 SUBSTRING()
數據類型轉換 Oracle 使用多個函數,每種類型的轉換有一個函數;DB2 和 PostgreSQL 使用 CAST();MariaDB、MySQL 和 SQL Server 使用 CONVERT()
取當前日期 DB2 和 PostgreSQL 使用 CURRENT_DATE;MariaDB 和 MySQL 使用 CURDATE();Oracle 使用 SYSDATE;SQL Server 使用 GETDATE();SQLite 使用 DATE()

可以看到,與 SQL 語句不一樣,SQL 函數不是可移植的。這意味著為特定 SQL 實現編寫的代碼在其他實現中可能不能用。

可移植(portable)

所編寫的代碼可以在多個系統上運行。

為了代碼的可移植,許多 SQL 程式員不贊成使用特定於實現的功能。雖然這樣做很有好處,但有的時候並不利於應用程式的性能。

如果不使用這些函數,編寫某些應用程式代碼會很艱難。必須利用其他方法來實現 DBMS 可以非常有效完成的工作。

提示:是否應該使用函數?

現在,你面臨是否應該使用函數的選擇。決定權在你,使用或是不使用也沒有對錯之分。

如果你決定使用函數,應該保證做好代碼註釋,以便以後你自己(或其他人)能確切地知道這些 SQL 代碼的含義。

二、使用函數

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

  • 用於處理文本字元串(如刪除或填充值,轉換值為大寫或小寫)的文本函數。
  • 用於在數值數據上進行算術操作(如返回絕對值,進行代數運算)的數值函數。
  • 用於處理日期和時間值並從這些值中提取特定成分(如返回兩個日期之差,檢查日期有效性)的日期和時間函數。
  • 用於生成美觀好懂的輸出內容的格式化函數(如用語言形式表達出日期,用貨幣符號和千分位表示金額)。
  • 返回 DBMS 正使用的特殊信息(如返回用戶登錄信息)的系統函數。

我們在 SQL 如何創建計算欄位 中看到函數用於 SELECT 後面的列名,但函數的作用不僅於此。

它還可以作為 SELECT 語句的其他成分,如在 WHERE 子句中使用,在其他 SQL 語句中使用等,後面會做更多的介紹。

2.1 文本處理函數

SQL 如何創建計算欄位 中,我們已經看過一個文本處理函數的例子,其中使用 RTRIM() 函數來去除列值右邊的空格。下麵是另一個例子,這次使用的是 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

可以看到,UPPER() 將文本轉換為大寫,因此本例子中每個供應商都列出兩次,第一次為 Vendors 表中存儲的值,第二次作為列 vend_name_upcase 轉換為大寫。

提示:大寫,小寫,大小寫混合

此時你應該已經知道 SQL 函數不區分大小寫,因此 upper(), UPPER(), Upper() 都可以,substr(), SUBSTR(), SubStr() 也都行。

隨你的喜好,不過註意保持風格一致,不要變來變去,否則你寫的程式代碼就不好讀了。

表 2 列出了一些常用的文本處理函數。

表 2 常用的文本處理函數

函數 說明
LEFT()(或使用子字元串函數) 返回字元串左邊的字元
LENGTH()(也使用 DATALENGTH()LEN() 返回字元串的長度
LOWER() 將字元串轉換為小寫
LTRIM() 去掉字元串左邊的空格
RIGHT()(或使用子字元串函數) 返回字元串右邊的字元
RTRIM() 去掉字元串右邊的空格
SUBSTR()SUBSTRING() 提取字元串的組成部分(見表 1)
SOUNDEX() 返回字元串的 SOUNDEX
UPPER() 將字元串轉換為大寫

表 2 中的 SOUNDEX 需要做進一步的解釋。

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

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

雖然 SOUNDEX 不是 SQL 概念,但多數 DBMS 都提供對 SOUNDEX 的支持。

說明:SOUNDEX 支持

PostgreSQL 不支持 SOUNDEX(),因此以下的例子不適用於這個 DBMS。

另外,如果在創建 SQLite 時使用了 SQLITE_SOUNDEX 編譯時選項,那麼 SOUNDEX() 在 SQLite 中就可用。

因為 SQLITE_SOUNDEX 不是預設的編譯時選項,所以多數 SQLite 實現不支持 SOUNDEX()

下麵給出一個使用 SOUNDEX() 函數的例子。Customers 表中有一個顧客 Kids Place,其聯繫名為 Michelle Green。但如果這是錯誤的輸入,此聯繫名實際上應該是 Michael Green,該怎麼辦呢?顯然,按正確的聯繫名搜索不會返回數據,如下所示:

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_contact = 'Michael Green';

輸出:

cust_name                      cust_contact
--------------------------     ----------------------------

現在試一下使用 SOUNDEX() 函數進行搜索,它匹配所有發音類似於 Michael Green 的聯繫名:

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

輸出:

cust_name                      cust_contact
--------------------------     ----------------------------
Kids Place                     Michelle Green

在這個例子中,WHERE 子句使用 SOUNDEX() 函數把 cust_contact 列值和搜索字元串轉換為它們的 SOUNDEX 值。

因為 Michael GreenMichelle Green 發音相似,所以它們的 SOUNDEX 值匹配,因此 WHERE 子句正確地過濾出了所需的數據。

2.2 日期和時間處理函數

日期和時間採用相應的數據類型存儲在表中,每種 DBMS 都有自己的特殊形式。日期和時間值以特殊的格式存儲,以便能快速和有效地排序或過濾,並且節省物理存儲空間。

應用程式一般不使用日期和時間的存儲格式,因此日期和時間函數總是用來讀取、統計和處理這些值。由於這個原因,日期和時間函數在 SQL 中具有重要的作用。遺憾的是,它們很不一致,可移植性最差。

我們舉個簡單的例子,來說明日期處理函數的用法。Orders 表中包含的訂單都帶有訂單日期。要檢索出某年的所有訂單,需要按訂單日期去找,但不需要完整日期,只要年份即可。

為在 SQL Server 中檢索 2020 年的所有訂單,可如下進行:

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

輸出:

order_num
-----------
20005
20006
20007
20008
20009

這個例子使用了 DATEPART() 函數,顧名思義,此函數返回日期的某一部分。DATEPART()函數有兩個參數,它們分別是返回的成分和從中返回成分的日期。

在此例子中,DATEPART() 只從 order_date 列中返回年份。通過與 2020 比較,WHERE 子句只過濾出此年份的訂單。

下麵是使用名為 DATE_PART() 的類似函數的 PostgreSQL 版本:

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

Oracle 沒有 DATEPART() 函數,不過有幾個可用來完成相同檢索的日期處理函數。例如:

SELECT order_num
FROM Orders
WHERE EXTRACT(year FROM order_date) = 2020;

在這個例子中,EXTRACT() 函數用來提取日期的成分,year 表示提取哪個部分,返回值再與 2020 進行比較。

提示:PostgreSQL 支持 Extract()

除了前面用到的 DatePart(),PostgreSQL 也支持 Extract() 函數,因而也能這麼用。

完成相同工作的另一方法是使用 BETWEEN 操作符:

SELECT order_num
FROM Orders
WHERE order_date BETWEEN to_date('2020-01-01', 'yyyy-mm-dd')
 AND to_date('2020-12-31', 'yyyy-mm-dd');

在此例子中,Oracle 的 to_date() 函數用來將兩個字元串轉換為日期。一個包含 2020 年 1 月 1 日,另一個包含 2020 年 12 月 31 日。

BETWEEN 操作符用來找出兩個日期之間的所有訂單。值得註意的是,相同的代碼在 SQL Server 中不起作用,因為它不支持 to_date() 函數。但是,如果用 DATEPART() 替換 to_date(),當然可以使用這種類型的語句。

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

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

在 SQLite 中有個小技巧:

SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = '2020';

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

DBMS 提供的功能遠不止簡單的日期成分提取。大多數 DBMS 具有比較日期、執行日期的運算、選擇日期格式等的函數。

但是,可以看到,不同 DBMS 的日期/時間處理函數可能不同。關於你的 DBMS 具體支持的日期/時間處理函數,請參閱相應的文檔。

2.3 數值處理函數

數值處理函數僅處理數值數據。這些函數一般主要用於代數、三角或幾何運算,因此不像字元串或日期/時間處理函數使用那麼頻繁。

具有諷刺意味的是,在主要 DBMS 的函數中,數值函數是最一致、最統一的函數。表 3 列出一些常用的數值處理函數。

表 3 常用數值處理函數

函數 說明
ABS() 返回一個數的絕對值
COS() 返回一個角度的餘弦
EXP() 返回一個數的指數值
PI() 返回圓周率 π 的值
SIN() 返回一個角度的正弦
SQRT() 返回一個數的平方根
TAN() 返回一個角度的正切

關於具體 DBMS 所支持的算術處理函數,請參閱相應的文檔。

三、小結

本文介紹瞭如何使用 SQL 的數據處理函數。雖然這些函數在格式化、處理和過濾數據中非常有用,但它們在各種 SQL 實現中很不一致。

原文鏈接:https://www.developerastrid.com/sql/sql-functions/

(完)


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 針對假如已經是安裝了redis,只是是單部署,需要把他切換成redis集群+哨兵模式,我因為偷懶,就寫了個腳本來執行,各位看官,請品~你品~你細品~ 首先準備個升級包,放到任意路徑,內容如下: 第一個文件不用管,第二個跟第四個,是把裡面的配置改好,如何配置請參考我之前寫的redis集群一, 然後是u ...
  • 一 、通過雲開發平臺快速創建初始化應用 1.創建相關應用模版請參考鏈接:5分鐘站點生成神器——Docusaurus 2.完成創建後就可以在github中查看到新增的Docusaurus倉庫 二 、 本地編寫個人博客 1.將應用模版克隆到本地 ● 首先假定你已經安裝了Git、node,沒有安裝請移步n ...
  • 本文例子參考《STM32單片機開發實例——基於Proteus虛擬模擬與HAL/LL庫》 源代碼:https://github.com/LanLinnet/STM33F103R6 項目要求 實現呼吸燈的效果:D1為長亮LED,D2為呼吸燈,通過PWM的方式實現D2亮→滅→亮→滅……的漸變效果,一次變化 ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 前情提要: 該方法只適用於Windows10以上版本,Ubuntu直接可在微軟商店安裝 在這個網頁docs.microsoft.com/zh-cn/windo… 可以設置完成前5部,然後根據下麵具體操作。 ps:1.在連接中我們要打開powers ...
  • 五一5天小假期的結束大家休息好了嗎?上班了狀態回整的怎麼樣呢?阿裡云云發平臺給大家帶福利了呢,通過玩游戲把獎品帶回家。 雲開發平臺兩周年,0門檻部署上線4款熱門游戲,游戲暢玩還有AirPods耳機、筋膜槍等8種獎品任你挑啦!4款游戲只需要完成2個就可以抽獎呢,100%中獎;還額外有故事有獎徵集的板塊 ...
  • 一、引言 SQL Server有一些很好用的功能,只不過由於個人原因沒用過或者不記得怎麼用,導致需要花點時間用其它方式來實現。 二、好用小知識 2.1、FORMAT函數 1)時間格式化,如將當前日期格式化為2022-05-12: SELECT FORMAT(GETDATE(),'yyyy-MM-dd ...
  • 一、引言 A表數據同步至B表的場景很常見,比如一個公司有總部及分廠,它們使用相同的系統,只是賬套不同。此時,一些基礎數據如物料信息,只需要總部錄入即可,然後間隔一定時間同步至分廠,避免了重覆工作。 二、測試數據 CREATE TABLE StudentA ( ID VARCHAR(32), Name ...
  • 本文介紹什麼是 SQL 的聚集函數,如何利用它們彙總表的數據。這些函數很高效,它們返回結果一般比你在自己的客戶端應用程式中計算要快得多。 一、聚集函數 我們經常需要彙總數據而不用把它們實際檢索出來,為此 SQL 提供了專門的函數。使用這些函數,SQL 查詢可用於檢索數據,以便分析和報表生成。這種類型 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...