Java mysql面试题

1、SQL中聚合函数有哪些？

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

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

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

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

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

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

2、SQL之连接查询（左连接和右连接的区别）？

● 外连接：

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

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

● 全连接：先以左表进行左外连接，再以右表进行右外连接。

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

3、SQL之sql注入是什么？

通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。举例：当执行的sql为select * from user where username = “admin” or “a” = “a”时，sql语句恒成立，参数username毫无意义。

● 防止sql注入的方式：

预编译语句：如，select * from user where username = ？，sql语句语义不会发生改变，sql语句中变量用?表示，即使传递参数时为“admin or ‘a’ = ‘a’”，也会把这整体当做一个字符创去查询。

Mybatis框架中的mapper方式中的#也能很大程度的防止sql注入（$无法防止sql注入）。

4、MySQL性能优化有哪些？

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

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

● 选择正确的数据库引擎

MySQL中有两个引擎MyISAM和InnoDB，每个引擎有利有弊。MyISAM适用于一些大量查询的应用，但对于有大量写功能的应用不是很好。甚至你只需要update一个字段整个表都会被锁起来。而别的进程就算是读操作也不行要等到当前update操作完成之后才能继续进行。另外，MyISAM对于select count(*)这类操作是超级快的。InnoDB的趋势会是一个非常复杂的存储引擎，对于一些小的应用会比MyISAM还慢，但是支持“行锁”，所以在写操作比较多的时候会比较优秀。并且，它支持很多的高级应用，例如：事务。

● 用not exists代替not in

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

● 对操作符的优化，尽量不采用不利于索引的操作符

如：in、not in、is null、is not null 、<> 等某个字段总要拿来搜索，为其建立索引：MySQL中可以利用alter table语句来为表中的字段添加索引，语法为：alter table表名add index(字段名)

5、MySQL 数据库架构图了解吗？

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

● MyISAM存储引擎

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

每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。这里特别要注意的是MyISAM不缓存数据文件，只缓存索引文件。

● InnoDB存储引擎

InnoDB存储引擎支持事务，主要面向OLTP（联机事务处理过程）方面的应用，其特点是行锁设置、支持外键，并支持类似于Oracle的非锁定读，即默认情况下读不产生锁。InnoDB将数据放在一个逻辑表空间中（类似Oracle）。

InnoDB通过多版本并发控制来获得高并发性，实现了ANSI标准的4种隔离级别，默认为Repeatable，使用一种被称为next-keylocking的策略避免幻读。

对于表中数据的存储，InnoDB采用类似Oracle索引组织表Clustered的方式进行存储。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。以下是InnoDB体系架构：

 

 

6、MySQL架构器中各个模块都是什么？

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

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

● 解析器是什么

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

● 优化器怎么用

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

● 执行器是什么

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

7、MySQL存储引擎有哪些？

● InnoDB存储引擎

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

● MyISAM存储引擎

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

● MEMORY存储引擎

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

● NDB存储引擎

NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC，但它是ShareNothing的架构，因此能提供更高级别的高可用性和可扩展性。NDB的特点是数据全部放在内存中，因此通过主键查找非常快。关于NDB，有一个问题需要注意，它的连接(join)操作是在MySQL数据库层完成，不是在存储引擎层完成，这意味着，复杂的join操作需要巨大的网络开销，查询速度会很慢。

● Memory(Heap)存储引擎

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

● Archive存储引擎

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

● Federated存储引擎

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

● Maria存储引擎

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

上述引擎中，InnoDB是事务安全的存储引擎，设计上借鉴了很多Oracle的架构思想，一般而言，在OLTP应用中，InnoDB应该作为核心应用表的首先存储引擎。InnoDB是由第三方的InnobaseOy公司开发，现已被Oracle收购，创始人是HeikkiTuuri，芬兰赫尔辛基人，和著名的Linux创始人Linus是校友。

8、MySQL事务介绍？

MySQL和其它的数据库产品有一个很大的不同就是事务由存储引擎所决定，例如MYISAM，MEMORY，ARCHIVE都不支持事务，事务就是为了解决一组查询要么全部执行成功，要么全部执行失败。MySQL事务默认是采取自动提交的模式，除非显示开始一个事务。

SHOW VARIABLES LIKE 'AUTOCOMMIT';

修改自动提交模式，0=OFF，1=ON，注意：修改自动提交对非事务类型的表是无效的，因为它们本身就没有提交和回滚的概念，还有一些命令是会强制自动提交的，比如DLL命令、locktables等。

SET AUTOCOMMIT = 0;

或

SET AUTOCOMMIT = OFF;

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

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

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

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

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

● 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

10、MySQL中四种隔离级别分别是什么？

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

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

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

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

11、MySQL怎么创建存储过程?

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

● 创建MySQL存储过程

下面代码创建了一个叫pr_add的MySQL存储过程，这个MySQL存储过程有两个int类型的输入参数“a”、“b”，返回这两个参数的和。

● drop procedure if exists pr_add;（备注：如果存在pr_add的存储过程，则先删掉）

● 计算两个数之和（备注：实现计算两个整数之和的功能）

create procedure pr_add (a int,b int)
begin
declare c 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;

● 调用 MySQL 存储过程

call pr_add(10， 20);

12、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种触发器，即：BEFOREINSERT、BEFOREUPDATE、BEFOREDELETE、AFTERINSERT、AFTERUPDATE、AFTERDELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。假设系统中有两个表：

● 班级表class(班级号classID，班内学生数stuCount)

● 学生表student(学号stuID，所属班级号classID)

要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：

create trigger tri_stuInsert after insert on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID); 
update class set stuCount = c + 1 where classID = new.classID;

查看触发器：和查看数据库（showdatabases;）查看表格（showtables;）一样，查看触发器的语法如下：

SHOW TRIGGERS [FROM schema_name];

其中，schema_name即Schema的名称，在MySQL中Schema和Database是一样的，也就是说，可以指定数据库名，这样就不必先“USE database_name;”了。

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

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

其中，schema_name即Schema的名称，在MySQL中Schema和Database是一样的，也就是说，可以指定数据库名，这样就不必先“USEdatabase_name;”了。