多表查询练习

我们看一下多表连接这一章的练习题,回顾我们刚才讲的知识点

1. 多表连接查询时, 若两个表有同名的列, 必须使用表的别名对列名进行引用, 否则出错!

查询的时候,如果两个表有同名的列,必须使用表的别名对列名进行引用,否则出错,你得告诉我是哪个表里的,

这个是一定需要指明的

2. 查询出公司员工的 last_name, department_name, city

	
	select last_name, department_name, city
	from departments d, employees e, locations l
	where d.department_id = e.department_id and d.location_id = l.location_id

涉及到了三个表,last_name是employees表的,department_name是departments表,city是locations表,

这个我们就不写了,这个我们也写了这个练习了,你看一眼,这是我们实现多表连接的,第一种方式,比较通用的,

直接from几个表,加上where连接条件,这个不加会有笛卡尔积的错误,你不加或者是少加,这个写了,这个and忘了,

仍然是有笛卡尔积的错误,下一个

3. 查询出 last_name 为 'Chen' 的 manager 的信息. (员工的 manager_id 是某员工的 employee_id) 
	
	0). 例如: 老张的员工号为: "1001", 我的员工号为: "1002", 

            我的 manager_id 为 "1001" --- 我的 manager 是"老张" 

	1). 通过两条 sql 查询:
  
			select manager_id
			from employees
			where lower(last_name) = 'chen' --返回的结果为 108
			
			select *
			from employees
			where employee_id = 108
			
	2). 通过一条 sql 查询(自连接):
			
			select m.*
			from employees e, employees m
			where e.manager_id = m.employee_id and e.last_name = 'Chen'		
			
	3). 通过一条 sql 查询(子查询):	
			
			select *
			from employees
			where employee_id = (
			                      select manager_id 
			                      from employees
			                      where last_name = 'Chen'
			                    )

查询last_name这个我们已经做过了,这里就不说了,这里我们会的,通过两条SQL语句,或者我们使用一条的话,

那就自连接,下边还有一个叫子查询,子查询我们放在第6节,来给大家讲,也是一个SQL语句就能够搞定了,我们到那

再给大家来说,先放一个引子

4. 查询每个员工的 last_name 和 GRADE_LEVEL(在 JOB_GRADES 表中). ---- 非等值连接

			select last_name, salary, grade_level, lowest_sal, highest_sal
			from employees e, job_grades j
			where e.salary >= j.lowest_sal and e.salary <= j.highest_sal

查询每一个员工的last_name,以及他的grade_level,我们以他作为一个典型的例题,说了一下非等值连接,

是什么意思,也就是连接条件是一个非等值的,就这样

5. 左外连接和右外连接

			select last_name, e.department_id, department_name
			from employees e, departments d
			where e.department_id = d.department_id(+)
			
			select last_name, d.department_id, department_name
			from employees e, departments d
			where e.department_id(+) = d.department_id
			
			理解 "(+)" 的位置: 以左外连接为例, 因为左表需要返回更多的记录,
			右表就需要 "加上" 更多的记录, 所以在右表的链接条件上加上 "(+)"
			
			注意: 1). 两边都加上 "(+)" 符号, 会发生语法错误!
			      2). 这种语法为 Oracle 所独有, 不能在其它数据库中使用.	

他两个肯定指的是外连接了,左外右外外连接,上面的都叫内连接,左外右外就是你返回员工左表或者右表,

不满足条件的行,就分别叫做左外或者右外,这个是左外还是右外,where e.department_id = d.department_id(+)

这个是左外吧,大家怎么来记忆,我们刚才从定义上来说,是输出左表中不满足条件的行,叫左外,说明左表中的行数多,

所以叫左外,左表中的行数多,就是意味着你在这个条件的时候,右边得多加上几个空行,给他补齐,一行得一行行对应,

右边不是得多补几个,所以在右边补加号,右边补加号的叫左外连接,左边补加号的叫右外连接,或者就像刚才那样记忆,

左边多就是左外,右边多就是右外,他只能实现左外或者右外,满外是不可以实现的,要实现满外,只能够用SQL99,SQL99

