SQL多表查询

USE h
CREATE TABLE zj1(protype_id INT PRIMARY KEY,protype_name VARCHAR(10))SELECT * FROM zj1;INSERT INTO zj1 VALUES(1,'家用电器');INSERT INTO zj1 VALUES(2,'手机数码');INSERT INTO zj1 VALUES(3,'电脑办公');INSERT INTO zj1 VALUES(4,'图书音像');INSERT INTO zj1 VALUES(5,'家居家具');INSERT INTO zj1 VALUES(6,'服装配饰');INSERT INTO zj1 VALUES(7,'个护化妆');INSERT INTO zj1 VALUES(8,'运动户外');INSERT INTO zj1 VALUES(9,'汽车用品');INSERT INTO zj1 VALUES(10,'食品酒水');INSERT INTO zj1 VALUES(11,'营养保健');CREATE TABLE fb1(pro_id INT,pro_name VARCHAR(30),protype_id INT,price INT,pinpai VARCHAR(10),chandi VARCHAR(10),CONSTRAINT fb1_zj1_fk FOREIGN KEY(pro_id) REFERENCES zj1(protype_id))SELECT * FROM fb1;
INSERT INTO fb1 VALUES(1,'康佳(KONKA)42英寸全高清液晶电视',1,1999,'康佳','深圳'); 
INSERT INTO fb1 VALUES(2,'索尼(SONY)4G手机(黑色)',2,3238,'索尼','深圳');  
INSERT INTO fb1 VALUES(3,'海信(Hisense)55英寸智能电视',1,4199,'海信','青岛');   
INSERT INTO fb1 VALUES(4,'联想(Lenovo)14英寸笔记本电脑',3,5499,'联想','北京');  
INSERT INTO fb1 VALUES(5,'索尼(SONY)13.3英寸触控超级本',3,11499,'索尼','天津'); 
INSERT INTO fb1 VALUES(11,'索尼(SONY)60英寸全高清液晶电视',1,6999,'索尼','北京'); 
INSERT INTO fb1 VALUES(12,'联想(Lenovo)14.0英寸笔记本电脑',3,2999,'联想','北京');
INSERT INTO fb1 VALUES(13,'联想 双卡双待3G手机',2,988,'联想','北京');
INSERT INTO fb1 VALUES(15,'惠普(HP)黑白激光打印机',3,1169,'惠普','天津');
/*查询价格在1000-5000的品牌是联想的商品名称、商品价格、产品类型*/
SELECT pro_name,price,protype_name FROM fb1 INNER JOIN zj1 ON fb1.`protype_id`=zj1.`protype_id` WHERE price BETWEEN 1000 AND 5000 AND pinpai='联想';
/*查询id为5的商品的产品类型相同的所有品牌、产地、此商品的数量*/
SELECT pinpai,chandi FROM fb1 WHERE  protype_id = (SELECT protype_id FROM fb1 WHERE pro_id='5'            )SELECT protype_id,pinpai,COUNT(*) FROM fb1 GROUP BY pinpai HAVING protype_id =(SELECT protype_id FROM fb1 WHERE pro_id='5');/*删除产品类型表中id大于7的记录*//*DELETE FROM zj1 WHERE protype_id in(DELETE FROM fb1 where pro_id>7select protype_id from fb1 where pro_id>7);*/DELETE FROM zj1 WHERE protype_id>7;/*将家居家具修改为‘家具用品’*/UPDATE zj1 SET protype_name='家具用品' WHERE protype_id='5';/*查询家用电器下的所有商品的品牌和价格*/SELECT pinpai,price FROM fb1 WHERE protype_id=(SELECT protype_id FROM zj1 WHERE protype_name='家用电器');