SQL查詢小案例

這是一篇自學MySQL的小案例，下麵是部分數據信息：goods表

1、查詢cate_name為‘超級本’的商品名稱、價格

SELECT
`name`,
price
FROM
goods
WHERE cate_name like '超級本';

-- 2、查詢商品的種類

SELECT
cate_name
FROM
goods
GROUP BY
cate_name;

-- 3、查詢所有電腦產品的平均價格，並且保留兩位小數

SELECT
ROUND(avg(price), 2) AS avg_price
FROM
goods;

-- 4、查詢每種商品的平均價格

SELECT
cate_name,
avg(price) AS avg_price
FROM
goods
GROUP BY
cate_name;

-- 5、查詢每種商品中的最高價格、最低價格，平均價、數量

SELECT
cate_name,
MAX(price) AS ma_price,
MIN(price) AS mi_price,
avg(price) AS avg_price,
COUNT(*)
FROM
goods
GROUP BY
cate_name;

-- 6、查詢所有價格大於平均價格的商品、並且按價格降序排列

SELECT
id,
NAME,
cate_name,
brand_name,
price
FROM
goods
WHERE
price > (
SELECT
ROUND(AVG(price), 2)
FROM
goods
)
ORDER BY
price DESC;

-- 7、查詢每種類型中最貴的電腦信息

SELECT
*
FROM
goods
INNER JOIN (
SELECT
cate_name,
MAX(price) AS max_price,
MIN(price) AS min_price,
COUNT(*)
FROM
goods
GROUP BY
cate_name
) AS goods_new_info ON goods.cate_name = goods_new_info.cate_name
AND goods.price = goods_new_info.max_price;