目录
1、alter table….add/drop…. 向表中添加、删除列 ....................................................................... 2 2、Truncate 删除表中的所有数据，但不删除表 ......................................................................... 2 3、exec sp_rename 修改表名、字段名 ........................................................................................ 2 4、复制表,只复制表结构，不复制表中数据 ................................................................................ 3 5、identity 表中新增列，并自动编号 .......................................................................................... 3 6、Insert 与 update的用法 ........................................................................................................... 3 7、行列转换 Pivot、Unpivot .......................................................................................................... 4 8、自动排序 row_number、rank、dense_rank、partition by ................................................... 7 9、字符串截取 Substring ，left ，right ....................................................................................... 9 10、Charindex 用法 ...................................................................................................................... 11 11、replace替换，len显示长度，lower大写转小写，reverse逆向显示 ............................... 11 12、ceiling进1取整，floor减1取整，round保留小数位数 .................................................. 12 13、convert时间转换（详细显示格式） ................................................................................... 12 14、时间函数datediff、dateadd比较 ......................................................................................... 15 15、时间函数datename、datepart 比较 ................................................................................... 17 16、查询重复记录的代码 ............................................................................................................. 18 17、waitfor延迟语句 .................................................................................................................... 18 18、case when 的用法.................................................................................................................. 19 19、将excel导入到 sql 的方法 .................................................................................................. 21 以下摘自网络 ................................................................................................................................. 26
1、SQL函数大全 .............................................................................................................................................................. 26
2、With…as 用法 ............................................................................................................................................................. 26 3、配置连接服务器 .......................................................................................................................................................... 30
由于时间问题只整理了一部分，希望对需要的人有所帮助！
1、alter table….add/drop…. 向表中添加、删除列
alter table castingreport
--增加列
add [packclass] varchar(20),[packer1] varchar(20),[floor] varchar(50)
alter table aa drop column[address] --删除一列
2、Truncate 删除表中的所有数据，但不删除表
TRUNCATE TABLE aa
3、exec sp_rename 修改表名、字段名
语法： 1、exec sp_rename 原表名,'新表名'
2、exec sp_rename '表名.[字段原名]','字段新名','column'
--column表示要修改的是列字段名，引号可以不加
exec sp_rename pvt,pv
exec sp_rename 生产,'product' --修改表名
exec sp_rename 'product.[日期]','time','column' --修改字段名
exec sp_rename 'aa.vid',id,'column'
4、复制表,只复制表结构，不复制表中数据
select * into bb from aa where 1<>1
select top(0) * into cc from aa
5、identity 表中新增列，并自动编号
Select identity(int,1,1) as AutoID,* into #tmdrop table teacher
select * into teacher from #tm
drop table #tm
6、Insert 与 update的用法
例：
insert into aa(age)
select emp5 from pvt
from teacher order by salary
insert into openquery([10.32.142.235],'select lot,time,para from test.dbo.aa')
select lot,GETDATE(),null from a
update aa
set name=b.Emp5,age=b.Emp2 from pvt as b where aa.VendorID=b.VendorID
update course
set teachername=(select name from teachers where id=course.teacherno )
update openquery([10.32.142.235],'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.lot
7、行列转换 Pivot、Unpivot
Pivot （行转列）
执行以下语句完成行转列：
select * from (
select name,course,score from tb) as pvt
pivot
(max(score) for course in([语文],[数学],[物理])) as a
select a.* from (
select lot,time,eqp,substring(para,1,charindex(':',para)-1) as para,substring(para,charindex(':',para)+1,len(para)) as value
from aa
where eqp='检测' and time between '2012-2-9' and '2012-2-10') as pvt
pivot
(max(value) for para in ([有效长度(mm)],[崩边(mm)],[晶棒异常])) as a
Unpivot （列转行）
执行以下语句完成列转行
select name,course,score from(
select name,chinese,math,physical from bb) as unpvt
unpivot
(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 unpvt
unpivot
(value for [type] in([score],[age])) as a
8、自动排序 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_number]
from 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_rank]
from employee
9、字符串截取 Substring ，left ，right
语法： substring(expression,start,length)
Left(expression,length)
Right(expression,length)
示例：1、select SUBSTRING('abcdefg',3,2) as value --返回值:cd
select LEFT('abcdefg',2) as value --返回值：ab
select RIGHT('abcdefg',2) as value --返回值：fg
2、substring | . 新建表如下：
use test
create table func1(id varchar(10),information varchar(50))
insert into func1
values('1001','My name is you.li')
--截取 ‘you’语句：
select id,SUBSTRING(information,CHARINDEX('.',information)-3,3) as surname from func1
--返回值: you
10、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.23
13、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) SELECT CONVERT(varchar(100), cdate, 103) SELECT CONVERT(varchar(100), cdate, 104) SELECT CONVERT(varchar(100), cdate, 105) SELECT CONVERT(varchar(100), cdate, 106) SELECT CONVERT(varchar(100), cdate, 107) SELECT CONVERT(varchar(100), cdate, 108) SELECT CONVERT(varchar(100), cdate, 109) SELECT CONVERT(varchar(100), cdate, 110) SELECT CONVERT(varchar(100), cdate, 111) --: 2009.06.24
--: 24/06/2009
--: 24.06.2009
--: 24-06-2009
--: 24 06 2009
--: 06 24, 2009
--: 10:57:49
--: 06 24 2009 21:57:49:437PM --: 06-24-2009
--: 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:49
14、时间函数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()) select datename(weekday,getdate()) select DATENAME(year,getdate()) select DATENAME(month,getdate()) select DATENAME(day,getdate()) --比较
select datepart(week,GETDATE()) select datepart(weekday,getdate()) select DATEPART(year,getdate()) select DATEPART(month,getdate()) -- 9
-- 星期一
--2012
--02
--27
-- 9
-- 2
--2012
--2
select DATEPART(day,getdate()) --27
16、查询重复记录的代码
--查询重复记录：
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 dd
select distinct * into #t from aa
drop table aa
select * into aa from #t
drop table #t
17、waitfor延迟语句
--等待5秒执行：
waitfor delay '00:00:05'
use dd
select * from aa
--到给定时间执行：
waitfor time '10:51:30'
use dd
select * from aa
18、case when 的用法
例：
use test
select case when (salary>=1500 and salary<1700) then 1
when salary >=1800 then 2 end as [level],
avg(salary) as avgsalary,count(*) as qty
from employee
group by case when (salary>=1500 and salary<1700) then 1
when salary >=1800 then 2 end
计算工资等级，执行结果如下：
19、将excel导入到 sql 的方法
1、先执行：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
执行结果：
2、然后：
--excel 2007 导入
SELECT * INTO aa FROM OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0',
'Data Source=C:\\Documents and Settings\\you.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 Settings\\you.li\\桌面\\12345.xls;Extended Properties=Excel 8.0')...[sheet1$]
（使用 SQL向导 导入更方便灵活）
1 .
2 选择数据源 .
3 目标路径 .
4、编辑映射，导入到目标表之前可以根据需要修改，也可以默认，如下：
5、完成
以下摘自网络
1、SQL函数大全
http://www.cnblogs.com/moss_tan_jun/archive/2010/08/23/1806861.html
2、With…as 用法
一．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 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语句所示： with cte1 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.id 3、如果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 value from p_lothistory where eqp='装料站') as pvt pivot ( max(value) for para in([装料人员工号(1)],[装料人员工号(2)],[装料人员工号(3)],[底板],[护板],[盖板]) ) as a) Select a.lot,time,quantity,qty,parts,si,mainid,subid,a.class,a.eqp,lasttime,result,checkintime,checkouttime,modeid,runtime,inserttime, c.class as packclass,c.name as packer1,d.name as packer2,e.name as packer3,[底板] AS [floor],[护板] AS guard,[盖板] AS cover from openquery([10.32.142.235],'select * from casting.dbo.castingreport') as a inner join datas as b on a.lot=b.lot and time between @begindate and @enddate left join openquery([10.32.142.235],'select * from [public].dbo.employee1') as c on b.[装料人员工号(1)]=right(c.id,5) collate Chinese_PRC_CI_AS left join openquery([10.32.142.235],'select * from [public].dbo.employee1') as d on b.[装料人员工号(2)]=right(d.id,5) collate Chinese_PRC_CI_AS left join openquery([10.32.142.235],'select * from [public].dbo.employee1') as e on b.[装料人员工号(3)]=right(e.id,5) collate Chinese_PRC_CI_AS 3、配置连接服务器 配置从 SQL Server 2000 到 SQL Server 2008 的链接服务器连接 如果要将 SQL Server 2000 中的 Project Server 2003 数据库迁移到另一台计算机上的 Office Project Server 2007，但 Office Project Server 2007 要使用的数据库服务器是 SQL Server 2008，请使用下列步骤配置链接服务器连接。 首先，在 SQL Server 2000 和 SQL Server 2008 中，将身份验证模式设置为混合模式，以便接受 SQL Server 登录和 Windows 登录。 将 SQL Server 2008 身份验证配置为混合模式 1.打开 SQL Server 2008 Management Studio。 2.在“对象资源管理器”窗格中，右键单击 SQL 2008 服务器名称，然后单击“属性”。 3.在“选择页面”列表中，单击“安全”。 4.在“服务器身份验证”部分，选择“SQL Server 和 Windows 身份验证模式”。 5.单击“确定”。 将 SQL Server 2000 身份验证配置为混合模式 1.打开 SQL Server 企业管理器。 2.展开包含将与 Project Server 2003 一起使用的数据库服务器的服务器组。 3.右键单击该服务器，然后单击“属性”。 4.单击“安全”选项卡。 5.在“身份验证”下，单击“SQL Server 和 Windows”。 6.单击“确定”。 配置连接 1.在运行 SQL Server 2000 和 SQL Server 2008 的计算机上，创建一个相同的 SQL 已验证用户帐户以运行链接服务器连接。请确保两台服务器上帐户的密码相同，并确保这些帐户对要使用链接服务器系统访问的数据库具有读/写权限。 2.在 SQL Server 2000 中打开查询分析器，对主数据库运行名为 instcat.sql 的文件。该文件应位于“Program Files”中的 SQL 文件夹下。 3.在 SQL Server 2008 Management Studio 中，选择运行 SQL Server 2008 的计算机，展开“服务器对象”，右键单击“链接服务器”，然后单击“新建链接服务器”。 a.输入运行 SQL Server 2000 的计算机的服务器名称。 b.选择“SQL Server”选项。 c.单击侧面窗格中的“安全”选项卡。 d.选择“使用此安全上下文建立连接”选项。 e.输入访问 Project Server 2003 数据库和 Project Server 2007 数据库所需的、在步骤 1 中创建的 SQL 帐户和密码。 f.单击“确定”。 4.在 SQL Server 2008 Management Studio 中，针对链接服务器启动查询以验证它是否运行。 a.SELECT * from b.使用适当的值替换服务器和数据库名称。 c.如果此查询成功返回，表明连接成功 因篇幅问题不能全部显示，请点此查看更多更全内容