第六章 SQL函式的使用
按函式種類可以分為聚合函式,數學函式,字串函式,日期和時間函式,轉換函式和後設資料函式6種.
>.聚合函式.
聚合函式對一組值執行計算,並返回單個值.除count外,聚合函式都會忽略空值.
通常與SELECT語句的GROUP BY 子句一起使用,為每一個分組返回一個單一值.
聚合函式包括:
>.SUM: 求各函式.只能用於數字列,空值會被忽略.返回值型別為expression.含有索引的欄位能夠加快計算速度
>.AVG : 求平均值函式.返回值型別由表示式的運算結果型別決定.
>.MIN : 求最小值函式,不能用於bit欄位,返回值型別為expression.
>.MAX: 求最大值函式,不能用於bit,text和image欄位,返回值型別為expression.
>.COUNT: 統計函式
>.DISTINCT: 去重函式
-- 計算總銷售額 SELECT SUM(amount) AS total_sales FROM sales; -- 使用SUM函式求sales表中amount列的總和 -- 計算平均產品價格 SELECT AVG(price) AS average_price FROM products; -- 使用AVG函式求products表中price列的平均值 -- 查詢最低的產品價格 SELECT MIN(price) AS min_price FROM products; -- 使用MIN函式找出products表中price列的最小值 -- 查詢最高的產品價格 SELECT MAX(price) AS max_price FROM products; -- 使用MAX函式找出products表中price列的最大值 -- 統計客戶總數 SELECT COUNT(customer_id) AS total_customers FROM customers; -- 使用COUNT函式計算customers表中customer_id列的非空值數量 -- 統計不同產品的訂單數量 SELECT COUNT(DISTINCT product_id) AS distinct_products_ordered FROM orders; -- 使用COUNT和DISTINCT函式統計orders表中product_id列的不同值數量
>.數學函式.
數學函式對數字表示式進行數學運算,並將結果返回給使用者.在預設情況下,傳遞給數學函式的數字將被解釋為雙精度浮點數.
數學函式包括:
>.ABS:絕對值
>.PI() :圓周率
>.POWER : 乘方函式
>.RAND: 隨機浮點函式,返回0~1之前的隨機float值.如果沒有傳入種子引數,則由系統自動分配,反之如果有傳入種子引數,則會返回相同的值,比如:
這裡順便說一下UNION 帶ALL與不帶ALL的區別: 不帶ALL的話,會把相同的值過慮掉,所以下圖只返回了三行記錄
>.ROUND: 四捨五入函式,它有三個引數,第一個是數值,第二和第三個是可省引數.預設值都為0. 其中第二個參數列示保留小數的位數,第三參數列示舍入的模式.0表示不改變,1表示向下取整,2表示身上取整,3表示向零取整.
-- 基本用法：將數值 3.14159 四捨五入保留兩位小數。 SELECT ROUND(3.14159, 2) AS rounded_number; -- 結果為 3.14 -- 指定舍入方式：將數值 3.5 四捨五入，但指定向上取整。 SELECT ROUND(3.5, 0, 2) AS rounded_up_number; -- 結果為 4 -- 省略小數位數：省略 `decimals` 引數，對整數進行四捨五入。 SELECT ROUND(10.789) AS rounded_integer; -- 結果為 11 -- 向下取整：將數值 3.8 向下取整為最接近的整數。 SELECT ROUND(3.8, 0, 1) AS rounded_down_number; -- 結果為 3 -- 向零取整：將數值 -2.5 向零取整。 SELECT ROUND(-2.5, 0, 3) AS rounded_towards_zero; -- 結果為 -2
>.SQUARE平方函式和SQRT平方根函式
-- 計算數值 5 的平方。 SELECT SQUARE(5) AS square_result; -- 結果為 25 -- 計算數值 25 的平方根。 SELECT SQRT(25) AS square_root_result; -- 結果為 5
>.三角函式: COS,COT,SIN,TAN函式
-- 計算角度為 0 的餘弦值。 SELECT COS(0) AS cosine_result; -- 結果為 1，因為餘弦值在 0 度時為 1 -- 計算角度為 1 的餘切值。 SELECT COT(1) AS cotangent_result; -- 結果為 0.64209261593433（約等於），因為餘切值是餘弦值除以正弦值 -- 計算角度為 0 的正弦值。 SELECT SIN(0) AS sine_result; -- 結果為 0，因為正弦值在 0 度時為 0 -- 計算角度為 0 的正切值。 SELECT TAN(0) AS tangent_result; -- 結果為 0，因為正切值在 0 度時為 0
>.字串函式.
字串函式對N進位制資料,字串和表示式執行不同的運算,如返回字串的起始位置,返回字串的個數等.
字串函式包括:
>.ASCII: 返回字串表示式最左端字元的ASCII碼值.返回值為int型別.
>.CHARINDEX: 返回字串的起始位置.
-- 查詢字串 'world' 在字串 'Hello, world!' 中的起始位置。 SELECT CHARINDEX('world', 'Hello, world!') AS position; -- 結果為 8，因為 'world' 在字串 'Hello, world!' 中從第 8 個位置開始 -- 查詢字串 'universe' 在字串 'Hello, world!' 中的起始位置。 SELECT CHARINDEX('universe', 'Hello, world!') AS position; -- 結果為 0，因為 'universe' 不存在於字串 'Hello, world!' 中，返回 0 表示未找到
>.LEFT / RIGHE / LEN / SUBSTRING
-- 使用 LEFT 函式從字串的左側提取指定長度的字元。 SELECT LEFT('Hello, world!', 5) AS left_result; -- 結果為 'Hello'，因為 LEFT 函式提取了字串 'Hello, world!' 的前 5 個字元 -- 使用 RIGHT 函式從字串的右側提取指定長度的字元。 SELECT RIGHT('Hello, world!', 6) AS right_result; -- 結果為 'world!'，因為 RIGHT 函式提取了字串 'Hello, world!' 的最後 6 個字元 -- 使用 LEN 函式獲取字串的長度。 SELECT LEN('Hello, world!') AS length_result; -- 結果為 13，因為字串 'Hello, world!' 的長度為 13 個字元 -- 使用 SUBSTRING 函式從字串中提取子字串。 SELECT SUBSTRING('Hello, world!', 7, 5) AS substring_result; -- 結果為 'world'，因為 SUBSTRING 函式從字串 'Hello, world!' 的第 7 個字元開始提取長度為 5 的子字串
>.REPLACE,替換字串
--在欄位中的使用方法 SELECT REPLACE(column_name, 'cat', 'dog') AS replaced_string FROM table_name; -- 使用 REPLACE 函式替換字串中的指定子字串。 SELECT REPLACE('Hello, world!', 'world', 'universe') AS replaced_string; -- 結果為 'Hello, universe!'，因為 REPLACE 函式將字串中的 'world' 替換為 'universe' -- 替換字串中的多個子字串。 SELECT REPLACE('The cat sat on the mat.', 'cat', 'dog') AS replaced_string; -- 結果為 'The dog sat on the mat.'，因為 REPLACE 函式將字串中的 'cat' 替換為 'dog'
>.REVERSE,反向排序
-- 使用 REVERSE 函式反轉字串的字元順序。 SELECT REVERSE('Hello, world!') AS reversed_string; -- 結果為 '!dlrow ,olleH'，因為 REVERSE 函式將字串 'Hello, world!' 的字元順序反轉
>.STR:將數字資料轉為字串資料
/*語法 STR ( float_expression, length, decimal ) 其中： float_expression 是要轉換為字串的數字表示式。 length 是返回的字串的總長度,包括小數點。當總長度小於第二個引數指定的長度時，SQL Server 使用空格填充。 decimal 是返回的字串中小數點後的位數。*/ -- 將數字 123.456 轉換為字串，總長度為 10，小數點後保留 2 位 SELECT STR(123.456, 10, 2) AS ConvertedString; --這將返回字串 ' 123.46'，其中數字部分右對齊，總長度為 10，小數點後保留兩位。
>.日期和時間函式.
日期和時間函式是用來處理與日期和時間相關的一些資料.SQL SERVER中,0解釋為1900年1月1日,在使用日期函式時,其日期只應在1753年-9999年,這是SQL SERVER 系統能識別的日期範圍.
日期和時間函式包括 :
>.DAY / MONTH / YEAR / GETDATE
>.DATEADD / DATEIFF 這兩個函式需要注意它的第一個引數規定了應在日期的哪一部分來做計算(兩個函式可以用縮寫),具體如下
-- 獲取當前日期的年份 SELECT YEAR(GETDATE()); -- 結果為當前年份，例如：2024 -- 獲取當前日期的月份 SELECT MONTH(GETDATE()); -- 結果為當前月份，例如：4 -- 獲取當前日期的日份 SELECT DAY(GETDATE()); -- 結果為當前日期的日份，例如：1 -- 將當前日期加上30天 SELECT DATEADD(DAY, 30, GETDATE()); -- 結果為當前日期加上30天的日期 -- 計算兩個日期之間的天數差 DECLARE @StartDate DATE = '2023-01-01'; DECLARE @EndDate DATE = '2023-02-01'; SELECT DATEDIFF(DAY, @StartDate, @EndDate); -- 結果為兩個日期之間的天數差，例如：31.第一引數也可以使用縮寫 dd 或者 d
順便說點format彆扭的用法:
>.轉換函式.
在SQL SERVER中資料型別轉換分為隱式轉換和顯式轉換.
隱式轉換由SQL SERVER系統自動處理某些資料型別的轉換.比如一個int型別的變數與一個smallint型別的變數作比較,smallint型別的變數就會被自動轉換為int型別,再與int資料型別的變數作比較 .
顯式轉換則由CAST和CONVERT來執行,它們不但可以將指定的資料型別轉換為另一種資料型別,還可以用來獲取各種特殊的資料格式.
另外CAST 函式基於SQL-92標準並且優先於CONVERT函式.
當處理SQL_variant資料型別時,SQL SERVER支援將具有其他資料型別的物件隱性轉換為SQL_variant型別,但是卻不支援從SQL_variant資料型別隱性地轉換到其他資料型別.簡單來說,就是非variant型別可以隱性轉成variant型別,但variant型別不能隱性的轉換為非variant型別.
-- 將字串'123'轉換為整數 SELECT CAST('123' AS INT); -- 結果為123 -- 將日期轉換為字串，格式為'YYYY-MM-DD' SELECT CONVERT(VARCHAR, GETDATE(), 23); -- 結果為類似 '2024-04-01' 的字串 -- 將浮點數3.14轉換為整數 SELECT CAST(3.14 AS INT); -- 結果為3 -- 將字串'2024-04-01'轉換為日期 SELECT CONVERT(DATE, '2024-04-01', 23); -- 結果為 '2024-04-01' -- 將日期轉換為字串，格式為'YYYYMMDD' SELECT CAST(GETDATE() AS VARCHAR(8)); -- 結果為類似 '20240401' 的字串
>.後設資料函式.
後設資料函式主要是返回與資料庫相關的資訊.它包括以下幾個方面
>.返回資料庫中資料表檢視的個數和名稱.
>.返回資料表中資料欄位的名稱,資料型別,長度等描述資訊.
>.返回資料表中定義 的約束,索引或者外來鍵等資訊.
它有由以下幾個函式:
>.COL_LENGTH: 資料列的定義長度
>.COL_NAME: 資料庫列的名稱
>.DB_NAME:資料庫名稱
-- 返回表[TableName]中列[ColumnName]的定義長度 SELECT COL_LENGTH('TableName', 'ColumnName'); -- 返回表[TableName]中第N列的列名 SELECT COL_NAME(object_id('TableName'), N); -- 返回當前資料庫的名稱 SELECT DB_NAME();