sqlserver練習

標籤：lte   locate   next   pre   prim   表示   ring   second   table   

1.基本表的練習：

create table Test(    name varchar(4),    age int,    sex varchar(2))alter table Testadd id char(16)alter table Testalter column id varchar(16)alter table Testdrop column iddrop table Testselect * from Test

 

2.記錄操作DML：

create table Test(    name varchar(4),    age int,    id char(16))alter table Test alter column name varchar(6) insert into Test values(‘崔哥哥‘,15,‘311409060209‘);--注意單引號delete from Testwhere 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 ascselect * from Test

 

3.function用法：

--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表示轉換後的資料的小數位元

 

4.bulk insert用法：

bulk insert Test from ‘C:\Users\Administrator\Desktop\test.txt‘with(    fieldterminator=‘ ‘,rowterminator=‘\n‘)

 

5.cursor用法：

--declare mycursor scroll cursor for select * from Testopen mycursordeclare @name varchar(6),@age int,@id char(16)fetch first from mycursor into @name,@age,@idprint @nameprint @ageprint @idwhile @@FETCH_STATUS=0begin    fetch next from mycursor into @name,@age,@id    --absolute 10    print @name    print @age    print @idendclose mycursordeallocate mycursor

 

6.primary key，unique，not null, check用法:

alter table Test drop column nameselect * from Testdelete from Testselect * from Testalter table Testadd name varchar(6) primary keyalter table Testalter column id varchar(16) not nullalter table Testdrop column agealter table Testadd age int check( age>=0 and age<=100)

 

7.view and index:

create view myview as select * from Testselect * from myviewselect * from myview where age=18create clustered index myindex on Test(age)alter index myindex on Test rebuilddrop index Test.myindex

 

8.trigger用法:

 

sqlserver練習