关于查询结果集的去重
mysql> select distinct job from emp; // distinct关键字去除重复记录
distinct只能出现在所有字段的最前面,表示后面所有字段联合起来去重。
案例:统计岗位的数量?
select count(distinct job) from emp;
连接查询
信息存储到一张表中,数据会存在大量的重复,导致数据的冗余。
根据表的连接方式来划分,包括:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接(左连接)
右外连接(右连接)
全连接(很少用)
在表的连接查询方面有一种现象被称为:笛卡尔积现象。(笛卡尔乘积现象)
笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。
关于表的别名:
select e.ename,d.dname from emp e,dept d;
表的别名有什么好处?
第一:执行效率高。
第二:可读性好。
怎么避免笛卡尔积现象?加条件进行过滤。
思考:避免了笛卡尔积现象,会减少记录的匹配次数吗?
不会,次数还是56次。只不过显示的是有效记录。
内连接之等值连接:最大特点是:条件是等量关系。
// inner可以省略的,带着inner目的是可读性好一些。
select
e.ename,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
语法:
...
A
join
B
on
连接条件
where
...
SQL99语法结构更清晰一些:表的连接条件和后来的where条件分离了。
内连接之非等值连接:最大的特点是:连接条件中的关系是非等量关系。
找出每个员工的工资等级,要求显示员工名、工资、工资等级。
select
e.ename,e.sal,s.grade
from
emp e
inner join
salgrade s
on
e.sal between s.losal and s.hisal;
自连接:最大的特点是:一张表看做两张表。自己连接自己。
找出每个员工的上级领导,要求显示员工名和对应的领导名。
员工的领导编号 = 领导的员工编号
select
a.ename as '员工名',b.ename as '领导名'
from
emp a
inner join
emp b
on
a.mgr = b.empno;
外连接
什么是外连接,和内连接有什么区别?
内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。
AB两张表没有主副之分,两张表是平等的。
外连接:
假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中
的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
外连接的分类?
左外连接(左连接):表示左边的这张表是主表。
右外连接(右连接):表示右边的这张表是主表。
左连接有右连接的写法,右连接也会有对应的左连接的写法。
找出每个员工的上级领导?(所有员工必须全部查询出来。)
外连接:(左外连接/左连接)
// outer是可以省略的。
select
a.ename '员工', b.ename '领导'
from
emp a
left outer join
emp b
on
a.mgr = b.empno;
外连接最重要的特点是:主表的数据无条件的全部查询出来。
找出哪个部门没有员工?
select
d.*
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
e.empno is null;
多张表连接:
....
A
join
B
join
C
on
...
表示:A表和B表先进行表连接,连接之后A表继续和C表进行连接。
找出每一个员工的部门名称、工资等级、以及上级领导。
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;
子查询
什么是子查询?子查询都可以出现在哪里?
select语句当中嵌套select语句,被嵌套的select语句是子查询。
子查询可以出现在哪里?
select
..(select).
from
..(select).
where
..(select).
where子句中使用子查询
找出高于平均薪资的员工信息。
select * from emp where sal > (select avg(sal) from emp);
from后面嵌套子查询
找出每个部门平均薪水的等级。
第一步:找出每个部门平均薪水(按照部门编号分组,求sal的平均值)
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步:将以上的查询结果当做临时表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) t0
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
+--------+-------------+-------+
案例:找出每个部门平均的薪水等级。
第一步:找出每个员工的薪水等级。
select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+--------+-------+
| ename | sal | deptno | grade |
+--------+---------+--------+-------+
| SMITH | 800.00 | 20 | 1 |
| ALLEN | 1600.00 | 30 | 3 |
| WARD | 1250.00 | 30 | 2 |
| JONES | 2975.00 | 20 | 4 |
| MARTIN | 1250.00 | 30 | 2 |
| BLAKE | 2850.00 | 30 | 4 |
| CLARK | 2450.00 | 10 | 4 |
| SCOTT | 3000.00 | 20 | 4 |
| KING | 5000.00 | 10 | 5 |
| TURNER | 1500.00 | 30 | 3 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| FORD | 3000.00 | 20 | 4 |
| MILLER | 1300.00 | 10 | 2 |
+--------+---------+--------+-------+
第二步:基于以上结果,继续按照deptno分组,求grade平均值。
select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
在select后面嵌套子查询
找出每个员工所在的部门名称,要求显示员工名和部门名。
select
e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;