数据库面试常见问题

共享锁和排它锁：

    共享锁（读锁）：在同一个时间段内，多个用户可以读取同一个资源，读取的过程中数据不会发生任何变化。读锁之间可以多个用户同时读，但不允许有人修改；

    排它锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作，只能由这一个用户来写，其他用户既不能读也不能写。

触发器：

    当某一事件触发，系统自动调用。

工作机制：

    触发器是建在表上的，当这个表发生新增、修改、删除操作时，如果这个表上有触发器，就会被自动调用。

作用：维护表的完整性，记录表的修改来审计表的相关信息。

创建触发器的SQL语法：

    create trigger 触发器名称   --触发器名称

    on 表名                 --建在哪个表上

    for insert|update|delete    --是插入事件处理还是修改事件处理还是删除事件处理

    as                       --以下是触发器基本格式

    begin

    end

触发器优点：

    自动执行：触发器不用像存储过程一样需要手动调用，是自动触发的，只有当对表进行更新，删除等操作的时候会立即触发。

    级联更新：触发器可以通过数据库中的相关表进行层叠更改，这比直接将代码写在前端的做法更安全合理。

    强化约束：触发器可以引用其他表的列，能够实现比check约束更为复杂的约束。

    跟踪变化：触发器可以阻止数据库中未经允许的指定更新和变化。

    强制业务逻辑：触发器可用于执行管理任务，并强制影响数据库的复杂业务规则。

缺点：

    不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务。

函数：

    MySQL中提供了许多内置函数，还可以自定义函数（实现程序员需要sql逻辑处理）

自定义函数创建语法：

　　创建：CREATE FUNCTION 函数名称(参数列表)

                  RETURNS 返回值类型 　函数体

 　  修改： ALTER FUNCTION 函数名称 [characteristic ...]

　   删除：DROP FUNCTION [IF EXISTS] 函数名称

      调用：SELECT 函数名称(参数列表)

索引——排好序的快速查找数据结构

    对数据库表中一或多个列的值进行排序的结构，利用索引可快速访问数据库表的特定信息。

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

优点：

• 索引加快数据库的检索速度，降低数据库的io成本
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
• 加速表和表之间的连接；
• 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间，降低了cpu的消耗。

缺点：

• 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
• 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
• 当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

索引类型

• 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
• 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。 为表定义主键将自动创建主键索引。（数据库表某列或列组合，其值唯一标识表中的每一行。该列称为表的主键。）
• 联合索引：指对表上的多个列做索引。只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。
• 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。目前只有char、varchar，text 列上可以创建全文索引。

一个表最多只能创建一个主键，但可以创建多个唯一索引。主键可以被其他表引用为外键，而唯一索引不能。

  

视图：

    一个命名的虚表，由一个查询来定义，可以当作表使用。

视图有什么用（应用场景）：

1、当一个查询你需要频频的作为子查询使用时，视图可以简化代码，直接调用而不是每次都去重复写这个东西。
2、系统的数据库管理员，需要给他人提供一张表的某两列数据，而不希望他可以看到其他任何数据，这时可以建一个只有这两列数据的视图，然后把视图公布给他。

创建视图sql语句：

    CREATE VIEW view_name AS

    SELECT column_name(s)

    FROM table_name

    WHERE condition

视图与表的区别：

      1、视图是已经编译好的sql语句，而表不是。
      2、视图没有实际的物理记录，而表有。
      3、表是内容，视图是窗口。
      4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能由创建的语句来修改。
      5、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
      6、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。 
      7、视图的建立和删除只影响视图本身，不影响对应的基本表。
      8、不能对视图进行update或者insert into操作。

三大范式

第一范式（1NF）
　　（在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。）

　　所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，第一范式就是无重复的列。强调的是列的原子性，即列不能够再分成其他几列。

第二范式（2NF）
　　满足第二范式（2NF）必须先满足第一范式（1NF）。另外包含两部分内容，一是表必须有主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式（3NF）
　　满足第三范式（3NF）必须先满足第二范式（2NF）第三范式就是属性不依赖于其它非主属性。非主键列必须直接依赖于主键，不能存在传递依赖。

Like %和-的区别：

    % 通配符：表示任何字符出现任意次数 (可以是0次)。

    _ 通配符：表示只能匹配单个字符，不能多也不能少，就是一个字符。

Mysql中的几种锁：

    1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

    2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

    3. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

主键和候选键：
    表格的每一行都由主键唯一标识,一个表只有一个主键。

    主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

    db_accessadmin：可以添加、删除用户的用户；

    db_datareader：可以查看所有数据库中用户表内数据。2

“插入异常”：指当要往数据库中插入新的数据的时候，插入不成功导致异常；

“删除异常”：指当要删除数据库中数据的时候，不能删除，删除不成功；

“修改异常”：指当要修改数据库中数据的时候，修改不成功；

数据库中的“插入异常”、“删除异常”、“修改异常”是数据库模式中存在依赖关系导致的。

