這是一篇自學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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/234679.html
標籤：其他
上一篇：最容易讓程式員翻車的 6 種情況！僅第二條，就讓人欲哭無淚
下一篇：mysql 無法連接提示 Authentication plugin 'caching_sha2_password' cannot be loaded