sql join
创建表
mysql> create table a(c1 int, c2 int);
Query OK, 0 rows affected (0.02 sec)
mysql> create table b(c3 int, c4 int);
Query OK, 0 rows affected (0.02 sec)
插入数据
mysql> insert into a values (1,2), (3,4), (5,6);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into b values (2,3), (4,5), (8,9);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
a
mysql> select * from a;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
+------+------+
3 rows in set (0.00 sec)
b
mysql> select * from b;
+------+------+
| c3 | c4 |
+------+------+
| 2 | 3 |
| 4 | 5 |
| 8 | 9 |
+------+------+
2 rows in set (0.00 sec)
join
join 就是一条一条的去对应
mysql> select * from a join b;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 1 | 2 | 2 | 3 |
| 3 | 4 | 2 | 3 |
| 5 | 6 | 2 | 3 |
| 1 | 2 | 4 | 5 |
| 3 | 4 | 4 | 5 |
| 5 | 6 | 4 | 5 |
| 1 | 2 | 8 | 9 |
| 3 | 4 | 8 | 9 |
| 5 | 6 | 8 | 9 |
+------+------+------+------+
9 rows in set (0.00 sec)
join on
mysql> select * from a join b on c2=c3;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 1 | 2 | 2 | 3 |
| 3 | 4 | 4 | 5 |
+------+------+------+------+
2 rows in set (0.00 sec)
left join
在join的基础上，保留左边的所有数据
mysql> select * from a left join b on c2=c3;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 1 | 2 | 2 | 3 |
| 3 | 4 | 4 | 5 |
| 5 | 6 | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)
right join
在join的基础上，保留右边的所有数据
mysql> select * from a right join b on c2=c3;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 1 | 2 | 2 | 3 |
| 3 | 4 | 4 | 5 |
| NULL | NULL | 8 | 9 |
+------+------+------+------+
3 rows in set (0.00 sec)
full join
把left join 和right join的数据union起来就包含了左右的数据了。
mysql> select * from a left join b on c2=c3
-> union
-> select * from a right join b on c2=c3;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 1 | 2 | 2 | 3 |
| 3 | 4 | 4 | 5 |
| 5 | 6 | NULL | NULL |
| NULL | NULL | 8 | 9 |
+------+------+------+------+
4 rows in set (0.01 sec)
求c2最大的数
自连接比较，如果没有比a.c2更大的数，那么就得到了最大的数了。
x y
小 大
小 大
小 空 <--- 这里的小就是最大的数，没有找到比x更大的数了
mysql> select * from a left join a as t1 on a.c2 < t1.c2;
+------+------+------+------+
| c1 | c2 | c1 | c2 |
+------+------+------+------+
| 1 | 2 | 3 | 4 |
| 1 | 2 | 5 | 6 |
| 3 | 4 | 5 | 6 |
| 5 | 6 | NULL | NULL | <--- 找不到比a.c2更大的了
+------+------+------+------+
4 rows in set (0.00 sec)
mysql> select * from a left join a as t1 on a.c2 < t1.c2 where t1.c1 is null;
+------+------+------+------+
| c1 | c2 | c1 | c2 |
+------+------+------+------+
| 5 | 6 | NULL | NULL |
+------+------+------+------+
1 row in set (0.00 sec)
mysql> select a.c2 from a left join a as t1 on a.c2 < t1.c2 where t1.c1 is null;
+------+
| c2 |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
求c2最小的数
x y
大 小
大 小
大 空 <--- 这里的大就是最小的数了，找不到比x更小的数了。
mysql> select * from a left join a as t1 on a.c2 > t1.c2;
+------+------+------+------+
| c1 | c2 | c1 | c2 |
+------+------+------+------+
| 3 | 4 | 1 | 2 |
| 5 | 6 | 1 | 2 |
| 5 | 6 | 3 | 4 |
| 1 | 2 | NULL | NULL | <--- 找不到比a.c2更小的了
+------+------+------+------+
4 rows in set (0.00 sec)
mysql> select a.c2 from a left join a as t1 on a.c2 > t1.c2 where t1.c1 is null;
+------+
| c2 |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
where放在哪里？
mysql> select * from a join b on a.c2=b.c3 where a.c2>2;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 3 | 4 | 4 | 5 |
+------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from (select * from a where a.c2>2) as t1
join b on t1.c2=b.c3;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 3 | 4 | 4 | 5 |
+------+------+------+------+
1 row in set (0.00 sec)
mysql>
你准备把where放哪里呢？
