第四天

一、范式的设计（重点）

二、分组函数

（1）having 是对分组的字段进行筛选！
只能查组级别的数据！

服务工还是我（F、W、G、H、S、O）
where子句不能直接接聚合函数
只能限制基本数据
（2）使用distinct避免count聚合函数的重复问题！

三、第七章练习题

练习1
1.查询部门20的员工，每个月的工资总和及平均工资。
SELECT SUM(sal),AVG( sal)
FROM emp
WHERE deptno=”20”;

2.查询工作在CHICAGO的员工人数，最高工资及最低工资。
SELECT COUNT(e.deptno) “员工人数”,MAX(sal) “最高工资”,MIN(sal) “最低工资”
FROM emp e,dept d
WHERE d.loc=”CHICAGO” AND e.deptno=d.deptno
GROUP BY e.deptno;

3.查询员工表中一共有几种岗位类型。
SELECT COUNT(DISTINCT JOB)
FROM emp;

练习2
1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
SELECT e.deptno “部门编号”,d.dname “部门名称”,COUNT(e.deptno) “部门人数”,MAX(e.sal) “最高工资”,MIN(e.sal) “最低工资”,
SUM(e.sal) “工资总和”,AVG(e.sal) “平均薪资”
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno
2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
SELECT e.deptno “部门编号”,d.dname “部门名称”,e.job “岗位名称”,COUNT(e.deptno) “部门人数”,MAX(e.sal),MIN(e.sal),SUM(e.sal),
AVG(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno,e.job;

练习3
1.查询部门人数大于2的部门编号，部门名称，部门人数。
SELECT d.deptno “部门编号”,d.dname “部门名称”,COUNT(e.deptno) “部门人数”
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno
HAVING COUNT(e.deptno)>2;

2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序
SELECT e.deptno “部门编号”,d.dname “部门名称”,COUNT(e.deptno) “部门人数”,AVG( sal) “部门平均工资”
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno
HAVING AVG(e.sal)>2000 AND COUNT(e.deptno)>2
ORDER BY e.deptno ASC;
3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。
SELECT e.ename “员工姓名”,e2.ename “经理姓名”,e2.empno “经理编号”,COUNT(e2.empno) “管理的人数”
FROM emp e LEFT JOIN emp e2
ON e.mgr=e2.empno
GROUP BY e2.empno;

练习4
1.查询入职日期最早的员工姓名，入职日期
SELECT ename “员工姓名”,hiredate “入职日期”
FROM emp
WHERE hiredate=(SELECT MIN(hiredate) FROM emp );
2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
SELECT e.ename “员工姓名”,e.sal “工资”,d.dname “部门名称”
FROM emp e,dept d
WHERE e.deptno=d.deptno AND e.sal>(SELECT sal FROM emp WHERE ename=”SMITH”) AND
d.loc=”CHICAGO”;
3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
SELECT ename “员工姓名”,hiredate “入职日期”
FROM emp e
WHERE hiredate<(SELECT MIN(hiredate) FROM emp WHERE deptno=20) ;
4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数
SELECT e2.deptno,d.dname,COUNT(*)
FROM emp e2,dept d
WHERE e2.deptno=d.deptno
GROUP BY d.deptno
HAVING COUNT(d.deptno)>
(SELECT AVG(IFNULL(dd,0))
FROM
(SELECT d.dname “部门名称”,COUNT(e.deptno) dd
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno) ee )

练习5
1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
SELECT ename,hiredate
FROM emp
WHERE hiredate>(SELECT MIN(hiredate) FROM emp WHERE deptno=10)
AND deptno!=10;
2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
SELECT ename,hiredate
FROM emp
WHERE hiredate>(SELECT MAX(hiredate) FROM emp WHERE deptno=10);
3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
SELECT ename,job
FROM emp
WHERE job in (SELECT job FROM emp WHERE deptno=10) AND deptno!=10;

课后作业：
周末完成
1.查询部门平均工资在2500元以上的部门名称及平均工资。
2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。
6.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
7.查询工资最高的员工姓名和工资。
8.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资
SELECT ee.deptno “部门编号”,dd.dname “部门名称”,MIN(ee.ls) “最低工资”
FROM dept dd,(SELECT MIN(e.sal) ls,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY e.deptno) ee
WHERE ee.deptno=dd.deptno AND ee.ls>(SELECT MIN(sal) FROM emp WHERE deptno=10);
9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
10.显示经理是KING的员工姓名，工资。
11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
12.使用子查询的方式查询哪些职员在NEW YORK工作。
13.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
14.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
15. 显示部门名称和人数16. 显示每个部门的最高工资的员工
17. 显示出和员工号7369部门相同的员工姓名，工资
18. 显示出和姓名中包含“W”的员工相同部门的员工姓名、

四、mysql索引底层原理

索引简介：
索引简介索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

（1）mysql底层的数据结构有两种，一种是hash表，一种是B+树
（2）B树和B+树的区别,B+树第三层是有双向链表，而B树为多层，查询速度慢，B+树查询速度快。

1）B树的每个结点都存储了key和data，B+树的data存储在叶子节点上。
节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。
2）树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录
由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

（3）使用hash表可能会产生hash冲突，比如20%3 和0%3 都为2，如果hash表已经存在一个值，就需要再次hash,这就是hash冲突。