#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);
![](https://i-blog.csdnimg.cn/blog_migrate/04c2bf68d94e60ce441e403c84cc0fa9.png)
![](https://i-blog.csdnimg.cn/blog_migrate/ddbcac0f2c1f1d69471efc818fe6ad52.png)
#模糊查询
#模糊查询
#案例:想查‘诺基亚开头的所有商品
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_';