SQLSERVER3T-SQL programming and advanced Query

1. T-SQL and SQLSQL fourth-generation programming language you just need to tell the computer What You Want To Do example: selectupdateT-SQL attaches the characteristics of the second-generation programming language, such as flow control (if-elsewhile) error Handling Stored Procedure transaction trigger 2. SQL comment-single line comment ** multiple lines comment 3. batch processing 10 Inserts

1. T-SQL and SQL fourth-generation programming language you just need to tell the computer What You Want To Do example: select update T-SQL attaches the characteristics of the second-generation programming language, such as flow control (if-else while) error Handling Stored Procedure transaction trigger 2. SQL annotation-single line annotation/**/multi-line annotation 3. Batch Processing of 10 Inserts

1. T-SQL and SQL

For the fourth generation programming language of SQL, you only need to tell the computer what to do. Example: select update

T-SQL attaches the features of the second generation of programming language, such as process control (if-else while) error handling stored procedure thing trigger

2. SQL comments

-- Single line comment/**/multi-line comment

3. Batch Processing

If 10 insert statements do not contain the go batch processing keyword, the SQL engine processes the statements one by one (which affects the efficiency)

If the go batch processing keyword is added, the SQL engine will process it at one time (improving processing efficiency)

4. Variables

Global variables @ (defined by the system) and local variables @

(1) variable definition declare @ variable name variable data type

Example: declare @ count int, @ count2 int

Declare @ count1 int

(2) variable assignment set select

Example: set @ count = 1 can be assigned only one value

Select @ count = 1 values are generally used in queries.

Example: select @ name = StuName from student where StuNo = 's25301'

(3) Before the go keyword is added, variables work before the end of the stored procedure.

(4) common global variables @ ERROR @ IDENTITY @ ROWCOUNT

5. Output

Print string | variable | function | string expression

6. Process Control statements

(1) if-else

If (condition)

Begin

Statement 1

Statement 2

End

Else

Begin

Statement 1

Statement 2

End

(2) case

-- Simple format

Select pcid,

Case PCUse

When 0 then 'id'

When 1 then 'busy'

End as 'pcuse'

, Pcnote from pcinfo

-- Search format

Select pcid,

Case

When PCUse = 0 then 'id'

When PCUse = 1 then 'busy'

End as 'pcuse'

, Pcnote from PCInfo

(3) while

While (condition for loop Establishment)

Statement xxx

[Break]

[Continue]

Example:

Declare @ count int

While (1 = 1)

Begin

Select @ count = COUNT (*) from cardInfo where cardBalance <20

If (@ count> 0)

Update cardInfo set cardBalance = cardBalance + 1 where cardBalance <20

Else

Break

End

7. subquery

To merge data from multiple tables, follow these steps:

EXISTS clause:

By using the EXISTS clause in the subquery, you can check whether the row in the subquery EXISTS.

IN subquery:

The IN subquery can follow the subquery that returns multiple records. The Hong Kong VM is used to check whether the value of a column is IN a certain range.

Subquery Division:

If a subquery is divided by its location, it can be a subquery following the WHERE keyword, A subquery following the FROM keyword, and a subquery following the SELECT keyword.

, Server space, server space