SQL Server (vi)--indexing, view, and SQL programming

1. Index

Add an index, design the interface, right-click on any column--index/key--tap to add an index

2. 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

3.SQL programming

(1) define variable: DECLARE @ variable name data type

Example: declare @a int

(2) variable assignment: SET @ variable name = value

Example: Set @a=10

Set @a = 10--Assignment, not printing

Select @a; --Print in the result set

Print @a; --Print in a message box

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

Example 1, check the car table name contains the BMW two characters

Declare @name varchar (20)  @name = '  BMW  select * from car where Name like  % ' 

View Code

Example 2, check the average of all cars in the car table and output

 declare  @price 10,4 )  @price = avg (Price) from Car Span style= "color: #0000ff;" >print  ' +cast ( @price as varchar (

View Code

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

(3)if the use of the If ... else, if there is no parenthesis, curly braces are substituted with begin end

If judging condition

Begin

The statement to execute

End

Else

Begin

The statement to execute

End

Cases:

Declare@aIntDeclare@bIntDeclare@cIntSet@a=10;Set@b=5;If@a>@b begin Set @c = @a + @b; end Else begin Set @c = @a - @b; End Print @c               

View Code

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

(4) Switch case in C # morphing into database usage

Declare@ccnamevarchar20)Set@ccname=‘Bmw‘Select*From Carwhere NameLikeCase--The beginning of switch...caseWhen@ccname= '  BMW  then  % BMW% "when  @ccname = " Audi  ' then  "% Audi% ' else  "% ' end --switch...case              

View Code

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

(5) cycle: Notice the four elements of the loop

Declare@strvarchar20)Set@str=‘How are you doing‘Declare@iIntSet@i=1while @i<= 10 begin print  @str + cast (@i as varchar ( Span style= "color: #800000; Font-weight:bold; " >20set @i = @i +  1 end           

View Code

Whie (condition) {loop body}

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

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

SQL Server (vi)--indexing, view, and SQL programming