T O P

[资源分享]     SQL查询小案例

  • By - 楼主

  • 2019-10-24 09:23:06
  • 这是一篇自学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;

    本帖子中包含资源

    您需要 登录 才可以下载,没有帐号?立即注册