个人备案域名可以做企业网站吗,网站建设课程设计心得体会,网站建设功能列表,宁波网站优化公司价格说到函数#xff0c;首先和存储过程作个比较吧#xff0c;两者有一个共同点都是预编译优化后存储在磁盘中#xff0c;所以效率 要比T-SQL高一点点。值得注意的是#xff0c;存储过程可以创建或访问临时表#xff0c;而函数不可以#xff1b;
同时函数不可 以修改表中的数…说到函数首先和存储过程作个比较吧两者有一个共同点都是预编译优化后存储在磁盘中所以效率 要比T-SQL高一点点。值得注意的是存储过程可以创建或访问临时表而函数不可以
同时函数不可 以修改表中的数据或调用产生副作用的函数比如rand,newid,getdate当然这并不是绝对的但 是函数可以作为select 或from或where子句的一部分而存储过程不可以。
系统函数
1 字符串函数
1.1 长度与分析用
datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
substring(expression,start,length) 不多说了,取子串
right(char_expr,int_expr) 返回字符串右边int_expr个字符
left(char_expr,int_expr) 返回字符串左边int_expr个字符1.2 字符操作类
upper(char_expr) 转为大写
lower(char_expr) 转为小写
space(int_expr) 生成int_expr个空格
replicate(char_expr,int_expr)复制字符串int_expr次
reverse(char_expr) 反转字符串
stuff(char_expr1,start,length,char_expr2) 将字符串char_expr1中的从
start开始的length个字符用char_expr2代替
ltrim(char_expr) rtrim(char_expr) 取掉空格
ascii(char) char(ascii) 两函数对应,取ascii码,根据ascii码取字符
1.3 字符串查找
charindex(char_expr,expression) 返回char_expr的起始位置
patindex(%pattern%,expression) 返回指定模式的起始位置,否则为0
2、数学函数
abs(numeric_expr) 求绝对值。
ceiling(numeric_expr) 取大于等于指定值的最小整数即向上取整。
floor(numeric_expr) 小于等于指定值得最大整数即向下取整。
avgnumeric_expr取平均数。
exp(float_expr) 返回e的n次方。
pi() 3.1415926.........即圆周率π。
power(底数m指数n) 返回m的n次方。
rand([int_expr]) 随机数产生器。
round(numeric_expr,int_expr) 按照int_expr规定的精度四舍五入。
sign(int_expr) 根据正数,零,负数,返回1,0,-1。
sqrt(float_expr) 返回平方根。3、日期时间函数
getdate() 返回日期
datename(datepart,date_expr) 返回名称如 June
datepart(datepart,date_expr) 取日期一部份
datediff(datepart,date_expr1.dateexpr2) 日期差
dateadd(datepart,number,date_expr) 返回日期加上 number
上述函数中datepart的
写法 取值和意义
yy 1753-9999 年份
qq 1-4 刻
mm 1-12 月
dy 1-366 日
dd 1-31 日
wk 1-54 周
dw 1-7 周几
hh 0-23 小时
mi 0-59 分钟
ss 0-59 秒
ms 0-999 毫秒
日期转换
convert()4、系统其他函数
suser_name() 用户登录名
user_name() 用户在数据库中的名字
show_role() 对当前用户起作用的规则
db_name() 数据库名
object_name(obj_id) 数据库对象名
col_name(obj_id,col_id) 列名
col_length(objname,colname) 列长度
valid_name(char_expr) 是否是有效标识符
5、类型转换函数
convert (数据类型[(长度)]表达式[样式])将一种数据类型的表达式显式转换为另一种数据类型的表达式
长度如果数据类型允许设置长度可以设置长度例如 varchar(10);
样式用于将日期类型数据转换为字符数据类型的日期格式的样式。cast (表达式 AS 数据类型[(长度)])将一种数据类型的表达式显式转换为另一种数据类型的表达式。
例如select cast(123 as nvarchar) 返回123
select N年龄 cast(23 as nvarchar) 返回 年龄236、系统函数
newid 无参数
返回一个GUID(全局唯一表示符)值
例如select newid()
返回2E6861EF-F4DB-4FFE-86EB-637482FE982J2isnumeric (任意表达式)
判断表达式是否为数值类型或者是否可以转换成数值。
是返回1不是返回0
例如select isnumeric(1111) 返回 1
select isnumeric(123rr) 返回 0
select isnumeric(123) 返回 1isnull (任意表达式1任意表达式2)
如果任意表达式1不为NULL则返回它的值否则在将任意表达式2的类型转换为任意表达式1的类型(如果这两个类
型不同)后返回任意表达式2的值。
例如select isnull(null,N没有值) 返回 没有值
select isnull(N具体的值N没有值) 返回 具体的值isdate (任意表达式)
确定输入表达式是否为有效日期或可转成有效的日期
是返回1不是返回0
例如select isdate(getdate()) 返回1
select isdate(2013-01-02) 返回1
select isdate(198) 返回07、排名函数
row_number 无参数
为结果集内每一行进行编号从1开始后面行依次加1常用于产生序号
例如select row_number() over(order by userid desc) as [NO],username,password from T_USER
8、聚合函数
count()
返回组中的总条数count(*)返回组中所有条数包括NULL值和重复值项如果抒写表达式则忽略空值表达式为任意表达式。max()
返回组中的最大值空值将被忽略表达式为数值表达式字符串表达式日期。min()
返回组中的最小值空值将被忽略表达式为数值表达式字符串表达式日期。sum()
返回组中所有值的和空值将被忽略表达式为数据表达式。avg()
返回组中所有值的平均值空值将被忽略表达式为数据表达式。
表值函数返回一张表比如returns table as ... return select ....
标量值函数返回当个数据类型的值除BLOB、游标、时间戳比如returns int as ... return var;
标量值函数
标量值函数返回当个数据类型的值除BLOB、游标、时间戳比如returns int as ... return var;
语法
--声明数据库引用
use 数据库名;
go--判断是否存在需要创建的函数如果存在则删除if exists(select * from sys.objects where name函数名称)
drop function 函数名称;
go
--创建用户自定义函数
create function [schema_name.] function_name
(parameter_name [as] [type_schema_name.] parameter_data_type [ default ] [ readonly],..n)
returns return_data_type
[with] [encryption][,][schemabinding][,][returns null on null input | called on null input][,][execute
as Clause]as
begin
function_body;
return scalar_expression;
end
go语法解析
--schema_name
--用户定义函数所属的架构的名称。--function_name
--用户定义函数的名称。 函数名称必须符合标识符规则并且在数据库中以及对其架构来说是唯一的,即使未指定参数函数名称后也需要加上括号。--parameter_name
--用户定义函数中的参数。 可声明一个或多个参数。
--一个函数最多可以有 2,100 个参数。 执行函数时如果未定义参数的默认值则用户必须提供每个已声明参数的值。
--通过将 at 符号 () 用作第一个字符来指定参数名称。 参数名称必须符合标识符规则。 参数是对应于函数的局部参数其他函数中可使用相同的参数名称。
--参数只能代替常量而不能用于代替表名、列名或其他数据库对象的名称。--[ type_schema_name. ] parameter_data_type
--参数的数据类型及其所属的架构后者为可选项。 对于 Transact-SQL 函数允许使用除 timestamp 数据类型之外的所有数据类型包括 CLR 用户定义类型和用户定义表类型。
--对于 CLR 函数允许使用除 text、ntext、image、用户定义表类型和 timestamp 数据类型之外的所有数据类型。
包括 CLR 用户定义类型。 在 Transact-SQL 函数或 CLR 函数中
--不能将非标量类型 cursor 和 table 指定为参数数据类型。
--如果未指定 type_schema_name 数据库引擎会按以下顺序查找 scalar_parameter_data_type
--包含 SQL Server 系统数据类型名称的架构。
--当前数据库中当前用户的默认架构。
--当前数据库中的 dbo 架构。--[ default ]
--参数的默认值。 如果定义了 default 值则无需指定此参数的值即可执行函数。
--如果函数的参数有默认值则调用该函数以检索默认值时必须指定关键字 DEFAULT。 此行为与在存储过程中使用具有默认值的参数不同在后一种情况下不提供参数同样意味着使用默认值。
--但在通过使用 EXECUTE 语句调用标量函数时DEFAULT 关键字不是必需的。
--readonly
--指示不能在函数定义中更新或修改参数。 如果参数类型为用户定义的表类型则应指定 READONLY。
--return_data_type
--标量用户定义函数的返回值。 对于 Transact-SQL 函数可以使用除 timestamp 数据类型之外的所有数据类型包括 CLR 用户定义类型。
--对于 CLR 函数允许使用除 text、ntext、image 和 timestamp 数据类型之外的所有数据类型包括 CLR 用户定义类型。 在 Transact-SQL 函数或 CLR 函数中不能将非标量类型 cursor 和 table 指定为返回数据类型。--encryption
--适用范围 SQL Server 2008 到 SQL Server 2017。
--指示 数据库引擎会将 CREATE FUNCTION 语句的原始文本转换为模糊格式。 模糊代码的输出在任何目录视图中都不能直接显示。 对系统表或数据库文件没有访问权限的用户不能检索模糊文本。
--但是可以通过 DAC 端口访问系统表的特权用户或直接访问数据库文件的特权用户可以使用此文本。 此外能够向服务器进程附加调试器的用户可在运行时从内存中检索原始过程。
--使用此选项可防止将函数作为 SQL Server 复制的一部分发布。 不能为 CLR 函数指定此选项。--schemabinding
--指定将函数绑定到其引用的数据库对象。 如果指定了 SCHEMABINDING则不能按照将影响函数定义的方式修改基对象。 必须首先修改或删除函数定义本身才能删除将要修改的对象的依赖关系。
--returns null on null input | called on null input
--指定标量值函数的 OnNULLCall 属性。 如果未指定则默认为 CALLED ON NULL INPUT。 这意味着即使传递的参数为 NULL也将执行函数体。
--如果在 CLR 函数中指定了 RETURNS NULL ON NULL INPUT它指示当 SQL Server 接收到的任何一个参数为 NULL 时它可以返回 NULL而无需实际调用函数体。 如果 method_specifier 中指定的 CLR 函数的方法已具有指示 RETURNS NULL ON NULL INPUT 的自定义属性
--但 CREATE FUNCTION 语句指示 CALLED ON NULL INPUT则优先采用 CREATE FUNCTION 语句指示的属性。 不能为 CLR 表值函数指定 OnNULLCall 属性。--execute as 子句
--对于本机编译的标量用户定义函数EXECUTE AS 是必需的。
--SELF
--EXECUTE AS SELF 与 EXECUTE AS user_name 等价其中指定用户是创建或更改模块的用户。 创建或更改模块的用户的实际用户 ID 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。--OWNER
--指定模块内的语句在模块的当前所有者上下文中执行。 如果模块没有指定的所有者则使用模块架构的所有者。 不能为 DDL 或登录触发器指定 OWNER。-- user_name
--指定模块内的语句在 user_name 指定的用户的上下文中执行。 将根据 user_name 来验证对模块内任意对象的权限。 不能为具有服务器作用域的 DDL 触发器或登录触发器指定 user_name。 请改用 login_name。--user_name 必须存在于当前数据库中并且必须是单一实例帐户。 user_name 不能为组、角色、证书、密钥或内置
帐户如 NT AUTHORITYLocalService、NT AUTHORITYNetworkService 或 NT AUTHORITYLocalSystem。
--执行上下文的用户 ID 存储在元数据中可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。-- login_name
--指定模块内的语句在 login_name 指定的 SQL Server 登录的上下文中执行。 将根据 login_name 来验证对模块内任意对象的权限。 只能为具有服务器作用域的 DDL 触发器或登录触发器指定 login_name。--login_name 不能为组、角色、证书、密钥或内置帐户如 NT AUTHORITYLocalService、NT
AUTHORITYNetworkService 或 NT AUTHORITYLocalSystem。
--function_body
--指定一系列定义函数值的 Transact-SQL 语句这些语句在一起使用不会产生负面影响例如修改表。
function_body 仅用于标量函数和多语句表值函数。
--在标量函数中function_body 是一系列 Transact-SQL 语句这些语句一起使用可计算出标量值。
--在多语句表值函数中function_body 是一系列 Transact-SQL 语句这些语句将填充 TABLE 返回变量。--scalar_expression
--指定标量函数返回的标量值。示例
--声明数据库引用
use testss;
go--判断是否存在需要创建的函数如果存在则删除
if exists(select * from sys.objects where namescalarfun)
drop function scalarfun;
go--创建用户自定义函数
create function dbo.scalarfun
(num1 int,num2 int,num3 int null)
returns int
--with encryption,schemabinding,returns null on null input,execute as owner
as
begin
declare sum1 intnull;
if (num1 is not null) and (num2 is not null)
set sum1num1num2;
else if (num1 is not null)
set sum1num1;
else if (num2 is not null)
set sum1num2;
else
set sum10;
return sum1;
end
go表值函数
表值函数返回一张表比如returns table as ... return select ....
use LearnEdu
go
if exists(select * from sys.objects where nameuseridList)
drop function mySub
go
create function dbo.useridList
(parameter1 int
) returns UserId table(userid int)
as
begininsert UserId select Id from SysUser where companyIdparameter1;return;
end
调用
select * from useridList(1)
