《经典SQL语句大全-yubinfeng-博客园》由会员分享，可在线阅读，更多相关《经典SQL语句大全-yubinfeng-博客园（13页珍藏版）》请在金锄头文库上搜索。
1、经典SQL语句大全 - yubinfeng - 博客园、说明：一条sql 语句搞定数据库分页select top 10 b.* from (select top 20 主键字段 ,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段具体实现：关于数据库分页：declare start int,end intsql nvarchar(600)set sql=select top+str(end-start+1)+from T where rid not in(select top+str(str
2、-1)+Rid from T where Rid-1)exec sp_executesql sql注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果 top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）alter table tablename-添加一个自增列add column_b int identity(1,1) delete from tablename where column_b n
3、ot in(select max(column_b) from tablename group by column1,column2,.)alter table tablename drop column column_bTRUNCATE TABLE table1“where 1=1” 是表示选择全部 “where 1=2”全部不选，如：if strWhere !=beginset strSQL = select count(*) as Total from + tblName + where + strWhereendelsebeginset strSQL = select count(*)
4、 as Total from + tblName + end错误！未找到目录项。set strSQL = select count(*) as Total from + tblName + where 1=1 安定 + strWhere 2、收缩数据库-重建索引DBCC REINDEXDBCC INDEXDEFRAG-收缩数据和日志DBCC SHRINKDBDBCC SHRINKFILEUSE tablename - 要操作的数据库名SELECT LogicalFileName = tablename_log, - 日志文件名MaxMinutes = 10, - Limit on time a
5、llowed to wrap log.NewSize = 1 - 你想设定的日志文件的大小(M)Setup / initializeDECLARE OriginalSize intSELECT OriginalSize = sizeFROM sysfilesWHERE name = LogicalFileNameSELECT Original Size of + db_name() + LOG is +CONVERT(VARCHAR(30),OriginalSize) + 8K pages or +CONVERT(VARCHAR(30),(OriginalSize*8/1024) + MBFR
6、OM sysfilesWHERE name = LogicalFileNameCREATE TABLE DummyTrans(DummyColumn char (8000) not null)DECLARE Counter INT,StartTime DATETIME,TruncLog VARCHAR(255)SELECT StartTime = GETDATE(),TruncLog = BACKUP LOG + db_name() + WITH TRUNCATE_ONLYDBCC SHRINKFILE (LogicalFileName, NewSize)EXEC (TruncLog)- Wr
7、ap the log if necessary.WHILE MaxMinutes DATEDIFF (mi, StartTime, GETDATE() - time has not expiredAND OriginalSize = (SELECT size FROM sysfiles WHERE name = LogicalFileName) AND (OriginalSize * 8 /1024) NewSize BEGIN - Outer loop.SELECT Counter = 0WHILE (Counter OriginalSize / 16) AND (Counter 50000
8、)BEGIN - updateINSERT DummyTrans VALUES (Fill Log) DELETE DummyTransSELECT Counter = Counter + 1ENDEXEC (TruncLog) ENDSELECT Final Size of + db_name() + LOG is +CONVERT(VARCHAR(30),size) + 8K pages or +CONVERT(VARCHAR(30),(size*8/1024) + MBFROM sysfilesWHERE name = LogicalFileNameDROP TABLE DummyTra
9、nsSET NOCOUNT OFFCREATE PROCEDURE dbo.User_ChangeObjectOwnerBatchOldOwner as NVARCHAR(128),NewOwner as NVARCHAR(128)ASDECLARE Name as NVARCHAR(128)DECLARE Owner as NVARCHAR(128)DECLARE OwnerName as NVARCHAR(128)DECLARE curObject CURSOR FORselect Name = name,Owner = user_name(uid)from sysobjectswhere
10、 user_name(uid)=OldOwnerorder by nameOPEN curObjectFETCH NEXT FROM curObject INTO Name, OwnerWHILE(FETCH_STATUS=0)BEGIN if Owner=OldOwnerbeginset OwnerName = OldOwner + . + rtrim(Name)exec sp_changeobjectowner OwnerName, NewOwnerend- select name,NewOwner,OldOwnerFETCH NEXT FROM curObject INTO Name,
11、OwnerENDdeclare i intset i=1while i-1例2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。set s = select top 1 * from T where pid not in (select top + str(count-1) + pid from T)print s exec sp_executesql s两种方式的效果相同select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like %表名%select * from master.s
12、ysdatabases D where sid not in(select sid from master.syslogins where name=sa)或者select dbid, name AS DB_NAME from master.sysdatabases where sid 0x01exec sp_dropserver ITSV , droplogins update bon a.column1=b.column1select *update bSELECT *基本函数2,substring(expression,start,length) 取子串，字符串的下标是从“1”，start为起始位置， length为字符串长度，实际应用中以len(expression)取
