[t-SQL] from variable to data type

1. Variables

Learning computer language, inevitably do not encounter this noun, but we are talking about the MSSQL (Microsoft SQL Server products) in the database language implementation.

A slightly more rigorous program of the language requires the use of the first to declare variables, such as c.c++,java,c#, T-SQL is no exception.

Can be tested under:

SELECT @hello

There will be an error message similar to the following:

[ERR] 42000-[SQL Server] must declare a scalar variable "@hello".

The declaration variable uses the following keyword: DECLARE (case insensitive)

Declaring a variable requires a type, because "I" want to know whether you are an integer or a date or a string, how much byte space should I allocate to you?

2. Data type

The data type is a category such as an integer, a character, as mentioned earlier.

Our format is:

DECLARE variable name type

I agree here, the keyword and type, such as uppercase, variable name lowercase, you can not, but I hope you abide by the "our school" norms, hehe.

First of all, TSQL this child variable a lot of Wow, and other languages are similar (other languages have, local variables, global variables and so on, I do not have any big special)

Here we use the Local variant, the T-SQL local variable is the @ followed by the variable name,

DECLARE @hello VARCHAR (+)

I can declare more than one at a time,

DECLARE @i INT,

@j INT

Of course, after the declaration, our value is a null (note that it is not a ' null ' string OH)

SELECT @i you have a look.

You pit me, the light is null how to use, don't worry, let's see how to assign them, not with other languages directly with the assignment operator = bar, but the syntax slightly different

3. Assigning values

1. SET @i=123

Let's see this time. Select @i

Of course set can also be replaced with select.

A little bit more complicated:

--=====================

--Calculate the area @[email protected]*@n

--=====================

DECLARE @m int,         @n int,         

The assignment can also be the result of SQL execution:

　　

Variables can also be used in SQL, such as the following:

4. Process Control

Branching conditional Statements This is too common, order, branching, and repetition (looping) are the 3 processes of structured programming, which has to be said.

Let's see how the conditional statements in T-SQL are written, if not many of the keywords are said.

As follows:

1. DECLARE @i INT
2. SET @i=123
3. IF @i>0 (SELECT ' positive ')

A statement can do so, a number of statement block how the whole, don't worry, BEGIN ... End, (if not seen in some languages, c similar to the inside of {} is always the line)

DECLARE @i INT SET @i=123 IF @i>0      BEGIN         Select ' Positive '         select ' Not negative '     END ELSE     

Yes, and else is supported.

can also be nested.

Loop loops

Use the keyword while

DECLARE @i INT Set @i=0 while @i<10     BEGIN         SET @[email protected]+1         PRINT ' @i= ' +cast (@i as CHAR)     

　　

While can also be nested.

5. Appendix: Summary of data types

Bit bit: value 0, 1, and null

Integral type:

Tinyint: Value 0-255, storage size 1 bytes

Smallint:2 bytes, -32768~+32767

Int:4 bytes

Bigint:8 bytes

The following documents are from:

Decimal and numeric

Decimal

Fixed precision and decimal digit data from -10^38 +1 to 10^38–1.

Numeric

Functionally equivalent to decimal.

Money and SmallMoney

Money

The currency data value is between -2^63 ( -922,337,203,685,477.5808) and 2^63-1 (+922,337,203,685,477.5807), which is accurate to 10 per thousand of the currency unit.

SmallMoney

The currency data value is between -214,748.3648 and +214,748.3647 and is accurate to 10 per thousand of the currency unit.

Approximate numbers

Float

Floating-point precision numbers from -1.79e + 308 to 1.79E + 308.

Real

Floating-point precision numbers from -3.40e + 38 to 3.40E + 38.

DateTime and smalldatetime

Datetime

Date and time data from January 1, 1753 to December 31, 9999, accurate to 3% seconds (or 3.33 milliseconds).

smalldatetime

Date and time data from January 1, 1900 to June 6, 2079, accurate to minutes.

String

Char

Fixed-length non-Unicode character data with a maximum length of 8,000 characters.

varchar

Variable-length non-Unicode data with a maximum of 8,000 characters.

Text

Variable-length non-Unicode data with a maximum length of 2^31-1 (2,147,483,647) characters.

Unicode string

NChar

Fixed-length Unicode data with a maximum length of 4,000 characters.

nvarchar

Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar, which refers to the database object name.

ntext

Variable-length Unicode data with a maximum length of 2^30-1 (1,073,741,823) characters.

Binary string

Binary

Fixed-length binary data with a maximum length of 8,000 bytes.

varbinary

Variable-length binary data with a maximum length of 8,000 bytes.

Image

Variable-length binary data with a maximum length of 2^31-1 (2,147,483,647) bytes.

Other data types

Cursor

A reference to the cursor.

sql_variant

A data type that stores values for various data types supported by SQL Server (excepttext,ntext,timestamp , and sql_variant ).

Table

A special type of data that stores the result set for later processing.

Timestamp

The unique number of database scopes that are updated each time the row is updated.

uniqueidentifier

The globally unique identifier (GUID).

Reprinted from: http://xcf007.blog.51cto.com/471707/833008