第六章 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();