mysql面试题总结

写出创建用户访问日志表Log的SQL语句，有如下四个列：ip 用户ip地址, 不超过40字符; uid，用户id，不超过20字符；dt，访问时间(年月日)；url, 请求的url，不超过255字符
统计2019-08-09这天，访问次数最多的ip地址及其访问次数，写SQL语句？

select ip,count(*) as count
from logs
where dt = '2018-08-09'
grou by ip
order by count desc 
limit 1

有三个表，一个是学生信息表，一个是平时成绩表，一个是期末成绩表，根据学生学号查出该学生的平时成绩，期末成绩（一个学生会对应多个平时成绩，平时成绩的科目要和期末成绩科目要一致）
student(id);p(s_id,km,sc);q(s_id,km,sc)

select * from student left join  p on student.id = p.s_id left join  q on  p.s_id=q.s_id and p.km=q.km;

表 student(id,name) score(s_id(学生id),score );找出张三，并把张三的成绩改为80分

 update score set score=80 where s_id in (select id from student where name="张三");

表学生id、学生name、科目 k、成绩score ，查出所有科目的成绩都大于60分的学生id和name

select id, name from student group by id having min(score>=60);

表学生id、学生name、成绩score，查找成绩第二大的学生id和name

select id, name from student order by score limit 1,1

单列索引、组合索引、主键索引、唯一索引、全文索引

Create index indexName on atabel (name1(255),name2(255);)

事务（Transaction）

是并发控制的基本单位。所谓的事务，它是一个操作序列，

事务四大特性（ACID）

原子性：是指事务是一个最小单元，不可再分隔，成为一个整体。事务要么全部执行，要么全部不执行，不存在中间状态。
一致性：是指事务中的方法要么同时成功，要么都不成功。比如A向B转账，要不都成功，要不都失败。
隔离性：MySQL数据库中可以同时启动很多的事务，但是，事务和事务之间他们是相互分离的，也就是互不影响的。通过锁来保证。
持久性：即当成功插入一条数据库记录时，数据库必须保证有一条数据永久的写入到数据库磁盘中。

事务的并发

多个事务同时执行

事务并发带来的问题

1、脏写
事务2中修改数据但还没提交
事务1修改了事务2中的数据，
接着事务2提交数据
接着事务1回滚，
导致事务2中的修改被覆盖回滚

2、脏读
事务2中修改数据但还没提交
事务1读取了事务2中的数据并提交，
事务2接着回滚，
导致事务1读取到了不存在的数据。

3、不可重复读（读操作出现的问题）
即重复读会出问题，事务1在同一条件下当前时刻读到数据和下一个时刻读到的数据不一致，即这两个时刻中间有别的事务进行了数据修改

4、幻读（更新时会出现幻读）
即事务1在同一条件下下一时刻读到的数据行数比当前时刻多，仿佛出现幻觉，因为这两个时刻中间有别的事务进行了添加数据
select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

事务隔离级别，每个级别会引发什么问题

参考：https://www.cnblogs.com/wyaokai/p/10921323.html
事务的隔离级别：读未提交、读已提交、可重复读、串行提交
1、读未提交（read-uncommitted）：会出现脏读、脏写、不可重复读、幻读

客户端A和客户端B都将事务级别设置为read-uncommitted
客户端B更新表a数据，但是不提交
客户端A读取表a数据，会读到客户端B更改但未提交的数据
客户端B数据回滚的话，客户端A读到的就是脏数据

2、读已提交：会出现不可重复读、幻读现象

客户端A和客户端B都将事务级别设置为read committed
客户端B更新表a数据，但是不提交
客户端A读取表a数据，不会读到客户端B更改但未提交的数据，解决了脏读的问题
客户端B提交更新
客户端A查到更新后的数据   ，客户端A两次查询结果不一致，产生了不可重复读的问题

3、可重复读：会出现幻读现象，mysql默认支持的事务级别

客户端A和客户端B都将事务级别设置为repeatable read
客户端B更新表a数据，但是不提交
客户端A读取表a数据，不会读到客户端B更改但未提交的数据
客户端B提交更新
客户端A没有查到客户端B更新的数据，客户端A两次查询结果一致，解决了不可重复读的问题
客户端A更新表a数据，

4、串行提交(序列化)：不会出现上述问题
串行提交是表级锁，加锁后其它客户端不可操作

客户端A将事务级别设置为serializable模式
客户端B更改表失败 

mysql默认是哪个级别

可重复读

mysql都有什么锁

