1、首先创建student库

create database student;

2、 在数据库中创建boy表 和 girl表,

mysql> create table boy( 
    ->  boyId int primary key auto_increment, 
    -> boyName varchar(100) not null, 
    -> age int, 
    -> connectId int) 
    -> auto_increment = 1;
mysql> create table girl( 
    -> girlid int primary key auto_increment, 
    -> girlName varchar(100) not null, 
    -> age int, 
    -> connectId int) 
    -> auto_increment = 101;

① 对boy表插入部分数据

mysql> insert into boy(boyName,age) values('李易峰',30); 
mysql> insert into boy(boyName,age) values('吴奇隆',40); 
mysql> insert into boy(boyName,age) values('周润发',55); 
mysql> insert into boy(boyName,age) values('周星驰',45); 
mysql> insert into boy(boyName,age) values('刘德华',47); 
mysql> insert into boy(boyName,age) values('成龙',60);
复制代码
mysql> select * from boy; 
+-------+---------+-----+-----------+ 
| boyId | boyName | age | connectId | 
+-------+---------+-----+-----------+ 
|     1 | 李易峰  |  30 | NULL      | 
|     2 | 吴奇隆  |  40 | NULL      | 
|     3 | 周润发  |  55 | NULL      | 
|     4 | 周星驰  |  45 | NULL      | 
|     5 | 刘德华  |  47 | NULL      | 
|     6 | 成龙    |  60 | NULL      | 
+-------+---------+-----+-----------+
复制代码

② 对girl表插入部分数据

复制代码
mysql> insert into girl(girlName,age) values('刘亦菲',31); 
mysql> insert into girl(girlName,age) values('唐嫣',35); 
mysql> insert into girl(girlName,age) values('刘诗诗',38); 
mysql> insert into girl(girlName,age) values('马苏',30); 
mysql> insert into girl(girlName,age) values('杨幂',39); 
mysql> insert into girl(girlName,age) values('赵丽颖',29);
复制代码
复制代码
mysql> select * from girl; 
+--------+----------+-----+-----------+ 
| girlid | girlName | age | connectId | 
+--------+----------+-----+-----------+ 
|    101 | 刘亦菲   |  31 | NULL      | 
|    102 | 唐嫣     |  35 | NULL      | 
|    103 | 刘诗诗   |  38 | NULL      | 
|    104 | 马苏     |  30 | NULL      | 
|    105 | 杨幂     |  39 | NULL      | 
|    106 | 赵丽颖   |  29 | NULL      | 
+--------+----------+-----+-----------+
复制代码

3、修改表中的connectId,利用boy表中的boyId 连接girl表中的girlId

 ① boyId=2 连接 girlId = 102

mysql> update boy set connectId=102 where boyId=2;

② boyId=3 连接 girlId = 104

mysql> update boy set connectId=104 where boyId=3;

③  boyId=6 连接 girlId = 105

mysql> update boy set connectId=105 where boyId=6;

得出boy表

复制代码
mysql> select * from boy; 
+-------+---------+-----+-----------+ 
| boyId | boyName | age | connectId | 
+-------+---------+-----+-----------+ 
|     1 | 李易峰  |  30 | NULL      | 
|     2 | 吴奇隆  |  40 |       102 | 
|     3 | 周润发  |  55 |       104 | 
|     4 | 周星驰  |  45 | NULL      | 
|     5 | 刘德华  |  47 | NULL      | 
|     6 | 成龙    |  60 |       105 | 
+-------+---------+-----+-----------+
复制代码

4、修改表中的connectId,利用girl表中的girlId 连接boy表中的boyId

① girlId=101 连接 boyd = 3

mysql> update girl set connectId=3 where girlId=101;

② girlId=103 连接 boyd = 4

mysql> update girl set connectId=4 where girlId=103;

③ girlId=105 连接 boyd = 6

update girl set connectId=6 where girlId=105;

得出girl表

