使用到的表
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/2470df848f86218a310f525b4db75f56.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/af5bdf4e7b7cf6f2b5ee8c1b46c830e0.png)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e1c1e719859da19990abbf8ad7eb97e2.png)
连接查询
什么是连接查询
当我们从一张表中进行数据的查询时称为单表查询。
当我们从emp表和dept表联合起来查询数据时,从emp表中取员工名字,从dept表中取部门名字,这种跨表查询,多张表联合起来查询数据,被称为连接查询。
连接查询的分类
根据语法的年代分类:
SQL92:1992年时出现的语法
SQL99:1999年时出现的语法
根据表连接的方式进行分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接(左连接)
右外连接(右连接)
全连接
笛卡尔积
笛卡尔乘积是指在数学中,两个集合X和Y的笛卡尔积(Cartesian product),又称直积,表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员
将员工表和部门表进行连接查询,没有任何条件限制,查询出员工姓名和部门名
select ename,dname from emp,dept;
可以看到总共有56条数据,员工表中的每一个员工都与部门表中的4个部门进行了一次匹配,得到 14*4=56条数据,即两张表数据条数的乘积。
想要避免笛卡尔积的现象,就需要在表连接时附加条件,将满足所给条件的记录筛选出来。
给数据库表取别名,并根据部门编号deptno进行连接查询
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; // (SQL92的语法)
内连接
内连接之等值连接
案例:查询每个员工所在部门名称,显示员工名和部门名。
需要emp e表和dept d表进行连接,条件是e.deptno=d.deptno
// SQL92语法
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno=d.deptno;
// SQL99语法
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno;
// join前面的inner可以省略
select
e.ename,d.dname
from
emp e
inner join
dept d
on
e.deptno=d.deptno;
SQL92语法:结构不清晰,表的连接条件和后期进一步筛选的条件都放到了where后面
SQL99语法:表连接的条件是独立的,连接之后如果还需要进一步筛选,则继续添加where进行条件查询
等值连接的查询条件是等量关系
内连接之非等值连接
案例:查询出每个员工的薪资等级,要求显示员工名,薪资和薪资等级。
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal; // 这里的条件不是一个等量关系
内连接之自连接
案例:查询emp表中员工的领导,自连接,连接条件e1.mgr=e2.empno
select
e1.ename as '员工名',e2.ename as '领导名'
from
emp e1
join
emp e2
on
e1.mgr=e2.empno;
13条记录,因为king没有领导。
自连接的技巧就是将一张表看作两张表去操作。
外连接
案例:查询部门名以及部门所对应的员工名
先采用内连接进行查询
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno;
因为在员工表中,没有对应部门号为40的,所以查询结果中也没有部门编号为40的部门。
内连接的特点:查询出完全能够匹配上条件的数据
外连接:
// 外连接(右外连接)
select
e.ename,d.dname
from
emp e
right outer join
dept d
on
e.deptno=d.deptno;
// 外连接(左外连接)
select
e.ename,d.dname
from
dept d
left outer join
emp e
on
e.deptno=d.deptno;
right:表示将join关键字右边的表当作主表,主要是为了将这张表的数据全部查询出来,捎带着查询左边的表。在外连接中,两张表产生了主次关系。
而上面的查询语句的意思就是右外连接查询部门表中的部门,即使员工表中没有对应的部门员工,也将部门查出,如上图所示,部门“OPERATIONS”在员工表中不能存在,ename就为null
带有right的是右外连接,又叫右连接。带有left的是左外连接,又叫左连接。任何一个右连接都有左连接的写法,反之亦然。
right和left的后面的outer是可以省略的
内连接和外连接的区别
内连接和外连接:
内连接时,表之间的关系是平等的,没有主次之分,匹配得上的数据就查询到并进行显示,匹配不到的数据就不显示。
而外连接时表分主次关系,对于主表来说,无论能不能被匹配到,数据都会被显示。
区分内连接和外连接:
关键字inner和outer,不过这两个关键字是可以省略的。
所以当我们要判断一个SQL语句是内连接还是外连接时主要看其是否带有right或left关键字,
带有right或left关键字的连接是外连接,不带的是内连接。
需要注意的是,外连接的查询结果条数一定是大于内连接的查询结果条数的。
多表联查
语法:
select
...
from
...
(right/left) join
...
on
...
(right/left) join
...
on
...
(right/left) join
...
on
...
案例:找出每个员工的部门名称以及工资等级,要求显示员工姓名、部门名、薪资和薪资等级。
涉及到三张表,emp、dept、salgrade。
select
e.ename,d.dname,e.sal,s.grade
from
emp e
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
order by
e.sal desc;
在一条SQL语句中,内连接和外连接是可以混合使用的
案例:找出每个员工的部门名称以及工资等级和上级领导,要求显示员工姓名、领导名、部门名、薪资和薪资等级。
这里要求显示每个员工的上级领导,如果再采取外连接进行查询,KING 的数据将查不出来
select
e.ename,e1.ename,d.dname,e.sal,s.grade
from
emp e
join
emp e1
on
e.mgr=e1.empno
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
order by
e.sal desc;
如下图所示,可以看到,查询的结果中没有关于"KING"的信息,这是因为我们所采用的是内连接进行的查询,查询不到KING的领导,则他的数据就被忽略了。
采用左外连接,将员工表作为主表,即使KING匹配不到条件,也会将他的数据进行显示
select
e.ename,e1.ename,d.dname,e.sal,s.grade
from
emp e
left join
emp e1
on
e.mgr=e1.empno
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
order by
e.sal desc;