Table joins, TSQL BASIC programming, and stored procedures

Table connections:
The data of multiple tables is queried one at a time, and the common display

Sub-query mode:
Select Column Name 1, column name 1, (select Column Name 2 from table Name 2 where table name 2. Common Column name = table Name 1. co-listing name) from table name 1--. meaning

Cartesian product : Kinda like a poor lift
Select Column Name 1, column name 2 from table name 1, table name 2--Take the data from the first table and compare it with all the data in the second table, the number of two tables multiplied
where table name 2. Common Column name = table Name 1. co-listing name

Join on:
Select Column Name 1, column name 2 from table name 1 join table name 2 on table Name 2. Common Column name = table name 1. Common column name--join after adding a table, on what is the condition on the following plus

There can be three modifiers before a join
INNER JOIN: Internal connection, is the default, all can be linked to the
Left join: The table in the main, the left table data display complete, and then stitching on the right side of the data
Right join: The table on the left is the main, the right table data is displayed intact, and then stitching the data

Note : You must add a where condition or on a relationship column that is followed by a condition, two tables

Vertical Connection : First display the above and then connect to the following
Union
Select Column Name 1 from table name 1
Union
Select Column Name 2 from table name 2

Note : The number of columns must be the same as the data type of the corresponding column in the vertical connected table

TSQL BASIC Programming:
Define variables:
Declare @a Int;--declare declares the meaning of the variable, the variable name must be added before
declare @b int;

Assignment value:
Set @a=10; --Assigned value
Select @b = 9; --Assigned value

Print in a message box:
Print @b--Map in message box

Map in result set:
Select @a;--map, display in result set

Branching statements:
declare @aa int;
Select @aa = 10;
declare @bb int;
Select @bb = 9;
declare @cc int;

If @aa > @bb--no parentheses, no spaces on the line
Begin--without curly braces, is replaced with Begin,end
Select @[email Protected][email protected];--set up to go this
End

Else
Begin
Select @[email Protected] @bb;
End

Select @cc;
Note : The execution is checked from the beginning

Circular statement notation:
declare @aaa int;
Select @aaa = 1; --Initial conditions

While @aaa <=10; --Cycle conditions
Begin
Select @aaa; --Loop body
Select @aaa +=1; -State change
End

stored Procedure : is the function

Definition: PS:
Create proc Jiafa--Creating a stored procedure
@a int,--parameter, can have multiple, separated by commas, the last one can not add commas
@b int
As--below the curly braces is the function body, with AS
declare @c int; --Function body
Select @[email Protected][email protected]
return @c
Go--Execute the next code, or you don't have to write

Save:
Stored procedures stored in the database for programmability

Modify:
Right-click on the table name of the system stored procedure to modify
Alter modify column, add column
Proc Stored Procedures
The dbo does not have a tube, the table name is basically preceded by
Change the function body and execute it again.

Use:
declare @ab int;
EXEC @ab =jiafa 5, 10; --exec execution function,
Select @ab;

Table joins, TSQL BASIC programming, and stored procedures