如何使用 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
  • 分組和樹形結構是不一樣的。 樹形結構是以遞歸形式存在。分組是以鍵值對存在的形式,類似於GroupBy這樣的形式。 舉個例子 ID NAME SEX Class 1 張三 男 1 2 李四 女 2 3 王二 男 1 當以Sex為分組依據時則是 Key Value 男 1 張三 男 1 3 王二 男 1 ...
  • NetCore中將SQLServer資料庫備份為Sql腳本 描述: 最近寫項目收到了一個需求, 就是將SQL Server資料庫備份為Sql腳本, 如果是My Sql之類的還好說, 但是在網上搜了一大堆, 全是教你怎麼操作SSMS的, 就很d疼! 解決方案: 通過各種查找資料, 還有一些老哥的幫助, ...
  • 我的Notion Clowd.Squirrel Squirrel.Windows 是一組工具和適用於.Net的庫,用於管理 Desktop Windows 應用程式的安裝和更新。 Squirrel.Windows 對 Windows 應用程式的實現語言沒有任何要求,甚至無需服務端即可完成增量更新。 ...
  • 轉載請註明來源 https://www.cnblogs.com/brucejiao/p/16188865.html 謝謝! 轉載請註明來源 https://www.cnblogs.com/brucejiao/p/16188865.html 謝謝! 轉載請註明來源 https://www.cnblog ...
  • 1. Netty源碼研究筆記(3)——Channel系列 依舊是通過先縱向再橫向的研究方法,在開篇中,我們發現不管是Sever還是Client,最終的啟動是通過調用channel的對應方法來完成的,而這個動作實際在channel綁定的eventLoop中執行。 接下來,我們繼續EchoSever、E ...
  • 大家好,今天給大家介紹一款輕量、快速、穩定可編排的組件式規則引擎框架LiteFlow。 一、LiteFlow的介紹 LiteFlow官方網站和代碼倉庫地址 官方網站:https://yomahub.com/liteflow Gitee托管倉庫:https://gitee.com/dromara/li ...
  • 我使用Spring AOP實現了用戶操作日誌功能 今天答辯完了,復盤了一下系統,發現還是有一些東西值得拿出來和大家分享一下。 需求分析 系統需要對用戶的操作進行記錄,方便未來溯源 首先想到的就是在每個方法中,去實現記錄的邏輯,但是這樣做肯定是不現實的,首先工作量大,其次違背了軟體工程設計原則(開閉原 ...
  • 《零基礎學Java》 繪製幾何圖形 Java可以分別使用 Graphics 和 Graphics2D 繪製圖形,Graphics類 使用不同的方法繪製不同的圖形(drawLine()方法可f以繪製線、drawRect()方法用於繪製矩形、drawOval()方法用於繪製橢圓形)。 Graphics類 ...
  • 本期教程人臉識別第三方平臺為虹軟科技,本文章講解的是人臉識別RGB活體追蹤技術,免費的功能很多可以自行搭配,希望在你看完本章課程有所收穫。 ...
  • 很多人都喜歡使用黑色的主題樣式,包括我自己,使用了差不多三年的黑色主題,但是個人覺得在進行視窗轉換的時候很廢眼睛。 比如IDEA是全黑的,然後需要看PDF或者WORD又變成白色的了,這樣來回切換導致眼睛很累,畢竟現在網頁以及大部分軟體的界面都是白色的。那麼還是老老實實的使用原來比較順眼的模式吧。 1 ...