SET运算符及练习

set运算符,set并不是我们在操作SQL语句的时候,使用set关键字,它实际上是包括一系列的操作,

我们看一下本章的一个学习目标

第一个叫做描述set操作符,第二个将多个查询用set操作符组成一个新的查询,这里指的set操作符有哪些呢,

并不是说他本身,而是指的我们下边这四个,UNION,UNION ALL,INTERSECT,MINUS,这是这4个,这四个单词,

我们当然可以给他翻译过来,实际上一翻译的话,你基本上他们表示的什么意思,大家都能够明白了,union叫

联合团结,union all呢,是都给连接起来,都给他显示出来,是这个意思吧,intersect呢,翻译过来就是一个取交集的

过程,取中间一个共同有的一个东西,minus叫一个减,加减乘除的减,然后后边还有一个叫order by,他不属于我们这里

set操作符了,他就是一个关键字,表示的叫排序,我们在讲SQL的第一节当中,最后就讲了这个排序,所以我们这里就是

一个旧知识的一个新应用,看这个图

这个图就把我们这一节要讲的内容,完全的概括进来了,一通过图示就非常的清晰了,

所以说我们这一节内容,不是太难,但是重要性来讲还是比较重要的,你真正在进行SQL操作的时候,

确确实实会用到这样一些命令的,大家看到这个图的时候,你是不是很容易回想起来大家上高中的时候,

一上高一,旧课标的时候,第一张叫做集合,我印象还是很深刻的,给当时教集合的老师,刚毕业的一个老师,

我们关于集合,是相应的有一些操作,那么相应的这些操作,就能够对应我们今天要讲的,关于set操作符,

首先看前两个,这个是不是相当于求两个集合,我这每一个集合,A集合和B集合,A用圆圈来表示,这个图和这一组,

大体上分别表示的是求A集合和B集合的一个并集,并集我们用的就是union和union all,这两个操作,这是取并集,

那么着两个并集我们再仔细的去看的话,他两又有什么区别,我们画一个东西,就明白了,这是一个集合,这是一个集合,

假设这个集合当中有123,这个集合中有234,那么我问大家,那这两个集合的union并集是多少,是不是1234,这是我们数学

上讲的并集,是1234,他自动排除了二者当中重复的一个元素,就是说是这个意思,union如果你写出来的话,就叫1234,

而union all呢,你先把这个集合写出来,123,那个集合有2跟4,相当于没有把重复的给删掉,这个叫union all,而union是

去除了重复的元素,所以大家看到这里还多了一个弧线,意味着他们两部分都保存,是吧,这个就是取并集

那相应的这个概念,就是对应我们集合里边的,交集的概念,就像我们刚才举的例子是一样的,这里存放的是123,

这是2跟4,那二者的交集是不是就是2,对吧,交集就是2

那minus,minus表示的叫减,或者对应我们集合中的一个概念叫做差集,叫差集,差集如果在集合当中表示的话,

是这样写的,A一个斜线,B,这个大家一定要知道,这是数学的一个写法,那什么叫差集,对应我们这个图里边,如果

从A集合当中,我minus一下B集合,中间就写了这个minus单词,他就意味着最后得到一个结果,是1和3,大家都明白吧,

就是在A集合当中,把他和B集合共有的元素给擦掉,相当于我们把2给干掉,那么就是1跟3,你也可以怎么理解,你可以

理解成A集合当中,找补集,把AB都有的元素2,从A集合当中找A集合的一个补集,也是13,这两种理解的方式都行,那么我们

这一节,就讲这四个,那我们首先来看这个叫union

union操作的具体的一个例子就在这,我们直接在这里来实现

在这我们使用atguigu这个用户来登陆,我们用scott用户来登陆,scott用户登陆的话,我们开启Command Window,

Command Window我们这样,我们不管以前的表是什么了,我们知道有一个叫employees这个表

select * from employees;

我们现在这个表里边只有105条数据,我现在基于employees表,创建两个新的表,我通过两个新的表,来开始

我们今天这个学习,首先我们create table,employees01,as select,from employees,where department_id

in,这样,我将70号部门,和80号部门的,所有元素拷贝进来

create table employees01

as

select * from employees

where department_id in (70,80);

创建成功了,然后呢我再创建一个,叫employees02,我把80号部门和90号部门的数据给拷贝进来,

create table employees02

as

select * from employees

where department_id in (90,80)

创建成功了,创建成功了以后大家能够想到,这两个新创建的表,一个叫employees01,一个叫employees02,

他里面有一些相同的数据,那么相同的数据就是80号部门的,我们这里先看一下,select employee_id,要不然

