sql server常用函数.doc

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位第三大类:近似浮点数值数据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字节,最长为8000个字节,尾部的空白会去掉.text:长宽也是设定的,最长可以存放2G的数据.第六大类:Unincode字符串数据nchar:长度是设定的,最短为1字节,最长为4000个字节.不足的长度会用空白补上.储存一个字符需要2个字节.nvarchar:长度是设定的,最短为1字节,最长为4000个字节.尾部的空白会去掉.储存一个字符需要2个字节.ntext:长度是设定的,最短为1字节,最长为2G.尾部的空白会去掉,储存一个字符需要2个字节.第七大类:货币数据类型money:记录金额范围为:-92233720368577.5808至92233720368577.5807.需要8 个字节.smallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节.第八大类:标记数据timestamp:该数据类型在每一个表中是唯一的!当表中的一个记录更改时,该记录的timestamp字段会自动更新.uniqueidentifier:用于识别数据库里面许多个表的唯一一个记录.第九大类:二进制码字符串数据binary:固定长度的二进制码字符串字段,最短为1,最长为8000.varbinary:与binary差异为数据尾部是00时,varbinary会将其去掉image:为可变长度的二进制码字符串,最长2G.sql常用函数语法函数的语法内建SQL函数的语法是：SELECT function(列) FROM表函数的类型在SQL中，基本的函数类型和种类有若干种。函数的基本类型是：Aggregate函数Scalar函数合计函数（Aggregate functions）【聚合函数】Aggregate函数的操作面向一系列的值，并返回一个单一的值。注释：如果在SELECT语句的项目列表中的众多其它表达式中使用SELECT语句，则这个SELECT必须使用GROUP BY语句！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)返回某列的总和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)返回某个被请求的文本域的右侧部分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/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter例子1现在，我们希望计算OrderPrice字段的平均值。我们使用如下SQL语句：SELECT AVG(OrderPrice) AS OrderAverage FROM Orders结果集类似这样：OrderAverage950例子2现在，我们希望找到OrderPrice值高于OrderPrice平均值的客户。我们使用如下SQL语句：SELECT Customer FROM OrdersWHERE OrderPrice(SELECT AVG(OrderPrice) FROM Orders)结果集类似这样：CustomerBushCarterAdamsCOUNT()函数COUNT()函数返回匹配指定条件的行数。SQL COUNT()语法SQL COUNT(column_name)语法COUNT(column_name)函数返回指定列的值的数目（NULL不计入）：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)适用于ORACLE和Microsoft SQL Server，但是无法用于Microsoft Access。SQL COUNT(column_name)实例我们拥有下列Orders表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter现在，我们希望计算客户Carter的订单数。我们使用如下SQL语句：SELECT COUNT(Customer) AS CustomerNilsen FROM OrdersWHERE Customer=Carter以上SQL语句的结果是2，因为客户Carter共有2个订单：CustomerNilsen2SQL COUNT(*)实例如果我们省略WHERE子句，比如这样：SELECT COUNT(*) AS NumberOfOrders FROM Orders结果集类似这样：NumberOfOrders6这是表中的总行数。SQL COUNT(DISTINCT column_name)实例现在，我们希望计算Orders表中不同客户的数目。我们使用如下SQL语句：SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders结果集类似这样：NumberOfCustomers3这是Orders表中不同客户（Bush, Carter和Adams）的数目。MAX()函数MAX函数返回一列中的最大值。NULL值不包括在计算中。SQL MAX()语法SELECT MAX(column_name) FROM table_name注释：MIN和MAX也可用于文本列，以获得按字母顺序排列的最高或最低值。SQL MAX()实例我们拥有下面这个Orders表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter现在，我们希望查找OrderPrice列的最大值。我们使用如下SQL语句：SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders结果集类似这样：LargestOrderPrice2000MIN()函数MIN函数返回一列中的最小值。NULL值不包括在计算中。SQL MIN()语法SELECT MIN(column_name) FROM table_name注释：MIN和MAX也可用于文本列，以获得按字母顺序排列的最高或最低值。SQL MIN()实例我们拥有下面这个Orders表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/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()实例我们拥有下面这个Orders表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter现在，我们希望查找OrderPrice字段的总数。我们使用如下SQL语句：SELECT SUM(OrderPrice) AS OrderTotal 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_nameSQL GROUP BY实例我们拥有下面这个Orders表：O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter现在，我们希望查找每个客户的总金额（总订单）。我们想要使用GROUP BY语句对客户进行组合。我们使用下列SQL语句：SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY Customer结果集类似这样：CustomerSUM(OrderPrice)Bush2000Carter1700Adams2000很棒吧，对不对？让我们看一下如果省略GROUP BY会出现什么情况：SELECT Customer,SUM(OrderPrice) FROM Orders结果集类似这样：CustomerSUM(OrderPrice)Bush5700Carter5700Bush5700Bush5700Adams5700Carter5700上面的结果集不是我们需要的。那么为什么不能使用上面这条SELECT语句呢？解释如下：上面的SELECT语句指定了两列（Customer和SUM(OrderPrice)）。SUM(OrderPrice)返回一个单独的值（OrderPrice列的总计），而Customer返回6个值（每个值对应Orders表中的每一行）。因此，我们得不到正确的结果。不过，您已经看到了，GROUP 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(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/28300Bush52008/08/062000Adams62008/07/21100Carter现在，我们希望查找订单总金额少于2000的客户。我们使用如下SQL语句：SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY CustomerHAVING SUM(OrderPrice)1500结果集：CustomerSUM(OrderPrice)Bush2000Adams2000UPPER()函数UPPER函数把字段的值转换为大写。SQL UPPER()语法SELECT UPPER(column_name) FROM table_nameSQL UPPER()实例我们拥有下面这个Persons表：IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing现在，我们希望选取LastName和FirstName列的内容，然后把LastName列转换为大写。我们使用如下SQL语句：SELECT UPPER(LastName) as LastName,FirstName FROM Persons结果集类似这样：LastNameFirstNameADAMSJohnBUSHGeorgeCARTERThomasLOWER()函数LOWER函数把字段的值转换为小写。SQL LOWER()语法SELECT LOWER(column_name) FROM table_nameSQL LOWER()实例我们拥有下面这个Persons表：IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing现在，我们希望选取LastName和FirstName列的内容，然后把LastName列转换为小写。我们使用如下SQL语句：SELECT LOWER(LastName) as LastName,FirstName FROM Persons结果集类似这样：LastNameFirstNameadamsJohnbushGeorgecarterThomasSUBSTRING()函数SUBSTRING函数用于从文本字段中提取字符。SQL SUBSTRING()语法SELECT SUBSTRING(column_name,start,length) FROM table_name参数描述column_name必需。要提取字符的字段。start必需。规定开始位置（起始值是1）。length可选。要返回的字符数。如果省略，则SUBSTRING()函数返回剩余文本。SQL SUBSTRING()实例我们拥有下面这个Persons表：IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing现在，我们希望从City列中提取前3个字符。我们使用如下SQL语句：SELECT SUBSTRING(City,1,3) as SmallCity FROM Persons结果集类似这样：SmallCityLonNewBeiLEN()函数LEN函数返回文本字段中值的长度。SQL LEN()语法SELECT LEN(column_name) FROM table_nameSQL LEN()实例我们拥有下面这个Persons表：IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing现在，我们希望取得City列中值的长度。我们使用如下SQL语句：SELECT LEN(City) as LengthOfAddress FROM Persons结果集类似这样：LengthOfCity687ROUND()函数ROUND函数用于把数值字段舍入为指定的小数位数。SQL ROUND()语法SELECT ROUND(column_name,decimals) FROM table_name参数描述column_name必需。要舍入的字段。decimals必需。规定要返回的小数位数。SQL ROUND()实例我们拥有下面这个Products表：Prod_IdProductNameUnitUnitPrice1gold1000 g32.352silver1000 g11.563copper1000 g6.85现在，我,们希望把名称和价格舍入为最接近的整数。我们使用如下SQL语句：SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products结果集类似这样：ProductNameUnitPricegold32silver12copper7GETDATE()函数GETDATE函数返回当前的日期和时间。SQL GETDATE()语法SELECT GETDATE() FROM table_nameSQL GETDATE()实例我们拥有下面这个Products表：Prod_IdProductNameUnitUnitPrice1gold1000 g32.352silver1000 g11.563copper1000 g6.85现在，我们希望显示当天的日期所对应的名称和价格。我们使用如下SQL语句：SELECT ProductName, UnitPrice, GetDate() as PerDate FROM Products结果集类似这样：ProductNameUnitPricePerDategold32.3512/29/200811:36:05 AMsilver11.5612/29/200811:36:05 AMcopper6.8512/29/200811:36:05 AMsql流程控制语句一、 语句块（beginend ）语法如下： BeginEndBeginend 用来设定一个语句块，可以将多条sql 语句封装起来构成一个语句块，在处理时，整个语句块被视为一条语句。Beginend经常用来在条件语句中如 ifelse 或while循环中。Beginend可以嵌套式使用。 BeginUpdate课程表 set 学分=5 where 课程号 = 001Update 课程表 set 学分=5 where 课程号=002Select 课程号.学分 from 课程号End二、 判断语句通常计算机是按顺序执行程序中的语句，但是在许多情况下，语句执行的顺序以及是否执行依赖于程序运行的中间结果，在这种情况下，必须根据某个变量或表达式的值做出判断，已决定执行哪些语句活不执行哪些语句，这时可以利用ifelse语句做出判断，选择执行某条语句或语句块判断语句如下： If Else 其中可以是各种表达式的组合，的值必须是 true或false，当为true时，执行，当为false时 执行。Else 是可选的最简单的if语句没有else字句部分。-计算学号为的学生的平均成绩，如果平均成绩=60,则显示及格，否则不及格。 Declare 是声明的意思declare cj_avg intselect cj_avg =AVG(成绩) from 成绩表 where学号=print 平均成绩print cj_avgif cj_avg=60print 平均成绩及格elseprint 平均成绩不及格三、 检测语句Ifexi