SQL Server各种函数用法

1、目录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大写转小写

2、，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. 向表中添加、删除列

3、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

4、'-column表示要修改的是列字段名，引号可以不加exec sp_rename pvt,pvexec sp_rename 生产,'product'-修改表名exec sp_rename 'product.日期','time','column'-修改字段名exec sp_rename 'aa.vid',id,'column'4、复制表,只复制表结构，不复制表中数据select * into bb from aa where 1<>1select top(0) * into cc

5、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.d

6、bo.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

7、 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

8、 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,mat

9、h,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 (partit

10、ion 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

11、_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

12、 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、s

13、ubstring | . 新建表如下：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('.&

14、#39;,information) 就是判断information字段中是否含有 . ，如果有就返回 . 出现的位置，如果没有则返回0例：select CHARINDEX('g','very good') -返回值为6 （空格占一位）11、replace替换，len显示长度，lower大写转小写，reverse逆向显示select REPLACE ('abcdefg','de','123') - abc123fg,用第三个表达式代替第一个表达式中出现的第二个表达式select LEN ('adfasdfao

15、isdfup') - 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时间转换（详细显示格式）语

16、句：执行结果：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 CO

17、NVERT(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(var

18、char(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(varcha

19、r(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 S

20、ELECT 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 SE

21、LECT 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),

22、 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)

23、, 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 D

24、ATEDIFF(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

25、-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 dat

26、eadd(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() -

27、 星期一 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、查询重

28、复记录的代码-查询重复记录：select * from abcd where lot in (select lot from abcd where time between '2010-2-20' and '2012-2-28'group by lot having COUNT(lot)>1 )-删除重复记录，只保留一条：use ddselect distinct * into #t from aadrop table aaselect * into aa from #tdrop table #t17、waitfor延迟语句-等待5秒执行：waitfor

29、delay '00:00:05'use ddselect * from aa-到给定时间执行：waitfor time '10:51:30'use ddselect * from aa18、case when 的用法例：use testselect case when (salary>=1500 and salary<1700) then 1when salary >=1800 then 2 end as level,avg(salary) as avgsalary,count(*) as qtyfrom employeegroup by ca

30、se when (salary>=1500 and salary<1700) then 1 when 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 O

31、PENDATASOURCE('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;Ex

32、tended Properties=Excel 8.0').sheet1$（使用 SQL向导 导入更方便灵活）1 .2 选择数据源 .3 目标路径 .4、编辑映射，导入到目标表之前可以根据需要修改，也可以默认，如下：5、完成以下摘自网络1、SQL函数大全2、Withas 用法一WITH AS的含义     WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作

33、为提供数据的部分。 特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。  &

34、#160; 下面是CTE的语法： WITH <common_table_expression> ,n <common_table_expression>:=        expression_name ( column_name ,n )     AS        ( CTE_query_definition )在使用CTE时应注意如下几点：1. CTE后面必须直接跟使用CTE的SQL语句（如sel

35、ect、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 wh

36、ere 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)

37、+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,c

38、.name 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 * fro

39、m 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 2