【SQL server数据库基础】内置函数与用户定义函数

1. 系统内置函数

① 转换函数

cast 转换

select cast('11.43' as float)  -- 带引号为字符串，转为浮点数
-- CAST (expression AS data_type [ (length) ]) 
-- expression 为任意有效的表达式

convert 转换

convert(float,'11.43')
-- CONVERT (data_type [ ( length ) ] , expression [ , style ] )

② 数字函数

ceiling 向上取整

select ceiling(5.6)

floor 向下取整

select floor(5.6)

round 四舍五入

select round(2.451,2) -- 2.451 是数字，2是小数点后2位，-2是小数点前2位
-- ROUND(number, digits)

abs 绝对值

select abs(-30)

③ 字符串函数

substring 取字符串子串

-- 学生信息表姓王的学生的学号、姓名和性别
select StdInfoNUm,StdINfoName,StdInfoSex
from dbo.StudentInfo
where substring(StdInfoName,1,1) = '王'  -- 取StdInfoName字段，从第一位取一位
-- SUBSTRING ( value_expression ,start_expression , length_expression )
select StdInfoNum,StdInfoName,StdInfoSex
from dbo.StudentInfo
where StdInfoName like '王%'

④ 日期时间函数

DATEDIFF 计算时间间隔 ( 日期类型, 开始日期, 结束日期 )

-- 查询学生信息表中年龄在20-25岁的学生、姓名、性别和年龄
select * from (
	select StdInfoNum,StdInfoName,StdInfoSex,
    datediff(year,StdInfoBirthD,getdate()) as age
    from dbo.StudentInfo) as temp
    where age between 20 and 25
)

2. 用户自定义函数

① 标量函数

标量函数返回一个值

-- 创建标量函数，统计指定院系的教师人数
create function Func_TitleSum(@DepName varchar(20)) -- 函数需要输入参数，在括号里面写上输入参数以及参数的类型
returns int -- 标量函数返回一个值，返回类型是int
as
begin
	daclare @num int
	select @num = count(*) from dbo.TeachInfo
	where DepInfoID = (
    	select DepInfoID from dbo.DepInfo
        where DepInfoName = @DepName
    return @num
    )
end
-- 调用函数
daclare @name varchar(20) = '信息工程系'
select dbo.Func_TitleSum(@name)

② 表值函数

表值函数返回一个表，又分为：

​ ① 内联表值函数 ：只能包含一个 select 语句，返回单个查询结果，没有相关联的返回变量，也没有函数体

create function 函数名(输入参数 参数类型，···)
returns table
as
return (···)

​ ② 多语句表值函数：可以包含多个 select 语句，存在函数体，可以在 select 语句的 from 子句中使用多语句表值函数

create function 函数名(输入参数 参数类型，···)
returns @返回变量 table  -- 返回变量是一个表
(返回字段 字段类型, ···)
as
begin
	insert @返回变量  -- 往变量里面插入数据
	函数体（select 查询语句）
	return
end

-- 创建内联表值函数，用来查询指定院系的教师信息
create function FselTeach(@depname varchar(20))
returns table -- 返回一个表
as
return (
	select TeachInfoNum,TeachInfoName,TeachInfoKnowl,TeachInfoTitle
    from dbo.TeachInfo
    where DepInfoID = (
    	select DepInfoID from dbo.DepInfo
        where DepInfoName = @depname
    )
)
-- 调用
declare @d varchar(20) = '信息工程系'
select * from FSelTeach(@d)

-- 创建多语句表值函数，用来查询指定教师任教的课程名称、实践课时、理论课时和总课时
create function FSelCourse(@teachName varchar(20))
returns @teach_Course table  -- @teach_Course 指定返回的表名
(
	课程名称 varchar(80),
    理论学时 int,
    实践学时 int,
    总学时 int
)
as
begin
	insert @teach_Course
	select CourseInfoName,COurseInfoRstPer,CourseInfoPraPer,COurseInfoRstPer+CourseInfoPraPer
	from dbo.CourseInfo c join dbo.TeachInfo a join dbo.TeachCourse b
	on a.TeachInfoID = b.TeachInfoID
	on c.CourseInfoID = b.CourseInfoID
	where TeachInfoName = @teachName
	return
end
-- 调用
select * from FSelCourse('朱志奇')

主文章：备考计算机三级数据库——SQL 案例