对mysql的练习

CREATE DATABASE jing_dong CHARSET utf8;
USE jing_dong;
CREATE TABLE good(
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    gname VARCHAR(150) NOT NULL,
    cate_name VARCHAR(40) NOT NULL,
    brand_name VARCHAR(40) NOT NULL,
    price DECIMAL(10,3) NOT NULL DEFAULT 0,
    is_show BIT NOT NULL DEFAULT 1,
    is_saleoff BIT NOT NULL DEFAULT 0
);
INSERT INTO good VALUES(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',DEFAULT,DEFAULT);
INSERT INTO good VALUES(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'x240 超极本','超级本','联想','4880',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'u330p 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'svp13226scb 触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT INTO good VALUES(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',DEFAULT,DEFAULT); 
INSERT INTO good VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

SELECT * FROM good;
#查询类型为超级本的名称和价格
SELECT gname,price FROM good WHERE cate_name='超级本';
#显示商品的种类 分组
SELECT cate_name AS '种类' FROM good GROUP BY cate_name;
#球所以电脑的平均价位 保留2位小数
SELECT ROUND(AVG(price),2) AS '均价' FROM good;
#显示每种商品的平均价格  先分组
SELECT brand_name,ROUND(AVG(price),2) AS '均价' FROM good GROUP BY brand_name;
#查询每种类型的商品中最贵 最便宜 平均价 数量
SELECT brand_name,
	MAX(price) AS '最贵',
	MIN(price) AS '最便宜',
	ROUND(AVG(price),2) AS '平均值',
	COUNT(brand_name) AS '数量'
	FROM good GROUP BY brand_name;
#查询所有价格大于平均价  以降序排序
SELECT * FROM good WHERE price>(SELECT AVG(price) FROM good) ORDER BY price DESC;

#查询每种类型最贵的电脑类型
SELECT * FROM good INNER JOIN 
	(SELECT MAX(price) AS max_price FROM good GROUP BY brand_name) 
	AS new_good ON new_good.max_price =good.price;







CREATE TABLE IF NOT EXISTS goods_cates(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40) NOT NULL
);

#查询good表中所有商品种类
SELECT cate_name FROM good GROUP BY cate_name;
#将分组结果写到good_cates数据表
INSERT INTO goods_cates (NAME) SELECT cate_name FROM good GROUP BY cate_name;
SELECT * FROM goods_cates;

#同步表数据  通过goods_cates 数据表来更新goods表
UPDATE goods AS g INNER JOIN goods_cates AS c ON g.cate_name=c.name SET g.cate_name=c.id;

#创建 "商品品牌表" 表  通过create...select来创建数据表并且同时写入记录,一步到位
SELECT brand_name FROM good GROUP BY brand_name;
CREATE TABLE goods_brands (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40) NOT NULL) SELECT brand_name AS NAME FROM good GROUP BY brand_name;

#同步数据 通过goods_brands数据表来更新good数据表
UPDATE good AS g INNER JOIN goods_brands AS b ON g.brand_name=b.name SET g.brand_name=b.id;