MySQL阶段性知识学习总结（二）

目录

一、不错的mysql学习网址：

二、mysql面试题总结

1. mysql中编码和字符

2. char和varchar有什么区别？

3. 多行函数有哪些

4. where和having的区别

5. mysql其他函数

6. 数值类型decimal(P,D)详解

7. mysql有哪些字段约束

8. #{}与${}占位符

9. 怎么判断是否创建索引

10. 为什么mysql用B+树做索引而不是B-树或者红黑树？

11. 为什么索引快？

12. 使用group by注意事项

13. SQL的执行顺序★★★★★

18. mysql中的事务操作★★★★★

---

一、不错的mysql学习网址：

MySQL视图、索引    ---mysql字典

详解MySQL的常用数据类型_鹤冲天Pro的博客-CSDN博客

二、mysql面试题总结

1. mysql中编码和字符

在 mysql 中，一个中文汉字所占的字节数与编码格式有关：

• 如果是GBK编码，则一个中文汉字占2个字节，英文占1个字节
• 如果是UTF8编码，则一个中文汉字占3个字节，而英文字母占1字节。

比如定义某个字段数据类型为：varchar(32)，表示这个可以存储 32 个字符，此时表示的是字符，所以跟中英文无关，也就是该字段可以存储 32 个中文，或者是 32 个英文，或者是 32 个中文和英文的混搭都行。但如果字符数超过 32 个的话就会报错。

varchar(n)类型最大能存的数据量是0~65535个字节，

latin1编码中，1个字符对应1个字节，n的最大值约是65535/1（max=65532）；
gbk编码中，1个字符对应2个字节，n的最大值约是65535/2（max=32766）；

utf8编码中，1个字符对应3个字节，n的最大值约是65535/3（max=21844）；

2. char和varchar有什么区别？

（1）char 和 varchar 存的数量是不同的，char类型最多能存255个字符，varchar类型最多能存65535个字节；

（2）char(n) 和 varchar(n) 括号中的 n 代表最大可容纳的字符的个数，并不代表字节个数。注意，一个中文和一个英文都是 1 个字符，只不过 mysql 的编码格式不同时，1 个中文和 1 个英文所占用的存储字节不同而已。（虽然在早期的版本中，n 指的是字节数，但已经是非常旧的版本了，估计一般人也用不到）

（3）一个是定长字符串，一个是变长字符串。CHAR(n) 和 VARCHAR(n) 都是表示可存储 n 个字符，但是 char 类型在少于 n 个字符时，会在字符串的右边使用空格来填充以达到 n 个字符。比如：CHAR(4) 和 VARCHAR(4) ，对于 CHAR(4) 表示固定容纳4个字符，当少于4个字符时，会使用空格填充空缺的部分，如果超过4个字符，会自动截断超出部分。例如你存入数据为 ‘ab’ ，实际会存入 'ab ’ （ab后有2个空格）。但是如果我们使用 select 语句来查询 char 类型的字段时，会发现根本就没有自动补空格，这是因为 CHAR 字段在检索输出时，会自动省略右侧的空格。

CHAR 和 VARCHGAR 不同之处在于 MYSQL 数据库处理这个指示器（即 n）的方式：CHAR 把这个大小视为值的大小，在长度不足的情况下就用空格补足。而 VARCHAR 类型只会把 n 作为限制字符串的最大长度，短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。

3. 多行函数有哪些

多行函数也叫聚合函数（聚集函数），常见的多行函数有：

• count( * | 列名 )：

                count(*)统计查询结果中有多少行记录，包含null值在内；

                count(列名)则不统计null值的行数；

• sum(列名)，
• max(列名)，
• min(列名)，
• avg(列名)：表示统计当前这一列中所有值的平均值，即：这一列中所有值的和 / 不是null值的个数。

select count(*) from emp where sal>3000; -- 7条
select count(id) from emp where sal>3000; -- 7条
select count(bonus) from emp where sal>3000; -- 6条
-- 由于奖金bonus中有null值，在统计时，null值直接被丢弃，不参与统计

select avg(sal) from emp; -- 薪资平均值（总薪资/12）
select avg(bonus) from emp; -- 奖金平均值（总薪资/11），有null值

4. where和having的区别

（1）where和having都是用来过滤数据的，where是先过滤再筛选，having是先筛选再过滤（故能使用where则不使用having）；

