T-SQL (base) of database series

Variable 1 Variable classification

Global variables, local variables.

2 Local Variables

DECLARE @ variable name variable type [, @ variable name variable type ...]

Declare @Name varchar (+) Declare @upoint int,@birthdaydatetime

Note: Before a local variable is assigned a value, its value is null.
2.1 Local variable Assignment
SET @ local variable = variable value
Or
SELECT @ local variable = variable value
Set only supports assigning a value to a variable
Select supports assigning values to multiple variables at the same time and is often used in select query statements.
2.2 Example of variable assignment

--declaring a variableDeclare @Name varchar( -)--declare two variables at a timeDeclare @upoint int,@birthday datetime/*You can assign this value*/Set @Name = 'Xiao Xin'Set @upoint =  $Select @birthday = '1990-5-8'/*You can also assign values like this*/Set @Name = 'Xiao Xin'Select @upoint =  $,@birthday = '1990-5-8'  fromCustomers

2.3 Example
Thinking analysis
First of all, to get the points of joy and happiness, stored in the variable @upoint;
Use the SELECT statement to query the records for the upoint> @upoint.

/* The first step: get the points of joy and joy */ Declare @upoint int Select @upoint =  from  where customername=' Joy '/* * Step two: Execute a conditional query statement */select * FROM Customers where Upoint > @ upoint

Note: The Select assignment statement cannot be used in conjunction with the SELECT statement

3 Global Variables

Global variables do not require user declaration, are defined at the server level, and are scoped to any program.
Global variables start with @@ 开头 and local variable names cannot be the same as global variables.
3.1 Common global variables
Variable meaning
@ @ERROR The error number of the last T-SQL error
@ @IDENTITY The last-inserted identity value
@ @ROWCOUNT The number of rows affected by the previous SQL statement
@ @SERVERNAME The name of the local server
There are two types of 3.2 output statements
Select query statement
Print statement
PRINT expression
Print is typically used to observe intermediate results when the T-SQL program runs.
3.3 Example

Declare @name varchar( -)Declare @upoint int UseBooksmanagerSelect @name=CustomerName@upoint =Upoint fromCustomerswhereCustomerID=‘10008'Print'Name:'[Email protected]print ' Points:'+Str(@upoint,5)

Control statements

Control statements in T-SQL are similar to other programming languages, mainly: order, condition, loop

Sequential statements

BEGIN
< command line or program block >
END

--Exampleif @myavg > -   begin     Print 'C # Programming excellence, the top three exam information is:'     Select Top 3 *  fromscorewhereCourseno=@courseid      Order  byScoredesc   EndElse  begin     Print 'C # Poor programming results, the following three exam information is:'     Select Top 3 *  fromscorewhereCourseno=@courseid      Order  byscoreEnd

Conditional statements

If statement

IF < conditional expressions >
< command line or program block >
[ELSE [conditional expression]
< command line or program block;]

 Use Master if exists (Select*fromwhere name=' Booksmanager')   drop Database Booksmanager

Branch statements

Case < op >
When < expressions > then < expressions >
......
When < expressions > then < expressions >
[ELSE < op;]
END

.........

SelectStuno, Score= Case     whenScore< -  Then 'E'     whenScorebetween  -  and  the  Then 'D'     whenScorebetween  -  and  -  Then 'C'     whenScorebetween  the  and  the  Then 'B'    Else 'A'End fromscorewhereCourseno=@courseid

Looping statements

While < conditional expressions >
BEGIN
< command line or program block >
[Break]
[CONTINUE]
[Command line or program block]
END

 while @pass/@total <0.8begin     UpdateScoreSetScore=Score+2 whereCourseno=@courseid     Select @pass = Count(*) fromScorewhereCourseno=@courseid  andScore>= -End

Batch Processing

In SQL Sever, you can execute multiple T-SQL statements at once, which are called batch statements.
"GO" is a token of batch processing.

 Use MySchool Go -- batch-processing flags Select *  from Course1  -- There is no course1Select* from StudentGo-- batch processing flag

Comments

There are two types of annotation that can be used in the T-SQL language: line comments and block annotations.
Comments do not participate in code execution.
--line comments for less descriptive text

/* *  block annotations are used to describe more descriptive text occasions ...  ........**/

T-SQL (base) of database series