SQL Server Practice

1. Basic Table Exercises:

Create TableTest (Namevarchar(4), ageint, Sexvarchar(2))Alter TableTestAddIdChar( -)Alter TableTestAlter columnIdvarchar( -)Alter TableTestDrop columnIDDrop TableTestSelect *  fromTest

2. Record Operation DML:

Create TableTest (Namevarchar(4), ageint, idChar( -))Alter TableTestAlter columnNamevarchar(6) Insert  intoTestValues('brother Choi .', the,'311409060209');--Note single quotation marksDelete  fromTestwhereName='brother Choi .'UpdateTestSetAge= - whereName='brother Choi .'Bulk InsertTest from 'C:\Users\Administrator\Desktop\test.txt' with(FieldTerminator=' ', Rowterminator='\ n')SelectName fromTestwhere LEN(ID)=7 Order  by Age--desc ASCSelect *  fromTest

3.function usage:

--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 (' John Doe ',;--SELECT * from Test--where Left (name,1) = ' Cui ' or right (name,1) = ' three ' or substring (name,2,1) = ' Four '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 (parameter 1, parameter 2, parameter 3)--The parameter 2 represents the total number of digits of the converted data, including the decimal point, the sign--Parameter 3 indicates the number of decimal digits of the converted data

4.bulk Insert Usage:

Bulk Insert  from'C:\Users\Administrator\Desktop\test.txt'with(    FieldTerminator = ' ', rowterminator='\ n')

5.cursor usage:

--DECLARE mycursor scroll cursor for SELECT * FROM TestOpenMyCursorDeclare @name varchar(6),@age int,@id Char( -)FetchFirst fromMyCursor into @name,@age,@idPrint @namePrint @agePrint @id while @ @FETCH_STATUS=0begin    Fetch Next  fromMyCursor into @name,@age,@id    --Absolute Ten    Print @name    Print @age    Print @idEndCloseMyCursordeallocateMyCursor

6.primary key,unique,not null, check usage:

Alter TableTestDrop columnnameSelect *  fromTestDelete  fromTestSelect *  fromTestAlter TableTestAddNamevarchar(6)Primary KeyAlter TableTestAlter columnIdvarchar( -) not NULLAlter TableTestDrop column AgeAlter TableTestAddAgeint Check(age>=0  andAge<= -)

7.view and Index:

Create ViewMyView as Select *  fromTestSelect *  fromMyViewSelect *  fromMyViewwhereAge= -Create Clustered IndexMyindex onTest (age)Alter IndexMyindex onTest RebuildDrop IndexTest.myindex

8.trigger usage:

SQL Server Practice