【JAVA面试题整理】数据库之Mysql

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

1. form子句组装来自不同数据源的数据

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

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

4. 使用聚集函数进行计算

5. 使用having子句筛选分组

6. 计算所有的表达式

7. select的字段

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

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

(1) FROM <left_table>

(2) <join_type> JOIN <right_table>

(3) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list>

(6) WITH {CUBE | ROLLUP}

(7) HAVING <having_condition>

(8) SELECT

(9) DISTINCT

(9) ORDER BY <order_by_list>

(10) <TOP_specification> <select_list>

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

逻辑查询处理阶段简介：

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

8、 SELECT：处理SELECT列表，产生VT8。

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

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

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

where子句中的条件书写顺序

二、SQL之聚合函数

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

1. avg（）：返回的是制定组中的平均值，空值被忽略

2. count（）：返回的是指定组中的项目个数

3. max（）：返回指定数据中的最大值

4. min（）：返回指定数据中的最小值

5. sum（）：返回指定数据的和，只能用于数字列，空值被忽略

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

三、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数据库引擎会在找到一条结果停止搜索，而不是继续查询吓一跳是否符合标准指导所有记录查询完毕。

1. 选择正确的数据库引擎

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

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

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

1. 用not exists代替not in

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

1. 对操作符的优化，为其建立索引：

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

六、Mysql数据库架构图

 

MyISAM和InnoDB是最常见的两种存储引擎，特点如下。

MyISAM存储引擎

MyISAM是MySQL官方提供的默认存储引擎，其特点是不支持事务、表锁和全文索引，对于一些OLAP（联机分析处理）系统，操作速度快。

每个MyISAM在磁盘上存储分成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）.MYD（MYData存储数据）.MYI

（MYIndex，存储索引）。这里特别要注意的是MyISAM不缓存数据文件，之缓存索引文件。

InnoDB存储引擎

InnoDB存储引擎支持事务，主要面向OLTP（联机事务处理过程方面的应用，其特点是行锁设置、支持外键，并支持类似于Oracle的非锁定读，即默认情况下读不产生锁。InnoDB将数据放在一个逻辑表空间中，InnoDB通过多版本并发控制来获得高并发性，实现了ANSI标准的4种隔离级别，默认为Repeatable，使用一种被称为next-key locking的策略避免幻读。

对于表中数据的存储，InnoDB采用类似Oracle索引组织表Clustered的方式进行存储。

InnoDB存储引擎提供了具有提交、回滚和崩溃回复能力的事务安全。但是对比MyISAM的存储引擎，InNoDB系的效率差一些并且会占用更多的磁盘空间以保留数据和索引。

七、Mysql机构中各个模块都是什么

1、连接管理与安全验证是什么

每个客户端都会家里一个与服务器连接的线程，服务器会有一个线程池来管理这些连接，如果客户端需要连接到MYSQL数据库还需要进行验证，包括用户名，密码，主机信息等。

2、解析器是什么？

解析器的作用主要是分析查询语句，最终生成解析树；首先解析器会对查询语句的语法进行解析，分析语法是否有问题，还有解析器会查询缓存，如果在缓存中有对应的语句，就返回查询结果不进行接下来的优化执行操作。前提是缓存中的数据没有被修改，当然如果被修改了也会被清理出缓存。

3、优化器怎么用？

优化器的作用主要是对查询语句进行优化操作，包括选择合适的索引，数据的读取方式，保罗获取查询的开销信息，统计信息等，这也是为什么途中会有优化器指向存储引擎的箭头。之前在别的文章中没有看到优化器跟存储引擎之间的关系，在这里我个人的理解是因为优化器需要通过存储引擎获取查询的大致数据和统计信息。

4、执行器是什么？

执行器包括执行查询语句，返回查询结果，生成执行计划包括与存储引擎的一些处理操作。

八、Mysql存储引擎有哪些？

1、InnoDB存储引擎

InnoDB是事务性数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的Mysql引擎。

2、MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

3、MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

4、NDB存储引擎

NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC，但它是Share Nothing的架构，因此能提供更高级别的可用性和可扩展性。NDB的特点是数据全部放在内存中，因此通过主键查找非常快。

5、Memory（Heap）存储引擎

Memory存储引擎（之前成为Heap）将表中数据存放到内存中，如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。

6、Archive存储引擎

正如其名称所示，Achive非常适合存储归档数据，如日志信息。它只支持insert和select操作，其设计的主要目的是提供高速的插入和压缩功能。

7、Federated存储引擎

Federated存储引擎不存放数据，它至少指向一台远程Mysql数据库服务器上的表，非常类似于Oracle的透明网关。

8、Maria存储引擎

Maria存储引擎是新开发的引擎，其设计默表示用来取代原有MyISAM存储引擎，从而成为Mysql默认的存储引擎。

 

上述引擎中，InnoDB是事务安全的存储引擎，设计上借鉴了很多Oracle的架构思想，一般而言，在OLTP应用中，InnoDB应该作为核心应用表的首先存储引擎。

九、Mysql事务介绍

