【bnuz_xmsz】sz04MySQL 2021-10-19

实验报告

《项目实战-sz04MySQL》
责任人
开发时间 2021.10.19
开发地点 木铎
开发环境 数据库
步骤与结果 sz04MySQL

题目

1. 取得每个部门最高薪水的人员名称

SELECT ename, e.deptno, e.sal
from emp e
join (
	SELECT max(sal)maxsal, deptno
	FROM emp
	GROUP BY deptno
) p
on e.deptno = p.deptno
and e.sal = p.maxsal;

2. 哪些人的薪水在部门平均薪水之上
   

select ename, e.sal, s.avgsal, s.deptno
FROM emp e
join (
	SELECT AVG(sal) avgsal, deptno
	FROM emp
	GROUP BY deptno
) s
WHERE e.sal > s.avgsal and e.deptno = s.deptno;

3. 取得部门中所有人的平均的薪水等级
   

select d.deptno, d.avgsal,  grade
from salgrade s
join (
	SELECT AVG(sal) avgsal, deptno
	FROM emp
	GROUP BY deptno
)d
WHERE d.avgsal > s.losal 
AND d.avgsal < s.hisal;

4. 取得平均薪水最高的部门和部门编号
   

SELECT d.deptno, s.avgsal
from dept d
JOIN (
	SELECT AVG(sal) avgsal, deptno
	FROM emp
	GROUP BY deptno
) s
WHERE s.deptno = d.deptno
ORDER BY s.avgsal DESC
LIMIT 1;

5. 取得比普通员工（员工代码没在mgr字段出现的）最高薪水更高的领导人姓名
   第一步：找出所有的经理
   

第二步：找出纯员工中工资最高的员工名字与该员工的工资

第三步：找比该员工的工资高的经理

SELECT e.empno, e.ename, e.sal
FROM emp e
WHERE e.sal > (
	select e.sal emaxsal
	from emp e
	WHERE e.empno not in (
		SELECT mgr
		from emp e
		WHERE e.mgr is not null
		group by mgr
	) 
	ORDER BY e.sal DESC
	LIMIT 1
) ;

6. 取得薪水最高的第六到第十名员工
   

SELECT e.ename, e.sal
from emp e
ORDER BY e.sal desc
LIMIT 6,10;

验证：

7. 列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水
   第一步：查出部门为30的员工的薪水
   

第二步：查出其他员工中的 【无结果】

SELECT e.ename, e.sal
from emp e
WHERE e.sal in (
	select  es.sal
	FROM emp es
	WHERE es.deptno = 30
) 
and e.deptno != 30;

8. 求出部门名称中，带有‘s’字符的部门员工的工资合计，部门人数
   第一步：查出部门中带有s的名字的部门
   

第二步：筛选出员工表中部门编号

第三部：
两表相连

第四步：发现有些为0的没有显示，使用ifnull

select d.deptno,d.dname,IFNULL(SUM(e.sal),0) sumsal, COUNT(e.empno) countt
from emp e
right JOIN dept d
on e.deptno = d.deptno
WHERE d.dname like "%s%"
GROUP BY d.deptno;

9. 列出各种工作的最低工资以及从事此工作的雇员姓名
   第一步：先查出每个工作的最低工资
   

第二步：再将最低工资表和员工表进行联合查询

select  e.job, ename, sal
from emp e 
inner join ( 
	select job ,min(sal) minSal 
	from emp 
	group by job
) tt 
on e.job=tt.job 
and e.sal=tt.minSal;

10. 列出与scott从事相同工作的所有员工及部门名称
    第一步：先查询出scott的工作
    

第二步；将员工表和部门表联合查询

select job 
from emp e
where e.ename = "scott";

select e.ename, d.dname, e.job
from emp e, dept d
where e.job in (
	select job 
	from emp e
	where e.ename = "scott"
)
and e.deptno = d.deptno;

加分任务(30分):

回答下列问题:

(1) 在编写SQL语句时应注意什么以避免索引失效导致全表查询？

1、 避免模糊查询，like这类的效率较低，like ‘%…%’（全模糊）
2、 查询条件中含有is null的select语句执行慢。
3、 查询条件中使用了不等于操作符（<>、!=）的select语句执行慢，不等于操作符会限制索引，可以改成or等。
4、 or语句使用不当会引起全表扫描
5、 组合索引，排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。
6、 Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
7、 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
8、 select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
9、 sql的where条件要绑定变量，比如where column=：1，不要写成where column=‘aaa’，这样会导致每次执行时都会重新分析，浪费CPU和内存资源。

(2) 什么是MySQL事务，事务隔离级别有哪些，举例一个应用场景？

事物：做事有始有终，做事情 要么一次做完，要么不做

1、读取未提交
A端，设置事务为read uncommitted（未提交读），查询salary初始值，之后在a端提交事务前，打开b端，修改salary的值，此时b未提交，但a可以查询到b的更新后的数据。一旦b的事务因为回滚，所有操作都会被撤销，就导致a查询到的是脏数据。
就好比初始化balance是400，a中执行balance = balance – 50 的操作，而b中在此之前就把balance变为了450，这时候在施行a就会不会改变balance的值。并不知道b回滚了，要想解决这个问题可以采用读已提交的隔离级别

2、读已提交
A端，设置为read committed（未提交读） 查询account表，在a的事务提交之前，打开另一个B，更新表account，这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题：然后客户端B的事务提交，A执行与上一步相同的查询，结果与上一次的查询结果不一样，就产生了不可重复读的问题。

3、可重复读
A端，设置为repeatable read，查询表account，在A的事务提交之前，打开B，更新表account并提交，在A查询表account的所有记录，与上一次的查询结果一致，没有出现不可重复读的问题。在A端，接着执行update balance = balance - 50 点操作，balance没有变成400-50=350，因为balance值用的是上一次结果中的350来算的，所以是300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）
1、事务隔离级别为读提交时，写数据只会锁住相应的行。
2、事务隔离级别为可重复读时，如果检索条件有索引的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
3、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

