MySQL基础篇【第四篇】| 连接查询、子查询(嵌套)

一:连接查询

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

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

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

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

例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次;只不过显示的是有效记录

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

编辑

2. 内连接

2.1 等值连接

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

例1:还是那个例子,显示每个员工信息,并显示所属的部门名称;使用SQL92和SQL99两种语法写

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; ---等值连接 -- SQL92语法的where不是真正用到过滤条件,只能在后面用and加入过滤条件 select e.ename,d.dname from emp e join -- inner join 这里省略了inner dept d on e.deptno = d.deptno; ---等值连接 --join B on 连接条件,后面还可以继续跟where;where后面才是真正的过滤条件 --SQL99语法结构更清晰一些:表的连接条件和后来的where条件分离了 --inner可以省略的,带着inner目的是可读性好一些,表示内连接

编辑

2.2 非等值连接

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

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

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

select ename,sal from emp;

编辑

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

select * from salgrade;

编辑

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

select     e.ename,e.sal,s.grade from     emp e inner join     salgrade s on     e.sal between s.losal and s.hisal;

编辑

2.3 自连接

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

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

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

查看表中所有信息

select * from emp;

编辑

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

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

select     a.ename as '员工名',b.ename as '领导名' from     emp a inner join --inner可省略     emp b on     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条信息呢?

使用左外连接: 

select     a.ename '员工', b.ename '领导' from     emp a left outer join -- 左外链接,outer可以省略     emp b on     a.mgr = b.empno;

编辑

 使用右外连接:

select     a.ename '员工', b.ename '领导' from     emp b right outer join -- 右外链接,outer可以省略     emp a on     a.mgr = b.empno;

编辑

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

第一步:找员工表EMP

编辑

第二步:找部门表 DEPT

编辑

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

select e.*,d.* from emp e right join dept d on e.deptno = d.deptno;

编辑

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

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

编辑

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

第一步:拿到员工信息

select empno,ename,deptno,sal from emp;

编辑

第二步:拿到部门信息

select * from dept;

编辑

第三步:拿到等级信息

select * from salgrade;

编辑

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

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

编辑

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

select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on sal between s.losal and s.hisal;

编辑

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

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

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

编辑

二:子查询

什么是子查询?子查询都可以出现在哪里?
    select语句当中嵌套select语句,被嵌套的select语句是子查询。
    子查询可以出现在哪里?

     select             ..(select) --出现在selelct后面         from             ..(select) --出现在from后面         where             ..(select) --出现在where后面

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

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

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

编辑

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

select empno,ename from emp where empno in (select distinct mgr from emp where mgr is not null); -- 子查询中,先找到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

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

编辑

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

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

第一步:找出每个部分的薪水等级

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

编辑

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

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

编辑

3. select后面嵌套子查询

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

第一种方法:使用连接

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

编辑

 第二种方法:使用select嵌套

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

编辑


#MySQL#
全部评论

相关推荐

爱吃肉的伊登在写日记:好棒,27届简历能做成这个样子,但是第一个项目感觉cover住难度还是不小的,特别是二面的时候肯定要对分布式系统设计这一块儿有高出正常面试者的水平才行
点赞 评论 收藏
分享
评论
2
2
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务