mysql的一些面试问题

1.SQL的select语句完整的执行顺序

（1）from子句组装来自不同数据源的数据。

（2）where基于指定的条件对记录进行筛选

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

（4）使用聚合函数进行计算

（5）使用having子句筛选分组

（6）计算所有的表达式

（7）select的字段

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

2.SQL连接查询

外连接：

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

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

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

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

3.SQL注入

防止注入的方式：

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

4.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）对操作符的优化，尽量不采用不利于索引的操作符

5.Mysql存储引擎

（1）InnoDB存储引擎

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

（2）MyISAM存储引擎

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

其他的几种数据引擎不多加介绍。

6.Mysql事务的四大特性

数据库事务正确执行的四个最基本要素。原子性，一致性，隔离性，持久性。

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

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

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

（4）持久性

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

7.Mysql中四种隔离级别

读未提交（read uncommitted）未提交读隔离级别也叫脏读，就是事务可以读取其他事务未提交的数据。

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

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

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

8.Mysql怎么创建存储过程

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

（1）创建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;
/*declare 表示声明的意思。就是声明变量的。*/
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)；

9.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个触发器。

下面是一个例子：

1)班级表 class(班级号 classID, 班内学生数 stuCount)
2)学生表 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;

查看触发器，删除触发器与删除数据库，查看数据库一样。

10.mysql语句优化：

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

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

（2）select * from admin left join log on admin.admin_id = log.admin_id where0 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'

11.如何提高mysql的安全性

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

（2）设置除了root用户外的其他的任何用户不允许访问mysql主数据库中user表。

（3）不使用明文密码，使用md5加密，同时使用加盐技术。

（4）采用防火墙，取出一半的危险。同时为了解决安装防火墙外部网络的访问用户不能访问内部网络服务器的问题，而设立一歌非安全系统与安全系统之间的缓存区。

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

（6）应用程序连接到数据库时应该使用一般的用户账号，开放少数必要的权限给该用户。