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));
2、创建 category 栏目表CREATE TABLE category( cat_id INT PRIMARY KEY AUTO_INCREMENT, cat_name VARCHAR(20), parent_id INT);删除goods表的goods_desc 字段,及货号字段ALTER TABLE goods DROP goods_desc;ALTER TABLE goods DROP goods_sn;ALTER TABLE goods ADD good_sn CHAR(9);
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;
4、| 索爱骒貉缭塄原装M2卡读卡器 11 7 ECS000005 8 20.00 3INSERT INTO goods( goods_id, goods_name,cat_id,brand_id,g泠贾高框oods_number,shop_price,good_sn,click_count )VALUES(5,'索爱原装M2卡读卡器',11,7,8,20.00,'ECS000005',3);INSERT INTO goods(goods_name,cat_id,brand_id,goods_number,shop_price,good_sn,click_count )VALUES('胜创KINGMAX内存卡',11,0,15,42.00,'ECS000006',0);INSERT INTO goods(goods_name,cat_id,brand_id,goods_number,shop_price,good_sn,click_count )VALUES('诺基亚N85原装立体声耳机HS-82 ',8,1,20,100.00,'ECS000007',0),('飞利浦9@9v',3,4,17,399.00,'ECS000008',9),(' 诺基亚E66 ',3,1,13,2298.00,'ECS000009',20);INSERT INTO goods(goods_name,cat_id,brand_id,goods_number,shop_price,good_sn,click_count )VALUES('索爱C702c ',3,7,7,1328.00,'ECS000010',11),('索爱C702',3,4,0,1300.00,'ECS000011',0),('摩托罗拉A810',3,2,8,983.00,'ECS000012',14);1.1:主键为3的商品SELECT * FROM goods WHERE goods_id=3;1.2:不属第3栏目的所有商品SELECT*FROM goods;SELECT *FROM goods WHERE NOT cat_id=3;SELECT *FROM goods WHERE cat_id <>3;SELECT *FROM goods WHERE cat_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;
6、11:取出名字以"诺基亚"开头的商品SELECT *FROM goods WHERE goods_name LIKE '诺基亚%';1.12:取出名字为"诺基亚Nxx"的手机SELECT *FROM goods WHERE goods_name LIKE '诺基亚N__';1.13:取出名字不以"诺基亚"开头的商品SELECT *FROM goods WHERE goods_name NOT LIKE '诺基亚%';1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量>5 "诺基亚"开头的系列商品CREATE TABLE main(num INT);INSERT INTO main VALUES(3),(12),(15),(25),(23),(29),(33);把num值处于[20,29]之间,改为20UPDATE main SET num=20 WHERE num BETWEEN 20 AND 29;SELECT*FROM main;
