1. 分页sql语句
Select top 3 * from Users where UserId not in (select top 3 UserId from Users order by UserId Asc) order by UserId Asc
以上语句，查询出4，5，6条记录
Top关键字的妙用:
选取表的第100-200条记录，应使用top取表的前200条，然后逆序，并用top取其前100条记得之…
2. m:记录总数 n:每页要显示的记录数 page:页数
page = (m % n)= =0?(m / n) : (m / n +1)
Trans-Sql分页一次选取部分数据(比GridView一次绑定全部数据要快得多):
use northwind
go
declare @former datetime
set @former =getdate()
declare @PageCounter int --每页显示的数据量
set @PageCounter=15
declare @PageIndex int --要得到的页码
set @PageIndex=750
--先用表变量存储符合条件的记录
declare @table table(_id int)
insert into @table select OrderID from orders where CustomerID is null
--根据每页的大小和药得到的页码确定要选出的id范围
declare @LowerLimit int
declare @UpperLimit int
set @LowerLimit=(@PageIndex-1)*@PageCounter+1
set @UpperLimit=@PageIndex*@PageCounter
--选出目标
select O.* from Orders O,@table T
where T._id<=@UpperLimit
and T._id >= @LowerLimit
and T._id=O.OrderId
--得到执行时间（毫秒）
select inter=datediff(ms,@former,getdate())
go
3. XXXDataSource是一次查询出所有记录，然后再分页显示，降低性能。所以在企业开发中真正绑定数据是倾向于Ado.net或者其他O/R Mapping框架，如 Nhibernate和Linq
4. 表变量
DECLARE @table TABLE(userid INT,username varchar(50)) --定义表变量
INSERT INTO @table SELECT UserID,UserName FROM Users --插入数据
SELECT * FROM @table ORDER BY userid desc --从表变量获得数据
5. Sql变量的使用
declare @intData int
set @intData =100
print @intData
6. Trans-Sql：
a).if条件语句
use pubs
go
if (select avg(price) from titles where type='mod_cook') <$15
begin
print 'the following are cook book:'
print ' '
select substring(title,1,35) as title
from titles
where type='mod_cook'
end
else
print 'Average title price is more than $15.'
b).循环语句
use pubs
go
while (select avg(price) from titles) <$30
begin
update titles set price =price*2
select max(price) from titles
if(select max(price) from titles)>$50
break
else
continue
end
print 'Too much for the market to bear'
c).Case语句
use pubs
go
select category=
case type
when 'popular_comp' then 'popular computing'
when 'mod_cook' then 'Modern Cooking'
when 'business' then 'Business'
end,
cast(title as varchar(25)) as 'Shortened Title',
price as Price
from titles
where price is not null
order by type,price
compute avg(price) by type
go
d).流程控制的嵌套
use pubs
go
declare @myInt int ,@myChar char(1)
set @myInt =51
set @myChar='e'
if @myInt = 5
begin
print '执行第一个if'
print '从第一个if退出'
end
else
begin
print '执行第一个else和第二个if'
if @myChar = 'e'
print '第一个else中退出'
else
begin
print '执行第二个else'
print '从第二个else中退出'
end
end
go
use pubs
go
declare @myInt int ,@myChar char(1)
set @myInt =1
set @myChar='e'
while @myInt < 20
begin
print '变量值为：'+cast(@myInt as char(3)) +'小于20，继续执行'
set @myInt=
case @myInt
when 1 then 10
when 11 then 20
else 11
end
if @myInt =11
break
else
print '执行else'
end
go
7. 取得两次数据库操作的时间差
use northwind
go
declare @former datetime
set @former=getdate()
select * from orders
select inter=datediff(ms,@former,getdate()) --得到select操作时间
set @former=getdate()
declare @i int
set @i=0
while @i<16000
begin
insert into orders default values
set @i=@i+1
end
select inter=datediff(ms,@former,getdate())
go
8. 返回插入记录的ID
use pubs
go
insert into jobs
values('hello,word',20,120)
print @@identity
go
9. @@identity获得最后一次操作的Identtity字段的值
create procedure dbo.proc_Users_InsertUserOutPut
(
@param_userName varchar(50),
@param_userID int output
)
as
insert into Users values(@param_userName,@param_userName)
set @param_userID=@@identity
return
declare @id int
exec dbo.proc_Users_InsertUserOutPut 'dsdddqq',@id output
exec proc_Books_Users_SelectAllUser
select @id
go
create procedure dbo.proc_Users_DeleteUserReturnID
(
@param_userName varchar(50)
)
as
delete from users where UserName=@param_userName
return @@identity
declare @id int
exec @id=dbo.proc_Users_DeleteUserReturnID 'admin'
exec proc_Books_Users_SelectAllUser
select @id
go
9. 利用表变量得到前3条记录
drop procedure proc_Users_UserTableVariable
create procedure proc_Users_UserTableVariable
as
declare @tt table(ttID int,ttName varchar(50))
insert into @tt(ttID,ttName) (select top 3 UserId,UserName from Users)
select * from @tt
return
exec proc_Users_UserTableVariable
10. 通过newID（）系统函数来随机的选取n条记录，一下一选取2条为例
create procedure proc_Users_RandomSelect
as
select top 2 *
from users
order by newID()
return
go
exec proc_Users_RandomSelect
go
11. Exec的妙用
create procedure proc_Users_RandomSelectVar
(
@counter int
)
as
/*
select top @counter *
from users
order by newID()
*/
---以上语句使得@counter在前期被解析，故而出错，应在后期解析，如下
exec ('select top '+@counter+' * from Users order by newID()')
return
go
exec proc_Users_RandomSelectVar 5
go
exec proc_Users_RandomSelectVar 4
go
12. 利用存储过程进行复杂选取
create procedure proc_ComplexSelectLatest
as
select max(dates) as dates,userid
into #tempWorks
fromWorks
group by userID
select U.userID,U.userName,W.workName,W.dates
from Users as U join Works as W on (U.userId=W.userId)
join #tempUsers as T on (U.userId=T.userId and T.userId=W.userId)
drop table #tempWorks
return
exec proc_ComplexSelectLatest
13. 在数据绑定中，<%#数据源%>是通用的runat=server控件的绑定方式，而<%=数据源%>是在非服务器端控件才能用的。
14. 在嵌套DataGrid中不能用Container.DataItem，所以必须用程序给显示控件赋值。
15. 触发器,
当表之间建立了外键约束的时候用for [table] after delete 会出现问题，故而应该用
Instead of 代替after ，也就是我们执行删除语句的时候 不是真的执行删除语句 而是由触发器语句代替 触发删除的delete语句，我们然后再在触发器语句中来删除，不过此时到可以访问到deleted临时表中的数据来作为条件。
alter TRIGGER trigCategoryDelete
ON Category
instead of delete
AS
BEGIN
declare @caId int
select @caId=id from deleted
--删除评论
delete comment where newsId in (select newsId from news where caId=@caId
--删除新闻
delete news where caId=@caId
--删除类别
delete category where Id=@caId
END
GO
16. 创建一个新表同时表中不含任何数据，可以复制结构…( 答案：a)
17. CSS的选择器优先级
浙公网安备 33010602011771号