SQL 代码片段

最新推荐文章于 2023-11-01 22:16:22 发布

微雨燕双飞

最新推荐文章于 2023-11-01 22:16:22 发布

阅读量1k

点赞数

分类专栏： SQL数据库

本文链接：https://blog.csdn.net/cqkxzyi/article/details/9165303

版权

SQL数据库专栏收录该内容

25 篇文章 3 订阅

订阅专栏

●：重置表

--- 删除所有表数据,并重置自增列

exec sp_msforeachtable @Command1 ='truncate table ?'

--- 删除原表数据,并重置自增列
truncate table tablename

--重置表的自增字段,保留数据
     DBCC CHECKIDENT (tablename,reseed,0)
-- 设置允许显式插入自增列
      SET IDENTITY_INSERT tablename ON
-- 当然插入完毕记得要设置不允许显式插入自增列
     SET IDENTITY_INSERT tablename Off

●：删除、开启数据库连接
ALTER DATABASE [库名] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER database [库名] set online

●：查看sql语句执行步奏

set statistics profile on的用法

●：SQL语句

select (SELECT COUNT(*) AS 行数 FROM T_Exchange whereT_Exchange.PrizeID = T_Prize.ID) as行数,T_Prize.* from T_Prize

●With as 的用法：

with 
cr as 
( 
    select CountryRegionCode from person.CountryRegion where Name like 'C%' 
)

●批量更新

Update t1set t1.c2 = t2.c2 from t2 where t1.c1 = t2.c1

或者：Update t1set t1.c2 = t2.c2 from t1inner join t2on t1.c1 = t2.c1

●清理日志文件

USE DB_tshEMBA;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DB_tshEMBA
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DB_tshEMBA_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DB_tshEMBA
SET RECOVERY FULL;
GO

●For XML PATH：将查询结果集以XML形式展现

SELECT hobbyID as 'MyCode',hName as 'MyName' FROM T_User FOR XML PATH('UserModel')

SELECT '[ '+UserName+' ]' FROM T_User FOR XML PATH('')

SELECT UserName+',' FROM T_User FOR XML PATH('')

●CROSS APPLY：(CROSS JOIN 的变种)

APPLY有两种，一个OUTER APPLY，一个CROSS APPLY，区别在于指定OUTER，意味着结果集中将包含使右表表达式为空的左表表达式中的行，而指定CROSS，结果集中不包含使右表表达式为空的左表表达式中的行。常常配合函数来使用

●获取标识列的种子值
可使用函数IDENT_SEED,用法：
SELECT IDENT_SEED ('表名')

●获取标识列的递增量
可使用函数IDENT_INCR ,用法：
SELECT IDENT_INCR('表名')

●关闭Identity自增列插入数据

set identity_insert B_Province ON--打开
insert into B_Province(id,Code,Name) select id,code,name from [NTXV5.1].[dbo].[B_Province]
set identity_insert B_Province OFF--关闭

●存储过程参数返回方法

SqlParameter[] sqlParame = new SqlParameter[]
                {
                    new SqlParameter("@iMsgID", SqlDbType.BigInt),
                    new SqlParameter("@bMmsFlag", SqlDbType.Int),
                    new SqlParameter("@return", SqlDbType.Int)
                };
            sqlParame[0].Value = msg.MsgID;
            sqlParame[1].Direction = ParameterDirection.Output;
            sqlParame[2].Direction = ParameterDirection.ReturnValue;

int iResult = WebDbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "<span style="font-family: Arial, Helvetica, sans-serif;">spGenerateMmsToSend</span>", sqlParame);
int iRet = Int32.Parse(sqlParame[1].Value.ToString());