in MySQL, we can save a value or a query result in a user-defined variable, and then in the following statement in the application.
set defines variables;SET @var_name: = expr [, @var_name = expr] ....
SET @var_name = expr [, @var_name = expr] ....
Note:① here is ": =" or "=", but "=" has the same meaning in other statement statements, is easily confused, and sometimes goes wrong. It is
strongly recommended to use ": =". ② in the statement, can be used directly with @var_name = expr definition, do not advocate this, the equivalent of not declaring direct use.
Here are some examples: a simple definition that shows
mysql>SET @t1=1, @t2=2, @t3:=4;
mysql>SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+| @t1 | @t2 | @t3 | @t4: = @t1 + @t2 + @t3 |+------+------+------+---------- ----------+| 1 | 2 | 4 | 7 | +------+------+------+--------------------+
Someone use them for rank
set @pos :=  0; #这里用等号, it feels like a logical judgment, and the result is wrong
select @pos: [email protected]+1 as Rank,name From players ORDER by score DESC;
Print only the 100th users
SET@counter:=0;SELECTusers.* fromUsers having (@counter:=@counter+1)% -=0ORDER byuser_id; |
Save query result values value:
SELECT @total: = COUNT (*) from table_name; # Simicolon Split single statement SELECT table_name.id Count (*) as ' count ', count (*)/ (SELECT @total) As percent from Table_name,where 1=1group to Year (birthday) ORDER by year (birthday)
Note that the SQL statement above looks logical, but it is the same as the execution of the following statement (possibly MySQL internal optimization)
Select table_name.id count (*) as ' count ', count (*)/ (select
COUNT ( *)
from
table_name)
From Table_name,where 1=1group by year (birthday) ORDER by year (birthday)
Some other examples:
http://www.mysqldiary.com/user-defined-variables/
Questions:
The variable here can only hold a result value, how can I temporarily save a select out of the result set.
Of course, the simple way is to create a table/view, or a temporary table, there is a good way? Pending study.
Declare declares the variable and then assigns the value
DECLARE @var_name Var_type
Here's an example:
Example 1:
DECLARE @total INT DECLARE @total_distinct intselect @total: =count (lice_no) #using ": =" Notationfrom table_name; Select @total_distinct: =count (distinct lice_no) table_nameSELECT @total-@total_distinct
Example 2:
DECLARE @register_count int;declare @total_count INT; SELECT @register_count: = count (1) from T1 WHERE ID > 10;
SELECT @total_count: = count (1) from T1; SELECT (@register_count * @total_count) as Ratio2
DECLARE and SET differences:
- DECLARE must specify type, and set is not required
- The set defines a user-defined variable, which is the session sensitive; The variable declared by DECLARE is usually a local variable, and its effective interval is in the declared function or stored procedure.
- After the variable is not used, you can oh that select var_name: = Init_value. Set to the initial value. Convenient for later use, avoid the variable value in the next reference to the previous error caused by the old finger.
- Define global variables should be set global @var_name the latter set @ @GLOBAL. var_name
Note and Appendix: here the scope of the use of variables should be clear. Official Introduction Website: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
Reprint Please specify source: http://blog.csdn.net/acema/article/details/37114379