目录
一.内联查询
1.inner join:只查询键值一致（交叉）的部分
2.演示
二.左连接
1.left join:以左表为标准，查询输出左表中没有的字段信息
2.演示
三.右连接
1.right join:以右表为标准，查询输出右表中没有的字段信息
2.演示
四.自连接
将一张表通过别名虚拟为两张表，查询比较其中的不同字段
一.内联查询
1.inner join:只查询键值一致（交叉）的部分
select (*|指定字段) from 表1 inner join 表2 on 匹配条件;
可以配合where语句、order by语句、逻辑语句等一起使用
2.演示
--t5的id和t6的id具有外键关系
mysql8.0 [SLB]>select * from t5;
+----+---------------+-----------+
| id | name | major |
+----+---------------+-----------+
| 1 | sulibao | dianshang |
| 2 | lixinjin | dianshang |
| 3 | zhanghongyuan | jike |
+----+---------------+-----------+
3 rows in set (0.00 sec)
mysql8.0 [SLB]>select * from t6;
+------+--------+------+
| age | weight | id |
+------+--------+------+
| 18 | 75.5 | 1 |
| 19 | 75.5 | 2 |
| 25 | 90 | 3 |
+------+--------+------+
3 rows in set (0.00 sec)
mysql8.0 [SLB]>select * from t5 inner join t6 on t5.id=t6.id; --联合查询所有字段
+----+---------------+-----------+------+--------+------+
| id | name | major | age | weight | id |
+----+---------------+-----------+------+--------+------+
| 1 | sulibao | dianshang | 18 | 75.5 | 1 |
| 2 | lixinjin | dianshang | 19 | 75.5 | 2 |
| 3 | zhanghongyuan | jike | 25 | 90 | 3 |
+----+---------------+-----------+------+--------+------+
3 rows in set (0.00 sec)
mysql8.0 [SLB]>select t5.id,t5.name,t5.major,t6.age,t6.weight from t5 inner join t6 on t
5.id=t6.id;
--联合查询指定字段
+----+---------------+-----------+------+--------+
| id | name | major | age | weight |
+----+---------------+-----------+------+--------+
| 1 | sulibao | dianshang | 18 | 75.5 |
| 2 | lixinjin | dianshang | 19 | 75.5 |
| 3 | zhanghongyuan | jike | 25 | 90 |
+----+---------------+-----------+------+--------+
3 rows in set (0.00 sec)
mysql8.0 [SLB]>select a.id,a.name,a.major,b.age,b.weight from t5 a inner join t6 b on a.id=b.id;
--也可以为名称较长的表取别名
+----+---------------+-----------+------+--------+
| id | name | major | age | weight |
+----+---------------+-----------+------+--------+
| 1 | sulibao | dianshang | 18 | 75.5 |
| 2 | lixinjin | dianshang | 19 | 75.5 |
| 3 | zhanghongyuan | jike | 25 | 90 |
+----+---------------+-----------+------+--------+
3 rows in set (0.00 sec)
二.左连接
1.left join:以左表为标准，查询输出左表中没有的字段信息
select (*|指定字段) from 左表 left join 从表 on 匹配条件;
可以配合where语句、order by语句、逻辑语句等一起使用
2.演示
(1)
mysql8.0 [SLB]>select * from t5;
+----+---------------+-----------+
| id | name | major |
+----+---------------+-----------+
| 1 | sulibao | dianshang |
| 2 | lixinjin | dianshang |
| 3 | zhanghongyuan | jike |
+----+---------------+-----------+
3 rows in set (0.00 sec)
mysql8.0 [SLB]>select * from t6;
+------+--------+------+
| age | weight | id |
+------+--------+------+
| 18 | 75.5 | 1 |
| 19 | 75.5 | 2 |
| 25 | 90 | 3 |
+------+--------+------+
3 rows in set (0.00 sec)
mysql8.0 [SLB]>select * from t5 left join t6 on t5.id=t6.id;
+----+---------------+-----------+------+--------+------+
| id | name | major | age | weight | id |
+----+---------------+-----------+------+--------+------+
| 1 | sulibao | dianshang | 18 | 75.5 | 1 |
| 2 | lixinjin | dianshang | 19 | 75.5 | 2 |
| 3 | zhanghongyuan | jike | 25 | 90 | 3 |
+----+---------------+-----------+------+--------+------+
3 rows in set (0.01 sec)
mysql8.0 [SLB]>select * from t6 left join t5 on t5.id=t6.id;
+------+--------+------+------+---------------+-----------+
| age | weight | id | id | name | major |
+------+--------+------+------+---------------+-----------+
| 18 | 75.5 | 1 | 1 | sulibao | dianshang |
| 19 | 75.5 | 2 | 2 | lixinjin | dianshang |
| 25 | 90 | 3 | 3 | zhanghongyuan | jike |
+------+--------+------+------+---------------+-----------+
3 rows in set (0.00 sec)
(2)
左表有的字段而从表没有对应内容则映射为null
mysql8.0 [SLB]>insert t5 values(4,"niexincheng","dianshang");
Query OK, 1 row affected (0.00 sec)
mysql8.0 [SLB]>select * from t5 left join t6 on t5.id=t6.id;
+----+---------------+-----------+------+--------+------+
| id | name | major | age | weight | id |
+----+---------------+-----------+------+--------+------+
| 1 | sulibao | dianshang | 18 | 75.5 | 1 |
| 2 | lixinjin | dianshang | 19 | 75.5 | 2 |
| 3 | zhanghongyuan | jike | 25 | 90 | 3 |
| 4 | niexincheng | dianshang | NULL | NULL | NULL |
+----+---------------+-----------+------+--------+------+
4 rows in set (0.00 sec)
左表没有的字段而从表有则不查询这些字段内容
mysql8.0 [SLB]>select * from t6 left join t5 on t5.id=t6.id;
+------+--------+------+------+---------------+-----------+
| age | weight | id | id | name | major |
+------+--------+------+------+---------------+-----------+
| 18 | 75.5 | 1 | 1 | sulibao | dianshang |
| 19 | 75.5 | 2 | 2 | lixinjin | dianshang |
| 25 | 90 | 3 | 3 | zhanghongyuan | jike |
+------+--------+------+------+---------------+-----------+
3 rows in set (0.00 sec)
三.右连接
1.right join:以右表为标准，查询输出右表中没有的字段信息
2.演示
用法同左连接类似
mysql8.0 [SLB]>select * from t6 right join t5 on t5.id=t6.id;
+------+--------+------+----+---------------+-----------+
| age | weight | id | id | name | major |
+------+--------+------+----+---------------+-----------+
| 18 | 75.5 | 1 | 1 | sulibao | dianshang |
| 19 | 75.5 | 2 | 2 | lixinjin | dianshang |
| 25 | 90 | 3 | 3 | zhanghongyuan | jike |
| NULL | NULL | NULL | 4 | niexincheng | dianshang |
+------+--------+------+----+---------------+-----------+
4 rows in set (0.01 sec)
mysql8.0 [SLB]>select * from t5 right join t6 on t5.id=t6.id;
+------+---------------+-----------+------+--------+------+
| id | name | major | age | weight | id |
+------+---------------+-----------+------+--------+------+
| 1 | sulibao | dianshang | 18 | 75.5 | 1 |
| 2 | lixinjin | dianshang | 19 | 75.5 | 2 |
| 3 | zhanghongyuan | jike | 25 | 90 | 3 |
+------+---------------+-----------+------+--------+------+
3 rows in set (0.00 sec)
四.自连接
将一张表通过别名虚拟为两张表，查询比较其中的不同字段
示例：yl表中查询yage<lage的所有信息，yl表中查询yage>lage的所有信息
mysql8.0 [SLB]>select y1.* from yl y1 join yl y2 on y1.lname=y2.lname and y1.yage<y2.lage;
+----------+------+------+-------+
| name | yage | lage | lname |
+----------+------+------+-------+
| sulibao | 20 | 25 | lh |
| lixinjin | 21 | 29 | cq |
+----------+------+------+-------+
2 rows in set (0.00 sec)
mysql8.0 [SLB]>select * from yl;
+-------------+------+------+-------+
| name | yage | lage | lname |
+-------------+------+------+-------+
| sulibao | 20 | 25 | lh |
| lixinjin | 21 | 29 | cq |
| niexincheng | 28 | 25 | zxw |
+-------------+------+------+-------+
3 rows in set (0.00 sec)
mysql8.0 [SLB]>select y1.* from yl y1 join yl y2 on y1.lname=y2.lname and y1.yage>y2.lage;
+-------------+------+------+-------+
| name | yage | lage | lname |
+-------------+------+------+-------+
| niexincheng | 28 | 25 | zxw |
+-------------+------+------+-------+
1 row in set (0.01 sec)
