高级子查询2

相关子查询什么意思,我们看

相关子查询是按照一行阶一行的子查询,主查询的每一行都执行一次子查询,这个呢叫相关子查询,好似对应的就应该叫不相关子查询,当然我们没有这个概念,不相关的,只是明确是相关的,外层子查询和内层子查询的关系比较密切,什么相关性,就是我们红色标注的,主查询每一行,都会执行一次子查询,怎么来理解这个事,我们来看一下,我们那会写过一个练习,就是他,就是这个练习,这个练习这个题目是这样要求的,按照department_name进行排序,我这儿使用了一个子查询,大家你看是不是这样,我外层当你每进行一次select的时候,从这个employees表当中,选一个id,选一个name,他相应的都会执行一遍这个子查询,子查询按照这样一个条件,返回一个department_name,那就意味着每一条数据,都有可能是返回不同的department_name,然后根据name的不同,进行一个order by,这就是我们说的相关子查询,内外相关度是比较高的,因为我使用了这样一个语句,因为明显你的这个d是内层的表,e1是外层的表,他两之间有相关性,这就叫相关子查询,对应的貌似叫非相关的,什么情况,就是我们之前写过的这个例子,比如说select employee_id,salary,from employees,where salary大于,我们写过这个例子,select salary,返回公司当中工资比Abel工资高的信息,我们是这样写的select employee_id,salary from employees where salary > (select salary from employees wherelast_name='Abel')当时我们是不是还说了,真正执行的时候是先执行内层查询,把内层查询的结果,返回给外层查询,你看这个题目当中,你外层不管是哪行数据进来,这个值都一样,没有内外层连接条件出现刚才那个情景,所以他呢就不能称之为相关子查询了,就是一个很一般的查询,因为你内层返回的结果跟外层的每一行都不一样,跟外层每一行都没有关系,返回的数据都是一样的,都是一个值

这里一共有11条,相比较于刚才相关的,大家理解一下,这里面我们有一个框,大家看一下,get这个环节,从主查询中获取候选列,然后execute执行,子查询使用主查询的数据,然后use,如果满足内查询的条件就返回该行,你看这个就和我们说的这个练习完全吻合,你外层找到一列的数据,这一列是谁啊,是department_id,然后这一列数据,然后这个数据在内层当中按照这个执行,执行的时候都会返回一个值,当然有的话就返回了,返回一个值,然后这个值被外层所使用,用它来进行排序,这就是一个相关子查询,就是因为有他select department_id, last_namefrom employees e1order by (select department_name from departments d where e1.department_id=d.department_id) asc

我们来看看这里面的题目,外层的表在内层中使用了,就是相关子查询

这个题目刚才我们都写了,查询员工工资大于平均工资的信息

这个题我们刚才是不是都已经做过了,我们解释一下,这不就是查询这三个信息,然后要求比本部门,外层的outer,在内层中使用,连接条件就是他们的department_id是一样,返回这个部门的平均工资,那意味着你外层每一条数据进去以后,都应该返回不同的avg,但是每次你外层数据进去的时候,都会再执行一遍再返回回来,这就叫相关子查询select last_name, salary, department_idfrom employees outerwhere salary > (select avg(salary) from employees where department_id=outer.department_id)

这个我们就不用多说了,若employees表中的,employee_id与job_history表中的employee_id相同的数目不小于2,输出这些相同id的员工的信息

这儿又有一个表叫job_history,数目不小于2,就是大于等于2的时候,那这几个人的信息给他输出出来,我们先看一下这个表,熟悉一下,select * from job_history,这是这个表中的信息select * form job_history

这是这个表中的信息,我们看一下,说employees表中的跟这个表中相同的,确实有相同的,比如这里边101,他恰好就等于2,说明员工换工作的,员工换工作的信息,紧接着又在另一个部门工作的,换部门了,这是一个人,还有一个176,这个人也是一样的,相当于是我们这个题目,他就把你这两个人,给输出出来,是这个意思吧,那我们就使用到相关子查询,那就是你外层的表每进来一个employee_id,都跟这里面的去匹配,匹配完了以后,匹配的个数,如果是大于等于2的,就把那个数据外层的给他拿出来,这样明白以后,我们就来写了,查询哪些信息,employee_id,last_name,job_id,from employees,然后加条件,where要求你外层每进入一条数据,返回的你看个数,与2去比,要求你不小于2,要求你这个数据大于等于2,我们习惯把子查询写在右边,那就是2小于等于右括号,这是我们子查询要写的,这个怎么写,2比的是个数,那你这里就应该用到组函数count(*),from job_history,where加连接条件了,我们在这里给这个表起个别名,要求你里面的employee_id等于e1的employee_id,然后别的就没有了,怎么来理解,我们外层这个数据,就按照这个employee_id进入,从job_history一条条的去匹配,一旦匹配的话就算一个,整个遍历一遍,这个表,一旦这个值大于等于2的,这个信息就作为一个返回结果输出来,整个这个效果就相当于把这两个表当中相同的数目,如果大于等于2,这些员工的三个信息给输出来,运行一下结果select employee_id,last_name,job_idfrom employees e1where 2 <= (select count(*) from job_history where employee_id = e1.employee_id)

