面试题整理-MYSQL篇

1.Mysql的存储引擎,MyISAM和InnoDB的区别。

2.delete、drop、truncate区别

使用场合：

对于有主外键关系的表，不能使用truncate而应该使用不带where子句的delete语句，由于truncate不记录在日志中，不能够激活触发器

3.优化MYSQL数据库的方法

4.mysql_fetch_row()和mysql_fetch_array()有什么分别？

mysql_fetch_row() # 返回的结果集是索引数组。
mysql_fetch_assoc() # 返回的结果集是关联数组
# 既可以返回索引数组也可以返回关联数组，
# 取决于它的第二个参数 MYSQL_BOTH MYSQL_NUM MYSQL_ASSOC 默认为MYSQL_BOTH
mysql_fetch_array()

5.php访问数据库有哪几步?

1.连接数据库服务器：

mysql_connect('数据库服务器的主机名或ip','数据库服务器的用户名','数据库服务器的密码');

2.选择数据库：

mysql_select_db(数据库名);

3.设置从数据库提取数据的字符集：

mysql_query("set names utf8");

4.执行sql语句：

mysql_query(sql语句);

5.关闭结果集，释放资源：

mysql_free_result($result);

6.关闭与数据库服务器的连接：

mysql_close($link);

6.表设计三大范式

1. 第一范式(原子性):所有字段值都是不可分解的原子值。

2. 第二范式(在第一范式的基础上):确保表中的每列都和主键相关，即一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

3. 第三范式(在第二范式的基础上):确保每列都和主键列直接关联,而不是间接相关。

7.索引类型

1.普通索引(index):

# 创建:
CREATE INDEX <索引名> ON tablename (索引字段)
# 修改:
ALTER TABLE tablename ADD INDEX [索引名] (索引字段)
# 创建指定索引:
CREATE TABLE tablename([...],INDEX[索引名](索引字段))

2.唯一索引(unique):在普通索引的基础上，会进行排除重复值

#创建:
CREATE UNIQUE <索引名> ON tablename (索引字段)
#修改:
ALTER TABLE tablename ADD UNIQUE [索引名] (索引字段)
#创建指定索引:
CREATE TABLE tablename([...],UNIQUE[索引名](索引字段)) 

3.主键(primary key):和唯一索引的区别在于一个表里只能有一个主键索引，但是唯一索引可以有多个

#它是唯一索引,一般在创建表时建立
CREATA TABLE tablename ([...],PRIMARY KEY[索引字段])

4.联合索引

# 语法：
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

5.全文索引 (fulltext)

版本支持

创建全文索引

