mysql面试题

1. 查询学生表中30-41条的记录

limit
第一个参数指定第一个返回记录行的偏移量（从0开始）
第二个参数指定返回记录行的最大数目

SELECT * FROM student limit 29,12

2. 查出有两门不及格的学生信息

学生表student [id, name]
课程表course [id, name]
成绩表scores [sid, cid, score]

select s.id, s.name from student s
where s.id in(
	select sc.sid from sc where sc.score < 60 group by sc.sid having count(sc.cid) = 2
)

3. 去除数据库中重复的记录（保留id最小的记录）

分析步骤：

1. 查出重复的数据：

select name from tuser group by name having count(name) > 1

2. 查出重复的name数据中最小的id

select min(id) from tuser group by name having count(name) > 1

3. 查出重复的name数据中非最小的id（需要删除的）

select id from tuser where name in(
	select name from tuser group by name having count(name) > 1)
	and id not in(select min(id) from tuser group by name having count(name) > 1)
)

4. 在mysql中是不能删除查询出来的记录的，而是要通过一张临时表来解决

select id from (
	select id from tuser where name in(
		select name from tuser group by name having count(name) > 1)
		and id not in(select min(id) from tuser group by name having count(name) > 1)
) as t

5. 删除name重复的数据（只保留id最小的那一条）

delete from tuser where id in(
	select id from (
		select id from tuser where name in(
			select name from tuser group by name having count(name) > 1)
			and id not in(select min(id) from tuser group by name having count(name) > 1)
	) as t
)

4. 批量修改 id 字段值

将数据库中所有的 id 值加上100

update tuser set id = LAST_INSERT_ID(id+100) WHERE id >= 1

注意：如果某条记录的 id 加上100后和其他记录的 id 值相碰撞，则修改失败
可采用如下方法将 id 值加上100：

update tuser set id = LAST_INSERT_ID(id+10000) WHERE id >= 1
update tuser set id = LAST_INSERT_ID(id-99900) WHERE id >= 1

5. 谈谈数据库的三大范式及反范式

第一范式：列不可分
第二范式：要有主键（唯一标识）
第三范式：不可存在传递依赖

为什么会有反范式设计？
为了提高查询效率（将关联查询转换为单表查询）

6. 说说常用的聚合函数有哪些及作用？

count(*|列)		求记录数
sum(列)			求总和
avg(列)			求平均值
max(列)			求最大值
min(列)			求最小值

7. 左连接，右连接，内连接，如何编写SQL，区别是什么？

8. 如何解决SQL注入？

SQL注入，是指通过字符串拼接的方式构成了一种特殊的查询语句

解决方案：
采用预处理对象（PreparedStatement），而不是Statement对象。
不仅可以解决SQL注入的问题，也可以提高执行效率，因为是预先编译执行

9. JDBC如何实现对事务的控制及事务边界

JDBC中默认事务是自动提交的，对事务的操作是基于Connection来进行控制的，具体代码如下：

try {
   //开启事务
   connection.setAutoCommit(false);
   //做业务操作
   //doSomething();
   //提交事务
   connection.commit();
}catch(Exception e){
   //回滚事务
   try {
      connection.rollback();
   } catch (SQLException e1) {
      e1.printStackTrace();
   }
}

10. 谈谈事务的特点

事务具有原子性、一致性、隔离性和持久性。简称ACID

原子性（ Atomicity ）
事务是数据库的逻辑工作单位，事务中包含的各操作要么一起成功，要么一起失败。

一致性（ Consistency ）
事务一致性是指数据库中的数据在事务操作前后都必须满足业务规则约束。
比如A转账给B，那么转账前后，AB的账户总金额应该是一致的。

隔离性（ Isolation ）
一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
(设置不同的隔离级别，互相干扰的程度会不同)

持久性（ Durability ）
事务一旦提交，结果便是永久性的。即使发生宕机，仍然可以依靠事务日志完成数据的持久化。

11. 谈谈事务的隔离级别

READ UNCOMMITTED 读未提交，脏读、不可重复读、幻读有可能发生。
READ COMMITTED 读已提交，可避免脏读的发生，但不可重复读、幻读有可能发生。
REPEATABLE READ 可重复读，可避免脏读、不可重复读的发生，但幻读有可能发生。
SERIALIZABLE 串行化，可避免脏读、不可重复读、幻读的发生，但性能会影响比较大。

脏读：指A事务对数据进行了修改但还没有提交，此时B事务读到了被A事务修改的数据

不可重复读：指A事务读取了两次某数据，在此期间由于B事务的修改，导致A事务两次读取的数据不一致

幻读，指A事务查询数据时是3条，在此期间由于B事务插入了数据，导致A事务提交修改时修改了4条数据

12. 数据库优化的方法

1. 设置最适用的字段属性，比如把字段宽度设置地尽可能的小
2. 给合适的字段建立索引
3. 优化sql查询语句，防止索引失效
4. 数据库分库分表，实现读写分离
5. 优化数据库系统的配置
6. 优化数据库的表结构
7. 硬件优化