7.24MySQL练习

1、火腿肠被谁买了

 SELECT goods.goods_name,account.name FROM goods,cart,account
 WHERE goods.good_no = cart.goods_no AND account.id = cart.account_id
 AND goods.goods_name = '火腿肠';

2、零食被谁买了

 SELECT account.name,category.name FROM goods,cart,account,category
 WHERE goods.good_no = cart.goods_no AND account.id = cart.account_id 
 AND category.no = goods.category_no
 AND category.name = '零食';

3、张三花了多少钱

SELECT account.name,SUM(goods.price*cart.num) AS '总额' 
FROM goods,cart,account
WHERE goods.good_no = cart.goods_no AND account.id = cart.account_id
AND account.name = '张三'
GROUP BY account.name;

4、每个人购物车总价

 SELECT account.name,SUM(goods.price * cart.num) AS 总数 
 FROM cart,category,goods,account 
 WHERE cart.goods_no = goods.good_no 
 AND cart.account_id = account.id ADN category.no = goods.category_no 
 GROUP BY account.name;

5、一周内哪一天的商品总价最高

 SELECT dayofweek(cart.create_time) AS week_day,
 MAX(goods.price*cart.num) AS 总价
 FROM goods,cart,account
 WHERE cart.account_id = account.id
 AND cart.goods_no = goods.good_no
 GROUP BY cart.create_time
 ORDER BY 总价 DESC;

6、张三的购物时间在什么时候

 SELECT cart.create_time AS 购物时间 
 FROM account, cart 
 WHERE account.id = cart.account_id
 AND account.name = '张三';

7、购物车内的总销售额

 SELECT SUM((goods.price-goods.cost)*cart.num) AS 盈利 
 FROM account, cart ,goods WHERE account.id = cart.account_id 
 AND cart.goods_no = goods.good_no ;

8、那个商品利润率最高

 SELECT DISTINCT goods.goods_name,
 (goods.price-goods.cost)/goods.cost AS 利润率 FROM cart ,goods 
 WHERE cart.goods_no = goods.good_no 
 ORDER BY 利润率 DESC;

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

 SELECT goods.goods_name FROM cart,goods
 WHERE cart.goods_no = goods.good_no
 AND
 cart.create_time 
 BETWEEN DATE_SUB('2023-03-12',INTERVAL 1 WEEK) AND '2023-03-12';

10、求用户购买东西后还有多少余额

 SELECT account.name,sum(goods.price * cart.num) AS 总花费,
 account.money-sum(goods.price * cart.num) AS 余额
 FROM account,goods,cart
 WHERE account.id=cart.account_id and cart.goods_no=goods.good_no
 GROUP BY account.money,account.name;

11、减去用户购买后商品剩余库存

 SELECT goods.goods_name,goods.count-sum(cart.num) AS 剩余库存 FROM goods,cart
 WHERE goods.good_no = cart.goods_no
 GROUP BY goods.goods_name,goods.count;