MySQL的内连接,左连接,右连接个人实测记录

讨论 未结
0 150
!腾飞!
!腾飞! 2019-09-03
建两张表:students学生表和subject分数表 学生表: img[https://img-blog.csdnimg.cn/20190722154351525.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NwYWNlWF85MQ==,size_16,color_FFFFFF,t_70] 成绩表: img[https://img-blog.csdnimg.cn/20190722154301601.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NwYWNlWF85MQ==,size_16,color_FFFFFF,t_70] 两表的关联字段为students的name和subject的sname 内连接(inner join)查询: SELECT t.*,b.sgrade,b.subject FROM `students` t (inner) join `subject` b on t.name = b.sname;//inner可省略不写 img[https://img-blog.csdnimg.cn/20190722155046342.png] 内连接查询结果为两表的交集!! 左连接和右连接统称为外连接。 左(外)连接(left join/left outer join)查询: SELECT t.*,b.sgrade,b.subject FROM `students` t left join `subject` b on t.name = b.sname; img[https://img-blog.csdnimg.cn/20190722160227528.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NwYWNlWF85MQ==,size_16,color_FFFFFF,t_70] 左(外)连接以左表(students表)为主表,读取主表的全部数据,右表没有对应数据的则为null。(如果两个表中数据有相同部分,只显示一个) 右(外)连接(right join/right outer join)查询: SELECT t.*,b.sgrade,b.subject FROM `students` t right join `subject` b on t.name = b.sname; img[https://img-blog.csdnimg.cn/20190722160947540.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NwYWNlWF85MQ==,size_16,color_FFFFFF,t_70] 右(外)连接正好跟左连接相反!
回帖
  • 消灭零回复
黄金赞助商
关注公众号

关注公众号,随时随地交流技术