mysql查询
- 一、分组查询
- 1、分组查询设计的两个字句：
- 2、group by
- 2.1 order by 【表示通过哪个或者哪些字段进行排序】
- 2.2 案例：找出每个工作岗位的最高薪水【先按照工作岗位分组，使用max函数求每一组的最高薪水】
- 2.3 案例：计算每个部门的平均薪水【按部门编号分组，对每一组求平均薪水】
- 2.4 案例：计算不同部门不同岗位的最高薪水
- 2.5 案例：找出每个岗位的最高薪水，除去岗位为 MANAGER 的部分。
- 3、having
- 3.1案例：找出每个工作岗位的平均薪水。要求显示平均薪水大于1500。
- 4、 一个完整的DQL语句总结：
- 二、连接查询
- 1、连接查询根据出现的年代分类：
- 2、连接查询根据连接方式分类：
- 3、当多张表进行连接查询，若没有任何条件进行限制，会发生什么现象？
- 4、案例：查询每一个员工所在部门的名称，要求最终显示员工名和对应部门名。
- 5、案例：找出每一个员工对应的工资等级，要求显示员工名，工资，工资等级等。
- 6、案例：找出每一个员工的上级领导，要求显示员工名以及对应的领导名。
- 7、案例：找出每个员工对应的部门名称，要求部门名称全部显示。
- 8、案例：找出每一个员工对应的领导名，要求显示所有的员工。
- 9、案例：找出每一个员工对应的部门名称，以及该员工对应的工资等级。要求显示员工名、部门名、工资等级。
- 三、子查询
- 1、什么是子查询？
- 2、子查询可以出现在哪里？
- 3、where 后面使用select子查询
- 4、from后面使用子查询【小窍门：将查询结果当作临时表】
- 5、在select后面使用子查询【了解即可】
一、分组查询
1、分组查询设计的两个字句：
- group by
- having2、group by
2.1 order by 【表示通过哪个或者哪些字段进行排序】
group by 【表示通过哪个或者哪些字段进行分组】
2.2 案例：找出每个工作岗位的最高薪水【先按照工作岗位分组，使用max函数求每一组的最高薪水】
select
max(sal)
from
emp
group by
job;以上语句，先按照job分组，然后对每一组使用max（sal）求最高薪水
select
job,max（sal）
from
emp
group by
job;重点：若一条DQL语句中有group by 字句，那么select关键字后面只能跟参与分组的字段和分组函数。
2.3 案例：计算每个部门的平均薪水【按部门编号分组，对每一组求平均薪水】
select deptno,avg(sal) as avgsal from emp group by deptno;±-------±------------+
| deptno | avgsal |
±-------±------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
±-------±------------+
2.4 案例：计算不同部门不同岗位的最高薪水
select deptno,job,max(sal) from emp group by deptno,job;2.5 案例：找出每个岗位的最高薪水，除去岗位为 MANAGER 的部分。
select job,max(sal) from emp where job<>'MANAGER' group by job;3、having
having和where功能相同，都是为了 完成数据的过滤。两者后面都是添加条件。
where在group by 之前完成过滤；
having在group by之后完成过滤。
3.1案例：找出每个工作岗位的平均薪水。要求显示平均薪水大于1500。
select job,avg(sal) from emp where avg(sal)>1500 group by job;
ERROR 1111 (HY000): Invalid use of group function
原因：where关键字后面不能直接使用分组函数，分组函数必须在分组完成后执行mysql> select job,avg(sal) from emp group by job having avg(sal)>1500;±----------±------------+
| job | avg(sal) |
±----------±------------+
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
±----------±------------+
原则：由于两个函数的效率不同问题，尽量在where 中过滤，无法过滤的数据，通常先分组之后在过滤，这时候可以选择使用having。
4、 一个完整的DQL语句总结：
select…
from …
where…
group by …
having …
order by…
第一：以上的关键字顺序不能变，严格遵守
第二：执行顺序：
1、from 从某张表中检索数据
2、where 经过某条件进行过滤
3、group by 然后分组
4、having 分组之后不满意的在过滤
5、select查询出来
6、order by 排序输出
二、连接查询
1、连接查询根据出现的年代分类：
-sql92
-sql99【主要掌握】
2、连接查询根据连接方式分类：
-内连接: 等值连接 \ 非等值连接 \ 自连接
-外连接:左外连接\右外链接
-全连接【使用很少】
3、当多张表进行连接查询，若没有任何条件进行限制，会发生什么现象？
案例：查询每一个员工所在部门的名称，要求最终显示员工名和对应部门名。
emp 员工表
±-------±-------+
| ename | deptno |
±-------±-------+
| SIMITH | 20 |
| ALLEN | 30 |
| WARD | 30 |
| JONES | 20 |
| MARTIN | 30 |
| BLAKE | 30 |
| CLARK | 10 |
| SCOTT | 20 |
| KING | 10 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| FORD | 20 |
| MILLER | 10 |
±-------±-------+
dept部门表
±-------±------------+
| deptno | dname |
±-------±------------+
| 10 | ACCOUNTING |
| 20 | RESEARCHING |
| 30 | SALES |
| 40 | OPERATIONS |
±-------±------------+
主要分析：多张表连接查询，若没有任何条件进行限制，会发生什么现象？
小知识点：在进行夺标联合查询时，尽量给表起别名。这样可读性高。
select e.ename,d.dname from emp e ,dept d;±-------±------------+
| ename | dname |
±-------±------------+
| SIMITH | ACCOUNTING |
| SIMITH | RESEARCHING |
| SIMITH | SALES |
| SIMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCHING |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
…
56 rows in set (0.00 sec)
结论：若两张表进行连接查询的时候，没有任何条件限制，最终查询结果总数总是两张表记录条数乘积，这种现象被称为笛卡尔积现象。为了避免笛卡尔积现象的发生，必须在表链接的时候添加限制条件。
4、案例：查询每一个员工所在部门的名称，要求最终显示员工名和对应部门名。
sql92语法：内连接中的等值连接
mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;sql99语法：内连接中的等值连接
sql99语法的优点：表连接独立出来了，结构清晰，对表连接不满意的话，可以再追加where进行过滤。
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;±-------±------------+
| ename | dname |
±-------±------------+
| SIMITH | RESEARCHING |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCHING |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCHING |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCHING |
| JAMES | SALES |
| FORD | RESEARCHING |
| MILLER | ACCOUNTING |
±-------±------------+
14 rows in set (0.01 sec)
5、案例：找出每一个员工对应的工资等级，要求显示员工名，工资，工资等级等。
sql92语法：内连接中的非等值连接
select e.ename,s.grade from emp e ,salgrade s where e.sal>=s.losal and e.sal<= s.hisal;sql99语法：内连接中的非等值连接
select e.ename,e.sal, s.grade from emp e inner join salgrade s on e.sal betwee
n s.losal and s.hisal; //inner可以省略±-------±--------±------+
| ename | sal | grade |
±-------±--------±------+
| SIMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
±-------±--------±------+
6、案例：找出每一个员工的上级领导，要求显示员工名以及对应的领导名。
sql92语法：内连接中的自连接
select a.ename empname, b.ename leadername from emp a , emp b where a.mgr=b.empno;sql99语法：内连接中的自连接
select a.ename empname, b.ename leadername from emp a inner join emp b on a.mgr=b.empno;//inner可以省略±--------±-----------+
| empname | leadername |
±--------±-----------+
| SIMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
±--------±-----------+
7、案例：找出每个员工对应的部门名称，要求部门名称全部显示。
内连接：
A表和B表能够完全匹配的记录查询出来，被称为内连接；
外连接：【只掌握SQL99语法】
A表和B表能够完全匹配的记录查询出来之外，将其中一张表的记录全部无条件能查询出来，对方表没有匹配的记录，会自动模拟出NULL与之匹配，这种查询被称为外连接。
外连接的查询结果条数>=内连接的查询结果条数
sql99语法：外连接中的右外连接【右连接】
select e.ename,d.dname from emp e right outer join dept d on e.deptno=d.deptno;//outer可以省略sql99语法：外连接中的左外连接【左连接】
select e.ename,d.dname from dept d left outer join emp e on e.deptno=d.deptno;//outer可以省略任何一个右外连接都可以写成左外连接，任何一个左外连接也同样可以写成右外连接。
±-------±------------+
| ename | dname |
±-------±------------+
| SIMITH | RESEARCHING |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCHING |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCHING |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCHING |
| JAMES | SALES |
| FORD | RESEARCHING |
| MILLER | ACCOUNTING |
| NULL | OPERATIONS |
±-------±------------+
为什么inner和outer可以省略，加上去有什么好处？
-可以省略，区分内外连接依靠的不是这些关键字，而是看SQL语句中是否存在left/right。若存在，表示一定是一个外连接，其他都是内连接；
-加上去的好处是增强可读性。
8、案例：找出每一个员工对应的领导名，要求显示所有的员工。
select a.ename empname,b.ename leadername from emp a left join emp b on a
.mgr=b.empno;±--------±-----------+
| empname | leadername |
±--------±-----------+
| SIMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
±--------±-----------+
14 rows in set (0.00 sec)
9、案例：找出每一个员工对应的部门名称，以及该员工对应的工资等级。要求显示员工名、部门名、工资等级。
多张表格进行表连接的语法格式：
select xxx from a join b on 条件 join c on 条件；
原理：a和b表进行表连接之后，a表再和c表进行表连接。
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;±-------±------------±------+
| ename | dname | grade |
±-------±------------±------+
| SIMITH | RESEARCHING | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCHING | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCHING | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCHING | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCHING | 4 |
| MILLER | ACCOUNTING | 2 |
±-------±------------±------+
三、子查询
1、什么是子查询？
-select语句嵌套select语句2、子查询可以出现在哪里？
select...(select), from.....(select),where....(select)3、where 后面使用select子查询
案例：找出薪水比公司平均薪水高的员工，要求显示员工名和薪水。
select ename ,sal from emp where sal > avg(sal);
以上语句错误，分组函数不能直接使用在where后面。
第一步：找出公司的平均薪资；
select avg(sal) from emp;
第二步：找出薪水大于平均薪水的员工信息
select ename,sal from emp where sal >(select avg(sal) from emp);±------±--------+
| ename | sal |
±------±--------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
±------±--------+
4、from后面使用子查询【小窍门：将查询结果当作临时表】
案例：找出每一个部门的平均薪水，并且要求显示平均薪水的薪水等级
第一步：找出每个部门的平均薪水
select deptno, avg(sal) as avgsal from emp group by deptno;
±-------±------------+
| deptno | avgsal |
±-------±------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
±-------±------------+
第二步：将上面的查询结果当作临时表t，t表和salgrade s表进行表连接，条件：t.avgsal between s.losal and s.hisal;
select
t.deptno,t.avgsal,s.grade
from
t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
将t表换成第一步的执行结果即可。
select t.avgsal, 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;±------------±------+
| avgsal | grade |
±------------±------+
| 2175.000000 | 4 |
| 1566.666667 | 3 |
| 2916.666667 | 4 |
±------------±------+
5、在select后面使用子查询【了解即可】
select e,ename,(select d,dname from dept d where e.deptno=d.deptno) as dname from emp e;
四、union
