SQL Server常用小代码收藏

--SQL Server：
Select TOP N * From TABLE Order By NewID() 

--开头到N条记录
Select Top N * From 表

--N到M条记录(要有主索引ID)
Select Top M-N * From 表Where ID in (Select Top M ID From 表) Order by ID  Desc

--选择10从到15的记录
select top 5 * from (select top 15 * from table order by id asc) A  order by id desc 

--N到结尾记录
Select Top N * From 表Order by ID Desc
 

  

--显示最后5条记录,但是显示的顺序必须为5,6,7,8,9,10,而不是10,9,8,7,6,5 如下解决方法:

select top 5 from test where id in(select top 5 from test order by id desc) order by id asc


--通过这个问题也能总结出4-10条,5-100条这种限定一定范围内的sql语句的写法:

select top <末端ID-顶端ID+1> * from <表名> where ID not in(select top <顶端ID-1>) ID from <表名>)

--例如:4-10条就应该写成
select top 10-4+1 * from test where id not in(select top 4-1 id from test)

  

 上一篇: select top 1 * from [news_table] where [新闻标识列]<当前id号 where ......
 下一篇: select top 1 * from [news_table] where [新闻标识列]>当前id号 where ...... order by [新闻标识列] desc

 

 

--最新发布的20条信息列表，要求包含：信息ID、信息标题、信息发布时间、信息发布人姓名、信息评论总数和最后评论时间，并且按最后评论时间排序
       
       Select Top(100) a.infoID,a.infoTitle,a.infoPubDate,c.userName,Max( b.infoReplyDate ) As 最后时间,Count( b.infoReplyID ) As 评论总数
       From info a
       Left Join [InfoReply] b On a.infoID = b.infoID
       Left Join [User] c On a.infoPubUser = c.userNo
                             And b.infoReplyUser = c.userNo
      Group By a.infoPubDate,a.infoID,a.infoTitle,c.userName
      Order By Max( a.infoPubDate ) desc 

 

一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
select id, Count（*) from tb group by id having count(*)>1


--两条记录完全相同，如何删除其中一条
set rowcount=1 
delete from thetablename where id=@duplicate_id--@duplicate_id为重复值的id 


--模糊查询
select *  from product where detail like '%123.jpg%'
--替换字段里面部分内容
update product set detail=replace(cast(detail as varchar(8000)),'abc.jpg','efg.jpg') 

 

--日期转换参数,值得收藏
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08 

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608

select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12

select CONVERT(varchar(12) , getdate(), 112 )
20040912

select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12

 

--一个月第一天
SELECT   DATEADD(mm,   DATEDIFF(mm,0,getdate()),   0)  --  2009-06-01 00:00:00.000

--当天
select  * from product  where DateDiff(day,modiDate,GetDate())>1 

--如何查询本日、本月、本年的记录SQL
本年：
select * from loanInfo where year(date)=year(getdate()) 
本月：
select * from loanInfo where year(date)=year(getDate()) And month(date)=month(getdate()) 
本日：
select * from loanInfo where year(date)=year(getDate()) And month(date)=month(getdate()) and Day(date)=Day(getDate())   


本周的星期一   SELECT  DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0)  
 
一年的第一天  SELECT  DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0)  
 
季度的第一天   SELECT  DATEADD(qq,  DATEDIFF(qq,0,getdate()),  0)  
 
当天的半夜   SELECT  DATEADD(dd,  DATEDIFF(dd,0,getdate()),  0)  
 
上个月的最后一天 
 
       这是一个计算上个月最后一天的例子。它通过从一个月的最后一天这个例子上减去毫秒来获得。有一点要记住，在Sql  Server中时间是精确到毫秒。这就是为什么我需要减去毫秒来获得我要的日期和时间。 
 
       SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0))  
 
       计算出来的日期的时间部分包含了一个Sql  Server可以记录的一天的最后时刻(“:59:59:997”)的时间。 
 
去年的最后一天 
 
       连接上面的例子，为了要得到去年的最后一天，你需要在今年的第一天上减去毫秒。 
 
       SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0))  
 
本月的最后一天 
 
       现在，为了获得本月的最后一天，我需要稍微修改一下获得上个月的最后一天的语句。修改需要给用DATEDIFF比较当前日期和“-01-01”返回的时间间隔上加。通过加个月，我计算出下个月的第一天，然后减去毫秒，这样就计算出了这个月的最后一天。这是计算本月最后一天的SQL脚本。 
 
       SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))  
 
本年的最后一天 
 
       你现在应该掌握这个的做法，这是计算本年最后一天脚本 
 
       SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate())+1,  0))。 
 
