mysql使用join连接多个表,mysqlleftjoin多表查询语句
终极管理员 知识笔记 63阅读
目录
1、多表查询概览
1.1、分类
1.2、外连接的分类
1.3、常用的SQL语法标准
2、内外联接案例
2.1、初始化表
2.2、内连接
2.3、外连接案例
2.4、全连接案例
2.5、union和union all
2.6、实现MySQL全连接
2.7、内外连接面试基础
2.8、SQL99多表查询新特性
1、多表查询概览 1.1、分类
可以根据3个角度进行分类
角度1是否使用符号
等值接连where条件中表字段与表字段直接使用等于符号进行判断非等值连接where条件中表字段与表字段使用非符号如<小于等于、>大于等于、between and等等。角度2连接表的数量是否大于1
自连接一张表直接的关联查询自己表连接自己进行查询如菜单表查子级非自连接多表关联查询角度3多表关联时是否只查询有关联的数据
内连接合并具有同一列的两个以上的表的行结果集中不包含一个表与另一个表不匹配的行外连接合并具有同一列的两个以上的表的行结果集中包含一个表与另一个表匹配的行之外还包含了左表 或 右表不匹配的行 1.2、外连接的分类 左外连接left outer join可缩写为left join两个表连接过程中除了返回满足条件的行以外还会返回左表中不满足条件的行这种连接称为左连接右外连接right outer join可缩写为right join两个表连接过程中除了返回满足条件的行以外还会返回右表中不满足条件的行这种连接称为右连接全连接full outer join可缩写为full join又称为满外连接两个表连接过程中返回两表直接的所有数据这种连接称为全连接 1.3、常用的SQL语法标准 SQL921992发布的是数据库的一个ANSI/ISO标准偶尔使用SQL991999发布的是数据库的一个ANSI/ISO标准现在开发中主流标准 ANSI美国国家标准学会ISO国际标准化组织 2、内外联接案例 2.1、初始化表<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.classidt2.classid<2>SQL99内连接写法
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1join classinfo t2on t1.classidt2.classid结果
2.3、外连接案例需求查询所有的学生信息并查出学生所对应的班级名称
【注意多表查询时当查询一个表所有数据该查询语句一定是外连接】
<1>SQL92外连接写法
注意
这种写法MySQL不支持但在Oracle中支持在不需要查询表中所有数据的那张表后添加”()”表示外连接理解为这个表只是附加的
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1,classinfo t2where t1.classidt2.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.classidt2.classid右连接写法
select t2.id -- 学生ID ,t2.name -- 学生姓名 ,t2.age -- 学生年龄 ,t1.name -- 班级名称from classinfo t1right join student t2on t1.classidt2.classid结果
2.4、全连接案例需求查询学生表中的所有信息并关联班级表信息及显示未关联的班级表信息
在SQL92中并不直接支持全连接语法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.classidt2.classidMySQL实现全连接需要使用关键字union或者union all
2.5、union和union allunion联合、合并的意思
union对两个查询的结果集进行合并操作会对重复的数据进行去重同时进行默认规则主键升序的排序因此效率比较低。
union all对两个查询的结果集进行合并操作不对数据进行去重也不进行排序直接把两个结果进行合并效率高。
例如我们把学生表查询两次并使用union或union all进行合并
<1>union 语句
select * from studentunion -- 会进行去重操作select * from student结果
<2>union all 语句
select * from studentunion all -- 不去重select * from student结果
注意
union和union all使用时select下的字段数量必须一致否则会报错 2.6、实现MySQL全连接需求查询学生表中的所有信息并关联班级表信息及显示未关联的班级表信息
实现方式有多种这里我使用
首先查询出学生表所有信息并显示对应的班级表信息其次查询班级表中classid不在学生表中的数据把上述结果使用union all合并代码如下
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1left join classinfo t2 -- 注意left join是缩写也可以写为left outer joinon t1.classidt2.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 )结果
2.7、内外连接面试基础上述图对应7种多表查询是面试及实际开发中必会的操作这里就不多言了
A看作是学生表B看作是班级表注意当关联表的数量超过3个时禁止使用join因为一个join相当于一个for性能会很差
2.8、SQL99多表查询新特性<1>natural join
自然连接就是等值内连接会自动查询两张连接表中所有相同的字段然后进行等值连接如上面的内连接SQL为
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1join classinfo t2on t1.classidt2.classid使用natural join进行改造如下
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1natural join classinfo t2 --自然连接结果
查询到了0条数据这是因为
natural join 关联多张表时会自动根据表中相同的字段名称去匹配上述student表中classid班级编号、name学生姓名与classinfo表中的 classid班级编号、name班级名称是一样的字段而班级名称不可能与学生姓名相等所以查询不到数据也就是上述的自然连接转义为内连接的SQL为
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1join classinfo t2on t1.classidt2.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.classidt2.classid使用using:
select t1.id -- 学生ID ,t1.name -- 学生姓名 ,t1.age -- 学生年龄 ,t2.name -- 班级名称from student t1join classinfo t2using(classid)结果