# 创建表时创建全文索引
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `phone` char(11) NOT NULL DEFAULT '' COMMENT '手机号',
  `name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
  `address` text NOT NULL COMMENT '地址',
  PRIMARY KEY (`id`),
  # 创建全文索引列
  FULLTEXT KEY address_fulltext(address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  COMMENT='用户帐号表';
# 在已存在的表上创建全文索引
CREATE fulltext index name_fulltext on `user`(address); 
# 通过SQL语句 ALTER TABLE 创建全文索引
alter table `user` add FULLTEXT index name_fulltext(address);

使用全文索引和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如: 

SELECT * from `user` WHERE MATCH(`address`) against('碧园春小区')

match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，请单独为该列创建全文索引。

问题: 普通索引／唯一索引／主键索引 哪个速度更快？

速度是一样的快，因为三者都是采用btree二叉树算法进行查找。

8.索引算法

1.BTREE算法

Innodb和MyISAM默认的索引是BTREE索引,采用二叉树算法，左边的树枝小于根节点关键词，右边大于根节点，两边的树的深度不大于1，从而降低时间复杂度。

2.HASH算法

Mermory默认的索引是Hash索引,Hash索引只能用于HASH值比较，例如=,<> 操作符，不像BTREE索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTREE索引。

为什么不默认采用HASH索引呢？

HASH只能用在=和<>上，所以功能受限，所以默认采用BTREE。

9.insert和replace的区别

replace into 跟 insert 功能类似，不同点在于: replace into 首先尝试插入数据到表中， 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

replace into tbl_name(col_name, ...) values(...)
replace into tbl_name(col_name, ...) select ...
replace into tbl_name set col_name=value, ...

前两种形式用的多些。其中 "into” 关键字可以省略，不过最好加上 "into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值

10.事务操作数据库的四大特性(ACID)

11.不考虑事务的隔离性所引发的问题

脏读：一个事务读取到了一个未提交的事务的数据。不可重复读：在读取数据库的某条数据的时候返回了不同的值，造成这个结果的原因是因为我们在查询了一次之后准备进行第二次查询的这个间隔之间，对我们要进行查询的这条数据进行了修改操作，从而导致两次读取的数据不一致。

脏读和不可重复读的区别:脏读是一个事务读取到了一个未提交事务的脏数据，而不可重复读是一个数据读取了一个已经提交了的事务的数据。

虚读(幻读)：出现幻读不是对一条数据的操作而产生的问题，而是操作多条数据产生的问题，例如：事务A想要对一张表中的某一字段的值进行修改，假设有一个字段的值全部为1，事务A现在想要将1全部修改为2，在提交事务之后，事务B接着又进行了一个操作，在这张表中添加了一个字段，值全部为1。那么这时候操作事务A的用户在查看的时候，会发现还有一行数据没有进行修改，其实这是事务B在他查看之前添加的。

幻读和不可重复读都是读取了一个已经提交的事务，而脏读是读取了一个未提交的事务。不同的是不可重复读查询的是同一条数据，而虚读查询的是批量数据。

12.事务的四种隔离级别

以上四种的隔离级别最高的Serializable，最低的是Read uncommitted，级别越高，虽然安全级别越高，但是执行的效率就越低，MySQL中默认的隔离级别是:Repeatable read(可重复读)，oracle默认的隔离级别是：Read committed(读已提交)。

这里需要注意的是，mysql支持以上四种隔离级别，但是oracle只支持Serializable(串行化)和Read committed(读已提交)这两种隔离级别。

select @@tx_isolation

set tx_isolation='read-uncommitted'

记住:设置数据库的隔离级别一定要是在开启事务之前！

隔离级别的设置只对当前的链接有效。对于MySQL窗口来说，一个窗口就是一个链接，当前设置的事务隔离级别只对当前的窗口有效。

13.CHAR和VARCHAR的区别

1.存储方式

2.数据检索

如果CHAR字段的数据较短时，会经过空格填充，所以查询出来的结果需删除尾随空格。

3.存储上限

4.性能对比

按照查询速度：char最快， varchar次之，text最慢

5.不同存储引擎对CHAR和VARCHAR的使用

14.BLOB和TEXT有什么区别

15.NOW()和CURRENT_DATE() 有什么区别

16.如何理解超键、候选键、主键、外键

1. 主键(Primary Key)：对数据库表中的每一行数据进行唯一标识。

2. 外键(foreign key)：是表中的一列，其值必须在另一个表的主键中。3.超键(Super Key)：在关系中能惟一标识元组(数据库中的一条记录)的属性集称为关系模式的超键。比如:一张学生信息表，学生表中含有学号或者身份证号的任意组合都为此表的超键。如：（学号）、（学号，姓名）、（身份证号，性别）等。4.候选键(Candidate Key)：不含有多余属性的超键称为候选键。也就是在候选键中，若要再删除属性，就不能唯一标识元组了。比如:学生表中的候选键为：（学号）、（身份证号）。

17.一条SQL语句在MySQL中是如何执行的?

查询语句的执行流程如下: 

权限校验（如果命中缓存）--> 查询缓存 -->分析器-->优化器-->权限校验-->执行器-->引擎

更新语句执行流程如下:

分析器--> 权限校验--> 执行器--> 引擎--> redo log(prepare 状态--> binlog--> redo log(commit状态)

18.什么是存储过程？

存储过程是一些预编译的SQL语句。

1. 更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

2. 存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全。

19.什么是视图？以及视图的使用场景有哪些？

1. 视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

2. 只暴露部分字段给访问者，所以就建一个虚表，就是视图。

3. 查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异。

20.数据库的乐观锁和悲观锁是什么？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁: 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

乐观锁: 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

21.什么是触发器，MySQL中都有哪些触发器？

触发器是指一段代码，当触发某个事件时，自动执行这些代码。在MySQL数据库中有如下六种触发器: