The identifiers, variables, batches, and operators of SQL-learned T-SQL programming

1. identifiers:

(1) Definition: Just like everyone has a name, in SQL Server, each object also has a name that is used as the identity, which is the identifier. For example, database names, data table names, field names, and so on, these names are collectively referred to as identifiers.

(2) Naming rules:

A, English characters: A~z or a~z, which are not case-sensitive in SQL.

B, Number: 0~9, but the number must not be the first character of the identifier.

C, special characters: _, #, @, $, but #, @, $ must not be the first character of the identifier.

D, the legal language of the special language: for example, Chinese characters can also be used as identifiers of the legal character.

Special Note: If the object name does not conform to the above rule, as long as the name is preceded by a square bracket, the name becomes a legal identifier (but the length of the identifier cannot exceed 128 characters)

Note: Identifiers cannot be the key word for SQL, For example, "table", "Table", "Select", "select" and so on cannot be used as identifiers.

Identifiers cannot have whitespace, or special symbols other than _, #, @, $.

The length of an identifier must not exceed 128 characters

2. Variables:

(1) Classification:

There are two types of variables in SQL Server:

Global variables: defined and maintained by the system, plus "@@" before variable names

such as: @ @VERSION

Local variables:

Defined and used by the user, only valid in the batch, stored procedure, or trigger that declares it. Variable name plus "@" such as: @mystr

(2) Declaration of a local variable:

Basic format:

DECLARE @ variable name data type

Assignment of variables:

Format 1: Defining variables at the same time as assigning values

Format 2:set assignment, (SET @ variable name = expression)

Select Assignment, (SELECT @ variable-name as expression)

Example:

DECLARE @A CHAR (20)

DECLARE @f Float, @cn char (8) = ' AA '

Precautions:

You can declare multiple variables at once, separated by commas

All local variables are initialized to null after declaration

Declared local variables can be assigned with a set or select

A SET statement can assign values to only one variable at a time, and a SELECT statement can assign values to multiple variables at the same time.

(3) Output of local variables:

The output of local variables can be implemented by print and select, except that print can output only one variable at a time, and select outputs values of multiple variables at once

3. Batch processing and scripting:

(1) Batch processing:

A batch is a set of statements consisting of one or more T-SQL statements . These statements are submitted to the server as a whole by the application and executed as a whole on the server side. Batch processing can be used according to your own programming habits and the requirements of writing batches

In Query Analyzer, you can use the go command to flag the end of a batch. Go is not a T-SQL statement , it is the function of informing the query parser how many statements to include in the current batch.

The Query Analyzer takes the first go statement or two go statements, or the last go statement as a batch, respectively, a string to the server to execute

Issues to be aware of when using batch processing:

A: You cannot reference a variable defined in another batch in one batch

B: You cannot start a comment from one batch and end it in another batch

C: You cannot modify the structure of a table in a batch, and then reference the new column that you just modified in the same batch

D: You cannot create a database in a batch, and then select the library in the same batch process

e: Most statements with the Create keyword cannot be in the same batch as other statements , such as Create DEFAULT, create PROCEDURE, create RULE, C Reate trigger with the CREATE VIEW statement, but create TABLE, create DATABASE, create INDEX can

F: use the EXEC statement when you need to execute the stored procedure . If the stored procedure is the first statement in the batch, omitting the EXEC keyword can also execute the stored procedure

G: Each batch is executed independently and does not affect each other . That is, the next batch continues to execute regardless of whether the previous batch was executed correctly

(2) Script:

A script is a sequence of batches that are submitted sequentially . Scripts can be entered and executed directly in tools such as Query Analyzer, stored in files, and opened by tools such as Query Analyzer

A script can contain one or more batches. The go command in the script flags the end of a batch, and if a script does not contain a go command, it is considered to be a batch

3. Operators:

(1) Definition: an operator is a symbol used to specify an action to be performed in one or more expressions. SQL Server uses the following types of operators: arithmetic operators, assignment operators, bitwise operators, comparison operators, logical operators, string join operators

(2) Arithmetic operators:

Arithmetic operators include + (plus),-(minus), * (multiply),/(except), and% (remainder of integer division), for numeric and datetime operations.

The functions of some of these operators are already clear and are not discussed here, only the operation of date-time data is described here. Date time and value can be added or reduced, the meaning of the date plus a few days or minus a few days, the result is still date time data.

(3) Assignment operator:

The assignment operator has only one, which is the "=" (equal sign) used to assign a numeric value or string to a field or variable.

(4) Bitwise operators:

The bitwise operators include &, |, ^ Three, which are used to perform logical operations on bits.

&: Is the bitwise AND (and) operator. When the operand before and after the operator is 1 o'clock, the result is 1, as long as one is not 1, the result is 0.

|: is the bitwise OR (or) operator. This operator before and after the operand only 1 to 1, the result is 1, only two are 0, the result is 0.

^: is an XOR (Exclusive or) operator. It is only 1 if the value of the two operands is different, otherwise 0.

(5) Comparison operators:

The comparison operator, also known as the relational operator, is used to test the relationship between the values of two expressions with the result that the value of the Boolean type is true or false.

In addition to the text, ntext, or image type data, the comparison operator can be used for all expressions.

(6) Logical operators:

A logical operator is used to test a condition to get its true condition. Logical operators, like comparison operators, return Boolean data types with True or FALSE.

(7) String Join operator:

The string join operation symbol is "+", which is used to concatenate strings. It can concatenate string variables, columns, and string expressions. If data of another data type is to be added to the string, it must be converted to a character type.

(8) Operator Precedence:

When multiple operators are used to form an expression, the operator with the higher precedence takes precedence of the operation. If you want a part to be prioritized, enclose it in parentheses. If there are multiple parentheses, the calculation in the inner layer takes precedence. For example, "6/(4-2)", the result is 9.

The following lists the priority levels from highest to lowest:

() brackets

+ (positive),-(negative), ~ (bitwise NOT)

* (multiply),/(except),% (modulo)

+ (plus), (+ series),-(minus)

=, <, >=, <=, <>,! =,!>,!< comparison operator

^ (bit xor), & (Bit and), | (bit or)

Not

and

All, any, between, in, like, or, SOME

= (assigned value)

This article from "A Growing small Tree" blog, reproduced please contact the author!

The identifiers, variables, batches, and operators of SQL-learned T-SQL programming