没事多尝试下 最多报错, 不会损坏数据库(最多也是测试环境数据库,难道你还是在生产环境学习sql?)

多级关联和两个表关联查询没什么区别 大胆的去尝试就知道了,直接在后面加 left jion on

mysql> select * from user right join orders on user.id = orders.user_id right join orderdetail on orders.id = orderdetail.orders_id left join items on items.id = orderdetail.items_id;
+----+----------+------------+-----+---------+----+---------+---------+---------------------+------+----+-----------+----------+-----------+----+---------+--------+--------+------------------------------------------+---------------------+
| id | username | birthday   | sex | address | id | user_id | number  | createtime          | note | id | orders_id | items_id | items_num | id | name    | price  | detail | pic                                      | createtime          |
+----+----------+------------+-----+---------+----+---------+---------+---------------------+------+----+-----------+----------+-----------+----+---------+--------+--------+------------------------------------------+---------------------+
|  1 | 王五     | 2017-11-25 | 3   | 南阳    |  3 |       1 | 1000010 | 2015-02-04 13:22:35 | NULL |  1 |         3 |        1 |         1 |  1 | 台式机1 | 3000.0 |        | e2269631-2190-45ed-a215-b8a8dc0dc3d7.png | 2015-02-03 13:22:50 |
|  1 | 王五     | 2017-11-25 | 3   | 南阳    |  3 |       1 | 1000010 | 2015-02-04 13:22:35 | NULL |  2 |         3 |        2 |         3 |  2 | 笔记本  | 6000.0 |        | fffb17d9-0280-461a-b9db-9927ffe5c1b7.png | 2015-02-09 13:22:57 |
|  1 | 王五     | 2017-11-25 | 3   | 南阳    |  4 |       1 | 1000011 | 2015-02-03 13:22:41 | NULL |  3 |         4 |        3 |         4 |  3 | 背包    |  200.0 |        | NULL                                     | 2015-02-06 13:23:02 |
|  1 | 王五     | 2017-11-25 | 3   | 南阳    |  4 |       1 | 1000011 | 2015-02-03 13:22:41 | NULL |  4 |         4 |        2 |         3 |  2 | 笔记本  | 6000.0 |        | fffb17d9-0280-461a-b9db-9927ffe5c1b7.png | 2015-02-09 13:22:57 |
+----+----------+------------+-----+---------+----+---------+---------+---------------------+------+----+-----------+----------+-----------+----+---------+--------+--------+------------------------------------------+---------------------+
4 rows in set (0.08 sec)

复杂的sql

要求: 统计用户的各订单价格

(注意,这里每个订单中可能有几个订单项,比如一个订单里面有书和鞋,这里面的订单价格就是 书的价格+鞋的价格)

mysql> select user.id,user.username,user.address,orders.number,sum(items.price) from user right join orders on user.id = orders.user_id right join orderdetail on orders.id = orderdetail.orders_id left join items on items.id = orderdetail.items_id GROUP BY orders.number;
+----+----------+---------+---------+------------------+
| id | username | address | number  | sum(items.price) |
+----+----------+---------+---------+------------------+
|  1 | 王五     | 南阳    | 1000010 |           9000.0 |
|  1 | 王五     | 南阳    | 1000011 |           6200.0 |
+----+----------+---------+---------+------------------+
2 rows in set (0.11 sec)
mysql> 
select user.id,user.username,user.address,orders.number,items.name, items.price from user right join orders on user.id = orders.user_id right join orderdetail on orders.id = orderdetail.orders_id left join items on items.id = orderdetail.items_id;
+----+----------+---------+---------+---------+--------+
| id | username | address | number  | name    | price  |
+----+----------+---------+---------+---------+--------+
|  1 | 王五     | 南阳    | 1000010 | 台式机1 | 3000.0 |
|  1 | 王五     | 南阳    | 1000010 | 笔记本  | 6000.0 |
|  1 | 王五     | 南阳    | 1000011 | 背包    |  200.0 |
|  1 | 王五     | 南阳    | 1000011 | 笔记本  | 6000.0 |
+----+----------+---------+---------+---------+--------+
4 rows in set (0.08 sec)

第一个查询统计价格用到了 sum() 函数 用来累加求和 属于聚合函数

数据聚合函数的还有 avg()求平均值 max()最大值 min()最小值 count()统计数据条数。