 |
|||||||
|---|---|---|---|---|---|---|---|
| goods_id | goods_name | cat_id | brand_id | goods_sn | goods_number | shop_price | click_count |
| 1 | KD876 | 4 | 8 | ECS000000 | 10 | 1388.00 | 7 |
| 4 | 诺基亚N85原装充电器 | 8 | 1 | ECS000004 | 17 | 58.00 | 0 |
| 3 | 诺基亚原装5800耳机 | 8 | 1 | ECS000002 | 24 | 68.00 | 3 |
| 5 | 索爱原装M2卡读卡器 | 11 | 7 | ECS000005 | 8 | 20.00 | 3 |
| 6 | 胜创KINGMAX内存卡 | 11 | 0 | ECS000006 | 15 | 42.00 | 0 |
| 7 | 诺基亚立体声耳机 | 8 | 1 | ECS000007 | 20 | 100.00 | 0 |
| 8 | 飞利浦9@9v | 3 | 4 | ECS000008 | 17 | 399.00 | 9 |
| 9 | 诺基亚E66 | 3 | 1 | ECS000009 | 13 | 2298.00 | 20 |
| 10 | 索爱C702c | 3 | 7 | ECS000010 | 7 | 1328.00 | 11 |
| 11 | 索爱C702c | 3 | 7 | ECS000011 | 1 | 1300.00 | 0 |
| 12 | 摩托罗拉A810 | 3 | 2 | ECS000012 | 8 | 983.00 | 14 |
| 13 | 诺基亚5320 XpressMusic | 3 | 1 | ECS000013 | 8 | 1311.00 | 13 |
| 14 | 诺基亚5800XM | 4 | 1 | ECS000014 | 4 | 2625.00 | 6 |
| 15 | 摩托罗拉A810 | 3 | 2 | ECS000015 | 3 | 788.00 | 8 |
| 16 | 恒基伟业G101 | 2 | 11 | ECS000016 | 0 | 823.33 | 3 |
| 17 | 夏新N7 | 3 | 5 | ECS000017 | 1 | 2300.00 | 2 |
| 18 | 夏新T5 | 4 | 5 | ECS000018 | 1 | 2878.00 | 0 |
| 19 | 三星SGH-F258 | 3 | 6 | ECS000019 | 0 | 858.00 | 7 |
| 20 | 三星BC01 | 3 | 6 | ECS000020 | 13 | 280.00 | 14 |
| 21 | 金立 A30 | 3 | 10 | ECS000021 | 40 | 2000.00 | 4 |
| 22 | 多普达Touch HD | 3 | 3 | ECS000022 | 0 | 5999.00 | 15 |
| 23 | 诺基亚N96 | 5 | 1 | ECS000023 | 8 | 3700.00 | 17 |
| 24 | P806 | 3 | 9 | ECS000024 | 148 | 2000.00 | 36 |
| 25 | 小灵通固话50元充值卡 | 13 | 0 | ECS000025 | 2 | 48.00 | 0 |
| 26 | 小灵通固话20元充值卡 | 13 | 0 | ECS000026 | 2 | 19.00 | 0 |
| 27 | 联通100元充值卡 | 15 | 0 | ECS000027 | 2 | 95.00 | 0 |
| 28 | 联通50元充值卡 | 15 | 0 | ECS000028 | 0 | 45.00 | 0 |
| 29 | 移动100元充值卡 | 14 | 0 | ECS000029 | 0 | 90.00 | 0 |
| 30 | 移动20元充值卡 | 14 | 0 | ECS000030 | 9 | 18.00 | 1 |
| 31 | 摩托罗拉E8 | 3 | 2 | ECS000031 | 1 | 1337.00 | 5 |
| 32 | 诺基亚N85 | 3 | 1 | ECS000032 | 1 | 3010.00 | 9 |
1.基础查询（where的练习）
查出满足以下条件的商品：
1.1:主键为32的商品：
select goods_id,goods_name,shop_price from ecs_goods where goods_id = 32;1.2:不属第3栏目的所有商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id! = 3;1.3:本店价格高于3000元的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price > 3000;1.4:本店价格低于或等于100元的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price <= 100;1.5:取出第4栏目或第11栏目的商品(不许用or)
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id in (4,11);1.6:取出100<=价格<=500的商品(不许用and)
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price between 100 and 500;1.7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id!=3 and cat_id!=11;
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id not in (3,11);1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price>100 and shop_price <300 or shop_price >4000 and shop_price <5000;1.9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where cat_id=3 and (shop_price <1000 or shop_price>3000) and click_count>5;1.10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where cat_id in (2,3,4,5);1.11:取出名字以”诺基亚”开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like '诺基亚%';1.12:取出名字为”诺基亚Nxx”的手机
select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like '诺基亚N__';1.13:取出名字不以”诺基亚”开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goos where goods_name not like '诺基亚%';1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量>5 “诺基亚”开头的系列商品
-- 方法一
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id=3 and shop_price>1000 and shop_price <3000 and click_count>5 and goods_name like '诺基亚%';
-- 方法二
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price between 1000 and 3000 and cat_id=3 and click_count>5 and goods_name like '诺基亚%';2.分组查询group
2.1:查出最贵的商品的价格
select max(shop_price) from ecs_goods;2.2:查出最大(最新)的商品编号
select max(goods_id) from ecs_goods;2.3:查出最便宜的商品的价格
select min(shop_price) from ecs_goods;2.4:查出最旧(最小)的商品编号
select min(goods_id) from ecs_goods;2.5:查询该店所有商品的库存总量
select sum(goods_number) from ecs_goods;2.6:查询所有商品的平均价
select avg(shop_price) from ecs_goods;2.7:查询该店一共有多少种商品
select count(*) from ecs_goods;2.8:查询每个栏目下面
最贵商品价格 最低商品价格 商品平均价格 商品库存量 商品种类 提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)
select cat_id,max(shop_price) from ecs_goods group by cat_id;3.having与group综合运用查询
3.1:查询该店的商品比市场价所节省的价格
select goods_id,goods_name,market_price-shop_price as j from ecs_goods;3.2:查询每个商品所积压的货款(提示:库存*单价)
select goods_id,goods_name,goods_number*shop_price from ecs_goods3.3:查询该店积压的总货款
select sum(goods_number*shop_price) from ecs_goods;3.4:查询该店每个栏目下面积压的货款.
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;3.5:查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)
select goods_id,goods_name,market_price-shop_price as k from ecs_goods where market_price-shop_price > 200;
select goods_id,goods_name,market_price-shop_price as k from ecs_goods having k > 200;3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id having k>200004.order by与limit查询
4.1:按价格由高到低排序
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;4.2:按发布时间由早到晚排序
select goods_id,goods_name,add_time from ecs_goods order by add_time;4.3:接栏目由低到高排序,栏目内部按价格由高到低排序
select goods_id,cat_id,goods_name,shop_price from ecs_goods order by cat_id ,shop_price desc;4.4:取出价格最高的前三名商品
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;4.5:取出点击量前三名到前5名的商品
select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;5.连接查询
5.1:取出所有商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from
ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id;5.2:取出第4个栏目下的商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from
ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id where ecs_goods.cat_id = 4;5.3:取出第4个栏目下的商品的商品名,栏目名,与品牌名
select goods_name,cat_name,brand_name from
ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id left join ecs_brand on ecs_goods.brand_id=ecs_brand.brand_id where ecs_goods.cat_id = 4;6.子查询
6.1:查询出最新一行商品(以商品编号最大为最新,用子查询实现)
select goods_id,goods_name from ecs_goods where goods_id =(select max(goods_id) from ecs_goods);6.2:用where型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
select goods_id,goods_name,cat_id from ecs_goods where goods_id in (select max(goods_id) from ecs_goods group by cat_id);6.3:用from型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;6.4:用exists型子查询,查出所有有商品的栏目
select * from category where exists (select * from goods where goods.cat_id=category.cat_id);