SQL-函数

1 函数类型

• 标量函数

用户定义标量函数返回在 RETURNS子句中定义的类型的单个数据值
内联标量函数，返回的标量值是单个语句的结果
多语句标量函数，函数体可以包含一系列T-SQL语句，这些语句返回单个值
返回类型看是除 text、ntext、image、timestamp等以外任何数据类型

CREATE FUNCTION TestFun(@Sage )
RETURNS int
AS
BEGIN
	DECLARE @ret int;
	SELECT @ret = SUM(Sno) 
	FROM Student
	WHERE Sage >= @Sage;
	
	IF ( @ret IS NULL )
		SET @ret = 0;
	RETURN @ret;
END

• 表值函数
  用户定义的表值函数返回 table数据类型(表是单个SELECT语句的结果集)

CREATE FUNCTION TestFun1(@Sage)
RETURN TABLE
AS
RETURN(
	SELECT Sno,Sname,Sage,Ssex 
	FROM Student
	WHERE Sage >= @Sage
);

• 系统函数
  SQl Server提供了许多用于执行各种操作的系统函数。这些函数不能修改

2 用户自定义函数操作

2.1 内联标量

User-Defined函数(UDF)

• 单个语句标量UDF

-- 创建一个获取学生出生年份的函数
CREATE FUNTION TestFun2(@birthYear int)
RETRUN DECIMAL int,
AS
BEGIN
	RETURN datepart(yy,getdate())-@birthYear;
END
-- 调用UDF
SELECT Sno,Sname,TestFun2(Sage),Ssex FROM Student;

• 多语句标量UDF

-- 定义一个判断学生年龄的函数
CREATE FUNCTION TestFun3(@Sage INT)
RETURN char(10)
AS
BEGIN
	DECLARE @String char(10);
	IF @Sage < 20
		SET @String = '00后';
	ELSE IF @Sage < 30
		SET @String = '90后';
	ELSE
		SET @String = '较大年龄';
	RETURN @String;
END

-- 调用UDF
SELECT Sno,Sname,TestFun2(Sage),Ssex FROM Student;

2.2 修改函数

ALTER FUNCTION 函数名
函数体…

ALTER FUNCTION TestFun()
RETURN [datetime]
AS
BEGIN
	RETURN DATEADD(millisecond,-2,CONVERT(datetime,'20040701',112));
END

2.3 删除函数

语法:
DROP FUNCTION 函数名

语法:

	DROP FUNCTION 函数名
示例:

DROP FUNCTION TestFun;

2.4 重命名函数

无法使用 Transact-SQL 语句执行此任务。 
若要使用 Transact-SQL 重命名用户定义函数，
	必须首先删除现有的函数，
	然后用新名称重新创建函数。 

2.5 查看函数

• 获取函数的定义和属性

-- 查看函数名、定义、相关属性
SELECT sm.object_id,OBJECT_NAME(sm.object_id) AS object_name,
	o.type
	o.type_desc,
	sm.definition,
	sm.use_ansi_nulls,
	sm.users_quoted_identifier,
	sm.is_schema_bound,
	sm.execute_as_principal_id
	FROM sys.sql_modules AS sm
	JOIN sys.object AS o ON sm.object_id = o.object_id
	WHERE sm.object_id = OBJECT_ID('TestFun')
	ORDER BY o.type;
GO

3 系统函数

函数类别	作用
聚合函数	将多个值合并成一个值
转换函数	将值从一种数据类型转换为另一种
日期和时间函数	可以更改日期和时间的值
数学函数	执行三角、几何等其他数学运算
字符串函数	可更改char、varchar、nchar、nvarchar、binary、varbinary值

3.1 聚合函数

-- 1. COUNT() 函数统计行个数
SELECT COUNT(Sno) FROM Student; -- 统计学生人数

-- 2. SUM() 函数统计行数据和
SELECT SUM(Sage) FROM Student; -- 通过全校学生年龄和

-- 3. AVG() 函数统计行数据平均值
SELECT AVG(Sage) FROM Student; -- 计算全校学生年龄平均值

-- 4. MAX(),MIN()函数计算行数据的最大值和最小值
SELECT MAX(Sage),MIN(Sage) FROM Student; -- 计算全校最大和最小年龄

3.2 转换函数

Select CONVERT(varchar(100), GETDATE(), 1);--01/08/18
Select CONVERT(varchar(100), GETDATE(), 101);--01/08/2018

3.4 日期和时间函数

-- GETDATE

-- DATEADD DATEIFF日期偏移
SELECT DATEADD(month, 1, '20220429');
SELECT DATEADD(month, 1, '2022-04-29');
-- DATEDIFF ( datepart , startdate , enddate )  
SELECT DATEDIFF(year,        '2022-04-29 23:59:59.9999999', '2023-01-01 00:00:00.0000000');
SELECT DATEDIFF(month,     '2022-04-29 23:59:59.9999999', '2023-01-01 00:00:00.0000000');

3.4 数学函数

-- 1. ABS(X) 绝对值函数
SELECT ABS(-2.0),ABS(2);

-- 2. PE() 返回圆周率的函数
SELECT PI();

-- 3.SQRT() 平方根函数
SELECT SQRT(9),SQRT(11);

-- 4.RAND()和RAND(X) 函数，返回一个随机浮点值(0<=n<=1.0)
SELECT RAND(),RAND(),RAND(); 
SELECT RAND(5),RAND(5),RAND(5);

-- 5.ROUND(X,Y)函数，四舍五入，返回最接近于参数X的值
-- 并保留到小数点后Y位
SELECT ROUND(3333.3333,2);

-- 6. SIGN(X)函数，返回参数的符号
SELECT SIGN(3),SIGN(0),SING(-3),SIGN(3.33),SIGN(-3.33);

3.5 字符串函数

--left(string,int) 从左向右去截取指定长度串
SELECT LEFT('abcdef',3); -- abc

-- RIGHT 于LEFT效果相反
SELECT RIGHT('abcdef',4); -- cdef

-- SUBSTRING 从指定位置，截取指定长度的串
SELECT SUBSTRING('abcdefg',2,4); --cdef