insert some data for learning SQL

 

 

CREATE TABLE [IndexTest]  
(  
    [gid] [int] IDENTITY (1, 1) NOT NULL ,  
    [fariqi] [smalldatetime] NOT NULL ,  
    [neibuyonghu] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,  
    [title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,  
    [timestamp] [datetime] NOT NULL CONSTRAINT [DF_IndexTest_timestamp] DEFAULT (getdate())  
) ON [PRIMARY]

 

 

INSERT 10 DAYS DATA

set nocount on
declare @i int  
declare @f float  
declare @yh nvarchar(50)  
declare @title nvarchar(50)  
declare @d datetime
declare @endd datetime

set @d=getdate() 
set @endd = getdate() +10  

while @d<@endd  
begin  
    set @i=0  
    while @i<100000  
    begin  
        set @f=RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) 
        set @yh='zhangx'  
        if @f<0.75  
            set @yh='yangm'  
        if @f<0.5  
            set @yh='yangzk'  
        if @f<0.25  
            set @yh='lidg'  
        set @title=cast(RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) as nvarchar)  
        insert into IndexTest(fariqi,neibuyonghu,title) values(@d,@yh,@title)  
        set @i=@i+1
    end  
    set @d=dateadd(day,1,@d)  
end  
print 'over'

 

 

QUERY

 

declare @d datetime
set @d=getdate() 
select gid,fariqi,neibuyonghu,title from IndexTest where fariqi> dateadd(day,-30,'2001-01-18') 
print datediff(ms,@d,getdate())