复制代码
mysql> select * from girl; 
+--------+----------+-----+-----------+ 
| girlid | girlName | age | connectId | 
+--------+----------+-----+-----------+ 
|    101 | 刘亦菲   |  31 |         3 | 
|    102 | 唐嫣     |  35 | NULL      | 
|    103 | 刘诗诗   |  38 |         4 | 
|    104 | 马苏     |  30 | NULL      | 
|    105 | 杨幂     |  39 |         6 | 
|    106 | 赵丽颖   |  29 | NULL      | 
+--------+----------+-----+-----------+
复制代码

5、连接,分为 内连接、左连接、有连接, where形式连接

① where连接,匹配的数据显示出来

  boy表连接girl表

复制代码
mysql> select b.*,g.* 
    -> from boy b,girl g 
    -> where b.connectId = g.girlId; 
+-------+---------+-----+-----------+--------+----------+-----+-----------+ 
| boyId | boyName | age | connectId | girlid | girlName | age | connectId | 
+-------+---------+-----+-----------+--------+----------+-----+-----------+ 
|     2 | 吴奇隆  |  40 |       102 |    102 | 唐嫣     |  35 | NULL      | 
|     3 | 周润发  |  55 |       104 |    104 | 马苏     |  30 | NULL      | 
|     6 | 成龙    |  60 |       105 |    105 | 杨幂     |  39 |         6 | 
+-------+---------+-----+-----------+--------+----------+-----+-----------+ 
3 rows in set
复制代码

  girl表连接boy表

复制代码
mysql> select b.*,g.* 
    -> from boy b,girl g 
    -> where g.connectId = b.boyId; 
+-------+---------+-----+-----------+--------+----------+-----+-----------+ 
| boyId | boyName | age | connectId | girlid | girlName | age | connectId | 
+-------+---------+-----+-----------+--------+----------+-----+-----------+ 
|     3 | 周润发  |  55 |       104 |    101 | 刘亦菲   |  31 |         3 | 
|     4 | 周星驰  |  45 | NULL      |    103 | 刘诗诗   |  38 |         4 | 
|     6 | 成龙    |  60 |       105 |    105 | 杨幂     |  39 |         6 | 
+-------+---------+-----+-----------+--------+----------+-----+-----------+
复制代码

 ② 内连接,匹配的显示出来

  boy - > girl

复制代码
mysql> select b.*,g.* 
    -> from boy b inner join girl g 
    -> on b.connectId = g.girlId; 
+-------+---------+-----+-----------+--------+----------+-----+-----------+ 
| boyId | boyName | age | connectId | girlid | girlName | age | connectId | 
+-------+---------+-----+-----------+--------+----------+-----+-----------+ 
|     2 | 吴奇隆  |  40 |       102 |    102 | 唐嫣     |  35 | NULL      | 
|     3 | 周润发  |  55 |       104 |    104 | 马苏     |  30 | NULL      | 
|     6 | 成龙    |  60 |       105 |    105 | 杨幂     |  39 |         6 | 
+-------+---------+-----+-----------+--------+----------+-----+-----------+
复制代码

  girl - > boy

复制代码
mysql> select b.*,g.* 
    -> from girl g inner join boy b 
    -> on g.connectId = b.boyId; 
+-------+---------+-----+-----------+--------+----------+-----+-----------+ 
| boyId | boyName | age | connectId | girlid | girlName | age | connectId | 
+-------+---------+-----+-----------+--------+----------+-----+-----------+ 
|     3 | 周润发  |  55 |       104 |    101 | 刘亦菲   |  31 |         3 | 
|     4 | 周星驰  |  45 | NULL      |    103 | 刘诗诗   |  38 |         4 | 
|     6 | 成龙    |  60 |       105 |    105 | 杨幂     |  39 |         6 | 
+-------+---------+-----+-----------+--------+----------+-----+-----------+
复制代码

 ③ 左外连接 left    join     on  ,哪个表在左边就偏向哪个表,把该表的数据全部列出来,不管匹配不匹配都显示

复制代码
mysql>  select b.*,g.* 
    -> from boy b left join girl g 
    -> on b.connectId = g.girlId; 
