1.分组查询
1.1什么是分组:
针对于班上所有的同学:
分组情况1-按照性别分组:男生一组，女生一组，之后可以统计男生和女生的数量；
分组情况2-按照年龄段分组:80后一组，90后一组；
分组情况3-按照籍贯分组:广东一组，湖南一组，江西一组；
1.2语法:
SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(s)]
[GROUP BY 分组字段1 [,分组字段2 ,…]]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];
注意：
- 使用GROUP BY子句将表分成小组
- 组函数忽略空值,可以使用ifnull
- 结果集隐式按升序排列,如果需要改变排序方式可以使用order by 子句
1.3练习：
-- 1，按照职位分组，求出每个职位的最高和最低工资
SELECT MAX(SAL),JOB FROM emp GROUP BY JOB;
-- 2，查询出每一个部门员工的平均工资
SELECT AVG(SAL),DEPTNO FROM emp GROUP BY DEPTNO;
-- 3，查询平均工资高于2000的部门和其平均工资
SELECT AVG(SAL),DEPTNO FROM emp GROUP BY DEPTNO HAVING AVG(SAL) >=2000;
-- 4，查询各个部门和岗位的平均工资
SELECT AVG(SAL),DEPTNO,JOB FROM emp GROUP BY DEPTNO,JOB;
-- 5，查询各个管理人员下员工的平均工资，其中最低工资不能低于1300，不计算老板
SELECT AVG(SAL),DEPTNO,JOB FROM emp GROUP BY job HAVING AVG(SAL) >1300;
-- 6，查询在80,81,82,83年各进公司多少人
SELECT COUNT(*),YEAR(HIREDATE) FROM emp WHERE YEAR(HIREDATE) IN ('1980','1981','1982','1983') GROUP BY YEAR(HIREDATE);
1.4分组函数使用注意:
1，出现在SELECT列表中的字段，如果出现的位置不是在分组函数中，那么必须出现在GROUP BY子句中
2，在GROUP BY 子句中出现的字段，可以不出现在SELECT列表中
3，如果没有GROUP BY子句,SELECT列表中的任何列或表达式不能使用统计函数(单独使用统计函数除外)：
分组函数单独使用：
SELECT COUNT(empno) FROM emp;
错误的使用，出现了其他字段：
SELECT empno,COUNT(empno) FROM emp;
正确做法:
SELECT empno,COUNT(empno) FROM emp GROUP BY empno,job;
如果现在要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现：
正确做法：
SELECT job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job;
错误的做法：
SELECT deptno,job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job;
正确做法:
SELECT deptno,job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY deptno,job;
在group by 子句中，可以按单列进行分组，也可以在多列上进行分组，多列分组就是按照多个字段的组合进行分组，最终的结果也会按照分组字段进行排序显示。
1.5分组函数执行流程
查询在80,81,82,83年各进公司多少人
SELECT COUNT(empno), YEAR(hiredate) FROM emp WHERE YEAR(hiredate) IN ('1980','1981','1982','1983') GROUP BY YEAR(hiredate)
在整个语句执行的过程中，最先执行的是WHERE子句，在对表数据进行过滤后，符合条件的数据通过Group by进行分组，分组数据通过Having子句进行组函数过滤，最终的结果通过order by子句进行排序，排序的结果被返回给用户。
注意点：WHERE和HAVING的区别
WHERE：是在执行GROUP BY操作之前进行的过滤，表示从全部数据之中筛选出部分的数据，在WHERE之中不能使用统计函数；
HAVING：是在GROUP BY分组之后的再次过滤，可以在HAVING子句中使用统计函数；
2.多表查询
2.1笛卡尔积:
没有连接条件的表关系返回的结果。
多表查询会产生笛卡尔积:
假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
实际运行环境下，应避免使用全笛卡尔集。
select * from emp,dept
解决方案: 在WHERE加入有效的连接条件---->等值连接/不等值连接
注意:连接 n张表,至少需要 n-1个连接条件。
2.2隐式连接
没有join，通过Where的条件进行连接
2.2.1语法
SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名] ,…]
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(S)]
[ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]];
2.2.2隐式等值连接
使用表连接从多个表中查询数据
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
在 WHERE 子句中写入连接条件
当多个表中有重名列时，必须在列的名字前加上表名作为前缀
等值连接是连接操作中最常见的一种，通常是在存在主外键约束条件的多表上建立的，连接条件中的两个字段通过等号建立等值关系。
需求:查询员工编号，员工名称，员工所属部门的编号和名称.
2.2.3使用别名
使用表的别名简化了查询，提高了查询的性能
SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno;
2.2.4对多表做等值连接
为了连接n个表，至少需要n-1个连接条件。例如，为了连接三个表，至少需要两个连接条件
2.2.5非等值连接
查询员工的姓名,工资,所在部门的名称,以及工资的等级.
输出：
SELECT e.ename, e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal
2.3显示连接
隐式连接的问题在于：
1，需要在where条件中写连接条件，如果忘记写，代码不会出错，产生笛卡尔乘积；
2，隐式连接只能做内连接；
2.3.1内连接
SELECT table1.column, table2.column
FROM table1 JOIN table2 ON(table1.column_name = table2.column_name)
自然连接的条件是基于表中所有同名列的等值连接
为了设置任意的连接条件或者指定连接的列，需要使用ON子句
连接条件与其它的查询条件分开书写
使用ON 子句使查询语句更容易理解
先执行join再执行过滤
如果要先过滤再join，那么在join中可以通过join on ..and ..and来先过滤再连接
练习，使用显式内连接查询：
需求:查询员工编号，员工名称，员工所属部门的编号和名称.
SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno
SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e JOIN dept d USING(deptno)
2.3.2外连接
需求:查询出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。
需求:查询每一个部门的总人数.
| deptno | dname | COUNT(empno) |
|---|---|---|
| 10 | ACCOUNTING | 3 |
| 20 | RESEARCH | 5 |
| 30 | SALES | 6 |
| 40 | OPERATIONS | 0 |
外连接查询:
左外连接:查询出JOIN左边表的全部数据查询出来,JOIN右边的表不匹配的数据使用NULL来填充数据.
右外连接:查询出JOIN右边表的全部数据查询出来,JOIN左边的表不匹配的数据使用NULL来填充数据.
SELECT dname,COUNT(empno) FROM emp JOIN dept USING (deptno) GROUP BY deptno;
SELECT deptno,dname,COUNT(empno) FROM emp RIGHT JOIN dept USING (deptno) GROUP BY deptno,deptno;
左外连接
在LEFT OUTER JOIN中，会返回所有左边表中的行，即使在右边的表中没有可对应的列值。即对连接中左边的表中的记录不加限制
SELECT table1.column, table2.column
FROM table1 LEFT [OUTER] JOIN table2 ON (table1.column_name = table2.column_name);
右边外连接
RIGHT OUTER JOIN中会返回所有右边表中的行，即使在左边的表中没有可对应的列值。即对连接中右边的表中的记录不加限制
SELECT table1.column, table2.column
FROM table1 RIGHT [OUTER] JOIN table2 ON (table1.column_name = table2.column_name);
全外连接
FULL OUTER JOIN中会返回所有右边表中的行和所有左边表中的行，即使在左边的表中没有可对应的列值或者右边的表中没有可对应的列
SELECT table1.column, table2.column
FROM table1 FULL [OUTER] JOIN table2 ON (table1.column_name = table2.column_name);
MYSQL中暂时不支持全连接,可以通过union +左右连接来完成；
2.3.3自连接
在查询语句中，一张表可以重复使用多次，完成多次连接的需要；
需求:查询员工名称和其对应经理的名称.
SELECT e.empno,e.ename,m.ename FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;
3.子查询
3.1子查询
子查询指的就是在一个查询之中嵌套了其他的若干查询.
在使用select语句查询数据时,有时候会遇到这样的情况，在where查询条件中的限制条件不是一个确定的值，而是一个来自于另一个查询的结果。
子查询一般出现在FROM和WHERE子句中.
SELECT <select_list>
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
- 1、子查询在主查询前执行一次
- 2、主查询使用子查询的结果
练习
1、查询大于公司平均工资的员工姓名
SELECT ename,sal FROM emp WHERE sal >
(SELECT AVG(sal) FROM emp)
2、查询出工资比MARTIN还要高的全部雇员信息
SELECT * FROM emp WHERE sal >
(SELECT sal FROM emp WHERE ename = 'MARTIN')
使用子查询的注意事项：
- 1、子查询要用括号括起来
- 2、将子查询放在比较运算符的右边(增强可读性)
- 3、对单行子查询使用单行运算符
- 4、对多行子查询使用多行运算符
3.2子查询种类
3.2.1单行单列子查询：
只包含一个字段的查询，返回的查询结果也只包含一行数据
1、返回一行记录
2、使用单行记录比较运算符：=；>；>=；<；<=；<>
练习：
1、查询大于公司平均工资的员工姓名
SELECT ename,sal FROM emp WHERE sal >
(SELECT AVG(sal) FROM emp)
2、查询出工资比MARTIN还要高的全部雇员信息
SELECT * FROM emp WHERE sal >
(SELECT sal FROM emp WHERE ename = 'MARTIN')
3.2.2多行单列子查询：
只包含了一个字段，但返回的查询结果可能多行或者零行(多行子查询返回多行单列)
1、返回多行
2、使用多行比较运算符
IN：与列表中的任意一个值相等 :需求:查询工资等于部门经理的员工信息.
ANY：与子查询返回的任意一个值比较
1): = ANY:此时和IN操作符相同. :需求:查询工资等于任意部门经理的员工信息.
2): > ANY:大于子查询中最小的数据. :需求:查询工资大于任意部门经理的员工信息.
3): < ANY:大于子查询中最大的数据. :需求:查询工资小于任意部门经理的员工信息.
ALL：与子查询返回的每一个值比较
1): > ALL:大于子查询中最大的数据.
2): < ALL:小于子查询中最小的数据.
练习
查询平均工资高于公司平均工资的部门信息
SELECT deptno,AVG(sal) FROM dept JOIN emp USING (deptno) GROUP BY deptno HAVING AVG(sal) >
(
SELECT AVG(sal) FROM emp
);
select * from departments where department_id in
(select department_id from employees
group by department_id having avg(salary)>
(select avg(salary) from employees));
3.2.3多列子查询：
包含多个字段的返回，查询结构可能是单行或者多行。(子查询返回的结果是多行多列)
一般会把子查询返回的结果当成一个临时表，接着在临时表上继续查询或者连接查询；
注意，多行多列的子查询返回的结果必须要设置一个临时表名；
查询出每个部门的编号、名称、部门人数、平均工资:
SELECT d.deptno,d.dname,COUNT(e.empno),IFNULL(AVG(e.sal),0)
FROM dept d JOIN emp e USING (deptno)
GROUP BY d.deptno,d.dname
分析性能:笛卡尔积数量:
可以先把每一个部门的编号,总人数,平均工资先查询出来.
SELECT deptno dno,COUNT(empno) count ,AVG(sal) avg FROM emp GROUP BY dno
再和dept表联合查询部门名称.
SELECT dept.deptno,temp.count,temp.avg FROM dept JOIN (SELECT deptno dno,COUNT(empno) count ,AVG(sal) avg FROM emp GROUP BY deptno) temp ON dept.deptno = temp.dno
3.3UNION/UNION ALL
JOIN是用于把表横向连接，UNION/UNION ALL是用于把表纵向连接(一般用于做查询的临时表)
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
3.3.1注意
1，UNION 内部的 SELECT 语句必须拥有相同数量的列。
2，列也必须拥有兼容的数据类型。
3，每条 SELECT 语句中的列的顺序必须相同。
4，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名
5，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL(性能高).
3.3.2语法：
SELECT column_name(s) FROM table_name1
UNION|UNION ALL
SELECT column_name(s) FROM table_name2
3.3.3在MYSQL 中实现FULL JOIN:
查询员工的编号,名称和部门名称.
1:先在emp表中插入一条数据,并设置depto为NULL.
2:查询
SELECT empno,ename,dname FROM emp LEFT JOIN dept USING (deptno)
UNION
SELECT empno,ename,dname FROM emp RIGHT JOIN dept USING (deptno)
