2019Java面试宝典数据库篇 -- MySQL

一、SQL 的 select 语句完整的执行顺序：

1、from 子句组装来自不同数据源的数据;

2、where 子句基于指定的条件对记录行进行筛选;

3、group by 子句将数据划分为多个分组;

4、使用聚集函数进行计算;

5、使用 having 子句筛选分组;

6、计算所有的表达式;

7、select 的字段;

8、使用 order by 对结果集进行排序。

SQL 语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中,代码按编码顺序被处理。但在 SQL 语句中,第一个被处理的子句是 FROM,而不是第一出现的 SELECT。SQL 查询处理的步骤序号:

(1) FROM

(2) JOIN

(3) ON

(4) WHERE

(5) GROUP BY

(6) WITH {CUBE | ROLLUP}

(7) HAVING

(8) SELECT

(9) DISTINCT

(9) ORDER BY

(10)

以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在查询中指定某一个子句，将跳过相应的步骤。

逻辑查询处理阶段简介：

1、 FROM:对 FROM 子句中的前两个表执行笛卡尔积(交叉联接),生成虚拟表 VT1。

2、 ON:对 VT1 应用 ON 筛选器,只有那些使为真才被插入到 TV2。

3、 OUTER (JOIN):如果指定了 OUTER JOIN(相对于 CROSS JOIN 或 INNER JOIN),保留表中未找到匹配的行将作为外部行添加到 VT2,生成 TV3。如果 FROM 子句包含两个以上的表,则对上一个联接生成的结果表和下一个表重复执行步骤 1 到步骤 3,直到处理完所有的表位置。

4、 WHERE:对 TV3 应用 WHERE 筛选器,只有使为 true 的行才插入 TV4。

5、 GROUP BY:按 GROUP BY 子句中的列表对 TV4 中的行进行分组,生成 TV5。

6、 CUTE|ROLLUP:把超组插入 VT5,生成 VT6。

7、 HAVING:对 VT6 应用 HAVING 筛选器,只有使为 true 的组插入到 VT7。

8、 SELECT:处理 SELECT 列表,产生 VT8。

9、 DISTINCT:将重复的行从 VT8 中删除,产品 VT9。

10、 ORDER BY:将 VT9 中的行按 ORDER BY 子句中的列列表顺序,生成一个游标(VC10)。

11、 TOP:从 VC10 的开始处选择指定数量或比例的行,生成表 TV11,并返回给调用者。

下面是一个关于MySQL中SQL语句执行顺序的例子：

SELECT 考生姓名, MAX(总成绩) AS max总成绩
FROM tb_Grade
WHERE 考生姓名 IS NOT NULL
GROUP BY 考生姓名
HAVING MAX(总成绩) > 600
ORDER BY max总成绩

在上面的示例中 SQL 语句的执行顺序如下:：

   (1). 首先执行 FROM 子句, 从 tb_Grade 表组装数据源的数据 。

   (2). 执行 WHERE 子句, 筛选 tb_Grade 表中所有考生姓名数据不为 NULL 的数据 。

   (3). 执行 GROUP BY 子句, 把 tb_Grade 表按 "学生姓名" 列进行分组(注：这一步开始才可以使用select中的别名，他返回的是一个游标，而不是一个表，所以在where中不可以使用select中的别名，而having却可以使用)。

   (4). 计算 max() 聚集函数， 按 "总成绩" 求出总成绩中最大的一些数值。

   (5). 执行 HAVING 子句, 筛选课程的总成绩大于 600 分的。

   (7). 执行 ORDER BY 子句, 把最后的结果按 "Max 成绩" 进行排序。

二、SQL 之聚合函数

聚合函数是对一组值进行计算并返回单一的值的函数，它经常与 select 语句中的 group by 子句一同使用。

avg()：返回的是指定组中的平均值，空值被忽略。

count()：返回的是指定组中的项目个数。

max()：返回指定数据中的最大值。

min()：返回指定数据中的最小值。

