SQL函数

3.SQL函数

3.1函数组成

  • 确定性函数：如果函数的输出只与输入的参数的值相关，而与其他外部因素无关。
  • 非确定性函数：如果函数的输出基于环境条件，或者产生随机或者依赖结果的算法。

在T-SQL中，变量既可用于输入，也可用于输出。用户变量以@符号开头，用于声明特定的数据类型。可以使用SET或者SELECT语句给变量赋值。

DECLARE@MyNumberint，@MyCountintSET@MyNumber=144SET@MyCount=100

SELECTSQRT(@MyNumber)

运行结果：12

同时，也可以使用SELECT来进行变量赋值。可以在一个操作内同时给多个变量赋值。

DECLARE@MyNumber1int,@MyNumber2int,@MyResult1int,@MyResult2int

SELECT@MyNumber1=144,@MyNumber2=121

SELECT@MyResult1=SQRT(@MyNumber1), @MyResult2=SQRT(@MyNumber2)

SELECT@MyResult1,@MyResult2

运行结果：12，11

3.2聚合函数

聚合函数应用特定的聚合操作并返回一个标量值。返回的数据类型对应于该列或者传递到函数中的值。聚合经常和分组、累积以及透视等表运算一起使用。

AVG()函数：返回一组数值中所有非空数值的平均值。

select AVG(UnitPrice) as Avgprice from Products

COUNT()函数：返回一个列内所有非空值的个数。

selectcount(*) as Countprice from Products

MIN()和MAX()函数：返回一个列范围内的最小非空值和最大值。当所查的列结构为非数据类型，返回的数值为ASCII值的大小。

selectMIN(ProductName) as MINName from Products

SUM()函数：返回一个列范围内所有非空值的总和。

3.3配置变量

配置变量返回SQL Server执行环境的标量信息。

3.31@@ERROR变量

包含当前连接发生的最后一次错误的代码。执行的语句没有发生错误时，@@ERROR变量的值是0.出现标准错误时，错误是由数据库引擎引发的。所有的标准错误代码与信息都保存在sys.messages系统视图中。

select5/0

select@@error

运行结果

消息8134，级别16，状态1，第1 行

遇到以零作除数错误。

3.32@@SERVICENAME变量

用于执行和维护当前SQL Server实例的Windows服务名。

3.33@@TOTALE_RRORS变量

记录从打开当前连接开始发生的总错误次数。

3.34@@TOTAL_READ变量

从打开当前连接时开始计算的磁盘读取总数

select@@TOTAL_READ

运行结果

1250

3.35@@VERSION变量

返回当前SQL Server实例的完整版本信息

select@@VERSION

运行结果

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

3.36错误函数

编写错误错误代码时，SQL程序员必须把这些代码放在会引发系统错误的catch代码块中。

错误函数用于返回错误的特定信息：

ERROR_MESSAGE()---返回错误的描述

ERROR_NUMBER()---返回错误号

ERROR_SEVERITY()---返回错误的严重级别

ERROR_STATE()---返回错误的状态号

ERROR_LINE()---返回例程中导致错误的行号

ERROR_PROCEDURE()---返回发生错误的储存过程名或触发器名

3.4转换函数

3.41CAST()函数

包括用AS关键字分割的源值和目标数据类型

selectcast('123.1'asdecimal(9,2))

运行结果

123.10

3.41CONVERT()函数

需要两个参数：第一个是目标数据类型，第2个是源数据

selectconvert(decimal(9,2),'2010.1234')

运行结果

2010.12

CONVERT函数还可以返回经过格式化的字符串值。

3.42STR()函数

将数字转化为字符串，函数有三个参数：数值、总长度和小数位数。

DECLARE@NumDECIMAL

SET@NUM=123456.7191

SELECTSTR(@NUM,9,4)

运行结果

123457.00

3.5游标函数与变量

游标可以处理多行数据，在过程循环中一次访问一行。

3.51CURSOR_STATUS()函数

返回一个整型值，表示传递给这个函数的游标类型变量的状态。

3.52@@CURSOR_ROWS全局变量

返回一个整型值，表示当前连接中打开的游标中的行数。

3.53@@FETCH_STATUS全局变量

返回一个标记，用于表示当前游标指针的状态。

3.6日期函数

3.61DATEADD()函数

在日期/时间值上加上日期单位间隔。

selectdateadd(Day,90,'1-20-2010')

运行结果

2010-04-20 00:00:00.000

其中时间间隔参数传递给DATEADD()函数如下

时间间隔

时间间隔参数

年

Year,yyyy,yy

季度

Quarter,qq,q

月

Month,mm,m

一年内的天

DayOfYear,dy,y

天

Day,dd,d

星期

Week,wk,ww

小时

Hour,hh

分钟

Minute,mi,m

秒

Second,ss,s

毫秒

Millsecond,ms

3.62DATEDIFF()函数

在等式的两端有4个元素：起始日期、时间间隔、差值和最终日期。

selectdatediff(day,'1-20-2010','6-15-2009')

运行结果

219

3.63DATEPART()与DATENAME()函数

