mysql基础②goods表练习

1.查询主键为32的商品

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

2.不属于第三栏目的所有商品

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

3.本店价格高于3000元的商品

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

4.本店商品价格低于或等于100元的商品

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

5.取出第4栏目或第11栏目的商品

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

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

6.取出价格100<=价格<=500的商品

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

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

7.取出不属于第3栏目且不属于第11栏目的商品

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

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

8.取出价格大于100且小于300，或者大于4000且小于5000的商品；（不要用between between的范围意思是大于等于而这里是大于）

select goods_id,goods_name,cat_id，shop_price from goods where shop_price>100 and shop_price<300 or shop_price>4000 and shop_price<5000;

9.取出第三个栏目下面价格<1000或>3000,并且点击量>5的系列商品

select goods_id,goods_name,cat_id,shop_price,click_count where cat_id=3 and shop_price<1000 or shop_price>3000 and click_count>5;

10取出名字以诺基亚开头的商品

select goods_id,goods_name,cat_id,shop_price from goods where goods_name like'诺基亚%';

11.取出名字为’诺基亚Nxx‘的手机

select goods_id,goods_name,shop-price from goods where goods_name like'诺基亚N__';

12取出名字不以诺基亚开头的商品

select goods_id,goods_name,cat_id,shop_price from goods where goods_name not like'诺基亚%';

13.取出第3个栏目下面价格在1000到3000之间，并且点击量>5'诺基亚'开头的商品

select goods_id,goods_name,cat_id,shop_price,click_count from goods where cat_id=3 and shop_price between 1000 and 3000 and click_count>5 and goods_name like'诺基亚%';

select goods_id,goods_name,cat_id,shop_price,click_count from goods where goods_id=3 and shop_price>=1000 and shop_price<=3000 and click_count>5 and goods_name like'诺基亚%'；

14，把goods表中商品名字为’诺基亚xxx‘的商品，改为'htcxxx’（大胆把列看为变量，调用函数substing(截取字符串)，concat(连接字符串)来处理

select goods_id,substring(goods_name,4) from goods where goods_name like'诺基亚%'；

select goods_id,concat('htc',substring(goods_name,4)) from goods where goods_name like'诺基亚%'；

update goods set goods_name=concat('htc',substing(goods_name,4)) from goods where goods_name like '诺基亚%'；

 

转载于:https://www.cnblogs.com/ctx1989/p/5909817.html