mysql面试题

1.数据库三范式

2.事务四大特性

3.MySQL的事务隔离级别

4.MySQL数据库的四类索引

5.delete、drop、truncate区别

6.sql语句分类

7.有哪些数据库优化方面的经验

8.mysql中varchar与char的区别以及varchar(50)中的50代表的涵义

9.表中有大字段X（例如：text类型），且字段X不会经常更新，以读为为主，将该字段拆成子表好处是什么

10… Mysql常见函数

11.为什么要尽量设定一个主键

12.主键使用自增ID还是UUID

13.如果要存储用户的密码散列,应该使用什么字段进行存储

14说一说你能想到的sql语句优化，至少五种

15.说一说你能想到的表结构优化，至少五种

16.说几个你除了增删改查之外在mysql中常用的命令答

17.说几个除了增删改查之外常用的关键字

18.varchar(20)和int(20)中的20含义一样吗

19.在什么时候你会选择使用外键，为什么

20.常用SQL语句

21、sql执行顺序

)

##1.数据库三范式
式：对属性的原子性约束，要求字段具有原子性，不可再分解；
第二范式：在满足第一范式的前提下，非主键字段不能出现部分依赖主键；解决：消除复合主键就可避免出现部分以来，可增加单列关键字。
第三范式：在满足第二范式的前提下，非主键字段不能出现传递依赖，比如某个字段a依赖于主键，而一些字段依赖字段a，这就是传递依赖。解决：将一个实体信息的数据放在一个表内实现。

## 2.事务四大特性
(1).原子性：不可分割的操作单元，事务中所有操作，要么全部成功；要么撤回到执行事务之前的状态
(2).一致性：如果在执行事务之前数据库是一致的，那么在执行事务之后数据库也还是一致的；
(3).隔离性：事务操作之间彼此独立和透明互不影响。事务独立运行。这通常使用锁来实现。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
(4).持久性：事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复

## 3.MySQL的事务隔离级别
(1).未提交读(Read Uncommitted)：允许脏读，其他事务只要修改了数据，即使未提交，本事务也能看到修改后的数据值。也就是可能读取到其他会话中未提交事务修改的数据
(2).提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。
(3).可重复读(Repeated Read)：可重复读。无论其他事务是否修改并提交了数据，在这个事务中看到的数据值始终不受其他事务影响。
(4).串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞
MySQL数据库(InnoDB引擎)默认使用可重复读（ Repeatable read)

## 4.MySQL数据库的四类索引:

(1)index ---- 普通索引,数据可以重复，没有任何限制。(即针对数据库表创建索引)
　　(2)unique ---- 唯一索引,要求索引列的值必须唯一，但允许有空值；如果是组合索引，那么列值的组合必须唯一。
　　(3)primary key ---- 主键索引,是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的同时创建主键索引。
　　(4)组合索引 ---- 在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
(5)fulltext ---- 全文索引(基本不使用),是对于大表的文本域：char，varchar，text列才能创建全文索引，主要用于查找文本中的关键字，并不是直接与索引中的值进行比较。fulltext更像是一个搜索引擎，配合match against操作使用，而不是一般的where语句加like。
　　注:全文索引目前只有MyISAM存储引擎支持全文索引，InnoDB引擎5.6以下版本还不支持全文索引
　　索引可以提高查询的速度，但是创建和维护索引需要耗费时间，同时也会影响插入的速度，如果需要插入大量的数据时，最好是先删除索引，插入数据后再建立索引。
　　索引生效条件
　　　假设index（a,b,c）
                      最左前缀匹配：模糊查询时，使用%匹配时：’a%‘会使用索引，’%a‘不会使用索引
                      条件中有or，索引不会生效
                      a and c，a生效，c不生效
                      b and c，都不生效
                      a and b > 5 and c,a和b生效，c不生效。

##5.delete、drop、truncate区别
truncate 和 delete只删除数据，不删除表结构 ,drop删除表结构，并且释放所占的空间。
删除数据的速度，drop> truncate > delete
delete属于DML语言，需要事务管理，commit之后才能生效。drop和truncate属于DDL语言，操作立刻生效，不可回滚。
使用场合：
当你不再需要该表时， 用 drop;
当你仍要保留该表，但要删除所有记录时， 用 truncate;
当你要删除部分记录时（always with a where clause), 用 delete.

##6.sql语句分类：
DDL：数据定义语言（create drop）
DML：数据操作语句（insert update delete）
DQL：数据查询语句（select ）
DCL：数据控制语句，进行授权和权限回收（grant revoke）
TPL：数据事务语句（commit collback savapoint）

##7.有哪些数据库优化方面的经验?
定位:查找、定位慢查询
在项目自验项目转测试之前,在启动mysal数据库时开启慢查询,并且把执行慢的语句写到日志中,在运行一定时间后,通过查看日志找到慢查询语句.使用explain慢查询语句,来详细分析语句的问题.

优化手段：
创建索引:创建合适的索引,我们就可以现在索引中查询,查询到以后直接找对应的记录.
分表:当一张表的数据比较多或者一张表的某些字段的值比较多并且很少使用时,采用水平分表和垂直分表来t化
读写分高:当一台服务器不能满足需求时,采用读写分离的方式进行集群
缓存:使用redis来进行缓存
一些常用优化技巧

##8.mysql中varchar与char的区别以及varchar(50)中的50代表的涵义？
(1).varchar与char的区别: char是一种固定长度的类型，varchar则是一种可变长度的类型.
(2).varchar(50)中50的涵义 : 最多存放50个字节
(3).int（20）中20的涵义: int(M)中的M indicates the maximum display width (最大显示宽度)for integer types. The maximum legal display width is 255.