他们的项太长了,看起来别叫费劲,department_id,from employees01

select employee_id from employees01;

我们看一下,这个employees01当中,一共是有35条数据,其中有一条数据是70号部门的,剩下的全是

80号部门的,70号部门有一个,80号部门有34条,同样的,我查询一下employees02

select employee_id,department_id from employees02;

这个表有37条,应该34条是80号部门的,3条是90号部门的,稍微捋一下,70号部门我们有一个,

80号部门有34个,然后90号部门是有3个,如果他们两个进行一个union操作的话,大家算一下,应该得出

多少啊,union是不是就这三加一下,应该是38个,那union all呢,union all是不是应该把他们的交集再加一遍,

把34再加一个,应该72条,是这个意思吧,我们来操作一下,select employee_id,department_id,from employees01,

在这这样操作,上边我们操作employees01的,然后union我们下边操作employees02,改成02就可以了,我们刚才算了一下,

结果应该是38

select employee_id, department_id

from employees01

union

select employee_id, department_id

from employees02

就应该是38,1+34+3,38条,这就是取他两的并集,而且去重的,把重复的80号部门的只算了一份,这就是叫union,

union all就是这样来写

select employee_id, department_id

from employees01

union all

select employee_id, department_id

from employees02

union all你是在38的基础之上再加上一个34,就是72,就是这两个操作,如果大家真正工作当中使用SQL语句,

算一下这个部门和这个部门一共是有多少的话,就用的union,一般我们去重的机会比较多的,然后这个我们说一下,

怎么来写,这个union的操作,包括我们后边的minus和intersect类似的语法格式,就是上面是你一个表,下边是一个表,

如果用数学的语言来看,上边是一个集合,下边是一个集合,中间是你集合的一个操作符,大家上高中的时候不就是这样

学的吗,A和B的并集,前后都是你两个集合,我们这里一样,上面是一个表,下面是一个表,中间就是你关于两个表的操作,

是什么关系的,他两,这是这两个表,然后我们再需要说的,你这儿前后都是需要对应的,你这是叫employee_id,department_id,

这边也是这样来对应的,他就没有错,最后都是这样对应的,或者说你要是写错的话,我在后边加了一个last_name,我这没有加,

大家看是不是就出错了

select employee_id, department_id, last_name

from employees01

union all

select employee_id, department_id

from employees02

大家看,是不是就出错了,所以说这里需要注意的,第一个你这个列的项数,跟你这个项数是要一样的,

这是三项,这也得三项,其次数据类型也得一样,是吧,数据类型也得一样,比如一个last_name写这儿了,

你这儿写到这儿了,这里是不是又不行了

select employee_id, department_id, last_name

from employees01

union all

select employee_id, last_name, department_id

from employees02

这里是列数不一样,这是数据类型不一样,这是需要你大家注意的,一般情况下就是一一对应的,

谁跟那一列去对应,都给你对应起来,那或者你不这样写的话,你直接写一个*,也可以,因为这两个

表都是基于employees表来生成的,而且相应列的顺序也都是一样的,个数当然也一样,所以我这里也可以用

星来代替

select * 

from employees01

union all

select *

from employees02

没有问题,就是这个意思,然后呢我们再说一下,我们把错误的给他去掉,这是我们刚才写的,我们这把all

暂时也去了,再说一点是什么呢,我可以起一个别名,可以起一个别名,别名我在这加上一个id,这儿呢可以不加,

这样的话,他就默认的是按照你上面的,就是你这个union set的运算符,上面的表的名字来命名,比如我这里这么写了,

emp_id,我在也写了dept_id,然后我运行一下,大家看到这个结果是不是以我新命名的别名来命名

select employee_id emp_id, department_id dept_id

from employees01

union all

select employee_id, department_id

from employees02

你要是把列名起到这儿的话,起到这是没有效果的,你看,这后边的还是叫department_id,前面的是没有

变化的

select employee_id emp_id, department_id

from employees01

union all

select employee_id, department_id dept_id

from employees02

所以说大家请注意一下,你给这两个表进行set运算符操作的时候,他新生成的数据,是以你上面表的

别名显示的,如果没有别名,就以本身的名来显示,这是我们说的这个知识点,再一个知识点呢,大家你看一下我们这个结果,

这个结果的话,他看似是有一个默认的顺序,有一个id顺序来排的,这是我们要说的下一点,当你进行union,或者intersect,

或者叫minus操作的时候,它会默认的按照需要查询的第一列的默认的从小到大的顺序去排,那这里employee_id,就按照他的从小到大

来排,这是默认的排序,我们最好再加上一个order by,order by排序,默认的是按照employee_id来排,这儿呢像我们排序,我是不是可以

使用这个别名,他默认叫asc,你不写也行,使用他来排序,它是这个结果

select employee_id emp_id, departmetn_id

from employees01

union all

select employee_id, department_id dept_id

from employees02

order by emp_id

你要是想按照倒序来排呢,就desc,给他显示的指明,这是一个倒序的排,就是这样一个基本的操作点

select employee_id emp_id, department_id

from employees01

union all

select employee_id, department_id dept_id

from employees02

上下是两个表,然后这个数据的这个列一一对应,个数得一样,数据类型也得一样,再一方面你得保证他有意义,

你不能把它两给他对调了,没啥意义,虽然是number类型的,你得让他有意义,其次这个别名,按照他的别名为准,

最后可以排序,排序的时候按照你这个别名也可以,升序或倒序,默认是按照第一列的从小到大的顺序来排的,

这是我们讲的union和union all,然后我们说谁啊,交集intersect,intersect表示的就是取交集,交集在我们

这个题目里边,是不是就是80号部门的,80号部门的就是这34条数据,这就是叫交集,交集的话也没啥可说的

select employee_id emp_id, department_id

from employees01

intersect

select employee_id, department_id dept_id

from employees02

order by emp_id desc

order by还是按照employee_id的降序排列,降序排列,这叫intersect,那么intersect我们就说完了,那剩下的是不是

就叫minus,minus就是这个表minus这个表,把这个表里面的数据,差掉这个表里面,他们两公共的就是80号部门,这个是70,80的,

这个是80,90的,这个一差完,只剩下70号部门的,不要他了,minus,这个翻译过来就是差,减的意思,大家看,是不是只有一个部门的了,

因为你取差集,就只剩下70号部门的了,这就是我们常用的操作

select employee_id emp_id, department_id

from employees01

minus 

select employee_id, department_id dept_id

from employees02

大家看,是不是只有这一个部门的了,是吧,因为给你取差集,70号部门的了,这就是我们常用的几个操作,取并集,

取交集,取差集,我们看看我们这一节还有哪些内容,intersect,minus,刚才说了,set注意事项我们刚才也讲到了,

要求你在操作的时候,你列的数量和数据类型上,一定要相应的对应,而且你要保证他们是有意义的,然后括号可以

改变执行的顺序,order by 排序,可以使用第一个查询中的,就是你union上面那个表中的,列名,或者列的别名,

或者是相对位置来进行排序,相对位置是什么意思,相对位置,我们等一一下有个例子要讲一下,相当于是什么啊,

就是比如说,我们这儿是第一列,这个是不是第二列,然后还可以有一个第三列,当然你不加第三列也行,比如我们这里

是按照第一列的降序排,你就可以写个1,order by 1 desc,这里是minus,我们改一下,intersect,这个就是按照这个

的降序排

select employee_id emp_id, department_id

from employees01

intersect

select employee_id, department_id dept_id

from employees02

order by 1 desc

你要是按照这个都是80号部门,那就没啥可说的了,这样吧,union,我按照第二个的一个降序排，

第二列不就是department_id吗,大家看,是不是90,80,70,这就是降序,就是这个意思

select employee_id emp_id, department_id

from employees01

union

select employee_id, department_id dept_id

from employees02

order by 2 desc

除了union all以外,系统会将重复的记录给他删除,这就不用多说了,按照第一个显示的列名显示在输出当中,

刚刚我们说了别名,显示一下别名,会自动按照第一个的升序排,这也说了

我们看一下这个内容,刚才我们说了,你在数量上和数据类型上一定要对应,那要万一就是对应不起来,怎么办,

那我们相应的要做处理,我们有两个例子,两个匹配的例子,我来给大家讲讲这个

比如我们进行这个操作,这个叫select employee_id,department_id,然后我union一下这个表,我们是不是有个表叫

departments,departments的话,那相应的他有两个列,一个叫department_id,还有一个叫department_name,我想把这两个

表给他连起来,departments,但是这两个连的时候,我们说了要求你这个数据类型,和个数都得一致,那现在显然是不匹配,

这个是id,这个是id,显然匹配不起来,从数据类型或者实际意义上来讲,都不对,那我们就得处理一下,咋处理啊,首先我们保证

类型和个数要一致,我们这叫id,emp_id,这个叫department_id,我让他这个跟这个对应,我后边就加上一个,你加不能乱加,你要

保证他有意义,那我们怎么处理,因为department_name是一个varchar类型的,所以叫to_char,就这样操作,相当于给他补了一列,

