MySQL数据库面试题

Mysql优化

1. 不要用*

2. 用连接查询代替子查询

3. 合理使用索引

4. 46. 在数据库中条件查询速度很慢的时候，如何进行优化？
       1）建索引
       2）减少表之间的关联
       3）优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据量大的
       表排在前面
       4） 简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据

首先介绍事物的四大特性(ACID)：

原子性：事务是执行数据库操作的最小逻辑,不可再分，要么全部执行，要么全部失败（回滚）；

一致性： 事务完成之后，不允许有非法的数据写入数据库。

例如转账案例 假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

隔离性：多个事务可以在一定程度上并发执行,不影响其他事物。

持久性：事务完成之后，数据要写入数据库,对数据库的影响是持久的（也就是说会写入硬盘）。

脏读，幻读，不可重复读，丢失修改

脏读：事务A查询数据后进行了一次修改且未提交，而事务B这个时候去查询，然后使用了这个数据，因为这个数据还没有被事务A 提交到数据库中，所以事务B的得到数据就是脏数据，对脏数据进行操作可能是不正确的。

不可重复读: 事务A访问了两次数据，但是这访问第二次之间事务B进行一次并进行了修改，导致事务A访问第二次的时候得到的数据与第一次不同，导致一个事务访问两次数据得到的数据不相同。因此叫做不可重复读。

幻读： 与不可重复读都点相似，只是这次是事务B在事务A访问第二次的之前做了一个新增，导致事务A第二次读取的时候发现了多的记录，这就是幻读。

丢失修改：事务A访问该数据，事务B也访问该数据，事务A修改了该数据，事务B也修改了该数据，这样导致事务A的修改被丢失，因此称为丢失修改；

不可重复度和幻读区别：

不可重复读主要是修改操作，幻读的主要在于新增或者删除。

幻读主要在于数据的条数变了，而不可重复读主要在于数据内容变了。

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读 （non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有 了“隔离级别”的概念。

注意隔离级别越高，效率越低
SQL 标准的事务隔离级（参考高性能MySQL第三版）

读未提交（read uncommitted）:事务可以读取未提交的数据,这也被称为脏读( Dirty Read)。这个级别会导致 很多问题,从性能上来说, 读未提交不会比其他的级别好太多,但却缺乏 其他级别的很多好处,除非真的有非常必要的理由,在实际应用中一般很少使用。最低的隔离级别

读已提交（READ-COMMITTED）：大多数数据库默认隔离级别都是这个，但是MySQL不是，一个事务开始的时候到提交所做的操作对其他事务是不可见的。避免了脏读，但是仍然会出现幻读和不可重复读 MySQL默认隔离级别。

可重复读(REPEATABLE-READ)：保证了同一个事务多次读取同样的记录结果是一致的。可以阻止脏读和不可重复读，但幻读仍有可能发生。

可串行化(SERIALIZABLE)：最高的隔离级别，完全服从ACID的隔离级别。强制事务串行执行，避免了幻读，脏读和不可重复读。他会在读取的时候给每一个行加锁，所以可能导致大量的超时和锁争用的情况，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下才考虑的级别。

视图

为什么要使用视图？什么是视图？

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

视图有哪些特点？

视图的特点如下:

视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。

视图是由基本表(实表)产生的表(虚表)。

视图的建立和删除不影响基本表。

对视图内容的更新(添加，删除和修改)直接影响基本表。

当视图来自多个基本表时，不允许添加和删除数据。

视图的操作包括创建视图，查看视图，删除视图和修改视图。

视图的使用场景有哪些？

视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。

下面是视图的常见使用场景：

重用SQL语句；

简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；

使用表的组成部分而不是整个表；

保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；

更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的优点

查询简单化。视图能简化用户的操作
数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

视图的缺点

性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。

修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图（其中有些例外）

如何创建视图?

create view 视图名 as select

create database jsd2008db default character set utf8;
use jsd2008db;
create table t_emp(
	id int primary key auto_increment,
	name varchar(50),
	salary int,
	age int
);

insert into t_emp values(null,'Sally',10000,22);
insert into t_emp values(null,'Keely',20000,26);
insert into t_emp values(null,'Eric',30000,32);

create view v_emp as select * from t_emp;
create view v_emp2(name,age) as select name,age from t_emp;

--对视图可以进行添加、删除、修改等操作，这些操作会影响到底层的表，但是，
--如果视图是基于多张表创建的，则不允许做这些操作。视图一般用于查询。
insert into v_emp2 values('John',23);

create table t_dept(
	id int primary key,
	name varchar(50),
	city varchar(50)
);
insert into t_dept values(100,'开发部','北京');
insert into t_dept values(200,'财务部','上海');

create table t_staff(
	id int primary key auto_increment,
	name varchar(50),
	age int,
	dept_id int
);
insert into t_staff values(null,'张三丰',108,100);
insert into t_staff values(null,'花花',28,100);
insert into t_staff values(null,'King',38,200);

create view v_staff_dept(sname,dname,city) as select s.name sname,d.name dname,d.city from t_staff s join t_dept d on s.dept_id=d.id;

--这儿不能进行添加、删除、修改操作，因为该视图是基于多表创建的。
insert into v_staff_dept(sname,dname,city) values('Rose','dev','abc');

如何删除视图?

​ drop view 视图名

drop view v_emp;

​

视图的优点

​ a. 简化开发：

​ 可以将一些复杂的查询定义成视图。

​ b. 方便维护（逻辑独立性）：

​ 对原始表做一些改动，只要不涉及到视图的定义，是不会影响到视图的。

​ c. 安全：

​ 开发人员只能查看到视图中的数据。

什么是游标？

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行
结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

超键、候选键、主键、外键分别是什么？

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

SQL 约束有哪几种？

SQL 约束有哪几种？

1.Not Null: 用于控制字段的内容一定不能为空（NULL）。
2.Unique: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
3.Primary key: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
1).相当于唯一性约束 + 非空约束的组合
2).每个表最多只允许一个主键
3).创建主键时,数据库系统默认会创建一个主键索引.
4.Foreign key: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。用于保证两张表之间的参照完整性.
5.check: 用于控制字段的值范围。