数据准备
#MySQL
-- 创建京东数据库
-- CREATE DATABASE jing_dong CHARSET=OUTFILE;
-- 使用京东数据库
-- USE jing_dong;
-- 创建一个商品goods数据表
CREATE TABLE goods (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
NAME 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
);-- 向goods表中插入数据
INSERT INTO goods
VALUES
( 0, 'r510vc 15.6英寸笔记本', '笔记本', '华硕', '3399', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'y400n 14.0英寸笔记本电脑', '笔记本', '联想', '8499', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'g150th 15.6英寸游戏本', '游戏本', '雷神', '2799', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'x550cc 15.6英寸笔记本', '笔记本', '华硕', '4880', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'x240 超极本', '超极本', '联想', '4299', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'u330p 13.3英寸超极本', '超极本', '联想', '7999', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'svp13226scb 触控笔超极本', '超极本', '索尼', '1998', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'ipad mini 7.9英寸平板电脑', '平板电脑', '苹果', '3388', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'ipad air 9.7 英寸平板电脑', '平板电脑', '苹果', '2788', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'ipad mini 配备 retina 显示屏', '平板电脑', '苹果', '3499', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'idea centre c340 20英寸一体电脑', '台式机', '联想', '2899', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'vostro 3800-r1206 台式电脑', '台式机', '戴尔', '9188', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'at7-7414lp 台式电脑 linux)', '台式机', '宏碁', '3699', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'z220sff f4f06pa工作站', '服务器 / 工作站', '惠普', '4288', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'powerredge ii服务器', '服务器 / 工作站', '戴尔', '6388', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'mac pro专业级台式电脑', '服务器 / 工作站', '苹果', '28888', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'hmz-t3w 头戴显示设备', '笔记本配件', '索尼', '6999', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, '商务双肩包', '笔记本配件', '索尼', '99', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, 'x3250 m4机架式服务器', '服务器 / 工作站', 'ibm', '6888', DEFAULT, DEFAULT );
INSERT INTO goods
VALUES
( 0, '商务双肩包', '笔记本配件', '索尼', '99', DEFAULT, DEFAULT );
SELECT
*
FROM
goods;
查询练习
-- 查询类型 cate_name为‘超极本’的商品名称name、价格price(where)
SELECT NAME
,
price
FROM
goods
WHERE
cate_name = '超极本';
-- 显示商品的种类
-- 1 分组的方式(group by)
SELECT
cate_name
FROM
goods
GROUP BY
cate_name;
-- 2 去重的方法(DISTINCT)
SELECT DISTINCT
cate_name
FROM
goods;
-- 求所有电脑产品的平均价格avg, 并且保留两位小数(round)
SELECT
ROUND( AVG( price ), 2 ) AS "平均价格"
FROM
goods;
-- 显示每种类型cate_name的平均价格
SELECT
cate_name AS "商品类型",
AVG( price ) AS "平均价格"
FROM
goods
GROUP BY
cate_name;
-- 查询每种类型的商品中最贵max、最便宜min、平均价格avg、数量count
SELECT
cate_name AS "商品类型",
MAX( price ) AS "最高价格",
MIN( price ) AS "最低价格",
AVG( price ) AS "平均价格",
COUNT(*) AS "商品数量"
FROM
goods
GROUP BY
cate_name;
-- 查询所有价格大于平均价格的商品,并且按价格降序排序
-- 1. 查询平均价格
SELECT
AVG( price ) AS "平均价格"
FROM
goods;
-- 2. 所有价格大于平均价格的商品,并且按价格降序排序
SELECT
*
FROM
goods
WHERE
price > ( SELECT AVG( price ) AS "平均价格" FROM goods )
ORDER BY
price DESC;
-- 查询每种类型中最贵的电脑信息
-- 1. 查询每种类型中最贵的价格
SELECT
cate_name,
MAX( price )
FROM
goods
GROUP BY
cate_name;
-- 2. 查询最贵的电脑信息
SELECT
*
FROM
goods
INNER JOIN ( SELECT cate_name, MAX( price ) AS max_price FROM goods GROUP BY cate_name ) AS max_price_goods ON goods.cate_name = max_price_goods.cate_name
AND goods.price = max_price_goods.max_price;