怎么来实现呢,左外叫left outer join,outer也可以省略,也可以加上,加上就写在left和join之间,left,right和full,

这是左外,右外,和全外,那么除此之外,SQL99的语法当中,还可以实现他自己,如何实现多表连接,叫join on,就是这种

方式大家是需要掌握的,两种方式,一种是这种方式,join什么on,一种是开头的这样,这一章就需要大家掌握这两个重点,

那么除此之外呢,作为了解的,join on说了,叫using,上面还有一个家里natural join,自然的一个连接,这个我们作为了解,

using也是作为一个了解,他们两个都有局限性,真正我们开发中去使用的就是join on,或者最上面这个,就这样,这个我们说完

以后,我们来看练习题

6. SQL 99 连接 Employees 表和 Departments 表
			1).
			select *
			from employees join departments
			using(department_id)
			
			缺点: 要求两个表中必须有一样的列名.
			
			2).
			select *
			from employees e join departments d
			on e.department_id = d.department_id
			
			3).多表连接
			select e.last_name, d.department_name, l.city
			from employees e join departments d
			on e.department_id = d.department_id
			join locations l
			on d.location_id = l.location_id			     
			
7. SQL 99 的左外连接, 右外连接, 满外连接
			1).
			select last_name, department_name
			from employees e left outer join departments d
			on e.department_id = d.department_id
			
			2).
			select last_name, department_name
			from employees e right join departments d
			on e.department_id = d.department_id
			
			3).
			select last_name, department_name
			from employees e full join departments d
			on e.department_id = d.department_id	

多表查询

1.	显示所有员工的姓名，部门号和部门名称。
a)	select last_name,e.department_id,department_name
b)	from employees e,departments d
c)	where e.department_id = d.department_id(+)

方法二：
select last_name,e.department_id,department_name
from employees e left outer join departments d
on e.department_id = d.department_id

一个一个来,第一个,显示所有员工的姓名,部门号,和部门的名称,select 姓名last_name,部门号department_id,

部门名称department_name,这是我们比较典型的多表连接的问题,from,两种方式,我们先写第一种,employees e,

departments d,上面尤其是department_id,因为它两个表都有这一列,需要你显示的指明,他是在那个表中的,

你写e也行,也d也可以,务必加上过滤条件,e.department_id等于d.department_id,这就是我们实现了一个多表的

一个查询

select last_name,e.department_id,department_name from employees e,departments d where

e.department_id=d.department_id

这个就是我们实现了一个多表的查询

106条记录,内连接,你看,大家注意,所有员工你就他加上

select last_name,e.department_id,department_name from employees e,departments d

where e.department_id = d.department_id(+)

107个人,这是我们写的一种方式,希望大家掌握,那我们还讲了SQL99语法的方式,我们这里写方法二,

那么这个怎么写,还是查询这样几个信息,我在这直接改了,他join他,on,把加号去掉,你要是左外连接的话,

加上一个left outer

select last_name,e.department_id,department_name from employees e left outer join 

departments d on e.department_id=d.department_id

两种方式你都给他掌握,我们来看第二个题,

2.	查询90号部门员工的job_id和90号部门的location_id
a)	select distinct job_id,location_id
b)	from employees e left outer join departments d
c)	on e.department_id = d.department_id
d)	where d.department_id = 90

90号部门员工的job_id,和location_id,我们看一下这个,job_id是不是只在employees表里边,

location_id在departments表里边,所以我们是不是需要这两个表,好了,再来,select,他只查job_id,

和location_id,from employees e,这里你使用哪一种方式都可以,要是SQL99语法就是join departments,

on e.department_id等于d.department_id,同时呢,还有个条件90号部门,那就是d.department_id等于90,

90号部门,这个你用e也可以,有连接条件了,他的90号部门的信息,就这样,有的员工没有job_id,在90号部门

没有这个信息,那这个怎么办,那就给你加上一个left呗,保险起见

select job_id,location_id from employees e left join departments d on 

e.department_id=d.department_id where e.department_id=90

这三个,我发现后两个一样,后两个一样,这个公司有三个人,你要是想去重的话,加上一个distinct就Ok了

select distinct job_id,location_id from employees e left join departments d 

