Database Print Graphics

Good! Let's take a look at some images first:

Rib Shape Triangular Square round Rectangle trapezoidal

All of these graphs are implemented in T-SQL, and at the end of the article we use T-SQL to draw these graphs.

First, let's start by reviewing the basic syntax of T-sql:

Function

ABS (x): Seek absolute value ;

Example: Select ABS ( -3)   value is: 3

sqrt (x): square root ;

Example: Select sqrt (4)  value is: 2.0

rand ([0]): Returns the random float value between 0~1 ;

Floor (x): Returns the largest integer less than or equal to the X value ;

Example: Select Floor (34.5) value is: 34

Ceiling (x): Returns the smallest integer greater than or equal to the x value;

Example: Select Ceiling (34.5) value is: 35

Round (x,length): Rounding function, length is positive, then the X decimal place rounding, length is negative, the X decimal points to the left of the length of the rounding, if length is both negative and its absolute value is greater than the number of x integer part, the function value is 0;

Example: Select ROUND (63.567, 1) value is: 63.600

Select ROUND (63.567,-1) values are: 60.000

Select ROUND (63.567, 0) is a value of: 64.000

Select ROUND (63.567,-3) values are: 0.000

Sign (x): Find the symbolic function, x>0 signs (x) = 1; X=0 sign (X) = 0; X<0 sign (X) =-1

Example: Select sign ( -3)    value is: -1  Select sign (3) value is:  1  Select sign (0)    value is: 0

Power (x, y): X is the y-square;

Example: the Select Power (4,2)  value is: 16

String functions

ASCII (String): Returns the ASCII code value of the leftmost character of the character expression;

Example: Select ASCII (' BC ') value is: 98

CHAR (ASCII code): used to convert the ASCII code to a character, or null if no ASCII value between 0 and 255 is entered;

Example: the Select Char  value is: a

Lower (String): convert all strings to lowercase;　　　　

Example: The value of select lower (' Qingpingguo ')   is: Qingpingguo

Upper (String): Convert all strings to uppercase;

Example: The value of select upper (' Qingpingguo ')  is: Qingpingguo

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

Example (go left space): Select ' Blog Park ' +ltrim ('   green apple   ') + ' blog Park '  value: Blog Park green Apple    Blog Park

Space (number): Returns the specified number of spaces;

Replicate (string, number of times): repeats the number of times specified;

Example: Select Replicate (' Green apple ', 2) value: Green apple green apple

Left (string, number): Returns the number of characters that are known to start at the beginning of the string;

Example: Select Left (' Green apple in Blog Park ', 4) value: Green Apples in

Right (string, number): Returns the number of characters that are known to start at the left of the string;

Example: Select Right (' Green apple in Blog Park ', 4) value: In the blog park

Datalength (String): Returns the length of the number of bytes in a string, calculating the trailing spaces. It can be used to check the dynamic length of varchar,text and so on;

Example: Select Datalength (' Green apple in Blog Park ') value: 14

SubString (string, start position, length): Returns a string that is ' length ' from the left ' start position ' of the string. Where an expression can be a string or a binary string or an expression with a field name or a character variable. Note here that the substring () function cannot be used for text and image data types;

Example: Select substring (' Green apple in Blog Park ', 5,2) value: Blog

Len (String): Returns the length of an expression. Note that it returns the number of characters, not the number of bytes. Do not count trailing spaces;

Example: Select   len (' Green apple cnblogs    ') value is: 10

Replace (' String 1 ', ' String 2 ', ' String 3 '): Replace all strings 1 characters that appear in string 2 with string 3;

Example: Select replace (' Green apple in Beijing ', ' Beijing ', ' Blog Park ') value: Green Apple in Blog Park

Stuff (string 1, start position, length, String 2): Delete string 1 at the specified position starting at the specified length, and insert strings 2 at the specified position;

