SQL Server基础四---函数

————ascii用于将第一个字符转换成相对应的ascii值
print ascii(‘乐’)
print ascii(‘秀’)
if ascii(‘乐乐’)>ascii(‘秀秀’)
begin
print ‘乐乐比秀秀大’
end
else
print ‘秀秀比乐乐大’

—————char把一个ASCII码转换成对应的字符
print char(‘97’)

——test
select * from Users where possword=’123456’

——————————replace——替换
print replace(‘你们喜欢的是xxx，传说xxx真是一个好人呀’,’xxx’,’嘉嘉’)

—————两个转换————-cast\convert————————-
declare @a varchar(50)
set @a=’20220308’
print cast(@a as date)

————————————————————————-
declare @b int
set @b=’100’
print ‘结果是：’+convert(varchar(10),@b)

—————————————-
declare @a datetime
set @a=getdate()
print convert(varchar(50),@a,120)

——————-利用循环输出A-Z大写字母及其现应的ASCII码值
declare @a int
set @a=65
while @a<91
begin
print char(@a) +’的ASCII码值是:’+cast(@a as varchar(20))
set @a=@a+1
end

select str (price,10,2)from Product order by Price asc

select Customername,len(Customername)as[length] from Customer

———————-日期相关的函数
print getdate()
print getutcdate()
————day,month,year—
declare @today datetime
set @today=getdate()
print @today
print year(@today)
print month(@today)
print day(@today)

—dateadd——-增减时间
print dateadd(dd,1,getdate())
print convert(varchar(50),dateadd(yyyy,-21,getdate()),120)

—datediff——-返回指定日期之间的部分
declare @birthday datetime
set @birthday=’2001-06-10’
print datediff (dd,@birthday,getdate())

—-你预计你能活到多少岁，你算算你还剩多少天？
declare @sr datetime
set @sr =’2101-06-10’
print datediff(dd,getdate(),@sr)

——-用户自定义函数UDF(标量函数、表值函数)—-
——-标量函数（返回其他）

—加法
create function myadd
(
@a int,
@b int
)
returns int
as
begin
return @a+@b
end

print [dbo].myadd

—————-我想看看指定的用户在当前系统中花费了多少钱？
create function GetMoneyByName
(
@name varchar(50)
)
returns money
as
begin
declare @total money
SELECT @total=sum(OrderDetails.Quantity*Product.Price)
FROM Customer INNER JOIN
Orders ON Customer.CustomerID = Orders.CustomerID INNER JOIN
OrderDetails ON Orders.OrderID = OrderDetails.OrderID INNER JOIN
Product ON OrderDetails.ProductID = Product.ProductID
WHERE (Customer.CustomerName=@name)
if @total is null
begin
return 0
end
return @total
end
print dbo.GetMoneyByName(‘小艾’)

create function days
(
@birthday datetime
)
returns int
as
begin
declare @time int,@deaday datetime,@day int
set @time=80
set @deaday=convert(varchar(50),dateadd(yy,@time,@birthday),120)
set @day=datediff(dd,getdate(),@deaday)
return @day
end
print dbo.days(‘2001-01-10’)

————表值函数（返回表）
————定义一个方法，获得所有address在‘武汉’的人的信息
create function GetCustomerFromAddress
(
@city varchar(50)
)
returns table
as
return selectfrom Customer where Address like ‘%’+@city+’%’
select from GetCustomerFromAddress(‘武汉’)

create function daydiff
(
@datea datetime,
@dateb datetime
)
returns varchar(50)
as
begin
if(@datea>@dateb)
begin
declare @datec datetime
set @datec=@datea
set @datea=@dateb
set @dateb=@datec
end
return cast(datediff(dd,@datea,@dateb)as varchar(50))+’天’
end
print dbo.daydiff(‘2001-01-10’,’2022-03-09’)