郑州电脑维修 Win7粉丝 网站地图
您的位置:IT服务网网页制作数据库浏览正文

SQL语句

2011-04-23 12:24:45 浏览次数:0 评论 0

SQL语句这篇文章的内容如下: 基础 光盘 B 2   光盘 A 2   手机 B 3   手机 C 3 初始化表table1   TRUNCATE TABLE table1 选择从10到15的记录   select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 数据类型转换   declare @numid int   declare @id varchar(50)   set @numid=2005   set @id=convert(varchar,@numid)   通过上述语句完成数据类型Int转换成varchar,其他转换类似,可参看convert函数   在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 and '+ @strWhere 收缩数据库   --重建索引   DBCC REINDEX   DBCC INDEXDEFRAG   --收缩数据和日志   DBCC SHRINKDB   DBCC SHRINKFILE 压缩数据库   dbcc shrinkdatabase(dbname)   转移数据库给新用户以已存在用户权限   exec sp_change_users_login 'update_one','newname','oldname'   go 检查备份集   RESTORE VERIFYONLY from disk='E:\dvbbs.bak' 修复数据库   Alter DATABASE [dvbbs] SET SINGLE_USER   GO   DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK   GO   Alter DATABASE [dvbbs] SET MULTI_USER   GO 日志清除   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 更改某个表   exec sp_changeobjectowner 'tablename','dbo' 存储更改全部表   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 SQL SERVER中直接循环写入数据   declare @i int   set @i=1   while @i<30   begin   insert into test (userid) values(@i)   set @i=@i+1   end



本文地址:http://www.itfw5.com/webdesign/database/7243.html
  • IT服务网为郑州市区提供郑州电脑上门维修服务;
  • 来源:新浪博客 作者:IT服务网
    [错误报告][收藏][打印][关闭][返回顶部]
    请自觉遵守相关政策法规,严禁发布色情/暴力/反动言论。

    相关广告

    数据库推荐文章