Mysql 学习
Mysql 学习
MySQL是一个传统的RDBM数据库，也就是关系型数据库，广泛应用于OLTP场景
OLTP(联机事务处理)是传统的关系型数据库的主要应用，用于基本的日常交易处理，例如银行的交易记录
OLAP(联机分析处理)是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并提供直观和易于理解的查询结果。最常见的应用是复杂的动态报告系统
一般来说，OLTP用于日常处理，OLAP用于数据分析
语法
数据类型
MySQL 支持所有标准 SQL 数值数据类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型
Varchar & char
Char 是一种固定长度的类型，varchar 则是一种可变长度的类型
Varchar 不是越大越好，在内存加载时每次按最大空间分配，varchar 占用空间大
Varchar 会在行记录头部额外用字节存储长度信息
Varchar(n)中 n 在 V5.0以前代表最多存储字节数，之后代表最多存储字符数
Int
在 int(11)中的11仅代表显示长度为11，不影响存储空间(将被废弃)
BLOB&TEXT
BLOB 是一个二进制大对象，可以容纳可变数量的数据
有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，区别在于可容纳存储范围不同
TEXT 为非二进制文本数据，同样有4种类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT，与 BLOB 相对应
SELECT
子句顺序：
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
| LIMIT | 要检索的行数 | 否 |
NULL
在匹配过滤和不匹配过滤中都不返回 NULL 值
COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值
大小写
Mysql 中查询数据时是否区分大小写取决于以下几个因素：
- 数据库表的字符集和排序规则
- 如果字符集是二进制的，如 binary、varbinary 等，那么查询时会区分大小写
- 如果排序规则是二进制的，如 utf8_bin、latin1_bin 等，那么查询时也会区分大小写
- 数据库表的存储引擎
- 如果存储引擎是 MyISAM 或 InnoDB，那么查询时会根据字符集和排序规则来判断是否区分大小写
- 如果存储引擎是 MEMORY 或 CSV，那么查询时不会区分大小写
- 操作系统的文件系统。如果数据库表是以文件形式存储在操作系统中，那么查询时会受到文件系统是否区分大小写的影响
- 在 Linux 系统中，文件名是区分大小写的，所以查询时也会区分大小写
- 在 Windows 系统中，文件名是不区分大小写的，所以查询时也不会区分大小写
函数
约束
MySQL 唯一约束(Unique Key)要求该列唯一，允许为空，但只能出现一个空值
MySQL 非空约束(NOT NULL)通过 CREATE TABLE 或 ALTER TABLE 语句中在某个字段的定义后加上关键字 NOT NULL 作为限定词，来约束该列的取值不能为空
其他
- 应该总是使用4位数字的年份
- 在仅需要日期时始终使用 Date()
- 使用 ORDER BY 排序数据，不依赖 GROUP BY 排序数据
- 如果想从表中删除所有行，不要使用 DELETE，可使用 TRUNCATE TABLE 语句，它完成相同的工作，但速度更快(TRUNCATE 实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据)
- 用 SHOW processlist 可以查看用户正在运行的所有线程以及对应执行的 sql 命令
基础知识
操作分类
DDL
数据定义 - Data Definition Language，用于创建、修改和删除数据库结构
CREATE：用于创建数据库或其对象(如表、索引、函数、视图、存储过程和触发器)DROP：用于从数据库中删除对象ALTER：用于改变数据库的结构TRUNCATE：用于从表中删除所有记录，包括为删除的记录分配的所有空间COMMENT：用于向数据字典添加注释RENAME：用于重命名数据库中存在的对象
DQL
数据查询 - Data Query Language，用于查询架构对象中的数据
SELECT：用于从数据库中检索数据
DML
数据操作 - Data Manipulation Language，为用户提供添加、删除、更新数据的能力
INSERT：用于将数据插入表中UPDATE：用于更新表中的现有数据DELETE：用于从数据库表中删除记录LOCK：表并发控制CALL：调用一个 PL/SQL 或 JAVA 子程序EXPLAIN：描述数据的访问路径
DCL
数据控制 - Data Control Language，主要处理数据库系统的权限
GRANT：此命令授予用户访问数据库的权限REVOKE：此命令撤消使用 GRANT 命令授予的用户访问权限
TCL
事务控制 - Transaction Control Language，主要控制数据库中的事务
事务将一组任务分组到单个执行单元中，每个事务都以特定任务开始，并在组中的所有任务成功完成时结束，如果任何任务失败，事务将失败
BEGIN：开启事务COMMIT：提交事务ROLLBACK：在发生任何错误的情况下回滚事务SAVEPOINT：在事务中设置一个保存点SET TRANSACTION： 指定事务的特征
三范式
第一范式：数据库表的每一字段都是不可分割的基本数据项
在任何一个关系数据库中，1NF 是对关系模式的基本要求，不满足1NF 的数据库就不是关系数据库
第二范式(依赖性)：数据库表中的每个实例或行必须可以被唯一的区分
2NF 建立在1NF 之上，要求实体的属性完全依赖于主关键字，不能仅依赖部分主关键字
第三范式(唯一性)： 一个数据库表中不包含已在其它表中包含的非主关键字信息，即属性不依赖于其他非主属性
3NF 建立在2NF 之上，非主键字段不能相互依赖，任何非主属性不依赖于其它非主属性
逆范式: 通过增加冗余或重复的数据来提高数据库的读性能
主键&辅助键&外键
主键
主键(PRIMARY KEY)是用于唯一标识数据库中每条记录的字段，不能包含NULL值
选取主键的一个基本原则：不使用任何业务相关的字段作为主键
一个表只能有一个主键约束，该约束可以包含一个或多个字段，构成主键的多个字段被统一称为联合主键
主键设置：
- CREATE 创建库时指定
- 通过 ALTER 添加
主键设计：
- 自增主键设计 - 数据库会在插入数据时自动为每一条记录分配一个自增整数
- 用 BIGINT 而非 INT做主键
- 当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误
- MySQL 8.0 版本前自增值存在回溯现象，不建议使用
- 自增值做主键，只能在当前实例中保证唯一，不能保证全局唯一
- UUID 主键设计 - 使用一种全局唯一的字符串作为主键
- 业务自定义生成主键 - 在随机主键基础上结合业务信息生成主键
辅助键
辅助键显示每条记录唯一的辅助值，它可用于标识记录，并且通常被索引，它也被称为备用键，一个表可以有多个辅助键
外键
外键 (FOREIGN KEY) 是用于在两个表中的数据之间建立和加强链接的一列或多列的组合，约束两个表中数据的一致性和完整性
相关联字段中主键所在的表就是主表(父表)，外键所在的表就是从表(子表)，默认情况下子表的记录相关数据删除后，主表才能删除记录
其他
伪列
伪列是指一些具有特殊含义的列名，它们并不是表中实际存在的列，但可以在 SQL 查询语句中使用
伪列的行为与表中的列相同，但并未存储具体数值，因此伪列只具备读属性
常见的伪列：
ROWNUM：用于返回每条结果记录在结果集合中的行编号ROWID：用于返回表中每一行的唯一标识符LEVEL：用于在连接查询中表示连接层次
连接池
数据库连接池是程序启动时建立的足够多的数据库连接，这些连接组成一个连接池，由程序动态地对池中的连接进行申请，使用，释放
创建数据库连接是一个很耗时的操作，MySQL 短连接每次请求操作数据库都需要与 MySQL 服务器建立 TCP 连接，在并发量非常大的情况会有影响
数据库连接池的机制：
- 程序初始化时创建连接池
- 使用时向连接池申请可用连接
- 使用完毕，将连接返还给连接池
- 程序退出时，断开连接，并释放资源
- 最大连接数：支持的最大连接数，即能打开连接的最大上限
- 最大空闲连接数： 表示连接池中最多有多少个空闲连接，某个连接做完事务之后暂时空闲，如果连接池中空闲连接数没有达到上限，即可放入连接池，该参数其实可以理解为一共可维护多少个长连接来节约连接建立的成本
- 最长空闲时间： 连接池中连接使用完毕后会等到新的请求到来，表明了连接池中的连接在空闲时能在池子里呆多久，如果长时间没有请求到来，说明请求量非常小，此时就需要释放掉连接来节省资源，等待多久，就是由该参数决定，通常情况下10-20s 就足够了
MYSQL 的最大连接数在5.7版本中默认是151，最大可以达到16384(2^14)
最大连接数太小造成的后果是连接失败，query failed Error 1040: Too many connections 错误
太大且当连接该数据库的机器比较多的时候则会对 MYSQL 的性能产生影响，因为系统需要同时维护过多的 TCP 连接
MYSQL 官方给出的设置最大连接数的建议比例为 Max_used_connections / max_connections * 100% ≈ 85%
临时表
Mysql 内部临时表是分为内存临时表和磁盘临时表，通常在 union 和 group by 会用到临时表，Extra 会显示 Using temporary
如果 group by 需要统计的数据量较大，内存临时表装不下，就会用到磁盘临时表，所以尽量让 group by 过程用上表的索引，这样就不会用到磁盘临时表