Mysql学习笔记二——基础查询where练习

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; 

select goods_id,cat_id,goods_name,shop_price  from ecs_goods 

     where cat_id<>sle3;
 

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);
select goods_id , goods_name from goods where cat_id=4 or cat_id=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;

select goods_id , goods_name 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 , goods_name,shop_price from 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 , goods_name,shop_price,cat_id from 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 ‘诺基亚%’;

1.15 一道面试题
有如下表和数组
把num值处于[20,29]之间,改为20
num值处于[30,39]之间的,改为30

mian表
+——+
| num |
+——+
| 3 |
| 12 |
| 15 |
| 25 |
| 23 |
| 29 |
| 34 |
| 37 |
| 32 |
| 45 |
| 48 |
| 52 |
+——+
update mian set num=20 where num between 20 and 29;

update mian set num=30 where num between 30 and 39;

【update mian set num=floor(num/10)*10 where num between 20 and 39;】

floor（）舍弃小数

1.16 练习题: 

把good表中商品名为'诺基亚xxxx'的商品,改为'HTCxxxx', 

提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 . 

substring(),concat() 

select goods_id ,concat(‘HTC’,substring(goods_name,4)) from goods where goods_name like ‘诺基亚%’;

update goods set goods_name = concat(‘HTC’,substring(goods_name,4)) where goods_name like ‘诺基亚%’;