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 @i begin
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
?以上就是mysql經典語句大全——技巧篇的內容，更多相關內容請關注PHP中文網（www.php.cn）！?
