数据库面试题

1、count(*)、count(1)、count(column)的区别

• count(*)对行的数目进行计算,包含NULL
• count(column)对特定的列的值具有的行数进行计算,不包含NULL值。
• count()还有一种使用方式,count(1)这个用法和count(*)的结果是一样的。
• 性能问题:
• 1.任何情况下SELECT COUNT(*) FROM tablename是最优选择;
• 2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询;
• 3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
• 如果表没有主键,那么count(1)比count(*)快。
• 如果有主键,那么count(主键,联合主键)比count(*)快。
• 如果表只有一个字段,count(*)最快。
• count(1)跟count(主键)一样,只扫描主键。
• count(*)跟count(非主键)一样,扫描整个表。明显前者更快一些。

2、什么是索引？索引的作用是什么？创建索引的指导原则？

索引的通俗解释就是索引就像是图书的目录，根据目录中的页码快速找到所需内容。

作用（好处）:

• 索引能大大加快数据的检索速度，
• 加速表和表之间的连接。
• 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
• 创建唯一性索引，保证数据库表中每一行数据的唯一性;
• 索引可以有效提高系统性能。
• 指导原则:
• 在经常用作过滤器的字段上建立索引；
• 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；
• 在不同值较少的字段上不必要建立索引，如性别字段；
• 对于经常存取的列避免建立索引；
• 用于联接的列（主健/外健）上建立索引；
• 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；
• 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有有限数目（不是很少）唯一的列；进 行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各 种查询的分析和预测中，也取决于DBA的所设计的数据库结构。

3、在数据库中查询语句速度很慢，如何优化？

1. 建索引
2. 减少表之间的关联
3. 优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据 量大的表排在前面
4. 简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据
5. 尽量用PreparedStatement来查询，不要用Statement

4、truncate、 delete与drop区别

• delete和truncate只删除表的数据不删除表的结构
• 速度,一般来说: drop> truncate >delete
• delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
• 如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback
• segment中,不能回滚. 操作不触发trigger.
• TRUNCATE TABLE ：删除内容、释放空间但不删除定义。
• DELETE TABLE: 删除内容不删除定义，不释放空间。
• DROP TABLE ：删除内容和定义，释放空间。
• 不再需要一张表的时候，用drop
• 想删除部分数据行时候，用delete，并且带上where子句
• 保留表而删除所有数据的时候用truncate

5、所有的视图是否都可以更新?为什么?

答案：不是。

• 视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。
• 因为有些视图的更新不能惟一有意义地转换成对相应基本表的更新，所以，并不是所有的视图都是可更新的

6、内联接,外联接区别？

• 内连接是保证两个表中所有的行都要满足连接条件，而外连接则不然。
• 在外连接中，某些不满条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种

7、什么是事务？事务有哪些特性？

事务是程序中一系列严密的操作，所有操作执行必须成功完成，否则在每个操作所做的更改将会被撤销，这也是事务的原子性（要么成功，要么失败）。

事务特性：

事务特性分为四个：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持续性（Durability）简称ACID。

• 原子性（Atomicity）:
• 事务是数据库逻辑工作单元，事务中包含的操作要么都执行成功，要么都执行失败。
• 一致性（Consistency）：
• 事务执行的结果必须是使数据库数据从一个一致性状态变到另外一种一致性状态。当事务执行成功后就说数据库处于一致性状态。如果在执行过程中发生错误，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这是数据库就处于不一致状态。
• 隔离性（Isolation）：
• 一个事务的执行过程中不能影响到其他事务的执行，即一个事务内部的操作及使用的数据对其他事务是隔离的，并发执行各个事务之间无不干扰。
• 持续性（Durability）：
• 即一个事务执一旦提交，它对数据库数据的改变是永久性的。之后的其它操作不应该对其执行结果有任何影响。

8、数据库存储过程的优点

• 优点

• 1.运行速度：对于很简单的sql，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对其进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通sql快。

• 2.减少网络传输：存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。但是在存储过程中没有多次数据交互，那么实际上网络传输量和直接sql是一样的。而且我们的应用服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速。

• 3.可维护性：的存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。 有些bug，直接改存储过程里的业务逻辑，就搞定了。

• 4.增强安全性：提高代码安全，防止 SQL注入。这一点sql语句也可以做到。

• 5.可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后的扩展和DBA维护优化。

• 缺点

• 1.SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理，如果如果把业务逻辑全放在存储过程里面，违背了这一原则。

• 2.如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用，等等，这时候估计会比较繁琐了。

• 3.开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。

• 4.没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。

• 5.不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

9、简要叙述软件系统的三层架构？

软件系统的三层架构一般分为三层，从下至上分别为：数据访问层、业务逻辑层（又或称为领域层）、表示层。

1. 表示层（UI）：通俗讲就是展现给用户的界面，即用户在使用一个系统的时候他的所见所得。
2. 业务逻辑层（BLL）：针对具体问题的操作，也可以说是对数据层的操作，对数据业务逻辑处理。
3. 数据访问层（DAL）：该层所做事务直接操作数据库，针对数据的增添、删除、修改、查找等。

10、存储过程与触发器的区别

• 触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。
• 触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。
• 触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

11、SQL的select语句完整的执行过程

1. from 子句组装来自不同数据源的数据；
2. where 子句基于指定的条件对记录行进行筛选；
3. group by 子句将数据划分为多个分组，使用聚集函数进行计算；
4. 使用 having 子句筛选分组，计算所有的表达式；
5. select 的字段；
6. 使用 order by 对结果集进行排序。
7. limit