mysql---(入门语句12--where详解)

#where详解


use mugua;//打开库

show tables;

select goods_id,goods_name from goods;

select *from goods;//取所有行所有列
select *from goods where goods_id >20;//取部分行所有列
select  goods_id,goods_name from goods where goods_id >20;//取部分行所有列
select goods_id,goods_name,shop_price,market_price from goods;

select goods_id,goods_name,shop_price,market_price from goods 
where shop_price>3000;//把商品价格大于3000的取出来

select goods_id,goods_name,shop_price,market_price from goods 
where market_price-shop_price>200;//把市场价格大于商场价格200的取出来

select goods_id,goods_name,shop_price from goods where shop_price <=3000;//取出本店价格少于等于3000的
select goods_id,goods_name,shop_price from goods where shop_price =3000;//取出本店价格等于3000的
#in
#查不属于第3号栏目的所有商品
1.select goods_id,cat_id,goods_name from goods where cat_id <>3;
2.select goods_id,cat_id,goods_name from goods where cat_id !=3;
#in(值1,值,值3.。。。值n),等于值1-N任意一个,都行
#想查询第4个栏目或第5个栏目的商品
select goods_id cat_id,goods_name from goods where cat_id in (4,5);
#between
#between 值1 and 值2,表示在值1和值2之间
#取出商品价格在2000-3000之间的商品
select goods_id,goods_name,goods_price from goods where shop_price between 2000 and 3000;
#不用between and
#想买3000-5000之间的商品,但不用between and
select goods_id,goods_name,shop_price from goods where shop_price >=3000 and shop_price<=5000;
#or
#想买3000-5000之间的商品,或者500-1000的商品(同时满足)
select goods_id,goods_name,goods_price from goods where shop_price>=3000 and shop_price<=5000 
or shop_price>=500 and shop_price <=1000;
#not
#not 表示非
#not的用法:
#取出不属于第4,5栏目的商品
select goods_id,cat_id,goods_name from goods where cat_id !=4 and cat_id !=5;
#用not in来实现
select goods_id,cat_id,goods_name from goods where cat_id not in(4,5);


#模糊查询
#模糊查询
#案例:想查‘诺基亚开头的所有商品
like-->像
select goods_id,goods_name from goods where goods_name like '诺基亚';//取出来是空集

#统配符
%-->通佩任意字符
‘_’-->单个字符

select goods_id,goods_name from goods where goods_name like '诺基亚%';
#取‘诺基亚Nxxx’系列
select goods_id,goods_name from goods where goods_name like '诺基亚N_';


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值