Home >Database >Mysql Tutorial >mysql利用中间值来暂时性存储值_MySQL
mysql利用中间值来暂时性存储值_MySQL
- WBOYOriginal
- 2016-06-01 13:37:301249browse
bitsCN.com
mysql利用中间值来暂时性存储值
mysql在查询数据的时候，有时候后面的语句会用到查出来的某个值，
这时候你会考虑怎么把这个值存起来，有两个方法来达到目的：
一。利用自定义值。二。利用中间表。
下面会分别说一下这两个方法如何调用：
1.如何把某张表的某个值存到自定义变量上
这个利用内存来存值
SQL代码
#定义@amax_price为常量值
mysql> set @amax_price=(select max(FScoreCount) from Tbl_User);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from Tbl_User where FScoreCount= @amax_price;
+---------+-----------+-------+------+-------------+------------+--------------+
--------+--------+---------+---------+---------+---------+--------------+-------
-------+---------+---------------------+------------+--------------+------------
---------+----------------+-------+
| FUserId | FQQ | FNick | FPwd | FScoreCount | FVoteCount | FInviteCount |
FBType | FLType | FEnable | FValue1 | FValue2 | FValue3 | FValue4 | FValue
5 | FValue6 | FTime | FDate | FIp | FLastLoginT
ime | FLastLoginDate | FMemo |
+---------+-----------+-------+------+-------------+------------+--------------+
--------+--------+---------+---------+---------+---------+--------------+-------
-------+---------+---------------------+------------+--------------+------------
---------+----------------+-------+
| 1 | 455342107 | | | 22 | 0 | 2 |
0 | 0 | 0 | 1 | 0 | | 2011-08-15_3 | 2011-0
8-10_2 | | 2011-08-10 10:02:40 | 2011-08-10 | 192.168.1.34 | 2011-08-16
14:30:18 | 2011-08-16 | |
+---------+-----------+-------+------+-------------+------------+--------------+
--------+--------+---------+---------+---------+---------+--------------+-------
-------+---------+---------------------+------------+--------------+------------
---------+----------------+-------+
2.如何把某张表的某个值存到临时表
SQL代码
#查出Tbl_User表中所有值
mysql> select FQQ,FScoreCount from Tbl_User;
+-----------+-------------+
| FQQ | FScoreCount |
+-----------+-------------+
| 455342107 | 22 |
| 24222 | 0 |
| 345333 | 5 |
| 664444 | 5 |
| 234324 | 0 |
| 137543511 | 5 |
| 519422206 | 5 |
| 234222 | 0 |
| 14234 | 5 |
| 1242354 | 5 |
| 111565 | 5 |
| 342323 | 0 |
| 234322 | 0 |
| 543244 | 0 |
+-----------+-------------+
14 rows in set (0.00 sec)
#把最大值存储到临时表tmp上
mysql> create table tmp select max(FScoreCount) as max_score from Tbl_User;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
#查出临时表结构
mysql> select * from tmp;
+-----------+
| max_score |
+-----------+
| 22 |
+-----------+
1 row in set (0.00 sec)
mysql> show tables;
+----------------------------------+
| Tables_in_DB_Nissansunny_2011_08 |
+----------------------------------+
| Tbl_Code |
| Tbl_Comment |
| Tbl_File |
| Tbl_InviteHistory |
| Tbl_LotteryCount |
| Tbl_LotteryHistory |
| Tbl_QQshow |
| Tbl_Score |
| Tbl_ScoreDetail |
| Tbl_User |
| Tbl_UserAward |
| Tbl_UserProfile |
| Tbl_VoteHistory |
| tmp |
+----------------------------------+
14 rows in set (0.00 sec)
两张方法各有差异：自定义变量是利用了内存，临时表利用了数据库空间。。哈哈，建议数据量大还是用临时表好一点。。。
bitsCN.com