1、查询结果去重(distinct)
selece distinct job from emp;
select ename,distinct job from emp; 是错误
select distinct job,deptno from emp;正确,两个字段联合去重
注意:distinct 只能出现在所有的字段的最前面(的)
举例:
(1)统计岗位的数量
select count(distinct job) from emp;
2、连接查询
(1)什么是连接查询?
在实际开发中,大部分情况下都不是从单表查询数据,一般是多张表联合查询去取最终的结果。 在实际开发中,一般一个业务对应多张表,比如:学生与班级,起码两张表。
(2)连接查询分类
根据表的连接方式来划分,包括:
①内连接
等值连接
非等值连接
自连接
②外连接
左外连接(左连接)
右外连接(右连接)
③全连接
(3)在表的连接查询方面有一种现象被称为:笛卡儿积现象
例如:将员工表 emp 和 部门表 dept 联合起来
select ename,dname from emp,dept;
这里如果 emp 有 10 条记录,dept 有 5条记录,则最终查出来的有 10 * 5 = 50 条数据。(这便是 笛卡儿积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终查询的结果条数是两张表记录条数的乘积)
(4)类的别名
select e.ename,d.dname from emp e,dept d;
(5)怎样避免笛卡儿积现象?
回答:加条件进行过滤
思考:避免了笛卡儿积现象,会减少记录的匹配次数吗?----- 不会,次数还是56次,只不过显示的是有效记录。
3、内连接
语法:(inner 可以省略)
...
A
inner join
B
on
连接条件
where
...
(1)等值连接
案例:找出每一个员工的部门名称,要求显示员工名和部门名。
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno;
(2)非等值连接
案例:找出每个员工的薪资等级,要求显示员工名,薪资,薪资等级
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and hisal;
(3)自连接
(最大的特点:一张表可以看作两张表。自己连接自己)
select
a.ename as '员工名',b.ename as '领导名'
from
emp a
inner join
emp b
on
a.mgr = b.empno;
4、外连接
(1) 什么是外连接,和内连接有什么区别?
内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。
AB两张表没有主副之分,两张表是平等的
外连接:
假设A和B进行连接,使用外连接的话,AB两张表中有一张是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表上的数据匹配上,副表自动模拟出null与之匹配。特点:主表数据 无条件都查询出来
外连接的分类:
① 左外连接(左连接):表示左边的这张表是主表
② 右外连接(右连接):表示右边的这张表是主表左连接有右连接的写法,右连接也会有对应的左连接
案例:
(1)找出每个员工的上级领导?(采用外连接的方式,对于没有领导的员工,比如老总,也能查出数据,而内连接就会丢失该项数据)
外连接(左外连接/左连接)
//outer可以省略
select
a.ename as '员工名',b.ename as '领导名'
from
emp a
left outer join
emp b
on
a.mgr = b.empno;
外连接(右外连接/右连接)结果一样
select
a.ename as '员工名',b.ename as '领导名'
from
emp b
right join
emp a
on
a.mgr = b.empno;
(2)找出那个部门没有员工?
select
d.*
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
e.empno is null;
5、三张表的连接查询
...
Ajoin
B
on
...
join
C
on
...
案例:
(1)找出每一个员工的部门名称以及工资等级
select
e.ename,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;
(2)找出每一个员工的部门名称、工资等级以及上级领导
select
e.ename '员工',d.dname,s.grade ,e1.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 e1
on
e.mgr = e1.empno;
6、子查询
(1)什么是子查询?子查询都可以出现在哪里?
select 当中嵌套select语句,被嵌套的select语句是子查询。
子查询可以出现在哪里?
select
...(select).
from
..(select).
where
..(select)
(2) where子句中使用子查询
案例:找出高于平均薪资的员工信息
select * from emp where sal > avg(sal);//错误的写法,where后面不能直接使用分组语句
第一步,找出平均薪资
select avg(sal) from emp ;第二步,where过滤
select * from emp where sal > (select avg(sal) from emp);
(3)from后面嵌套子查询
案例:找出每个部门平均薪资的薪资等级
第一步:找出每个部门平均薪资(按照部门编号分组,求sal)
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步,将以上的查询结果当作临时表t,让t 表何salgrade s表连接,条件是:t.avgsal between s.losal and s.hisal;
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
找出每个部门平均的薪资等级
第一步,找出每个员工的薪资等级
select
e.ename ,e.sal,e.deptno,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
第二步,基于以上结果,继续按照deptno分组,求grade平均值
select
deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
(4) 在select后面嵌套子查询
案例:找出每个员工所在的部门名称,要求显示部门名的员工名
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
select
e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
7、union (可以将查询的结果集相加)
案例:找出工作岗位是SALESMAN和MANAGER的员工?
第一种:select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
第二种:select ename,job from emp where job in('SALESMAN','MANAGER');
第三种:
select ename,job from emp where job = 'SALESMAN'
union
select ename,job from emp where job = 'MANAGER';
8、limit
(1)limit是MySQL特有的,其他数据库中没有,不通用(Oracle中有一个相同的机制,叫做rownum)
(2)limit取结果集 中的部分数据,这是它的作用。
(3)语法机制:
limit startIndex, length
startIndex表示起始位置,从0开始,0表示第一条数据
length表示取几个
案例:取出工资前五名的员工(思路:降序取前五个)
select ename,sal from emp order by sal desc limit 0,5;
(select ename,sal from emp order by sal desc limit 5;)
找出工资排名在第四到第九名的员工
select ename,sal from emp order by sal desc limit 3,6;
(4)limit是sql语句最后执行的一个环节
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
limit 7
...
参考:动力节点数据库课程