sql server常用函数

1、SQL 数据类型SQLServer的数据类型第一大类:整数数据bit:bit数据类型代表0,1或NULL,就是表示true,false.占用1:以4个字节来存储正负数.可存储范围为:-231至231-1.smallint:以2个字节来存储正负数.存储范围为:-215至215-1tinyint: 是最小的整数类型,仅用1字节,范围:0至此8-1第二大类:精确数值数据decimal:表示的数字可以达到38位,存储数据时所用的字节数目会随着使用位数的多少变化.numericdecimal:和decimal差不多decimal(m,n)表示总位数m为（不包含小数点），小数位n位第三大类

2、:近似浮点数值数据float:用8个字节来存储数据.最多可为53位.范围为:-1.79E+308至1.79E+308.real:位数为24,用4个字节,数字范围:-3.04E+38至3.04E+38第四大类:日期时间数据datatime:表示时间范围可以表示从1753/1/1至9999/12/31,时间可以表示到3.33/1000秒.使用8个字节.smalldatetime:表示时间范围可以表示从1900/1/1至2079/12/31.使用4个字节.第五大类:字符串数据char:长度是设定的,最短为1字节,最长为8000个字节.不足的长度会用空白补上.varchar:长度也是设定的,最短为1字

3、节,最长为8000个字节,尾部的空白会去掉.text:长宽也是设定的,最长可以存放2G的数据.第六大类:Unincode字符串数据nchar:长度是设定的,最短为1字节,最长为4000个字节.不足的长度会用空白补上.储存一个字符需要2个字节.nvarchar:长度是设定的,最短为1字节,最长为4000个字节.尾部的空白会去掉.储存一个字符需要2个字节.ntext:长度是设定的,最短为1字节,最长为2G.尾部的空白会去掉,储存一个字符需要2个字节.第七大类:货币数据类型money:记录金额范围为:-92233720368577.5808至92233720368577.5807.需要8 个字节.s

4、mallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节.第八大类:标记数据timestamp:该数据类型在每一个表中是唯一的!当表中的一个记录更改时,该记录的timestamp字段会自动更新.uniqueidentifier:用于识别数据库里面许多个表的唯一一个记录.第九大类:二进制码字符串数据binary:固定长度的二进制码字符串字段,最短为1,最长为8000.varbinary:与binary差异为数据尾部是00时,varbinary会将其去掉image:为可变长度的二进制码字符串,最长2G.sql常用函数语法函数的语法内建 SQL&

5、#160;函数的语法是：SELECT function(列) FROM 表函数的类型在 SQL 中，基本的函数类型和种类有若干种。函数的基本类型是：Aggregate 函数 Scalar 函数合计函数（Aggregate functions）【聚合函数】Aggregate 函数的操作面向一系列的值，并返回一个单一的值。注释：如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句，则这个 SELECT 必须使用GROUP BY 语句

6、！"Persons" table (在大部分的例子中使用过)NameAgeAdams, John38Bush, George33Carter, Thomas28在 SQL Server 中的合计函数函数描述AVG(column)返回某列的平均值COUNT(column)返回某列的行数（不包括NULL值）COUNT(*)返回被选行数COUNT(DISTINCT column)返回相异结果的数目MAX(column)返回某列的最高值MIN(column)返回某列的最低值STDEV(column) 返回某列的标准偏差SUM(column)返回某列的总

7、和Scalar 函数【标量函数】Scalar 函数的操作面向某个单一的值，并返回基于输入值的一个单一的值。MS sql server 中的 Scalar 函数函数描述UPPER(c)将某个域转换为大写LOWER(c)将某个域转换为小写SUBSTRING(c,start,end)从某个文本域提取字符LEN(c)返回某个文本域的长度CHARINDEX(c,char)返回在某个文本域中指定字符的数值位置LEFT(c,number_of_char)返回某个被请求的文本域的左侧部分RIGHT(c,number_of_char)返回某个被请求的文本域的右侧

