MySQL知识点

数据库阶段提问考核问题

1.关系型数据库和非关系型数据库的区别

1. 关系型数据库支持多个表之间连接查询（join）,非关系型数据库不支持连接查询
2. 关系型强调数据之间的强关联型，严格按照表（有行、列、字段）,并支持事物来确保数据的增删改查的强一致性，而非关系型数据库对数据的事物则支持的不是很好
3. 关系型数据库不灵活，是一种二维的数据结构，而且对一行的数据格式类型要求很高，非关系型数据库数据格式则更灵活，数据字段类型也很灵活
4. 非关系型数据库比关系型数据库更容易扩展
5. 非关系型数据库对大量的并发读写支持的更好，性能更高，而关系型由于IO瓶颈，高并发读写速度慢，支持不好
6. 传统关系型数据库在数据库高并发读写，对海量的数据存储，对数据库的高可扩展性应用场景性能不好
   （非关系型数据库有Mongdb,Redis,Hbase,关系型数据库有Mysql,Oracle）

2.SQL的分类

DDL数据定义语言

创建修改删除 操作结构

DML数据操作语言

操作数据表的记录 （增删改） 操作数据

DQL数据库查询语言

查询数据表中的记录

DCL数据控制语言

做授权、撤销（root超级管理员） 操作权限

TCL事务控制语言

3.Mysql和Oracle的区别

1.

Oracle数据库是一个收费的数据库；MySQL是一个开源、免费的数据库。

2.数据库安全性：

MySQL使用三个参数来验证用户，即用户名，密码和位置；
Oracle使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等。

3.存储上的区别：

与Oracle相比，MySQL没有表空间，角色管理，快照，同义词和包以及自动存储管理。

4. 字符数据类型比较：

MySQL具有CHAR和VARCHAR；
Oracle支持四种字符类型，即CHAR，NCHAR，VARCHAR2和NVARCHAR2。

5.备份类型：

Oracle提供不同类型的备份工具，如冷备份，热备份，导出，导入，数据泵。
MySQL有mysqldump和mysqlhotcopy备份工具。

6.主键的使用：

MySQL：一般使用自动增长类型，在创建表的时候只要指定表的主键为auto increment,插入记录时就不需要再为主键添加记录了，主键会自动增长；
Oracle:没有自动增长，主键一般使用序列，插入记录时将序列号的下一值付给该字段即可，只是ORM框架只是需要native主键生成策略即可。

4.char和varchar的区别

char定长：长度不可变，用不完也不变
varchar可变：要留一个字符做缓冲

5.常用的约束有哪些

非空

唯一

字段列的数据是唯一的

主键（非空+唯一）

一张表只能有一个主键，但可做成联合主键（多列，姓名、性别，单列可重复，但组合起来不能）
找不到非空且唯一的列作为主键，自动生成一个row_id

默认值 0，null（未添加数据的情况）

检查约束 enum

对于插入数据选择的一个过程
eg:雌雄 check (‘雌’,‘雄’)

外键约束

6. delete drop truncate 区别

1. delete属于数据库DML操作语言，只删除数据不删除表的结构，会走事务，执行时会触发trigger，可以回滚；
2. drop 会删除表结构及所有数据，并将表所占空间全部释放。执行后立即生效，无法找回；
3. truncate不会删除表结构，表数据会完全清空，并且不可以回滚

7.聚合函数

count、min 、max、 avg、sum

8. IF函数 IFNULL函数 常用的日期函数

-- if 
SELECT if (2>3,'true','false'); -- false 

-- ifnull 如果expr1不是null,返回expre1,否则返回expre2
SELECT IFNULL(10,0)
				

-- 设计一个表 表中有一个字段叫 最后更新时间 添加时间 默认是Now()
SELECT now();
SHOW VARIABLES like '%time_zone';

-- year 返回年份 yyyy-mm-dd 
SELECT year('2001-11-20');
-- month 返回月份
SELECT month('2001-11-20');
-- date 日期 年月日
SELECT date('2001-11-20');
-- week 返回周数
SELECT week('2001-11-20');

-- DATE_ADD DATE_SUB 日期的加减法
SELECT DATE_ADD('2008-01-02',INTERVAL 30 day);
SELECT DATE_sub('2008-01-02',INTERVAL 30 day);

-- datediff 算两个日期的间隔,结果为正1表示早一天，负1表示晚一天
SELECT DATEDIFF('2008-01-02','2008-01-03')  -- -1
SELECT DATEDIFF('2008-01-02','2008-01-01')  -- 1

-- 两个日期只能做减法，不能做加法

9. where 和having区别

（ HAVING 支持 WHERE 关键字中所有的操作符和语法）

Where	Having
过滤数据行	过滤分组
不可使用聚合函数	可以使用聚合函数
分组前过滤	分组后过滤
针对数据库文件进行过滤 （根据数据中的字段直接进行过滤）	针对查询结果进行过滤（根据已经查询的字段进行过滤）
查询条件中不可以使用段别名	查询条件中可以使用段别名