返回datetime或者shortdatetime值日期部分。DATEPART()返回一个整型值，DATENAME()返回一个包含描述性文字的字符串。

selectdatepart(month,'1-21-2010')

运行结果

1

3.64GETDATE()与GETUTCDATE()函数

返回datetime类型的当前日期与时间。

3.65DAY()、MONTH()和YEAR()函数

select'Year: '+CONVERT(varchar(4),YEAR(GETDATE()))

+', Month: '+CONVERT(varchar(2),MONTH(GETDATE()))

+', Day: '+CONVERT(varchar(2),DAY(GETDATE()))

运行结果

Year: 2010, Month: 1, Day: 21

3.66字符串操作函数

ACSII()、CHAR()、UNICODE()、NCHAR()返回ASCII码等

CHARINDEX():寻找在一个字符串中某字符串第一次出现的位置

selectcharindex('k','skykeysky')

运行结果

2

PATINDEX():与charindex()相似，增加对通配符Like的支持，返回一个字符模式的索引

selectpatindex('%k%','skykeysky')

运行结果

2

LEN()函数：返回一个代表字符串长度的整型值

declare@DATEvarchar(50)

set@DATE='2010-01-20'

selectlen(@date)

运行结果

10

LEFT()和RIGHT()函数:返回一定长度的子字符串。LEFT()返回字符串最左边的字符。

declare@DATEvarchar(50)

set@DATE='2010-01-20'

selectLEFT(@date,4)

运行结果

2010

declare@DATEvarchar(50)

set@DATE='2010-01-20'

selectRIGHT(@date,2)

运行结果

10

SUBSTRING()函数:从字符串的一个位置开始，返回一个特定长度的子字符串。返回最大可能长度的字符串，而不会将多出的长度以空格填空，索引从1开始

declare@DATEvarchar(50)

set@DATE='2010-01-20'

selectsubstring(@DATE,1,4)+substring(@DATE,6,2)+substring(@DATE,9,2)

运行结果

20100120

LOWER()和UPPER()函数：将字符串中所有字符分别转换为小写和大写。

declare@Namevarchar(50)

set@Name='keySky'

selectUPPER(@Name)

运行结果

KEYSKY

LTRIM()与RTRIM()函数：返回将字符串的左边和右边的空白修剪之后的字符串

REPLACE()函数：把字符串中的某个字符或某个子字符串替换为另一个字符或者子字符串。

DECLARE@Mesvarchar(50)

set@Mes='KeySky is writing a message for a potential challenge'

selectreplace(@Mes,'writing','receiving')

运行结果

KeySky is receiving a message for a potential challenge

REPLICATE()与SPACE()函数：将一些字符重复填充进一个字符串。

DECLARE@Mesvarchar(50)

set@Mes='KeySky'

select@Mes+replicate('*',10-len(@Mes))

运行结果

KeySky****

REVERSE()函数：将字符串中的字符转置。

DECLARE@Mesvarchar(50)

set@Mes='KeySky'

selectreverse(@Mes)

运行结果

ykSyeK

STUFF()函数：将字符串中的一部分替换为另一个字符串。

DECLARE@Mesvarchar(50)

set@Mes='KeySkyKeySky'

selectSTUFF(@Mes,4,3,'Hello')

运行结果

KeyHelloKeySky

3.7数学函数

函数

说明

ABS()

返回一个数的绝对值

ACOS()

计算一个角的反余弦值，以弧度表示

ASIN()

计算一个角的正余弦值，以弧度表示

ATAN()

计算一个角的反切值，以弧度表示

ATN2()

计算两个值的反正切，以弧度表示

CEILING()

返回大于或等于一个数的最小整数

COS()

计算一个角的余弦值，以弧度表示

COT()

计算一个角的余切值，以弧度表示

DEGREES()

将一个角从弧度转换为角度

EXP()

指数运算

FLOOR()

返回小于或等于一个数的最大整数

LOG()

计算以2为底的自然对数

LOG10()

计算以10为底的自然对数

PI()

返回以浮点数表示的圆周率

POWER()

幂运算

RADIANS()

将一个角从角度转换为弧度

RAND()

返回以随机数算法算出的一个小数

ROUND()

对一个小数进行四舍五入运算

SIGN()

根据参数正负，返回-1或者1

SQRT()

返回一个数学的平方根

SQUARE()

返回一个数的平方

TAN()

计算一个角正切的值，以弧度表示

3.8系统统计变量

变量

说明

@@CONNECTIONS

打开连接的次数

@@CPU_BUSY

从上次启动服务器开始，SQL Server一共工作的毫秒数

@@IDLE

从上次启动服务器开始，SQL Server一共空闲的毫秒数

@@IO_BUSY

从上次启动服务器开始，SQL Server一共处理的网络数据包数

@@PACK_RECEIVED

从上次启动服务器开始，SQL Server一共收到的网络数据包数

@@PACK_SENT

从上次启动服务器开始，SQL Server一共发送的网络数据包数

@@PACKET_ERRORS