1、锁是计算机协调多个进程或线程并发访问某一资源的机制.
2、锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。
其他：
3、mysql锁乐观锁、悲观锁、共享锁、排它锁、行锁、表锁
共享锁（读锁）：其他事务可以读，但不能写。
排他锁（写锁） ：其他事务不能读取，也不能写。
按力度分：表级锁、页面锁、行级锁。
默认情况下有表锁和行锁
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

mysql悲观锁和乐观锁

1、悲观锁，正如其名，它指的是对数据被外界（包括当前系统的其它事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排它性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

还可以简单理解，就是Java中的 Synchronized 关键字。只要对代码加了 Synchronized 关键字，JVM 底层就能保证其线程安全性。
悲观锁的实现，参考 https://www.cnblogs.com/cyhbyw/p/8869855.html

select ..... for update

2、乐观锁认为一般情况下数据不会造成冲突，所以在数据进行提交更新时才会对数据的冲突与否进行检测。如果没有冲突那就OK；如果出现冲突了，则返回错误信息并让用户决定如何去做。
乐观锁在数据库上的实现完全是逻辑的，数据库本身不提供支持，而是需要开发者自己来实现。
常见的做法有两种：版本号控制及时间戳控制。

版本号控制的原理：

为表中加一个 version 字段；
当读取数据时，连同这个 version 字段一起读出；
数据每更新一次就将此值加一；
当提交更新时，判断数据库表中对应记录的当前版本号是否与之前取出来的版本号一致，如果一致则可以直接更新，如果不一致则表示是过期数据需要重试或者做其它操作

至于时间戳控制，其原理和版本号控制差不多，也是在表中添加一个 timestamp 的时间戳字段，然后提交更新时判断数据库中对应记录的当前时间戳是否与之前取出来的时间戳一致，一致就更新，不一致就重试。

select 时怎么加排它锁？

锁冲突和死锁

1、死锁是两个或多个事务/请求之间的锁出现了死循环。
2、锁冲突：有个session持有了某个资源（比如id=1的数据）的写锁，另一个seession正好也要请求同一条数据的写锁，就必须被阻塞，直到前面的session释放锁为止，我一般称之为”锁死“或”长时间锁等待“。

死锁判定原理和具体场景，死锁怎么解决？

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。
那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

mysql常见的三种存储引擎的区别？

1、InnoDB
2、MyISAM
3、MEMORY

MySQL的MyISAM与InnoDB两种存储引擎在事务、锁级别，各自的适用场景？

查询语句不同元素（where、join、limit、group by、having等等）执行先后顺序？

总结下顺序：我(W)哥(G)是(SH)偶(O)像 join where group by limit
W（where）->G（Group）->S（Select）->H（Having）->O（Order）
1、执行where xx对全表数据做筛选，返回第1个结果集。
2.针对第1个结果集使用group by分组，返回第2个结果集。
3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
4.针对第3个结集执行having xx进行筛选，返回第4个结果集。
5.针对第4个结果集排序。
6、limit

什么是临时表，临时表什么时候删除?

临时表与实体表类似，只是在使用过程中，临时表是存储在系统数据库tempdb中。当我们不再使用临时表的时候，临时表会自动删除。

mysql索引

1、索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。
2、MySQL主要有两种结构：Hash索引和B+ Tree索引

建立索引有什么优缺点?

索引的优点：1. 天生排序。2. 快速查找。
索引的缺点：1. 占用空间。2. 降低更新表的速度。
为什么占用空间：因为建立索引会把排序后组织成B树的数据，放到磁盘上。

聚集索引和非聚集索引区别？

像查字典一样
拼音检索就是聚集索引；笔画检索就是非聚集索引
正文内容按照一个特定维度排序存储，这个特定的维度就是聚集索引；
非聚集索引索引项顺序存储，但索引项对应的内容却是随机存储的；

如何检查索引是否生效

SHOW INDEX FROM <表名> [ FROM <数据库名>] 查看表中是否有索引
使用explain函数验证索引是否生效
explain select * from p_user WHERE name=‘B’

何时使用索引

MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”，倒不如说，和全表扫描比起来，去分析两个索引B+树更加耗费时间。所以where A=a and B=b这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。

（1）主键，unique字段；
（2）和其他表做连接的字段需要加索引；
（3）在where里使用＞，≥，＝，＜，≤，is null和between等字段；
（4）使用不以通配符开始的like，where A like 'China%'；
（5）聚集函数MIN()，MAX()中的字段；
（6）order by和group by字段；

何时不使用索引

（1）表记录太少；
（2）数据重复且分布平均的字段（只有很少数据值的列）；
（3）经常插入、删除、修改的表要减少索引；
（4）text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立索引）；
（5）MySQL能估计出全表扫描比使用索引更快时，不使用索引；

