多表查询
mysql> select age,gender from info union select name,phone from teacher;
+---------+-----------+
| age | gender |
+---------+-----------+
| 25 | 男 |
| 23 | 女 |
| 26 | 女 |
| 23 | 男 |
| 21 | 男 |
| leo | 12314312 |
| Jack | 4543121 |
| Tom | 565466746 |
| Tom | NULL |
| Jerry | NULL |
| Jerry_1 | NULL |
+---------+-----------+
inner join
mysql> select * from student;
+------+-------+--------+------+-------------+
| id | name | gender | age | phone |
+------+-------+--------+------+-------------+
| 2 | Jack | 1 | 28 | 17777777777 |
| 3 | Jerry | 1 | 20 | 12356548 |
| 4 | Mary | 2 | 19 | 1235457952 |
| 5 | Maria | 2 | 19 | 156459628 |
| 6 | leo | 1 | 24 | 43212354 |
+------+-------+--------+------+-------------+
mysql> select * from score;
+----+-------+-------+
| id | stuid | score |
+----+-------+-------+
| 1 | 1 | 89.00 |
| 2 | 3 | 80.00 |
| 3 | 4 | 70.00 |
+----+-------+-------+
mysql> select name,score from student inner join score on student.id=score.stuId;
+-------+-------+
| name | score |
+-------+-------+
| Jerry | 80.00 |
| Mary | 70.00 |
+-------+-------+
内连接，要有公共字段。student表中的id与score表中的stuId，
inner join注意事项
mysql> select name,score from student inner join score on student.id=score.stuId;
三张表后面再跟inner join
left join
mysql> select * from student;
+------+-------+--------+------+-------------+
| id | name | gender | age | phone |
+------+-------+--------+------+-------------+
| 2 | Jack | 1 | 28 | 17777777777 |
| 3 | Jerry | 1 | 20 | 12356548 |
| 4 | Mary | 2 | 19 | 1235457952 |
| 5 | Maria | 2 | 19 | 156459628 |
| 6 | leo | 1 | 24 | 43212354 |
+------+-------+--------+------+-------------+
mysql> select * from score;
+----+-------+-------+
| id | stuid | score |
+----+-------+-------+
| 1 | 1 | 89.00 |
| 2 | 3 | 80.00 |
| 3 | 4 | 70.00 |
+----+-------+-------+
mysql> select name,score from student left join score on student.id=score.stuId;
+-------+-------+
| name | score |
+-------+-------+
| Jerry | 80.00 |
| Mary | 70.00 |
| Jack | NULL |
| Maria | NULL |
| leo | NULL |
+-------+-------+
左连接，以左表为基准，上图是以student为基准，score为辅
right join
mysql> select name,score from student right join score on student.id=score.stuId;
+-------+-------+
| name | score |
+-------+-------+
| Jerry | 80.00 |
| Mary | 70.00 |
| NULL | 89.00 |
+-------+-------+
左连接，以右表为基准，上图是以score为基准，student为辅
cross join
mysql> select * from t_1;
+------+-------+
| id | name |
+------+-------+
| 1 | leo |
| 2 | jerry |
+------+-------+
mysql> select * from t_3;
+------+------+
| id | age |
+------+------+
| 1 | 19 |
| 2 | 15 |
| 3 | 18 |
| 4 | 20 |
| 5 | 22 |
| 6 | NULL |
| 7 | 24 |
+------+------+
#返回一个笛卡尔积
mysql> select * from t_1 cross join t_3;
+------+-------+------+------+
| id | name | id | age |
+------+-------+------+------+
| 1 | leo | 1 | 19 |
| 2 | jerry | 1 | 19 |
| 1 | leo | 2 | 15 |
| 2 | jerry | 2 | 15 |
| 1 | leo | 3 | 18 |
| 2 | jerry | 3 | 18 |
| 1 | leo | 4 | 20 |
| 2 | jerry | 4 | 20 |
| 1 | leo | 5 | 22 |
| 2 | jerry | 5 | 22 |
| 1 | leo | 6 | NULL |
| 2 | jerry | 6 | NULL |
| 1 | leo | 7 | 24 |
| 2 | jerry | 7 | 24 |
+------+-------+------+------+
mysql> select * from t_1 cross join t_3 where t_1.id=t_3.id;
+------+-------+------+------+
| id | name | id | age |
+------+-------+------+------+
| 1 | leo | 1 | 19 |
| 2 | jerry | 2 | 15 |
+------+-------+------+------+
交叉连接
natural join
mysql> select * from t_1 cross join t_3 where t_1.id=t_3.id;
+------+-------+------+------+
| id | name | id | age |
+------+-------+------+------+
| 1 | leo | 1 | 19 |
| 2 | jerry | 2 | 15 |
+------+-------+------+------+
#自然左连接与自然连接一样
mysql> select * from t_1 natural left join t_3;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | leo | 19 |
| 2 | jerry | 15 |
+------+-------+------+
#自然右连接
mysql> select * from t_1 natural right join t_3;
+------+------+-------+
| id | age | name |
+------+------+-------+
| 1 | 19 | leo |
| 2 | 15 | jerry |
| 3 | 18 | NULL |
| 4 | 20 | NULL |
| 5 | 22 | NULL |
| 6 | NULL | NULL |
| 7 | 24 | NULL |
+------+------+-------+
自然连接，根据同名字段判断,t_1和t_3都有同名字段为id
无公共同名字段的自然连接返回笛卡尔积
mysql> select * from t_1;
+--------+-------+
| number | name |
+--------+-------+
| 1 | leo |
| 2 | jerry |
+--------+-------+
mysql> select * from t_3;
+------+------+
| id | age |
+------+------+
| 1 | 19 |
| 2 | 15 |
| 3 | 18 |
| 4 | 20 |
| 5 | 22 |
| 6 | NULL |
| 7 | 24 |
+------+------+
#返回笛卡尔积
mysql> select * from t_1 natural join t_3;
+--------+-------+------+------+
| number | name | id | age |
+--------+-------+------+------+
| 1 | leo | 1 | 19 |
| 2 | jerry | 1 | 19 |
| 1 | leo | 2 | 15 |
| 2 | jerry | 2 | 15 |
| 1 | leo | 3 | 18 |
| 2 | jerry | 3 | 18 |
| 1 | leo | 4 | 20 |
| 2 | jerry | 4 | 20 |
| 1 | leo | 5 | 22 |
| 2 | jerry | 5 | 22 |
| 1 | leo | 6 | NULL |
| 2 | jerry | 6 | NULL |
| 1 | leo | 7 | 24 |
| 2 | jerry | 7 | 24 |
+--------+-------+------+------+
using
#t_1与t_3的字段同名
mysql> select * from t_1;
+------+-------+
| id | name |
+------+-------+
| 1 | leo |
| 2 | jerry |
+------+-------+
mysql> select * from t_3;
+------+------+
| id | name |
+------+------+
| 1 | 19 |
| 2 | 15 |
| 3 | 18 |
| 4 | 20 |
| 5 | 22 |
| 6 | NULL |
| 7 | 24 |
+------+------+
mysql> select * from t_1 natural join t_3;
Empty set, 2 warnings (0.00 sec)
#以id为准，用using指定连接字段
mysql> select * from t_1 inner join t_3 using(id);
+------+-------+------+
| id | name | name |
+------+-------+------+
| 1 | leo | 19 |
| 2 | jerry | 15 |
+------+-------+------+
哪一个连接实用？
inner join更加使用，使用的过程写全，可读性增强，也可根据业务需求
浙公网安备 33010602011771号