+-------+---------+-----+-----------+--------+----------+------+-----------+ 
| boyId | boyName | age | connectId | girlid | girlName | age  | connectId | 
+-------+---------+-----+-----------+--------+----------+------+-----------+ 
|     1 | 李易峰  |  30 | NULL      | NULL   | NULL     | NULL | NULL      | 
|     2 | 吴奇隆  |  40 |       102 |    102 | 唐嫣     |   35 | NULL      | 
|     3 | 周润发  |  55 |       104 |    104 | 马苏     |   30 | NULL      | 
|     4 | 周星驰  |  45 | NULL      | NULL   | NULL     | NULL | NULL      | 
|     5 | 刘德华  |  47 | NULL      | NULL   | NULL     | NULL | NULL      | 
|     6 | 成龙    |  60 |       105 |    105 | 杨幂     |   39 |         6 | 
+-------+---------+-----+-----------+--------+----------+------+-----------+
复制代码

④ 右外连接 right   join     on  ,哪个表在右边就偏向哪个表,把该表的数据全部列出来,不管匹配不匹配都显示

复制代码
mysql>  select b.*,g.* 
    -> from boy b right join girl g 
    -> on b.connectId = g.girlId; 
+-------+---------+------+-----------+--------+----------+-----+-----------+ 
| boyId | boyName | age  | connectId | girlid | girlName | age | connectId | 
+-------+---------+------+-----------+--------+----------+-----+-----------+ 
|     2 | 吴奇隆  |   40 |       102 |    102 | 唐嫣     |  35 | NULL      | 
|     3 | 周润发  |   55 |       104 |    104 | 马苏     |  30 | NULL      | 
|     6 | 成龙    |   60 |       105 |    105 | 杨幂     |  39 |         6 | 
| NULL  | NULL    | NULL | NULL      |    101 | 刘亦菲   |  31 |         3 | 
| NULL  | NULL    | NULL | NULL      |    103 | 刘诗诗   |  38 |         4 | 
| NULL  | NULL    | NULL | NULL      |    106 | 赵丽颖   |  29 | NULL      | 
+-------+---------+------+-----------+--------+----------+-----+-----------+
复制代码

 ⑤  全连接(笛卡尔积),MySQL不支持 full join,但是可以通过  union来实现连接结果集(剔除重复数据),会显示两张表的各自匹配的数据,union all 不会剔除重复数据

复制代码
mysql> select b.*,g.* 
    -> from boy b left join girl g 
    -> on b.connectId = g.girlId 
    -> union 
    -> select b.*,g.* 
    -> from boy b right join girl g 
    -> on b.connectId = g.girlId; 
+-------+---------+------+-----------+--------+----------+------+-----------+ 
| boyId | boyName | age  | connectId | girlid | girlName | age  | connectId | 
+-------+---------+------+-----------+--------+----------+------+-----------+ 
|     1 | 李易峰  |   30 | NULL      | NULL   | NULL     | NULL | NULL      | 
|     2 | 吴奇隆  |   40 |       102 |    102 | 唐嫣     |   35 | NULL      | 
|     3 | 周润发  |   55 |       104 |    104 | 马苏     |   30 | NULL      | 
|     4 | 周星驰  |   45 | NULL      | NULL   | NULL     | NULL | NULL      | 
|     5 | 刘德华  |   47 | NULL      | NULL   | NULL     | NULL | NULL      | 
|     6 | 成龙    |   60 |       105 |    105 | 杨幂     |   39 |         6 | 
| NULL  | NULL    | NULL | NULL      |    101 | 刘亦菲   |   31 |         3 | 
| NULL  | NULL    | NULL | NULL      |    103 | 刘诗诗   |   38 |         4 | 
| NULL  | NULL    | NULL | NULL      |    106 | 赵丽颖   |   29 | NULL      | 
+-------+---------+------+-----------+--------+----------+------+-----------+
复制代码

 2018年1月16日02:12:02,睡觉...


发布评论
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

mysql全连接知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。