Mysql和其他的数据库产品有一个很大的不同就是事务有存储引擎所决定，事务就是为了解决一组查询要么全部执行成功，要么全部执行失败。

1、事务的四大特征是什么？

数据库事务transanction正确执行的四个基本要素。ACID：原子性（Atomicity）、一致性（Correspondence）、隔离性（Isolation）、持久性（Durability）。

1. 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有被执行过一样

2. 一致性：在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏

3. 隔离性：隔离状态执行事务，使它们好像是在系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事物在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

4. 持久化：在事务完成之后，该事务对数据库所做的更改便持久的保存在数据库中，并不会被回滚。

2、Mysql中四种隔离级别分别是什么？

读未提交（READ UNCOMMITTED）：未提交读隔离级别也叫读脏，就是事务可以读取其它事务未提交的数据。

读已提交（READ COMMITTED）：在其它数据库系统比如SQL Server默认的隔离级别就是提交读，已提交读隔离级别就是在事务未提交之前所做的修改其它事务是不可见的。

可重复读（REPEATABLE READ）：保证同一个事务中的多次相同的查询的结果是一致的，比如一个事务一开始查询了一条记录然后过了几秒钟又执行了相同的查询，保证两次查询的结果是相同的，可重复读也是mysql的默认隔离级别。

可串行化（SERIALIZABLE）：可串行化就是保证读取的范围内没有新的数据插入，比如事务第一次查询得到某个范围的数据，第二次查询也同样得到了相同范围的数据，中间没有新的数据插入到该范围中。

十、Mysql怎么创建存储过程

Mysql存储过程是从mysql5.0开始增加的新功能。存储过程的优点，最主要的是执行效率和SQL代码封装，特别是SQL代码封装功能，如果没有存储过程，在外部程序访问数据库时，要组织很多SQL语句。特别是业务逻辑复杂的时候，一大堆的sql和条件夹杂在代码中，让人不寒而栗，现在有了mysql存储过程，业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高。

1、创建mysql存储过程

下面代码创建了一个叫pr_add的mysql存储过程，这个mysql存储过程有两个int类型的输入参数‘a’,'b'，返回两个参数的和

1. drop procedure if exists pr_add

2. 计算两个数之和

create procedure pr_add(aint,bint)begin declarec int;
if a is null then set a=0;
end if;
if b is null then set b=0;
end if;
set c=a+b;
select c as sum;

2、调用mysql存储过程

call pr_add（10,20）;

十一、Mysql触发器怎么写

Mysql包含对触发器的支持，触发器是一种与表操作有关的数据库对象，当初发起所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器执行。

Mysql中，创建触发器语法如下：

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中

trigger_name：标识触发器名称，用户自行指定

trigger_time：标识触发时机，取值BEFORE或AFTER

trigger_event：标识触发时间，取值为INSERT、UPDATE或DELETE

tbl_name：标识建立触发器的标明，即在哪张表上建立触发器

trigger_stmt：触发器程序体，可以使依据SQL语句，或者用BEGIN和END包含的多条语句。

由此可见，可以建立6中触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

查看触发器：

和查看数据库（show databases）查看表格（show tables）一样，查看触发器的语句如下：

show triggers [from schema_name];

其中，schema_name即schema的名称，在Mysql中schema和Database是一样的，也就是说，可以指定数据库名，这样就不必先use databse_name了。

删除触发器：

和删除数据库、删除表格一样，删除触发器的语法如下：

drop trigger[if exists] [schema_name]trigger_name；

十二、Mysql语句优化

1、where子句中可以对字段进行null值判断吗？

可以，比如select id from t where num is null这样的sql也是可以的。但是最好不要给数据库留null，尽可能使用not null填充数据库。不要以为null不需要空间，比如char(100)型，在字段建立时，空间就固定了，不管是否插入值，都是占用100个字符的空间，如果varchar这样的变长字段，null不占用空间。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from where num =0.

2、select * from admin left join log on admin.admin_id = log.admin_id where log.admin_id>10如何优化？ 

优化为：select * from (select * from admin where admin_id>10) T1 lef join log on T1.admin_id = log.admin_id。

使用join 时候，应该用小的结果驱动大的结果（left join左表表结果尽量小，如果有条件应该尽量放到左边先处理，right join 同理相反），同时尽量把牵涉到多表联合的查询拆分成多个query（多表查询效率低，容易到之后锁表和阻塞）。

3、limit 的技术比较大时使用between

例如：select * from admin order by admin_id limit 100000,10

优化为：select * from admin where admin_id between 100000 and 100010 order by admin_id

4、尽量避免在列上做运算，这样导致索引失效

例如：select * from admin where year(admin_time)>2014

优化为：select * from admin where admin_time> '2014-01-01′

十三、Mysql中文乱码问题完美解决方案

解决乱码的核心思想是统一编码。我们在使用mysql建数据库和建表时应尽量使用统一的编码，强烈推荐的是utf8编码，因为该编码几乎可以兼容世界上所有的字符。

