当前位置:   article > 正文

MySQL基础篇 | 连接查询、子查询(嵌套)_mysql嵌套查询

mysql嵌套查询

tips:前些天突然发现了一个巨牛的人工智能学习网站,通俗易懂,风趣幽默,忍不住分享一下给大家,感兴趣的同学可以点击网站进行学习人工智能学习网站

目录

一:连接查询

1. 连接查询原理以及笛卡尔积现象

2. 内连接

2.1 等值连接

2.2 非等值连接

2.3 自连接

3. 外连接(左外和右外)

二:子查询

1. where后面嵌套子查询

2. from后面嵌套子查询(重要)

3. select后面嵌套子查询


一:连接查询

连接查询:也可以叫跨表查询,需要关联多个表进行查询!

(1)根据语法出现的年代来划分的话,包括:
SQL92(一些DBA可能还在使用这种语法。DBA:DataBase Administrator,数据库管理员)
SQL99(比较新的语法)
(2)根据表的连接方式来划分,包括:
内连接:
     等值连接
     非等值连接

     自连接
外连接:
     左外连接(左连接)
     右外连接(右连接)

全连接(很少用)

1. 连接查询原理以及笛卡尔积现象

笛卡尔积现象当两张表(m,n)进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表(m,n)记录条数的乘积:m*n

例1:显示每个员工姓名,并显示所属的部门名称

第一步:先找出员工名,EMP表

select ename,deptno from emp;

第二步:找出部门名,DEPT表

select * from dept;

​第三步: 结合使用,实际上会有:14 * 4 = 56条数据;每一个EMP中的数据都会与DEPT中的数据结合一次

select ename,dname from emp,dept; --56条数据,笛卡尔现象

第四步:进行过滤,只有EMP的deptno 和 DEPT的deptno相等时,才进行拼接结合

怎么避免笛卡尔积现象?加条件进行过滤;但是不会减少记录的匹配次数,次数还是56次;只不过显示的是有效记录

  1. select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno;
  2. select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno; --使用别名
  3. --表的别名有什么好处?
  4. --第一:执行效率高。
  5. --第二:可读性好。
  6. -- 以上是SQL92语法,老语法

2. 内连接

①如果查询语句中出现了多个表中都存在的字段,则必须指明字段所在的表!

②如果给表起了别名,一旦在select或where等中使用别名的话,则必须使用表的别名,而不能使用表的原名!究其原因还是SQL语句的执行顺序,先执行的是from并且是在from时取得别名,那么此时别名会把原来的表名给覆盖,所以后面的语句只能使用别名!

③如果有N个表实现多表的查询,则需要至少N-1个连接条件!

2.1 等值连接

最大特点是:条件是等量关系!

例1:还是那个例子,显示每个员工信息,并显示所属的部门名称;

使用SQL92和SQL99两种语法写出来!

SQL92:

SQL92语法的where不是真正用到过滤条件,要想加过滤条件,只能在后面用and加入过滤条件!

  1. select
  2. e.ename,d.dname
  3. from
  4. emp e,dept d
  5. where
  6. e.deptno = d.deptno; ---等值连接

SQL99: 

join.....on 连接条件;后面还可以继续跟where,where后面才是真正的过滤条件;

SQL99语法结构更清晰一些:表的连接条件和后来的where条件分离了;inner可以省略的,带着inner目的是可读性好一些,表示内连接!

  1. select
  2. e.ename,d.dname
  3. from
  4. emp e
  5. join -- inner join 这里省略了inner
  6. dept d
  7. on
  8. e.deptno = d.deptno; ---等值连接

2.2 非等值连接

最大的特点是:连接条件中的关系是非等量关系!

例1:找出每个员工的工资等级,要求显示员工名、工资、工资等级

第一步:先找出每个员工的员工名和工资

select ename,sal from emp;

​第二步:再找出员工工资等级

select * from salgrade;

​第三步:根据emp sal的取值范围,在salgrade losal 和 hisal 范围之间进行关系建立

  1. select
  2. e.ename,e.sal,s.grade
  3. from
  4. emp e
  5. inner join
  6. salgrade s
  7. on
  8. e.sal between s.losal and s.hisal;

2.3 自连接

最大的特点是:一张表看做两张表;自己连接自己!

例1:找出每个员工的上级领导,要求显示员工名和对应的领导名

解析:员工和上级领导在同一张表中,因为领导也是员工!

查看表中所有信息

select * from emp;

这张表既可以看成员工表,又可以看成领导表 ;我们只需要让:

员工的领导编号 = 领导的员工编号

  1. select
  2. a.ename as '员工名',b.ename as '领导名'
  3. from
  4. emp a
  5. inner join --inner可省略
  6. emp b
  7. on
  8. a.mgr = b.empno; --员工的领导编号 = 领导的员工编号

注:只有13条数据,因为king虽然也是员工,但是他没有上级领导!

3. 外连接(左外和右外)

(1)什么是外连接,和内连接有什么区别?

内连接:假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接AB两张表没有主副之分,两张表是平等的。

外连接:假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
(2)外连接的分类
左外连接(左连接):表示左边的这张表是主表。
右外连接(右连接)表示右边的这张表是主表。

左连接有右连接的写法,右连接也会有对应的左连接的写法。

(3)外连接最重要的特点是:主表的数据无条件的全部查询出来!

(1)找出每个员工的上级领导?(所有员工必须全部查询出来包括King)

注:我们在使用“内连接---等值查询”时,没有显示king的信息,因为他的上级是NULL,匹配不上所以没有显示,只显示了13条信息;那么我们如果使用外连接,会显示14条信息!