Example: Select Stuff (' Green apple ' is a program ape? ', 5, 3, ' Siege Lion ') value: is the green apple a siege lion?

Reverse (String): The character of the specified string is arranged in a reversed order;

Example: The value of select reverse (' 12345 ') is: 54321

CHARINDEX (String 1, String 2): Return string 1 at the beginning of the string 2, can be found from the ' Start position ';

Example: Select CHARINDEX (' Guo ', ' Qingpingguo ') value is: 9

Conversion functions

A conversion function is a function that converts an expression of a data type into another data type.
CAST (expression as data type [(length)])

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

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

Example: Select  ' Today is: ' +convert (char (Ten), GETDATE ())    value: Today is: 07 23 2012

Here is a function DateDiff function for differential value;

DATEDIFF (DATEPART,DATE1,DATE2)

Example: Select  DateDiff (yy, ' 1988.09.14 ', ' 2012.12.21 ') values are:  datediff (mm, ' 1988.09.14 ', ' 2012.12.21 ') Value is: 291

Aggregation functions

An aggregate function is a statistical function that computes a set of values, with the functions of summing (sum), Minimum (min), maximum (max), Total row count (count), averaging (avg)

Example: Sum: Select SUM (name) from TableName  minimum: select min (name) from TableName     maximum: Select MAX (name) from Table Name    Total: Select COUNT (name) from TableName    averaging: Select AVG (name) from TableName　

Some keywords for t-SQL

Print returns user information to the client

Example: print ' green apple '         screen will show "Green Apple Three words"

Go is used to notify the end of SQL batch statements

Distinct Remove Duplicate values

Declare used to declare variables

Example: declare @a int

Set assigns a value to a variable

Example: Set @a= ' green apple '

While is used in SQL to loop (as if there are few keywords used in SQL to do loops)

Syntax: While < conditional expressions >
BEGIN
< command line or program block >
[Break]
[CONTINUE]
[Command line or program block]
END

Whil more important, let's do an example to deepen the understanding of the while loop:

DECLARE @a int
Set @a=1
While @a<5
Begin
print ' Green apple '
Set @[email protected]+1
End

Output result: Green apple
Green apples
Green apples
Green apples

If Else judgment statement

Judge the statement with more or more we will do an example to illustrate;

The maximum value of a, B, c three numbers?

DECLARE @a int,@b int,@c int, @max int
Set @a=1 set @b=2 set @c=3
If @a>@b
Set @[email protected]
Else
Set @[email protected]
If @max <@c
Set @[email protected]
Print @max

Output results: 3

Begin end is used to set a program block that will be at begin ... All programs in end are treated as one unit execution.

Exists judge whether there is

Case is also used to judge, and the IF statement is similar, its format is:

Case  < expressions > when     < expressions 1> then  < results 1>            ... when< expression n> then   < results n>          [ELSE  < results n+1>]  END

Return used to end the execution of the current program, returning to the previous program or other program that called it. You can specify a return value within the parentheses.

The goto identifier is used to change the process of execution of the program, causing the program to jump to the specified program line labeled with an identifier and continue execution. Note that the identifier as a jump target can be a combination of numbers and characters, but must end with ":", such as: "1023:" "Qingpingguo:"

Example:
DECLARE @a int
Set @a = 1
Qingpignguo:
Print @a
Set @a = @a + 1
While @a < 6
Goto Qingpignguo

Output results: 12345

The last one to show you a fun:

The Waitfor is used to pause the execution of the program until after the specified time has elapsed, or the set time is up to continue running the program.

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

Explain:

(1) ' Time ' must be datetime type data and cannot include date, such as ' 10:12:05 '

(2) DELAY: Used to set the length of time to wait, up to 24 hours. (is a time interval)

(3) Time: Used to set the point at which the wait ends (is a specific time)

Example:    waitfor  delay ' 00:00:03 ' print ' Hello, I'm green apple ' go

That's all of T-SQL, so let's use the last time to draw a few graphs:

Right triangle

DECLARE @a int
Set @a=1
while (@a<11)
Begin
Print replace (space (@a), ' ', ' * ')
Set @[email protected]+1
End

Right triangle Output results:

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 @[email protected]+ ' * '
Set @[email protected]+1
End
Print @c
Set @[email protected]+1
End

Square Output Result:

Rhombic:

DECLARE @a int,@b int
Set @a=1 set @b=15

if (@b%2!=1)
print ' numbers must all be odd '
Else
while (@a<[email protected])
Begin
if (@a%2=1)
Print space ((@[email protected)/2) +replace (Space (@a), ', ' * ') +space ((@[email protected])/2)
Set @[email protected]+1
End
Set @[email protected]
while (@a<[email protected])
Begin
if (@a%2=1)
Print space ((@[email protected)/2) +replace (Space (@a), ', ' * ') +space ((@[email protected])/2)
Set @[email protected]
if (@a<0)
Break
End

Diamond Output Results:

Trapezoidal:

DECLARE @a int,@b int
Set @a=7 set @b=21
if (@a%2=1)
while (@a<@b)
Begin
Print space ((@[email protected)/2) +replace (Space (@a), ', ' * ') +space ((@[email protected])/2)
Set @[email protected]+2
End

Trapezoid output Result:

Rectangular:
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 @[email protected]+ ' * '
Set @[email protected]+1
End
Print @c
Set @[email protected]+1
End

Rectangle Output Result:

Circular:

DECLARE @a int,@b int
Set @a=9 set @b=13

while (@a<[email protected])
Begin
if (@a%2=1)
Print space ((@[email protected)/2) +replace (Space (@a), ', ' * ') +space ((@[email protected])/2)
Set @[email protected]+1
End
Set @[email protected]
Begin
Print space ((@[email protected)/2) +replace (Space (@a), ', ' * ') +space ((@[email protected])/2)
End
while (@a<[email protected])
Begin
if (@a%2=1)
Print space ((@[email protected)/2) +replace (Space (@a), ', ' * ') +space ((@[email protected])/2)
Set @[email protected]
if (@a<10)
Break
End
Set @[email protected]
Begin
Print space ((@[email protected)/2) +replace (Space (@a), ', ' * ') +space ((@[email protected])/2)
End

Round output results:

Database Print Graphics