SQL statement: T-SQL usage and syntax

The basic statement of T-SQL, most people are very familiar with, today help house small Editor and everyone to share the SQL statement, T-SQL usage and syntax, as well as some examples, the purpose is to help everyone to review the basic statements of T-SQL, so that the project can be used more quickly, of course, for Beginners should learn and sum up. I personally feel that database is very important for programmers, both JAVA programmers, dotnetprogrammers, and other programmers. In order to help everyone better to review the T-SQL, the house of small make up the use of free time to sort out the T-SQL statements and syntax. Hope to be useful to everyone!

MySQL database management tool 64Bit	Http://www.bkjia.com/soft/3999.html
MySQL Database Manager 32Bit	Http://www.bkjia.com/soft/4554.html

Okay! Let's first look at several images:

Prism

Triangle

Square

Circle

Rectangle

Trapezoid

The above graphics are implemented with T-SQL, the article at the end of the we together with the T-SQL to draw these graphics.

[Page_break]

First, let's look back at the basic syntax of T-SQL:

Function

Abs (x): returns the absolute value;

Example: select abs (-3) value: 3

Sqrt (x): returns the square root;

For example, the value of select sqrt (4) is 2.0.

Rand ([0]): returns 0 ~ Random float value between 1;

Floor (X): returns the largest integer that is less than or equal to the value of X;

For example, the select floor (34.5) value is 34.

Ceiling (X): returns the smallest integer greater than or equal to the value of X;

For example, the select ceiling (34.5) value is 35.

Round (x, length): Rounding function. If length is positive, rounding X decimal places and length is negative, rounding X from the length to the left of the decimal point, if length is a negative number and its absolute value is greater than the number of digits in the X integer part, the function value is 0;

Example: select ROUND (63.567, 1) Value: 63.600

Select ROUND (63.567,-1) Value: 60.000

Select ROUND (63.567, 0) value: 64.000

Select ROUND (63.567,-3) value: 0.000

Sign (X): Evaluate the signed function. If X> 0, sign (x) = 1. If X = 0, sign (X) = 0. If X <0, sign (X) =-1

For example, the select sign (-3) value is-1.

Select sign (3) value: 1

Select sign (0) value: 0

Power (X, y): returns the Power of y of X;

For example, the select power () value is 16.

String Functions

ASCII (string): returns the ASCII value of the leftmost character of a character expression;

For example, the select ASCII ('bc') value is 98.

CHAR (ASCII code): used to convert an ASCII code to a character. If no value is entered ~ ASCII code value between 255. the return value is NULL;

For example, the select char (97) value is.

Lower: converts all strings to lowercase letters;

Example: select lower ('qingpingguo') value: QingPingGuo

Upper (string): converts all strings to uppercase;

Example: select upper ('qingpingguo') value: QingPingGuo

LTrim (string), RTrim (string): Remove left and right spaces;

For example (left blank space): select 'blog service' + LTrim ('green app') + 'blog service'; Value: qingapple blog Service'

Space (number): returns a specified number of spaces;

Replicate (string, number of times): specifies the number of times the string is repeated;

Example: select replicate ('green app', 2) value: Green app'

Left (string, number): returns the number of characters specified from the Left of a known string;

Example: select left ('qingapple in the blog service', 4) value: qingapple in

Right (string, number): returns the number of characters specified from the Right of a known string;

Example: select right ('qingapple in the blog service', 4) value: In the blog Service'

DataLength: returns the length of the number of bytes of the string, and calculates the space at the end of the string. You can use it to check the dynamic lengths of varchar and text;

For example, select datalength ('green apple in blog Service') value: 14

SubString (string, start position, length): returns the string with the 'start position 'on the left of the string and the number of strings with the 'length. The expression can be a string, a binary string, or an expression containing a field name or variable. Note that the SUBSTRING () function cannot be used for the TEXT and IMAGE data types;

For example, select substring ('qingapple in the blog service', 5, 2): blog

Len (string): returns the length of the expression. Note that it returns the number of characters rather than the number of bytes. Do not calculate trailing spaces of strings;

Example: select len ('qingapple cnblogs') value: 10

Replace ('string1', 'string2', 'string3'): Replace all strings that appear in string 1 with string 3;

For example, select replace ('green apple in Beijing', 'beijing', 'blog Garden ') value: green apple in blog Garden

Stuff (string 1, start position, length, string 2): delete string 1 at the specified position and insert string 2 at the specified position;

For example, select stuff ('is the green apple a programmer? ', 5, 3, 'siege lions') value: Are qingapple lions attacked?

Reverse (string): reverse the character arrangement order of the specified string;

For example, the select reverse ('20140901') value is 12345.

Charindex (string 1, string 2): returns the starting position of string 1 at string 2, which can be searched from the given 'starting location;

Example: select charindex ('guo', 'qingpingguo') value: 9

Conversion functions

The so-called conversion function is to convert the expression of a data type conversion to another data type.

CAST (expression AS data type [(length)])