10.多表关系

取出来的数据可能不在一张表中

一对一

主键关联

一对多

主键绑定非主键

多对多

非主键绑定非主键
双向一对多
需要一张中间表记录关联的数据

11.内连接和外连接的区别

内连接： 等值连接 展示两张表相关联的数据（外键关联的两个字段相关联）
外连接：以一张表为基准，将另外一张表与之相对于的数据展示出来，基准表全部展示，连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。

12.in 和 exists的作用

• in()适合子表比主表数据小的情况

• exists()适合子表比主表数据大的情况

13.union 和 union all的区别

union all查询出来的结果，只合并，不去重

union联合查询，合并，而且去重。

14.some all any 的区别

all大于子查询集的最大值
some=any大于子查询集的最小值

15.索引的种类有哪些

单列索引

一个索引只包含单个列，但一个表中可以有多个单列索引。

1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。

3、主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引

替代模糊查询

16.B+Tree 索引的特点

• 遍历全表的速度特别快，不需要查询了子节点之后，再返回父节点进行查询
• 范围查询的时候，只要找到第一个节点就能很快找到后面的节点，也是不需要再返回父节点进行查询

17.B+ Tree索引和hash索引的区别

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;
B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问

18.索引的优点和缺点

优：
协助快速查询、更新数据库表中数据。
缺：
增加了数据库的存储空间
在插入和修改数据时要花费较多的时间（因为索引也要随之变动）。

索引什么时候失效？

• 索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）
• 字符串不加引号，出现隐式转换
• like 条件中前面带%
• 负向查询

19.聚簇索引和非聚簇索引

聚簇索引

概念：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

• 主键索引是聚集索引
• 就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。
  （比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引）。
• 其实就是围绕主键建立的索引 叶子结点存储的是数据的地址

非聚簇索引

概念：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

• 非主键都是非聚集索引
• 如果我们遍历的是非聚簇索引，我们只能获取索引列和主键值

innodb必须要有主键吗？

• 显示主键 primary key
• 隐式主键innodb 存储引擎
  • 优先选择 非空且唯一的字段 作为主键
  • 六个字节的隐藏字段 row_id 1—n

20.联合索引的最左匹配原则

联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的

21.回表查询

先定位主键值，再定位行记录

22.如何查看执行计划

explain 加句子
如:explain select * from emp;

23.索引的建立原则

如何建立
建立什么样的索引
在哪些字段建立索引

• 针对于数据量较大，且查询比较频繁的表建立索引。
• 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。
• 要控制索引的数量，索引越多，维护索引结构的代价越大，会影响增删改的效率。
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含
  NULL值时，它可以更好地确定哪个索引最有效地用于查询。

24.SQL优化方案(DML DQL语句的优化)

1.1插入数据(一次性往数据库表中插入多条记录)

• 批量插入数据
• 手动控制事务

1.2插入数据(一次性大批量插入数据)

• 主键顺序插入

2.主键优化

3.order by优化

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
• 尽量使用覆盖索引。
• 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。

4.group by优化

• 通过索引来提高效率。
• 索引的使用也满足最左前缀法则。

5.limit优化

• 优化思路:
  一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

6.count优化

按照效率排序，count(字段) < count(主键 id) < count(1) ≈ count(*)，尽量使用 count(*)。

7.update优化

25.事务的四大特性

① 原⼦性(Atomicity)：⼀个事务中的所有操作，要么全部执⾏成功，要么全部执⾏失败。
原⼦性是事务最重要的特性，全部执⾏失败并不是不执⾏，⽽是通过逆操作 rollback（回滚）数据。

• 如何保持原子性？如何实现原则性？
  • redo log
  • undo log

② ⼀致性(Consistency)：⼀个事务在执⾏前后数据必须保持⼀种合法的状态，事务总是从⼀个⼀致状态到另⼀个⼀致状态。
③ 隔离性(Isolation)：多个事务并发访问时，事务之间是相互隔离的，⼀个事务不应该被其他事务⼲扰，多个并发事务之间要相互隔离。
④ 持久性(Durability)：事务执⾏完成之后，它所做的所有修改都是永久的（不会丢失）。

数据存储到磁盘中就是持久的，不会丢失的，不会因为电脑重启⽽丢失。

26.脏读 不可重复读 幻读

①脏读：⼀个事务读取到了另⼀个事务修改的数据之后，后⼀个事务⼜进⾏了回滚操作，从⽽导致第⼀个事务读取的数据是错误的。
②不可重复读：在⼀个事务中，两次查询同⼀条数据得到了不同的结果就是不可重复读。在⼀个事务两次查询中间，另⼀个事务把这条数据修改了。
③幻读：当同⼀查询在不同时间产⽣不同的结果，就是事务中的幻读问题。

27.四种隔离级别