然后这个,department_id跟他对应,前面是不是要补上一个,又由于这是一个number类型的,所以叫to_number(null),然后这个id跟

他对应,大家能看懂吧,我们用union操作,这就搞定了

select employee_id emp_id,department_id,to_char(null)

from employees01

union

select to_number(null),department_id,department_name

from departments

相当于求了这两个表的一个并集,这两个表的一个并集,就是这样一个操作,保证刚才我们说的刚才的这样一个效果,

最后我还有一个练习

这个题目是这个意思,比如我们想输出这样一句话,I want to study at www.atguigu.com,我想把这一句话给他打印,

当然他不是在一个语句里面去打,就是这样,有三行,你可以列成三个表,三个表必须得让他这样打印,我就这样写了,

select 'study at',from dual虚表,union,select,你可以再取一个别的,比如说这个I want to,from dual,union

select 'www.atguigu.com',from dual,这是三个虚表,通过两个union给他连接起来,然后我们说了还可以起一个别名,

as "My Dream",这是这三个信息,如果我们这样来写的话,他是不是默认按照这个的升序排

select 'studey at' as "My Dream"

from dual

union

select 'I want to'

from dual

union

select 'www.atuigu.com'

from dual

升序正好从小到大,假如我把这三个去了,这个时候是不是就乱套了

select 'study at' as "My Dream"

from dual

union

select 'I want to'

from dual

union

select 'atguigu.com'

from dual

你看这个时候是不是乱套了,这个顺序不对了,我们得调整一下,调整的时候我们说,是不是说我们可以加上一个序号,

study at应该在第二列出现,I want to应该在第一列,这个呢应该是在第三列,我只要加上一个order by,按照第二列来

进行排列,asc的,从小到大,这个你要看是不是好了

select 'study at' as "My Dream",2

from dual

union

select 'I want to',1

from dual

union

select 'atguigu.com',3

from dual

这个就没问题了,但是这里有一个问题是啥啊,就是你这里多了一个序号,序号我根本就不想要这个东西,

不想要他,不想要的话我们就进行这么一个操作,这个大家了解一下就行,column起个名,a_dummy,noprint,

就是不让a_dummy这个列打印,执行了

column a_dummy noprint;

是不是就搞定了,就是相对位置我们要说明的,这节内容实际上不难,当然你开发当中真正用的话,

就是union,union all,intersect,minus,这是取并集,取交集,取差集,使用它进行排序,就完了,

下面我们说一下练习题

1.	查询部门的部门号，其中不包括job_id是”ST_CLERK”的部门号

/*
select department_id
from departments
where department_id not in (
                           select distinct department_id
                           from employees
                           where job_id = 'ST_CLERK'
                           )
*/
select department_id
from departments
minus
select department_id
from employees
where job_id = 'ST_CLERK'

查询部门的部门号,其中不包括部门id是这个的部门号,大家拿到题目后先给他分析一下,然后我们再想,

首先明确它是让你查询部门号的,然后部门号里面少了一些东西,相当于把这个内容给他干掉,相当于一个

集合当中,剪掉另一个集合,这是用数学语言来说的,这个题目拿到以后大家想想,用以前的知识你能不能解决,

我们在前面讲select的时候,还记不记得讲了一个子查询,这个job_id一定对应了一个部门号,当然也不一定一个了,

一定对应了department_id,我们把department_id给他抠出去,我们以前用in,加个not in,所以说这个题目,我们先用旧的知识

给解决一下,他不是让查询department_id吗,查询部门的部门号,from,你这里就别写from employees,有的部门里面没有员工,

所以这里会少,你得用谁啊,departments,然后你把这个给减出去,where department_id,以前用in,现在只要in里边的,那前面

加个not in就行了,in哪儿,select department_id from employees,where,你先把job_id是他的先给他找到,job_id

等于'ST_CLERK',他这个department_id找到,他这个department_id可能是有多个,你给他整一个distinct也行,不加问题也不大,

distinct他,not in他,因为有可能有多个,所以用in,如果一个的话不等就行,这里我们一共有26个部门

select department_id 

from departments

where department_id not in (select distinct department_id from employees where job_id = 'ST_CLERK')

然后再考虑用我们这一节新的知识来讲,这样我来把它注掉,相当于我们使用了旧知识子查询,

就可以搞定,那我们采用新的方式,是不是我们刚才说了,是不是把它给抠出去,把它给抠出去,

就是minus,首先我们明确查询的还是他,然后叫minus,minus谁,select,还是departments_id,

这个时候是叫employees,from employees,然后呢,where job_id等于'ST_CLERK',把这个这个表里边的,

这个department_id给他抠掉,刚才我们查询的是26条记录,我们运行一下这个,还是吧

select department_id 

from departments

minus

select department_id 

from employees

where job_id = 'ST_CLERK'

2.	查询10，50，20号部门的job_id，department_id并且department_id按10，50，20的顺序排列

1)column a_dummy noprint;
2)
SELECT job_id,department_id,1 a_dummy
from employees
where department_id = 10
union
SELECT job_id,department_id,2
from employees
where department_id = 50
union
SELECT job_id,department_id,3
from employees
where department_id = 20
order by 3 asc

查询10,50,20号部门的job_id,department_id,并且得要求department_id按照10,50,20的顺序排列,这个题主要是考

什么的,首先你这三个部门你得列出来,其次呢有一个排序,如果你没有排序这个事呢还好办呢,怎么没有排序就好办了,

相当于我们就输出他,我们现在没有他,值考虑这个,只考虑他的话,是不是这样啊,select,叫job_id,department_id,

from employees,where department_id in(10,50,20),就这样写,这是没有排序的时候是这样处理,你像加上排序的话,

我写一个order by,但是这个题他比较恶心,你说他排序从小到大也行,或者从大到小也行,我们都可以用order by实现,

select job_id, department_id

from employees

where department_id in(10,50,20)

但是这个题目有个先后,没法让他正序或者倒序了,你用我们这个旧的知识是没法解决的,大家清楚吧,你要按照从小到大

或者从大到小,但是你要是中间突出来了,你既不是从小,也不是从大,那这个就必须得用我们今天讲的set运算符,其中我们

使用的union,你看这个是不是一个信息,这是一个信息,这也是一个,我们把这三个union起来,它是一部分,它是一部门,他是

一部分,按照三部分的一二三的顺序给他排序,清楚吧,那我们来考虑这个怎么来实现,我们先来一个department_id等于10,

然后呢union,这个是50,然后再来一个union,来一个20,大家你这个时候想一想,如果现在这样写的话,他是不是按照10,50,20

排序的,是不是的话我们运行一下看看,显然不是啊

select job_id, department_id

from employees

where job_id = 10

union

select job_id, department_id

from employees

where job_id = 50

union

select job_id, department_id

from employees

where job_id = 20

这个不是不是按department_id从到大,而是这儿,我们是不是说了这个排序,你如果没有写order by的话,

它会按照你第一个表的第一个列的顺序依次往下从小到大的排序,这里看是按照department_id从小到大

排序的,这是我们默认的一个排序,那现在显示的先有10,后有50,再有20,我们是不是可以在后面加上一个,

1,2,3,然后order by,第三列,按照第三列的从小到大排,你可以加上一个asc,这个就是按照我们的要求做到了

select job_id, department_id, 1

from employees

where job_id = 10

union

select job_id, department_id, 2

from employees

where job_id = 50

union

select job_id, department_id, 3

from employees

where job_id = 2

order by 3 asc

这个就按照我们的要求做到了,只不过有一点不好,他后面是不是整了一个这个,整了这个,刚才我们讲课程的时候,

我们可以用一个这个的命令,叫column,这个别名随便取,column 他noprint,就不需要他打印了

column a_dummy noprint;

然后我这个别名赋在这,赋在这你再一操作,这一列就没有了

select job_id, department_id, 1 a_dummy

from employees

where job_id = 10

union

select job_id, department_id, 2

from employees

where job_id = 50

union

select job_id, department_id, 3

where job_id = 20

order by 3 asc

3.	查询所有员工的last_name ,department_id 和department_name


select last_name,department_id,to_char(null)
from employees
union
select to_char(null),department_id,department_name
from departments

查询所有员工的last_name,department_id,department_name,这个department_name我们明确一下,

前面这两个在employees这个表里,这个是不是在departments表里,现在如果我们让set运算符来操作的话,

是不是就用到了我们说的要对应起来,我们来写一下吧,select last_name,跟我们讲的练习是类似的,last_name,

department_id,from employees,union,select,你可以写一个department_id,然后department_name,然后我们说了

你得一一对应,from departments,一一对应的话,department_name,department_id,这个是对应的,所以你在前面要

补一个,varchar型的就用to_char来代替,他两对应,然后这两对应,然后第三个还少一个,department_name也正好是

varchar类型的,to_char(null),是这样子吧,运行一下,这个题目我们就做完了

select last_name,department_id,to_char(null)

from employees

union

select to_char(null),department_id,department_name

from departments