on e.department_id=d.department_id where e.department_id=90

3.	选择所有有奖金的员工的
last_name , department_name , location_id , city
select last_name,department_name,d.location_id,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where e.commission_pct is not null

选择有奖金的员工,select,输出这四个信息,这个四个信息,我们看标点符号,要特别注意一下,我们都是在英文的格式

下的,中文的支持一定要给他改过来,from两种方式都行,假设我还是用SQL99语法,employees e join departments d,

on e.department_id=d.department_id,然后再join,locations l,on d.location_id=l.location_id,几个表之间的

连接条件你得熟悉一下,要不然你没法写了,然后看一下有没有不明确列的,location_id需要指明是d表还是l表,其他几个

不写也没事,所有有奖金的员工,这个是不是还没有加这个条件,加一个where,e.commission_pct is not null,有奖金的

这样几个信息

select last_name,department_name,d.location_id,city from employees e join departments d 

on e.department_id=d.department_id join locations l on d.location_id=l.location_id

where e.commission_pct is not null

4.	选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 
select last_name , job_id , e.department_id , department_name
from employees e ,departments d,locations l
where e.department_id = d.department_id and l.city = 'Toronto' and d.location_id = l.location_id

你如果仅仅看要输出的结果的话,仅仅涉及到了两个表,employees表和departments表,但是多了一个city,city只

存在于locations表,所以还是三个条件,select,然后from,我们换一种方式吧,employees e,逗号,departments d,

那你这里需要指明d的或者e的,都行,这不行,这还得有一个,locations l,条件是什么,要求你e的department_id

等于d的department_id,同时and,l的city要是Toronto,多伦多,加拿大的一个城市

select last_name,d.department_id,department_name from employees e,departments d,locations l

where e.department_id=d.department_id and l.city = 'Toronto'

最后你看一下是否有未明确的列,没有就OK

表或视图不存在,employees少个o

select last_name,d.deparment_id,department_name,city from employees e,departments d,

locations l where e.deparment_id=d.deparment_id and l.city = 'Toronto'

106条记录,看有没有问题,看看,显然不对是吧,是不是还要加上一个条件,否则会发生笛卡尔积的错误了,

d.location_id=l.location_id

select last_name,d.department_id,department_name,city from employees e,departments d,

locations l where e.department_id=d.department_id and d.location_id=.location_id

and l.city = 'Toronto'

我们是不是说了,你有三个表的话,如果他们做连接查询,至少得需要两个连接条件,我这里光写了一个,

所以这个千万不能丢,要加上,l.city=''是额外的一个连接条件,你再看结果

select e1.last_name "employees",e1.employee_id "Emp#",e2.last_name"Manger",e2.employee_id "Mgr#"
from employees e1,employees e2
where e1.manager_id = e2.employee_id(+)

员工的员工号以及管理者的姓名,员工号,他的员工号是他,他老板的员工号是他,他直接直属的一个管理者,

把所有的人都给输出出来,这是不是一个自连接,这是一个自连接,我们实现一下,select,他只是需要查询到

他的员工名,和他的id,select员工名,last_name,employee_id,后边还是一个last_name,employee_id,

from employees,这里我就不写什么叫员工,什么叫老板,第一个叫e1,employees e2,我让e1当做员工,e2当做

老板的表,前面都是e1,两个表加连接条件,e1.manager_id=e2.employee_id,这个题目我们写完了,但是这个别名

相当于,所以你给他改一改,last_name这里有一个别名,employees,id起的叫Emp#,Manager,Mgr#

select e1.last_name "employees",e1.employee_id "Emp#",e2.last_name "Manager",e2.employee_id "Mgr#"

from employees e1,employees e2 where e1.manager_id=e2.employee_id

我们运行一下结果看看

106条记录,把每一个人以及他的管理者给列出来,肯定有一个人没有管理者,有个人没有管理者,给他

输出出来的话,给他在这里整一个左外连接

select e1.last_name "employees",e1.employee_id "Emp#",e2.last_name "manager",e2.employee_id "Mgr#"

from employees e1,employees e2 where e1.manager_id=e2.employee_id(+)