sqlserver练习

            create
            table
             Test(
    name 
            varchar(4),
    age int,
    sex varchar(2)
)
altertable Test
add id char(16)
altertable Test
altercolumn id varchar(16)
altertable Test
dropcolumn id
droptable Test
select*from Test

            create
            table
             Test(
    name 
            varchar(4),
    age int,
    id char(16)
)
altertable Test
 altercolumn name varchar(6) 
insertinto Test 
values(‘崔哥哥‘,15,‘311409060209‘);--注意单引号deletefrom Test
where name=‘崔哥哥‘update Test set age=18where name=‘崔哥哥‘bulkinsert Test 
from‘C:UsersAdministratorDesktoptest.txt‘with(
    fieldterminator=‘‘,rowterminator=‘n‘
)

select name from Test whereLEN(id)=7orderby age 
--desc ascselect*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())
printdateadd(minute,2,getdate())
printdateadd(second,2,getdate())
printdateadd(hour,2,getdate())
printdatediff(second,getdate(),dateadd(hour,2,getdate()))
printdatepart(week,getdate())-datepart(week,dateadd(month,-1,getdate()))
printstr(1.25,3,1)+‘asd‘--print str(参数1,参数2,参数3)
--参数2表示转换后的数据的总位数，包括小数点，正负号
--参数3表示转换后的数据的小数位数

            bulk
            insert
             Test 

            from
            ‘
            C:UsersAdministratorDesktoptest.txt
            ‘
            with
            (
    fieldterminator
            =
            ‘
            ‘,rowterminator=‘n‘
)

            --
            declare mycursor scroll cursor for select * from Test
            open
             mycursor

            declare
            @name
            varchar(6),@ageint,@idchar(16)

fetch first from mycursor into@name,@age,@idprint@nameprint@ageprint@idwhile@@FETCH_STATUS=0beginfetchnextfrom mycursor into@name,@age,@id--absolute 10print@nameprint@ageprint@idendclose 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) primarykeyaltertable Test
altercolumn id varchar(16) notnullaltertable Test
dropcolumn age
altertable Test
add age intcheck( age>=0and age<=100)

            create
            view myview asselect*from Test
select*from myview
select*from myview where age=18createclusteredindex myindex on Test(age)
alterindex myindex on Test rebuild
dropindex Test.myindex