Mysql基础篇

1 数据库的三大范式

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。

第二范式：在第一范式的基础上，消除非主属性对主属性的部分函数依赖。要求实体的非主键完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的属性。

第三范式：在第二范式的基础上，任何非主键不依赖于其它非主键。非主属性必须直接依赖于主属性，不能间接依赖主属性。

2、SQL语句主要分为哪几类?

数据定义语言DDL （Data Ddefinition Language） CREATE，DROP，ALTER，主要为以上操作即对逻辑结构等有操作的，其中包括表结构，视图和索引。

数据查询语言DQL（Data Query Language) SELECT，这个较为好理解，即查询操作，以select关键字，各种简单查询，连接查询等，都属于DQL。

数据操纵语言DML （Data Manipulation Language）INSERT，UPDATE，DELETE主要为以上操作，即对数据进行操作的，DQL与DML共同构建了常用的增删改查操作。

数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK主要为以上操作即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

3 MySQL存储引擎 to memory

MySQL支持多种存储引擎，比如InnoDB、MyISAM、Memory、 Archive等等.在大多数的情况下，直接选择使用InnoDB引擎都是最合适的，InnoDB也是 MySQL的默认存储引擎。

MyISAM和InnoDB的区别有哪些:

InnoDB支持事务，MyISAM不支持。

InnoDB支持外键，MyISAM不支持。

InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高;MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。

InnoDB不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高。

InnoDB不保存表的具体行数，MyISAM 用一个变量保存了整个表的行数。

InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁；MyISAM采用表级锁(table-level locking)。

4 SQL约束

NOT NULL：用于控制字段的内容一定不能为空(NULL)。

UNIQUE：控件字段内容不能重复，一个表允许有多个Unique 约束。

PRIMARY KEY：也是用于控件字段内容不能重复，但它在一个表只允许出现一个。

FOREIGN KEY：用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

5 主键和外键

主键：表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行，这样的一列或多列称为表的主键，通过它可强制表的实体完整性。一个表只能有一个 PRIMARY KEY约束，而且PRIMARY KEY约束中的列不能接受空值。

外键：在一个表中存在的另一个表的主键称此表的外键。

6 char和varchar的区别

char：定长，存取效率高，一般用于固定长度的表单提交数据存储，例如：身份证号，手机号，电话，密码等，长度不够的时候，会采取右补空格的方式。

varchar：不定长，更节省空间，需要用一个或者两个字节来存储数据的长度。具体规则是:如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

在内存中的操作方式，varchar也是按最长的方式在内存中进行操作的。比如：进行排序的时候，varchar(100)是按100这个长度来进行的。

varchar由于行是变长的，在UPDATE时可能使行变得比原来更长，会导致分裂页和产生碎片。

7 IN 和 Exists 用法及区别

7.1 基本用法

IN：后面的子查询是返回结果集的,换句话说执行次序和Exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

Exists：后面的子查询被称做相关子查询, 他是不返回列表的值的.只是返回一个true或false的结果(这也是为什么子查询里是 "select 1 "的原因,当然也可以select任何东西) 其运行方式是先运行主查询一次。再去子查询里查询与其对应的结果，如果是true则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询。

7.2 运行过程

Exists执行顺序如下：　　1.首先执行一次外部查询　　2.对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。　　3.使用子查询的结果来确定外部查询的结果集。（如果外部查询返回100行，SQL 就将执行101次查询，一次执行外部查询，然后为外部查询返回的每一行执行一次子查询。但实际上，SQL的查询优化器有可能会找到一种更好的方法来执行相关子查询，而不需要实际执行101次查询。）

IN的执行过程如下：

1.首先运行子查询，获取子结果集

2.主查询再去结果集里去找符合要求的字段列表，.符合要求的输出,反之则不输出。

7.3 区别

7.3.1 IN和Exists

IN是把外表和内表作hash 连接，而Exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为Exists比IN效率高的说法是不准确的。如果查询的两个表大小相当，那么用IN和Exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用Exists，子查询表小的用IN： 例如：表A（小表），表B（大表）： 　　select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引； 　　select * from A where exists(select cc from B where cc= A .cc) 效率高，用到了B表上cc列的索引。 相反的：　　select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；　　select * from B where exists(select cc from A where cc=B .cc) 效率低，用到了A表上cc列的索引。

7.3.2 not IN 和not Exists

　　not IN 和not Exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not Exists都比not IN要快。

7.3.3 in 与 = 的区别

　　select name from student where name in (‘A’,‘S’,‘D’,‘F’); 与　　select name from student where name=‘A’ or name=‘S’ or name=‘D’ or name=‘F’ 的结果是相同的。

8 drop delete truncate的区别

9 InnoDB存储原理

页(page): (16k)最小io单元；

区(regoin): (1m)64个页，大小刚好为1MB，存储引擎空间申请的最小单位；

段(segment): 每个段有32个碎片页，段中的空间首先保存在这32个页中，超出容量后再以区的方式申请空间，段的这种页和区混合管理的方式，是出于对存储空间尽量节约的角度考虑；

表空间(table space):存储引擎逻辑结构最高层，由各个段组成，数据段，索引段，回滚段;碎片页从碎片区中申请，碎片区不属于任何段，碎片区直属于表空间。

10 InnoDB存储引擎的内存结构由4部分组成

从MSQL5.5版本开始，默认使用的就是InnoDB存储引擎，它擅长处理事务，具有自动崩溃恢复的特性，使用非常广泛。

Buffer Pool:缓冲池，作用就是用来缓存表数据和索引数据，减少磁盘IO操作，提升效率。

Change Buffer:写缓冲区，是针对二级索引页(辅助索引)的更新优化措施。

Log Buffer: 日志缓冲区，用来缓存写入磁盘上log文件(Redo、Undo)的数据,日志缓冲区的内容会定期的刷新到磁盘Log文件中。主要作用是:用来优化每次更新操作之后都要写入redo log产生的磁盘IO的。

Adaptive Hash Index:自适应Hash索引。InnoDB不支持手动创建哈希索引的，但是InnoDB会进行自调优，如果判断建立自适应哈希索引能够提升查询效率，InnoDB就会在自己的内存中创建相关的Hash索引。自适应指的是不需要人工手动干预，InnoDB会根据自己的需求去创建自适应Hash索引。