SQL Programming Statements

View

The view is the virtual table we've queried.
Creating views: Create View name
As
SQL query statements, grouping, sorting, in and so on can not be written
View Usage: SELECT * from view name

SQL programming

Define variable: DECLARE @ variable name data type declare @a int
Variable assignment: SET @ variable name = value Set @a=10

Select @a--Print directly in the results box
Set @a = 10--is also assigned, does not print

Select @a; --Print in the result set
Print @a; --Print in a message box

-----------------------------------------------------
Check the car table with the name of the BMW two words
DECLARE @name varchar (20)
Set @name = ' BMW '
SELECT * from car where Name like '% ' [email protected]+ '% '

Check the average of all cars in the car table and output
DECLARE @price decimal (10,4)
Select @price = AVG (price) from Car
print ' average price for all cars: ' +cast (@price as varchar (20))

-----------------------------------------------------

If ... else usage, if there is no parenthesis, curly braces are replaced with begin end

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

-----------------------------------------------------
DECLARE @a int
DECLARE @b int
DECLARE @c int

Set @a = 10;
Set @b = 5;

If @a>@b
Begin
Set @c = @a + @b;
End
Else
Begin
Set @c = @a-@b;
End
Print @c
-----------------------------------------------------
Switch case in C # morphing into database usage

DECLARE @ccname varchar (20)
Set @ccname = ' BMW '
SELECT * from Car where Name is like

The beginning of case--switch...case
When @ccname = ' bmw ' Then '% BMW '
When @ccname = ' Audi ' Then '% Audi '
Else '% '
End of--switch...case

-----------------------------------------------------
Cycle:
Notice the four elements of the loop

DECLARE @str varchar (20)
Set @str = ' Hello '
DECLARE @i int
Set @i = 1

While @i<=10
Begin
Print @str + cast (@i as varchar (20))
Set @i = @i + 1
End

Whie (condition)
{
Loop body
}

-----------------------------------------------------
Note: Do not write a semicolon or comma after the statement ends

SQL Programming Statements