A complete collection of MYSQL classic statements—techniques

1, 1=1, 1=2 are often used in SQL statement combinations
　 "where 1=1" means to select all "where 1=2" means not to select all,
　For example: 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
　We can write it directly
　Error! Catalog entry not found.
　　set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 '+ @strWhere 2. Shrink the database
　　--Rebuild the index
　DBCC REINDEX
　DBCC INDEXDEFRAG
　　--Shrink Data and logs
　DBCC SHRINKDB
　DBCC SHRINKFILE
　3. Compress database
　dbcc shrinkdatabase(dbname)
　4. Transfer database to new users with existing user rights
　exec sp_change_users_login 'update_one','newname','oldname'
　go
5. Check the backup set
　RESTORE VERIFYONLY from disk='E:dvbbs.bak'
　6. Repair the database
　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. Log clearing
　SET NOCOUNT ON
　DECLARE @LogicalFileName sysname,
　@MaxMinutes INT,
　@NewSize INT
　USE tablename -- the database name to be operated
　SELECT @LogicalFileName = 'table name_log', -- Log file name
　 @MaxMinutes = 10, -- Limit on time allowed to wrap log.
　 @NewSize = 1 -- The size of the log file you want to set (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' Starttime DateTime,
@trunClog Varchar (255)
Selects @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 Dummytransy SET NOCOUNT OFF
8. Explanation: Change a table
Exec SP_CHANGEOBJECTOWNER 'TABLENAME', 'Dbo'
9. Storage and change all tables
ERBATCHo @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. Write data directly in a loop in SQL SERVER
　declare @i int
　set @i=1
　while @i<30
　begin
　insert into test (userid) values( @i) set @i=@i+1
end
　Case:
　There is a table below, which requires that all the unfailing grades be corrected, and on the basis of an increase of 0.1 each time, they will just pass: 9
　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

The above is the content of the MYSQL Classic Statement - Techniques. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!