sum()：返回指定数据的和,只能用于数字列,空值忽略。

group by()：对数据进行分组,对执行完 group by 之后的组进行聚合函数的运算,计算每一组的值。最后用 having 去掉不符合条件的组, having 子句中的每一个元素必须出现在 select 列表中(只针对于 mysql)。

三、SQL 之连接查询(左连接和右连接的区别)

外连接：

左连接(左外连接):以左表作为基准进行查询，左表数据会全部显示出来,右表如果和左表匹配的数据则显示相应字段的数据,如果不匹配则显示为 null。

右连接(右外连接):以右表作为基准进行查询，右表数据会全部显示出来,左表如果和右表匹配的数据则显示相应字段的数据,如果不匹配则显示为 null。

全连接:先以左表进行左外连接,再以右表进行右外连接。

内连接：显示表之间有连接匹配的所有行。

四、SQL 之 sql 注入

通过在 Web 表单中输入(恶意)SQL 语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行 SQL 语句。举例:当执行的 sql 为：

 select * from user where username = “admin”or “a”=“a”

此时，sql 语句恒成立，参数 admin 毫无意义。

防止 sql 注入的方式：

1. 预编译语句：如,select * from user where username = ?,sql 语句语义不会发生改变,sql 语句中变量用?表示，即使传递参数时为“admin or ‘a’= ‘a’”,也会把这整体当做一个字符创去查询。
2. Mybatis 框架中的 mapper 方式中的 # 也能很大程度的防止 sql 注入($无法防止 sql 注入)。

五、Mysql 性能优化

1、当只要一行数据时使用 limit 1

查询时如果已知会得到一条数据，这种情况下加上 limit 1 会增加性能。因为 mysql 数据库引擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。

2、选择正确的数据库引擎

Mysql 中有两个引擎 MyISAM 和 InnoDB，每个引擎有利有弊。

MyISAM 适用于一些大量查询的应用，但对于有大量写功能的应用不是很好。甚至你只需要update 一个字段整个表都会被锁起来。而别的进程就算是读操作也不行要等到当前 update 操作完成之后才能继续进行。另外，MyISAM 对于 select count(*)这类操作是超级快的。

InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用会比 MyISAM 还慢,但是支持“行锁”，所以在写操作比较多的时候会比较优秀。并且,它支持很多的高级应用，例如：事务。

3.用 not exists 代替 not in

Not exists 用到了连接能够发挥已经建立好的索引的作用，not in 不能使用索引。Not in 是最慢的方式要同每条记录比较,在数据量比较大的操作红不建议使用这种方式。

4.对操作符的优化,尽量不采用不利于索引的操作符

如：in、not in、is null、is not null、<>等。

某个字段总要拿来搜索,为其建立索引：

Mysql 中可以利用 alter table 语句来为表中的字段添加索引,语法为：

alter table 表名
add index (字段名);

六、常见SQL 面试题

面试题的表架构：

Student(S#,Sname,Sage,Ssex) 学生表 
Course(C#,Cname,T#) 课程表 
SC(S#,C#,score) 成绩表 
Teacher(T#,Tname) 教师表 

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

selecta.S# from(selects#,score fromSC whereC#='001') a,(selects#,score

fromSC whereC#='002') b

wherea.score>b.score anda.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩；

selectS#,avg(score)

fromsc

groupbyS# havingavg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩；

selectStudent.S#,Student.Sname,count(SC.C#),sum(score)

fromStudent leftOuterjoinSC onStudent.S#=SC.S#

groupbyStudent.S#,Sname

4、查询姓“李”的老师的个数；

selectcount(distinct(Tname))

fromTeacher

whereTname like'李%';

5、查询没学过“叶平”老师课的同学的学号、姓名；

selectStudent.S#,Student.Sname

fromStudent

whereS# notin(selectdistinct( SC.S#) fromSC,Course,Teacher whereSC.C#=Course.C# andTeacher.T#=Course.T# andTeacher.Tname='叶平');