从上次启动服务器开始，SQL Server一共收到的网络数据包错误数

@@TIMETICKS

每个时钟滴答有多少毫秒

@@TOTAL_ERRORS

从上次启动服务器开始，SQL Server一共收到的磁盘I/O错误数

@@TOTAL_READ

从上次启动服务器开始，SQL Server一共进行的物理磁盘读取次数

@@TOTAL_WRITE

从上次启动服务器开始，SQL Server一共进行的物理磁盘写入次数

4.聚合与分组

4.1复杂统计函数

STDEV()函数：返回某数字范围内所有非空值的简单标准方差。返回的数据类型是FLOAT

selectSTDEV(UnitPrice) from Products

运行结果

33.8151114580251

STDEVP()函数：返回标准方差

VAR()函数：返回测量最小值或最大值和中点的距离有多远。

4.2数据分组

4.21 GROUP BY子句

添加到查询的WHERE和ORDER BY子句之后。查询先于聚合函数与分组操作执行。这些结果读进内存后，SQL Server会遍历这些记录，并在这些记录上应用分组和聚合计算。

select id,sum(value),count(id) from tbltest groupby id

运行结果

1   60.00  2

2   23.00  1

4   64.00  2

6   64.00  2

8   26.00  1

9   99.00  1

10  28.00  1

11  113.00 2

13  83.00  1

14  27.00  1

4.22 HAVING子句

如果需要根据聚合值的结果来过滤分组查询的结果，必须首先执行聚会操作。不能使用where子句，它会在分组和聚合之前对结果进行处理，HAVING子句实现分组后过滤行。

select id,sum(value),count(id) from tbltest groupby id havingcount(id)>1

运行结果

1       60.00        2

4       64.00        2

6       64.00        2

11     113.00      2

4.23 ROLLUP子句

对列表进行小技和总计计算。

select id,sum(value),count(id) from tbltest groupby id with rollup havingcount(id)>1

运行结果

1       60.00        2

4       64.00        2

6       64.00        2

11     113.00      2

NULL         587.00      14

4.24 CUBE子句

用于对每个分组的列值执行累积。

4.25 GROUPING()子句

返回一个位值（1或者0）来表明某行是一个累计值，将该行与空值的聚合相隔离。

4.26 COMPUTE与COMPUTE BY子句

select id,val from tbltest computesum(val)

运行结果

1       25.00

1       35.00

2       23.00

4       12.00

4       52.00

6       27.00

6       37.00

8       26.00

9       99.00

10     28.00

11     65.00

11     48.00

13     83.00

14     27.00

587.00

5.多表查询

5.1子查询与连接

5.11 WHERE子句中的连接

SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.SupplierID,
dbo.Products.CategoryID, dbo.Products.QuantityPerUnit,

dbo.Products.UnitPrice, dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel, dbo.Products.Discontinued, dbo.Categories.CategoryName

FROM dbo.Categories ， dbo.Products

WHERE dbo.Categories. CategoryID= dbo.Products.CategoryID

这个查询实现一个内连接，两个列引用中间的等号表示这个查询只返回两个表中有匹配记录的行。这种连接方式成为同等连接，表示在连接运算中比较的两个表中的值必须相等。

5.12 FROM子句中的连接

在FROM子句中，通过JOIN语句引用两个表，后跟ON关键字。

SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.SupplierID,
dbo.Products.CategoryID, dbo.Products.QuantityPerUnit,

dbo.Products.UnitPrice, dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel, dbo.Products.Discontinued,

dbo.Categories.CategoryName

FROM dbo.Categories INNERJOIN

dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID

WHERE (dbo.Products.Discontinued =0)

官方推荐，在where 与FROM子句连接中使用FROM的ANSI标准方法。

5.13 连接的类型

内连接：只返回两个表中的相关记录；外连接：先返回同一个表中的所有行，然后返回第二个表中的相关行。

5.14 内连接

内连接的目的是将一个表中的记录和另一个表中的对应记录进行匹配，前提是两个表的相关列包含相同的值。例如上面的FROM子句连接和WHERE子句连接都属于内连接

5.15 外连接

外连接用于从一个表中返回所有的行，然后匹配相关表中具有相同连接列值的行。在外连接中，查询将返回一个表中不匹配的行。

SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.SupplierID,
dbo.Products.CategoryID, dbo.Products.QuantityPerUnit,

dbo.Products.UnitPrice, dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder,
dbo.Products.ReorderLevel, dbo.Products.Discontinued,

dbo.Categories.CategoryName

FROM dbo.Categories LEFTOUTERJOIN

dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID

WHERE (dbo.Products.Discontinued =0)

这表示返回Categories中的所有行，然后返回相关的Products行

5.16 多列连接

在连接的基础上使用AND和OR字符自合来实现多列连接

另外还有非同等连接以及一些特殊目的的连接运算。

5.17 合并查询

UNION()查询垂直地扩展结果，将一个记录堆积到另一个记录的顶部。

posted @ 2013-12-04 22:43  海阔峰高  阅读(747)  评论(0)  编辑  收藏  举报