三个重要约束：

    1、实体完整性——主键约束

     2、参照完整性——外键约束

     3、用户定义完整性——检查/非空/默认值约束

在数据库的安全性控制中，为了保证用户只能存取他有权存取的数据，在授权的定义中，数据对象的范围越小，授权子系统就越灵活。

提取前n条记录：
    sql server：select TOP n * from table_name;

    Mysql：select * from table_name limit 0,n;

    Oracle：select * from table_name where rownum <=n;

数据库怎么优化查询效率？

    1、储存引擎选择：如果数据表需要事务处理，应该考虑使用 InnoDB，因为它完全符合 ACID 特性。如果不需要事务处理，使用默认存储引擎 MyISAM 是比较明智的

    2、分表分库，主从。

    3、对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

    4、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

    5、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描

    6、应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

    7、Update 语句，如果只更改 1、2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志

    8、对于多张大数据量（这里几百条就算大了）的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，性能很差。

Mysql 集群的优缺点？

优点：

    99.999%的高可用性

    快速的自动失效切换

    灵活的分布式体系结构，没有单点故障

    高吞吐量和低延迟

    可扩展性强，支持在线扩容

缺点：

    存在很多限制，比如：不支持外键

    部署、管理、配置很复杂

    占用磁盘空间大、内存大

    备份和恢复不方便

    重启的时候，数据节点将数据 load 到内存需要很长的时间

你用的 Mysql 是哪个引擎，各引擎之间有什么区别？

主要 MyISAM 与 InnoDB 两个引擎，其主要区别如下：

    InnoDB 支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而 MyISAM 就不可以了；

    MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用；

    InnoDB 支持外键，MyISAM 不支持；

    MyISAM 是默认引擎，InnoDB 需要指定；

    InnoDB 不支持 FULLTEXT 类型的索引；

    InnoDB 中不保存表的行数，如 select count() from table 时，InnoDB；需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。注意的是，当 count()语句包含where 条件时 MyISAM 也需要扫描整个表；

    对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立联合索引；清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表；

InnoDB 支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

数据库的优化？

    1.优化索引、SQL 语句、分析慢查询；

    2.设计表的时候严格根据数据库的设计范式来设计数据库；

    3.使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘 IO

    4.优化硬件；采用 SSD，使用磁盘队列技术(RAID0,RAID1,RDID5)等

    5.采用 MySQL 内部自带的表分区技术，把数据分层不同的文件，能够提高磁盘的读取效率；

    6.垂直分表；把一些不经常读的数据放在一张表里，节约磁盘 I/O；

    7.主从分离读写；采用主从复制把数据库的读操作和写入操作分离开来；

    8.分库分表分机器（数据量特别大），主要的的原理就是数据路由；

    9.选择合适的表引擎，参数上的优化

    10.进行架构级别的缓存，静态化和分布式；

    11.不采用全文索引；

    12.采用更快的存储方式，例如 NoSQL 存储经常访问的数据**

如何对查询命令进行优化？

    a. 应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索。

    b. 应尽量避免在 where 子句中对字段进行 null 值判断，避免使用!=或<>操作符，避免使用 or 连接条件，或在 where 子句中使用参数、对字段进行表达式或函数操作，否则会导致权标扫描

    c. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

    d. 使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

    e. 很多时候可考虑用 exists 代替 in。

    f. 尽量使用数字型字段。

    g. 尽可能的使用 varchar/nvarchar 代替 char/nchar。

    h. 任何地方都不要使用 select from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。

    i. 尽量使用表变量来代替临时表。

    j. 避免频繁创建和删除临时表，以减少系统表资源的消耗

    k. 尽量避免使用游标，因为游标的效率较差。

   l. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF。

    m. 尽量避免大事务操作，提高系统并发能力。

    n. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

Sql 注入是如何产生的，如何防止？

程序开发过程中不注意规范书写 sql 语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST 和 GET 提交一些 sql 语句正常执行。产生 Sql 注入。下面是防止办法：

    a. 过滤掉一些常见的数据库操作关键字，或者通过系统函数来进行过滤。

    b. 在 PHP 配置文件中将 Register_globals=off;设置为关闭状态

    c. SQL 语句书写的时候尽量不要省略小引号(tab 键上面那个)和单引号

    d. 提高数据库命名技巧，对于一些重要的字段根据程序的特点命名，取不易被猜到的

    e. 对于常用的方法加以封装，避免直接暴漏 SQL 语句

    f. 开启 PHP 安全模式：Safe_mode=on;

    g. 打开 magic_quotes_gpc 来防止 SQL 注入

    h. 控制错误信息：关闭错误提示信息，将错误信息写到系统日志。

    i. 使用 mysqli 或 pdo 预处理。

NoSQL 和关系数据库的区别？

1. 性能

    NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。

2. 可扩展性

    同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势：

1. 复杂查询

    可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。

2. 事务支持

    使得对于安全性能很高的数据访问要求得以实现。