mysql高级查询

火腿肠被谁买了？

mysql> select account.name as `名字` from account,cart where cart.goods_no = '1' and cart.account_id = account.id;
+--------+
| 名字   |
+--------+
| 张三   |
+--------+
1 row in set (0.00 sec)

零食被谁买了？

mysql> select distinct account.name from goods,account,category,cart
    -> where
    -> goods.category_no = category.no and category.no = 1
    -> and
    -> cart.goods_no = 1 and cart.account_id = account.id;
+--------+
| name   |
+--------+
| 张三   |
+--------+
1 row in set (0.00 sec)

张三买了哪些商品？

mysql> select goods.goods_name from goods,account,cart
    -> where
    -> account.id = 2 and account.id = cart.account_id
    -> and
    -> cart.goods_no = goods.good_no;
+------------+
| goods_name |
+------------+
| 火腿肠     |
| 鼠标       |
+------------+
2 rows in set (0.00 sec)

所有用户分别买了多少钱？

mysql> select account.name,sum(goods.price * cart.num) as `一共买了` from goods,cart,account
    -> where
    -> account.id = cart.account_id and cart.goods_no = goods.good_no group by account.name,account.money;
+--------+--------------+
| name   | 一共买了     |
+--------+--------------+
| 张三   |          105 |
| 王五   |           50 |
| 李四   |         1007 |
+--------+--------------+
3 rows in set (0.00 sec)

加上购物车东西全部销售，周几的营业额最高

# 将日期转换为星期几，并输出星期几的价钱最高：
mysql> select cart.create_time, sum(price*num), date_format(cart.create_time,'%W') as 星期 from cart,goods where cart.goods_no=goods.good_no group by cart.create_time order by sum(price*num);
+---------------------+----------------+----------+
| create_time         | sum(price*num) | 星期     |
+---------------------+----------------+----------+
| 2023-03-11 19:50:00 |              5 | Saturday |
| 2023-03-13 19:50:00 |             50 | Monday   |
| 2023-03-12 19:50:00 |           1107 | Sunday   |
+---------------------+----------------+----------+
3 rows in set (0.00 sec)

mysql> select count(num) 数量 from cart group by '数量';
+--------+
| 数量   |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

扩展了几个查询

1、date_add(create_time, interval 1 year) 
 给create_time增加1年
2、date_add(create_time, interval 1 month)
给create_time增加1月
3、date_add(create_time, interval 1 day) 
给create_time增加1天
4、date_add(create_time, interval 1 hour)
给create_time增加1小时
5、date_add(create_time, interval 1 minute) 
给create_time增加1分钟
6、date_add(create_time, interval 1 second) 
给create_time增加1秒钟
7、date_add(create_time, interval 1 microsecond) 
给create_time增加1季节
8、date_add(create_time, interval 1 week)
给create_time增加1周
9、date_add(create_time, interval 1 quarter) 
给create_time增加1刻钟

 date_sub 减少

张三在什么时候购物

select account.name,
cart.create_time as 购物时间
from cart,account
where account.name='张三'
and account_id=id;

+--------+---------------------+
| name   | 购物时间            |
+--------+---------------------+
| 张三   | 2023-03-11 19:50:00 |
| 张三   | 2023-03-12 19:50:00 |
+--------+---------------------+
2 rows in set (0.00 sec)

求哪个商品利润率最高

mysql> select goods.goods_name as `商品名称`,goods.cost as `成本`,goods.price as `售价`,goods.price-go
ods.cost as `利润`,format(((goods.price-goods.cost)/goods.cost),2) as `利润率` from goods order by (go
ods.price-goods.cost) /goods.cost DESC limit 3;
+--------------+--------+--------+--------------------+-----------+
| 商品名称     | 成本   | 售价   | 利润               | 利润率    |
+--------------+--------+--------+--------------------+-----------+
| 火腿肠       |    0.5 |    2.5 |                  2 | 4.00      |
| 白菜         |    0.2 |    0.8 | 0.6000000000000001 | 3.00      |
| 薯片         |    1.5 |    3.5 |                  2 | 1.33      |
+--------------+--------+--------+--------------------+-----------+
3 rows in set (0.00 sec)

求2023年3月12日前一周销售的商品

mysql> select goods.goods_name, cart.create_time from goods,cart where cart.goods_no=goods.good_no and cart.create_time between date_sub('2023-03-12', interval 1 week ) and '2023-03-12';
+------------+---------------------+
| goods_name | create_time         |
+------------+---------------------+
| 火腿肠     | 2023-03-11 19:50:00 |
+------------+---------------------+
1 row in set (0.00 sec)

求买过商品之后用户剩多少钱？

mysql> select account.name,account.money-sum(goods.price * cart.num) as `剩余` from goods,cart,account
  where account.id = cart.account_id and cart.goods_no =goods.good_no group by account.name,account.money;
+--------+--------+
| name   | 剩余   |
+--------+--------+
| 张三   |    245 |
| 王五   |  906.3 |
| 李四   | -738.5 |
+--------+--------+
3 rows in set (0.00 sec)

求库存还有多少

mysql> select goods.goods_name,goods.count-sum(cart.num)
    -> from goods,cart
    -> where
    -> goods.good_no=cart.goods_no
    -> group by goods.goods_name,goods.count;
+------------+---------------------------+
| goods_name | goods.count-sum(cart.num) |
+------------+---------------------------+
| 火腿肠     |                        48 |
| 薯片       |                        18 |
| 萝卜       |                        40 |
| 车厘子     |                        30 |
| 鼠标       |                        18 |
+------------+---------------------------+
5 rows in set (0.00 sec)