1 什么是连接查询?
从一张表中单独查询,称为单表查询。
emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字。这种跨表查询,多张表联合起来查询数据,被称为连接查询。
2 连接查询的分类?
-
根据语法的年代分类:
SQL92:1992年的时候出现的语法
SQL99:1999年的时候出现的语法
我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子) -
根据表连接的方式分类:
内连接:
等值连接
非等值连接
自连接外连接:
左外连接(左连接)
右外连接(右连接)全连接(不讲)
3 当两张表进行连接查询时,没有任何条件的限制会发生什么现象?例:查询每个员工所在部门名称?
(1)查询emp表
mysql> select ename,deptno from emp;
(2)查询dept表
mysql> select * from dept;
(3)两张表连接没有任何条件限制:emp表与dept表连接的过程是每一条ename记录依次匹配所有的dname记录。
select ename,dname from emp, dept;
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是两张表条数的乘积,这种现象被称为:笛卡尔积现象。(笛卡尔发现的,这是一个数学现象。)
4 怎么避免笛卡尔积现象?
连接时加条件,满足这个条件的记录被筛选出来!
(1)从emp、dept中查询ename和dname,效率比较低
select
ename,dname
from
emp, dept
where
emp.deptno = dept.deptno;
(2) 仅仅在emp中查询ename、在dept中查询dname,效率比上一个好
select
emp.ename,dept.dname
from
emp, dept
where
emp.deptno = dept.deptno;
(3) 给表起别名,比较推荐使用
select
e.ename,d.dname
from
emp e, dept d
where
e.deptno = d.deptno; //SQL92语法。
5 内连接之等值连接。
案例:查询每个员工所在部门名称,显示员工名和部门名?
emp e和dept d表进行连接。条件是:e.deptno = d.deptno
- SQL92语法:
select
e.ename,d.dname
from
emp e, dept d
where
e.deptno = d.deptno;
sql92的缺点:结构不清晰,表的连接条件,和后期进一步筛选的条件,都放到了where后面。
- SQL99语法:
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
//inner可以省略(带着inner可读性更好!!!一眼就能看出来是内连接)
select
e.ename,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno; // 条件是等量关系,所以被称为等值连接。
sql99优点:表连接的条件是独立的,连接之后,如果还需要进一步筛选,再往后继续添加where
SQL99语法:
select
...
from
a
join
b
on
a和b的连接条件
where
筛选条件
6 内连接之非等值连接
案例:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级?
mysql> select * from emp;
mysql> select * from salgrade;
条件不是一个等量关系,称为非等值连接。
select
e.ename, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal; // 条件不是一个等量关系,称为非等值连接。
7 内连接之自连接
案例:查询员工的上级领导,要求显示员工名和对应的领导名?
mysql> select empno,ename,mgr from emp;
技巧:一张表看成两张表。
select
a.ename,b.ename
from
emp a
join
emp b
on
a.mgr = b.empno;
13条记录,没有KING。《内连接》
8 外连接
(1)内连接:(A和B连接,AB两张表没有主次关系。平等的。)
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno; //内连接的特点:完成能够匹配上这个条件的数据查询出来。
(2) 外连接(右外连接):
select
e.ename,d.dname
from
emp e
right join
dept d
on
e.deptno = d.deptno;
// outer是可以省略的,带着可读性强。
select
e.ename,d.dname
from
emp e
right outer join
dept d
on
e.deptno = d.deptno;
right代表什么:表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。
在外连接当中,两张表连接,产生了主次关系。
外连接(左外连接):
select
e.ename,d.dname
from
dept d
left join
emp e
on
e.deptno = d.deptno;
// outer是可以省略的,带着可读性强。
select
e.ename,d.dname
from
dept d
left outer join
emp e
on
e.deptno = d.deptno;
(3)总结
带有right的是右外连接,又叫做右连接。
带有left的是左外连接,又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。
(4)思考:外连接的查询结果条数一定是 >= 内连接的查询结果条数?
正确。
(5) 案例:查询每个员工的上级领导,要求显示所有员工的名字和领导名?
select
b.ename,a.ename
from
emp a
right join
emp b
on
b.mgr = a.empno;
9 三张表,四张表怎么连接?
(1)语法:
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件
一条SQL中内连接和外连接可以混合。都可以出现!
(2)案例:找出每个员工的部门名称以及工资等级,
要求显示员工名、部门名、薪资、薪资等级?
select
e.ename,e.sal,d.dname,s.grade
from
emp e
// emp表与dept表连接
join
dept d
on
e.deptno = d.deptno
// emp表与salgrade表连接
join
salgrade s
on
e.sal between s.losal and s.hisal;
案例:找出每个员工的部门名称以及工资等级,还有上级领导,
要求显示员工名、领导名、部门名、薪资、薪资等级?
select
e.ename,e.sal,d.dname,s.grade,l.ename
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp l
on
e.mgr = l.empno;