What is a script. We learned in front of the Create TABLE <table name>, use <database name> These are scripts, why with scripts. Scripts are stored in files and can be reused and extracted.
Create variable: DECLARE statement most commonly used syntax: DECLARE @<variable name> <variable type>[=value][[,@<variable name> < Variable type>[=value]@<variable name> <variable type>[=value],[.....n]];
DECLARE @TotalCount int=0 --Create a variable and initialize the value (SQL server2008 the following database error)Select @TotalCount --Output 0SET @TotalCount=Ten--set the variable value to tenSelect @TotalCount --Output TenSelect @TotalCount=Count(*) fromdbo.test003Select @TotalCount --Output 5You can also create a table. DECLARE @temp Table(IDint Identity(1,1)Primary Key, namenvarchar( -) not NULL)Insert into @temp Values('test_1');Insert into @temp Values('test_2');Select * from @tempResult: The following
Using Process Control Statements:
Create a data table first
Use Panda GO CREATE TABLE test004 ( ID INTIdentity(1,1) primary Key , name nvarchar(notnull, typeint int )
If....else: Basic grammar If<boolean Expression><sql statement>| BEGIN <code series> END [ELSE <sql statement>| BEGIN <code series> END] where the expression can be almost any Boolean expression.
Declare @count int Set @count=0; while(@count< -)begin Set @count=@count+1; if(@count%4=0) Insert intodbo.test004Values('Test_'+Convert(nvarchar( -),@count),0); Else Insert intodbo.test004Values('Test_'+Convert(nvarchar( -),@count),@count%4);End
More than one statement:
if (Boolen expression) begin -- Statement 1 -- Statement 2 -- Statement 3 End -- multiple statements are tied together into a single statement
Case: Basic syntax: Case<input expresstion> when <when expression> then <result expression>[...] [ELSE <result Expression>] END
SELECTId,name,[ State]= CaseTypeint%4 when 1 Then 'Shipping' when 2 Then 'Sign' when 3 Then 'return' ELSE 'Unknown' END fromdbo.test004
While basic syntax: while <boolen expression>|<sql statement> [begin <statement Block>[break]<sql Statement>|<statement block>[continue] end];
Break is a forced end loop. CONTINUE back to the starting point and stop the current loop.
Declare @count int Set @count=0; while(@count< -)begin Set @count=@count+1; Insert intodbo.test004Values('Test_'+Convert(nvarchar( -),@count),@count/4)End
WAITFOR: Basic syntax: waitfor delay< ' time ' > | Time < ' time ' >
The delay parameter specifies the period of time to wait and the number of days that can be specified-only hours, seconds, and the maximum allowable delay of 24 hours. Therefore, the following statement. WAITFOR DELAY ' 24:00 ' will run any code before the WAITFOR statement. The WAITFOR statement is then stopped for 24 hours before the next statement continues.
The time parameter parameter specifies the waiting period, cannot specify a date, can only be a 24-hour time, and the same maximum delay time is one day. Therefore, the following statement. WAITFOR time ' 00:00 ' will run any code before the WAITFOR statement until 0 o'clock in the morning to stop execution. After executing the WAITFOR statement, the next code is followed.
Conclusion: Because of limited knowledge, plus beginners, this chapter blog is not very good, there is time to revise. and re-write, there is a writing where the description is not clear ... Will be updated later ... The next chapter of the blog is the stored procedure.
SQL Getting Started Classic (vii) script and batch processing