MySQL 相关知识笔记

一、字符编码

MySQL 默认的编码是 utf8，仅支持三个字节的存储；utf8mb4 支持四个字节的存储。

二、数据库操作

1. 查询

   • 查询所有数据库：SHOW DATABASES;
   • 查询当前数据库：SELECT DATABASE();

2. 创建

   • CREATE DATABASE [IF NOT EXISTS] 数据库名 (DEFAULT CHARSET 字符集) [COLLATE 排序规则];

3. 删除

   • DROP DATABASE [IF EXISTS] 数据库名;

4. 使用

   • USE 数据库名;

三、表操作

1. 查询

   • 查询当前数据库所有表：SHOW TABLES;
   • 查询表结构：DESC 表名;
   • 查询指定表的建表语句：SHOW CREATE TABLE 表名;

2. 创建

   • CREATE TABLE 表名 ( 字段1 字段1类型 [COMMENT 字段1注释], 字段2 字段2类型 [COMMENT 字段2注释], 字段3 字段3类型 [COMMENT 字段3注释], 字段n 字段n类型 [COMMENT 字段n注释] ) [COMMENT 表注释];
   • 注意：若有多个字段，需在每个字段后面添加 “，”，但最后一个字段后面没有 “，”。Java 中的字符串用 String，MySQL 中的字符串用 varchar (50)。
   • MySQL 中的数据类型包括数值类型、引用类型和日期时间类型。

3. 修改

   • 添加字段：ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
   • 修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
   • 修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
   • 删除字段：ALTER TABLE 表名 DROP 字段名;

4. 删除

   • 删除表：DROP TABLE [IF EXISTS] 表名;
   • 删除指定表，并重新创建该表：TRUNCATE TABLE 表名;

四、聚合函数

MySQL 聚合函数将一列数据作为一个整体进行纵向计算

常见聚合函数：

1. 统计数量：count
2. 最大值：max
3. 最小值：min
4. 平均值：avg
5. 求和：sum

语法：

SELECT 聚合函数(字段列表) FROM 表名;

注意：

使用聚合函数时，所有的 null 值不参与运算。例如，select count(*) from emp;可以查询到所有的数量，而select count(idcard) from emp;若 idcard 中有 null 值则会缺少，因为 null 不参与计算。

五、分组查询

语法：SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

where 和 having 的区别：

1. 执行时机不同：where 是分组之前进行过滤，不满足 where 条件的不参与分组；而 having 是分组之后对结果进行过滤。
2. 判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

注意：

1. 执行顺序：where > 聚合函数 > having。
2. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义。

六、排序查询

语法：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：

1. ASC：升序（默认值）。
2. DESC：降序。

注意：

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

七、函数

函数是一段可以直接被另一段程序调用的程序或代码。

八、约束

约束作用于表中字段上的规则，用于限制存储在表中的数据，目的是保证数据库中数据的正确、有效性和完整性。

约束分类如下：

1. 非空约束：限制该字段的数据不能为 null，关键字为NOT NULL。
2. 唯一约束：保证该字段的所有数据都是唯一、不重复的，关键字为UNIQUE。
3. 主键约束：主键是一行数据的唯一标识，要求非空且唯一，关键字为PRIMARY KEY。
4. 默认约束：保存数据时，如果未指定该字段的值，则采用默认值，关键字为DEFAULT。
5. 检查约束（8.0.16 版本之后）：保证字段值满足某一个条件，关键字为CHECK。
6. 外键约束：用来让两张表的数据之间建立连接，保证数据的一致性和完整性，关键字为FOREIGN KEY。

注意：

1. 约束是作用于表中字段上的，可以在创建表 / 修改表的时候添加约束。
2. 多个约束之间使用空格分开。

约束演示：

create table user(
    id int primary key auto_increment comment'主键',
    name varchar(10) not null unique comment'姓名',
    age int check(age > 0 && age <= 120) comment'年龄',
    status char(1) default '1' comment'状态',
    gender char(1) comment'性别'
) comment'用户表';

外键约束：

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

• 删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除 / 更新行为：

九、索引

索引是帮助 MySQL 高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引向（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

1. 索引的优点：

   • 提高数据检索的效率，降低数据库的 IO 成本。
   • 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗。

2. 索引的缺点：

   • 索引列也是要占用空间的。
   • 索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行 INSERT、UPDATE、DELETE 时，效率降低。

3. 索引的结构：

   • MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，如：
     • B + Tree 索引：最常见的索引类型，大部分引擎都支持 B + 树索引。
     • Hash 索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
     • R - tree（空间索引）：空间索引是 MylSAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
     • Full - text（全文索引）：是一种通过建立倒排索引快速匹配文档的方式，类似于 Lucene、Solr、ES。

B - Tree（多路平衡查找树）：

以一颗最大度数（max - degree）为 5（5 阶）的 b - tree 为例（每个节点最多存储 4 个 key，5 个指针）：

数据：10, 15, 18, 20, 23, 25, 28, 30, 34, 56, 58, 62, 64, 78, 88, 89, 92, 96, 98
指针：3, 4, 14, 16, 17, 19

知识小贴士：

树的度数指的是一个节点的子节点个数。

B + Tree：

以一颗最大度数（max - degree）为 4（4 阶）的 b + tree 为例：

数据：38, 55, 58, 67, 90, 94
指针：6, 12, 16, 18, 29, 34, 38, 45, 55, 56, 58, 62, 87, 90, 92, 94, 98

注：

1. B + 树的所有节点都会出现在叶子节点上，上面的节点只起到一个索引的作用。
2. B + 树的叶子节点之间有一个单项链表。
3. MySQL 中的 B + 树是 B + 树的优化版。
4. MySQL 索引数据结构对经典的 B + Tree 进行了优化。在原 B + Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B + Tree，提高区间访问的性能。

Hash索引：

哈希索引是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中。

注：

如果两个（或多个）键值，映射到同一个槽位上，它们就产生了 hash 冲突（hash 碰撞），可以通过链表来解决。

Hash 索引的特点：

1. Hash 索引只能用于对等比较（=，in），不支持范围查询。
2. 无法利用索引完成排序操作（计算的 hash 值是无序的）。
3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B + tree 索引。

为什么 InnoDB 存储引擎选择使用 B + tree 索引结构？

答：相对于二叉树，层级更少，搜索效率高；对于 B - tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；相较 Hash 索引，B + tree 支持范围匹配及排序操作。

索引分类：

在 InnoDB 存储引擎中，根据索引的存储形式，可以分为以下两种：

聚集索引的选取规则：

1. 如果存在主键，主键索引就是聚集索引。
2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
3. 如果表没有主键，或没有合适的唯一索引，InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

思考：

以下 SQL 语句，哪个执行效率高？为什么？

select * from user where id = 10；
select * from user where name = 'Arm'；

id 为主键，name 字段创建的有索引。

答：select * from user where id = 10；更快。

原因：

select * from user where id = 10；`这个语句中根据 id = 10 可以有以下步骤：
直接找到 id = 10 的整行数据。
 
`select * from user where name = 'Arm'；`这个语句根据 name = 'Arm' 有以下步骤：
先找到 Arm 的 id，然后再根据 id 找到整行数据，涉及到回表，因此效率不高。

索引的语法：

1. 创建索引：CREATE [UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,...);
2. 查看索引：SHOW INDEX FROM table_name ;
3. 删除索引：DROP INDEX index_name ON table_name;

十、实际操作示例

1. 为 name 字段（该字段的值可能会重复）创建索引：CREATE INDEX idx_user_name ON tb_user(name);
2. 为 phone 手机号字段（该字段的值是非空且唯一的）创建唯一索引：CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
3. 为 profession、age、status 创建联合索引：CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, status);
4. 为 email 建立合适的索引来提升查询效率：CREATE INDEX idx_email ON tb_user(email);

十一、其他命令

1. 查看服务器状态信息的命令：
   
   show [session|global] status like 'Com_______';（注：该命令可查询执行频次，一个下划线代表一个字符）
2. 慢查询：
   • 查看慢查询的开关开启情况：show variables like ’slow_query_log'
   • 在 MySQL 的配置文件（/etc/my.cnf）中配置：
     • #开启 MySQL 慢日志查询开关
     • slow_query_log = 1
     • 设置慢日志的时间为 2 秒，SQL 语句执行时间超过 2 秒，就会视为慢查询，记录慢查询日志
     • long_query_time = 2
   • 配置结束后重启 MySQL 服务器：systemctl restart mysqld
   • 日志文件存放路径：cd /var/lib/mysql
   • 日志文件：localhost - slow.log
3. 关于 profile：
   • SELECT @@have_profiling; #查看该数据库是否支持
   • SET profiling = 1； #profiling 默认是关闭的，可以开启，可以通过 session/global 设置开启级别
   • show profiles; #查看 SQL 语句的执行情况
   • show profile for query ${id}; #查看指定 query_id 的 SQL 语句各个阶段的耗时情况

十二、总结

以上是根据 B 站黑马课程学习后的 MySQL 笔记，为了日后复习和给别人参考而整理，希望对大家有所帮助。同时，在实际应用中，还需要根据具体情况合理使用索引、约束等功能，以提高数据库的性能和数据的完整性。