1 、 select t. * from ( select * from < table 表1 > where gradeinfo like ' 一% ' union all /**/ /**/ /**/ /* 选择所有的,union 选择唯一记录*/ select * from < table 表1 > where gradeinfo like ' 高% ' )t 2 、 select DISTINCT bookID from < table 表2 > where GoodsID in ( select distinct GoodsId from < table 表3 > ) 注释: DISTINCT bookID 选择唯一的标识,筛选重复记录。 3 、 select * from < table 表1 > where gradeid between 1 and 10 注释: between 1 and 10 在两个数字之间。 4 、 select * into #aa from < table 表4 > where EditionId in ( select EditionId from < table 表5 > where SubjectId = 1 and (GradeId not in ( 3 , 4 , 5 , 6 )) and Editionid = 15 ) select * into #bb from < table 表4 > where EditionId in ( select EditionId from < table 表5 > where SubjectId = 1 and (GradeId not in ( 3 , 4 , 5 , 6 )) and Editionid <> 15 ) select * from #aa union select * from #bb drop table #aa drop table #bb注释: select * into #aa -- -- 自动生成具有相同结果的临时表 union :两表合并 数据唯一 union all :选择所有的,不筛选记录。 drop table :删除临时表。 5 、声明一个表变量: @indextable 效果同上 declare @indextable table (EditionId int ,EditionAllName varchar ( 100 ) null ,EditionName varchar ( 100 ) null ,EditionInfo varchar ( 100 ) null ) insert into @indextable select * from < table 表4 > where EditionId in ( select EditionId from < table 表5 > where SubjectId = 1 and (GradeId not in ( 3 , 4 , 5 , 6 )) and Editionid = 15 ) insert into @indextable select * from < table 表4 > where EditionId in ( select EditionId from < table 表5 > where SubjectId = 1 and (GradeId not in ( 3 , 4 , 5 , 6 )) and Editionid <> 15 ) select * from @indextable 6 、 select * from sysobjects where name like ' sxysitestudy% ' and xtype = ' u ' exec sp_help ' <procedure 存储过程> ' exec Sp_helptext ' <procedure 存储过程> ' exec sp_depends ' <procedure 存储过程> ' exec Sp_stored_procedures ' <procedure 存储过程> ' 注释: 系统存储过程、系统表操作 7 、 declare mycourse cursor for select name from sysobjects where type = ' u ' and (name like ' 表说明 ' ) open mycourse declare @name varchar ( 100 ) while ( 0 = 0 ) begin fetch next from mycourse into @name if @@fetch_status <> 0 break exec ( ' drop table ' + @name ) -- print @name end close mycourse deallocate mycourse注释: 游标声明、打开、释放删除表集合: 8 、 alter table spt_server_info 修改表结构 add name varchar ( 100 ) -- 增加一列 drop column name -- 删除一列 drop table aa 删除表 9 、 -- ------------删除用户存储过程 条件 type status select * from sysobjects where type = ' p ' and status >= 0 declare mycourse cursor for select name from sysobjects where type = ' p ' and status >= 0 -- and (name like 'down%') or (name like 'set%') open mycourse declare @name varchar ( 100 ) while ( 0 = 0 ) begin fetch next from mycourse into @name if @@fetch_status <> 0 break exec ( ' drop PROCEDURE ' + @name ) -- print @name end close mycourse deallocate mycourse 10 、 select * into bak from [ user ] -- 备份数据表 11 、 select min (id) as id,name,pass from [ user ] group by name,pass union -- 会自动筛选相同的记录 12 、 insert into [ user ] select ' 1 ' , ' 1 ' union select ' 8 ' , ' 8 ' union select ' 8 ' , ' 8 ' union select ' 1 ' , ' 1 ' 13 、 update book set PreviewImage = Replace (PreviewImage, '' , ' / ' ) where bookid = 121 14 、通用分页存储过程 create procedure < procedure 存储过程名 > @pagecount int output, @pageSize int , @pageindex int , @Name NVARCHAR ( 50 ) = NULL As set nocount on declare @row int declare @indextable table (id int Identity ( 1 , 1 ),pkid int ) declare @pagelowerbound int declare @pageupperbound int set @pagelowerbound = ( @pageindex - 1 ) * @pageSize set @pageupperbound = @pagelowerbound + @pagesize -- set ROWCOUNT @pageupperbound insert into @indextable (pkid) select pkid from < table 表 > where PhotoSubject like ' % ' + @Name + ' % ' order by CreateTime desc select @row = count ( * ) from < table 表a > a, < table 表b > b, < table 表p > p, @indextable t where p.PhotoBelong = a.PhotoVolumeID and b.YYID = a.HostID and a.OpenTypeID = 0 and p.pkid = t.pkid if @row % @pagesize = 0 set @pagecount = @row / @pageSize else set @pagecount = @row / @pageSize + 1 ; select a. < filed 字段1 > ,b. < filed 字段2 > ,b. < filed 字段3 > ,p. * from < table 表a > a, < table 表b > b, < table 表p > p, @indextable t where p. < filed字段 >= a. < filed字段 >and b. < filed字段 >= a. < filed字段 >and a. < filed字段 >= 0 and p. < field字段 >= t. < filed 字段 > and t. < filed 字段 >> @pagelowerbound and t.id <= @pageupperbound order by p. < filed 字段 > desc set nocount off declare @pagecount int exec < procedure 存储过程名 > @pagecount out, 1 , 1 , ' 元旦 ' 15 、通用分页 例子2: 方法同上 /**/ /**/ /**/ /*************************************************************************** @ColumnID INT -- 栏目ID号** @PageSize INT -- 页面大小** @PageIndex INT -- 页码** @DoCount INT -- 是否进行数量统计** output**************************************************************************/ CREATE PROCEDURE [ dbo ] . [ GetForumPostsByAll ] @ColumnID INT , @PageSize INT , @PageIndex INT , @DoCount INT AS SET NOCOUNT ON IF ( @DoCount = 1 ) SELECT COUNT (PKID) AS TotalCount FROM ForumPost WHERE ColumnID = @ColumnID AND IsAvailable = 1 ELSE BEGIN DECLARE @IndexTable TABLE (id INT IDENTITY ( 1 , 1 ),pkid INT ) DECLARE @PageLowerBound INT DECLARE @PageUpperBound INT SET @PageLowerBound = ( @PageIndex - 1 ) * @PageSize SET @PageUpperBound = @PageLowerBound + @PageSize SET ROWCOUNT @PageUpperBound INSERT INTO @IndexTable (pkid) SELECT PKID FROM ForumPost WHERE ColumnID = @ColumnID AND IsAvailable = 1 ORDER BY IsTop DESC ,LastReplyTime DESC SELECT P. * FROM ForumPost P, @IndexTable T WHERE P.PKID = T.pkid AND T.id > @PageLowerBound and T.id <= @PageUpperBound ORDER BY T.id END SET NOCOUNT OFF RETURN 0 16 、日期处理: select * from < table 表名 > where Year (OriginTime) = Year ( getdate ()) and month (OriginTime) = month ( getdate ()) and Day (OriginTime) = Day ( getdate ()) select * from < talbe 表名 > where OriginTime between ' 2006-08-21 ' and ' 2006-09-01 ' 17 、事务: CREATE PROCEDURE [ dbo ] . [ GiveScore ] @UserID INT , @Score INT AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRANSACTION :: // 准备执行的语句: COMMIT TRANSACTION RETURN 0 GO 18 、 declare @iStreamObjectId int select @iStreamObjectId = 4444 或 set @iStreamObjectId = 123 print @iStreamObjectId 注释: 给变量赋值