{"id":69,"date":"2022-07-12T11:31:15","date_gmt":"2022-07-12T03:31:15","guid":{"rendered":"https:\/\/xinchewhd.com.cn\/?p=69"},"modified":"2022-11-03T15:13:52","modified_gmt":"2022-11-03T07:13:52","slug":"mysql%e6%8a%80%e5%b7%a7","status":"publish","type":"post","link":"https:\/\/xinchewhd.com.cn\/index.php\/mysql\/mysql%e6%8a%80%e5%b7%a7\/","title":{"rendered":"MySQL\u6280\u5de7"},"content":{"rendered":"\n

1\u30011=1\uff0c1=2\u7684\u4f7f\u7528\uff0c\u5728SQL\u8bed\u53e5\u7ec4\u5408\u65f6\u7528\u7684\u8f83\u591a<\/strong>\u201cwhere 1=1\u201d \u662f\u8868\u793a\u9009\u62e9\u5168\u90e8    \u201cwhere 1=2\u201d\u5168\u90e8\u4e0d\u9009\uff0c<\/strong>
\u5982\uff1a<\/p>\n\n\n\n

if\u00a0@strWhere !=''\u00a0beginset @strSQL =\u00a0'select count() as Total from ['\u00a0+ @tblName +\u00a0'] where '\u00a0+ @strWhereendelse\u00a0beginset @strSQL =\u00a0'select count(<\/em>) as Total from ['\u00a0+ @tblName +\u00a0']'\u00a0end<\/p>\n\n\n\n

\u6211\u4eec\u53ef\u4ee5\u76f4\u63a5\u5199\u6210<\/strong><\/p>\n\n\n\n

set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 \u5b89\u5b9a '+ @strWhere <\/p>\n\n\n\n

2\u3001\u6536\u7f29\u6570\u636e\u5e93<\/strong><\/p>\n\n\n\n

--\u91cd\u5efa\u7d22\u5f15
dbcc reindex
dbcc indexdefrag<\/p>\n\n\n\n


--\u6536\u7f29\u6570\u636e\u548c\u65e5\u5fd7
dbcc shrinkdb
dbcc shrinkfile<\/p>\n\n\n\n

3\u3001\u538b\u7f29\u6570\u636e\u5e93<\/strong><\/p>\n\n\n\n

dbcc shrinkdatabase(dbname)<\/p>\n\n\n\n

4\u3001\u8f6c\u79fb\u6570\u636e\u5e93\u7ed9\u65b0\u7528\u6237\u4ee5\u5df2\u5b58\u5728\u7528\u6237\u6743\u9650<\/strong><\/p>\n\n\n\n

exec sp_change_users_login 'update_one','newname','oldname'
go<\/p>\n\n\n\n

5\u3001\u68c0\u67e5\u5907\u4efd\u96c6
<\/strong>restore verifyonly from disk='E:\\dvbbs.bak'<\/p>\n\n\n\n

6\u3001\u4fee\u590d\u6570\u636e\u5e93<\/strong><\/p>\n\n\n\n

alter database [dvbbs] set single_user
go
dbcc checkdb('dvbbs',repair_allow_data_loss) with tablock
go
alter database [dvbbs] set multi_user
go<\/p>\n\n\n\n

7\u3001\u65e5\u5fd7\u6e05\u9664<\/strong><\/p>\n\n\n\n

set NOCOUNT ON
declare @LogicalFileName sysname,
@MaxMinutes int,
@NewSize int<\/p>\n\n\n\n

use tablename --\u00a0\u8981\u64cd\u4f5c\u7684\u6570\u636e\u5e93\u540d
select\u00a0\u00a0@LogicalFileName = 'tablename_log', --\u00a0\u65e5\u5fd7\u6587\u4ef6\u540d
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1\u00a0\u00a0--\u00a0\u4f60\u60f3\u8bbe\u5b9a\u7684\u65e5\u5fd7\u6587\u4ef6\u7684\u5927\u5c0f(M)<\/p>\n\n\n\n

Setup \/ initialize
declare @OriginalSize int
select @OriginalSize = size\u00a0
from sysfiles
where name = @LogicalFileName
select 'Original Size of ' + db_name() + ' LOG is ' +\u00a0
convert(varchar(30),@OriginalSize) + ' 8K pages or ' +\u00a0
convert ( varchar (30),(@OriginalSize*8\/1024)) + 'MB'
from sysfiles
where name = @LogicalFileName
create table DummyTrans
(DummyColumn char (8000) not null)<\/p>\n\n\n\n

declare @Counter\u00a0\u00a0int,
@StartTime datetime,
@TruncLog\u00a0\u00a0 varchar(255)
select @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'<\/p>\n\n\n\n

dbcc shrinkfile (@LogicalFileName, @NewSize)
exec (@TruncLog)
-- Wrap the log if necessary.
while @MaxMinutes > datediff(mi, @StartTime, GETDATE()) -- time has not expired
and\u00a0 @OriginalSize = (select size from sysfiles where name = @LogicalFileName)\u00a0\u00a0
and (@OriginalSize * 8 \/1024) > @NewSize\u00a0\u00a0
begin -- Outer loop.
select @Counter = 0
while\u00a0\u00a0 ((@Counter < @OriginalSize \/ 16) and (@Counter < 50000))
begin -- update
insert DummyTrans VALUES ('Fill Log') DELETE DummyTrans
select @Counter = @Counter + 1
end
exec(@TruncLog)\u00a0\u00a0
end
select 'Final Size of ' + db_name() + ' LOG is ' +
\u00a0convert ( varchar (30),size) + ' 8K pages or ' +\u00a0
convert(varchar(30),(size*8\/1024)) + 'MB'
from sysfiles\u00a0
where name = @LogicalFileName
drop table DummyTrans
set nocount off<\/p>\n\n\n\n

8\u3001\u8bf4\u660e\uff1a\u66f4\u6539\u67d0\u4e2a\u8868<\/strong><\/p>\n\n\n\n

exec sp_changeobjectowner 'tablename','dbo'<\/p>\n\n\n\n

9\u3001\u5b58\u50a8\u66f4\u6539\u5168\u90e8\u8868<\/strong><\/p>\n\n\n\n

create procedure dbo.User_ChangeObjectOwnerBatch
@OldOwner as nvarchar(128),
@NewOwner as nvarchar(128)
AS<\/p>\n\n\n\n

declare @Name\u00a0\u00a0\u00a0 as nvarchar (128)
declare @Owner\u00a0\u00a0 as nvarchar (128)
declare @OwnerName\u00a0\u00a0 as nvarchar (128)<\/p>\n\n\n\n

declare curObject cursor for\u00a0
select 'Name'\u00a0\u00a0\u00a0 = name,
\u00a0\u00a0 'Owner'\u00a0\u00a0\u00a0 = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name<\/p>\n\n\n\n

open\u00a0\u00a0 curObject
fetch next form curObject into @Name, @Owner
while(@@FETCH_STATUS=0)
begin\u00a0\u00a0\u00a0\u00a0\u00a0
if @Owner=@OldOwner\u00a0
begin
\u00a0\u00a0 set @OwnerName = @OldOwner + '.' + rtrim(@Name)
\u00a0\u00a0 exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner<\/p>\n\n\n\n

fetch next from curObject INTO @Name, @Owner
end<\/p>\n\n\n\n

close curObject
deallocate curObject
go<\/p>\n\n\n\n

10\u3001SQL SERVER\u4e2d\u76f4\u63a5\u5faa\u73af\u5199\u5165\u6570\u636e<\/strong><\/p>\n\n\n\n

declare @i int
set @i=1
while @i<30
begin
    insert into test (userid) values(@i)
    set @i=@i+1
end
<\/p>\n\n\n\n

\u6848\u4f8b<\/strong>\uff1a<\/strong>\u6709\u5982\u4e0b\u8868\uff0c\u8981\u6c42\u5c31\u88f1\u4e2d\u6240\u6709\u6c92\u6709\u53ca\u683c\u7684\u6210\u7e3e\uff0c\u5728\u6bcf\u6b21\u589e\u95770.1\u7684\u57fa\u790e\u4e0a\uff0c\u4f7f\u4ed6\u5011\u525b\u597d\u53ca\u683c:<\/p>\n\n\n\n

Name\u00a0 \u00a0scorZhangshan\u00a080Lishi\u00a059Wangwu\u00a050Songquan\u00a069<\/p>\n\n\n\n

while((select min(score) from tb_table)<60)\nbegin\nupdate tb_table set score =score*1.01\nwhere score<60\nif (select min(score) from tb_table)>60
\n break
\n else
\n continue
\nend<\/p>\n","raw":"","protected":false},"excerpt":{"rendered":"

1\u30011=1\uff0c1=2\u7684\u4f7f\u7528\uff0c\u5728SQL\u8bed\u53e5\u7ec4\u5408\u65f6\u7528\u7684\u8f83\u591a\u201cwhere 1=1\u201d \u662f\u8868\u793a\u9009\u62e9\u5168\u90e8  &nb …<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"emotion":"","emotion_color":"","title_style":"","license":"","footnotes":""},"categories":[3],"tags":[],"class_list":["post-69","post","type-post","status-publish","format-standard","hentry","category-mysql"],"post_thumbnail_image":"","content_first_image":null,"post_medium_image_300":"","post_thumbnail_image_624":"","post_frist_image":null,"post_medium_image":"","post_large_image":"","post_full_image":"","post_all_images":[],"videoAdId":"","listAd":"0","listAdId":"","listAdEvery":6,"total_comments":0,"category_name":"MySQL","post_date":"2022-07-12","like_count":"0","praiseWord":"\u9f13\u52b1","copyright_state":"","excitationAd":"0","rewardedVideoAdId":"","detailAdId":"","detailAd":"0","enterpriseMinapp":"0","audios":[],"postImageUrl":"https:\/\/wp-moto-1258805347.cos.ap-shanghai.myqcloud.com\/2023\/05\/20230519082947553.jpg","avatarurls":[],"related_posts":null,"pageviews":292,"next_post_id":551,"next_post_title":"\u4e13\u5751\u540c\u4e8b\u7684SQL \u5c31\u8fd9\u4e48\u5199\uff0c\u6027\u80fd\u964d\u4f4e100\u500d\uff0c\u4e0d\u6765\u770b\u770b\uff1f","previous_post_id":61,"previous_post_title":"MySQL\u8fdb\u9636","_links":{"self":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/posts\/69","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/comments?post=69"}],"version-history":[{"count":0,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/posts\/69\/revisions"}],"wp:attachment":[{"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/media?parent=69"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/categories?post=69"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xinchewhd.com.cn\/index.php\/wp-json\/wp\/v2\/tags?post=69"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}