1000 Line MySQL Learning notes (ix)

/ * SQL Programming */------------------

--//local variable----------

 --Variable declaration declare var_name[,...] type [default value] This statement is used to declare a local variable. To provide a default value for a variable, include one of the defaults clauses. The value can be specified as an expression and does not need to be a constant. If there is no default clause, the initial value is null.    --assigns values to variables using set and select into statements. 
-NOTE: You can use global variables (user-defined variables) within a function
 
--//Global Variables----------

--Define, assign value
a Set statement can define and assign a value to a variable. 
 set @var = value; You can also use the SELECT INTO statement to initialize and assign values to variables. This requires the SELECT statement to return only one row, but it can be multiple fields, which means that multiple variables are assigned at the same time, and the number of variables needs to match the number of columns in the query. You can also think of an assignment statement as an expression that is done through select execution. In order to avoid = is treated as a relational operator at this time, use: = instead. (The Set statement can use = and: =). 

 Select @var: =;
 Select @v1: =id, @v2=name  from T1 limit 1;
 Select *  from Tbl_name where @var: =;

 Select into can assign data from a query in a table to a variable. 
  -| Select Max (height)  into @max_height from TB;  
 
--Custom variable name
 
to avoid a user-defined variable in a SELECT statement that conflicts with a system identifier (usually a field name), the user-defined variable uses @ as the start symbol before the variable name. 
 @var=ten; 

-After the variable is defined, it is valid throughout the session period (log on to exit)
 
--//Control Structure----------

--If statement
 
 if search_condition  then statement_list [ElseIf search_condition then statement_list]< /c4>
...
[Else statement_list]
 End If;

--Case Statement
 
  Case value  when [Compare-value]    and then result
 [When [Compare-value]  and then result ...]
[ELSE result]
END

--While Loop
 
 [Begin_label:]  while search_condition do statement_list
 End while [End_label];

-If you need to prematurely terminate the while loop within the loop, you need to use a label; the label needs to appear in pairs. 
   
--Exit cycle      
Exits the entire loop leave exits the current loop iterate by exiting the label determines which loop to exit

(not to be continued)

(Shocker Source: http://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html)

1000 Line MySQL Learning notes (ix)