Shulou(Shulou.com)06/01 Report--

1. Identifier:

(1)Definition: Just as everyone needs a name, in SQL Server, every object needs a name that is used as an identifier. For example, database names, data table names, field names, and so on, collectively referred to as identifiers.

(2)Naming rules:

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

Number: 0~9, but the number shall not be used as the first character of the identifier.

Special characters: _,#,@,$, but #,@,$shall not be used as the first character of the identifier.

d. Legal characters of special language families: for example, Chinese characters can also be used as legal characters of identifiers.

Special note: If the object name does not conform to the above rules, as long as the square brackets are added around the name, the name becomes a legal identifier (but the length of the identifier cannot exceed 128 characters).

Note: Identifiers cannot be key words of SQL, such as "table","TABLE","select","SELECT", etc. cannot be used as identifiers.

Identifiers cannot have spaces or special symbols other than_,#,@, or $.

Identifiers must not exceed 128 characters in length

2. Variables:

(1)Category:

There are two types of variables in SQL Server:

Global variables: defined and maintained by the system, preceded by "@@"

For example: @@VERSION

Local variables:

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

(2)Declarations of local variables:

Basic format:

declare @ Variable name Data type

Assignment of variables:

Format 1: simultaneous assignment of defined variables

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

select assignment,(select @ variable name as expression)

Examples:

DECLARE @A CHAR(20)

DECLARE @f float，@cn char(8)='aa'

Notes:

Multiple variables can be declared at once, separated by commas

All local variables are initialized to NULL after declaration

Declared local variables can be assigned with 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 simultaneously.

(3)Output of local variables:

The output of local variables can be achieved by print and select, the difference is that print can only output the value of one variable at a time, select can output the value of multiple variables at a time

Batch processing and scripts:

(1)Batch processing:

A batch is a set 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 requirements for writing batch processing

In Query Analyzer, you can mark the end of a batch with the GO command. GO is not a T-SQL statement; it tells the query analyzer how many statements to include in the current batch.

The query analyzer takes the statements before the first GO, the statements between two GO, or the statements after the last GO as a batch process, and respectively forms a character string to be executed by the server.

Problems to be noted when using batch processing:

a: Variables defined in other batches cannot be referenced in one batch

b: Comments cannot start in one batch and end in another batch

c: You cannot modify the structure of a table in a batch and then introduce the newly modified column in the same batch.

d: You cannot create a database in one batch and then select it in the same batch

e: Most statements with the CREATE keyword cannot be in the same batch as other statements, such as CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements, but CREATE TABLE, CREATE DATABASE, and CREATE INDEX can.

f: Use EXEC statements when stored procedures need to be executed. If the stored procedure is the first statement in a batch, it can be executed by omitting the EXEC keyword

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

(2)Script:

A script is a series of batches submitted sequentially. Scripts can be entered and executed directly in tools such as query analyzer, or they can be saved in files and opened and executed by tools such as query analyzer

A script can contain one or more batches. GO command in a script marks the end of a batch. If a script does not contain GO command, it is considered a batch as a whole.

3. Operator:

(1)Definition: An operator is a symbol used to specify an operation 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 concatenation operators

(2)Arithmetic operators:

Arithmetic operators include +(addition), -(subtraction), *(multiplication),/(division), and %(remainder of integer division) for numerical and date-time operations.

The functions of some of the above operators are already clear, so I will not repeat them here. Here, I will only explain the operation of date and time data. Date time and numerical value can be added or subtracted, the meaning is the date plus a few days or minus a few days, the result is still the date time data.

(3)Assignment operator:

There is only one assignment operator,"="(equal sign), which assigns a value or string to a field or variable.

(4)Bitwise operators:

Bitwise operators include &,| Three types,^, are used to perform logical operations on bits.

&: is the bitwise AND operator. When both operands before and after the operator are 1, the result is 1, and as long as one of them is not 1, the result is 0.

|: is the bitwise OR operator. The result is 1 if one of the operands before and after this operator is 1, and 0 if both operands are 0.

^: Exclusive OR operator. It is 1 only if the two operands have different values, otherwise it is 0.

(5)Comparison operator:

The comparison operator, also known as the relational operator, tests the relationship between the values of two expressions, and its result is a Boolean value of TRUE or FALSE.

Comparison operators can be used for all expressions except data of type text, ntext, or p_w_picpath.

(6)Logical operators:

Logical operators are used to test a condition for its truth. Logical operators, like comparison operators, return Boolean data types with TRUE or FALSE.

(7)String concatenation operators:

The string concatenation operator is "+" and is used to concatenate strings. It concatenates string variables, columns, and string expressions. If data of another data type is added to a string, it must be converted to a character type.

(8)Operator precedence:

When multiple operators are used to compose an expression, the operator with higher priority takes precedence. If you want some part to take precedence, you can enclose it in parentheses. If there are multiple parenthesis, the formula in the inner layer takes precedence. For example,"3*(6/(4-2))" results in 9.

Listed below in descending order of priority:

(b) parenthesis

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

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

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

=, >, =, , !

^(bit exclusive OR),&(bit AND),|(bit or)

NOT

AND

ALL、ANY、BETWEEN、IN、LIKE、OR、SOME

=(assignment)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.