MySQL Query exercise

mysql> #查询每个栏目最贵的商品mysql > select goods_id,shop_price,cat_id from (select goods_id,shop_price,cat_id from goods ORDER BY shop_price DESC) as temp GROUP by cat_id;+----------+------------+--------+| goods_id | Shop_price |       cat_id |+----------+------------+--------+|     16 |      823.33 |       2 | |    22 |      5999.00 |       3 | |    18 |      2878.00 |       4 | |    23 |      3700.00 |        5 | |     7 |      100.00 |        8 | |      6 |     42.00 |       11 | |      25 |     48.00 |       13 | |      29 |     90.00 |       14 | |      27 |     95.00 | |+----------+------------+--------+9 rows in Set (0.02 sec) mysql> #查询出每个栏目最低的商品mysql > select Goods_id,shop_ price,cat_id from (select Goods_id,shop_price,cat_idfrom goods ORDER by shop_price ASC) as temp GROUP by cat_id;+---------- +------------+--------+| goods_id | Shop_price |       cat_id |+----------+------------+--------+|     16 |      823.33 |       2 | |     20 |      280.00 |        3 | |    1 |      1388.00 | 4 ||    23 |      3700.00 |        5 | |      4 |      58.00 |        8 | |      5 |     20.00 |       11 | |      26 |     19.00 |       13 | |      30 |     18.00 |       14 | |      28 |     45.00 | |+----------+------------+--------+9 rows in Set (0.00 sec) mysql> #查询出每个栏目的平均价格mysql > select goods_id,cat_id, Shop_price from goods where cat_id=2;+----------+--------+------------+| goods_id | cat_id |       Shop_price |+----------+--------+------------+|      16 |     2 | 823.33 |+----------+--------+------------+1 row in Set (0.00 sec) mysql> Select Goods_id,cat_id,shop_price from goods w Here cat_id=3;+----------+--------+------------+| goods_id | cat_id |        Shop_price |+----------+--------+------------+|      8 |     3 |        399.00 | |      9 |    3 |       2298.00 | |      10 |    3 |       1328.00 | |      11 |    3 |       1300.00 | |      12 |     3 |       983.00 | |      13 |    3 |       1311.00 | |      15 |     3 |       788.00 | |      17 |    3 |     2300.00 | |  19 |     3 |       858.00 | |      20 |     3 |       280.00 | |      21 |    3 |       2000.00 | |      22 |    3 |       5999.00 | |      24 |    3 |       2000.00 | |      31 |    3 |       1337.00 | |      32 |    3 | 3010.00 |+----------+--------+------------+15 rows in Set (0.00 sec) mysql> Select Cat_id,avg (shop_price) from goods WH ere cat_id=3;+--------+-----------------+| cat_id |      AVG (shop_price) |+--------+-----------------+|     3 | 1746.066667 |+--------+-----------------+1 row in Set (0.00 sec) mysql> Select cat_id from goods GROUP by cat_id;+------ --+|      cat_id |+--------+|      2 | |      3 | |      4 | |      5 | |     8 | |     11 | |     13 | |     14 | |  |+--------+9 rows in Set (0.00 sec) mysql> Select Cat_id,avg (shop_price) from goods where cat_id in (select CAT_ID from goods Group by cat_id) +--------+-----------------+| cat_id |      AVG (shop_price) |+--------+-----------------+|     4 | 1232.526774 |+--------+-----------------+1 row in set (0. sec) mysql> Select Cat_id,sum (goods_number) from goods where cat_id=4;+--------+-------------------+| cat_id |      SUM (goods_number) |+--------+-------------------+|                 4 | 3 |+--------+-------------------+1 row in Set (0.00 sec) mysql> Select Cat_id,sum (goods_number) from goods GROUP by Cat_ id;+--------+-------------------+| cat_id |      SUM (goods_number) |+--------+-------------------+|                 2 |      0 | |               3 |      203 | |                 4 |      3 | |                 5 |      8 | |                8 |     61 | |                11 |     23 | |                 13 |     4 | |                 14 |     9 | |                 15 | 2 |+--------+-------------------+9 rows in Set (0.00 sec) mysql> #取出每个栏目下的商品的平均价格mysql > select Cat_id,avg (shop_ Price) as ' average ' from goods group by cat_id;+--------+-------------+| cat_id |      Average price |+--------+-------------+|  2 |      823.330000 | | 3 |      1746.066667 | | 4 |      2297.000000 | | 5 | 3700.000000 | |   8 |     75.333333 | |   11 |     31.000000 | |   13 |     33.500000 | |   14 |     54.000000 | |   15 | 70.000000 |+--------+-------------+9 rows in Set (0.03 sec) mysql> #取出每个栏目下的库存量mysql > select Cat_id,goods_number From goods GROUP by cat_id;+--------+--------------+| cat_id |      Goods_number |+--------+--------------+|            2 |      0 | |            3 |      1 | |            4 |      1 | |            5 |      8 | |           8 |     17 | |            11 |     8 | |            13 |     2 | |            14 |     0 | |            15 |  2 |+--------+--------------+9 rows in Set (0.00 sec) mysql> #查询每个栏目下商品的种类mysql > select Cat_id,count (*) as ' product type ' from Goods cat_id;+--------+----------+| cat_id |      Product Category |+--------+----------+|       4 | |+--------+----------+1 row in Set (0.00 sec) mysql> Select Cat_id,count (*) as ' product category ' from goods Group by cat_id;+-- ------+----------+| cat_id |      Product Category |+--------+----------+|        2 |      1 | |       3 | 15||        4 |      3 | |        5 |      1 | |        8 |     3 | |        11 |     2 | |        13 |     2 | |        14 |     2 | |        15 | 2 |+--------+----------+9 rows in Set (0.02 sec) mysql> #having和group综合使用查询mysql > #查询该店的商品比市场价所省的价格mysql > Select Goods_id,shop_price,market_price-shop_price as ' money from the market province ' from goods;+----------+------------+--------------+| goods_id | Shop_price |        More money than the market |+----------+------------+--------------+|    1 |       1388.00 |        277.60 | |      4 |        58.00 |        11.60 | |      3 |        68.00 |        13.60 | |      5 |         20.00 |        4.00 | |      6 |         42.00 |        8.40 | |     7 |        100.00 |        20.00 | |     8 |        399.00 |        79.79 | |    9 |       2298.00 |       459.60 | |    10 |       1328.00 |       265.60 | |    11 |     1300.00 |       -1300.00 | |     12 |       983.00 |       196.60 | |    13 |       1311.00 |       262.20 | |    14 |       2625.00 |       525.00 | |     15 | 788.00|       157.60 | |     16 |       823.33 |       164.67 | |    17 |       2300.00 |       460.00 | |    18 |       2878.00 |       575.60 | |     19 |       858.00 |       171.60 | |     20 |        280.00 |       56.00 | |    21 |       2000.00 |       400.00 | |    22 |      5999.00 |       1199.80 | |    23 |       3700.00 |       740.00 | |    24 |       2000.00 |       400.00 | |      25 |         48.00 |       9.59 | |      26 |         19.00 |       3.80 | |      27 |         95.00 |       5.00 | |      28 |         45.00 |       5.00 | |      29 |       90.00 |       -90.00 | |      30 |         18.00 |       3.00 | |    31 |       1337.00 |       267.39 | |    32 |       3010.00 | 602.00 |+----------+------------+--------------+31 rows in Set (0.05 sec) mysql> #查询每个商品所积压的货款mysql > select Goods_ Id,goods_number*shop_price as ' backlog of payments ' from goods;+----------+------------+| goods_id |        Backlog of payment |+----------+------------+|    1 |        1388.00 | |     4 |        986.00 | |    3 |1632.00 | |     5 |        160.00 | |     6 |        630.00 | |    7 |        2000.00 | |     8 |        399.00 | |    9 |       9192.00 | |    10 |       9296.00 | |    11 |       1300.00 | |    12 |       7864.00 | |   13 |       10488.00 | |    14 |       2625.00 | |    15 |       2364.00 | |       16 |       0.00 | |    17 |       2300.00 | |    18 |       2878.00 | |   19 |       10296.00 | |    20 |       3360.00 | |   21 |       80000.00 | |    22 |       5999.00 | |   23 |       29600.00 | |  24 |       200000.00 | |      25 |       96.00 | |      26 |       38.00 | |     27 |       190.00 | |       28 |       0.00 | |       29 |       0.00 | |     30 |       162.00 | |    31 |       1337.00 | |   32 | 12040.00 |+----------+------------+31 rows in Set (0.01 sec) mysql> #查询积压的总货款mysql > select sum (shop_price*goods_ Number) as ' backlog of total payments ' from goods;+--------------+|    Backlog of total loans |+--------------+| 398620.00 |+--------------+1 row in Set (0.00 sec) mysql> #查询每个栏目积压的货款mysql > select cat_id,SUM (shop_price*goods_number) as ' backlog of payments ' from goods Group by cat_id;+--------+------------+| cat_id |      Backlog of payment |+--------+------------+|       2 |      0.00 | |  3 |      356235.00 | |    4 |      6891.00 | |   5 |      29600.00 | |    8 |     4618.00 | |     11 |     790.00 | |     13 |     134.00 | |     14 |     162.00 | |     15 | 190.00 |+--------+------------+9 rows in Set (0.00 sec) mysql> #查询比市场价省钱200元以上的商品及该商品所省的钱mysql > select goods_id, Market_price-shop_price as ' sheng ' from goods where market_price-shop_price>200;+----------+---------+| goods_id |        Sheng |+----------+---------+|  1 |        277.60 | |  9 |       459.60 | |  10 |       265.60 | |  13 |       262.20 | |  14 |       525.00 | |  17 |       460.00 | |  18 |       575.60 | |  21 |       400.00 | | 22 |       1199.80 | |  23 |       740.00 | |  24 |       400.00 | |  31 |       267.39 | |  32 | 602.00 |+----------+---------+13 rows in Set (0.12 sec) mysql> Select Goods_id,market_price-shop_price as ' Sheng ' frOm goods having sheng>200;+----------+---------+| goods_id |        Sheng |+----------+---------+|  1 |        277.60 | |  9 |       459.60 | |  10 |       265.60 | |  13 |       262.20 | |  14 |       525.00 | |  17 |       460.00 | |  18 |       575.60 | |  21 |       400.00 | | 22 |       1199.80 | |  23 |       740.00 | |  24 |       400.00 | |  31 |       267.39 | |  32 | 602.00 |+----------+---------+13 rows in Set (0.00 sec) mysql> #查询货款大于2w的栏目, as well as the backlog of payments in this column mysql> select Cat_id,sum ( Goods_number*shop_price) as Huokuan from goods group by CAT_ID have huokuan>20000;+--------+-----------+| cat_id |      Huokuan |+--------+-----------+| 3 |      356235.00 | |  5 | 29600.00 |+--------+-----------+2 rows in Set (0.01 sec) mysql>

MySQL Query exercise