数据库在安装的时候可以设置默认编码，在安装时就一定要设置为utf8编码。设置之后再创建的数据库和表如果不指定编码，默认都会使用utf8编码，省去了很多麻烦。

数据库软件安装好之后可以通过如下命令查看默认编码：

1、查询数据库软件使用的默认编码格式

show variables like “%colla%”；
show varables like “%char%”

其中collation，代表了字符串排序（比较）的规则，如果值是utf8_general_ci，代表使用utf8字符集大小写不敏感的自然方式比较。

如果character_set的值不为utf8，那么可以使用如下命令修改为utf8.

2、修改数据库默认编码为utf8

SET character_set_client='utf8';
SET character_set_connection='utf8';
SET character_set_results='utf8';

如果不想设置数据库软件的全局默认编码，也可以单独修改或者设置某个具体数据库的编码也可以单独修改或设置某个数据库中某个表的编码。

3、创建数据库的时候指定使用utf8编码

CREATE DATABASE `test`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';

4、创建表的时候指定使用utf8编码

CREATE TABLE `database_user` (
`ID` varchar(40) NOT NULL default '',
`UserID` varchar(40) NOT NULL default '',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果数据库已经创建好了，可以使用show database 数据库名；和show create table 表名；查看一下数据库和表的字符集是否为utf8，如果不是则在命令行下面可以用如下命令，将数据库和表编码修改为utf8.

5、修改具体某数据库或表的编码

ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `tb_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

十三、如何提高MySQL的安全性

1.如果MySQL客户端和服务器端的连接需要跨越并通过不可信任的网络，那么需要使用ssh隧道来加密该连接的通信。

2.使用set password语句来修改用户的密码，先“mysql -u root”登陆数据库系统，然后“mysql> update mysql.user set password=password(’newpwd’)”，最后执行“flush privileges”。

3.MySQL需要提防的攻击有，防偷听、篡改、回放、拒绝服务等，不涉及可用性和容错方面。对所有的连接、查询、其他操作使用基于ACL（ACL（访问控制列表）是一种路由器配置和控制网络访问的一种有力的工具，它可控制路由器应该允许或拒绝数据包通过，可监控流量，可自上向下检查网络的安全性，可检查和过滤数据和限制不必要的路由更新，因此让网络资源节约成本的ACL配置技术在生活中越来越广泛应用。）即访问控制列表的安全措施来完成。

4.设置除了root用户外的其他任何用户不允许访问mysql主数据库中的user表；

5.使用grant和revoke语句来进行用户访问控制的工作;

6.不要使用明文密码，而是使用md5()和sha1()等单向的哈系函数来设置密码;

7.不要选用字典中的字来做密码;

8.采用防火墙可以去掉50%的外部危险，让数据库系统躲在防火墙后面工作，或放置在DMZ（DMZ是英文“demilitarized zone”的缩写，隔离区，它是为了解决安装防火墙后外部网络的访问用户不能访问内部网络服务器的问题，而设立的一个非安全系统与安全系统之间的缓冲区。）区域中;

9.从因特网上用nmap来扫描3306端口，也可用telnet server_host 3306的方法测试，不允许从非信任网络中访问数据库服务器的3306号tcp端口，需要在防火墙或路由器上做设定;

10.服务端要对SQL进行预编译，避免SQL注入攻击，例如where id=234，别人却输入where id=234 or 1=1。

11.在传递数据给mysql时检查一下大小;

12.应用程序连接到数据库时应该使用一般的用户帐号，开放少数必要的权限给该用户;

13.学会使用tcpdump和strings工具来查看传输数据的安全性，例如tcpdump -l -i eth0-w -src or dst port 3306 strings。以普通用户来启动mysql数据库服务;

14.确信在mysql目录中只有启动数据库服务的用户才可以对文件有读和写的权限;

15.不许将process或super权限付给非管理用户，该mysqladmin processlist可以列举出当前执行的查询文本;super权限可用于切断客户端连接、改变服务器运行参数状态、控制拷贝复制数据库的服务器;

16.如果不相信dns服务公司的服务，可以在主机名称允许表中只设置ip数字地址;

17.使用max_user_connections变量来使mysqld服务进程，对一个指定帐户限定连接数;

18.grant语句也支持资源控制选项;

19.启动mysqld服务进程的安全选项开关，–local-infile=0或1，若是0则客户端程序就无法使用local load data了，赋权的一个例子grant insert(user) on mysql.user to ‘user_name’@'host_name’;若使用–skip-grant-tables系统将对任何用户的访问不做任何访问控制，但可以用mysqladmin flush-privileges或mysqladmin reload来开启访问控制;默认情况是show databases语句对所有用户开放，可以用–skip-show-databases来关闭掉。

20.碰到error 1045(28000) access denied for user ‘root’@'localhost’(using password:no)错误时，你需要重新设置密码，具体方法是:先用–skip-grant-tables参数启动mysqld，然后执行mysql -u root mysql,mysql>update user set password=password(’newpassword’) where user=’root’;mysql>flush privileges;，最后重新启动mysql就可以了。