三、技巧
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

  

INSERT INTO party_branch
SELECT
UUID(),m.name,m.secreta_name,m.contacts_name,m.contact_phon,m.category_name,m.type,’admin’,’admin’,
NOW(),NOW() FROM mypary_branch m

我们可以直接写成
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]
where 1=1 ‘+ @strWhere

删除list集合 

 

2、收缩数据库
–重建索引 DBCC REINDEX DBCC INDEXDEFRAG
–收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE

 **Mapper.xml

UPDATE party_branch SET id =REPLACE(id,’-‘,”)

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

  <delete id="deleteBackstageRecommend" parameterType="java.util.List">
        delete from backPersonConfig
        where (agentCode,guaranteeType) in
        <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
            (#{item.agentCode},#{item.guaranteeType})
        </foreach>
   </delete>

 

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

**Mapper

必须分开执行 如果

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

 /**
     * 删除推荐产品
     * @param backPersonConfig
     */
    public void deleteBackstageRecommend(List<BackPersonConfig> backPersonConfig);

 

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

  

INSERT INTO party_branch
SELECT
 REPLACE(UUID(),’-‘,”),m.name,m.secreta_name,m.contacts_name,m.contact_phon,m.category_name,m.type,’admin’,’admin’,
NOW(),NOW() FROM mypary_branch m

发表评论

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