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🀎 Total from [' + @tblName + ']'
end
我們可以直接寫成
錯誤！未找到目錄項。
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定'+ @strWhere 2、收縮資料庫
--重建索引
DBCC REINDINDEX〜『資料與日誌
DBCC SHRINKDB
DBCC SHRINKFILE
3、壓縮資料庫
dbcc shrinkdatabase(dbname)
up_confate,4、轉移用戶給新用戶以已存在於不存在_fate's_fates_fate'conlog; newname','oldname'
go
5.檢查備份集
RESTORE VERIFYONLY from disk='E:dvbbs.bak'
6、修復資料庫
ALTER DATABASE [dvallows] SET SINGLE_USER] data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日誌清除
SET NOCOUNT ON
DECLARE @LogicalFileName E tablename -- 要操作的資料庫名稱
SELECT @LogicalFileName = 'tablename_log', -- 日誌檔案名稱
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你要設定的日誌檔案的大小(M) 鐵》無素》 。 or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles WHERE name = @LogicalFileName
CREATE TABLE DummyTransnotero inot 7 n 70747F, 45%)a] er INT,
@ StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() +' NewSize)
EXEC (@ TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
Name 〨〦S 是接。 IN -- update
INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
》_EC (@TruncLogEXEC (@uncunc. () + ' 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、儲存變更全部表
7 CREATE PROCEDURE done.User_Change37( ner CRE〔27.
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128) CHAR 一物OR 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🀜 〦〜) @Owner=@OldOwner
begin
set @ OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@ Owft 〦＋ END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循環寫入資料
declare @i int🀜 〜 〜〜〟 insert into test (userid) values(@i) set @i=@i+1
end
案例：
有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好好：
Name score
〠 Wangwu 50
Songquan 69
while ((select min(score) from tb_table) begin
update tb_table set score =score*1.01
where score else
continue
end
以上就是MYSQL經典語句大全－技巧篇的內容，更多相關內容請關注PHP中文網（www.php.cn）！
