SQL面试题总结

1.描述主键、外键、候选主键、超键是什么

超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键

候选键(candidate key):不含有多余属性的超键称为候选键

主键(primary key):一个列或多列的组合，其值能唯一地标识表中的每一行

外键(foreign key)如果关系模式R1中的某属性集不是R1的主键，而是另一个关系R2的主键,则该属性集是关系模式R1的外键。

2.数据库设计的三大范式

第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性;

第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键；

第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关(外键也是直接相关)，

3.drop,delete与truncate的区别

1、drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。

drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。

注意:truncate 不能删除行数据,要删就要把表清空。

3、delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存

以便进行进行回滚操作。

truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）

4、truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。

如果要删除表定义及其数据，请使用 drop table 语句。
5、对于由foreign key约束引用的表，不能使用truncate table ，而应使用不带where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。

6、执行速度，一般来说: drop> truncate > delete。

7、delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

4.SQL UNION 和 UNION ALL 区别

UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。

5.exists、in、any、all区别

in表示值是否存在子查询结果集中

exists是表示子查询是否返回结果，而不管返回的具体内容。

in是“=any的简写”；

not in是“<>all”的简写

6.sql语句的执行顺序

form > join > on > where > group by > avg,sum… > having > select > distinct > order by >limit

7.count(*)和count(1)和count(id)区别

count(*) 和count(1) 都是统计表的行数，而count(col) 是统计col列非null的行数

执行效率方面：

count(*)>count(1)>count(id)

因为mysql本身对于count(*)做了特别的优化处理,count(1)在每行添加一个新列值为1,效率略低,count(id)最低，因为在扫描数据表的同时，还要排除null

8.SQL语句优化（不少于6条）

选择最有效率的表名顺序

数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表放在最后，如果有3个以上的表连接查询,那就需要选择那个被其他表所引用的表放在最后。

WHERE子句中的连接顺序

数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

SELECT子句中避免使用*号

数据库在解析的过程中,会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间

1 对查询进行优化,应避免全表扫描,首先考虑在where及order by涉及的列上建立索引

2 避免在where子句中使用!=和<>,否则搜索引擎将放弃使用索引而进行全表扫描

3 避免在where子句中对字段进行null值判断,否则搜索引擎将放弃使用索引而进行全表扫描

4 避免在where子句中使用or来连接条件,否则搜索引擎将放弃使用索引而进行全表扫描

5 避免在where子句中使用进行表达式操作,否则搜索引擎将放弃索引而进行全盘扫描

6 避免在where子句中对字段进行函数操作,否则搜索引擎将放弃索引而进行全盘扫描

9.mysql explain是什么？有什么作用？

EXPLAIN :模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。

通过expalin可以得到：

1.表的读取顺序

2.表的读取操作的操作类型

3.哪些索引可以使用

4.哪些索引被实际使用

5.表之间的引用

6.每张表有多少行被优化器查询

10.什么是索引？索引的种类？索引的优缺点?

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构实现原理（B+树）

唯一索引： UNIQUE 例如：create unique index stusno on student（sno）；
表明此索引的每一个索引值只对应唯一的数据记录，对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

主键索引： primary key
数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

聚集索引（也叫聚簇索引）：cluster
在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。 如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

建立索引的优点：

1、索引能够提高数据检索的效率，降低数据库的IO成本。

2、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性，创建唯一索引

3、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

4、加速两个表之间的连接，一般是在外键上创建索引

建立索引的缺点：

1、需要占用物理空间,建立的索引越多需要的空间越大

2、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

11.设计索引的原则？(不少于4条)

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8 . 最左前缀匹配原则，非常重要的原则。

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9 .=和in可以乱序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

10 . 尽量选择区分度高的列作为索引。

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录

11 .索引列不能参与计算，保持列“干净”。

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

12 .尽量的扩展索引，不要新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

12.什么情况下索引会失效？（不少于4条）

like查询是以%开头

在where子句中使用or来连接条件

在where语句中使用Not In

where子句中对字段进行表达式操作,对字段进行null值判断,使用!=和<>,对字段进行函数操作操作 都会使索引失效

13.什么是存储过程？有什么优缺点？

存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库

优点:

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程可以重复使用,可减少数据库开发人员的工作量

4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

缺点：
1.调试麻烦

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

2.可移植性差

由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。

14.数据库优化方案？（至少3条）

1、数据表存储引擎的选择

2、从内存中读取数据，将数据保存到内存中

3、定期根据数据库数据情况优化重建数据库

4、减少磁盘IO操作

5、提高磁盘读写速度

6、建立索引

7、分析查询日志和慢查询日志

8、极端情况可以使用内存磁盘

9、用 NOSQL 的方式使用 MYSQL (使用B-TREE建立索引)

15. 描述数据库通用的函数?（除了聚合函数）（至少4个）

1. nvl(数字|列，默认值) ：如果显示数字是null，则使用默认数值表示，如果不为空，则显示原始值
2. nvl2 (数字|列，返回结果1（不为空显示），返回结果2（为空显示）)：判断指定列是否是null，如果不为null则显示结果1，否则返回结果2
3. nullif（表达式1，表达式2）：比较表达式1和表达式2的结果是否相等，如果相等返回NULL，如果不等返回表达式1
4. decode（列|值，判断值1，显示结果1.判断值2，显示结果2.。。。默认值）：多值判断，如果某一个列（或者某一个值）与判断值相同，则使用指定的显示结果输出，如果没有满足条件，则显示默认值
5. case 列|数值 when 表达式1 then 显示结果1….else…表达式n…end:用于是吸纳多条件判断，在when之后编写条件，而在then之后编写条件满足的显示操作，如果都不满足则使用else中的表达式处理
6. coalesce（表达式1，表达式2，。。。。表达式n）：将表达式逐个判断，如果表达式1的内容是null，则显示表达式2，如果表达式2的内容是null，则显示表达式3，依次类推，如果表达式n的结果还是null，则返回null

16. mysql中有哪几种锁？都有什么区别？

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

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

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

4. 悲观锁（Pessimistic Concurrency Control，PCC）：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。至于怎么加锁，加锁的范围也没讲。

5. 乐观锁（Optimistic Concurrency Control，OCC）：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。也没具体指定怎么检查。