目录 1、多表查询概览 1.1、分类 1.2、外连接的分类 1.3、常用的SQL语法标准 2、内外联接案例 2.1、初始化表 2.2、内连接 2.3、外连接案例 2.4、全连接案例 2.5、union和union all
目录
可以根据3个角度进行分类:
角度1:是否使用"="符号
角度2:连接表的数量是否大于1
角度3:多表关联时,是否只查询有关联的数据
<1>学生表:student
create table if not exists taobao.student(id int auto_increment primary key,name varchar(50) null,classid int null,age int null)comment '学生表';INSERT INTO student (id, name, classid, age) VALUES (1, '张三', 1, 18);INSERT INTO student (id, name, classid, age) VALUES (2, '李四', 1, 18);INSERT INTO student (id, name, classid, age) VALUES (3, '王五', 2, 17);INSERT INTO student (id, name, classid, age) VALUES (4, '老六', 2, 18);INSERT INTO student (id, name, classid, age) VALUES (5, '七七', null, 17);INSERT INTO student (id, name, classid, age) VALUES (6, '二流子', null, 19);INSERT INTO student (id, name, classid, age) VALUES (7, '巴哥', null, 18);
<2>班级表:classinfo
create table if not exists taobao.classinfo(classid int auto_increment primary key,name varchar(100) null)comment '班级表';INSERT INTO classinfo (name) VALUES ('高一1班');INSERT INTO classinfo (name) VALUES ('高一2班');INSERT INTO classinfo (name) VALUES ('高一3班');
需求:查询已分配的学生信息,如:学生基本信息,所在班级名称
<1>SQL92内连接写法:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1,classinfo t2where t1.classid=t2.classid
<2>SQL99内连接写法:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1join classinfo t2on t1.classid=t2.classid
结果:
需求:查询所有的学生信息,并查出学生所对应的班级名称
【注意:多表查询时,当查询一个表所有数据,该查询语句一定是外连接】
<1>SQL92外连接写法:
注意:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1,classinfo t2where t1.classid=t2.classid(+)
<2>SQL99外连接写法:
左连接写法:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1left join classinfo t2 --注意:left join是缩写,也可以写为:left outer joinon t1.classid=t2.classid
右连接写法:
select t2.id -- 学生ID ,t2.name -- 学生姓名 ,t2.age -- 学生年龄 ,t1.name -- 班级名称from classinfo t1right join student t2on t1.classid=t2.classid
结果:
需求:查询学生表中的所有信息,并关联班级表信息及显示未关联的班级表信息
SQL99全连接写法(Oracle):
关键字:full join ... on ... 或者 full outer join ... on ...
注意:MySQL不支持全连接,但是Oracle支持
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1full join classinfo t2on t1.classid=t2.classid
mysql实现全连接,需要使用关键字"uNIOn"或者"union all"
union:联合、合并的意思
union:对两个查询的结果集,进行合并操作,会对重复的数据进行去重,同时进行默认规则(主键升序)的排序(因此效率比较低)。
union all:对两个查询的结果集,进行合并操作,不对数据进行去重,也不进行排序,直接把两个结果进行合并(效率高)。
例如:我们把学生表查询两次,并使用union或union all进行合并
<1>union 语句
select * from studentunion -- 会进行去重操作select * from student
结果:
<2>union all 语句
select * from studentunion all -- 不去重select * from student
结果:
注意:
需求:查询学生表中的所有信息,并关联班级表信息及显示未关联的班级表信息
实现方式有多种,这里我使用:
代码如下:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1left join classinfo t2 -- 注意:left join是缩写,也可以写为:left outer joinon t1.classid=t2.classidunion allselect null -- null:这里设置为null,只是为了与上一个select的结果行字段(数量)进行匹配,以下2个null作用一样 ,null ,null ,t1.namefrom classinfo t1where t1.classid not in ( select distinct classid -- distinct表示去重 from student t2 where t2.classid is not null )
结果:
上述图对应7种多表查询,是面试及实际开发中,必会的操作,这里就不多言了
注意:当关联表的数量超过3个时,禁止使用join,因为一个join相当于一个for,性能会很差
<1>natural join
如:上面的内连接SQL为:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1join classinfo t2on t1.classid=t2.classid
使用natural join进行改造,如下:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1natural join classinfo t2 --自然连接
结果:
查询到了0条数据,这是因为:
也就是上述的自然连接,转义为内连接的SQL为:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1join classinfo t2on t1.classid=t2.classidand t1.name = t2.name -- 这个条件也被自然连接附带上了
因此,使用natural join的前提条件就是:
综上:在实际开发中,我们应当避免使用natural join,造成表与表之间的耦合较高
<2>using
等值条件的一种优化写法
语法:
- using(多表关联的字段名称)
前提:
- 多表关联时,关联字段的名称必须相同
- 注意:这种写法公司一般不给使用,当某个字段改名时,很难定位错误
如:上面的内连接SQL为:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1join classinfo t2on t1.classid=t2.classid
使用using:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1join classinfo t2using(classid)
结果:
来源地址:https://blog.csdn.net/weixin_42675423/article/details/130067465
--结束END--
本文标题: MySQL多表查询内连接外连接详解,join、left join、right join、full join的使用
本文链接: https://www.lsjlt.com/news/391512.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0