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>7
        select 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='家用电器'
         );