Example: select 'Today is: '+ Cast (GetDate () as char (10) value: today is: 07 23 2012

CONVERT (converted target data type [(length)], expression [style])

Example: select 'Today is: '+ convert (char (10), getdate () value: today is: 07 23 2012

Next, let's look at a function called datediff to evaluate the difference;

DATEDIFF (datepart, date1, date2)

For example, select datediff (yy, '192. 100', '192. 100') value: 24

Select datediff (mm, '192. 1988 ', '192. 100') value: 09.14

Aggregate functions

An aggregate function is a statistical function. It is mainly used to calculate a group of values. Its functions are sum, min, and max), calculates the total number of rows (count), calculates the average value (avg)

Example: sum: select sum (Name) from TableName

Minimum: select min (Name) from TableName

Maximization: select max (Name) from TableName

Sum: select count (Name) from TableName

Averaging: select avg (Name) from TableName

Some keywords for T-SQL

Print returns user information to the client

For example, on the screen of print 'green app', "Green app' is displayed."

Go is used to notify the end of a batch of SQL statements.

Remove duplicate values from Distinct

Declare is used to Declare Variables

Example: declare @ a int

Set assigns a value to a variable.

For example, set @ a = 'qingapple'

While is used for loop in SQL (as if there are not many keywords used for loop in SQL)

Syntax: WHILE <conditional expression>

BEGIN

<Command line or program block>

[BREAK]

[CONTINUE]

[Command line or program block]

END

Whil is important. Let's give an example to further understand the While loop:

Declare @ a int

Set @ a = 1

While @ a <5

Begin

Print 'green app'

Set @ a = @ a + 1

End

Output result: qingapple

Green Apple

Green Apple

Green Apple

If else judgment statement

To judge whether statements are used more often, let's give an example;

Q: What is the maximum number of numbers a, B, and c?

Declare @ a int, @ B int, @ c int, @ max int

Set @ a = 1 set @ B = 2 set @ c = 3

If @ a> @ B

Set @ max = @

Else

Set @ max = @ B

If @ max <@ c

Set @ max = @ c

Print @ max

Output result: 3

Begin end is used to set a program block... All programs in END are considered as one unit.

Exists

Case is also used for judgment. It is similar to the IF statement, and its format is:

CASE <formula>

WHEN <Formula 1> THEN <result 1>

...

WHEN <formula n> THEN <result n>

[ELSE <result n + 1>]

END

Return is used to end the execution of the current program and Return to the previous program or other program that calls it. You can specify a return value in parentheses.

The Goto identifier is used to change the process of program execution, so that the program jumps to the specified program line marked with the identifier and continues to execute. Note that the identifier of the jump target can be a combination of numbers and characters, but must end with ":", for example, "1023:" "qingpingguo :"

Example:

Declare @ a int

Set @ a = 1

Qingpignguo:

Print @

Set @ a = @ a + 1

While @ a <6

Goto qingpignguo

Output result: 12345

The last one is interesting:

Waitfor is used to pause program execution, and continues to run the program until the specified time is reached or the specified time is reached.

Syntax: waitfor {delay 'time' | time '}

Explanation:

(1) 'time' must be DATETIME-type data and cannot contain dates, for example, '10: 12: 05'

(2) DELAY: used to set the length of wait time, which can be up to 24 hours. (A time interval)

(3) TIME: used to set the wait TIME (a specific TIME)

Example:

Waitfor delay '00: 00: 03'

Print 'Hello, I'm a green app'

Go

The above is all the content of the T-SQL.

[Page_break]

Next we will use the last time to draw several figures:

Right Triangle:

Declare @ a int

Set @ a = 1

While (@ a <11)

Begin

Print replace (space (@ ),'','*')

Set @ a = @ a + 1

End

Output result of right triangle:

Square:

Declare @ a int

Declare @ B int

Declare @ c nvarchar (100)

Set @ a = 1

Set @ B = 1

Set @ c =''

While (@ a <9)

Begin

While (@ B <15)

Begin

Set @ c = @ c + '*'

Set @ B = @ B + 1

End

Print @ c

Set @ a = @ a + 1

End

Square output result:

Diamond:

Declare @ a int, @ B int

Set @ a = 1 set @ B = 15

If (@ B % 2! = 1)

Print 'digits must be odd number'

Else

While (@ a <= @ B)

Begin

If (@ a % 2 = 1)

Print space (@ B-@ a)/2) + replace (space (@ a), '', '*') + space (@ B-@) /2)

Set @ a = @ a + 1

End

Set @ a = @ A-2

While (@ a <= @ B)

Begin

If (@ a % 2 = 1)

Print space (@ B-@ a)/2) + replace (space (@ a), '', '*') + space (@ B-@) /2)

Set @ a = @ A-1

If (@ a <0)

Break

End

Diamond output result:

Trapezoid:

Declare @ a int, @ B int

Set @ a = 7 set @ B = 21

If (@ a % 2 = 1)

While (@ a <@ B)

Begin

Print space (@ B-@ a)/2) + replace (space (@ a), '', '*') + space (@ B-@) /2)

Set @ a = @ a + 2

End

Trapezoid output result:

Rectangle:

Declare @ a int

Declare @ B int

Declare @ c nvarchar (100)

Set @ a = 1

Set @ B = 1

Set @ c =''

While (@ a <9)

Begin

While (@ B <23)

Begin

Set @ c = @ c + '*'

Set @ B = @ B + 1

End

Print @ c

Set @ a = @ a + 1

End

Rectangular output result:

Circle:

Declare @ a int, @ B int

Set @ a = 9 set @ B = 13

While (@ a <= @ B)

Begin

If (@ a % 2 = 1)

Print space (@ B-@ a)/2) + replace (space (@ a), '', '*') + space (@ B-@) /2)

Set @ a = @ a + 1

End

Set @ a = @ A-1

Begin

Print space (@ B-@ a)/2) + replace (space (@ a), '', '*') + space (@ B-@) /2)

End

While (@ a <= @ B)

Begin

If (@ a % 2 = 1)

Print space (@ B-@ a)/2) + replace (space (@ a), '', '*') + space (@ B-@) /2)

Set @ a = @ A-1

If (@ a <10)

Break

End

Set @ a = @ A-2

Begin

Print space (@ B-@ a)/2) + replace (space (@ a), '', '*') + space (@ B-@) /2)

End

Circular output result:

Recommended reading:

SQLcode error code summary and sqlstate = 37000 Solutions