使用左外连接: 

  1. select
  2. a.ename '员工', b.ename '领导'
  3. from
  4. emp a
  5. left outer join -- 左外链接,outer可以省略
  6. emp b
  7. on
  8. a.mgr = b.empno;

 使用右外连接:

  1. select
  2. a.ename '员工', b.ename '领导'
  3. from
  4. emp b
  5. right outer join -- 右外链接,outer可以省略
  6. emp a
  7. on
  8. a.mgr = b.empno;

(2)找出哪个部门没有员工?

第一步:找员工表EMP

第二步:找部门表 DEPT

第三步:从两张表中可以看出部门编号为40的没有员工,那么怎么显示出来呢?

  1. select e.*,d.*
  2. from emp e
  3. right join dept d
  4. on e.deptno = d.deptno;

我们发现14个员工,却有15条数据,因为我们是以dept表为主表查询的,没有员工的会自动匹配为NULL;所以我们只需要最后一条数据,利用where进行进一步的筛选

  1. select d.*,e.ename
  2. from emp e
  3. right join dept d --采用右连接,dept是主表
  4. on e.deptno = d.deptno
  5. where e.ename is null; -- 进一步筛选信息

 (3)找出每一个员工的部门名称以及工资等级(三张表连接查询

第一步:拿到员工信息

select empno,ename,deptno,sal from emp;

第二步:拿到部门信息

select * from dept;

第三步:拿到等级信息

select * from salgrade;

第四步:先通过deptno让员工表EMP和部门表DEPT连接(两张表)

  1. select e.ename,d.dname
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno;

第五步:在通过薪资sal与等级表建立联系(三张表)

  1. select e.ename,d.dname,s.grade
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno
  5. join salgrade s
  6. on e.sal between s.losal and s.hisal;
  7. -- 两个内连接

(4)找出每一个员工的部门名称、工资等级、以及上级领导

注:这就需要外连接了,因为king虽然没有上级领导,但是有部门名称、工资等级,必须显示出来!

  1. select e.ename '员工',d.dname,s.grade,e1.ename '领导'
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno
  5. join salgrade s
  6. on e.sal between s.losal and s.hisal
  7. left join emp e1 --表示e1与e是左连接(这里实际上是增加了一个自连接)
  8. on e.mgr = e1.empno; --员工的领导编号 = 领导的员工编号
  9. -- 不使用外连接left,实际上带有null的信息就会不显示,只有13条数据

二:子查询

什么是子查询?select语句当中嵌套select语句,被嵌套的select语句是子查询。
子查询可以出现在哪里?出现在selelct后面、from后面、where后面。

子查询就是嵌套的select语句,可以理解为子查询是一张表

  1. select
  2.    ..(select) --出现在selelct后面
  3. from
  4.    ..(select) --出现在from后面
  5. where
  6.    ..(select) --出现在where后面

1. where后面嵌套子查询

一般查询的结果都是可以直接使用的,不需要表连接等操作;例如:直接数据比大小!

(1)找出高于平均薪资的员工信息

注:select * from emp where sal > avg(sal); 错误的写法,where后面不能直接使用分组函数。

第一步:找出平均薪资

select avg(sal) from emp;

第二步:where过滤

select * from emp where sal > 2073.214286;

第三步:联合嵌套使用

select ename,sal from emp where sal > (select avg(sal) from emp);

 (2)查询员工信息,查询哪些人是管理者?要求显示出其员工编号和员工姓名

 第一步:首先取得管理者的编号,去除重复的;并且排查出NULL

  1. select distinct mgr from emp where mgr is not null;
  2. --提出重复的,并且让显示出其员工编号和员工姓名,肯定要排查出NULL

第二步: 查询员工编号包含管理者编号的

  1. select empno,ename from emp where empno in (select distinct mgr from emp where mgr is not null);
  2. -- 子查询中,先找到mgr的编号,再根据员工编号包含mgr的编号,是就代表是管理者

2. from后面嵌套子查询(重要)

一般都是把查询的结果作为一张临时表处理,有表连接等操作!

(1)找出每个部门平均薪水的等级

第一步:找出每个部门平均薪水(按照部门分组,求sal的平均值)

select deptno,avg(sal) as avgsal from emp group by deptno;

第二步:将以上的查询结果当做临时表t,让t表和salgrade s表连接,条件是:t.avgsal between s.losal and s.hisal

  1. select t.*,s.grade
  2. from (select deptno,avg(sal) as avgsal from emp group by deptno) t --上一个运行的结果看成一个表,起别名t
  3. join salgrade s
  4. on t.avgsal between s.losal and s.hisal;

(2)找出每个部门平均的薪水等级

注:这道题与上面有所不同,我们第一步查询的数据都是原数据,并没有新数据,我们不需要临时表,直接拿来用就行!

第一步:先找出每个部门的薪水对应的等级

  1. select e.ename,e.sal,e.deptno,s.grade
  2. from emp e
  3. join salgrade s
  4. on e.sal between s.losal and s.hisal;

 第二步:基于以上结果,继续按照deptno分组,求grade字段平均值

  1. select e.deptno,avg(s.grade)
  2. from emp e
  3. join salgrade s
  4. on e.sal between s.losal and s.hisal
  5. group by e.deptno;

3. select后面嵌套子查询

(1)找出每个员工所在的部门名称,要求显示员工名和部门名

第一种方法:使用连接

  1. select e.ename,d.dname
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno;

 第二种方法:使用select嵌套

  1. select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
  2. from emp e;

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/2023面试高手/article/detail/498800
推荐阅读
相关标签
  

闽ICP备14008679号