积存进度,Sql的基础知识技巧

转自

转自

.游标格局
 DECLARE @Data NVARCHAR(max)
 SET @Data=’1,tanw;2,keenboy’   –Id,Name
 
 DECLARE @dataItem NVARCHAR(100)
 DECLARE data_cursor CURSOR FOR (SELECT * FROM split(@Data,’;’))
 OPEN data_cursor
 FETCH NEXT FROM data_cursor INTO @dataItem   
 WHILE @@FETCH_STATUS=0
 BEGIN
 DECLARE @Id INT
 DECLARE @Name NVARCHAR(50)
 
 DECLARE dataItem_cursor CURSOR FOR (SELECT * FROM
split(@dataItem,’,’))
 OPEN dataItem_cursor   
 FETCH NEXT FROM dataItem_cursor INTO @Id
 FETCH NEXT FROM dataItem_cursor INTO @Name
 CLOSE dataItem_cursor
 DEALLOCATE dataItem_cursor
 
 /*
   在此间做逻辑处理,插入或更新操作 …
 */
 END
 
 CLOSE data_cursor
 DEALLOCATE data_cursor

三、技巧
1、1=1,1=2 的运用,在 SQL 语句组合时用的较多
“where 1=1” 是意味着选取一切
“where 1=2”全体不选,
如:
if @strWhere !=”
begin set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]
where ‘ + @strWhere
end
else
begin
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]’
end

1.游标格局

1.游标格局

 

俺们可以直接写成
积存进度,Sql的基础知识技巧。set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]
where 1=1 ‘+ @strWhere

  

  

 

2、减少数据库
–重建索引 DBCC REINDEX DBCC INDEXDEFRAG
–减弱数据和日志 DBCC SHRINKDB DBCC SHRINKFILE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Author,,Name>
— Create date: <Create Date,,>
— Description: <Description,,>
— =============================================
create PROCEDURE InsertCheckItemCategory
— Add the parameters for the stored procedure here
@levelCode INT
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Author,,Name>
— Create date: <Create Date,,>
— Description: <Description,,>
— =============================================
create PROCEDURE InsertCheckItemCategory
— Add the parameters for the stored procedure here
@levelCode INT
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

.While方式
 DECLARE @Data NVARCHAR(max)
 SET @Data=’tanw,keenboy’   –Id,Name
 
 DECLARE @Temp TABLE
 (
    Id INT IDENTITY(1,1),
    Name  NVARCHAR(50)
 )
 DECLARE @Id INT
 DECLARE @Name NVARCHAR(50)
 DECLARE @Results NVARCHAR(MAX) SET @Results=”
 INSERT INTO @Temp SELECT (SELECT * FROM split(@Data,’;’))
 
 WHILE EXISTS(SELECT * FROM @Temp)
 BEGIN
     SELECT TOP 1 @Id=Id,@Name=Name from @Temp
     DELETE FROM @Temp where [id] = @Id
     SET @Results=@Results+@Name+’,’
    
     /*
    
         在此间做逻辑处理,插入或更新操作 …
    
     */
 END
 SELECT @Results

3、压缩数据库 dbcc shrinkdatabase(dbname)

DECLARE @newCategoryId UNIQUEIDENTIFIER
DECLARE @oldCategoryId NVARCHAR(50)
DECLARE @childLevelCode INT
SET @childLevelCode=@levelCode+1

DECLARE @newCategoryId UNIQUEIDENTIFIER
DECLARE @oldCategoryId NVARCHAR(50)
DECLARE @childLevelCode INT
SET @childLevelCode=@levelCode+1

 

4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login ‘update_one’,’newname’,’oldname’ go

DECLARE data_cursor CURSOR
FOR
( SELECT CheckItemCategory.Id AS newCategoryId ,
AppCheckItem.Id AS oldCategoryId
FROM CheckItemCategory
LEFT JOIN AppCheckItem ON CheckItemCategory.Descr =
AppCheckItem.[Text]
WHERE LevelCode = @levelCode
) –使用游标的靶子(跟据要求填写select文)
OPEN data_cursor –打开游标
FETCH NEXT FROM data_cursor INTO @newCategoryId, @oldCategoryId
–将游标向下移1行,获取的数目放入事先定义的变量@id,@name中
WHILE @@fetch_status = 0 –判断是还是不是中标获取数据
BEGIN
–进行对应处理(跟据须求填写SQL文)
INSERT INTO dbo.CheckItemCategory
( Id ,
Descr ,
OrderNo ,
LevelCode ,
ParentId ,
CreatedTime ,
UpdatedTime ,
IsDeleted
)
SELECT NEWID() ,
AppCheckItem.[Text] AS Descr ,
ROW_NUMBER() OVER(ORDER BY Id ASC) AS RowNumber,
@childLevelCode ,
@newCategoryId ,
GETDATE() ,
GETDATE() ,
0
FROM AppCheckItem
WHERE ParentId = @oldCategoryId AND Title=1
FETCH NEXT FROM data_cursor INTO @newCategoryId,
@oldCategoryId –将游标向下移1行
END

