面试笔记MySQL

已于 2023-10-23 23:22:59 修改
阅读量91 收藏
点赞数
分类专栏： 笔记 文章标签： 面试 笔记 mysql
于 2021-07-06 23:27:52 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45080791/article/details/118528173
版权

MySQL写入顺序与读取顺序

在这里插入图片描述
在这里插入图片描述

7中JOIN

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
这里MySQL不支持Full Outer Join，我们可以使用union连接（全A+全B）
在这里插入图片描述

在这里插入图片描述
可以使用A独有+B独有

索引

什么是索引

一种帮助数据库高效访问数据的数据结构
排好序的快速查找的数据结构

索引优缺点

优点：加快查询速率
缺点：

  1. 维护索引会耗费数据库资源
  2. 索引占用磁盘空间
  3. 对表进行增删改的时候，因为需要维护索引，会降低性能所以索引不是越多越好

索引分类

1.主键索引

设立为主键后数据库会自动创建索引（Innodb为聚簇索引）

2.单列索引

一个索引只包含单个列，一个表可以有多个单列索引，也叫普通索引

3.唯一索引

索引的列的值必须唯一，但可以是空值，和主键索引的区别：主键索引索引列的值不能为null，而唯一索引可以存在null，但只能存在一个

4.复合索引

多个列组和在一起共同构建的索引

索引的操作

1.查看索引：show index from (表名)

show index from teacher
主键索引创建表时自动创建在这里插入图片描述

普通索引创建

建表后创建 create index index_name on table_name(col)

CREATE index teachername on teacher(tname)
查看一下
show index from teacher
在这里插入图片描述

创建表时创建索引

CREATE table index_text (id VARCHAR(20) PRIMARY KEY, name VARCHAR(20), KEY(name))
show index from index_text

在这里插入图片描述

唯一索引的创建

建表时创建

CREATE table index_text1 (id VARCHAR(20) PRIMARY KEY, name VARCHAR(20), UNIQUE(name))
show index from index_text1

在这里插入图片描述
建表后创建

CREATE UNIQUE index uniqueKey on index_text(name)
show index from index_text

在这里插入图片描述

复合索引创建

建表时创建

create table index_text2(id VARCHAR(20) PRIMARY key,name VARCHAR(20),age INTEGER,key(name,age))
show index from index_text2

在这里插入图片描述
建表后创建

create index aindex on index_text2(id,name,age)
show index from index_text2

在这里插入图片描述
使用复合索引必须基于最左前缀原则：可以用id查，可以id+name，还可以id+name+age，其余情况都不能用
不但使用最左前缀原则，MySQL还会自动调整查询字段以便利用索引：综上，一个字段包含id的，两个字段包含id和name的，三个字段就是都包含的

索引的数据结构

主键索引排了序
innodb默认一页存储16kb
其上还有页目录（也是一个页，但是并不包含数据，只包含指针）
B树

1.所有的键值分布在整棵树中
2.搜索可能在非叶子节点结束（性能逼近二分查找）
3.每棵树最多有m个子树
4.根节点最少有两个子树
5.分支节点至少m/2棵子树（非根非叶子的节点）
6.所有叶子节点都在同一层，每一个节点最6，多有m - 1个key，并且升序排列
实例在这里插入图片描述
比如查找28

  1. 根据根节点找到磁盘1，读入内存（IO）
  2. 比较关键字28在区间（16，34）的范围，找到指针P2
  3. 根据磁盘一的P2找到磁盘三，读入内存（IO）
  4. 比较28所在（27，29）找到磁盘3所在指针P2
  5. 根据磁盘三的P2指针找到磁盘8，读入内存（IO）
  6. 在磁盘8中找到关键字
    缺点1：每个节点都有key，也有data，而每页的存储空间有限，如果data较大的话会导致每个节点存储key数量变小
    缺点2：当存储数据量很大的时候会导致深度很大，也就是有多次IO，影响性能

4.B+树

B+树是在B树上做的优化：

  1. B+树的每个节点可以包含更多的节点：降低了书的高度，将数据范围变为多个区间，区间越多，数据检索越快
  2. 非叶子节点存储key，叶子节点存储key和数据
  3. 叶子节点的两两指针相互连接，顺序查询的性能更高
    支持两种查找：基于主键的范围查找和分页查找或者从根开始随即查找
    在这里插入图片描述

B+树最大的好处是只有叶子节点存储数据，非叶子节点不存储数据，降低了B+树的高度，减少了IO次数（Innodb最顶层根节点为常驻内存）
B树非叶子节点就要春初数据，这样每个非叶子节点存储的指针就要少一些，同样多的数据，B树深度更深,增加磁盘IO次数，影响效率。
区别：

  • B+树非叶子节点只存储键值信息
  • B+树的叶子节点之间存在一个链指针
  • B+数据记录都放在叶子节点中