1.READ UNCOMMITTED：读未提交，也叫未提交读，该隔离级别的事务可以看到其他事务中未提交的数据。
2. READ COMMITTED：读已提交，也叫提交读，该隔离级别的事务能读取到已经提交事务的数据,因此它不会有脏读问题。
3.REPEATABLE READ：可重复读，是 MySQL 的默认事务隔离级别，它能确保同⼀事务多次查询的结果⼀致。
4.SERIALIZABLE：序列化，事务最高隔离级别，它会强制事务排序，使之不会发⽣冲突，从⽽解决了脏读、不可重复读和幻读问题。（Oracle的默认隔离级别是序列化）

28.MVCC 和 LBCC

• LBCC
  第一种，我既然要保证前后两次读取数据一致，那么我读取数据的时候，锁定我要操作的数据，不允许其他的事务修改就行了。这种方案我们叫做基于锁的并发控制 LockBased Concurrency Control（LBCC）。
  如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他时候修改，那就意味着不支持并发的读写操作，而我们的大多数应用都是读多写少的，这样会极大地影响操作数据的效率。
• MVCC
  所以我们还有另一种解决方案，如果要让一个事务前后两次读取的数据保持一致，那么我们可以在修改数据的时候给它建立一个备份或者叫快照，后面再来读取这个快照就行了。这种方案我们叫做多版本的并发控制 Multi Version Concurrency Control（MVCC）。
  MVCC 的核心思想是： 我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

29.死锁的四个必要条件

1、互斥条件
2、不可剥夺条件
3、请求与保持条件
4、循环等待条件

30.如何避免死锁

预防死锁
避免死锁
检测死锁
解除死锁

31.视图的优点和缺点

• 优点：
  1. 对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
  2. 用户通过简单的查询可以从复杂查询中得到结果。
  3. 维护数据的独立性，试图可从多个表检索数据。
  4. 对于相同的数据可产生不同的视图。
• 缺点：
  性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据

32.存储过程的优点和缺点

• 优点：
  1. 存储过程是预编译过的，执行效率高。
  2. 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
  3. 安全性高，执行存储过程需要有一定权限的用户。
  4. 存储过程可以重复使用，可减少数据库开发人员的工作量。
• 缺点：移植性差

33.存储过程和函数的区别

1. 存储过程用于在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据。
2. 存储过程声明用procedure，函数用function。
3. 存储过程不需要返回类型，函数必须要返回类型。
4. 存储过程可作为独立的pl-sql执行，函数不能作为独立的plsql执行，必须作为表达式的一部分。
5. 存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out以外，还可以使用return返回值。
6. sql语句（DML或SELECT)中不可用调用存储过程，而函数可以。

34.显式游标和隐式游标的区别

• 显式游标是可以由用户显式声明和操作的游标，专用于处理返回SELECT语句返回的多行数据。
• 隐式游标

自动创建 1. dml 2.select into
自动管理 1.无需认为干预 自动声明 自动打开 自动关闭 2. 游标默认名 ‘SQL’

35.序列的作用

序列 (SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值 (类型为数字)。 其主要的用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值

CREATE sequence auto_increment_seq -- 创建 序列 序列名 
START with 1 --从几开始 
INCREMENT by 1 -- 步长 也就是每次增长几个 
nomaxvalue  -- 可以设置最大值 maxvalue 
minvalue 1  -- 最小值 是1 
nocycle   -- 是否自动循环    如果自动循环就是 cycle 
nocache ;-- 缓存数量  可以不开启  nocache

-- 调用序列 
SELECT auto_increment_seq.nextval from dual;-- 下一个序列 
SELECT auto_increment_seq.currval from dual;-- 上一个序列 

36.数据库的冷热备份

冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。
１、 是非常快速的备份方法（只需拷文件）
２、 轻易归档（简单拷贝即可）
３、 轻易恢复到某个时间点上（只需将文件再拷贝回去）
热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法
优点　
1． 可在表空间或数据库文件级备份，备份的时间短。
2． 备份时数据库仍可使用。
3． 可达到秒级恢复（恢复到某一时间点上）。

37.数据库优化方案

1、选取最适用的字段属性
2、使用连接（JOIN）来代替子查询(Sub-Queries)
3、使用联合(UNION)来代替手动创建的临时表
4、使用保持数据库中数据的一致性和完整性
5、锁定表
6、使用外键
7、使用索引
8、优化查询语句

38.数据库的三范式、第四范式、第五范式、BC范式

• 第一范式（1NF）
  确保原子性
• 第二范式（2NF）
  表中的所有列，其数据都必须依赖于主键
• 第三范式（3NF）
  表中每一列数据不能与主键之外的字段有直接关系
• 第四范式（4NF）
  要消除表中的多值依赖关系
• 第五范式（5NF）
  建立在4NF的基础上，进一步消除表中的连接依赖，直到表中的连接依赖都是主键所蕴含的。
• BC范式
  任何主属性不能对其他主键子集存在依赖。