大家看一共是有三个人,有101,176,200,200也有两个,那就是这三个人,这就叫相关子查询,然后就是exists这个操作符

包括下面的not exists,还有相关的更新,他们都是跟相关子查询都是有联系的,只不过是相关子查询的一个应用罢了,这个操作符在检查子查询中是否存在满足条件的行,他就检查子查询中是否存在满足子查询条件的行的,如果满足的话,就不再去查找,返回true,如果在子查询中不满足条件,然后返回false,继续查找,比较类似我们讲的流程控制,if,else,一旦你小括号返回true就执行,然后我们看具体怎么来使用他,这里使用了一个例子,这个例子我们学习一下

查询公司管理者的employee_id,last_name,job_id,department_id,拿到这个题目以后,我们去分析他,查询这几个信息,这四个信息都存在于employees这个表中,查询管理者的信息,就是说你看管理者是谁,这个我们用的旧的知识我们可不可以解决,我们先用旧的知识把这个给他搞定,那怎么写,select不就要查这四个信息吗,from employees e1,这个e1,你把它想象成什么啊,管理者,就只让输出管理者的这几个信息,不是管理者的不让输出,是这个意思吧,那我们是不是得加上一个where条件,e1的employee_id,这个我们就不过多的去说了,select manager_id from employees e2,where e1的,这个就有点像相关子查询了,我们写法是多种多样的,都可以,就这样写吧,e1的employee_id等于e2的manager_id,然后呢找到这个manager_id,这个肯定是有多个的,in吧,18个select employee_id,last_name,job_id,department_idfrom employees e1where e1.employee_id in (select manager_id from employees e2 where e1.employee_id = e2.manager_id)

然后这个题目还可以这么样来写,这个还是有点今天的这个知识,再用旧知识的话我们可以这样,employees e2,然后e1的employee_id,e2的manager_id,是吧,然后这样查大家看一下结果,这我得指定一下,e1select e1.employee_id, e1.last_name, e1.job_id, e1.department_idfrom employoees e1, employees e2where e1.employee_id = e2.manager_id

这里的肯定是会有重复的在里边,重复的我就加上distinct,18个select distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_idfrom employees e1, employees e2where e1.employee_id = e2.manager_id

更我们刚才的查询结果是一样的,这是我们通过两个钟方式来给大家一个解答,这两个方式哪种都行,现在我们讲这个,又是另一种方式了,有点区别了,查询公司管理者的这四个信息,你看我们刚才写的这几个题,是不是他,这个,我说你这个题目当中,它是把内层的manager_id给返回回来了,其实我也不用返回什么select employee_id,last_name,job_id,department_idfrom employees e1where manager_id in (select manager_id from employees e2 where e1.employee_id=e2.manager_id)

我把这个改成exists,然后这里边怎么来修改,select我这里不需要你具体查什么,随便写一个东西就行,我们就写个A吧,select A from他,这个题目我们照常这样写的话,结果仍然是可以查询出来的,我们看一下select employee_id,last_name,job_id,department_idfrom employees e1where exists(select 'A' from employees e2 where e1.employee_id=e2.manager_id)

