declare sql语句_那些你必须知道的sql(6/100)

大家好，我是天天。

这是我日更计划的第6篇文章。

本来是想把枯燥的技术用有趣的方式表达出来，想了一下还是算了，今天比较丧，我才不会告诉你是因为我写了个bug，导致客户损失了1k元，正在闭门思过中。

所以，这是一篇严肃的技术文。

本文列举了本猿工作中经常用到的sql查询，算是做一下记录，避免踩坑。

--查看每个表的行数SELECT   a.name, b.rowsFROM      sysobjects AS a INNER JOIN                 sysindexes AS b ON a.id = b.idWHERE   (a.type = 'u') AND (b.indid IN (0, 1))ORDER BY b.rows DESC

说明：复制表(只复制结构,源表名：a 新表名：b) SQL: select * into b from a where 1<>1说明：拷贝表(拷贝数据,源表名：a 目标表名：b)SQL: insert into b(a, b, c) select d,e,f from b;

SELECT  ROW_NUMBER() OVER (ORDER BY CustomerID DESC)AS rn , CustomerID    INTO  # FROM  dbo.tb_Bonus_Cal    DECLARE @first INT ,@end INT  --循环次数    SET @first = 1    SET @end = (SELECT MAX(rn) FROM #)    PRINT @first    PRINT @end    --开始循环    WHILE @first <= @end    BEGIN        DECLARE @CustomerID BIGINT      --这个人的id        SET    @CustomerID =(SELECT CustomerID FROM #  WHERE rn = @first  )        PRINT 'aa'        SELECT * FROM @T        PRINT @CustomerI        SET @first = @first + 1    END

--循环处理表，很有用DECLARE @TableNameSuffix NVARCHAR(50)

SET @TableNameSuffix = 1WHILE @TableNameSuffix <= 50BEGIN  DECLARE @SqlStr NVARCHAR(MAX)  SET @SqlStr = '    ALTER TABLE dbo.tb_Wallet'+@TableNameSuffix+' ADD FrozenBalance DECIMAL(19,2) DEFAULT 0 '  EXEC (@SqlStr)  SET @SqlStr = '    UPDATE dbo.tb_Wallet'+@TableNameSuffix+' SET FrozenBalance = 0 '  EXEC (@SqlStr)SET @TableNameSuffix = @TableNameSuffix+1END

-- 删除日志USE [master]GOALTER DATABASE JRV SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE JRV SET RECOVERY SIMPLE --简单模式GOUSE JRVGODBCC SHRINKFILE (N'dsis20081_Log' , 11, TRUNCATEONLY)GOUSE [master]GOALTER DATABASE JRV SET RECOVERY FULL WITH NO_WAITGOALTER DATABASE JRV SET RECOVERY FULL --还原为完全模式GO

使用下面语句查看执行的查询计划SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql LIKE '%Users%'  and sql not like '%syscacheobjects%'

说明：复制表(只复制结构,源表名：a 新表名：b) SQL: select * into b from a where 1<>1说明：拷贝表(拷贝数据,源表名：a 目标表名：b)SQL: insert into b(a, b, c) select d,e,f from b;

检查数据库表死锁：SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName FROM sys.dm_tran_locks WHERE resource_type='OBJECT 'kill 查出来的ID，如kill 54

-- 生成数据归零语句( 累计型字段和decimal型字段 )DECLARE @rzsql nvarchar(4000)  -- rz = return to zero( 归零 )SELECT @rzsql = N'UPDATE tb_Bonus_Cal SET 'SELECT @rzsql = @rzsql + col. name +' = 0,'+ char(9)FROM sysobjects obj, syscolumns col, systypes stypeWHERE col. id = obj. idAND obj. xtype = 'U' AND col. xtype = stype. xtype AND stype. xtype = stype. xusertype AND obj. name = 'tb_Bonus_Cal'AND ( CHARINDEX( 'accu', col. name ) > 0 OR stype. name = 'decimal' ) -- 累计型和decimal型字段(注意：不能把int型也清零。因为lft，rgt是int型)SELECT @rzsql = substring( @rzsql, 1, len( @rzsql ) - 2 )  -- 截掉结尾的', '-- 执行数据归零语句EXECUTE(@rzsql)