1:火腿肠被谁买了
mysql> select account.name ,goods.goods_name from account , goods ,cart
-> where
-> goods.goods_name = '火腿肠'
-> and
-> cart.goods_no = goods.good_no and cart.account_id = account.id;
2:零食被谁买了
mysql>select c.name,a.goods_name from goods a,cart b,account c,category d where a.category_no = d.no
-> and a.good_no = b.goods_no
-> and b.account_id = c.id
-> and d.name = '零食';
3:张三买了多少钱东西;
mysql> select account.name,sum(goods.price*cart.num) as '总价'
-> from goods,cart,account
-> where account.name='张三' and cart.goods_no=goods.good_no and account.id=cart.account_id;
+------+------+
| name | 总价 |
+------+------+
| 张三 | 105 |
+------+------+
1 row in set (0.00 sec)
4:求每个人购物车的总价
mysql> select account.name,sum(goods.price*cart.num) as '总价'
-> from goods,cart,account
-> where cart.goods_no=goods.good_no and account.id=cart.account_id group by account.name;
+------+------+
| name | 总价 |
+------+------+
| 张三 | 105 |
| 王五 | 50 |
| 李四 | 1007 |
+------+------+
3 rows in set (0.00 sec)
5:营业额最高的是星期几;
mysql> select cart.create_time,sum(goods.price*cart.num) as 营业额 from cart,goods where goods.good_no=cart.goods_no group by cart.create_time order by 营业额 DESC;
+---------------------+--------+
| create_time | 营业额 |
+---------------------+--------+
| 2023-03-12 19:50:00 | 1007 |
| 2023-03-13 19:50:00 | 150 |
| 2023-03-11 19:50:00 | 5 |
+---------------------+--------+
3 rows in set (0.00 sec)
6:张三买东西的时间
mysql> select account.name,cart.create_time from account,cart where account.id=cart.account_id and account.name='张三';
+------+---------------------+
| name | create_time |
+------+---------------------+
| 张三 | 2023-03-11 19:50:00 |
| 张三 | 2023-03-13 19:50:00 |
+------+---------------------+
2 rows in set (0.00 sec)
7:商品利润是多少
mysql> select sum((goods.price-goods.cost)*cart.num) as '利润'
-> from goods,cart,account
-> where goods.good_no=cart.goods_no and account.id=cart.account_id;
+------+
| 利润 |
+------+
| 455 |
+------+
1 row in set (0.00 sec)
8:求商品的利润率;
mysql> select goods.goods_name,sum((goods.price-goods.cost)/goods.cost)
-> from goods,cart,account
-> where goods.good_no=cart.goods_no and account.id=cart.account_id group by goods.goods_name;
+------------+------------------------------------------+
| goods_name | sum((goods.price-goods.cost)/goods.cost) |
+------------+------------------------------------------+
| 火腿肠 | 4 |
| 薯片 | 2.6666666666666665 |
| 萝卜 | 0.8749999999999999 |
| 车厘子 | 0.6666666666666666 |
| 鼠标 | 0.25 |
+------------+------------------------------------------+
5 rows in set (0.00 sec)
9:求2023年3月12日前一周销售的是商品。
mysql> select goods_name,create_time
-> from goods,cart
-> where goods.good_no=cart.goods_no and create_time=date_sub(2023/3/12,interval 1 week);
Empty set, 3 warnings (0.00 sec)
mysql> select goods_name,create_time
-> from goods,cart
-> where goods.good_no=cart.goods_no and create_time=date_sub(2023/3/12,interval 1 week);
Empty set, 3 warnings (0.00 sec)
11:求购买完商品后,顾客还剩多少余额
mysql> select account.name,(money-sum(goods.price*cart.num)) as '余额'
-> from account,goods,cart
-> where goods.good_no=cart.goods_no and account.id=cart.account_id group by account.name,money;
+------+--------+
| name | 余额 |
+------+--------+
| 张三 | 245 |
| 王五 | 906.3 |
| 李四 | -738.5 |
+------+--------+
3 rows in set (0.00 sec)
12:还剩多少库存
mysql> select sum(count-num)
-> from goods,cart
-> where goods.good_no=cart.goods_no;
+----------------+
| sum(count-num) |
+----------------+
| 184 |
+----------------+
1 row in set (0.00 sec)
mysql> select goods_name,sum(count-num)
-> from goods,cart
-> where goods.good_no=cart.goods_no group by goods_name;
+------------+----------------+
| goods_name | sum(count-num) |
+------------+----------------+
| 火腿肠 | 48 |
| 薯片 | 48 |
| 萝卜 | 40 |
| 车厘子 | 30 |
| 鼠标 | 18 |
+------------+----------------+
5 rows in set (0.00 sec)