mysql 进阶学习 满满的知识点

目录

一、简介

二、引擎

1、InnoDB

2、MyISAM

3、MEMORY

三、3大范式

四、索引

主键索引（PRIMARY）

唯一索引（UNIQUE）

普通索引（INDEX）

全文索引（FULLTEXT）

空间索引（SPATIAL）

五、常用表字段类型

六、语法

新增一条数据：

更新数据：

删除数据：

查询数据：

排序并分页：

分组计数：

左连接：

七、事务

数据库事务特性：

事务的隔离级别：

八、视图、存储过程、触发器

视图

存储过程

触发器

---

一、简介

MySQL 是一个开源的数据库管理系统，具有灵活性和可扩展性，适用于各种规模的应用程序。MySQL还支持多种存储引擎，如 InnoDB、MyISAM 等，可以根据需要选择不同的存储引擎以满足特定的性能和可靠性需求。MySQL 还提供了丰富的查询功能和强大的事务处理能力，可以高效地处理大量数据和复杂的事务操作。

二、引擎

MySQL提供了多种存储引擎，有InnoDB、MyISAM、MEMORY、CSV、ARCHIVE等，其中最为常见的是InnoDB、MyISAM、MEMORY，MySQL5.5之前默认引擎是MyISAM, 从MySQL5.5版本开始，默认引擎是InnoDB

1、InnoDB

提供了事务支持、行级锁定和外键约束等功能，适用于高并发读写场景。（InnoDB会将每一条SQL都默认封装成事务自动提交）

2、MyISAM

不支持事务和行级锁定，在执行简单的查询操作时速度较快，因为它没有事务和行级锁定的开销,空间占用小，不需要像InnoDB那样使用额外的空间来存储事务和锁定信息，适用于简单的读操作场景。（注意MyISAM用 一个变量保存了整个表的行数，所以查整表行数快）

3、MEMORY

MEMORY是MySQL中用于临时表的存储引擎，它将数据存储在内存中，适用于临时数据的处理和快速查询。

三、3大范式

MySQL的3大范式是数据库设计的理论框架，它们分别关注于减少数据冗余、确保数据完整性和减少数据复杂度。理解和应用这些范式可以设计出更加高效、可维护和可扩展的数据库模型。

第一范式（1NF）：确保每列保持原子性，列不可再分。比如地址可以分为省、市、区等。

第二范式（2NF）：在第一范式的基础上，非主键列必须完全依赖于主键，区分行。

第三范式（3NF）：在第二范式的基础上，一个表中不包含其他表中非主键的字段信息。

四、索引

主键索引（PRIMARY）

就是表主键，的值必须是唯一的，不允许有空值。

唯一索引（UNIQUE）

索引列中的值必须是唯一的，但是允许一个空值。

普通索引（INDEX）

这是最基本的索引类型，它没有任何限制，可以创建在任何列上。

全文索引（FULLTEXT）

全文索引对文本进行了预处理，能够更快地搜索和匹配文本数据。（只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引）

空间索引（SPATIAL）

用于空间数据类型。

五、常用表字段类型

1. INT：整数类型，通常用于存储整数。
2. TINYINT：较小的整数类型，通常用于存储较小的整数。
3. DATE：日期类型，用于存储日期数据。
4. VARCHAR：可变长度字符串类型，用于存储长度可变的字符串。
5. TEXT：文本类型，用于存储较大的文本数据。
6. BLOB：二进制对象类型，用于存储二进制数据。

六、语法

新增一条数据：

INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...);

更新数据：

UPDATE 表名
SET column1 = value1, column2 = value2, ...
WHERE 条件;

删除数据：

DELETE FROM 表名 WHERE 条件;

查询数据：

SELECT * FROM 表名 WHERE 条件;

排序并分页：

SELECT * FROM 表名
ORDER BY 排序列名
LIMIT 页面大小 OFFSET 分页偏移量;

• 排序列名：您希望根据其进行排序的列的名称。
• 页面大小：每页显示的行数。
• 分页偏移量：要跳过的行数，以确定开始位置。

分组计数：

SELECT 列1, 列2, COUNT(*) as 计数
FROM 表名
GROUP BY 列1, 列2;

左连接：

SELECT *
FROM 左表
LEFT JOIN 右表 ON 左表.列 = 右表.列;

七、事务

数据库事务特性：

原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性 (Durabiliy)。简称ACID。

事务的隔离级别：

1. 读未提交（Read Uncommitted）：这是最低的隔离级别，在这个级别，一个事务可以读取另一个未提交的事务的数据。这意味着可能出现脏读和不可重复读的情况。
2. 读已提交（Read Committed）：这是次低的隔离级别，在这个级别，一个事务只能读取另一个已提交的事务的数据。这可以防止脏读，但可能出现不可重复读的情况。
3. 可重复读（Repeatable Read）：这是MySQL的默认隔离级别，在这个级别，一个事务在整个过程中可以多次读取同一行数据，而不会看到其他事务对这一行数据的修改。这可以防止脏读和不可重复读，但可能出现幻读（Phantom Read）的情况。
4. 串行化（Serializable）：这是最高的隔离级别，在这个级别，事务排他性地顺序执行，而不是并发执行。这可以防止脏读、不可重复读以及幻读的情况。

注意：

与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key

Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如SQL Server) 是不同的。

八、视图、存储过程、触发器

视图

视图是一种虚拟表，是执行查询语句后所返回的结果集。视图将一个查询的结果作为一个表来使用，因此视图可以被看作是存储的查询或一个虚拟表，与真实表不同，视图不会要求分配存储空间，视图中也不会包含实际的数据。视图只是定义了一个查询，视图中的数据是从基表中获取，这些数据在视图被引用时动态的生成。

存储过程

存储过程是一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译。MySQL存储过程封装了一组SQL语句，在需要时可以方便地调用。

触发器

MySQL触发器和存储过程一样，都是嵌入到MySQL的一段程序。但是触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发， 触发器是一段能自动执行的程序！