DECLARE data_cursor CURSOR
FOR
( SELECT CheckItemCategory.Id AS newCategoryId ,
AppCheckItem.Id AS oldCategoryId
FROM CheckItemCategory
LEFT JOIN AppCheckItem ON CheckItemCategory.Descr =
AppCheckItem.[Text]
WHERE LevelCode = @levelCode
) –使用游标的目的(跟据要求填写select文)
OPEN data_cursor –打开游标
FETCH NEXT FROM data_cursor INTO @newCategoryId, @oldCategoryId
–将游标向下移1行,获取的数目放入事先定义的变量@id,@name中
WHILE @@fetch_status = 0 –判断是还是不是中标获取数据
BEGIN
–举行对应处理(跟据要求填写SQL文)
INSERT INTO dbo.CheckItemCategory
( Id ,
Descr ,
OrderNo ,
LevelCode ,
ParentId ,
CreatedTime ,
UpdatedTime ,
IsDeleted
)
SELECT NEWID() ,
AppCheckItem.[Text] AS Descr ,
ROW_NUMBER() OVER(ORDER BY Id ASC) AS RowNumber,
@childLevelCode ,
@newCategoryId ,
GETDATE() ,
GETDATE() ,
0
FROM AppCheckItem
WHERE ParentId = @oldCategoryId AND Title=1
FETCH NEXT FROM data_cursor INTO @newCategoryId,
@oldCategoryId –将游标向下移1行
END

 

5、检查备份集 RESTORE VERIFYONLY from disk=’E:\dvbbs.bak’

CLOSE data_cursor –关闭游标
DEALLOCATE data_cursor
END
GO

CLOSE data_cursor –关闭游标
DEALLOCATE data_cursor
END
GO

 

6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB(‘dvbbs’,repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO

    2.While方式

    2.While方式

 //———上边的措施相比相符———-//

7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

    复制代码代码如下:

    复制代码代码如下:

 

USE tablename — 要操作的数码库名
SELECT @LogicalFileName = ‘tablename_log’, — 日志文件名
@MaxMinutes = 10, — Limit on time allowed to wrap log.
@NewSize = 1 — 你想设定的日志文件的深浅(M)

    DECLARE @Data NVARCHAR(max)

    DECLARE @Data NVARCHAR(max)

 

Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT ‘Original Size of ‘ + db_name() + ‘ LOG is ‘ +
CONVERT(VARCHAR(30),@OriginalSize) + ‘ 8K pages or ‘ +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ‘MB’
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

    SET @Data=’tanw,keenboy’ –Id,Name

    SET @Data=’tanw,keenboy’ –Id,Name

 

DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = ‘BACKUP LOG ‘ + db_name() + ‘ WITH TRUNCATE_ONLY’

    DECLARE @Temp TABLE

    DECLARE @Temp TABLE

BCP方式:

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
— Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) — time
has
not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @Logical
FileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN — Outer loop
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN — update
INSERT DummyTrans VALUES (‘Fill Log’) DELETE DummyTrans

    (

    (

/// <summary>

SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
ELECT ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +
CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +
CONVERT(VARCHAR(30),(size*8/1024)) + ‘MB’
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

    Id INT IDENTITY(1,1),

    Id INT IDENTITY(1,1),

/// 大批量插入数据(2000每批次)

8、表明:更改某个表
exec sp_changeobjectowner ‘tablename’,’dbo’

    Name NVARCHAR(50)

    Name NVARCHAR(50)

/// 已采用整体事物控制

9、存储更改所有表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name
as NVARCHAR(128)
DECLARE @Owner
as NVARCHAR(128)
DECLARE @OwnerName
as NVARCHAR(128)

    )

    )

/// </summary>

DECLARE curObject CURSOR FOR
select ‘Name’= name, ‘Owner’ = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0) BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + ‘.’ + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
— select @name,@NewOwner,@OldOwner

    DECLARE @Id INT

    DECLARE @Id INT

