1、建立数据库，编码格式utf-8CREATE DATABASE demo1 CHARSET utf8;创建 goods 表格CREATE TABLE goods( goods_id INT PRIMARY KEY AUTO_INCREMENT, goods_name VARCHAR(10) UNIQUE NOT NULL, cat_id INT, brand_id INT, goods_sn CHAR(10), goods_number INT, shop_price DECIMAL(7,2), goods_desc VARCHAR(40));
3、并增加字段:click_count -- 点击量ALTER TABLE goods ADD click_count INT;UPDATE goods SET click_count=3 WHERE goods_id=3;SELECT*FROM goods;ALTER TABLE goods ADDINSERT INTO goods VALUES(1,'KD876',4,8,10,1388.00,'ECS000000');INSERT INTO goods VALUES(4,'诺基亚N85原装充电器',8,1,17,58.00,'ECS000004');INSERT INTO goods VALUES(3,'诺基亚原装5800耳机',8,1,24,68.00,'ECS000002');DELETE FROM goods WHERE goods_id=3;
5、3:本店价格高于300元的商品SELECT * FROM goods WHERE shop_pric髫潋啜缅e>300;1.4:本店价格低于或等于100元的商品SELECT *FROM goods WHERE shop_price<=100;1.5:取出第4栏目或第11栏目的商品(不许用OR)SELECT *FROM goods;SELECT *FROM goods WHERE cat_id=4 OR cat_id=11;SELECT * FROM goods WHERE cat_id IN (4,11);:取出100<=价格<=500的商品(不许用AND)SELECT *FROM goods WHERE shop_price>=100 AND shop_price<=500;SELECT *FROM goods WHERE shop_price BETWEEN 100 AND 500;1.7:取出不属于第3栏目且不属于第11栏目的商品(AND,或NOT IN分别实现)SELECT *FROM goods WHERE (cat_id !=3 AND cat_id !=11);SELECT *FROM goods WHERE cat_id NOT IN(3,11);取出价格大于100且小于300,或者大于4000且小于5000的商品()SELECT *FROM goods WHERE (shop_price>=100 AND shop_price<=300)OR(shop_price>1000 AND shop_price<2000);1.9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品SELECT *FROM goods WHERE cat_id=3 AND (shop_price<100 OR shop_price>1000) AND click_count>5 GROUP BY cat_id;SELECT*FROM goods;
