经典SQL语句大全（三）_MySQL

bitsCN.com

三、技巧

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

我们可以直接写成

错误！未找到目录项。
set @strsql = 'select count(*) as total from [' + @tblname + '] where 1=1 安定 '+ @strwhere 2、收缩数据库
--重建索引
dbcc reindex
dbcc indexdefrag
--收缩数据和日志
dbcc shrinkdb
dbcc shrinkfile

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

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

5、检查备份集
restore verifyonly from disk='e:////dvbbs.bak'

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

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)

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)


declare @counter int,
@starttime datetime,
@trunclog varchar(255)
select @starttime = getdate(),
@trunclog = 'backup log ' + db_name() + ' with truncate_only'

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 = @logicalfilename)
and (@originalsize * 8 /1024) > @newsize
begin -- outer loop.
select @counter = 0
while ((@counter begin -- update
insert dummytrans values ('fill log') delete dummytrans
select @counter = @counter + 1
end
exec (@trunclog)
end
select '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

8、说明：更改某个表
exec sp_changeobjectowner 'tablename','dbo'

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)

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

fetch next from curobject into @name, @owner
end

close curobject
deallocate curobject
go


10、sql server中直接循环写入数据
declare @i int
set @i=1
while @ibegin
insert into test (userid) values(@i)
set @i=@i+1
end
案例：
有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:

name score

zhangshan 80

lishi 59

wangwu 50

songquan 69

while((select min(score) from tb_table)
begin

update tb_table set score =score*1.01

where score
if (select min(score) from tb_table)>60

break

else

continue

end

更多内容来自-伊来网Eliyahr

迅易年度企业管理系统开源完整版

系统功能强大、操作便捷并具有高度延续开发的内容与知识管理系统，并可集合系统强大的新闻、产品、下载、人才、留言、搜索引擎优化、等功能模块，为企业部门提供一个简单、易用、开放、可扩展的企业信息门户平台或电子商务运行平台。开发人员为脆弱页面专门设计了防刷新系统，自动阻止恶意访问和攻击；安全检查应用于每一处代码中，每个提交到系统查询语句中的变量都经过过滤，可自动屏蔽恶意攻击代码，从而全面防止SQL注入攻击

0

查看详情