什么是聚簇索引，什么是非聚簇索引

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。聚簇索引不一定是主键索引，但是主键索引一定是聚簇索引，一张表中只有一个聚簇索引
非聚簇索引（辅助索引）：将数据和索引分开存储，索引结构的叶子节点指向了数据对应位置
在这里插入图片描述

小问题:为什么非聚簇索引要用主键而不是行数据物理位置（偏移量）

因为如果使用地址的话当增删后地址有可能发生改变

Innodb中

  • Innodb中使用主键索引，会将主键构成一棵B+树，并且行数据就存储在叶子节点上，若是使用where id = 14，就会按照B+树的算法直接找到对应行数据
  • 对Name列进行条件搜索，需要两个步骤，1：在B+树中检索Name，到达其叶子节点获取对应主键，之后再根据主键索引B+树中再执行一次B+树检索操作，最终达到叶子节点获取整行数据。（使用其他建想这样操作要创建辅助索引（非聚簇索引））
  • 聚簇索引默认主键，若表中没有定义主键，InnoDB会自动选择唯一且非空的索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引，如果设置了主键所谓聚簇索引又想单独设置聚簇索引，只能先删除主键，之后添加想要的聚簇索引后再恢复主键

MyISAM（不支持事务）

在这里插入图片描述
MyISAM中使用非聚簇索引，非聚簇索引的两颗B+树没什么不同，节点的结构完全一致只是存储的内容不同，主键B+树存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的位置。这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，两个键没有区别，和InnoDB的区别就是索引树是独立的，通过辅助键检索也不用访问主键的索引树

Innodb聚簇索引优势

  1. 由于行数据和聚簇索引的叶子节点存储在一起，同一页中有多条行数据，访问同一数据页不同行记录时，已经将页加载到了缓存器中，再次访问时会在内存中完成访问，不必IO=》找到了叶子节点就可以直接返回数据，（使用主键更快）
  2. 辅助索引的叶子节点，存储主键值而不是数据地址值，好处时当行数据进行增删等，索引树节点也需要分裂变化；当我们要炸找的数据在上一次IO没有，需要发生一个新的IO时，可以避免对辅助索引的维护工作，只要维护聚簇索引树（主键那个）即可。另一个好处：辅助索引树叶子节点存放的主键值，减少了辅助索引占用的存储空间

使用注意

最好不用UUID=》不适合排序
建议使用自增（对索引树结构影响小）

为什么自增id

只要索引相邻，那么数据也是相邻的页，同时碎片少

什么没法用索引

1.使用Like关键字（%在右面能用到，但是在左边不行）
2.使用多列索引（复合索引）=》必须使用了第一个字段
3.使用or关键字（如果or前后都有索引，那么会使用索引，但是如果有一个不是，就不能用到索引）

在这里插入图片描述
也会失效

哪些情况建立索引

  1. 自动建立唯一索引
  2. 频繁查找的字段应该建立索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 高并发下倾向创建组合索引
  5. 排序了的字段
  6. 统计或者分组字段

哪些情况不需要建立

  1. 数据少
  2. 经常增删改的表
  3. 很多重复的字段没必要建立

优化

explain：简称执行计划

事务

事务的ACID

A：原子性

事务开始后的所有操作，要么全都做完，要么全都不做，不可能停滞在中间环节，当事务执行过程中报错，事务会回滚到开始前的状态。

C：一致性

事务开始和结束后，数据库的完整性约束并没有被破坏。比如表中唯一字段的某个值，在事务回滚后变为非唯一字段，就违背了一致性

I：隔离性

同一时间，只允许一个事务请求统一数据，不同事物之间彼此没有干扰

D：持久性

事务完成后，事务对数据库的所有更新将会保存到数据库中，不能回滚

MySQL事务隔离级别

在这里插入图片描述
脏读：事务A读取了事务B更新的数据，然后B回滚了，那么A读到的就是脏数据
不可重复读：事务A多次读取同一数据，事务B在事务A多次读取数据过程中，对数据进行了修改并且提交，导致A多次读取同一数据不一致
幻读：A用户使用将数据进行字段修改，此时B用户插入一条原有字段的数据，那么A改后就会发现有一条数据没有动
解决不可重复读的问题只需要锁住满足条件的行，解决幻读要锁住整个表

常见SQL题

1.用一条SQL语句查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)
B：select name from table group by name having min(fenshu)>80

  1. 学生表 如下:
    自动编号 学号 姓名 课程编号 课程名称 分数
    1 2005001 张三 0001 数学 69
    2 2005002 李四 0001 数学 89
    3 2005001 张三 0001 数学 69
    删除除了自动编号不同, 其他都相同的学生冗余信息