/// <param name="connString">数据库链接字符串</param>

FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO

    DECLARE @Name NVARCHAR(50)

    DECLARE @Name NVARCHAR(50)

/// <param name="tableName">数据库服务器上目标表名</param>

10、SQL SERVER 中一贯循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
案例:
犹如下表,必要就裱中保有沒有及格的成績,在历次增長 0.1
的根底上,使他们恰好及格:
Name score
Zhangshan 80
Lishi 59
Wangwu 50
Songquan 69

    DECLARE @Results NVARCHAR(MAX) SET @Results=”

    DECLARE @Results NVARCHAR(MAX) SET @Results=”

/// <param name="dt">含有和目标数据库表结构完全一致(所包含的字段名完全一致即可)的DataTable</param>

while((select min(score) from tb_table)<60)
begin
update tb_table set score =score*1.01
where score<60
if (select min(score) from tb_table)>60
break
else
continue
end

    INSERT INTO @Temp SELECT (SELECT * FROM split(@Data,’;’))

    INSERT INTO @Temp SELECT (SELECT * FROM split(@Data,’;’))

public static void BulkCopy(``string connString, ``string
tableName, DataTable dt)

    WHILE EXISTS(SELECT * FROM @Temp)

    WHILE EXISTS(SELECT * FROM @Temp)

{

    BEGIN

    BEGIN

``using (SqlConnection conn = ``new SqlConnection(connString))

    SELECT TOP 1 @Id=Id,@Name=Name from @Temp

    SELECT TOP 1 @Id=Id,@Name=Name from @Temp

``{

    DELETE FROM @Temp where [id] = @Id

    DELETE FROM @Temp where [id] = @Id

``conn.Open();

    SET @Results=@Results+@Name+’,’

    SET @Results=@Results+@Name+’,’

 

    www.jb51.net

    www.jb51.net

``using (SqlTransaction transaction = conn.BeginTransaction())

    /*

    /*

``{

    在此间做逻辑处理,插入或更新操作 …

    在那边做逻辑处理,插入或更新操作 …

``using (SqlBulkCopy bulkCopy = ``new
SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))

    */

    *997755.com澳门葡京,/

``{

    END

    END

``bulkCopy.BatchSize = 2000;

    SELECT @Results

    SELECT @Results

``bulkCopy.BulkCopyTimeout = _CommandTimeOut;

    借使是粗略单表批量插入操作的,上边方法大可不必要

    如若是简简单单单表批量布置操作的,上边方法大可不须求

``bulkCopy.DestinationTableName = tableName;

 

``try

``{

``foreach (DataColumn col ``in dt.Columns)

``{

``bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);

 

``}

``bulkCopy.WriteToServer(dt);

``transaction.Commit();

``}

``catch (Exception ex)

``{

``transaction.Rollback();

``throw ex;

``}

``finally

``{

``conn.Close();

``}

``}

``}

``}

}

 

 

 

 

 

SqlDataAdapter:

/// <summary>

/// 批量更新数据(每批次5000)

/// </summary>

/// <param name="connString">数据库链接字符串</param>

/// <param name="table"></param>

public static void Update(``string
connString, DataTable table)

{

``SqlConnection conn = ``new SqlConnection(connString);

``SqlCommand comm = conn.CreateCommand();

``comm.CommandTimeout = _CommandTimeOut;

``comm.CommandType = CommandType.Text;

``SqlDataAdapter adapter = ``new SqlDataAdapter(comm);

``SqlCommandBuilder commandBulider = ``new
SqlCommandBuilder(adapter);

``commandBulider.ConflictOption = ConflictOption.OverwriteChanges;

``try

``{

``conn.Open();

``//设置批量更新的每次处理条数

``adapter.UpdateBatchSize = 5000;

``adapter.SelectCommand.Transaction = conn.BeginTransaction();``/////////////////开始事务

``if (table.ExtendedProperties[``"SQL"``] != ``null``)

``{

``adapter.SelectCommand.CommandText = table.ExtendedProperties[``"SQL"``].ToString();

``}

``adapter.Update(table);

``adapter.SelectCommand.Transaction.Commit();``/////提交事务

``}

``catch (Exception ex)

``{

``if (adapter.SelectCommand != ``null
&& adapter.SelectCommand.Transaction != ``null``)

``{

``adapter.SelectCommand.Transaction.Rollback();

``}

``throw ex;

``}

``finally

``{

``conn.Close();

``conn.Dispose();

``}

}

 

 

 

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website