mysql的多表查询

一、表内容

mysql> show tables;
+-----------------+
| Tables_in_store |
+-----------------+
| account         |
| cart            |
| category        |
| goods           |
+-----------------+
4 rows in set (0.00 sec)

mysql> select * from account;
+------+-------+----------+------------+--------+-------+-------+---------------------+
| id   | phone | password | type       | name   | point | money | create_time         |
+------+-------+----------+------------+--------+-------+-------+---------------------+
|    1 | 111   | 111      | 0x00       | 管理员 |  NULL |  NULL | 2023-03-07 16:50:00 |
|    2 | 222   | 222      | 0x01       | 张三   |     0 |   350 | 2023-03-11 19:50:00 |
|    3 | 333   | 333      | 0x01       | 李四   |     0 | 268.5 | 2023-03-12 19:50:00 |
|    4 | 444   | 444      | 0x01       | 王五   |     0 | 956.3 | 2023-03-13 19:50:00 |
+------+-------+----------+------------+--------+-------+-------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from cart;
+------+----------+------+------------+---------------------+
| id   | goods_no | num  | account_id | create_time         |
+------+----------+------+------------+---------------------+
|    1 |        1 |    2 |          2 | 2023-03-11 19:50:00 |
|    2 |        2 |    2 |          3 | 2023-03-12 19:50:00 |
|    3 |        4 |   10 |          4 | 2023-03-13 19:50:00 |
|    4 |        6 |   20 |          3 | 2023-03-12 19:50:00 |
|    5 |        9 |    1 |          2 | 2023-03-12 19:50:00 |
|    6 |        2 |   10 |          4 | 2023-03-13 19:50:00 |
+------+----------+------+------------+---------------------+
6 rows in set (0.00 sec)

mysql> select * from category;
+------+----------+
| no   | name     |
+------+----------+
|    1 | 零食     |
|    2 | 蔬菜     |
|    3 | 水果     |
|    4 | 电子产品 |
|    5 | 酒水     |
+------+----------+
5 rows in set (0.00 sec)

mysql> select * from goods;
+---------+------------+------+-------+-------+---------------------+-------------+
| good_no | goods_name | cost | price | count | create_time         | category_no |
+---------+------------+------+-------+-------+---------------------+-------------+
|       2 | 薯片       |  1.5 |   3.5 |    30 | 2023-03-13 19:50:00 |           1 |
|       3 | 鼠标垫     | 20.5 |  25.5 |    20 | 2023-03-13 19:50:00 |           4 |
|       4 | 萝卜       |  0.8 |   1.5 |    50 | 2023-03-13 19:50:00 |           2 |
|       5 | 白菜       |  0.2 |   0.8 |    50 | 2023-03-13 19:50:00 |           2 |
|       6 | 车厘子     |   30 |    50 |    50 | 2023-03-13 19:50:00 |           3 |
|       7 | 芒果       |    5 |     8 |    50 | 2023-03-13 19:50:00 |           3 |
|       8 | 五粮液     | 1350 |  1700 |    60 | 2023-03-13 19:50:00 |           5 |
|       9 | 鼠标       |   40 |    50 |    20 | 2023-03-13 19:50:00 |           4 |
|       1 | 火腿肠     |  0.5 |   2.5 |    50 | 2023-03-13 19:50:00 |           1 |
+---------+------------+------+-------+-------+---------------------+-------------+
9 rows in set (0.00 sec)

二、问题

1.改日期

#将日期增加一天
 update account set create_time=date_add(create_time,interval 1 day);
 #将日期增加一月
 update account set create_time=date_add(create_time,interval 1 month);
 注意：此处date_add(create_time,interval 1 day),为一个函数day可为month,year,day,minute
 #修改年
 date_add(create_time, interval 1 year) 
 #修改月
 date_add(create_time, interval 1 month)
 #修改天
 date_add(create_time, interval 1 day) 
 date_add(create_time, interval 1 hour)
 date_add(create_time, interval 1 second) 

2.改别名

select 列名 as 新的别名 from 哪个表

3.联表查询

select * from account,cart,goods;

4.薯片[火腿肠] [零食]被谁买走了

select account.name,goods.goods_name from account,cart,goods where cart.goods_no = goods.good_no and cart.account_id = account.id and goods_name = '薯片[火腿肠][零食]';  

5.求每个人的购物车总价

select account.name, sum(cart.num * goods.price) from account,cart,goods,category where  cart.goods_no = goods.good_no and goods.category_no=category.no and cart.account_id = account.id group by account.name;  

6.求哪天的营业额最高

 select sum(cart.num*goods.price),cart.create_time  from goods,cart where cart.goods_no=goods.good_no  group by cart.create_time order by sum(cart.num*goods.price) desc limit 1; 

7.张三最爱在什么时候购物

 select cart.create_time
     from account,cart
     where
     account.id=cart.account_id
     and
     account.create_time=cart.create_time
     and
     account.name='张三';

8.求哪个商品的利润率最高

select goods.goods_name,(goods.price-goods.cost)/goods.cost from goods,cart where cart.goods_no=goods.good_no order by  (goods.price-goods.cost)/goods.cost desc limit 1;

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

select goods.goods_name from goods,cart where cart.goods_no = goods.good_no and cart.create_time between '2023-03-05' and '2023-03-12';

10.求每个用户的余额

select account.name,account.money-sum(cart.num*goods.price) from account,cart,goods where cart.account_id=account.id and goods.good_no=cart.goods_no group by account.name,account.money;

11.求每个商品的库存还余多少

 select
     goods.goods_name,goods.count-sum(cart.num)
     from
     goods,cart
     where
     cart.goods_no = goods.good_no
     group by goods.goods_name,goods.count;