mysql -5练习

-- 创造产品类型表
CREATE TABLE product_type(
        protype_id INT PRIMARY KEY AUTO_INCREMENT,
        protype_name VARCHAR(15)
         )
    SELECT * FROM product_type;
INSERT product_type(protype_name)VALUES('家用电器');
INSERT product_type(protype_name)VALUES('手机数码');
INSERT product_type(protype_name)VALUES('电脑办公');
INSERT product_type(protype_name)VALUES('图书音像');
INSERT product_type(protype_name)VALUES('家居家具');
INSERT product_type(protype_name)VALUES('服装配饰');
INSERT product_type(protype_name)VALUES('个护化妆');
INSERT product_type(protype_name)VALUES('运动户外');
INSERT product_type(protype_name)VALUES('汽车用品');
INSERT product_type(protype_name)VALUES('食品酒水');
INSERT product_type(protype_name)VALUES('营养保健');


DROP TABLE product;
-- 创造产品表
CREATE TABLE product(
   proid INT PRIMARY KEY,
   pro_name VARCHAR(50),
   protype_id INT,
   price INT,
   pinpai VARCHAR(10),
   chandi VARCHAR(10),
   CONSTRAINT product_product_type_fk FOREIGN KEY (protype_id) REFERENCES product_type(protype_id)
)

SELECT * FROM product;  

INSERT INTO product VALUES(1,'康佳（KNOKA）42英寸全高清液晶电视',1,1999,'康佳','深圳'); 
INSERT INTO product VALUES(2,'索尼（SONY）4G手机(黑色)',2,3238,'索尼','深圳'); 
INSERT INTO product VALUES(3,'海信（Hisense）55英寸智能电视',1,4199,'海信','青岛'); 
INSERT INTO product VALUES(4,'联想（Lenovo）14.0英寸笔记本电脑',3,5499,'联想','北京'); 
INSERT INTO product VALUES(5,'索尼（SONY）13.3英寸触控超级本',3,11499,'索尼','天津'); 
INSERT INTO product VALUES(11,'索尼（SONY）60英寸全高清液晶电视',1,6999,'索尼','北京');
INSERT INTO product VALUES(12,'联想（Lenovo）14.0英寸笔记本电脑',3,2999,'联想','北京'); 
INSERT INTO product VALUES(13,'联想 双卡双待3G手机',2,988,'联想','北京');
INSERT INTO product VALUES(15,'惠普（HP）黑白激光打印机',3,1169,'惠普','天津');   

-- 1查询价格在1000~5000之间的品牌为联想的商品名称、商品价格、产品类型
SELECT pro_name,price,protype_name FROM product JOIN product_type ON product.`protype_id` = product_type.`protype_id` 
WHERE price BETWEEN 1000 AND 5000;
-- 2查询与id为5的商品产品类型相同的所有品牌的品牌，产地，此品牌的产品数量，
SELECT pinpai,chandi,COUNT(pinpai) FROM product
 WHERE protype_id =(SELECT protype_id FROM product WHERE proid = 5) GROUP BY pinpai;
-- 3 删除产品类型表中id大于7的记录 
DELETE FROM product_type WHERE protype_id>7;
-- 4修改‘家居家具’为‘家具用品’；
UPDATE product_type SET protype_name ='家具用品' WHERE protype_id = 5;
-- 5查询‘家用电器’下所有商品的品牌和价格
SELECT pinpai,price FROM product JOIN product_type ON product.`protype_id` = product.`protype_id`
WHERE protype_name = '家用电器'；