1、建立数据库，编码格式utf-8CREATE DATAB钽吟篑瑜ASE demo1 CHARSET utf8;创建 goods 表格CREAT呶蓟鹭毵E 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));创建 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);并增加字段: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 | 索爱原装M2卡读卡器 11 7 ECS000005 8 20.00 3INSERT INTO goods( goods_id, goods_name,cat_id,brand_id,goods_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;1.3:本店价格高于300元的商品SELECT * FROM goods WHERE shop_price>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;1.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;2.1:查出最贵的商品的价格SELECT MAX(shop_price) a FROM goods;SELECT *FROM goods WHERE shop_price IN(SELECT MAX(shop_price) a FROM goods);2.2:查出最大(最新)的商品编号SELECT MAX(goods_id) NEW FROM goods;SELECT *FROM goods WHERE goods_id IN (SELECT MAX(goods_id) FROM goods);2.3:查出最便宜的商品的价格SELECT *FROM goods WHERE shop_price IN(SELECT MIN(shop_price) a FROM goods);2.5:查询该店所有商品的库存总量SELECT SUM(goods_number) FROM goods;SELECT SUM(goods_number) FROM goods;2.6:查询所有商品的平均价SELECT AVG(shop_price)FROM goods;
2、7:查询该店一共有多少种商品SELECT COUNT(*)FROM goods;每个栏目下最贵的商品SELECT * FROM goods WHERE shop_price IN(SELECT MAX(shop_price)FROM goods GROUP BY cat_id);每个栏目下平均价SELECT cat_id,AVG(shop_price)FROM goods GROUP BY cat_id;SELECT * FROM goods;SELECT * FROM category;3.2:查询每个商品所积压的货款(提示:库存*单价)SELECT goods_id,goods_name,(goods_number*shop_price) monery FROM goods;3.3:查询该店积压的总货款SELECT SUM(goods_number*shop_price) FROM goods;3.4:查询该店每个栏目下面积压的货款.SELECT cat_id,(goods_number*shop_price) monery FROM goods GROUP BY cat_id;3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款SELECT cat_id,SUM(goods_number*shop_price) AS money FROM goods GROUP BY cat_id HAVING money>20000;
3、1:按价格由高到低排序SELECT *FROM goods ORDER BY shop_price ASC; SELECT *FROM goods ORDER BY shop_price DESC; 4.2:按发布时间由早到晚排序SELECT*FROM goods ORDER BY goods_id DESC;4.3:接栏目由低到高排序,栏目内部按价格由高到低排序SELECT * FROM goods ORDER BY cat_id ASC,shop_price DESC;4.4:取出价格最高的前三名商品SELECT*FROM goods ORDER BY shop_price DESC LIMIT 0,3;SELECT*FROM goods ORDER BY shop_price DESC LIMIT 0,3;4.5:取出点击量前三名到前5名的商品SELECT * FROM goods ORDER BY click_count DESC LIMIT 2,3;5.1:取出所有商品的商品名,栏目名,价格SELECT* FROM category;DESC category;SELECT *FROM goods;
4、CREATE TABLE matches猾诮沓靥( matchID INT PRIMARY KEY, hostTeamID INT , guestTeamID INT, matchResult VARCHAR(20), matchTime DATE);CREATE TABLE team( teamID INT PRIMARY KEY, teamName VARCHAR(20));INSERT INTO matches VALUES(1,1,2,'2:0','2006-05-21');INSERT INTO matches VALUES(2,2,3,'1:2','2006-06-21');INSERT INTO matches VALUES(3,3,1,'2:5','2006-06-25');INSERT INTO matches VALUES(4,2,1,'3:2','2006-07-21');SELECT matchID MID,hostTeamID hid,guestTeamID gid,matchResult mres,matchTime matime FROM matches;CREATE TABLE m( MID INT PRIMARY KEY, hid INT , gid INT, mres VARCHAR(20), matime DATE);INSERT INTO m VALUES(1,1,2,'2:0','2006-05-21');INSERT INTO m VALUES(2,2,3,'1:2','2006-06-21');INSERT INTO m VALUES(3,3,1,'2:5','2006-06-25');INSERT INTO m VALUES(4,2,1,'3:2','2006-07-21');CREATE TABLE t( tid INT PRIMARY KEY AUTO_INCREMENT, tname VARCHAR(20));INSERT INTO t(tname)VALUES('国安');INSERT INTO t(tname)VALUES('申花');INSERT INTO t(tname)VALUES('传智联队');SELECT*FROM m;SELECT*FROM t;CREATE TABLE ta( id CHAR(1), num INT);CREATE TABLE tb( id CHAR(1), num INT);
5、INSERT INTO ta VALUES('a',5),('b',10),('c',15),('d',10);INSERT INTO ta VALUES('b',5),('c',15),('d',20),('e',99);SELECT * FROM ta UNION ALL SELECT*FROM tb ;SELECT id,SUM(num) FROM(SELECT*FROM ta UNION ALL SELECT*FROM tb) temp GROUP BY id;SELECT id,SUM(num) FROM (SELECT*FROM ta UNION ALL SELECT*FROM tb)temp GROUP BY id;
6、子查询查询出最新一行商品(以商品编号最大为最新,用子查询实现)SELECT * FROM goods WHERE goods_id IN(SELECT MAX(goods_id)FROM goods );7.3:用WHERE型子查询把ecs_goods表中的每个栏目下面最新的商品取出来SELECT *FROM goods WHERE goods_id IN(SELECT MAX(goods_id)FROM goods GROUP BY cat_id) ;SELECT *FROM goods;SELECT* FROM goods WHERE goods_id IN(SELECT MAX(goods_id )FROM goods GROUP BY cat_id);7.4:用FROM型子查询把ecs_goods表中的每个栏目下面最新的商品取出来SELECT * FROM(SELECT * FROM goods GROUP BY cat_id DESC)temp GROUP BY cat_id ;
