MySQL常见面试题

目录

1.MySQL常用基础语句

2.MySQL常用函数

 3.MySQL事务的四大特性(ACID)

4.MySQL 支持以下四种隔离级别

5.mysql约束

6.SQL语句的执行顺序

7.MySQL的存储引擎

8.MySQL的索引

9.窗口函数

10.在MySQL中truncate、delete与drop区别？

11.如何优化 SQL 查询性能？

12.用sql计算历史时间到当前时间的平均RFM值

13.MySQL遇到高并发问题,怎么解决

14.MySQL的存储过程

15.SQL实现行转列和列转行

1.MySQL常用基础语句

1.添加表：alter table 表名 add 字段名 类型（长度）；

2.修改类型：alter table 表名 modify 要修改的字段名 类型；

3.修改列名：alter table 表名 change 旧列名 新列名 类型；

4.删除列：alter table 表名 drop 列名；

5.修改表名：rename table 表名 to 新表名；

                   alter table 旧表名 rename to 新表名；

6.插入数据：Insert into 表名 value 数据

7.更新修改数据：Update table set 字段=值 where 条件

8.删除数据：Delete from 表名 where 条件

2.MySQL常用函数

• length(str)函数 :字符串长度(占用的内存空间长度)

• concat(str1,str2,...)函数 :字符串连接

• upper(str)、lower(str)函数 :转大小写

• substr(str,start,len)函数 :从指定位置截取指定个数的字符串[从1开始]

• instr(str,要查找的子串)函数 :判断是否存在 返回 索引值 或者 0[没找到]

• trim(str)函数 : 去除两端空格

• replace(str,子串,另一个字符串)函数 : 字符串替换.

• round(x,保留位数)函数: 四舍五入保留小数位.

• truncate(x,D)函数 : 按照D的值截取小数部分.

 3.MySQL事务的四大特性(ACID)

面试问题: mysql更新三组数据,在事务里面会发生什么

事务的特性ACID：原子性、一致性、隔离性、持久性。

• 原子性：指事务包含的所有操作要么全部成功，要么全部失败回滚。

• 一致性：是指一个事务执行之前和执行之后都必须处于一致性状态。比如在转账业务中，无论事务是否成功，转账人和收款人的总额应该是不变的。

• 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，每个并发事务之间的数据库是独立的；

• 持久性：指一个事务一旦被提交了，对数据库中数据的修改就是永久的，即便系统故障也不会丢失。

4.MySQL 支持以下四种隔离级别

• 读未提交（Read uncommitted）：一个事务可以读取另一个未提交事务的数据，这可能会导致脏读、不可重复读和幻读等问题。

• 读已提交（Read committed）：一个事务只能读取另一个已提交事务的数据，可以避免脏读，但可能会导致不可重复读和幻读。

• 可重复读（Repeatable read）：在一个事务中多次读取同一数据时，结果是一致的，可以避免脏读和不可重复读，但可能会导致幻读。

• 串行化（Serializable）：最高的隔离级别，事务之间完全隔离，通过对数据加锁来实现，可以避免脏读、不可重复读和幻读，但会严重影响并发性能。

查看隔离级别：select @@transaction_isolation; 设置隔离级别：set session transaction isolation level read uncommitted;

5.mysql约束

主键约束: primary key

非空约束: not null

唯一约束: unique

默认值: default

外键: foreign key

6.SQL语句的执行顺序

from --> join on --> where -->group by --> having --> select --> order by --> limit / offset

7.MySQL的存储引擎

如果对事务安全性、并发性能和数据完整性要求较高，InnoDB 是一个不错的选择；如果以读取为主，对事务要求不高，可以考虑 MyISAM 或 Memory；如果需要存储大量历史数据且对空间有要求，可以使用 Archive 存储引擎

8.MySQL的索引

注 : 适当回答几个熟悉的即可

• Hash索引

• B+Tree索引

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

  • 普通索引 , 唯一索引 , 主键索引

• 组合索引

  • 组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机号建立索引，同样的可以建立为普通索引或者是唯一索引,复合索引的使用复合最左原则。

• 全文索引

  • 1.全文索引的关键字是fulltext

  • 2.全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配。

  • 3.用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题

• 空间索引

  • 1.空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

  • 2.MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。

  • 3.创建空间索引的列，必须将其声明为NOT NULL。

9.窗口函数

注 : 较常用的几个

ROW_NUMBER () : 每一行分配一个唯一的、连续的序号

RANK () : 并列排序,跳过重复序号

DENSE_RANK () : 并列排序,不跳过重复序号

lag(数据,n): 返回当前行的前n行的数据值

lead(数据,n): 返回当前行的后n行的数据值

first_value(n): 返回第一个n的值

last_value(n): 返回最后一个n的值

10.在MySQL中truncate、delete与drop区别？

• 不再需要一张表的时候，用drop；

• 删除整个表或数据库，用delete；

• 要快速清空表中的所有数据，保留表结构，用truncate。

11.如何优化 SQL 查询性能？

可以通过合理创建索引、避免全表扫描、优化查询语句结构、减少数据冗余等方式来优化 SQL 查询性能。例如，使用合适的索引可以大大提高查询速度；避免在 WHERE 子句中使用函数操作字段；对大表进行分区等。

12.用sql计算历史时间到当前时间的平均RFM值

最近一次消费 (Recency)、消费频率 (Frequency)、消费金额 (Monetary)

最近一次消费: 对id分组查询最大时间max(data)

消费频率: 对id分组对订单count(*)聚合

消费金额: 对id分组对每个用户每笔订单的消费金额求和sum()

平均RFM值: join连接三张临时表,对求出来的数据进行avg()求平均值

13.MySQL遇到高并发问题,怎么解决

在表设计阶段可以选择合适的存储引擎 , 对sql语句进行优化

14.MySQL的存储过程

MySQL 的存储过程是一组为了完成特定功能而预编译好的 SQL 语句集合，存储在数据库中，可以被反复调用。

例如:连续登录问题, 想要反复调用连续几天登录的用户,就可以使用存储过程定义一个函数n, 在我们需要反复调用用户连续登录多少天的时候就可以根据查询语句最后的结果来调用某个用户连续登录n天的问题

15.SQL实现行转列和列转行

• 列转行

SELECT student_id,
       MAX(CASE WHEN subject = '数学' THEN score END) AS 数学,
       MAX(CASE WHEN subject = '语文' THEN score END) AS 语文
FROM scores
GROUP BY student_id;

• 行转列

SELECT student_id, '数学' AS subject, math_score AS score
FROM students
UNION ALL
SELECT student_id, '语文' AS subject, chinese_score AS score
FROM students;