(3) 建立索引应遵循哪些原则？

1、唯一性。
2、为经常需要排序、操作、分组的字段建立索引
3、需要作为查询条件的字段
4、限制索引数目
5、使用前缀来索引
6、区分度要高

(4) 外键的缺点有哪些？

1、外键的频繁使用，会增加开发难度，导致表过多问题。
2、外键会导致，数数据管理起来复杂，操作繁琐，性能降低，一些导入导出操作，在更新 数据的时候比较慢）。
在数据量很大的时候不要去想外键，如果一个程序每天要insert几百万条记录，如果存在外键的约束，每次要去扫描此记录是否合格，一般还不止一个字段有外键，这样扫描的数量是成指数级别数的增长
3、几乎不用，抛开性能不说，开发、测试、部署、实施，以及维护的时候都带来不少问题。数据完整性几乎都是业务的要求，理应由业务部分负责维护，而不是依赖数据库访问量较大的web应用，以及有一定规模的企业应用，都关注伸缩性和性能问题，各种形式的垂直、水平切分运用越来越多，外键、触发器、存储过程之类的基本属禁区

(5)太长且复杂（超过100个单词、可读性差、各种嵌套查询、各种计算函数等）的查询SQL语句你认为有哪些问题，如何避免？

首先是给人乍一看是干净整洁的，不是一大坨在其中，注意格式，比如select放一行，from放一行这样。还有就是命名要规范，表名，字段名，最好可以考虑建中间表过渡一下，也可以先做几个视图啊，实在不行就先做数据处理，

第一步，要对sql语句进行格式化，使sql条理清晰，甚至分步骤添加注释，弄清楚每个步骤是为了得到什么；
第二步，用第一部格式化过的sql与最终需求做对比，没有用的表，就不要进行join了，没有用的字段也不要进行返回了。
第三步，分模块检查子查询，到底是哪个表查询速度慢，或者哪个条件导致的查询速度慢。
第四步，配合sql执行计划，这个由于自己也不擅长看执行计划，只能尽量避免全表扫描，提前限制sql数据筛选范围。
第五步，检查是否用了不合适的in查询，过量的in会导致效率骤然降低很多。
第六步，检查是否存在过多的or查询，or会导致全表扫描，能避免尽量避免。
第七步，检查子查询是否过于复杂，或者sql处理是否过于复杂，如果可以将复杂处理放到程序中进行。
第八步，可以用exists替代的地方，用exists进行替代。
第九步，可以建视图，对sql进行简化，至于视图是否可以提高查询效率，我也不知道，还需要学习研究。

总结与反思

数据库，感觉学完已经过去了很久很久，很多语句和细节都忘了，就记得select*from。。。最长用的还是select和where，对于什么gorp by join on leftjoin，可以说非常不熟悉，还有单表查询和连接查询，当时学的慢慢发现越学越困难了，每个题目都要思考很久，并且每个题目基本上不止一个答案，所以必须找出最优的答案。可以说复杂查询，我可以用where嵌套嵌套嵌套下去，还有需要加强一下语文的学习，有些题目，确实会有些让我怀疑我母语是不是中文。虽说springboot集成封装了sql，但基础还是要打牢固的。

SELECT ename, e.deptno, e.sal
from emp e
join (
	SELECT max(sal)maxsal, deptno
	FROM emp
	GROUP BY deptno
) p
on e.deptno = p.deptno
and e.sal = p.maxsal;

select ename, e.sal, s.avgsal, s.deptno
FROM emp e
join (
	SELECT AVG(sal) avgsal, deptno
	FROM emp
	GROUP BY deptno
) s
WHERE e.sal > s.avgsal and e.deptno = s.deptno;

select d.deptno, d.avgsal,  grade
from salgrade s
join (
	SELECT AVG(sal) avgsal, deptno
	FROM emp
	GROUP BY deptno
)d
WHERE d.avgsal > s.losal 
AND d.avgsal < s.hisal;

SELECT d.deptno, s.avgsal
from dept d
JOIN (
	SELECT AVG(sal) avgsal, deptno
	FROM emp
	GROUP BY deptno
) s
WHERE s.deptno = d.deptno
ORDER BY s.avgsal DESC
LIMIT 1;

SELECT e.empno, e.ename, e.sal
FROM emp e
WHERE e.sal > (
	select e.sal emaxsal
	from emp e
	WHERE e.empno not in (
		SELECT mgr
		from emp e
		WHERE e.mgr is not null
		group by mgr
	) 
	ORDER BY e.sal DESC
	LIMIT 1
) ;

SELECT e.ename, e.sal
from emp e
ORDER BY e.sal desc
LIMIT 6,10;

SELECT e.ename, e.sal
from emp e
WHERE e.sal in (
	select  es.sal
	FROM emp es
	WHERE es.deptno = 30
) 
and e.deptno != 30;

select d.deptno, d.dname, IFNULL(SUM(e.sal),0) sumsal, COUNT(e.empno) countt
from emp e
right JOIN dept d
on e.deptno = d.deptno
WHERE d.dname like "%s%"
GROUP BY d.deptno;

select  e.job, ename, sal
from emp e 
inner join ( 
	select job ,min(sal) minSal 
	from emp 
	group by job
) tt 
on e.job=tt.job 
and e.sal=tt.minSal;

select job 
from emp e
where e.ename = "scott";

select e.ename, d.dname, e.job
from emp e, dept d
where e.job in (
	select job 
	from emp e
	where e.ename = "scott"
)
and e.deptno = d.deptno;