##9.表中有大字段X（例如：text类型），且字段X不会经常更新，以读为为主，将该字段拆成子表好处是什么？
如果字段里面有大字段（text,blob)类型的，而且这些字段的访问并不多，这时候放在一起就变成缺点了。 MYSQL数据库的记录存储是按行存储的，数据块大小又是固定的（16K），每条记录越小，相同的块存储的记录就越多。此时应该把大字段拆走，这样应付大部分小字段的查询时，就能提高效率。当需要查询大字段时，此时的关联查询是不可避免的，但也是值得的。拆分开后，对字段的UPDAE就要UPDATE多个表了

##10… Mysql常见函数？
聚合函数：
　　AVG(col)返回指定列的平均值
　　COUNT(col)返回指定列中非NULL值的个数
　　MIN(col)返回指定列的最小值
　　MAX(col)返回指定列的最大值
　　SUM(col)返回指定列的所有值之和
　　GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
数学函数：
　　ABS(x) 返回x的绝对值
　　BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
　　
##11.为什么要尽量设定一个主键?
主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的ID列作为主键.设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全.

##12.主键使用自增ID还是UUID?
推荐使用自增ID,不要使用UUID.
因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降.
总之,在数据量大一些的情况下,用自增主键性能会好一些.

##13.如果要存储用户的密码散列,应该使用什么字段进行存储?
密码散列,盐,用户身份证号等固定长度的字符串应该使用char而不是varchar来存储,这样可以节省空间且提高检索效率.

##14说一说你能想到的sql语句优化，至少五种
(1).避免select *，将需要查找的字段列出来；
(2).使用连接（join）来代替子查询；
(3).拆分大的delete或insert语句；
(4).使用limit对查询结果的记录进行限定；
(5).用 exists 代替 in 是一个好的选择；
(6).用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤；
(7).不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引尽量避免在where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；
(8).尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描；
(9).尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

##15.说一说你能想到的表结构优化，至少五种
(1).为每张表设置一个ID ；
(2).选择正确的存储引擎 ;
(3).使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob；
(4).使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数；
(5).使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar；
(6).尽可能使用not null定义字段(给空字段设置默认值)；
(7).尽量少用text;
(8).给频繁使用和查询的字段建立合适的索引；

##16.说几个你除了增删改查之外在mysql中常用的命令答：
Explain:查看该SQL语句有没有使用上了索引，有没有做全表扫描
describe:查看表结构的
show:查看含义
truncate:当你仍要保留该表，但要删除所有记录时

##17.说几个除了增删改查之外常用的关键字
distinct:去重复(select distinct name from user;)
limit:分页(SELECT * FROM orange LIMIT 10,15; // 检索记录11-25)
order by:排序查询、asc升序、desc降序
group by:分组查询、having
union:是取唯一值，记录没有重复
union all:取到得是所有值，记录可能有重复

##18.varchar(20)和int(20)中的20含义一样吗
答：不一样，前者表示最多存放20个字符，后者表示最多显示20个字符，但是存储空间还是占4字节存储，存储范围不变；

##19.在什么时候你会选择使用外键，为什么
答：在我的业务逻辑非常简单，业务一旦确定不会轻易更改，表结构简单，业务量小的时候我会选择使用外键。因为当不符合以上条件的时候，外键会影响业务的扩展和修改，当数据量庞大时，会严重影响增删改查的效率。

##20.常用SQL语句
创建用户create user ‘用户名’@‘%’ identified by ‘密码’ （%处填%表示远程连接 localhost表示只能在本机登陆 也可以填ip ）
赋予最高权限grant all on . to ‘user’@‘%’；
使更改立即生效 flush privileges；
查看当前用户 select user() ;
查看当前数据库 select database() ;
创建数据库 create database [if not exist] 数据库名
查看有哪些数据库 show databases；
删除数据库 drop database [if exists] 数据库名
创建表 create table [if not exists] 表名
查看有哪些表 show tables
查看表结构 desc 表名
show create table 表名
删除表 drop table 表名
表中数据的增删改查
*增 insert into 表名 (id字段名) vaue();
*删delete from 表名 where 条件
*改update 表名 set id = value where 条件;
查询指定条件的内容　　select * from 表名 where 条件
模糊查询 　 　　　　 　select * from student where stu_name like ‘y%’ (
查询时排序　　　 　　 select * from student order by stu_id asc（desc倒序，asc正序）
分组查询　　　　　　 select dep_id as 学院代码,count(dep_id) as 人数 from student group by dep_id;
聚合函数　　　　　 　select MAX(stu_age) from student 　　常见函数（MAX()最大值MIN()最小值SUM()求和AVG()平均Count()统计ROUND()四舍五入）
增加条件 　　　　　　select dep_id as 学院代码,count(dep_id) as 人数 from student group by dep_id having 人数 = 1;

##21、sql执行顺序
(1)from
(3) join
(2) on
(4) where
(5)group by(开始使用select中的别名，后面的语句中都可以使用)
(6) avg,sum…
(7)having
(8) select
(9) distinct
(10) order by

67, Explain语句结果中各个字段分表示什么

Column	含义
id	查询序号
select_type	查询类型
table	表名
partitions	匹配的分区
type	join类型
prossible_keys	可能会选择的索引
key	实际选择的索引
key_len	索引的长度
ref	与索引作比较的列
rows	要检索的行数(估算值)
filtered	查询条件过滤的行数的百分比
Extra	额外信息

1. id SQL查询中的序列号。 id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行

68、索引覆盖是什么
69、最左前缀原则是什么
70, Innodb是如何实现事务的
71、B树和B+树的区别,为什么Mysql使用B+树
72、Mysql锁有哪些,如何理解
73、 Mysq慢查询该如何优化?