索引何时失效

（1）组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；
（2）like未使用最左前缀，where A like '%China'；
（3）搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；
（4）or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
（5）如果列类型是字符串，要使用引号。例如where A='China'，否则索引失效（会进行类型转换）；
（6）在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

mysql B+Tree索引和Hash索引的区别？

sql查询语句确定创建哪种类型的索引？如何优化查询？

非关系型数据库和关系型数据库区别，优势比较？

1、关系型数据库一般都有固定的表结构，非关系型数据库是基于文档的，K-V键值对的存储机制。
2、非关系型数据库比关系型数据库读写速度快
3、非关系型数据库的扩展性好
4、关系型数据库要求一致性，非关系型没有严格要求。

数据库三范式，根据某个场景设计数据表？

数据库的读写分离、主从复制，主从复制分析的 7 个问题？
使用explain优化sql和索引？

MySQL慢查询怎么解决？

查看慢查询日志，查找慢查询原因，看是不是索引问题，索引是否错误。
优化查询语句，避免三表以上join等
是否可以用子查询减少查询次数。
对大量的数据，采用分库分表方式。

什么是 内连接、外连接、交叉连接、笛卡尔积等？

内连接：
select * from a
inner join b
on a.id=b.parend_id
左连接：
select * from a
left join b
on a.id=b.parend_id
右连接
select * from a
right join b
on a.id=b.parend_id

varchar和char的使用场景？

char使用指定长度的固定长度表示字符串的一种字段类型
varchar(M)是一种比char更加灵活的数据类型，同样用于表示字符数据，但是varchar可以保存可变长度的字符串。

mysql 高并发环境解决方案？

数据库崩溃时事务的恢复机制

REDO日志和UNDO日志
REDO 为了重做对数据页（page）更改保存的信息，用于恢复
UNDO 为了撤销对数据记录（tuple）更改保存的信息，用于回滚事务

深耕MySQL - 50道SQL练习题

https://blog.csdn.net/qq_42764468/article/details/122294619

1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（难）
select distinct a.s_id
from ( select * from score where c_id=01) a
join ( select * from score where c_id=02) b
on a.s_score>b.s_score
where a.s_id=b.s_id;

2、查询平均成绩大于60分的学生的学号和平均成绩
(1) 求每个学生的平均成绩
select s_id,avg(s_score) avg_score from score group by s_id;
(2) 使用having对每个学生的平均成绩过滤
select s_id,avg(s_score) avg_score from score group by s_id having avg(s_score)>60;

3、查询所有学生的学号、姓名、选课数、总成绩
(1) 先查询出所有学生的学号，选课数，总成绩
(2) 将步骤1中查询出来的这张表作为一个字表与student表进行连接查询
select a.s_name,b.s_id,b.count,b.sum_score
from student a
left join (select s_id,count(*) count,sum(s_score) sum_score
from score
group by s_id) b
on a.s_id=b.s_id;

简化查询
select a.s_name,b.s_id,count(b.s_id) count,sum(b.s_score) sum_score
from student a
left join score b
on a.s_id=b.s_id
group by s_id;

4、查询姓“猴”的老师的个数
select count(t_name) from teacher where t_name like ‘猴%’;

5、查询没学过“张三”老师课的学生的学号、姓名
select s_id,s_name from student where s_id not in (select a.s_id
from score a
join student b on a.s_id=b.s_id
join course c on a.c_id=c.c_id
join teacher d on c.t_id=d.t_id
where d.t_name=‘张三’);

6、查询学过“张三”老师所教的所有课的同学的学号、姓名
select b.s_id,b.s_name
from score a
join student b on a.s_id=b.s_id
join course c on a.c_id=c.c_id
join teacher d on c.t_id=d.t_id
where d.t_name=‘张三’;
7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
方法一：
select s_id,s_name
from student
where s_id in(select s_id
from score
where c_id=‘01’)
and s_id
in(select s_id
from score
where c_id=‘02’);
方法二：
8、查询课程编号为“02”的总成绩
select sum(s_score) from score group by c_id having c_id=‘02’;

9、查询所有，课程成绩小于60分的学生的学号、姓名
select distinct a.s_id,a.s_name
from student a
left join score b on a.s_id=b.s_id
where b.s_score<60 or a.s_id not in (select s_id from score);

10、查询没有学全所有课的学生的学号、姓名
select a.s_id,a.s_name
from student a
left join score b on a.s_id=b.s_id
group by a.s_id,a.s_name
having count(b.c_id)<(select count(c_id) from course);