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，睡觉...