这就是我们说的exists,我只需要查询,这个employees表中的,管理者的信息,我只需要知道你是管理者,就可以了,具体说你是几号管理者,我在一输出就完了,我不用过多的关注,我再说一遍,我只需要知道,哪一个员工是管理者就完了,至于你说,是几号的,我不用过多关注,因为你内层查询的时候,我不再需要你给我返回出来,你是几号的,你看我们刚才的查询方式,实际上你是告诉我,你是几号的select employee_id,last_name,job_id,department_idfrom employees e1where e1.employee_id in (select manager_id from employees e2 where e1.employee_id = e2.manager_id)这个信息实际上我们不需要,我只需要知道你是管理者就完了,那我们就用到exists,内层里面当然还是一个相关子查询,所以他是相关子查询的一种应用,这个的employee_id,跟你内层的manager_id,每外层进去一个,就能够找到一个manager_id,如果有的话,返回就true,如果没有,就返回一个空,也就是所谓的false,一旦这里面找到一个就返回true,不继续再寻找了,那你找到的放进去的这条数据,对应的employee_id是谁,就将这条数据输出出来,不就是公司的管理者吗,你每次找一条数据往里面一放,一旦返回true就是管理者,一旦是false,就不是管理者,对于这个题目也可以这样来写,这就是这样的一个题目,然后我们看下一个题,叫not exists

这个就和我们讲null一样,null是空,not null就是非空,not exists的意思,就是这个是true的,加上not就返回false,我们看看这个题目,查询departments表当中,不存在于employees表中的部门的,这两个信息,我们就把这个框写出来,select department_id,department_name,from departments,这毫无疑问了,关键是你where语句这,不在这个表中的信息,那就相当于你把这个表中的信息给他砍掉,实际上我们用旧的方式也能够实现,一会我们再说旧的方式,我们先用我们新的方式,新的方式的话,我只要你在或不在就行,我先把exist给他补上,然后呢你具体是谁,我不需要关注,你随便写一个select就行,比如'c',这都可以,from他不是说employees表吗,然后where,我们这个给外层这个表取一个别名,d,where内层这个表的department_id,等于外层这个表的department_id,你看我们这个查询是不是要求,这个外层这个表,id是在内层这个表里的,才返回一个true,现在要你返回不存在这里面的,那你前面加上一个not就行了,这个时候当你外层每一条数据进去的时候,一旦出现这个条件有数据的话,他就不让你输,因为他有not,如果没有,说明是查询到外层的数据不存在,存在于employees表中的select department_id,department_namefrom departments dwhere not exists (select 'c' from employees where department_id = d.department_id)

一共有16个,这就意味着这16个部门都是空的,里边是没有员工的,就是这个情况,这个题目其实我们用旧的知识也是可以来解决,怎么解决,我就相当于把这个表中的department_id,减去这个表的department_id,这个表有16个部门,是不是就可以这样写,select department_id,department_name,from这儿,minus,select department_id,from employees,这个我们还得把它去掉,大家看这也是16条数据select department_idfrom departments dminusselect department_idfrom employees

这是11条数据实际上是一样的,这是我们用不同的方式来实现的,当然你想在这里加上department_name的话,这个题目还不行了,这样有27个select department_id, department_namefrom departments dminusselect department_id, to_char(null) from employees

27个,这个也不一样了,你还是想让他带上department_name的话,就还是得用我们刚才这种方式select department_id,department_namefrom departments dwhere not exists (select 'c' from employees where department_id = d.department_id)如果只想看有哪些部门,你可以用我们上节讲的set运算符,这个是我们讲的exists和not exists的一个应用,下一个叫相关更新

相关更新,这儿也是相关子查询,只是我们在更新这一块,使用相关子查询,依据一个表中的数据,更新另一个表的数据,我们先看这个,这是一个例题,我们直接来实现就可以了

这样我们先创建一个表,这个表啊,就是对employees表的一个复制,就在这儿写,第一步,对一个表的一个复制,create table,emp,这个叫001吧,as select * from employees,创建好了011create table emp011asselect * from employees

这个表创建好以后,这个表我们知道,他里面是不含有department_name的,是吧,不含有department_name,现在我想把department_name加到这个表上,然后name的值是多少也给赋上,那这个怎么来实现,第二步我得先把这一列给补上,先补上,怎么补,就是alter table,emp011,然后add增加一个列,department_name,varchar 20,加上了alter table emp011add(department_name varchar(20))

加上了但是你这个列都是空的,每一个都是空的,下一个我就想把你这个name,一个一个的都得补上,补你不能乱补,本身你这个员工是哪个部门的你就是哪个部门的,有点意思,那我们这个怎么来实现,这个就是我们第三步要做的事情,第三步首先你给他补,相当于你要一条一条的数据,那就update,emp011,update用的是set,set怎么来补,你是要补他们的department_name,是吧,把它给补上,然后department_name,等于这里就是一个相关的子查询,update他,怎么写啊,你肯定是要写department_name了,from一定是在departments这个表里,关键是这个where怎么写,要求你外层进来每一条数据都是内层department_id一样的情况下,返回一个name,然后把name赋给外层的这个,就是一个非常典型的相关子查询,再说一遍,我外层每进来一条数据,一开始它是空的,进来的数据都对应着一个department_id,和你这里面找department_id一样的部门,把他这个name赋给他,就是先进去再出来,是吧,那就是等于emp011的department_id,你看,运行一下,执行了update emp011set department_name = (select department_id from departments where department_id = emp011.department_id)

