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