T—SQL用法剪辑，方便以后查看

一、用T-SQL查询表中第n行到第m行数据的写法示例

假设这里的n=6，m=10则有以下两种写法，qusID可以不连续，如下：

select top 5 * from tb_wenti where qusID not in(select top 5 qusID from tb_wenti);

select top 5 * from tb_wenti where qusID in(select top 10 qusID from tb_wenti) order by qusID desc;

一般的写法为

select top m-n+1 * from tablename where id not in(select top n-1 id from tablename);

select top m-n+1 * from tablename where id in(select top m id from tablename) order by id desc;

 二、从学生表（Student）里分别统计男生人数和女生人数（用一条SQL语句）

select distinct (select count(*) from Student where 性别='男') 男生数,(select count(*) from Student where 性别='女') 女生数 from Student;

其结果如图

三、查询数据库的逻辑文件名

（1）对象为数据库

select name, filename, * from dbo.sysfiles

*需要指定查询的数据库

（2）对象为数据库备份文件

restore filelistonly from disk =‘完整备份文件路径’

*使用master数据库

 四、断开数据库链接的存储过程

第一种方式

create proc killspid 
 @dbname sysname 
 as 
 declare @s nvarchar(1000) 
 declare tb cursor local for 
 select N'kill '+cast(spid as varchar) 
 from master..sysprocesses 
 where dbid=db_id(@dbname) 
 
 open tb 
 fetch next from tb into @s 
 while @@fetch_status=0 
 begin 
 exec(@s) 
 fetch next from tb into @s 
 end 
 close tb 
 deallocate tb 
go

第二种方式（在程序中使用）

string sqltext = " declare   @sql   varchar(100)   \n"
                            + "while   1=1   \n"
                            + "begin   \n"
                            + "select   top   1   @sql   =   'kill   '+cast(spid   as   varchar(3)) from     master..sysprocesses       where   spid   >   50   and   spid   <>   @@spid   and dbid=db_id('"+dbname+"')   \n"
                            + "if   @@rowcount   =   0   \n" + "break   \n"
                            + "exec(@sql)   \n" + "end   \n";

五、分离数据库
sp_detach_db '数据库名称','true'

六、判断指定数据库是否已附加在SQL Server中

select count(*) From master.dbo.sysdatabases where [name]='dbname'