联合查询是工作中用的最多的查询,而且面试的时候也非常爱考,因为SQL没啥考的难点,联合查询在SQL中稍微复杂。一、联合查询的简单理解联合查询是联合多个表进行查询设计数据是把表进行拆分为了消除表中的字段的依赖关系比如部分函数依赖传递依赖这时会导致一条SQL查出来的数据对于业务来说是不完整的我们就可以使用联合查询把关系中的数据全部查出来在一个数据行中显示详细信息。这个结果集才是我们想要的笛卡尔积现象当两张表进行连接查询时没有任何条件进行限制最终查询结果条数是两张表记录的乘积。怎么避免笛卡尔积现象添加连接条件过滤。联合查询时MYSQL是如何执行的1.取多张表的笛卡尔积对多张表进行笛卡尔积的过程先从第一张表中取一条记录然后再与第二张表的第一条记录进行组合生成一条新的记录先从第一张表中取一条记录然后再与第二张表的第二条记录进行组合生成一条新的记录……最后得到的结果就是一个全排列结果集mysql select * from student,class; ------------------------------------ | id | name | class_id | id | name | ------------------------------------ | 3 | 张三 | 1 | 2 | java112 | | 3 | 张三 | 1 | 1 | java113 | | 4 | 李四 | 1 | 2 | java112 | | 4 | 李四 | 1 | 1 | java113 | | 5 | 王五 | 2 | 2 | java112 | | 5 | 王五 | 2 | 1 | java113 | | 7 | 钱七 | 2 | 2 | java112 | | 7 | 钱七 | 2 | 1 | java113 | | 9 | 钱七1 | 2 | 2 | java112 | | 9 | 钱七1 | 2 | 1 | java113 | ------------------------------------ 10 rows in set (0.00 sec)通过观察两张表取笛卡尔积之后有些数据是无效数据如何过滤掉这些无效数据2.通过连接条件过滤掉无效数据两个表之间是有主外键关系只需要判断两个表中主外键字段是否相等即可mysql select * from student,class where student.class_idclass.id; ------------------------------------ | id | name | class_id | id | name | ------------------------------------ | 3 | 张三 | 1 | 1 | java113 | | 4 | 李四 | 1 | 1 | java113 | | 5 | 王五 | 2 | 2 | java112 | | 7 | 钱七 | 2 | 2 | java112 | | 9 | 钱七1 | 2 | 2 | java112 | ------------------------------------ 5 rows in set (0.00 sec)可以通过表名.列名的方式来解决这个问题3.通过指定列查询来精减结果集查询列表中通过表名.列名的方式指定要查询字段mysql select student.id,student.name,class.name from student,class where student.class_idclass.id; ---------------------- | id | name | name | ---------------------- | 3 | 张三 | java113 | | 4 | 李四 | java113 | | 5 | 王五 | java112 | | 7 | 钱七 | java112 | | 9 | 钱七1 | java112 | ---------------------- 5 rows in set (0.00 sec)通过给表名起别名的方式来简化SQL语句mysql select s.id,s.name,c.name from student s,class c where s.class_idc.id; ---------------------- | id | name | name | ---------------------- | 3 | 张三 | java113 | | 4 | 李四 | java113 | | 5 | 王五 | java112 | | 7 | 钱七 | java112 | | 9 | 钱七1 | java112 | ---------------------- 5 rows in set (0.00 sec)联合查询也叫表连接查询首先确定那几张表要参与查询根据表与表之间的主外键关系确定过滤条件精减查询字段得到想要的结果二、内连接内连接符合条件的数据都查询出来这个结果就是内连接的结果。一语法1select字段from表1别名1,表2别名2where连接条件and其他条件;2select字段from表1别名1[inner]join表2别名2on连接条件where其他条件;mysql select s.id,s.name,c.name from student s inner join class c on s.class_idc.id; ---------------------- | id | name | name | ---------------------- | 3 | 张三 | java113 | | 4 | 李四 | java113 | | 5 | 王五 | java112 | | 7 | 钱七 | java112 | | 9 | 钱七1 | java112 | ---------------------- 5 rows in set (0.00 sec)inner 可以省略join两边是参与查询的表on后面跟的是连接条件mysql select s.id,s.name,c.name from student s join class c on s.class_idc.id; ---------------------- | id | name | name | ---------------------- | 3 | 张三 | java113 | | 4 | 李四 | java113 | | 5 | 王五 | java112 | | 7 | 钱七 | java112 | | 9 | 钱七1 | java112 | ---------------------- 5 rows in set (0.00 sec)二示例首先确定那几张表要参与查询根据表与表之间的主外键关系确定过滤条件精减查询字段得到想要的结果查询“唐三藏”同学的成绩mysql select s.name, sc.score from student s join score sc on sc.student_id s.id where s.name 唐三藏; ------------------ | name | score | ------------------ | 唐三藏 | 70.5 | | 唐三藏 | 98.5 | | 唐三藏 | 33 | | 唐三藏 | 98 | ------------------ 4 rows in set (0.00 sec)查询所有同学的总成绩及同学的个⼈信息当一条SQL语句中有group by子句时select后边只能跟两种东西一种是参加分组的字段以及分组函数mysql select s.name, sum(sc.score) from student s, score sc where sc.student_id s.id group by (s.id); -------------------------- | name | sum(sc.score) | -------------------------- | 唐三藏 | 300 | | 孙悟空 | 119.5 | | 猪悟能 | 200 | | 沙悟净 | 218 | | 宋江 | 118 | | 武松 | 178 | | 李逹 | 172 | -------------------------- 7 rows in set (0.00 sec)联合查询步骤细化之后确定查询中涉及到哪些表也就是说要查询的数据保存在哪些表中对目标表取笛卡尔积确定连接条件确定对整个结果集的过滤条件精减查询字段三、外连接外连接除了将符合条件的数据都查询出来之外还要无条件的将其中一张表的所有的都展现出来叫做外连接。外连接的查询结果条数 内连接的查询结果条数在外连接的时候如果对方表没有与之匹配的数据则自动模拟NULL匹配。外连接分为左外连接和右外连接。如果联合查询左侧的表完全显示我们就说是左外连接右侧的表外全显示我们就说是右外连接。mysql select * from class; ------------- | id | name | ------------- | 1 | java113 | | 2 | java112 | | 3 | java114 | ------------- 3 rows in set (0.00 sec) mysql select * from student; ----------------------- | id | name | class_id | ----------------------- | 3 | 张三 | 1 | | 4 | 李四 | 1 | | 5 | 王五 | 2 | | 7 | 钱七 | 2 | | 9 | 钱七1 | 2 | ----------------------- 5 rows in set (0.00 sec)当前学生表中的记录并没有一个学生的班级是java114mysql select * from student,class where student.class_idclass.id; ------------------------------------ | id | name | class_id | id | name | ------------------------------------ | 3 | 张三 | 1 | 1 | java113 | | 4 | 李四 | 1 | 1 | java113 | | 5 | 王五 | 2 | 2 | java112 | | 7 | 钱七 | 2 | 2 | java112 | | 9 | 钱七1 | 2 | 2 | java112 | ------------------------------------ 5 rows in set (0.00 sec)使用内连接时并没有java114班的数据一语法--左外连接表1完全显⽰select字段名from表名1leftjoin表名2on连接条件;--右外连接表2完全显⽰select字段from表名1rightjoin表名2on连接条件;mysql select * from student right join class on student.class_idclass.id; -------------------------------------- | id | name | class_id | id | name | -------------------------------------- | 4 | 李四 | 1 | 1 | java113 | | 3 | 张三 | 1 | 1 | java113 | | 9 | 钱七1 | 2 | 2 | java112 | | 7 | 钱七 | 2 | 2 | java112 | | 5 | 王五 | 2 | 2 | java112 | | NULL | NULL | NULL | 3 | java114 | -------------------------------------- 6 rows in set (0.00 sec)没有学生是java114班的记录java114 右表真实存在的记录class右连接是以join右边的表为基准这个表中的数据会全部显示出来左边的表没有与之匹配的记录全部分NULL去填充。二示例查询没有参加考试的同学信息在同学表中有记录在分数表中没有该同学对应的记录# 左连接以JOIN左边的表为基准左表显⽰全部记录右表中没有匹配的记录⽤NULL填充 mysql select s.id, s.name, s.sno, s.age, sc.* from student s LEFT JOIN score sc on sc.student_id s.id; -------------------------------------------------------------------- | id | name | sno | age | id | score | student_id | course_id | -------------------------------------------------------------------- | 1 | 唐三藏 | 100001 | 18 | 1 | 70.5 | 1 | 1 | | 1 | 唐三藏 | 100001 | 18 | 2 | 98.5 | 1 | 3 | | 1 | 唐三藏 | 100001 | 18 | 3 | 33 | 1 | 5 | | 1 | 唐三藏 | 100001 | 18 | 4 | 98 | 1 | 6 | | 2 | 孙悟空 | 100002 | 18 | 5 | 60 | 2 | 1 | | 2 | 孙悟空 | 100002 | 18 | 6 | 59.5 | 2 | 5 | | 3 | 猪悟能 | 100003 | 18 | 7 | 33 | 3 | 1 | | 3 | 猪悟能 | 100003 | 18 | 8 | 68 | 3 | 3 | | 3 | 猪悟能 | 100003 | 18 | 9 | 99 | 3 | 5 | | 4 | 沙悟净 | 100004 | 18 | 10 | 67 | 4 | 1 | | 4 | 沙悟净 | 100004 | 18 | 11 | 23 | 4 | 3 | | 4 | 沙悟净 | 100004 | 18 | 12 | 56 | 4 | 5 | | 4 | 沙悟净 | 100004 | 18 | 13 | 72 | 4 | 6 | | 5 | 宋江 | 200001 | 18 | 14 | 81 | 5 | 1 | | 5 | 宋江 | 200001 | 18 | 15 | 37 | 5 | 5 | | 6 | 武松 | 200002 | 18 | 16 | 56 | 6 | 2 | | 6 | 武松 | 200002 | 18 | 17 | 43 | 6 | 4 | | 6 | 武松 | 200002 | 18 | 18 | 79 | 6 | 6 | | 7 | 李逹 | 200003 | 18 | 19 | 80 | 7 | 2 | | 7 | 李逹 | 200003 | 18 | 20 | 92 | 7 | 6 | | 8 | 不想毕业 | 200004 | 18 | NULL | NULL | NULL | NULL | -------------------------------------------------------------------- 21 rows in set (0.00 sec) # 过滤参加了考试的同学 mysql select s.* from student s LEFT JOIN score sc on sc.student_id s.id where sc.score is null; --------------------------------------------------------------- | id | name | sno | age | gender | enroll_date | class_id | --------------------------------------------------------------- | 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 1 | --------------------------------------------------------------- 1 row in set (0.00 sec)四、自连接一应用场景自连接一张表看作两张表自己与自己进行表连接可以把行转化为列再查询的时候可以使用where条件进行过滤也就是说可以实现行与行之间的比较功能mysql select * from exam; -------------------------------------------- | id | name | chinese | math | english | -------------------------------------------- | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | | 3 | 猪悟能 | 88.0 | 98.0 | 90.0 | | 4 | 曹孟德 | 70.0 | 60.0 | 67.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | | 7 | 宋公明 | 75.0 | 65.0 | 30.0 | | 8 | 孙行者 | 87.5 | 78.0 | 77.0 | | 13 | 赵云 | 40.0 | 80.0 | 90.0 | | 14 | 黄忠 | 60.0 | 70.0 | 50.0 | | NULL | 测试用户 | 90.0 | 80.0 | NULL | -------------------------------------------- 10 rows in set (0.00 sec)这样的表设计可以在一行中进行列与列之间的比较二示例案例找出每个员工的直属领导要求显示员工名、领导名。确定涉及的表 员工表领导表取笛卡尔积select e.ename 员工名, l.ename 领导名 from emp e join emp l on e.mgr l.empno;思路将emp表当做员工表 e将emp表当做领导表 l五、子查询子查询是把一条SQL的查询结果当做另一条SQL的查询条件可以嵌套很多很多层也叫嵌套查询。子查询可以出现在哪里select...(select)from...(select)where...(select)一语法select*fromtable1wherecol_name1 { |IN} (selectcol_name1fromtable2wherecol_name2 { |IN} [(select...)] ...)可以看出子查询是由很多条SQL语句组成的也可以把子查询分成一条一条单独的语句去执行最后再把结果和条件拼接在一起得到查询结果。二单行子查询⽰例查询与不想毕业同学的同班同学mysql select * from student where class_id (select class_id from student where name 不想毕业); --------------------------------------------------------------- | id | name | sno | age | gender | enroll_date | class_id | --------------------------------------------------------------- | 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 2 | | 6 | 武松 | 200002 | 18 | 1 | 2000-09-01 | 2 | | 7 | 李逹 | 200003 | 18 | 1 | 2000-09-01 | 2 | | 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 2 | --------------------------------------------------------------- 4 rows in set (0.00 sec)三多行子查询返回多行记录的子查询~~返回的是一个集合集合包含多个对象select * from table1 where table1.id IN (select id from table2 where xxx...);示例查询MySQL或Java课程的成绩信息mysql select * from score where course_id in (select id from course where name Java or name MySQL); ---------------------------------- | id | score | student_id | course_id | ---------------------------------- | 1 | 70.5 | 1 | 1 | | 5 | 60 | 2 | 1 | | 7 | 33 | 3 | 1 | | 10 | 67 | 4 | 1 | | 14 | 81 | 5 | 1 | | 2 | 98.5 | 1 | 3 | | 8 | 68 | 3 | 3 | | 11 | 23 | 4 | 3 | ---------------------------------- 8 rows in set (0.00 sec)四在from子句中使用子查询将查询结果当做一张临时表案例找出每个部门的平均工资的等级。找出每个部门的平均工资select deptno, avg(sal) avgsal from emp group by deptno;2.将以上查询结果当做临时表tt表和salgrade表进行连接查询。条件t.avgsal between s.losal and s.hisalselect t.*,s.grade from (select deptno, avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;五exists、not exists语法select * from 表名 where exists (select * from 表名1)exists后面括号中的查询语句如果有结果返回则执行外层的查询如果返回的是一个空结果则不执行外层的查询内层查询返回非空结果集mysql select * from student where id3; ---------------------- | id | name | class_id | ---------------------- | 3 | 张三 | 1 | ---------------------- 1 row in set (0.00 sec)mysql select * from student where exists ( select * from student where id3); ----------------------- | id | name | class_id | ----------------------- | 3 | 张三 | 1 | | 4 | 李四 | 1 | | 5 | 王五 | 2 | | 7 | 钱七 | 2 | | 9 | 钱七1 | 2 | ----------------------- 5 rows in set (0.00 sec)exists相当于if语句的判断条件有结果返回true没有返回false内层查询返回空结果集外层查询也返回空结果集也可以说外层查询没有执行mysql select * from student where id10; Empty set (0.00 sec) mysql select * from student where exists ( select * from student where id10); Empty set (0.00 sec)mysql select NUll; ------ | NULL | ------ | NULL | ------ 1 row in set (0.00 sec) mysql select * from student where exists ( select null); ----------------------- | id | name | class_id | ----------------------- | 3 | 张三 | 1 | | 4 | 李四 | 1 | | 5 | 王五 | 2 | | 7 | 钱七 | 2 | | 9 | 钱七1 | 2 | ----------------------- 5 rows in set (0.00 sec)返回的结果集是一个非空的只不过列名为null值也为null而已六、合并查询union和unionall不管是union还是union all都可以将两个查询结果集进行合并。union会对合并之后的查询结果集进行去重操作。union all是直接将查询结果集合并不进行去重操作。union all和union都可以完成的话优先选择union allunion all因为不需要去重所以效率高一些。案例查询工作岗位是MANAGER和SALESMAN的员工select ename,sal from emp where jobMANAGER union all select ename,sal from emp where jobSALESMAN;以上案例采用 in 也可以完成那 in 和union all有什么区别考虑走索引优化之类的选择union all其它选择 inin 使用不当很容易导致索引失效。【in 比 or 效率高】两个结果集合并时列数量要相同。
