SQL Server各种函数用法.doc

精品目录1、alter table.add/drop. 向表中添加、删除列22、Truncate 删除表中的所有数据，但不删除表23、exec sp_rename 修改表名、字段名24、复制表,只复制表结构，不复制表中数据25、identity 表中新增列，并自动编号36、Insert 与 update的用法37、行列转换 Pivot、Unpivot38、自动排序 row_number、rank、dense_rank、partition by59、字符串截取 Substring ，left ，right610、Charindex 用法711、replace替换，len显示长度，lower大写转小写，reverse逆向显示712、ceiling进1取整，floor减1取整，round保留小数位数813、convert时间转换（详细显示格式）814、时间函数datediff、dateadd比较915、时间函数datename、datepart 比较1016、查询重复记录的代码1017、waitfor延迟语句1018、case when 的用法1119、将excel导入到 sql 的方法12以下摘自网络171、SQL函数大全172、Withas 用法173、配置连接服务器19由于时间问题只整理了一部分，希望对需要的人有所帮助！1、alter table.add/drop. 向表中添加、删除列alter table castingreport -增加列add packclass varchar(20),packer1 varchar(20),floor varchar(50)alter table aa drop columnaddress -删除一列2、Truncate 删除表中的所有数据，但不删除表TRUNCATE TABLE aa 3、exec sp_rename 修改表名、字段名语法： 1、exec sp_rename 原表名,新表名2、exec sp_rename 表名.字段原名,字段新名,column-column表示要修改的是列字段名，引号可以不加exec sp_rename pvt,pvexec sp_rename 生产,product-修改表名exec sp_rename product.日期,time,column-修改字段名exec sp_rename aa.vid,id,column4、复制表,只复制表结构，不复制表中数据select * into bb from aa where 11select top(0) * into cc from aa5、identity 表中新增列，并自动编号Select identity(int,1,1) as AutoID,* into #tm from teacher order by salarydrop table teacherselect * into teacher from #tm drop table #tm6、Insert 与 update的用法例：insert into aa(age)select emp5 from pvtinsert into openquery(35,select lot,time,para from test.dbo.aa)select lot,GETDATE(),null from aupdate aaset name=b.Emp5,age=b.Emp2 from pvt as b where aa.VendorID=b.VendorIDupdate course set teachername=(select name from teachers where id=course.teacherno )update openquery(35,select lot as lotid,whtime from test.dbo.aa where whtime is null) set whtime=b.time from w_lothistory as b where lotid=b.lot7、行列转换 Pivot、UnpivotPivot （行转列）执行以下语句完成行转列：select * from (select name,course,score from tb) as pvtpivot(max(score) for course in(语文,数学,物理) as aselect a.* from (select lot,time,eqp,substring(para,1,charindex(:,para)-1) as para,substring(para,charindex(:,para)+1,len(para) as valuefrom aa where eqp=检测 and time between 2012-2-9 and 2012-2-10) as pvtpivot(max(value) for para in (有效长度(mm),崩边(mm),晶棒异常) as aUnpivot （列转行）执行以下语句完成列转行select name,course,score from(select name,chinese,math,physical from bb) as unpvtunpivot(score for course in(chinese,math,physical) as a-select * from (select grade,SUM(score) as score,SUM(age) as age from student group by grade) as unpvtunpivot(value for type in(score,age) as a8、自动排序 row_number、rank、dense_rank、partition by语法：row_number() over (partition by column1 order by column2)dense_rank() over (partition by column1 order by column2)rank() over (partition by column1 order by column2)区别：row_number() 没有重复值的排序（即使两条记录相等也不重复）dense_rank() 连续排序，两个第1名接着是第2名rank() 跳跃排序，两个第1名接着是第3名-partition by column1 -实现组内排序-下面是 partition by column1 示例：select *,ROW_NUMBER() over (partition by name order by score desc) as row_numberfrom tb执行后结果 -下面是row_number,dense_rank，rank三者区别的示例：执行下面语句后结果如下：（新增列进行排序）select userid,name,salary ,row_number() over (order by salary) as row_number,RANK() over (order by salary) as rank,DENSE_RANK() over (order by salary) as dense_rankfrom employee9、字符串截取 Substring ，left ，right语法：substring(expression,start,length)Left(expression,length)Right(expression,length)示例：1、select SUBSTRING(abcdefg,3,2) as value -返回值:cdselect LEFT(abcdefg,2) as value-返回值：abselect RIGHT(abcdefg,2) as value -返回值：fg2、substring | . 新建表如下：use testcreate table func1(id varchar(10),information varchar(50)insert into func1values(1001,My name is you.li)-截取 you语句：select id,SUBSTRING(information,CHARINDEX(.,information)-3,3) as surname from func1-返回值: you10、Charindex 用法上面用到 CHARINDEX(.,information) 就是判断information字段中是否含有 . ，如果有就返回 . 出现的位置，如果没有则返回0例：select CHARINDEX(g,very good) -返回值为6 （空格占一位）11、replace替换，len显示长度，lower大写转小写，reverse逆向显示select REPLACE (abcdefg,de,123) - abc123fg,用第三个表达式代替第一个表达式中出现的第二个表达式select LEN (adfasdfaoisdfup) - 15，显示字符串的长度select LOWER(DFaflkDK) - dfaflkdk 将字符串中的大写改成小写select reverse(abcd) -dcba,返回字符表达式的逆向表达式12、ceiling进1取整，floor减1取整，round保留小数位数select CEILING(12.358) -进1取整 结果：13 select FLOOR(12.758) -减1取整 结果：12 select ROUND(12.226322,2)-保留2位小数 结果：12.2313、convert时间转换（详细显示格式）语句：执行结果：SELECT CONVERT(varchar(100), getdate(), 0) -:24 06 2009 21:57PM SELECT CONVERT(varchar(100), getdate(), 1) -: 06/24/09 SELECT CONVERT(varchar(100), getdate(), 2) -: 09.06.24 SELECT CONVERT(varchar(100), getdate(), 3) -: 24/06/09 SELECT CONVERT(varchar(100), getdate(), 4) -: 24.06.09 SELECT CONVERT(varchar(100), getdate(), 5) -: 24-06-09 SELECT CONVERT(varchar(100), getdate(), 6) -: 24 06 09 SELECT CONVERT(varchar(100), getdate(), 7) -: 06 24, 09 SELECT CONVERT(varchar(100), getdate(),8) -: 10:57:46 SELECT CONVERT(varchar(100), getdate(), 9)-: 06 24 2009 21:57:46:827AM SELECT CONVERT(varchar(100), getdate(), 10) -: 06-24-09 SELECT CONVERT(varchar(100), getdate(), 11) -: 09/06/24 SELECT CONVERT(varchar(100), getdate(), 12) -: 090624 SELECT CONVERT(varchar(100), getdate(), 13) -: 24 06 2009 21:57:46:937 SELECT CONVERT(varchar(100), getdate(), 14) -: 10:57:46:967 SELECT CONVERT(varchar(100), getdate(), 20) -: 2009-06-24 21:57:47 SELECT CONVERT(varchar(100), getdate(), 21) -: 2009-06-24 21:57:47.157 SELECT CONVERT(varchar(100), getdate(), 22) -: 06/24/09 21:57:47 PM SELECT CONVERT(varchar(100), getdate(), 23) -: 2009-06-24 SELECT CONVERT(varchar(100), getdate(), 24) -: 10:57:47 SELECT CONVERT(varchar(100), getdate(), 25) -: 2009-06-24 21:57:47.250 SELECT CONVERT(varchar(100), cdate, 100) -: 06 24 2009 21:57PM SELECT CONVERT(varchar(100), cdate, 101) -: 06/24/2009 SELECT CONVERT(varchar(100), cdate, 102) -: 2009.06.24 SELECT CONVERT(varchar(100), cdate, 103) -: 24/06/2009 SELECT CONVERT(varchar(100), cdate, 104) -: 24.06.2009 SELECT CONVERT(varchar(100), cdate, 105) -: 24-06-2009 SELECT CONVERT(varchar(100), cdate, 106) -: 24 06 2009 SELECT CONVERT(varchar(100), cdate, 107) -: 06 24, 2009 SELECT CONVERT(varchar(100), cdate, 108) -: 10:57:49 SELECT CONVERT(varchar(100), cdate, 109) -: 06 24 2009 21:57:49:437PM SELECT CONVERT(varchar(100), cdate, 110) -: 06-24-2009 SELECT CONVERT(varchar(100), cdate, 111) -: 2009/06/24 SELECT CONVERT(varchar(100), cdate, 112) -: 20090624 SELECT CONVERT(varchar(100), cdate, 113) -: 24 06 2009 21:57:49:513 SELECT CONVERT(varchar(100), cdate, 114) -: 10:57:49:547 SELECT CONVERT(varchar(100), cdate, 120) -: 2009-06-24 21:57:4914、时间函数datediff、dateadd比较-datediff返回两个日期之间的天数-到年底还有多少周 select DATEDIFF(week,GETDATE(),2012-12-29) select convert(varchar,(DATEDIFF(week,GETDATE(),2011-12-29)+周 -到年底还有多少天select DATEDIFF(DAY,GETDATE(),2011-12-29) -到年底还有多少小时 select DateDiff(hh,getDate(),2011-12-29)-dateadd日期中添加或减去指定的时间间隔 select dateadd(year,2,2011) -返回：-01-01 00:00:00.000 select dateadd(month,2,2011) -返回：-03-01 00:00:00.000 select dateadd(week,2,2011) -2011-01-15 00:00:00.000 select dateadd(weekday,2,2011) -2011-01-03 00:00:00.000 select dateadd(day,2,2011) -2011-01-03 00:00:00.000 -本周第一天select dateadd(day,2-datepart(dw,getdate(),getdate() select dateadd(day,2-datepart(dw,convert(date,getdate(),convert(date,getdate() -本周最后一天select dateadd(day,8-datepart(dw,getdate(),getdate() select dateadd(day,8-datepart(dw,cast(getdate() as DATE),cast(getdate() as DATE) 15、时间函数datename、datepart 比较 -用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等- datename 返回varchar ，datepart 返回int 2012-2-27 select datename(week,GETDATE() - 9 select datename(weekday,getdate() - 星期一 select DATENAME(year,getdate() -2012 select DATENAME(month,getdate() -02 select DATENAME(day,getdate() -27 -比较 select datepart(week,GETDATE() - 9 select datepart(weekday,getdate() - 2 select DATEPART(year,getdate() -2012 select DATEPART(month,getdate() -2 select DATEPART(day,getdate() -2716、查询重复记录的代码-查询重复记录：select * from abcd where lot in (select lot from abcd where time between 2010-2-20 and 2012-2-28group by lot having COUNT(lot)1 )-删除重复记录，只保留一条：use ddselect distinct * into #t from aadrop table aaselect * into aa from #tdrop table #t17、waitfor延迟语句-等待5秒执行：waitfor delay 00:00:05use ddselect * from aa-到给定时间执行：waitfor time 10:51:30use ddselect * from aa18、case when 的用法例：use testselect case when (salary=1500 and salary=1800 then 2 end as level,avg(salary) as avgsalary,count(*) as qtyfrom employeegroup by case when (salary=1500 and salary=1800 then 2 end计算工资等级，执行结果如下：19、将excel导入到 sql 的方法1、先执行：exec sp_configure show advanced options,1reconfigureexec sp_configure Ad Hoc Distributed Queries,1reconfigure执行结果：2、然后：-excel 2007 导入SELECT * INTO aa FROM OPENDATASOURCE(Microsoft.Ace.OLEDB.12.0, Data Source=C:Documents and Settingsyou.li桌面12345.xls;Extended Properties=Excel 12.0).sheet1$-excel 2003 导入SELECT * INTO aa FROM OPENDATASOURCE(Microsoft.Jet.OLEDB.4.0, Data Source=C:Documents and Settingsyou.li桌面12345.xls;Extended Properties=Excel 8.0).sheet1$（使用 SQL向导 导入更方便灵活）1 .2 选择数据源 .3 目标路径 .4、编辑映射，导入到目标表之前可以根据需要修改，也可以默认，如下：5、完成以下摘自网络1、SQL函数大全/moss_tan_jun/archive/2010/08/23/1806861.html2、Withas 用法一WITH AS的含义 WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。 下面是CTE的语法： WITH ,n := expression_name ( column_name ,n ) AS ( CTE_query_definition )在使用CTE时应注意如下几点：1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE：2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示：withcte1 as( select * from table1 where name like abc%),cte2 as( select * from table2 where id 20) select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id3、如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了4. CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。实例：with datas as (select * from (select lot,eqp,substring(para,1,charindex(:,para)-1) as para,substring(para,charindex(:,para)+1,len(para) as valuefrom p_lothistory where eqp=装料站) as pvt pivot(max(value) for para in(装料人员工号(1),装料人员工号(2),装料人员工号(3),底板,护板,盖板) as a)Selecta.lot,time,quantity,qty,parts,si,mainid,subid,a.class,a.eqp,lasttime,result,checkintime,checkouttime,modeid,runtime,inserttime,c.class as packclass, as packer1, as packer2, as packer3,底板 AS floor,护板 AS guard,盖板 AS cover from openquery(35,select * from casting.dbo.castingreport) as a inner join datas as b on a.lot=b.lot and time between begindate and enddateleft join openquery(35,select * from public.dbo.employee1) as c on b.装料人员工号(1)=right(c.id,5) collate Chinese_PRC_CI_ASleft join openquery(35,select * from public.dbo.employee1) as d on b.装料人员工号(2)=right(d.id,5) collate Chinese_PRC_CI_ASleft join openquery(35,select * from public.dbo.employee1) as e on b.装料人员工号(3)=right(e.id,5) collate Chinese_PRC_CI_AS3、配置连接服务器配置从 SQL Server 2000 到 SQL Server 2008 的链接服务器连接如果要将 SQL Server 2000 中的 Project Server 2003 数据