执行了,然后select * from emp011;

这个就是我们使用的一个相关子查询,实现的一个更新操作,以后我再查询的时候,不用再用两个表了,因为我已经集成到一个表了,就是这个意思,还有一个叫相关的删除操作,类似的,叫delete,使用相关子查询,依据一个表的数据,删除另一个表的数据

有个例子我们把它写一下

删除表employees中,其与这个表皆有的数据,把这个表和这个表共有的数据删除,是这个意思,是吧,删除的话我们得先有一个准备的工作,我先造两个表,create table emp,emp022,as select *,from employees,where department_id in (80,90),80号,90号部门的数据,赋给他create table emp022asselect * from employeeswhere department_id in(80,90)

然后我再造一个033,把90号的数据都给他create table emp033asselect * from employeeswhere department_id = 90

然后我们看一下select * from emp022;

这是37条数据select * from emp033;

这是3条数据,上面这个表当中大家注意到,它是有3条数据是90号部门的,剩下的34条数据是80号部门的,现在我想把这个表的数据和这个表的数据一样的3条数据给干掉,就只要80号部门的,那这个怎么去写啊,我现在是想进行删除操作,而不是仅仅一个查询,如果仅仅是一个查询的话,那是不是和我们上节讲的一样的,我们上节讲的叫set运算符,是不是这样写,如果我仅仅是需要查询的话,select * from emp022,然后minus,select * from emp033,就是把022当中把033中一样的给减去就行,一共34个select * from emp022minusselect * from emp033

这仅仅是一个查询,你要改的话,delete from emp022,然后where,department_id等于某一个,select department_id from emp033,where,department_id等于外层的emp022的department_id,他两的部门id都一样,如果有一样的话,把一样的这个值给赋过来,返回多个值,indelete from emp022where department_id in (select department_id from emp033 where department_id = emp022.department_id)

然后你再select * from emp022;

就已经把emp033的数据,给删掉了,全是80号部门,这个讲完以后,我们关于相关的子查询,就说完了,我们刚才讲的这几节,相关子查询里面的,两个主要应用,一个是他,一个是他,当然他自己也有他自己的独立的应用,最后还剩一个,叫with子句,我们看一看with子句,什么意思,这里也有关于with子句的一个说明

使用with子句,可以避免在select语句当中重复书写相同的语句块,with子句将该子句中的语句块,执行一次并存储到用户的临时表空间中,使用他可以提高查询的效率,之所以能够提高效率,他可以避免你重复书写,然后存储到这,你下次调用就可以提升效率,这都好理解,关键是怎么来使用这个东西,怎么来使用他,怎么使用,我们看这里有一个题目

这个题目是他,这个题目还挺长的,我们先写一个比较简单的,然后再来说比较复杂的东西,简单到什么程度呢,还是我们一个老题,通过老题我们把新东西引进来,这样大家比较熟悉,还是写这个老题目,查询公司中工资中比Abel工资高的员工的信息,这个我们写了好多遍了,我们换成使用with语句怎么来实现,或者我们这样,先把我们老的题目实现方式先写出来select employee_id,salaryfrom employeeswhere salary > (select salary from employees where last_name = 'Abel')这个题是这样写的吧,我们先把这个值返回回来,然后赋给外层的查询,得到最后的结果,我们要把他这个题目改成使用with语句,该怎么来实现,我先写上with,然后我给这里面重新命名了一个表,这个叫Abel_sal,as,这怎么写,跟他一样,能不能看懂,这是查询Abel的工资,把他相当于查询一个结果,当成是一个表也好,这个就放到这儿了,放到这以后就要查询啊,你要查询,这个括号里面就需要你写上Abel的工资,刚才我们直接用查询的,这个都已经查好了,我只需要把这个信息给他查询出来就好了,select salary,把这个工资拿到,大家看,是不是还是11条数据with Abel_sal as(select salary from employees where last_name = 'Abel')select employee_id,salaryfrom employeeswhere salary > (select salary from Abel_sal)