8、部分ROUND(c,decimals)对某个数值域进行指定小数位数的四舍五入GETDATE()返回当前的系统日期DATEDIFF(d,date1,date2)用于执行日期计算AVG 函数定义和用法AVG 函数返回数值列的平均值。NULL 值不包括在计算中。SQL AVG() 语法SELECT AVG(column_name) FROM table_nameSQL AVG() 实例我们拥有下面这个 "Orders" 表：O_IdOrderDateOrderPriceCustomer12008/12/2910

9、00Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter例子 1现在，我们希望计算 "OrderPrice" 字段的平均值。我们使用如下 SQL 语句：SELECT AVG(OrderPrice) AS OrderAverage FROM Orders结果集类似这样：OrderAverage950例子 2现在，我们希望找到 OrderPrice 值

10、高于 OrderPrice 平均值的客户。我们使用如下 SQL 语句：SELECT Customer FROM OrdersWHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)结果集类似这样：CustomerBushCarterAdamsCOUNT() 函数COUNT() 函数返回匹配指定条件的行数。SQL COUNT() 语法SQL COUNT(column_name) 语法COUNT(column_name) 函数返回指定列的值的数目（NUL

11、L 不计入）：SELECT COUNT(column_name) FROM table_nameSQL COUNT(*) 语法COUNT(*) 函数返回表中的记录数：SELECT COUNT(*) FROM table_nameSQL COUNT(DISTINCT column_name) 语法COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：SELECT COUNT(DISTINCT column_name) FROM table_name注释：COUNT(DISTINCT) 适用于 O

12、RACLE 和Microsoft SQL Server，但是无法用于 Microsoft Access。SQL COUNT(column_name) 实例我们拥有下列 "Orders" 表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter现在，我们希望计算客户 "

13、Carter" 的订单数。 我们使用如下 SQL 语句： SELECT COUNT(Customer) AS CustomerNilsen FROM OrdersWHERE Customer='Carter'以上 SQL 语句的结果是 2，因为客户 Carter 共有 2 个订单：CustomerNilsen2SQL COUNT(*) 实例 如果我们省略 WHERE 子句，比如这样： SELECT C

14、OUNT(*) AS NumberOfOrders FROM Orders结果集类似这样：NumberOfOrders6这是表中的总行数。SQL COUNT(DISTINCT column_name) 实例现在，我们希望计算 "Orders" 表中不同客户的数目。我们使用如下 SQL 语句：SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders结果集类似这样：NumberOfCustomers3这是 "Orders"

15、60;表中不同客户（Bush, Carter 和 Adams）的数目。MAX() 函数MAX 函数返回一列中的最大值。NULL 值不包括在计算中。SQL MAX() 语法SELECT MAX(column_name) FROM table_name注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。SQL MAX() 实例我们拥有下面这个 "Orders" 表：O_IdOrderDateOrderPriceCustomer1200

16、8/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter现在，我们希望查找 "OrderPrice" 列的最大值。我们使用如下 SQL 语句：SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders结果集类似这样：LargestOrderPrice2000MIN() 函数MIN 函数返回一列中的最

17、小值。NULL 值不包括在计算中。SQL MIN() 语法SELECT MIN(column_name) FROM table_name注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。SQL MIN() 实例我们拥有下面这个 "Orders" 表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush5200

18、8/08/062000Adams62008/07/21100Carter现在，我们希望查找 "OrderPrice" 列的最小值。我们使用如下 SQL 语句：SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders结果集类似这样：SmallestOrderPrice100SUM() 函数SUM 函数返回数值列的总数（总额）。SQL SUM() 语法SELECT SUM(column_name) FROM table_nameSQL SUM()

19、0;实例我们拥有下面这个 "Orders" 表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter现在，我们希望查找 "OrderPrice" 字段的总数。我们使用如下 SQL 语句：SELECT SUM(OrderPrice) AS OrderTotal

20、 FROM Orders结果集类似这样：OrderTotal5700GROUP BY 语句合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。GROUP BY 语句GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。SQL GROUP BY 语法SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_