本月的第一个星期一 
 
       好了，现在是最后一个例子。这里我要计算这个月的第一个星期一。这是计算的脚本。 
 
         select  DATEADD(wk,  DATEDIFF(wk,0,                                                          
                               dateadd(dd,6-datepart(day,getdate()),getdate())        
                                                                                                 ),  0)                          
 
       在这个例子里，我使用了“本周的星期一”的脚本，并作了一点点修改。修改的部分是把原来脚本中“getdate()”部分替换成计算本月的第天，在计算中用本月的第天来替换当前日期使得计算可以获得这个月的第一个星期一。 


--删除一个月前,三个月前, 6个月前,一年前的数据 

DELETE FROM 表名WHERE datediff(MM, AddTime,GETDATE()) > 1
DELETE FROM 表名WHERE datediff(MM, AddTime,GETDATE()) > 3
DELETE FROM 表名WHERE datediff(MM, AddTime,GETDATE()) > 6
DELETE FROM 表名WHERE datediff(YY, AddTime,GETDATE()) > 1

---------------------------------------------------------------  
附录，其他日期处理方法 
 
1)去掉时分秒 
declare  @  datetime  
set  @  =  getdate()  --'2003-7-1  10:00:00'  
SELECT  @,DATEADD(day,  DATEDIFF(day,0,@),  0)  
 
2）显示星期几 
select  datename(weekday,getdate())    
 
3）如何取得某个月的天数 
declare  @m  int  
set  @m=2  --月份 
select    datediff(day,'2003-'+cast(@m  as  varchar)+'-15'  ,'2003-'+cast(@m+1    as  varchar)+'-15')  

另外，取得本月天数 
select    datediff(day,cast(month(GetDate())  as  varchar)+'-'+cast(month(GetDate())  as  varchar)+'-15'  ,cast(month(GetDate())  as  varchar)+'-'+cast(month(GetDate())+1    as  varchar)+'-15')  

任意月份的最大天数
select  day(dateadd(dd,-1,dateadd(mm,1,Dateadd(mm,datediff(mm,0,getdate()),0)))) 


或者使用计算本月的最后一天的脚本，然后用DAY函数区最后一天 
SELECT  Day(dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0)))  
 
4）判断是否闰年： 

SELECT  case  day(dateadd(mm,  2,  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0))))  when  28  then  '平年'  else  '闰年'  end  

或者 
select  case  datediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-01'))  
when  28  then  '平年'  else  '闰年'  end  
 
5）一个季度多少天 
declare  @m  tinyint,@time  smalldatetime  
select  @m=month(getdate())  
select  @m=case  when  @m  between  1  and  3  then  1  
                       when  @m  between  4  and  6  then  4  
                       when  @m  between  7  and  9  then  7  
                       else  10  end  
select  @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'  
select  datediff(day,@time,dateadd(mm,3,@time))   


 

 1、确定某年某月有多少天

实现原理：先利用DATEDIFF取得当前月的第一天，再将月份加一取得下月第一天，然后减去分钟，再取日期的天数部分，即为当月最大日期，也即当月天数

CREATE FUNCTION DaysInMonth ( @date datetime ) Returns int
AS
BEGIN
 RETURN Day(dateadd(mi,-3,DATEADD(m, DATEDIFF(m,0,@date)+1,0)))
END

调用示例：

select dbo.DaysInMonth ('2006-02-03') 

(2)计算哪一天是本周的星期一

SELECT DATEADD(week, DATEDIFF(week,'1900-01-01',getdate()), '1900-01-01')  --返回-11-06 00:00:00.000
或
SELECT DATEADD(week, DATEDIFF(week,0,getdate()),0)      

(3)当前季度的第一天

SELECT DATEADD(quarter, DATEDIFF(quarter,0,getdate()), 0)—返回-10-01 00:00:00.000 

(4)一个季度多少天

declare @m tinyint,@time smalldatetime 

select @m=month(getdate()) 
select @m=case when @m between 1 and 3 then 1 
                       when @m between 4 and 6 then 4 
                       when @m between 7  and 9 then 7 
                       else 10 end 

select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01' 
select datediff(day,@time,dateadd(mm,3,@time)) —返回



 

1.按姓氏笔画排序: 
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 

2.分页SQL语句
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from 表名As tab) As t where rownum between 起始位置And 结束位置

8.如何修改数据库的名称:

sp_renamedb 'old_name', 'new_name' 


3.获取当前数据库中的所有用户表
select * from sysobjects where xtype='U' and category=0 

4.获取某一个表的所有字段
select name from syscolumns where id=object_id('表名') 

5.查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%' 

6.查看当前数据库中所有存储过程
select name as 存储过程名称from sysobjects where xtype='P' 

7.查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa') 
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01 

8.查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns where table_name = '表名' 

 

 

9.使用事务
在使用一些对数据库表的临时的SQL语句操作时，可以采用SQL SERVER事务处理，防止对数据操作后发现误操作问题