这个就是我们使用的叫with语句,当然这个是比较简单的一个with语句,所以你从这个题目当中来看的话,貌似不要用with语句,更简洁,但是它是解决问题的一种思路,当你这个问题比较复杂的时候,也就是说当你这个题目需要进行多层的查询,然后依托于之前查询的效果的话,我先把你要查询的东西放到这,最后来一个select,这里相当于存了salary,这里调用就行,当题目越来越大的时候,你使用with语句,就明显的更清晰,而且结构非常的明确,写起来也更简单,这是一个比较简单的例子,然后我们回来看这个,这个例子如何使用with语句来进行实现,查询公司中各部门的总工资,大于公司中各部门的平均总工资的部门信息,查询部门信息,比如让你反馈一下部门的名字,都有哪些,我们来写这个题目,我们开一个窗口,他说让查询公司当中,各部门的总工资,大于公司中各部门的平均总工资,的部门有哪些,然后把这些部门的信息给输出出来,如果你刚拿到这个题目,没有什么思路的话,那你就先看看你能得到哪些东西,比如各部门的总工资,这个大家能不能做到,应该差不多,然后各部门的平均总工资,再想办法去求,我们先把第一个搞定了,select,这个信息呢,后边可能也要用,所以我们这里就直接来查询一下,除了查询总工资,sum(salary),除了他之外,我再查询一个,department_name,看各部门叫什么名,from,显然这里面,salary是employees表的,这是departments表的,那就departments d,employees e,加上一个连接条件,d.department_id等于e.department_id,同时别忘了,group by,按照他来分组,我习惯上把它写在前面,看着比较难受,我们就给他放在前边也行,这个我就相当于求出来,各个部门的总工资了,运行一下select department_name,sum(salary)from departments d, employees ewhere d.department_id = e.department_idgroup by department_name

就是这样的,各个总工资找到了,然后再往后看,然后说呢,公司中各部门的平均总工资,平均总工资,我刚才算出来的是各部门的总工资,然后下一步需要做的就是,你把这些值都加起来,然后除以11,除以11得到一个平均的总工资,这11个部门,就是相当于要依托于我现在算的这个值,然后再求出一个平均总工资,再比较谁比他大,要么你就用子查询,有多层嵌套,非常的呕心,我们现在就是一个现成的,那现成的这个东西,我想后面用一用他,那我们就要用到with语句,怎么整啊,with dept,sum_sal,as,怎么来实现的,这就是我们现在得到的这个值,然后为了方便,我在sum这里再取上一个别名,sum_sal,现在这个结构就放到这里了,这是一个结构with dept_sum_sal as (select department_name,sum(salary) sum_sal from departments d, employees ewhere d.department_id = e.department_id group by department_name),中间用逗号隔开,让你查询这个东西,with这个怎么写,平均的avg,dept_avgsql,as,在这写吧,怎么写,select,你现在需要查询的是公司的各个部门加起来的,平均的总工资,那相当于把这个值,再求一个和,各个部门的总工资,把他们加起来,sum(sum_sal),这个求一个和,from这个表,这个表你把它给算出来,这儿你再取一个别名也行,sum_sal1,我这个叫1,这个是公司整个的总和,现在要的是平均,你再给他除以一个count(*),这样写吧,from他,这个值是公司的平均总工资,这个提供完以后,这两个结构放在这,写上select,查询这些部门信息,就是相当于从这个表中去查,select * from这个表,where这个怎么写,这个表他有一个他,就是你各个部门的总工资大于公司的平均总工资,select这个值,from这个表with dept_avgsal as(select sum(sum_sal1)/count(*) avg_sum_sal2 from dept_sumsal),select * from dept_sumsalwhere sum_sal1 > (select avg_sum_sal2 from dept_avgsal)

这个返回的是各部门的平均总工资,然后看哪个部门的总工资比平均总工资大就返回来,最后再来一个排序,order by这个表里面的这个列

那就是这两个部门,这两个小部门的平均工资比较高,我们这个题就解决了,这个题目解决完以后啊,我们整个这一章就讲完了,这一章的内容比较多,难度也比我们之前讲的要难一些,我们再稍微捋一下,实际上我们讲了这样几块内容,一呢叫多列子查询,里边又分为成对和非成对的,里边有一个叫多列子查询,一个叫单列子查询,后边相关子查询,我们讲的一个重点,他的两个应用,一个叫exists,一个叫not exists子句,和相关更新和删除,最后我们说的叫with子句,中间其实还穿插了一个叫from,from子句中使用子查询,大家把这个题目都给写一写

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！