MYSQL經典語句大全——技巧篇

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)!?

? 版權聲明
THE END
喜歡就支持一下吧
點贊7 分享