开始事务
Begin tran 
Insert Into TableName Values(…) 
SQL语句操作不正常，则回滚事务。

回滚事务
Rollback tran 
SQL语句操作正常，则提交事务，数据提交至数据库。

提交事务
Commit tran 


计算执行SQL语句查询时间

declare @d datetime 
set @d=getdate() 
select * from SYS_ColumnProperties select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 



【关闭SQL Server 数据库所有使用连接】

use  master 
go 
create  proc  KillSpByDbName(@dbname  varchar(20))  
as  
begin  
declare  @sql  nvarchar(500),@temp varchar(1000) 
declare  @spid  int  
set  @sql='declare  getspid  cursor  for    
select  spid  from  sysprocesses  where  dbid=db_id('''+@dbname+''')'  
exec  (@sql)  
open  getspid  
fetch  next  from  getspid  into  @spid  
while  @@fetch_status <>-1  
begin  
  set @temp='kill  '+rtrim(@spid) 
  exec(@temp) 
fetch  next  from  getspid  into  @spid  
end  
close  getspid  
deallocate  getspid  
end  

--举例使用，关闭数据库下的所有连接操作
Use  master  
Exec  KillSpByDbName  '数据库名称' 


（一）挂起操作
在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作，要求重启，这里往往重启无用，解决办法：
到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager
删除PendingFileRenameOperations

（二）收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE

（三）压缩数据库
dbcc shrinkdatabase(dbname)

（四）转移数据库给新用户以已存在用户权限
exec sp_change_users_login update_one,newname,oldname
go

（五）检查备份集
RESTORE VERIFYONLY from disk=Evbbs.bak

（六）修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB(dvbbs,repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO

 

 


select top m * from tablename where id not in (select top n id from tablename) 


select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n 
select * from 表变量order by columnname desc 


select top n * from 
(select top m * from tablename order by columnname) a 
order by columnname desc 





复制表(只复制结构,源表名：a 新表名：b) (Access可用) 
法一：select * into b from a where 1 <>1 
法二：select top 0 * into b from a 

拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用) 
insert into b(a, b, c) select d,e,f from b; 

跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) 
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’where 条件
例子：..from b in '"&Server.MapPath(".")&"/data.mdb" &"' where.. 

子查询(表名：a 表名：b) 
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3) 

显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 

外连接查询(表名：a 表名：b) 
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 

在线视图查询(表名：a ) 
select * from (SELECT a,b,c FROM a) T where t.a > 1; 

between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2 
select a,b,c, from table1 where a not between 数值and 数值

in 的使用方法
select * from table1 where a [not] in (‘值’,’值’,’值’,’值’) 

两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 

四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .. 

日程安排提前五分钟提醒
SQL: select * from 日程安排where datediff('minute',f开始时间,getdate())>5 

一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段from 表名order by 排序字段desc) a,表名b where b.主键字段= a.主键字段order by a.排序字段


选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) 
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 

包括所有在TableA 中但不在TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC) 

随机取出条数据
select top 10 * from tablename order by newid() 

随机选择记录
select newid() 

 

  

删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,) 

select distinct * into #Tmp from TB
drop table TB
select * into TB from #Tmp
drop table #Tmp

 

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断   
select   *   from   people   
where   peopleId   in   (select     peopleId     from     people     group     by     peopleId     having     count(peopleId)   >   1)   
    
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录   
delete   from   people     
where   peopleId     in   (select     peopleId     from   people     group     by     peopleId       having     count(peopleId)   >   1)   
and   rowid   not   in   (select   min(rowid)   from     people     group   by   peopleId     having   count(peopleId   )>1)   
    
3、查找表中多余的重复记录（多个字段）     
select   *   from   vitae   a   
where   (a.peopleId,a.seq)   in     (select   peopleId,seq   from   vitae   group   by   peopleId,seq     having   count(*)   >   1)   
    
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录   
delete   from   vitae   a   
where   (a.peopleId,a.seq)   in     (select   peopleId,seq   from   vitae   group   by   peopleId,seq   having   count(*)   >   1)   
and   rowid   not   in   (select   min(rowid)   from   vitae   group   by   peopleId,seq   having   count(*)>1)   
    
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录   
select   *   from   vitae   a   
where   (a.peopleId,a.seq)   in     (select   peopleId,seq   from   vitae   group   by   peopleId,seq   having   count(*)   >   1)   
and   rowid   not   in   (select   min(rowid)   from   vitae   group   by   peopleId,seq   having   count(*)>1) 




--float字段保留一位小数，四舍五入

SELECT CONVERT(DECIMAL(18,1),1024.791454) ------- 1024.8 （所影响的行数为1 行）
 <%# Eval("字段")==null?"":Eval("字段").toString("0.0") %>