（2）where子句中不能使用多行函数和列别名，但可以使用表别名；having子句中多行函数、列别名、表别名则都可以使用；

（3）where应放在from子句后，group by子句前；

准备数据：

 测试：

举例二：求出员工表(emp)中哪些职位(job)的最低薪资是大于2000的？其中薪资的字段名为sal。

-- 正确写法
select job,min(sal) from emp group by job having min(sal)>2000;
-- 错误写法！！！
select job,min(sal) from emp where min(sal)>2000 group by job;

总结：group by、where、having三者执行的先后顺序依次为：where > group by > having

5. mysql其他函数

-- 2023-06-30 年月日
select curdate(),current_date from student_info;
-- 15:14:34 时分秒
select curtime(),current_time from student_info;
-- 2023-06-30 15:17:18 年月日时分秒
select current_timestamp,current_timestamp(),sysdate(),now() from student_info;
-- 提取日期中的年、月、日、时、分、秒
select year(now()) 年,month(now()) 月,day(now()) 日,hour(now()) 时,minute(now()) 分,second(now()) 秒 from dual;
-- 将s1、s2等多个字符串合并为一个字符串
select concat(s1,s2...) from dual;
-- 同concat(s1,s2...)函数，但是每个字符串之间要加上x,x是分隔符
select concat_ws(x,s1,s2...) from dual;

-- round函数用法
SELECT ROUND(-1.23); -- -1
SELECT ROUND(-1.58); -- -2
SELECT ROUND(1.58); -- 2
SELECT ROUND(1.298, 1); -- 1.3
SELECT ROUND(1.298, 0); -- 1
SELECT ROUND(23.298, -1); -- 20

6. 数值类型decimal(P,D)详解

• 用来存储精确的小数值；
• P表示有效数的精度，包括整数位和小数位，范围是1~65；
• D表示小数后的位数，范围是0~30，mysql要求D小于等于P；

例如：amount decimal(6,2)表示amount列最多可以存6为数字，小数位数为2位，因此，amount列的范围是从-9999.99到9999.99

7. mysql有哪些字段约束

主键约束；非空约束；唯一约束；外键约束 

8. #{}与${}占位符

• #{}占位符：其实就是JDBC中的问号(?)占位符，在MyBatis底层会将#{}占位符翻译为问号(?)占位符；
• ${}占位符：是为SQL语句中的某一个SQL片段进行占位，将参数传递过来时，直接将参数拼接在${}占位符所在的位置。因为是直接拼接，所以可能会引发SQL注入攻击，故不推荐大量使用。
• 如果在SQL语句中占位符只有一个#{}占位符，{}中名称没有要求，但不能是空的；参数可以直接传递，不用封装；如果SQL语句中的#{}占位符不止一个，参数值需要封装为Map或者POJO对象；但如果SQL语句中哪怕只有一个${}占位符，参数也必须得先封装到Map或者POJO对象中，再进行传递；
• MyBatis底层在执行SQL语句时，使用的就是PreparedStatement对象来传输SQL语句。

9. 怎么判断是否创建索引

创建索引：

• 主键约束默认建立唯一索引；
• 频繁出现在where查询条件的字段；
• 多表联查中与其他表进行on关联的字段，外键关系；
• 单列索引/复合索引的选择？——高并发下倾向于创建复合索引；
• 查询中经常用来排序的字段；
• 查询中经常用来统计或者分组字段。

不创建索引：

• 频繁更新的字段，每次更新都会影响索引树；
• where查询条件中用不到的字段；
• 表记录太少；
• 经常增删改的表：更新了表，索引也得更新才行；
• 注意：如果一张表中重复的记录非常多，为它创建索引就没有太大意义。

延伸：

目前大多数索引都采用BTree树方式构建。

查看索引：show index from student_info(表名); 或者 show indexes from student_info;

10. 为什么mysql用B+树做索引而不是B-树或者红黑树？

第一：B+树只有叶节点存放数据，其余节点用来索引；

