SQL Server language functions and SQL programming

1. Mathematical function: Manipulate one data, return a result

--Go to upper limit: ceiling☆
Select Ceiling (price) from car

--to the lower limit: floor☆
Select Floor, from car

--abs Absolute Value

--pi (), pi, brackets don't add anything.

--round () rounding ☆
Select round (3.76, 0)

--SQRT () Open radical

--square () square

2. String functions

--Convert capital Upper ()

--Convert lowercase lower ()

--Go to space

LTrim () go to the left space RTrim () go to the right space

--space () plus a space, a few numbers on the inside add a few

--left () similar to substring

--line () length

--replace () Replace
Select replace (' A ', ' B ', ' C ') replaces B in a with C

--reverse () Flip string
Select reverse (' abc ') output results CBA

--String conversion function str
Select STR (A,B,C)

The first parameter is the value to be converted, the second value is the maximum length of the conversion process, and the third
One parameter is to keep a few decimals
Requires the following two parameters to be used together

--intercept string substring
Select substring (' ABCDEFG ', 2, 3)

Intercept from the second position, intercept three, index starting from 1

3, Time function (all the time to add single quotation marks)

--Get current system time getdate ();

Sysdatetime () Get server time

--Get Month day

--Determine if the date is correct isdate ()
Select IsDate () returns the bit type, False is 0,true is 1

--Add Time DateAdd
Select DATEADD (Time to add, time)

Year value, month value, day value

--return days of the week Datename
Select Datename (Weekday, time)
You can also check the day of the weekday change

--Query is the day of the week datepart

Select DATEPART (Weekday, time)

1. Index
Add an index, design the interface, right-click in front of any column--index/key--add

Primary key, Unique key, also known as index

2. View
The view is the virtual table created by our query.
When you need to reuse more complex SQL statements, you can save the view to make it easier for the next query
Use

Format:

Create VIEW View name
As
SQL statements

Because aliases in the same table cannot have duplicate names, you can change the same column name in SQL at run time.
Use as to change the name

SQL query statements, grouping, sorting, in, etc. cannot be used when creating views

3. SQL Programming

First, define the variable: DECLARE @ variable name data type

Example: declare @a int

Variable assignment: SET @ variable name = value

Set @a=5

Select @a--Print directly in the results box

Select @a=10--Do not output only assign values

Print @a--Printing in a message box

Example:
DECLARE @price decimal (10,4)
Select @price = AVG (price) from car
print ' average price for all cars: ' +cast (@price as varchar (20))

Cast for data type conversions

Second, the use of if...else, if there is no parenthesis, curly braces with begin end instead

If judging condition
Begin
The statement to execute
End
Else
Begin
The statement to execute
End

Iii. use of switch case in the database

Case
When condition 1 then the statement to execute
When condition 2 then the statement to execute
Else
The statement to execute
End

Iv. circulation
Notice the four elements of the loop

While loop condition
Begin
Loop body
Conditions change
End

Note: Do not add semicolons or commas at the end of each statement

SQL Server language functions and SQL programming