尚硅谷 MySQL高级课程 Join

7种Join

创建数据库
CREATE SCHEMA `sggtest` ;
创建部门表
create table `sggtest`.`tbl_dept`(
`id` int(11) not null auto_increment,
`deptName` varchar(30) default null,
`locAdd` varchar(40) default null,
primary key(`id`)
)engine=INNODB auto_increment=1 default char set=utf8;
drop table if exists `sggtest`.`tbl_emp`;
create table `sggtest`.`tbl_emp`(
`id` int(11)not null auto_increment,
`name` varchar(20) default null,
`deptId` int(11) default null,
primary key(`id`),
key `fk_dept_id`(`deptId`),
constraint `fk_dept_id` foreign key (`deptId`) REFERENCES `tbl_dept`(`id`)
)auto_increment=1 default char set=utf8

外键约束 如果填入主表的外键在从表中没有,插入会出错。
比如现在tbl_emp表中,添加了外键约束,constraint没有隐掉,如果插入(‘s9’,51),MySQL会报错。

如果表A的主关键字是表B中的字段,则该字段称为表B的外键,表A称为主表,表B称为从表。

插入数据

insert into `sggtest`.tbl_dept(deptName, locAdd) values('RD',11),
('HR',12),
('MK',13),
('MIS',14),
('FD',15);

无外键

insert into `sggtest`.`tbl_emp`(name, deptId) values('z3',1),
('z3',1),
('z4',1),
('z5',1),
('w5',2),
('w6',2),
('s7',3),
('s8',4),
('s9',51);

tbl_dept

1   RD  11
2   HR  12
3   MK  13
4   MIS 14
5   FD  15

tbl_emp

1   z3  1
2   z4  1
3   z5  1
4   w5  2
5   w6  2
6   s7  3
7   s8  4
8   s9  51

sql解析
这里写图片描述

删除表中所有数据,保留表的定义。内容没了,数据结构还在。

truncate table `sggtest`.`tbl_emp`;

笛卡尔积 相当于内连接 inner join 下面两行SQL查询语句结果相同

select * from tbl_emp, tbl_dept;
select * from tbl_emp inner join tbl_dept; 

结果

1   z3  1   1   RD  11
1   z3  1   2   HR  12
1   z3  1   3   MK  13
1   z3  1   4   MIS 14
1   z3  1   5   FD  15
2   z4  1   1   RD  11
2   z4  1   2   HR  12
2   z4  1   3   MK  13
2   z4  1   4   MIS 14
2   z4  1   5   FD  15
3   z5  1   1   RD  11
3   z5  1   2   HR  12
3   z5  1   3   MK  13
3   z5  1   4   MIS 14
3   z5  1   5   FD  15
4   w5  2   1   RD  11
4   w5  2   2   HR  12
4   w5  2   3   MK  13
4   w5  2   4   MIS 14
4   w5  2   5   FD  15
5   w6  2   1   RD  11
5   w6  2   2   HR  12
5   w6  2   3   MK  13
5   w6  2   4   MIS 14
5   w6  2   5   FD  15
6   s7  3   1   RD  11
6   s7  3   2   HR  12
6   s7  3   3   MK  13
6   s7  3   4   MIS 14
6   s7  3   5   FD  15
7   s8  4   1   RD  11
7   s8  4   2   HR  12
7   s8  4   3   MK  13
7   s8  4   4   MIS 14
7   s8  4   5   FD  15
8   s9  51  1   RD  11
8   s9  51  2   HR  12
8   s9  51  3   MK  13
8   s9  51  4   MIS 14
8   s9  51  5   FD  15

A和B的公有部分
内连接 下面的两个SQL语句行数是一样的,只有哪个表的列在前的区别

select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
select * from tbl_dept a inner join tbl_emp b on a.id = b.deptId;

结果如下

1   z3  1   1   RD  11
2   z4  1   1   RD  11
3   z5  1   1   RD  11
4   w5  2   2   HR  12
5   w6  2   2   HR  12
6   s7  3   3   MK  13
7   s8  4   4   MIS 14
1   RD  11  1   z3  1
1   RD  11  2   z4  1
1   RD  11  3   z5  1
2   HR  12  4   w5  2
2   HR  12  5   w6  2
3   MK  13  6   s7  3
4   MIS 14  7   s8  4

理解起来就是在笛卡尔积中有40个结果,内连接就是找出笛卡尔积中满足on后面的语句的列。

SQL JOINS
这里写图片描述

这里写图片描述

左连接 A B公有 + A独有部分

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
1   z3  1   1   RD  11
2   z4  1   1   RD  11
3   z5  1   1   RD  11
4   w5  2   2   HR  12
5   w6  2   2   HR  12
6   s7  3   3   MK  13
7   s8  4   4   MIS 14
8   s9  51  null null null //此处是A表独有,B表没有,取null

左连接 A B公有部分 + B独有部分

select * from tbl_dept a left join tbl_emp b on a.id = b.deptId;

这个是反的。第一列和最后一列是一样的,相当于右连接。不要用这种方法,默认A在前面,B在后面,相当于图中A在左边,B在右边。好一一对照。

1   RD  11  1   z3  1
1   RD  11  2   z4  1
1   RD  11  3   z5  1
2   HR  12  4   w5  2
2   HR  12  5   w6  2
3   MK  13  6   s7  3
4   MIS 14  7   s8  4
5   FD  15  null null null      

右连接 A和B公共部分 + B独有

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
1   z3  1   1   RD  11
2   z4  1   1   RD  11
3   z5  1   1   RD  11
4   w5  2   2   HR  12
5   w6  2   2   HR  12
6   s7  3   3   MK  13
7   s8  4   4   MIS 14
            5   FD  15

左连接 + wher b.id = null条件,可以得到A部分扣掉A,B公有部分。

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
8   s9  51  null null null      

右连接 + where a.depId is null

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
null null  null     5   FD  15

A+B全有

select * from tbl_emp a full outer join tbl_dept b on a.deptId = b.id;

出错 MySQL不支持full outer join 语法

MySQL需要相加 A左连接 + 右连接 + union
union自带去重操作

select * from tbl_emp a left join  tbl_dept b on a.deptId = b.id union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
1   z3  1   1   RD  11
2   z4  1   1   RD  11
3   z5  1   1   RD  11
4   w5  2   2   HR  12
5   w6  2   2   HR  12
6   s7  3   3   MK  13
7   s8  4   4   MIS 14
8   s9  51  null null null      
null null null  5   FD  15

A,B各自独有

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
8   s9  51          
            5   FD  15
阅读更多
个人分类: 数据库
想对作者说点什么? 我来说一句

硅谷MySQL高级视频

2017年10月21日 48B 下载

MySQL高级视频教程

-

学院

2017年02月04日 17:28

硅谷MySQL高级思维导图PDF版

2017年04月01日 48B 下载

硅谷MYSQL高级下载

2017年01月19日 48B 下载

硅谷MYSQL高级学习视频

2017年11月09日 48B 下载

没有更多推荐了,返回首页

不良信息举报

尚硅谷 MySQL高级课程 Join

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