第二：B-树每个索引节点都会有Data区域；

        索引存储在磁盘上，因为索引本身也非常大，为了减少对内存的占用而存在磁盘上。那MySQL如何衡量查询效率呢？减少磁盘IO的次数。B+树、B-树的特点都是每层节点数目非常多，层数很少，目的就是为了减少磁盘IO次数，但是B-树的每个节点都有data域(指针)，这无疑增加了节点大小，说白了增加了IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数就会增多，一次IO多耗时），而B+树除了叶子节点外，其他节点并不存储data，节点小，磁盘IO次数就少。这是优点之一。

        另一个优点是：B+树所有的data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能取得全部数据，这样就能进行区间访问，而在数据库中基于区间的访问是非常频繁的。B-树不支持这样的遍历操作。

第三：B+树相对于红黑树的区别：

        平衡二叉树AVL和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据储存的时候，红黑树往往会出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。 

11. 为什么索引快？

• 排序；
• 树形结构，类似二分查找；
• 三层的BTree可以表示上百万的数据，如果上百万条数据，查询只需要三次IO，性能提高是巨大的，如果没有索引每次查找都要发生一次IO，那么总共就需要百万次的IO，显然成本是非常高的。

12. 使用group by注意事项

分组后，select后面的字段必须是：

        （1） 聚合函数；

        （2）分组中出现的字段；

13. SQL的执行顺序★★★★★

18. mysql中的事务操作★★★★★

• MySQL默认的事务级别是可重复读，Oracle的默认级别是读已提交。
• 默认情况下，mysql中每次执行一条SQL语句，都是一个单独的事务；如果需要在一个事务中执行多条SQL语句，那么就需要手动开启事务和手动结束事务。

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

MySQL InnoDB存储引擎默认的事务隔离级别是可重复读（REPEATABLE-READ），可以通过下面命令查看

select @@tx_isolation; -- MySQL 8.0以下(不含8.0)
SELECT @@transaction_isolation; -- MySQL 8.0以上

• START TRANSACTION | BEGIN ：开启事务；
• COMMIT：提交事务；
• ROLLBACK：回滚事务；

MySQL InnoDB存储引擎的可重复读并不能避免幻读，需要应用使用加锁读来保证，这加锁读使用到的机制就是Next-Key Locks。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是读取已提交（READ-COMMITTED），InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

InnoDB存储引擎在分布式事务的情况下一般会用到可串行化隔离级别。

🌈 拓展一下(以下内容摘自《MySQL 技术内幕：InnoDB 存储引擎(第 2 版)》7.7 章)：

InnoDB存储引擎提供了对XA事务的支持，并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对事务的原有ACID要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

例1：可以在数据库连接工具如dataGrip中打开两个窗口，

第一步：先在1窗口中执行如下sql语句；

begin;
delete from student_info where id=120009;

第二步：再去2窗口中select * from student_info;查询一下，发现该条记录仍然还在；

第三步：最后再回到窗口1提交一下事务(commit;)再去2窗口查询时发现没有了。

例2：同例1一样，打开两个窗口，步骤一二同例1中操作，然后执行如下sql，结果发现虽然1窗口的事务还没有提交，但是由于2窗口的事务级别修改为了读未提交(read uncommitted)，所以该条记录看不到了。

-- 修改窗口2中事务的隔离级别为读未提交
set transaction isolation level read uncommitted;
select * from student_info;

例3：不可重复读问题演示 & 解决方案

 解决方案：将窗口1中的事务级别设置为可重复读时，便不会出现上面的问题；

set transaction isolation level repeatable read;

例4：幻读问题演示 & 解决方案

 

• 在可重复读的事务级别下，给事务操作的这张表添加表锁（见上面图片中，如果在窗口1中不是执行第5行，而是执行第6行select * from student_info for update;时，便可以读到第9行记录）。
• 在可重复读的事务级别下，给事务操作的这张表添加 Next-Key Locks（说明：Next-Key Locks 相当于 行锁 + 间隙锁）。
• 将事务隔离级别调整为 SERIALIZABLE（见下图）。

 参考：MySQL事务隔离级别详解_晴天的空间的博客-CSDN博客

 总结：

1）幻读和不可重复读有些相似之处 ，但是不可重复读的重点是修改，幻读的重点在于新增或者删除；

2）数据库中读取数据存在哪些问题（或并发事务带来的问题）及解决方案；

3）不可重复读是指同一事务中两次读取的数据不一致，幻读是指并发场景下读取到的数据是假的；只要存在并发就必然存在幻读，故解决幻读的原理在于加锁机制，即排队。