010查询练习 – 重要

查询知识

注:以下查询基于ecshop网站的商品表(goods)

在练习时可以只取部分列,方便查看.

1: 基础查询 where的练习:

查出满足以下条件的商品

1.1:主键为32的商品

select goods_id,goods_name,shop_price from goods where goods_id=32;

1.2:不属第3栏目的所有商品

select goods_id,cat_id,goods_name,shop_price from goods where cat_id!=3;

1.3:本店价格高于3000元的商品

select goods_id,cat_id,goods_name,shop_price from goods where shop_price >3000;

1.4:本店价格低于或等于100元的商品

select goods_id,cat_id,goods_name,shop_price from goods where shop_price <=100;

1.5:取出第4栏目或第11栏目的商品(不许用or)

select goods_id,cat_id,goods_name,shop_price from goods where cat_id in (4,11);

select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 or cat_id=11;

select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 || cat_id=11;

1.6:取出100<=价格<=500的商品(不许用and)

select goods_id,cat_id,goods_name,shop_price from goods where shop_price between 100 and 500;

select goods_id,cat_id,goods_name,shop_price from goods where shop_price>=100 and shop_price<=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__';

weinxin
我的微信
把最实用的经验,分享给最需要的读者,希望每一位来访的朋友都能有所收获!
avatar
小鸟云服务器

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: