create table Test(

    name varchar(4),

    age int,

    sex varchar(2)

)

alter table Test

add id char(16)

alter table Test

alter column id varchar(16)

alter table Test

drop column id

drop table Test

select * from Test

create table Test(

    name varchar(4),

    age int,

    id char(16)

)

alter table Test

 alter column name varchar(6)

insert into Test

values('崔哥哥',15,'');--注意单引号

delete from Test

where name='崔哥哥'

update Test set age=18 where name='崔哥哥'

bulk insert Test

from 'C:\Users\Administrator\Desktop\test.txt'

with(

    fieldterminator=' ',rowterminator='\n'

)

select name from Test where LEN(id)=7 order by age

--desc asc

select * from Test

--declare @a int, @b int

--print rand()*26+65

--select @a=cast('123' as int), @b=convert(int,'456');

--print @b

--print ascii('A')

--print char(rand()*26+65)

--print substring('李四',1,1);

--select * from Test

--where left(name,1)='崔' or right(name,1)='三'or substring(name,2,1)='四'

print getdate()

print dateadd(day,2,getdate())

print dateadd(minute,2,getdate())

print dateadd(second,2,getdate())

print dateadd(hour,2,getdate())

print datediff(second,getdate(),dateadd(hour,2,getdate()))

print datepart(week,getdate())-datepart(week,dateadd(month,-1,getdate()))

print str(1.25,3,1)+'asd'

--print str(参数1,参数2,参数3)

--参数2表示转换后的数据的总位数，包括小数点，正负号

--参数3表示转换后的数据的小数位数

bulk insert Test

from 'C:\Users\Administrator\Desktop\test.txt'

with(

    fieldterminator=' ',rowterminator='\n'

)

--declare mycursor scroll cursor for select * from Test

open mycursor

declare @name varchar(6),@age int,@id char(16)

fetch first from mycursor into @name,@age,@id

print @name

print @age

print @id

while @@FETCH_STATUS=0

begin

    fetch next from mycursor into @name,@age,@id

    --absolute 10

    print @name

    print @age

    print @id

end

close mycursor

deallocate mycursor

alter table Test

drop column name

select * from Test

delete from Test

select * from Test

alter table Test

add name varchar(6) primary key

alter table Test

alter column id varchar(16) not null

alter table Test

drop column age

alter table Test

add age int check( age>=0 and age<=100)

create view myview as select * from Test

select * from myview

select * from myview where age=18

create clustered index myindex on Test(age)

alter index myindex on Test rebuild

drop index Test.myindex

--io10

declare @id varchar(), @i int, @j int, @k int, @temp varchar(), @tmp varchar();

set @temp = '豫京津沪';

set @tmp = 'HABCDEFGHJKLMN23456789';

set @i = ;

while(@i<)

begin

    set @id = '';

    select @k = ;

    set @j = rand()*+;

    set @id = @id + substring(@temp, @j, );

    set @j =rand()*+;

    set @id = @id + substring(@tmp, @j, );

    print @id;

    while(@k < )

    begin

        set @j =rand()*+;

        set @id = @id + substring(@tmp, @j, );

        set @k = @k + ;

    end

    set @i = @i + ;

    insert into viechle values(@id,,,);

end

--select * from viechle

--use handsomecui

--drop table mylog

--create table mylog(

--    tablename varchar(10),

--    altername varchar(10),

--    altertimr date

--)

insert into viechle values('',0,0,0);

update viechle set number_id='' where number_id=''

select * from mylog

--drop trigger mytrigg

create trigger mytrigger

on viechle after insert,update, delete

as

begin

if exists(select 1 from inserted) and exists(select 1 from deleted)

insert into mylog values('viechle','update',getdate());

if exists(select 1 from inserted) and not exists(select 1 from deleted)

insert into mylog values('viechle','insert',getdate());

if not exists(select 1 from inserted) and exists(select 1 from deleted)

insert into mylog values('viechle','delete',getdate());

end

--姓名>3 age 20-30

--drop table Worker

--create table Worker(

--    stname varchar(10),

--    stbirth date,

--    styear int,

--    stdate varchar(4),

--    cnt int

--)

declare @bir date, @yy int, @mm int, @dd int, @name varchar(10), @i int, @j int, @stdate varchar(4);

select @i = 0, @j = 0;

while(@i < 365)

begin

    select @name = '', @j = 0, @stdate = '';

    while(@j < 5)

    begin

        set @name = @name + char(rand()*26 + 65);

        set @j = @j + 1;

    end

    set @bir = dateadd(day, -20*365, dateadd(day, -10*365*rand(), getdate()));

    set @yy = datepart(year, @bir);

    set @mm = datepart(month, @bir);

    set @dd = datepart(day, @bir);

    if(@mm < 10)

    begin

        set @stdate = @stdate + '';

        set @stdate = @stdate + char(@mm + 48);

    end

    else

    begin

        set @stdate = @stdate + char(@mm/10 + 48);

        set @stdate = @stdate + char(@mm%10 + 48);

    end

    if(@dd < 10)

    begin

        set @stdate = @stdate + '';

        set @stdate = @stdate + char(@dd + 48);

    end

    else

    begin

        set @stdate = @stdate + char(@dd/10 + 48);

        set @stdate = @stdate + char(@dd%10 + 48);

    end

    insert into Worker values(@name, @bir, @yy, @stdate, 1);

    set @i = @i + 1;

end

--select * from Worker

--select stname from Worker where left(stname, 1)='A'

--create clustered index myindex on Worker(stdate)

--select  *  from Worker order by stdate 

--close mycursor

--deallocate mycursor

declare mycursor scroll cursor for select stname,stdate from Worker

declare @date varchar(4), @cnt int, @stname varchar(10);

open mycursor

fetch first from mycursor into @stname,@date

select @cnt = count(*) from Worker where stdate=@date group by stdate

update Worker set cnt=@cnt where stdate = @date;

while @@FETCH_STATUS=0

begin

    fetch next from mycursor into @stname,@date

    select @cnt = count(*) from Worker where stdate=@date group by stdate

    update Worker set cnt=@cnt where stdate = @date;

end

close mycursor

deallocate mycursor

--select * from Worker order by cnt desc

select * from Worker where stdate in(

    select top 1 stdate from Worker group by stdate

    order by count(1) desc

)