order by ,limit 和where子查询的使用
order by:
order by 列名1，[列名2],[列名3]...(结果先按列1进行排序，在列1的相同的情况下，再按照列2的排序，以此类推)。
mysql> select goods_id,goods_name,shop_price from goods -> group by cat_id,shop_price; +----------+------------------------------+------------+ | goods_id | goods_name | shop_price | +----------+------------------------------+------------+ | 16 | 恒基伟业G101 | 823.33 | | 20 | 三星BC01 | 280.00 | | 8 | 飞利浦9@9v | 399.00 | | 15 | 摩托罗拉A810 | 788.00 | | 19 | 三星SGH-F258 | 858.00 | | 12 | 摩托罗拉A810 | 983.00 | | 11 | 索爱C702c | 1300.00 | | 13 | 诺基亚5320 XpressMusic | 1311.00 | | 10 | 索爱C702c | 1328.00 | | 31 | 摩托罗拉E8 | 1337.00 | | 21 | 金立 A30 | 2000.00 | | 9 | 诺基亚E66 | 2298.00 | | 17 | 夏新N7 | 2300.00 | | 32 | 魅族mx4 | 3010.00 |
limit的用法:
limit的用法是限制显示表的行数：limit A,B。A表示是从第几行开始，B是显示几条数据，但是计算机内的第一条是从0开始算起的
所以limit 0,1表示第一条数据（0可以省略）
mysql> select goods_id,goods_name from goods limit 0,1; +----------+------------+ | goods_id | goods_name | +----------+------------+ | 1 | KD876 | +----------+------------+ 1 row in set (0.00 sec)
第三条到第五条数据:
mysql> select goods_id,goods_name from goods order by goods_id limit 2,3; +----------+---------------------+ | goods_id | goods_name | +----------+---------------------+ | 4 | 诺基亚N85原装充电器 | | 5 | 索爱原装M2卡读卡器 | | 6 | 胜创KINGMAX内存卡 | +----------+---------------------+ 3 rows in set (0.00 sec)
where子查询:
因为我们可以把查询出的数据表看成是一个临时表，把查询结果看成是变量，所以我们可以把用select查询的结果看成是变量
mysql> select goods_id,goods_name,goods_name from goods -> where goods_id =(select max(goods_id) from goods); +----------+------------+------------+ | goods_id | goods_name | goods_name | +----------+------------+------------+ | 32 | 魅族mx4 | 魅族mx4 | +----------+------------+------------+ 1 row in set (0.00 sec)