###这里是总的信息表
+----+--------+-------+--------------+
| id | name | price | cate_name |
+----+--------+-------+--------------+
| 1 | apple | 8888 | computer |
| 2 | huawei | 6888 | computer |
| 3 | lenvo | 5888 | computer |
| 4 | oppo | 3888 | mobile_phone |
| 5 | xiaomi | 2888 | mobile_phone |
| 6 | vivo | 2888 | mobile_phone |
| 7 | dell | 7888 | computer |
| 8 | HP | 7888 | computer |
| 9 | AMD | 3888 | cpu-device |
| 10 | intel | 4888 | cpu-device |
| 11 | nvidia | 1888 | gpu-device |
| 12 | AMD | 888 | gpu-device |
+----+--------+-------+--------------+
查询出价格最高的商品信息(这里信息是☞上表中的id、name、price、cate_name)当然这里也可以用left/right join on
select * from goods inner join (select goods.cate_name, max(price) as max_p from goods group by goods.cate_name) as goods_info on goods.cate_name=goods_info.cate_name and goods_info.max_p=goods.price;
+----+--------+-------+--------------+--------------+-------+
| id | name | price | cate_name | cate_name | max_p |
+----+--------+-------+--------------+--------------+-------+
| 1 | apple | 8888 | computer | computer | 8888 |
| 4 | oppo | 3888 | mobile_phone | mobile_phone | 3888 |
| 10 | intel | 4888 | cpu-device | cpu-device | 4888 |
| 11 | nvidia | 1888 | gpu-device | gpu-device | 1888 |
+----+--------+-------+--------------+--------------+-------+
select语句中嵌套的select语句,嵌套的语句查询结果如下,也就是是inner join 。。。on是表示将逻辑上的表(即查询出来的)和实际存在的(即建立在数据库中的)做链接。
select goods.cate_name,max(price) from goods group by goods.cate_name;
+--------------+------------+
| cate_name | max(price) |
+--------------+------------+
| computer | 8888 |
| cpu-device | 4888 |
| gpu-device | 1888 |
| mobile_phone | 3888 |
+--------------+------------+
分析:我们的目的是查询出最贵的商品的信息(id name cate_name price),首先见识过问题分解为两个部分:1、是查询出最贵的价格。2、是根据1查询出的结果找到对应的信息。这里与上面的两个表对应。即:
1、select goods.cate_name,max(price) from goods group by goods.cate_name;
2、select * from goods inner join (select goods.cate_name, max(price) as max_p from goods group by goods.cate_name) as goods_info on goods.cate_name=goods_info.cate_name and goods_info.max_p=goods.price;
添加一个group_by的用法
左连接/右链接/内连接的区别
先看内连接
select * from students inner join classes;
这是将students表中每一个id(主键)都与classe中id进行一次链接
+----+------------+--------+------+--------+-------+-----------+-----------+----+----------------+
| id | name | high | age | cls_id | score | home_addr | is_delete | id | name |
+----+------------+--------+------+--------+-------+-----------+-----------+----+----------------+
| 1 | chen | 170.32 | 25 | 1 | 78.00 | HK | 0 | 1 | nanshangyizho |
| 1 | chen | 170.32 | 25 | 1 | 78.00 | HK | 0 | 2 | python_school |
| 1 | chen | 170.32 | 25 | 1 | 78.00 | HK | 0 | 3 | python_school1 |
| 2 | wang | 175.21 | 19 | 4 | 54.32 | us | 0 | 1 | nanshangyizho |
| 2 | wang | 175.21 | 19 | 4 | 54.32 | us | 0 | 2 | python_school |
| 2 | wang | 175.21 | 19 | 4 | 54.32 | us | 0 | 3 | python_school1 |
| 3 | zhang | 165.68 | 18 | 7 | 82.56 | uk | 0 | 1 | nanshangyizho |
| 3 | zhang | 165.68 | 18 | 7 | 82.56 | uk | 0 | 2 | python_school |
| 3 | zhang | 165.68 | 18 | 7 | 82.56 | uk | 0 | 3 | python_school1 |
| 4 | lee | 136.90 | 13 | 5 | 78.00 | UK | 0 | 1 | nanshangyizho |
| 4 | lee | 136.90 | 13 | 5 | 78.00 | UK | 0 | 2 | python_school |
| 4 | lee | 136.90 | 13 | 5 | 78.00 | UK | 0 | 3 | python_school1 |
| 5 | xiaoming | 175.00 | 18 | 8 | 90.00 | CN | 0 | 1 | nanshangyizho |
| 5 | xiaoming | 175.00 | 18 | 8 | 90.00 | CN | 0 | 2 | python_school |
| 5 | xiaoming | 175.00 | 18 | 8 | 90.00 | CN | 0 | 3 | python_school1 |
| 6 | xiaoyueyue | 165.00 | 19 | 8 | 80.00 | Ger | 0 | 1 | nanshangyizho |
| 6 | xiaoyueyue | 165.00 | 19 | 8 | 80.00 | Ger | 0 | 2 | python_school |
| 6 | xiaoyueyue | 165.00 | 19 | 8 | 80.00 | Ger | 0 | 3 | python_school1 |
| 7 | liu | 175.00 | 45 | 4 | 70.00 | HK | 0 | 1 | nanshangyizho |
| 7 | liu | 175.00 | 45 | 4 | 70.00 | HK | 0 | 2 | python_school |
| 7 | liu | 175.00 | 45 | 4 | 70.00 | HK | 0 | 3 | python_school1 |
| 8 | jxiang | 155.00 | 16 | 9 | 80.00 | JP | 0 | 1 | nanshangyizho |
| 8 | jxiang | 155.00 | 16 | 9 | 80.00 | JP | 0 | 2 | python_school |
| 8 | jxiang | 155.00 | 16 | 9 | 80.00 | JP | 0 | 3 | python_school1 |
| 9 | ZHOU | 175.00 | 38 | 3 | 60.00 | TAI | 0 | 1 | nanshangyizho |
| 9 | ZHOU | 175.00 | 38 | 3 | 60.00 | TAI | 0 | 2 | python_school |
| 9 | ZHOU | 175.00 | 38 | 3 | 60.00 | TAI | 0 | 3 | python_school1 |
| 10 | GUO | 175.00 | 18 | 8 | 55.00 | CN | 0 | 1 | nanshangyizho |
| 10 | GUO | 175.00 | 18 | 8 | 55.00 | CN | 0 | 2 | python_school |
| 10 | GUO | 175.00 | 18 | 8 | 55.00 | CN | 0 | 3 | python_school1 |
| 11 | mao | 170.00 | 78 | 1 | 98.00 | hunan | 0 | 1 | nanshangyizho |
| 11 | mao | 170.00 | 78 | 1 | 98.00 | hunan | 0 | 2 | python_school |
| 11 | mao | 170.00 | 78 | 1 | 98.00 | hunan | 0 | 3 | python_school1 |
| 12 | ran | 180.23 | 25 | 2 | 82.00 | KERA0 | 0 | 1 | nanshangyizho |
| 12 | ran | 180.23 | 25 | 2 | 82.00 | KERA0 | 0 | 2 | python_school |
| 12 | ran | 180.23 | 25 | 2 | 82.00 | KERA0 | 0 | 3 | python_school1 |
+----+------------+--------+------+--------+-------+-----------+-----------+----+----------------+
select * from students inner join classes on students.cls_id=classes.id;
限制条件为cls_id和id相等的才内联
+----+------+--------+------+--------+-------+-----------+-----------+----+----------------+
| id | name | high | age | cls_id | score | home_addr | is_delete | id | name |
+----+------+--------+------+--------+-------+-----------+-----------+----+----------------+
| 1 | chen | 170.32 | 25 | 1 | 78.00 | HK | 0 | 1 | nanshangyizho |
| 9 | ZHOU | 175.00 | 38 | 3 | 60.00 | TAI | 0 | 3 | python_school1 |
| 11 | mao | 170.00 | 78 | 1 | 98.00 | hunan | 0 | 1 | nanshangyizho |
| 12 | ran | 180.23 | 25 | 2 | 82.00 | KERA0 | 0 | 2 | python_school |
+----+------+--------+------+--------+-------+-----------+-----------+----+----------------+
左连接
select * from students left join classes on students.cls_id=classes.id;
这里是以左表行为标准,左表中存在的必显示并且需要在右表中查询存在是否对应,如果没有则用null补齐,如果有则显示对应的数据
+----+------------+--------+------+--------+-------+-----------+-----------+------+----------------+
| id | name | high | age | cls_id | score | home_addr | is_delete | id | name |
+----+------------+--------+------+--------+-------+-----------+-----------+------+----------------+
| 1 | chen | 170.32 | 25 | 1 | 78.00 | HK | 0 | 1 | nanshangyizho |
| 11 | mao | 170.00 | 78 | 1 | 98.00 | hunan | 0 | 1 | nanshangyizho |
| 12 | ran | 180.23 | 25 | 2 | 82.00 | KERA0 | 0 | 2 | python_school |
| 9 | ZHOU | 175.00 | 38 | 3 | 60.00 | TAI | 0 | 3 | python_school1 |
| 2 | wang | 175.21 | 19 | 4 | 54.32 | us | 0 | NULL | NULL |
| 3 | zhang | 165.68 | 18 | 7 | 82.56 | uk | 0 | NULL | NULL |
| 4 | lee | 136.90 | 13 | 5 | 78.00 | UK | 0 | NULL | NULL |
| 5 | xiaoming | 175.00 | 18 | 8 | 90.00 | CN | 0 | NULL | NULL |
| 6 | xiaoyueyue | 165.00 | 19 | 8 | 80.00 | Ger | 0 | NULL | NULL |
| 7 | liu | 175.00 | 45 | 4 | 70.00 | HK | 0 | NULL | NULL |
| 8 | jxiang | 155.00 | 16 | 9 | 80.00 | JP | 0 | NULL | NULL |
| 10 | GUO | 175.00 | 18 | 8 | 55.00 | CN | 0 | NULL | NULL |
+----+------------+--------+------+--------+-------+-----------+-----------+------+----------------+