-- ╔══════════╗
-- ===================================== ║ Cast 函数转换 ║
-- ╚══════════╝
-- CAST（） 函数语法如下：
-- CAST （<expression> AS <data_ type>[ length ]）
/*
【0】、convert在进行日期转换时还提供了丰富的样式，cast只能进行普通的日期转换
【1】、data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。
【2】、length用于指定数据的长度，缺省值为30。
*/
-- ★ 实例 ★
select convert(nvarchar,123) --返回123
select N'年龄：'+convert(nvarchar,23)
-- 返回 年龄：23（注意：如果想要在结果中正确显示中文需要在给定的字符串前面加上N，加N是为了使数据库识别 Unicode字符）
select convert(nvarchar ,getdate())
-- 返回04 28 2009 10:21PM
select convert(nvarchar ,getdate(),101)
-- 返回04/28/2009
select convert(nvarchar ,getdate(),120)
-- 返回2009-04-28 12:22:21
select convert(nvarchar(10) ,getdate(),120)
-- 返回2009-04-28
-- ╔══════════╗
-- ================================= ║ CONVERT 函数转换 ║
-- ╚══════════╝
-- CONVERT（） 函数语法如下：
-- CONVERT （<data_ type>[ length ]， <expression> [， style]）
/*
【0】、convert在进行日期转换时还提供了丰富的样式，cast只能进行普通的日期转换
【1】、data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。
【2】、length用于指定数据的长度，缺省值为30。
*/
-- ★ 实例 ★
select cast(123 as nvarchar) -- 返回123
select N'年龄:'+cast(23 as nvarchar) -- 返回 年龄：23
-- ╔══════════╗
-- ==================================== ║ 参 数 说 明 ║
-- ╚══════════╝
/*
3、把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号（+）或负号（-）的数值。
4、TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。
5、IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。
6、把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。
7、BIT类型的转换把非零值转换为1，并仍以BIT类型存储。
8、试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。
*/
select N'年龄：'+convert(nvarchar,23)
-- 返回 年龄：23（注意：如果想要在结果中正确显示中文需要在给定的字符串前面加上N，加N是为了使数据库识别 Unicode字符）
-- ╔════════╗
-- ============================================ ║ 系 统 函 数 ║
-- ╚════════╝
系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数，下面我们通过案例对重要的系统函数进行注意的介绍。
实验： 系统函数应用实验
--该部分函数主要解决如何获取SQL-sever的系统信息。
1、COL_LENGTH(expression)：返回列的定义长度（以字节为单位）。
2、DATALENGTH(expression)：返回任何表达式所占用的字节数。
--例1：col_length （）函数的使用
Use sample
Go
Select col_length(’员工数据表’, ’姓名’) as name_data_length ,
Datalength(’姓名’) as name_data_length from 员工数据表
--（注解： col_length （）函数可以返回列的长度）
3、ISNUMERIC(expression)/返回类型 int：确定表达式是否为一个有效的数字类型。
--例2：ISNUMERIC （）函数的使用
USE school
SELECT ISNUMERIC(sno) FROM student
GO
4、USER_ID（）：返回用户的数据库标识号。
5、USER_NAME（）：返回给定标识号的用户数据库用户名。
USER_NAME ( [ id ] )id：用来返回用户名的标识号。id 的数据类型为 int，注意当省略 id 时，则假定为当前用户。必须加上圆括号。
--例3：USER_ID（）函数的使用
SELECT USER_ID('MY-TOMATO') , USER_NAME('MY-TOMATO')
----系统函数----
APP_NAME() --函数返回当前执行的应用程序的名称
COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值
COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
COL_NAME(<table_id>, <column_id>) --函数返回表中指定字段的名称即列名
DATALENGTH() --函数返回数据表达式的数据的实际长度
DB_ID(['database_name']) --函数返回数据库的编号
DB_NAME(database_id) --函数返回数据库的名称
HOST_ID() --函数返回服务器端计算机的名称
HOST_NAME() --函数返回服务器端计算机的名称
IDENTITY(<data_type>[, seed increment]) [AS column_name])
--IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE() --函数判断所给定的表达式是否为合理日期
ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL
-- 值用指定值替换
ISNUMERIC() --函数判断所给定的表达式是否为合理的数值
NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值
NULLIF(<expression1>, <expression2>)
--NULLIF 函数在expression1 与expression2 相等时返回NULL
-- 值若不相等时则返回expression1 的值
-- ╔═══════╗
-- =========================================== ║ 字符串函数 ║
-- ╚═══════╝
字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型，字符串函数可以分为以下几大类：
(1) 基本字符串函数：UPPER，LOWER，SPACE，REPLICATE，STUFF，REVERSE，LTRIM，RTRIM。
(2) 字符串查找函数：CHARINDEX，PATINDEX。
(3) 长度和分析函数：DATALENGTH，SUBSTRING，RIGHT。
(4) 转换函数：ASCH，CHAR，STR，SOUNDEX，DIFFERENCE。
下面我们通过案例对重要的字符串函数进行重点的介绍。
实验： 字符串函数应用实验
--该部分函数主要解决各种字符串的处理问题
1、CHAR（数字变量）
功能：将ASC码转换成为字符串；
--注意：ASC码是指0——255之间的整数
例子：select char(56)
2、LEFT（字符串表达式，整数）
功能：返回从字符串左边开始多少个字符
例子：select left(sname,2) from student
3、LTRIM函数和RTRIM函数
功能：删除字符串的前导空格与后导空格。
例子：insert into student(sno,sname) values(990,' 看看空格 ')
select sname from student where sname like '%看看空格%'
select LTRIM(sname) from student where sname like '%看看空格%'
select RTRIM(sname) from student where sname like '%看看空格%'
select RTRIM(LTRIM(sname)) from student where sname like '%看看空格%'
--注意：去除前后导空格一般通过RTRIM(LTRIM(查询字符串))联合使用完成。
4、REPLACE（’第一个字符串’，’第二个字符串’，’第三个字符串’）
功能：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式
例如：SELECT REPLACE('我是玉树临风的高狗熊','高狗熊','周星驰')
5、SUBSTRING(表达式，开始点，结束点)
功能：返回字符、binary、text 或 image 表达式的一部分。
例如：SELECT SUBSTRING('我是玉树临风的高狗熊',3,4)
6、CAST与CONVERT函数
功能：实现数据的格式转化；将某种数据类型的表达式显式转换为另一种数据类型。
CAST 和 CONVERT 提供相似的功能
使用 CAST：CAST ( expression AS data_type )
使用 CONVERT：CONVERT (data_type[(length)], expression [, style])
例如：select CONVERT(varchar(10),123)+'100'
select Cast(‘123’ as int)+ 100
7、LEN(string_expression)
功能：返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格
例如：select LEN(' 我是玉树临风的高狗熊 ')
8、LOWER()
功能：将大写字符数据转换为小写字符数据后返回字符表达式
例如：select UPPER('dsfgdfghtyuj')
9、UPPER()
功能：返回将小写字符数据转换为大写的字符表达式。
例如：create table titles(title varchar(24),price money)
insert into titles values('PPd',3.63)
SELECT LOWER(SUBSTRING(title, 1, 20)) AS Lower, UPPER(SUBSTRING(title, 1, 20)) AS Upper, LOWER(UPPER(SUBSTRING(title, 1, 20))) As LowerUpper FROM titles WHERE price between 1.00 and 200.00
10、CHARINDEX ( expression1 , expression2 [ , start_location ] )
功能：返回字符串中指定表达式的起始位置。
例如：SELECT CHARINDEX('不', sname) FROM student
select sname from student
declare @a varchar(200),@b varchar(200),@c int
set @a='aaa'
set @b='ssaaauuaaa'
set @c=0
set @c=CHARINDEX(@a,@b,4) -- 从起始位置开始搜索
print @c
11、REPLICATE ( character_expression , integer_expression )
功能：以指定的次数重复字符表达式。
例如：declare @c varchar(12)
set @c='我是谁'
SELECT REPLICATE(@c, 4)
12、REVERSE ( character_expression )
功能：返回字符表达式的反转。
例如：declare @c varchar(62)
set @c='请问你谁是周星驰啊'
SELECT REVERSE(@c)
13、STUFF ( character_expression , start , length , character_expression )
功能：删除指定长度的字符并在指定的起始点插入另一组字符。
--例如：SELECT STUFF('请问你谁是周星驰啊', 6, 3,'')
-- ╔════════╗
-- ============================== ║ 日期时间函数 ║
-- ╚════════╝
日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用日期和时间函数，如表2-5所示为日期时间函数的基本内容。
表2-5 标量函数的基本分类
函数
参数
功能
DATEADD
(datepart,number,date)
以datepart指定的方式，返回date加上number之和
DATEDIFF
(datepart,date1,date2)
以datepart指定的方式，返回date2与date1之差
DATENAME
(datepart,date)
返回日期date中datepart指定部分所对应的字符串
DATEPART
(datepart,date)
返回日期date中datepart指定部分所对应的整数值
DAY
(date)
返回指定日期的天数
GETDATE
()
返回当前的日期和时间
MONTH
(date)
返回指定日期的月份数
YEAR
(date)
返回指定日期的年份数
实验：日期时间函数实验
1、DATEADD ( datepart , number, date )
功能：在向指定日期加上一段时间的基础上，返回新的 datetime 值。
例如：USE school
SELECT DATEADD(day, 21, birthday) AS stu_biradd FROM student
2、DATEDIFF ( datepart , startdate , enddate )
功能：返回跨两个指定日期的日期和时间边界数。
例如：SELECT DATEDIFF(year, birthday, getdate()) AS 年龄 FROM student
3、DATENAME( datepart , date )
功能：返回代表指定日期的指定日期部分的字符串。
例如：SELECT DATENAME(month, birthday) AS '出生月' from student
4、DATEPART( datepart , date )
功能：返回代表指定日期的指定日期部分的整数。
例如：SELECT DATEPART (year, birthday) AS '出生月' from student
5、year(),month(),day()
功能：返回年月日
6、GETDATE（）函数
功能：返回今天的日期
例如：SELECT DATEPART(month, GETDATE()) AS 'Month Number'
SELECT DATEPART(day, GETDATE()) AS 'day Number'
SELECT DATEPART(year, GETDATE()) AS 'year Number'
-- ╔═══════╗
-- =============================== ║ 数学函数 ║
-- ╚═══════╝
数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQL Server提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行处理，具体解释见下面的实验内容。
实验：数学函数实验
可以使用数学函数执行各种算术或函数运算
1、ABS()函数（绝对值）
功能：精确数字或近似数字数据类型类别的表达式（bit 数据类型除外）。
例如：SELECT ABS(-2147483648)
2、CEILING()(取整函数)
功能：返回大于或等于所给数字表达式的最小整数。
例如：SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
3、FLOOR() (取整函数)
功能：返回小于或等于所给数字表达式的最大整数。
例如：SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)
注意： CEILING 和 FLOOR函数的差别是：
CEILING 函数返回大于或等于所给数字表达式的最小整数。FLOOR 函数返回小于或等于所给数字表达式的最大整数。例如，对于数字表达式 12.9273，CEILING 将返回 13，FLOOR 将返回 12。FLOOR 和 CEILING 返回值的数据类型都与输入的数字表达式的数据类型相同
4、ROUND()(四舍五入函数)
功能：返回数字表达式并四舍五入为指定的长度或精度。
语法：ROUND ( numeric_expression , length [ , function ] )
例如：下例显示两个表达式，说明使用 ROUND 函数且最后一个数字始终是估计值。
SELECT ROUND(123.9994, 3),ROUND(123.9995, 3)
SELECT ROUND(123.4545, 2),ROUND(123.45, -2)
5、sign(n)
功能： 当n>0, 返回1，n=0,返回0，n<0, 返回-1
例如：DECLARE @value real
SET @value = -1
WHILE @value < 2
BEGIN
SELECT SIGN(@value)
SELECT @value = @value + 1
END
6、RAND ( [ seed ] )
功能：返回 0 到1 之间的随机float 值。
例如：DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
BEGIN
SELECT RAND(@counter) Random_Number
SET @counter = @counter + 1
END
-- ╔════════╗
-- ============== ║ 数据库汇总函数 ║
-- ╚════════╝
/*
sql函数包括如下：
avg函数：计算查询中某一特定字段资料的算术平均值。
count函数：计算符合查询条件的记录数。
min, max函数：传回指定字段之中符合查询条件的最小值、最大值。
first, last函数：传回指定字段之中符合查询条件的第一条、最末条记录的资料。
stdev函数：计算指定字段之中符合查询条件的标准差。
sum函数：计算指定字段之中符合查询条件的资料总和。
var,函数：计算指定字段之中符合查询条件的变异数估计值。
*/
-- ╔═══════╗
-- ================================ ║ 行集函数 ║
-- ╚═══════╝
行集函数可以在Transact-SQL语句中当作表引用。下面的案例将通过行集函数OPENQUERY()执行一个分布式查询，以便从服务器local中提取表department中的记录。
select * from openquery(local, ‘select * from department’)
2-3-3 Ranking函数
Ranking函数为查询结果数据集分区中的每一行返回一个序列值。依据此函数，一些行可能取得和其他行一样的序列值。如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。例如，如果两位顶尖销售员具有同样的SalesYTD（销售额）值，他们将并列第一。由于已有两行排名在前，所以具有下一个最大SalesYTD 的销售人员将排名第三。因此，RANK 函数并不总返回连续整数。Transact-SQL提供以下一些Ranking函数：RANK；DENSE_RANK；NTILE；ROW_NUMBER。
实验：Ranking函数实验
为了便于说明排序函数的使用，我们选取了school数据库中的teacher表中salary（薪水）字段作为排序的测试数据。我们首先运行一段SQL查询：select tno,name , salary From teacher，查询后的基本结构如图2-3所示。我们看见，分别有三位教师的薪水是一样高的。
1、ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
该函数将返回结果集分区内行的序列号，每个分区的第一行从 1 开始。row_number函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。row_number函数的用法如下面的SQL语句所示：
select row_number() over(order by salary) as row_number,tno,name, salary from teacher
其中row_number列是由row_number函数生成的序号列。在使用row_number函数是要使用over子句选择对某一列进行排序，然后才能生成序号。实际上，row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录进行排序，然后按着这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系，这两处的order by 可以完全不同，如下面的SQL语句所示：select row_number() over(order by salary asc) as row_number,tno,name, salary from teacher order by salary desc。
结果比较图如图2-5所示，请读者与图2-4进行数据比较。
图2-3 薪酬排序基本情况 图2-4 row_number函数排序 图2-5 row_number另一使用
我们可以使用Row_number函数来实现查询表中指定范围的记录，一般将其应用到Web应用程序的分页功能上。下面的SQL语句可以查询teacher表中第2条和第3条记录：
with teacher_rowtable
as
(select row_number() over(order by tno) as row_number,tno,name, salary from teacher)
select * from teacher_rowtable where row_number>1 and row_number < 4 order by tno
2、RANK( ) OVER ([< partition_by_clause>]<order_by_clause>)
该函数将返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。一个相关的SQL语句案例如下：select rank() over(order by salary) as ranker,tno,name,salary from teacher order by salary，结果如图2-6所示。
我们看到，如果使用rank函数来生成序号，其中有3条记录的序号是相同的，而第6条记录会根据当前的记录数生成序号，后面的记录依此类推，也就是说，在这个例子中，第6条记录的序号是6，而不是4。rank函数的使用方法与row_number函数完全相同。
3、DENSE_RANK ( ) OVER([<partition_by_clause>]<order_by_clause>)
该函数的功能与rank函数类似，只是在生成序号时是连续的，而rank函数生成的序号有可能不连续。如上面的例子中如果使用dense_rank函数，第6条记录的序号应该是4，而不是6。如下面的SQL语句所示：select dense_rank() over(order by salary)as ranker,tno,name,salary from teacher order by salary，结果如图2-7所示，读者可以比较图2-6和图2-7所示的查询结果有什么不同。
4、NTILE(integer_expression) OVER([<partition_by_clause>]<order_by_clause>)
ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数，用来指定桶数。下面的SQL语句使用ntile函数对teacher表进行了装桶处理，本次共装3个桶：select ntile(3) over(order by salary) as bucket,tno,name,salary from teacher order by salary，结果如图2-8所示。
图2-6 RANK()使用情况 图2-7 DENSE_RANK()使用情况 图2-8 NTILE()使用情况
2-3-4 用户自定义函数
SQL SERVER创建了用户自定义的函数，它同时具备了视图和存储过程的优点，但是却牺牲了可移植性。
Create Function 函数名称
（形式参数名称 AS 数据类型）
Returns 返回数据类型
Begin
函数内容
Return 表达式
End
调用用户自定义函数的基本语法为：变量=用户名.函数名称（实际参数列表），注意：在调用返回数值的用户自定义函数时，一定要在函数名称的前面加上用户名。
1. 用户定义的标量函数
标量函数是返回单个值的函数，这类函数可以接收多个参数，但是返回的值只有一个值。在定义函数返回值时使用Returns定义返回值的类型，而在定义函数中将使用return最后返回一个值变量，因此在用户定义的函数中，return命令应当是最后一条执行的命令，其基本的语法结构见下所示：
CREATE FUNCTION [ 用户名.] 定义的函数名
( [ { @变量名 [AS] 变量类型 [ ,...n ] ] )
RETURNS 返回值的数据类型
[ AS ]
BEGIN
declare @返回值变量 function_body
RETURN @返回值变量
END
2. 自定义函数的执行方法
用户定义函数的执行方法有两种：
(1) 第一种：通过Execute执行函数，并获取返回值；
EXECUTE @用户自定义变量=dbo.用户自定义函数 输入参数
该执行方法使用过程中，dbo的概念是database owner，为数据库所有者，在执行该语句的时候，可以省略dbo。
例如：execute @ee=averc ‘3-105’或者execute @ee=dbo.averc ‘3-105’
(2) 第二种：通过Select语句执行函数，并获取返回值；
SELECT @用户自定义变量=dbo.用户自定义函数(输入参数)
与Execute执行函数不同的是，通过SELECT语句执行函数的时候，必须加上dbo用户，否则会出现语法错误。
例如：select @ee=dbo.averc(‘3-105’)，但是执行下列的语句系统将报错：
q 错误！select @ee=averc(‘3-105’)，原因是没有加dbo用户；
q 错误！select @ee=dbo.averc ‘3-105’，原因是没有按照select格式录入参数。
实验：自定义标量函数实验
--例1：建立自定义函数，输入课程号，返回该课程的平均成绩。
CREATE function averc(@cno varchar(12))
returns int
as
begin
declare @aver int
select @aver= ( select avg(degree) from score where cno=@cno group by cno )
return @aver
end
--下面是测试如何运行该函数部分。
declare @ee int, @ww varchar(12)
select @ee=dbo.averc('3-105‘)
print @ee
--例2：建立自定义函数，输入学生学号和课程号，返回不同的信息
use school
Go
--查询sysobjects系统表中是否有stufun_jg对象，如果有则将该函数对象删除。
if exists(select name from sysobjects where name='stufun_jg')
drop function stu_jg
go
--下面开始建立自定义函数
create function stu_jg
(@stu_no varchar(12),@cnurse_no varchar(12))
returns varchar(100)
as
begin
declare @message varchar(100),
@sname varchar(12),@cname varchar(10)
if exists(select sname,cname from score,course,student where student.sno=@stu_no and course.cno=@cnurse_no and student.sno=score.sno and course.cno=score.cno)
begin
select @sname=sname,@cname=cname from score,course,student
where student.sno=@stu_no and course.cno=@cnurse_no
and student.sno=score.sno and course.cno=score.cno
set @message='您查询的学生是:'+RTRIM(LTRIM(@sname))+',选择的课程是:'+RTRIM(LTRIM(@cname))
end
else
set @message='对不起查无此人,您输入的学生号码错误!'
return @message
end
--下面开始测试函数，注意函数的执行测试方法
declare @mess varchar(500)
exec @mess=dbo.stu_jg '121','3-105'
Print @mess
3. 用户定义的内嵌表值函数
用户定义的内嵌表值函数没有由begin—end标识的程序体，取而代之的是将select 语句作为table数据类型加以返回，其基本的语法结构见下所示：
CREATE FUNCTION [ 用户名.]用户定义的函数名
( [ { @局部变量名 [AS]局部变量数据类型 } [ ,...n ] ] )
RETURNS TABLE
[ AS ]
RETURN
( select-stmt)
实验：用户定义的内嵌表值函数实验
--例1：创建函数,查询选修了某门课程的学生姓名
create function fn_view(@cname varchar(20)) returns table
as
return
(select sname from student where sno in(select sno from score where cno in
(select cno from course where cname=@cname) ))
--下面开始测试函数
declare @ee varchar(20)
set @ee='高等数学'
select * from fn_view(@ee)
--例2：建立函数,输入一个学生的学号就可以知道他的姓名,选修课程名以及该门课程的成绩
create function stu_avg_table(@sno varchar(20))
returns @stu_avg table
(sno varchar(12),sname varchar(20),cname varchar(20),degree int)
as
begin
insert @stu_avg
select student.sno,sname,cname,degree from student,score,course
where student.sno=score.sno and course.cno=score.cno and student.sno=@sno
return
end
--下面开始测试函数
declare @rr varchar(20)
set @rr='103'
select * from stu_avg_table(@rr)
--例3：查询销售数据库，输入一个货币参数，返回超过该销售额数目的订单数据表
Use sample
Go
/*开始定义变量@higher_money ，以保存检索定单的总价限制*/
if exists(select name from sysobjects where name='large_order')
drop function large_order
go
--下面开始建立函数large_order，输入一个货币参数，返回查询订单表
Create function large_order(@higher_than money)
Returns @order_table table/*定义返回数据表*/
(客户名称 char(255),产品名称 varchar(10),定货时间 datetime,总价 money)
AS
--下面开始定义查询
Begin
insert @order_table
select 客户数据表.公司名称,产品数据表.产品名称,订单数据表.定货日期,订单数据表.定货数量*产品数据表.单价
from 订单数据表,客户数据表,产品数据表
where 产品数据表.编号=订单数据表.产品编号 and 订单数据表.客户编号=客户数据表.编号
and 订单数据表.定货数量*产品数据表.单价>@higher_than
Return
End
Go
--在查询中调用该函数
select * from large_order(50000)
Go
SQL字符串处理函数大全
select语句中只能使用sql函数对字段进行操作（链接sql server），
select 字段1 from 表1 where 字段1.IndexOf("云")=1;
这条语句不对的原因是indexof（）函数不是sql函数，改成sql对应的函数就可以了。
left（）是sql函数。
select 字段1 from 表1 where charindex（'云',字段1）=1;
字符串函数对二进制数据、字符串和表达式执行不同的运算。此类函数作用于CHAR、VARCHAR、 BINARY、 和VARBINARY 数据类型以及可以隐式转换为CHAR 或VARCHAR的数据类型。可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用字符串函数。
常用的字符串函数有：
一、字符转换函数
1、ASCII()
返回字符表达式最左端字符的ASCII 码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。
2、CHAR()
将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（） 返回NULL 。
3、LOWER()和UPPER()
LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。
4、STR()
把数值型数据转换为字符型数据。
STR (<float_expression>[，length[， <decimal>]])
length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。
当length 或者decimal 为负值时，返回NULL；
当length 小于小数点左边（包括符号位）的位数时，返回length 个*；
先服从length ，再取decimal ；
当返回的字符串位数小于length ，左边补足空格。
二、去空格函数
1、LTRIM() 把字符串头部的空格去掉。
2、RTRIM() 把字符串尾部的空格去掉。
三、取子串函数
1、left()
LEFT (<character_expression>， <integer_expression>)
返回character_expression 左起 integer_expression 个字符。
2、RIGHT()
RIGHT (<character_expression>， <integer_expression>)
返回character_expression 右起 integer_expression 个字符。
3、SUBSTRING()
SUBSTRING (<expression>， <starting_ position>， length)
返回从字符串左边第starting_ position 个字符起length个字符的部分。
四、字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX (<’substring_expression’>， <expression>)
其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。
此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。
PATINDEX (<’%substring _expression%’>， <column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为0。
与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。
五、字符串操作函数
1、QUOTENAME()
返回被特定字符括起来的字符串。
QUOTENAME (<’character_expression’>[， quote_ character]) 其中quote_ character 标明括字符串所用的字符，缺省值为“[]”。
2、REPLICATE()
返回一个重复character_expression 指定次数的字符串。
REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值，则返回NULL 。
3、REVERSE()
将指定的字符串的字符排列顺序颠倒。
REVERSE (<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值。
4、REPLACE()
返回被替换了指定子串的字符串。
REPLACE (<string_expression1>， <string_expression2>， <string_expression3>) 用string_expression3 替换在string_expression1 中的子串string_expression2。
4、SPACE()
返回一个有指定长度的空白字符串。
SPACE (<integer_expression>) 如果integer_expression 值为负值，则返回NULL 。
5、STUFF()
用另一子串替换字符串指定位置、长度的子串。
STUFF (<character_expression1>， <start_ position>， <length>，<character_expression2>)
如果起始位置为负或长度值为负，或者起始位置大于character_expression1 的长度，则返回NULL 值。
如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。
六、数据类型转换函数
1、CAST()
CAST (<expression> AS <data_ type>[ length ])
2、CONVERT()
CONVERT (<data_ type>[ length ]， <expression> [， style])
1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。
2）length用于指定数据的长度，缺省值为30。
3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。
5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。
6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。
7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。
8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。
9）用CONVERT（）函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。
七、日期函数
1、day(date_expression)
返回date_expression中的日期值
2、month(date_expression)
返回date_expression中的月份值
3、year(date_expression)
返回date_expression中的年份值
4、DATEADD()
DATEADD (<datepart>， <number>， <date>)
返回指定日期date 加上指定的额外日期间隔number 产生的新日期。
5、DATEDIFF()
DATEDIFF (<datepart>， <date1>， <date2>)
返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。
6、DATENAME()
DATENAME (<datepart>， <date>)
以字符串的形式返回日期的指定部分此部分。由datepart 来指定。
7、DATEPART()
DATEPART (<datepart>， <date>)
以整数值的形式返回日期的指定部分。此部分由datepart 来指定。
DATEPART (dd， date) 等同于DAY (date)
DATEPART (mm， date) 等同于MONTH (date)
DATEPART (yy， date) 等同于YEAR (date)
8、GETDATE()
以DATETIME 的缺省格式返回系统当前的日期和时间。
--将字符串中从某个字符开始截取一段字符，然后将另外一个字符串插入此处
select stuff('hi,world!',4,4,'****') --返回值hel****orld!
--返回从指定位置开始指定长度的字符串
select substring('Hello,World!',2,10) --返回值ello,World
--将字符串中某段字符替换为指定的字符串
select replace('hi,world!','ll','aa') --返回值heaao,world!
--去除字符串中左面的空格
select ltrim(' hi,world!') --返回值hi,world!
--去除字符串中左面的空格
select ltrim('hi,world! ') --返回值hi,world!
--去除字符串中左面和右边的空格
select ltrim(' hi,world! ') --返回值hi,world!
--将NULL值替换为指定字符
select isnull('a',null) --返回值a
--转换数据类型
select cast('2007-10-11' as datetime) --返回值2007-10-11 00:00:00.000
select convert(datetime,'2007-10-11') --返回值2007-10-11 00:00:00.000
--获取字符串长度
select len('hi,world!') --返回值12
--获取字符串的前3个字符
select left('hi,world!',3) --返回值hel
--获取字符串的后3个字符
select right('hi,world!',3) --返回值ld!
--去除字符串的前3个字符
select right('hi,world!',(len('hi,world!')-3)) --返回值lo,world!
--去除字符串的后3个字符
select left('hi,world!',(len('hi,world!')-3)) --返回值hi,wor
--获取在该字符串中某字符串的位置(返回数字)
select charindex('e','hi,world!') --返回值2
--返回从第二个字符开始前4个字符
select left(right('[哈哈哈哈]aaa',len('[哈哈哈哈]aaa')-1),4) --返回值哈哈哈哈
--返回字符的小写情势
select lower('HELLO,WORLD!') --返回值hi,world!
--返回字符的大写情势
select UPPER('hi,world!') --返回值HELLO,WORLD!
--用第三个表达式替换第一个字符串表达式中浮现的所有第二个指定字符串表达式的匹配项
（要是此中有一个输入参数属于 nvarchar 数据类型，则返回 nvarchar；不然返回 varchar。要是任何一个参数为 NULL，则返回 NULL。）
SELECT REPLACE('Hello,World!','l','a') --返回值Heaao,Worad!
SELECT REPLACE('Hello,World!','l','') --返回值Heo,Word!
SELECT REPLACE('Hello,World!','l',null) --返回值NULL
--以右边参数数值次数复制字符表达式
select REPLICATE('Hello,World!',4) --返回值Hello,World!Hello,World!Hello,World!Hello,World!
--返回反转后的字符串
select REVERSE('Hello,World!') --返回值!dlroW,olleH
--施用DIFFERENCE时，两个字符串发音越相似（仅限于英书契符），返回值越大（返回值在0-4之间）
DIFFERENCE('sun','san') --返回值4
DIFFERENCE('sun','safdsdf') --返回值3
DIFFERENCE('sun','dgffgfdg') --返回值0
--将带小数点的数字类型转换为可设定长度可设定小数位的四舍五入后的字符串
SELECT STR(123.34584, 7, 3) --返回值123.346
--当设定长度值小于整数部位长度时，字符串将返回设定长度个*
SELECT STR(123333.34584, 5, 4) --返回值*****
--================================================ ===================================
--=====================================数字操作汇总==================================
--返回指定数字的最大整数
select floor(123456.1234) --返回值123456
--返回不带小数部分并且不小于其参数的值的最小数字。要是参数是一个空序列，则返回空序列
select ceiling(123.010) --返回124
select ceiling(null) --返回NULL
--返回四舍五入后的最靠近该数值的数值
select round(126.018,2) --返回126.12
--返回一个0-1之间的FLoat类型的随机数
select rand() --返回0.94170703697981
--返回圆周率PI的值
SELECT PI() --返回3.14159265358979