遇见YY

导航

 

基本表

create table info(post char(16),id int);
create table user(name char(16),id int);
mysql> select * from user;
+------+------+
| name | id   |
+------+------+
| ff   |    0 |
| ll   |    1 |
| tt   |    2 |
| hh   |    3 |
| rr   |    3 |
| nn   |    2 |
| mm   |    4 |
| ss   |    5 |
+------+------+
8 rows in set (0.00 sec)
mysql> select * from info;
+---------+------+
| post    | id   |
+---------+------+
| student |    0 |
| teacher |    1 |
| worker  |    2 |
| farmer  |    3 |
+---------+------+
4 rows in set (0.00 sec)

交叉连接(Cross Join)

mysql> select * from user cross join info;
+------+------+---------+------+
| name | id   | post    | id   |
+------+------+---------+------+
| ff   |    0 | student |    0 |
| ff   |    0 | teacher |    1 |
| ff   |    0 | worker  |    2 |
| ff   |    0 | farmer  |    3 |
| ll   |    1 | student |    0 |
| ll   |    1 | teacher |    1 |
| ll   |    1 | worker  |    2 |
| ll   |    1 | farmer  |    3 |
| tt   |    2 | student |    0 |
| tt   |    2 | teacher |    1 |
| tt   |    2 | worker  |    2 |
| tt   |    2 | farmer  |    3 |
| hh   |    3 | student |    0 |
| hh   |    3 | teacher |    1 |
| hh   |    3 | worker  |    2 |
| hh   |    3 | farmer  |    3 |
| rr   |    3 | student |    0 |
| rr   |    3 | teacher |    1 |
| rr   |    3 | worker  |    2 |
| rr   |    3 | farmer  |    3 |
| nn   |    2 | student |    0 |
| nn   |    2 | teacher |    1 |
| nn   |    2 | worker  |    2 |
| nn   |    2 | farmer  |    3 |
| mm   |    4 | student |    0 |
| mm   |    4 | teacher |    1 |
| mm   |    4 | worker  |    2 |
| mm   |    4 | farmer  |    3 |
| ss   |    5 | student |    0 |
| ss   |    5 | teacher |    1 |
| ss   |    5 | worker  |    2 |
| ss   |    5 | farmer  |    3 |
+------+------+---------+------+
32 rows in set (0.00 sec)

内连接(inner join)

mysql> select * from user inner join info on user.id = info.id;
+------+------+---------+------+
| name | id   | post    | id   |
+------+------+---------+------+
| ff   |    0 | student |    0 |
| ll   |    1 | teacher |    1 |
| tt   |    2 | worker  |    2 |
| hh   |    3 | farmer  |    3 |
| rr   |    3 | farmer  |    3 |
| nn   |    2 | worker  |    2 |
+------+------+---------+------+
6 rows in set (0.00 sec)

左外连接(left join)

mysql> select * from user left join info on user.id = info.id;
+------+------+---------+------+
| name | id   | post    | id   |
+------+------+---------+------+
| ff   |    0 | student |    0 |
| ll   |    1 | teacher |    1 |
| tt   |    2 | worker  |    2 |
| nn   |    2 | worker  |    2 |
| hh   |    3 | farmer  |    3 |
| rr   |    3 | farmer  |    3 |
| mm   |    4 | NULL    | NULL |
| ss   |    5 | NULL    | NULL |
+------+------+---------+------+
8 rows in set (0.00 sec)

右外连接(right join)

mysql> select * from user right join info on user.id = info.id;
+------+------+---------+------+
| name | id   | post    | id   |
+------+------+---------+------+
| ff   |    0 | student |    0 |
| ll   |    1 | teacher |    1 |
| tt   |    2 | worker  |    2 |
| hh   |    3 | farmer  |    3 |
| rr   |    3 | farmer  |    3 |
| nn   |    2 | worker  |    2 |
+------+------+---------+------+
6 rows in set (0.00 sec)

联合查询(union(去重) union all)

mysql> select * from user union select * from info;
+---------+------+
| name    | id   |
+---------+------+
| ff      |    0 |
| ll      |    1 |
| tt      |    2 |
| hh      |    3 |
| rr      |    3 |
| nn      |    2 |
| mm      |    4 |
| ss      |    5 |
| student |    0 |
| teacher |    1 |
| worker  |    2 |
| farmer  |    3 |
+---------+------+
12 rows in set (0.00 sec)

mysql> select * from user union all select * from info;
+---------+------+
| name    | id   |
+---------+------+
| ff      |    0 |
| ll      |    1 |
| tt      |    2 |
| hh      |    3 |
| rr      |    3 |
| nn      |    2 |
| mm      |    4 |
| ss      |    5 |
| student |    0 |
| teacher |    1 |
| worker  |    2 |
| farmer  |    3 |
+---------+------+
12 rows in set (0.00 sec)

全连接(full join)[mysql不支持全连接,但是可用union或者union all间接实现]

mysql> select * from user  left join info on user.id = info.id 
union
    select * from user right join info on user.id = info.id; +------+------+---------+------+ | name | id | post | id | +------+------+---------+------+ | ff | 0 | student | 0 | | ll | 1 | teacher | 1 | | tt | 2 | worker | 2 | | nn | 2 | worker | 2 | | hh | 3 | farmer | 3 | | rr | 3 | farmer | 3 | | mm | 4 | NULL | NULL | | ss | 5 | NULL | NULL | +------+------+---------+------+ 8 rows in set (0.00 sec)
posted on 2020-09-16 16:51  一骑红尘妃子笑!  阅读(383)  评论(0编辑  收藏  举报