17
2012-12

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 < @OriginalSize / 16) AND (@Counter < 50000))
  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<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
  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


除非注明,文章均为史亚永原创,欢迎转载!转载请注明本文地址,谢谢。

本文地址:http://www.shiyayong.cn/post/53.html

评论列表:

1  茶馆老板  2012-12-17 9:09:22 回复该留言  IP:124.205.133.34
也许这么长的文字,看起来真的很费力很枯燥。但是我还是希望对数据库感兴趣的网络爱好者能够仔细认真地看完并操作一下。
茶馆老板

发表评论:

(设置个性头像)

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

无觅相关文章插件,快速提升流量