本文详细阐述了MySQL中商品查询、排序与限制操作的方法，包括如何通过where、group by、having、order by和limit等关键字实现高效的商品数据筛选与展示。
mysql> use test;
Database changed
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> #查询子句的作用过程：
mysql> #where group having order by limit
mysql> # where 查询出满足条件的行
mysql> #得到的不是结果集，还没有运算完毕，形成结果集
mysql> #拿到的数据，进行投影运算，进行列之间的计算及分组统计
mysql> #形成可能不是表中存在的结果，
mysql> #也可以使用having 对结果集再次进行筛选
mysql> #对结果集还可以进行排序 order by 和 limit操作
mysql> #
mysql> #对结果集排序，排序时针对最终结果集后，
mysql> #即排序是针对最终结果集的，
mysql> #即oder by 是放在where group by having 后面的
mysql> #顺序是不能乱的
mysql> #取出第四个栏目下的商品，并按价格由高到低排序
mysql> select goos_id,goods_name,shop_price from goods;
ERROR 1054 (42S22): Unknown column 'goos_id' in 'field list'
mysql> select goods_id,goods_name,shop_price from goods;
+----------+------------------------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------------------------+------------+
| 1 | KD876 | 1388.00 |
| 4 | 诺基亚N85原装充电器 | 58.00 |
| 3 | 诺基亚原装5800耳机 | 68.00 |
| 5 | 索爱原装M2卡读卡器 | 20.00 |
| 6 | 胜创KINGMAX内存卡 | 42.00 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | 100.00 |
| 8 | 飞利浦9@9v | 399.00 |
| 9 | 诺基亚E66 | 2298.00 |
| 10 | 索爱C702c | 1328.00 |
| 11 | 索爱C702c | 1300.00 |
| 12 | 摩托罗拉A810 | 983.00 |
| 13 | 诺基亚5320 XpressMusic | 1311.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 15 | 摩托罗拉A810 | 788.00 |
| 16 | 恒基伟业G101 | 823.33 |
| 17 | 夏新N7 | 2300.00 |
| 18 | 夏新T5 | 2878.00 |
| 19 | 三星SGH-F258 | 858.00 |
| 20 | 三星BC01 | 280.00 |
| 21 | 金立 A30 | 2000.00 |
| 22 | 多普达Touch HD | 5999.00 |
| 23 | 诺基亚N96 | 3700.00 |
| 24 | P806 | 2000.00 |
| 25 | 小灵通/固话50元充值卡 | 48.00 |
| 26 | 小灵通/固话20元充值卡 | 19.00 |
| 27 | 联通100元充值卡 | 95.00 |
| 28 | 联通50元充值卡 | 45.00 |
| 29 | 移动100元充值卡 | 90.00 |
| 30 | 移动20元充值卡 | 18.00 |
| 31 | 摩托罗拉E8 | 1337.00 |
| 32 | 诺基亚N85 | 3010.00 |
+----------+------------------------------+------------+
31 rows in set (0.04 sec)
mysql> select goods_id,goods_name,shop_price from goods where cat_id =4;
+----------+--------------+------------+
| goods_id | goods_name | shop_price |
+----------+--------------+------------+
| 1 | KD876 | 1388.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 18 | 夏新T5 | 2878.00 |
+----------+--------------+------------+
3 rows in set (0.06 sec)
mysql> select goods_id,goods_name,shop_price,is_delete from goods where cat_id =4;
ERROR 1054 (42S22): Unknown column 'is_delete' in 'field list'
mysql> select goods_id,goods_name,shop_price,is_del from goods where cat_id =4;
ERROR 1054 (42S22): Unknown column 'is_del' in 'field list'
mysql> #在实际商城上，goods_id =18 的没有显示？
mysql> #因为，商城实际表中，存在is_delete,is_on_sale字段
mysql> #表示是否删除，是否在出售，并不合适真正的删除
mysql> select goods_id,goods_name,shop_price
-> from goods where cat_id =4
-> order by shop_price desc;
+----------+--------------+------------+
| goods_id | goods_name | shop_price |
+----------+--------------+------------+
| 18 | 夏新T5 | 2878.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 1 | KD876 | 1388.00 |
+----------+--------------+------------+
3 rows in set (0.02 sec)
mysql> #排列的语法，
mysql> #order by 结果集中的列名 desc/asc
mysql> desc goods;
+--------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------------+------+-----+---------+----------------+
| goods_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| cat_id | smallint(5) unsigned | NO | | 0 | |
| goods_sn | varchar(60) | NO | | | |
| goods_name | varchar(120) | NO | | | |
| click_count | int(10) unsigned | NO | | 0 | |
| goods_number | smallint(5) unsigned | NO | | 0 | |
| market_price | decimal(10,2) unsigned | NO | | 0.00 | |
| shop_price | decimal(10,2) unsigned | NO | | 0.00 | |
| add_time | int(10) unsigned | NO | | 0 | |
| is_best | tinyint(1) unsigned | NO | | 0 | |
| is_new | tinyint(1) unsigned | NO | | 0 | |
| is_hot | tinyint(1) unsigned | NO | | 0 | |
+--------------+------------------------+------+-----+---------+----------------+
12 rows in set (0.05 sec)
mysql> # 按发布时间，升序排列
mysql> #即发布时间早的，时间戳小的靠前
mysql> select goods_id,goods_name,shop_price from
-> goods where cat_id=4
-> order by add_time desc;
+----------+--------------+------------+
| goods_id | goods_name | shop_price |
+----------+--------------+------------+
| 18 | 夏新T5 | 2878.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 1 | KD876 | 1388.00 |
+----------+--------------+------------+
3 rows in set (0.00 sec)
mysql> select goods_id,goods_name,shop_price from
-> goods where cat_id=4
-> order by add_time asc;
+----------+--------------+------------+
| goods_id | goods_name | shop_price |
+----------+--------------+------------+
| 1 | KD876 | 1388.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 18 | 夏新T5 | 2878.00 |
+----------+--------------+------------+
3 rows in set (0.00 sec)
mysql> #排序是针对结果集进行的排序
mysql> #按栏目排序，
mysql> select goods_id,goods_name.cat_id,shop_price
-> from goods
-> order by cat_id asc;
ERROR 1054 (42S22): Unknown column 'goods_name.cat_id' in 'field list'
mysql> select goods_id,goods_name,cat_id,shop_price
-> from goods
-> order by cat_id asc;
+----------+------------------------------+--------+------------+
| goods_id | goods_name | cat_id | shop_price |
+----------+------------------------------+--------+------------+
| 16 | 恒基伟业G101 | 2 | 823.33 |
| 32 | 诺基亚N85 | 3 | 3010.00 |
| 15 | 摩托罗拉A810 | 3 | 788.00 |
| 17 | 夏新N7 | 3 | 2300.00 |
| 19 | 三星SGH-F258 | 3 | 858.00 |
| 20 | 三星BC01 | 3 | 280.00 |
| 21 | 金立 A30 | 3 | 2000.00 |
| 22 | 多普达Touch HD | 3 | 5999.00 |
| 24 | P806 | 3 | 2000.00 |
| 31 | 摩托罗拉E8 | 3 | 1337.00 |
| 13 | 诺基亚5320 XpressMusic | 3 | 1311.00 |
| 12 | 摩托罗拉A810 | 3 | 983.00 |
| 11 | 索爱C702c | 3 | 1300.00 |
| 10 | 索爱C702c | 3 | 1328.00 |
| 9 | 诺基亚E66 | 3 | 2298.00 |
| 8 | 飞利浦9@9v | 3 | 399.00 |
| 1 | KD876 | 4 | 1388.00 |
| 18 | 夏新T5 | 4 | 2878.00 |
| 14 | 诺基亚5800XM | 4 | 2625.00 |
| 23 | 诺基亚N96 | 5 | 3700.00 |
| 3 | 诺基亚原装5800耳机 | 8 | 68.00 |
| 4 | 诺基亚N85原装充电器 | 8 | 58.00 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | 8 | 100.00 |
| 5 | 索爱原装M2卡读卡器 | 11 | 20.00 |
| 6 | 胜创KINGMAX内存卡 | 11 | 42.00 |
| 26 | 小灵通/固话20元充值卡 | 13 | 19.00 |
| 25 | 小灵通/固话50元充值卡 | 13 | 48.00 |
| 29 | 移动100元充值卡 | 14 | 90.00 |
| 30 | 移动20元充值卡 | 14 | 18.00 |
| 28 | 联通50元充值卡 | 15 | 45.00 |
| 27 | 联通100元充值卡 | 15 | 95.00 |
+----------+------------------------------+--------+------------+
31 rows in set (0.02 sec)
mysql> #栏目按照升序排列，同一个栏目下商品，按照商品价格排序
mysql> select goods_id,cat_id,shop_price
-> from goods
-> order by cat_id asc,shop_price desc;
+----------+--------+------------+
| goods_id | cat_id | shop_price |
+----------+--------+------------+
| 16 | 2 | 823.33 |
| 22 | 3 | 5999.00 |
| 32 | 3 | 3010.00 |
| 17 | 3 | 2300.00 |
| 9 | 3 | 2298.00 |
| 21 | 3 | 2000.00 |
| 24 | 3 | 2000.00 |
| 31 | 3 | 1337.00 |
| 10 | 3 | 1328.00 |
| 13 | 3 | 1311.00 |
| 11 | 3 | 1300.00 |
| 12 | 3 | 983.00 |
| 19 | 3 | 858.00 |
| 15 | 3 | 788.00 |
| 8 | 3 | 399.00 |
| 20 | 3 | 280.00 |
| 18 | 4 | 2878.00 |
| 14 | 4 | 2625.00 |
| 1 | 4 | 1388.00 |
| 23 | 5 | 3700.00 |
| 7 | 8 | 100.00 |
| 3 | 8 | 68.00 |
| 4 | 8 | 58.00 |
| 6 | 11 | 42.00 |
| 5 | 11 | 20.00 |
| 25 | 13 | 48.00 |
| 26 | 13 | 19.00 |
| 29 | 14 | 90.00 |
| 30 | 14 | 18.00 |
| 27 | 15 | 95.00 |
| 28 | 15 | 45.00 |
+----------+--------+------------+
31 rows in set (0.01 sec)
mysql> #多个字段进行排序，很容易，
mysql> #order by 列1asc/desc，列2asc/desc 。。。。。
mysql> #多字段排序，先区分第一个列，再区分。。。
mysql>
mysql> #limit 限制条目
mysql> #将3号栏目，按价格排序，只取出前十条
mysql> select goods_id,cat_id,shop_price
-> from goods
-> where cat_id=3
-> order by shop_price asc
-> limit 10;
+----------+--------+------------+
| goods_id | cat_id | shop_price |
+----------+--------+------------+
| 20 | 3 | 280.00 |
| 8 | 3 | 399.00 |
| 15 | 3 | 788.00 |
| 19 | 3 | 858.00 |
| 12 | 3 | 983.00 |
| 11 | 3 | 1300.00 |
| 13 | 3 | 1311.00 |
| 10 | 3 | 1328.00 |
| 31 | 3 | 1337.00 |
| 21 | 3 | 2000.00 |
+----------+--------+------------+
10 rows in set (0.00 sec)
mysql> select goods_id,cat_id,shop_price
-> from goods
-> where cat_id=3
-> \c
mysql> select goods_id,cat_id,shop_price,goods_name
-> from goods
-> where cat_id=3
-> order by shop_price asc
-> limit 10;
+----------+--------+------------+------------------------+
| goods_id | cat_id | shop_price | goods_name |
+----------+--------+------------+------------------------+
| 20 | 3 | 280.00 | 三星BC01 |
| 8 | 3 | 399.00 | 飞利浦9@9v |
| 15 | 3 | 788.00 | 摩托罗拉A810 |
| 19 | 3 | 858.00 | 三星SGH-F258 |
| 12 | 3 | 983.00 | 摩托罗拉A810 |
| 11 | 3 | 1300.00 | 索爱C702c |
| 13 | 3 | 1311.00 | 诺基亚5320 XpressMusic |
| 10 | 3 | 1328.00 | 索爱C702c |
| 31 | 3 | 1337.00 | 摩托罗拉E8 |
| 21 | 3 | 2000.00 | 金立 A30 |
+----------+--------+------------+------------------------+
10 rows in set (0.00 sec)
mysql> #limit 如何限制条目数量
mysql> #两个参数，
mysql> #第一个参数，偏移量 默认为0
mysql> #第二个参数，取出条目，
mysql> #如果第一个参数不写，相当于limit 0，N
mysql> #limit操作就是从偏移量开始，取出N条
mysql> #取出商品中价格最贵的前三条
mysql> select goods_id,good_name,shop_price
-> from goods
-> order by shop_price asc
-> limit 3;
ERROR 1054 (42S22): Unknown column 'good_name' in 'field list'
mysql> select goods_id,goods_name,shop_price
-> from goods
-> order by shop_price asc
-> limit 3;
+----------+-----------------------+------------+
| goods_id | goods_name | shop_price |
+----------+-----------------------+------------+
| 30 | 移动20元充值卡 | 18.00 |
| 26 | 小灵通/固话20元充值卡 | 19.00 |
| 5 | 索爱原装M2卡读卡器 | 20.00 |
+----------+-----------------------+------------+
3 rows in set (0.00 sec)
mysql> select goods_id,good_name,shop_price
-> from goods
-> limit 3;
ERROR 1054 (42S22): Unknown column 'good_name' in 'field list'
mysql> select goods_id,goods_name,shop_price
->
-> from goods
-> order by shop_price asc
-> \c
mysql> select goods_id,goods_name,shop_price
-> from goods order by shop_price
-> desc limit 3;
+----------+----------------+------------+
| goods_id | goods_name | shop_price |
+----------+----------------+------------+
| 22 | 多普达Touch HD | 5999.00 |
| 23 | 诺基亚N96 | 3700.00 |
| 32 | 诺基亚N85 | 3010.00 |
+----------+----------------+------------+
3 rows in set (0.00 sec)
mysql> #商品最贵的第三名到第五名
mysql> select goods_id,goods_name,shop_price
-> from goods order by shop_price
-> desc limit 3，5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3，5' at line 3
mysql> select goods_id,goods_name,shop_price
-> from goods order by shop_price
-> desc limit 3,5;
+----------+--------------+------------+
| goods_id | goods_name | shop_price |
+----------+--------------+------------+
| 18 | 夏新T5 | 2878.00 |
| 14 | 诺基亚5800XM | 2625.00 |
| 17 | 夏新N7 | 2300.00 |
| 9 | 诺基亚E66 | 2298.00 |
| 21 | 金立 A30 | 2000.00 |
+----------+--------------+------------+
5 rows in set (0.01 sec)
mysql> select goods_id,goods_name,shop_price
-> from goods order by shop_price
-> desc limit 2,3;
+----------+--------------+------------+
| goods_id | goods_name | shop_price |
+----------+--------------+------------+
| 32 | 诺基亚N85 | 3010.00 |
| 18 | 夏新T5 | 2878.00 |
| 14 | 诺基亚5800XM | 2625.00 |
+----------+--------------+------------+
3 rows in set (0.00 sec)
mysql> #偏移量是跳过的行数，要取的数减一
mysql> #N是实际取出的行数
mysql>
mysql>
mysql> #取出价格最高的一条商品：
mysql> select goods_id,goods_name,shop_price
-> from goods order by shop_price
-> desc limit 1;
+----------+----------------+------------+
| goods_id | goods_name | shop_price |
+----------+----------------+------------+
| 22 | 多普达Touch HD | 5999.00 |
+----------+----------------+------------+
1 row in set (0.01 sec)
mysql> 按商品价格降序排列，
-> 再取出最高的那一条
-> \c
mysql> #查询出每个栏目下id号最大的商品一条商品
mysql> select goods_id,cat_id,goods_name from goods group by cat_id
-> order by goods_id desc limit 1;
+----------+--------+-----------------+
| goods_id | cat_id | goods_name |
+----------+--------+-----------------+
| 29 | 14 | 移动100元充值卡 |
+----------+--------+-----------------+
1 row in set (0.03 sec)
mysql> select goods_id,cat_id,goods_name from goods group by cat_id
-> ;
+----------+--------+-----------------------+
| goods_id | cat_id | goods_name |
+----------+--------+-----------------------+
| 16 | 2 | 恒基伟业G101 |
| 8 | 3 | 飞利浦9@9v |
| 1 | 4 | KD876 |
| 23 | 5 | 诺基亚N96 |
| 4 | 8 | 诺基亚N85原装充电器 |
| 5 | 11 | 索爱原装M2卡读卡器 |
| 25 | 13 | 小灵通/固话50元充值卡 |
| 29 | 14 | 移动100元充值卡 |
| 27 | 15 | 联通100元充值卡 |
+----------+--------+-----------------------+
9 rows in set (0.00 sec)
转载于:https://blog.51cto.com/duguodong/1387713
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