A: delete tablename where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

3.一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合.

答：select a.name, b.name
from team a, team b
where a.name < b.name

4.面试题：怎么把这样一个
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

答案
select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year

5.说明：复制表(只复制结构,源表名：a新表名：b)

SQL: select * into b from a where 1<>1 (where1=1，拷贝表结构和数据内容)
ORACLE:create table b
As
Select * from a where 1=2

[<>（不等于）(SQL Server Compact)
比较两个表达式。 当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为 TRUE。 否则，结果为 FALSE。]

原表:
courseid coursename score
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80

为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark

1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass

写出此查询语句
select courseid, coursename ,score ,if(score>=60, “pass”,“fail”) as mark from course
7.表名：购物信息
购物人 商品名称 数量
A 甲 2
B 乙 4
C 丙 1
A 丁 2
B 丙 5
……

给出所有购入商品为两种或两种以上的购物人记录

答：select * from 购物信息 where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >= 2);
8.
info 表
date result
2005-05-09 win
2005-05-09 lose
2005-05-09 lose
2005-05-09 lose
2005-05-10 win
2005-05-10 lose
2005-05-10 lose
如果要生成下列结果, 该如何写sql语句?
　　 win lose
2005-05-09 2 2
2005-05-10 1 2
答案：
(1) select date, sum(case when result = “win” then 1 else 0 end) as “win”, sum(case when result = “lose” then 1 else 0 end) as “lose” from info group by date;
(2) select a.date, a.result as win, b.result as lose
　　from
　　(select date, count(result) as result from info where result = “win” group by date) as a
　　join
　　(select date, count(result) as result from info where result = “lose” group by date) as b
　　on a.date = b.date;

找出所有科目成绩都大于某一学科平均水平的学生

在这里插入图片描述

用户每个月的累计访问次数

在这里插入图片描述

计算店铺访问数

在这里插入图片描述

计算订单相关(having)

已知一个表 STG.ORDER，有如下字段:Date，Order_id，User_id，amount。请给出 sql 进
行统计:数据样例:2017-01-01,10029028,1000003251,33.57。

  1. 给出 2017 年每个月的订单数、用户数、总成交金额。
  2. 给出2017年11月的新客户数
    也就是指在11月才有第一笔订单
    （最小的订单时间是2017-11）

求所有用户和活跃用户的总数以及平均年龄（活跃用户指连续两天都有访问记录的用户）

