连接查询
连接查询是多张表联合起来查询数据,根据语法可分为SQL92(已经不常用)和SQL99,根据表连接的方式分为内连接和外连接
内连接分为:等值连接,非等值连接,自连接
外连接分为:左外连接(左连接)、右外连接(右连接)
全连接
-
两张表进行连接查询的时候,没有任何条件的限制会发生什么?
最终查询结果条数是两张表条数的乘积,被称为笛卡尔积现象
-
如何避免笛卡尔积现象?
连接时加条件,满足条件的现象被筛选出来
# SQL92 select emp.deptno, dept.deptno // e.deptno, d.deptno from emp, dept // emp e, dept d where emp.deptno = dept.deptno; // e.deptno, = d.deptno
表的连接次数越多效率越低,因此,在建表和查询过程中,慎用连接
内连接
将两张表能够匹配上某些条件的数据查询出来,没有主次关系
-
等值连接,查询每个员工所在部门名称,显示员工名和部门名
# SQL92 select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
另一种写法(将表连接和where分离),使用
join
关键词# SQL99 select e.ename, d.dname from emp e join // inner join dept d on e.deptno = d.deptno;
-
非等值连接,条件值不是一个等量关系
mysql> select * from salgrade; +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+ # 找出每个员工的工资等级 select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and e.sal<=s.hisal;
-
自连接,一张表看做两张表,使用别名区分
示例:查询员工的上级领导,显示员工的上级领导的姓名select a.ename as 'employee name', b.ename as 'manager name' from emp a join emp b on // on not where a.mgr = b.empno; +---------------+--------------+ | employee name | manager name | +---------------+--------------+ | SMITH | FORD | +---------------+--------------+
外连接
在外连接中,两张表连接产生了主次关系
外连接的查询结果条数一定大于等于内连接的查询结果条数
-
右连接:
select e.ename, d.dname from dept d // dept表只包含部门编号和名称 left join // left outer join emp e //主要查询emp员工表,后者作为主表,先dept后emp,右连接 on e.deptno = d.deptno
right
代表什么:表示将右边的表看作是主表,将这张表的数据全部查询出来,捎带着关联查询左边的表 -
左连接:
select e.ename, d.dname from emp e right join // right outer join dept d // 查询结果包含所有员工信息,emp仍作为主表 on e.deptno = d.deptno
left
代表什么:表示将左边的表看作是主表,将这张表的数据全部查询出来,捎带着关联查询右边的表,上述SQL语句执行效果与1等同 -
查询每个员工的上级领导,要求显示所有员工的名字和领导名
select a.ename as 'employee name', b.ename as 'manager name' from emp a left join emp b on a.mgr = b.empno;
总结:如果要将一张表的所有列展现出来,那么把这张表作为主表
多张表的连接
语法
使用join on
关键字,同时确保from
关键字后的表与多张表具有关系
select
...
from
a
join
b
on
// a和b的连接条件
join
c
on
// a和c的连接条件
right join
d
on
// a和d的连接条件
示例:找出每个员工的部门名称以及工资等级,还有上级领导,
要求显示员工名、领导名、部门名、薪资、薪资等级?
select e.ename as emplyee, m.ename as manager, d.dname as department, e.sal as salary, s.grade as grade
from
emp e
left join
emp m
on
e.mgr = m.empno
join
dept d
on
e.deptno = d.deptno
left join
salgrade s
on
e.sal between s.losal and s.hisal;
+---------+---------+------------+---------+-------+
| emplyee | manager | department | salary | grade |
+---------+---------+------------+---------+-------+
| SMITH | FORD | RESEARCH | 800.00 | 1 |
+---------+---------+------------+---------+-------+