连接查询
一、什么是连接查询
从一张表中单独查询,称为单表查询。
emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字。这种跨表查询,多张联合起来查询数据,被称为连接查询。
二、连接查询的分类
根据语法的年代分类:
SQL92:1992年的时候出现的语法
SQL99:1999年的时候出现的语法
此处重点写的是SQL99
根据表连接的方式分类:
-
内连接:
1) 等值连接
2) 非等值连接
3) 自连接 -
外连接:
1)左外连接(左连接)
2)右外连接(右连接) -
全连接(用的比较少)
三、笛卡尔积现象及如何避免
思考1:当两张表进行连接查询时,没有任何条件的限制会发生什么现象?
案例:查询每个员工所在部门名称
当两张表进行连接查询,没有任何条件限制的时候,最终查询的结果条数,是两张表条数的乘积,这种现象被称为:笛卡尔积现象。
怎么避免笛卡尔积现象?
连接时加条件,满足这个条件的记录被筛选出来
select
ename, dname
from
emp, dept
where
emp.deptno = dept.deptno;
思考2:最终查询的结果条数减少了,但是匹配的过程中,匹配的次数减少了吗?
其实最终匹配的次数还是没有减少,只是最后呈现的结果条数减少了。
上面的SQL语句还可以进行一下改进:
select
emp.ename, dep.dname
from
emp, dept
where
emp.ename = dept.deptno;
// 表起别名。很重要,效率问题
select
e.ename, d.dname
from
emp e, dept d
where
e.deptno = d.deptno;
// 这种语法是SQL92语法
注意:通过笛卡尔积现象得出,应当尽量减少表连接的次数,不然数据量大时候,查询的效率会很低。
四、内连接之等值连接
案例:查询每个员工所在部门名称,显示员工名和部门名
SQL92写法:
select
e.ename, d.dname
from
emp e, dept d
where
e.deptno = d.deptno;
SQL92语法的缺点:结构不够清晰,表的连接条件,和后期进一步筛选的条件,都放到了where后面。
SQL99写法:
select
e.name, d.ename
from
emp e
join
dept d
on
e.deptno = d.deptno;
SQL99语法的有点:表连接的条件是独立的,连接之后如果还需要进一步的筛选,再往后添加where
select
…
from
a
join
b
on
a和b的连接条件
where
筛选条件
五、内连接之非等值连接
案例:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级
select
e.ename, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal; // 条件不是一个等量关系,称为非等值连接
六、内连接之自连接
案例:查询员工的上级领导,要求显示员工名和对应的领导名
技巧是:将一张表看成两张表
select
a.ename as ‘员工名’, b.ename as ‘领导名’
from
emp a
join
emp b
on
a.mgr = b.empno;
以上就是内连接中的‘自连接’,技巧就是:一张表看作两张表。
内连接:(A和B连接,AB两张表没有主次关系,平等的。)
七、外连接
select
e.ename, d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno; // 内连接的特点:完成能够匹配上这个条件的数据查询出来。
外连接(右外连接):
select
e.ename, d.dname
from
emp e
right join
dept d
on
e.deptno = d.deptno;
外连接(左外连接):
select
e.ename, d.dname
from
emp e
right join
dept d
on
e.deptno = d.deptno;
right代表的含义是:表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。(如果右表中有左表中没有的内容,那么左表中的栏,会出现NULL)
left同理。
在外连接中,两张表连接,产生了主次关系。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。
实际上join的前面有一个outer,这个是可以省略的,只是加上后可读性比较强。
思考:外连接的查询结果条数一定是 >= 内连接的查询结果条数吗?
案例:查询每个员工的上级领导,要求显示所有员工的名字和领导名。
八、多张表连接
语法:
select
…
from
a
join
b
on
a和b的连接条件
join
c
on
a和c 的连接条件
join
d
on
a和d的连接条件
同时在一条SQL中,内连接和外连接可以混合出现。
案例:找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级。
select
e.ename, e.sal, d.dname, 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;
案例:找出每个员工的部门名称以及工资等级,还有上级领导,要求显示员工名、领导名、部门名、薪资、薪资等级。
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;
九、子查询
什么是子查询?
select语句中嵌套select语句,被嵌套的select语句称为子查询。
子查询都可以出现在哪里?
select
…(select)…
from
…(select)…
where
…(select)…
where子句中的子查询
案例:找出比最低工资高的员工姓名和工资
错误写法:
select
ename, sal
from
emp
where
sal > min(sal);
where子句中不能直接使用分组函数
正确实现思路 :
第一步:查询最低工资
select min(sal) from emp;
第二步:找出>800的
select ename, sal from emp where sal > 800;
第三步:合并
select ename, sal from emp where sal > (select min(sal) from emp);
from子句中的子查询
注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。(技巧)
案例:找出每个岗位的平均工资的薪资等级。
第一步:找出每岗位的平均薪资(按照岗位分组求平均值)
select job, avg(sal) from emp group by job;
第二步:把以上查询结果就当做一张真是存在的临时表
第三步:合并
select
t.*, s.grade
from
(select job, avg(sal) as avgsal form emp group by job) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
select后面出现的子查询
案例:找出每个员工的部门名称,要求显示员工名,部门名
select e.ename, e.deptno, (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
但是这个有个缺陷,嵌套语句中查询的结果必须是一条。
注意:对于select后面的子查询来说,这个子查询只能一次返回1条结果,多于1条,就会报错。
十、union合并查询结果集
案例:查询工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;
select ename,job from emp where job in(‘MANAGER’,‘SALESMAN’);
使用union进行查询结果的合并
select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’
union
select ename,job from emp where job in(‘MANAGER’,‘SALESMAN’);
union的效率要高一些。对于表连接来说,每次连接一次新表,则匹配的次数满足笛卡尔积,成倍增加。
但是union可以减少匹配的次数。在减少匹配次数的情况下,还可以完成两个结果集的拼接。
a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是:1000
a 连接 b一个结果:10 * 10 --> 100次
a 连接 c一个结果:10 * 10 --> 100次
使用union的话是:100次 + 100次 = 200次。(union把乘法变成了加法运算)
union在使用的时候有注意事项
//错误的:union在进行结果集合并的时候,要求两个结果集的列数相同。
select ename,job from emp where job = ‘MANAGER’
union
select ename from emp where job = ‘SALESMAN’;
// MYSQL可以,oracle语法严格 ,不可以,报错。要求:结果集合并时列和列的数据类型也要一致。
select ename,job from emp where job = ‘MANAGER’
union
select ename,sal from emp where job = ‘SALESMAN’;
十一、limit
limit作用:将查询结果集的一部分取出来。通常使用在分页查询当中。
分页的作用是为了提高用户的体验,因为一次全部都查出来,用户体验差。
可以一页一页翻页看。
limit使用:
完整用法:
limit startIndex, length
startIndex是起始下标,length是长度。
起始下标从0开始。
缺省用法:limit 5; 这是取前5.
按照薪资降序,取出排名在前5名的员工?
select
ename,sal
from
emp
order by
sal desc
limit 5; //取前5
select
ename,sal
from
emp
order by
sal desc
limit 0,5;
注意:mysql当中limit在order by之后执行
案例:取出工资排名在[3-5]名的员工?
select
ename,sal
from
emp
order by
sal desc
limit 2, 3;
2表示起始位置从下标2开始,就是第三条记录。
3表示长度。
案例:取出工资排名在[5-9]名的员工?
select
ename,sal
from
emp
order by
sal desc
imit 4, 5;
十二、分页
每页显示3条记录
第1页:limit 0,3 [0 1 2]
第2页:limit 3,3 [3 4 5]
第3页:limit 6,3 [6 7 8]
第4页:limit 9,3 [9 10 11]
每页显示pageSize条记录
第pageNo页:limit (pageNo - 1) * pageSize , pageSize
public static void main(String[] args){
// 用户提交过来一个页码,以及每页显示的记录条数
int pageNo = 5; //第5页
int pageSize = 10; //每页显示10条
int startIndex = (pageNo - 1) * pageSize;
String sql = "select ...limit " + startIndex + ", " + pageSize;
}
记公式:
limit (pageNo-1)*pageSize , pageSize
关于DQL语句的大总结:
select
…
from
…
where
…
group by
…
having
…
order by
…
limit
…
执行顺序
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit…