sql最近几天的笔记

test

select * from sgk where id =1;
exec sp_spaceused sgk;

--JIU:423771079  156005274

--rows
--428700186  

select top 10 * from sgk order by id desc
--mohuchaxun
select * from sgk where name1 like '%123%'
select * from sgk where email like '%123%'
--jingquechaxun
select * from sgk where name1 ='123'
--lianhechax
select * from sgk where name1 like '%123%' or email like '%123%'

select * from sgk where name1 = '123' or email = '123@qq.com'

--开启SQL SERVER的AWE,并设置最大内存为8G
sp_configure "show advanced options", 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure "awe enabled", 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure "max server memory", 8120
RECONFIGURE WITH OVERRIDE
GO
RECONFIGURE
GO



select  top 1000000 *  into testtable from  sgk


sp_attach_single_file_db @dbname=  'QunInfo1' ,
@physname= 'E:\BaiduYunDownload\QQ数据库\QQ数据库\QunData\QunInfo1_Data.MDF'

sp_attach_single_file_db @dbname=  'QQ_GroupData1' ,
@physname= 'E:\BaiduYunDownload\QQ数据库\QQ数据库\QunData\GroupData1_Data.MDF'


insert into QQ_GroupData.dbo.Group101
select * from Group1


insert  Group1
select * from(
select QQNum,Nick,Age,Gender,Auth,QunNum from Group2
union
select QQNum,Nick,Age,Gender,Auth,QunNum from Group3
union
select QQNum,Nick,Age,Gender,Auth,QunNum from Group4
)t



DECLARE @i int  
Set @i = 2
--开始循环插入数据
WHILE @i < 3 and @i>1
BEGIN   
insert  Group1
select * from(
select QQNum,Nick,Age,Gender,Auth,QunNum from Group+@i
)t
Set @i =@i +1
END   
select count(*) from Group1
--760440
truncate table group1

select * from Group100

select * from QunList2

select 'insert into group1 select * from '+DB_NAME()+'dbo.'+o.name+';' from sys.objects o where o.name like '%group%';
select 'insert into group1 select * from '+o.name+';' from sys.objects o where o.name like '%group%' and o.type='U';
select 'drop table '+o.name+';' from sys.objects o where o.name like '%group%' and o.type='U';



select * from Group1 where QQNum='123'
DBCC FREEPROCCACHE
DBCC ShrinkDatabase(QQ_GroupData1)