21、nameSQL GROUP BY 实例我们拥有下面这个 "Orders" 表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter现在，我们希望查找每个客户的总金额（总订单）。我们想要使用 GROUP BY 语句对客户进行组合。我们使用下列 SQL 语句：SELECT

22、 Customer,SUM(OrderPrice) FROM OrdersGROUP BY Customer结果集类似这样：CustomerSUM(OrderPrice)Bush2000Carter1700Adams2000很棒吧，对不对？让我们看一下如果省略 GROUP BY 会出现什么情况：SELECT Customer,SUM(OrderPrice) FROM Orders结果集类似这样：CustomerSUM(OrderPrice)Bush5700Carter5700Bush5700Bush5700Adams5700Carter5700上面的结果集不是我们需要的。那

23、么为什么不能使用上面这条 SELECT 语句呢？解释如下：上面的 SELECT 语句指定了两列（Customer 和SUM(OrderPrice)）。"SUM(OrderPrice)" 返回一个单独的值（"OrderPrice" 列的总计），而 "Customer" 返回 6 个值（每个值对应 "Orders" 表中的每一行）。因此，我们得不到正确的结果。不过，您已经看到了，GROUP

24、BY 语句解决了这个问题。GROUP BY 一个以上的列我们也可以对一个以上的列应用 GROUP BY 语句，就像这样：SELECT Customer,OrderDate,SUM(OrderPrice) FROM OrdersGROUP BY Customer,OrderDateHAVING 子句在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。SQL HAVING 语法SELECT column_name, aggregate_function(

25、column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator valueSQL HAVING 实例我们拥有下面这个 "Orders" 表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush520

26、08/08/062000Adams62008/07/21100Carter现在，我们希望查找订单总金额少于 2000 的客户。我们使用如下 SQL 语句：SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY CustomerHAVING SUM(OrderPrice)<2000结果集类似：CustomerSUM(OrderPrice)Carter1700现在我们希望查找客户 "Bush" 或 "Adams" 拥有超过&#

27、160;1500 的订单总金额。我们在 SQL 语句中增加了一个普通的 WHERE 子句：SELECT Customer,SUM(OrderPrice) FROM OrdersWHERE Customer='Bush' OR Customer='Adams'GROUP BY CustomerHAVING SUM(OrderPrice)>1500结果集：CustomerSUM(OrderPrice)Bush2000Adams2000UPPER() 函数UPPER 函数把字段的值转换为大写。

28、SQL UPPER() 语法SELECT UPPER(column_name) FROM table_nameSQL UPPER() 实例我们拥有下面这个 "Persons" 表：IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing现在，我们希望选取 "LastName" 和 "Fi

29、rstName" 列的内容，然后把 "LastName" 列转换为大写。我们使用如下 SQL 语句：SELECT UPPER(LastName) as LastName,FirstName FROM Persons结果集类似这样：LastNameFirstNameADAMSJohnBUSHGeorgeCARTERThomasLOWER() 函数LOWER 函数把字段的值转换为小写。SQL LOWER() 语法SELECT LOWER(column_name) FROM table_na

30、meSQL LOWER() 实例我们拥有下面这个 "Persons" 表：IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing现在，我们希望选取 "LastName" 和 "FirstName" 列的内容，然后把 "LastName" 

31、;列转换为小写。我们使用如下 SQL 语句：SELECT LOWER(LastName) as LastName,FirstName FROM Persons结果集类似这样：LastNameFirstNameadamsJohnbushGeorgecarterThomasSUBSTRING() 函数SUBSTRING 函数用于从文本字段中提取字符。SQL SUBSTRING() 语法SELECT SUBSTRING(column_name,start,length) FROM table_name参数描述column_name必需。要提取字符的字

32、段。start必需。规定开始位置（起始值是 1）。length可选。要返回的字符数。如果省略，则 SUBSTRING() 函数返回剩余文本。SQL SUBSTRING() 实例我们拥有下面这个 "Persons" 表：IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing现在，我们希望从 "City&qu

33、ot; 列中提取前 3 个字符。我们使用如下 SQL 语句：SELECT SUBSTRING(City,1,3) as SmallCity FROM Persons结果集类似这样：SmallCityLonNewBeiLEN() 函数LEN 函数返回文本字段中值的长度。SQL LEN() 语法SELECT LEN(column_name) FROM table_nameSQL LEN() 实例我们拥有下面这个 "Persons" 表：IdLastNameFirstNa

34、meAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing现在，我们希望取得 "City" 列中值的长度。我们使用如下 SQL 语句：SELECT LEN(City) as LengthOfAddress FROM Persons结果集类似这样：LengthOfCity687ROUND() 函数ROUND 函数用于把数值字段舍入为指定的小数位数。SQL ROUN

35、D() 语法SELECT ROUND(column_name,decimals) FROM table_name参数描述column_name必需。要舍入的字段。decimals必需。规定要返回的小数位数。SQL ROUND() 实例我们拥有下面这个 "Products" 表：Prod_IdProductNameUnitUnitPrice1gold1000 g32.352silver1000 g11.563copper1000 g6.85现在，我, 们希望把名称和价格舍入为最接近的整数。我们使用如下 SQL

36、0;语句：SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products结果集类似这样：ProductNameUnitPricegold32silver12copper7GETDATE() 函数GETDATE 函数返回当前的日期和时间。SQL GETDATE() 语法SELECT GETDATE() FROM table_nameSQL GETDATE() 实例我们拥有下面这个 "Products" 表：Prod_IdProductNameUnit

37、UnitPrice1gold1000 g32.352silver1000 g11.563copper1000 g6.85现在，我们希望显示当天的日期所对应的名称和价格。我们使用如下 SQL 语句：SELECT ProductName, UnitPrice, GetDate() as PerDate FROM Products结果集类似这样：ProductNameUnitPricePerDategold32.3512/29/2008 11:36:05 AMsilver11.5612/29/2008 11:36:05 AMcopper6.8512/29/20

38、08 11:36:05 AMsql流程控制语句一、 语句块（beginend ）语法如下： Begin<sql 语句或程序块>EndBeginend 用来设定一个语句块，可以将多条sql 语句封装起来构成一个语句块，在处理时，整个语句块被视为一条语句。Beginend经常用来在条件语句中如 ifelse 或while循环中。Beginend可以嵌套式使用。 BeginUpdate课程表 set 学分=5 where 课程号 = 001Update 课程表 set 学分=5 where 课程号=002Select 课程号.学分 from 课程号End二、 判断语句通常计算机

39、是按顺序执行程序中的语句，但是在许多情况下，语句执行的顺序以及是否执行依赖于程序运行的中间结果，在这种情况下，必须根据某个变量或表达式的值做出判断，已决定执行哪些语句活不执行哪些语句，这时可以利用ifelse语句做出判断，选择执行某条语句或语句块判断语句如下： If<条件表达式>< 命令行或语句块1> Else<条件表达式>< 命令行或语句块2> 其中<条件表达式>可以是各种表达式的组合，<条件表达式>的值必须是 true或false，当<条件表达式>为true时，执行< 命令行或语句块1>，当&l

40、t;条件表达式>为false时 执行< 命令行或语句块2>。Else 是可选的最简单的if语句没有else字句部分。-计算学号为的学生的平均成绩，如果平均成绩>=60,则显示及格，否则不及格。 Declare 是声明的意思declare cj_avg intselect cj_avg =AVG(成绩) from 成绩表 where学号=print 平均成绩print cj_avgif cj_avg>=60print '平均成绩及格'elseprint '平均成绩不及格'三、 检测语句Ifexists 语句用于检测数据是否存在，而不考虑与匹配的行数。对于存在性检验而言，使用ifexists 要比使用count（*）>0好，效率更高，因为只要找到第一条匹配的行，服务器就会停止执行select 语句。检测语句语发如下，If not exists (select查询语句)<命令行或语句块1>Else<条件表达式>< 命令行或语句块2>-检查学号为的同学是否有课程号的成绩。if exists