日期、用户、年龄
2019-02-11,test_1,23
2019-02-11,test_2,19
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-12,test_2,19
2019-02-13,test_1,23
2019-02-15,test_2,19
2019-02-16,test_2,19
桂圆桂圆车厘子
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL最全整理（面试题+笔记+导图），面试大厂不再被MySql难倒
2401_84435000的博客
04-21 910
网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**
MySQL面试笔记
Yanpr919的博客
02-17 182
目录 MySQL数据类型 脏读、不可重复读与幻读 MySQL数据隔离级别 SQL语言定义了三种操作数据库的能力 MySQL数据类型 MySQL数据库的数据类型主要是分为数值型，日期时间型以及字符串型这三个大类，具体如下图所示： MySQL 的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数，MySQL 允许我们指定数值字段中的值是否有正负之分或者用零填补。 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。 每个时间
Java面试题之 mysql面试题
冯安晨
09-22 2525
Java面试题之 mysql面试题
Mysql学习笔记（全）
热门推荐
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
MySQL学习笔记-面试15问
q66562636的博客
04-22 190
1. 数据库三大范式是什么？ 第一范式：每个列都不可以再拆分。 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。 事实上我们经常会为了性能而妥协数据库的设计。 2. mysql有关权限的表都有哪几个? MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_ins
MySQL笔记，面试复习
02-01
MySQL 面试学习笔记，是我个人整理的一份涵盖架构、索引、锁等多个方面的资料。 适合人群： 1. 初学者：这份笔记提供了系统而深入的 MySQL 学习路径，从数据库架构到索引、锁等关键概念，让你能够建立起对 MySQL ...
MySQL笔记、面试题
最新发布
06-17
以下是一些关于MySQL的重要知识点，这些内容可能来自于提供的“MySQL笔记、面试题”文本文件。 1. 数据库基础概念： - 数据库：存储和管理数据的系统，如MySQL。 - RDBMS：关系型数据库管理系统，基于表格的数据...
MySQL零基础小白学习及面试学习笔记.rar
08-01
MySQL零基础小白学习及面试学习笔记是个人在学习过程中整理和梳理的笔记。 其中包含邮件备份demo案例。 基础知识点笔记涵盖：安装、基础原理、存储引擎、索引事务锁、触发器等等面试相关使用到的知识点 实战专栏包含...
mysql面试题，mysql文档教程
03-12
MySQL面试题与数据库设计知识点总结 MySQL 面试题中，MySQL 文档教程是非常重要的一部分。以下是对 MySQL 面试题的详细知识点总结： 1. SQL 介绍 * SQL 概述：SQL 是 Structured Query Language 的缩写，用来和...
mysql 学习笔记，可用于面试和复习
02-14
这份“MySQL学习笔记”是为准备求职面试和复习基础知识而精心整理的，涵盖了从基础概念到高级特性的全面内容。 一、MySQL基础 1. 数据库与表：数据库是组织数据的集合，表则是数据库中的数据结构，包含列和行。在...
Scala
qq_45080791的博客
04-07 912
Scala配置环境 配置环境 配置$SCALA_HOME 在配置PATH IDEA自带的下载后 remark一下，之后创建类即可 object hello { def main(args: Array[String]): Unit = { println("Hello World") } }
Hive(一)
qq_45080791的博客
02-03 826
HiveHive的基本概念什么是HiveHive的优缺点优点缺点Hive的架构原理 Hive的基本概念 什么是Hive Hive：由Facebook开源用于解决海量结构化日志的数据统计 Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类sql查询功能 本质就是将HQL转化为MapReduce程序 Hive处理的数据存储在HDFS Hive分析数据底层实现是MapReduce 执行程序运行在Yarn上 Hive的优缺点 优点 操作接口采用类SQL语法，提供快速开发的
Flume
qq_45080791的博客
03-08 518
Flume概述定义Flume的优点Flume组成架构（重点）AgentSource：ChannelSinkEventFlume拓扑序列内部原理Flume Agent内部原理（重点）安装案例监控端口数据官方案例实时读取本地文件到HDFS 概述 定义 Flume是Cloudera提供的一个高可用、高可靠的分布式海量日志采集、聚合、传输系统，基于流式框架，灵活简单 流式框架：处理数据的单位很小 MapReduce是先获取整个文件再处理，是非流式的 Spark也不是流失构架 Flume的优点 可以和任意存储
Spark
qq_45080791的博客
04-23 417
用Spark来WordCount 简便写法和复杂点的 scala> sc.textFile("input").flatMap(s=>s.split(" ")).map(s=>(s,1)).reduceByKey(_+_).collect res6: Array[(String, Int)] = Array((Java,1), (hello,3), (Scala,1), (world,1), (spark,1), (hadoop,1), (Hi,2)) scala> sc.textF
笔记：JVM
qq_45080791的博客
11-10 407
JVM 类似面试题： 谈谈你对JVM理解，java8虚拟机有什么更新 什么是OOM，什么是StackOverFlowError，有哪些分析方法 JVM的常用参数调优你知道哪些 谈谈JVM中，对类加载器的认识 JVM体系结构概述 堆体系结构概述 堆参数调优入门 ...
Shell脚本
qq_45080791的博客
11-17 398
Shell解析器 Linux的Shell解析器有 [yyx@hadoop04 ~]$ cat /etc/shells /bin/sh /bin/bash /usr/bin/sh /usr/bin/bash /bin/tcsh /bin/csh CentOS默认的解析器为bash 创建一个脚本实例 要求：输出HelloWord 首先，创建一个.sh文件 touch helloword.sh 其次，vim编辑文件内容 #!/bin/bash echo "hello word"
SQL拼接、注入、处理blob类型问题
qq_45080791的博客
09-27 395
添加数据到数据库(JDBC) 需求：键盘输入teacher信息，并添加到数据库 public class TestProblem1 { public static void main(String[] args) throws ClassNotFoundException, SQLException { // 注册驱动 Class.forName("com.mysql.jdbc.Driver"); // 连接MySQL String u
SparkSQL
qq_45080791的博客
05-04 385
SparkSQLSparkSQL概述什么是SparkSQL特点DataFrameDataSetSparkSQL编程SparkSessionDataFrame创建从Spark数据源进行创建1.我们先创建一个json文件，并上传到linux2.spark.read3.展示结果从RDD转换、HiveTable返回SQL风格语法DSL风格语法RDD转换为DataFrame SparkSQL概述 什么是SparkSQL SparkSQL是Spark用来结构化数据的一个模块，它提供了两个编程抽象：DataFrame、D
SparkStreaming
qq_45080791的博客
05-05 369
流程： 存在接收器概念，即： 接收器是不能停的（Driver也一样），否则数据接收不到 用SparkStreaming来写一个接近实时的WordCount 需求：使用netcat工具向9999端口不断的发送数据，通过SparkStreaming读取端口数据并统计不同单词出现的次数 1.导入依赖： <dependency> <groupId>org.apache.spark</groupId> <artifactId>spark-stream
数据库面试专题及答案.pdf
08-27
数据库面试专题及答案.pdf

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值