MySQL学习笔记-从入门到精通

已于 2024-06-16 21:31:25 修改
阅读量1k 收藏 17
点赞数 28
分类专栏： 学习笔记 文章标签： mysql 学习 笔记
于 2024-06-16 21:26:34 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2201_75483710/article/details/139726442
版权

MySQL 入门到精通

该笔记基于B站黑马课程 《MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括》,详情请点击：
黑马程序员

1.数据库相关概念

名称全称简称
数据库存储数据的仓库，数据是有组织的进行存储DataBase(DB)
数据库管理系统操纵和管理数据库的大型软件DataBase Management System(DBMS)
SQL操纵关系型数据库的编程语言，定义了一套操作关系型数据库统一标准Structured Query Language(SQL)

2. 概述

2.1 启动及停止

启动

net start mysql[版本]

停止

net stop mysql[版本]

[!CAUTION]

Mysql 默认开机自动启动

2.2 客户端连接

方式1： MySQL提供的客户端命令工具

在这里插入图片描述

方式2：系统自带的命令行工具执行指令

mysql [-h 127.0.0.1] [-P 3306] -u root -p

[!CAUTION]

使用这种方式时须配置PATH环境变量

2.3 数据模型

关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

特点：

  1. 使用表存储结构，格式统一，便于维护
  2. 使用SQL语言操作，标准统一，使用方便

MySQL数据库模型（MODEL）

数据库（database）表（table）

3. 基础 - SQL

3.1 通用语法及分类

SQL 通用语法

  1. SQL 语句可以单行或多行书写，以分号结尾

  2. SQL 语句可以使用空格/缩进来增强语句的可读性

  3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写

  4. 注释：

    单行注释：-- 注释内容 或 # 注释内容（MySQL特有）

    多行注释：/* 注释内容 */

SQL 分类

分类全称说明
DDLData Definition Language数据定义语言，用来定义数据库对象（数据库，表，字段）
DMLData Mainpulation Language数据操作语言，用来对数据库表中的数据进行修改
DQLData Query Language数据查询语言，用来查询数据库中表的记录
DCLData Control Language数据控制语言，用来创建数据库用户、控制数据库的访问权限

3.2 DDL

3.2.1 DDL - 数据库操作

查询：

查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

创建：

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排列规则]

删除：

DROP DATABASE [IF EXISTS] 数据库名

使用：

USE 数据库名
3.2.2 DDL - 表操作

查询：

查询当前数据库所有表

SHOW TABLES

查询表结构

DESC 表名

查询指定表的建表语句

SHOW CREATE TABLE 表名

创建：

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
	字段3 字段3类型 [COMMENT 字段3注释],
	......
	字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释]；

[!CAUTION]

[…] 为可选对象，最后一个字段后无逗号

数据类型

数值类型：

类型大小有符号（SIGNED）范围无符号（SIGNED）范围描述
TINYINT1 byte(-128, 127)(0, 255)小整数值
SMALLINT2 bytes(-32768, 32767)(0, 65535)大整数值
MEDIYMINT3 bytes(-8388608, 8388607)(0, 16777215)大整数值
INT或INTEGER4 bytes(-2147483648, 2147483647)(0, 4294967295)大整数值
BIGINT8 bytes(-2^63, 2^63 - 1)(0, 2^64 -1 )极大整数值
FLOAT4 bytes(-3.402823466 E+38, 3.4028234663521 E+8)0 和 (1.175494351 E-38, 3.402823466 E+38)单精度浮点数值
DOUBLE8 bytes( -1.7976931348623157 E+308, 1.7976931348623157 E+38)0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308)双精度浮点数值
DECIMAL依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值小数值（精确定点数）

字符串类型：

类型大小描述
CHAR0 - 255 bytes定长字符串
VARCHAR0 - 65535 bytes变长字符串
TINYBLOB0 - 255 bytes不超过255个字符的二进制数据
TINYTEXT0 - 255 bytes短文本字符串
BLOB0 - 65 535 bytes二进制形式的长文本数据
TEXT0 - 65 535 bytes长文本数据
MEDIUMBLOB0 - 16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0 - 16 777 215 bytes中等长度文本数据
LONGBLOB0 - 4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0 - 4 294 967 295 bytes极大文本数据

[!CAUTION]

char(定长) 性能好 varchar(定长) 性能较差

日期类型

类型大小范围格式描述
DATE3 bytes1000-01-91 至 9999-12-31YYYY-MM-DD日期值
TIME3 bytes-838:59:59 至 838:59:59HH:MM:SS时间值或持续时间
YEAR1 bytes1901 至 2155YYYY年份值
DATETIME8 bytes1000-01-01 00:00:00 至 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP4 bytes1970-01-01 00:00:00 至 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳

修改：

添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束]

修改数据类型

ALTER  TABLE 表名 MODIFY 字段名 新数据类型(长度)

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧段名 新字段名 类型(长度) [COMMENT 注释] [约束]

删除字段

ALTER TABLE 表名 DROP 字段名

修改表名

ALTER TABLE 表名 RENAME TO 新表名

删除：

删除表

DROP TABLE [IF EXISTS] 表名

删除指定表，并重新创建该表

TRUNCATE TABLE 表名

[!CAUTION]

在删除表时，表中的全部数据也会被删除

3.3 DML

介绍

DML 英文全称 Data Mainpulation Language(数据操作语言)，用来对数据库中的数据记录进行增删改操作

添加数据（INSERT）

给指定字段添加数据

INSERT INTO 表名(字段1，字段2，...) VALUES(值1，值2，...)

给全部字段添加数据

INSERT INTO 表名 VALUES(值1，值2，...)

批量添加数据

INSERT INTO 表名(字段1，字段2，...) VALUES(值1，值2，...),(值1，值2，...),(值1，值2，...);

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

[!CAUTION]

1.插入数据时，指定的字段顺序需要与值的顺序是一一对应的

2.字符串和日期型数据应该包含在引号中

3.插入的数据大小，应该在字段的规定范围内

修改数据（UPDATE）

UPDATE 表名 SET 字段1=值1, 字段2=值2,...[WHERE 条件]

[!CAUTION]

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据

删除数据（DELETE）

DELETE FROM 表名 [WHERE 条件]

[!CAUTION]

DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据

DELETE 语句不能删除某一个字段的值(可以用UPDATE)

3.4 DQL

介绍

DQL 英文全称 Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录

查询关键字：SELETE

语法:

  • 基本查询
  • 条件查询(WHERE)
  • 聚合函数(count, max, min, avg, sum)
  • 分组查询(GROUP BY)
  • 排序查询(ORDER BY)
  • 分页查询(LIMIT)
SELECT 
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数
3.4.1 基本查询

查询多个字段

SELECT 字段1, 字段2, 字段3...FROM 表名

SELECT * FROM 表名

设置别名

SELECT 字段1 [AS 别名1], 字段2 [AS 别名2]...FROM 表名

去除重复值

SELECT DISTINCT 字段列表 FROM 表名
3.4.2 条件查询

语法

SELECT 字段列表 FROM 表名 WHERE 条件列表

条件

比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<> 或 !=不等于
BETWEEN … AND …在某个范围之内(含最小，最大值)
IN(…)在in之后的列表中的值，多选一
LIKE 占位符模糊匹配(…匹配单个字符，%匹配任意个字符)
IS NULL是NULL
逻辑运算符功能
AND 或 &&并且(多个条件同时成立)
OR 或 ||或者(多个条件任意一个成立)
NOT 或 ！非，不是
3.4.3 聚合函数

介绍

将一列数据作为一个整体，进行纵向计算

常见聚合函数

函数功能
count统计数量
max最大值
min最小值
avg平均值
sum求和

语法

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

[!CAUTION]

NULL 值不参与所有聚合函数运算

3.4.4 分组查询

语法

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

WHERE 和 HAVING 区别

执行时机不同：WHERE 是分组之间进行过滤，不满足 WHERE 条件，不参与分组；而 HAVING 是分组之后对结果进行过滤

判断条件不同：WHERE 不能对聚合函数进行判断，而 HAVING 可以

[!CAUTION]

执行顺序：WHERE > 聚合函数 > HAVING

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

3.4.5 排序查询

语法

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

排序方式

ASC: 升序(默认值)

DESC: 降序

[!CAUTION]

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

3.4.6 分页查询

语法

SELECT 字段列表 FROM LIMIT 起始索引, 查询记录数

[!CAUTION]

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是 LIMIT

如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10

执行顺序
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
SELECT 
	字段列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

3.5 DCL

介绍

DCL 英文全称 Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限

3.5.1 用户管理

查询用户

USE mysql;
SELECT * FROM user

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'

删除用户

DROP USER '用户名'@'主机名'

[!CAUTION]

主机名可以使用%调配

这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用

3.5.2 权限控制

MySQL 中定义了很多种权限，但是常用的就以下几种：

权限说明
ALL, ALL PRIVILEGES所有权限
SELECT查询数据
INSERT插入数据
UPDATE修改数据
DELETE删除数据
ALTER修改表
DROP删除数据库/表/视图
CREATE创建数据库/表

查询权限

SHOW GRANTS FOR '用户名'@'主机名'

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'

[!CAUTION]

多个权限之间，使用逗号分隔

授权时，数据库名和表名可以使用 * 进行调配，代表所有

3.6 函数

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

3.6.1 字符串函数
函数功能
CONCAT(S1, S2, …Sn)字符串拼接，将S1, S2, …Sn拼接成一个字符串
LOWER(str)将字符串 str 全部转为小写
UPPER(str)将字符串 str 全部转为大写
LPAD(str, n, pad)左填充，用字符串 pad 对 str 的左边进行填充，达到 n 个字符串长度
PRAD(strt, n, pad)左填充，用字符串 pad 对 str 的右边进行填充，达到 n 个字符串长度
TRIM(str)去掉字符串头部和尾部的空格
SUBSTRING(str, start, len)返回从字符串 str 从 start 位置起的 len 个长度的字符串
3.6.2 数值函数
函数功能
CEIL(x)向上取整
FLOOR(x)向下取整
MOD(x, y)返回 x/y 的模
RAND()返回 0 ~ 1内的随机数
ROUND(x, y)求参数 x 的四舍五入的值，保留 y 位小数
3.6.3 日期函数
函数功能
CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前日期和时间
YEAR(date)获取指定 date 的年份
MONTH(date)获取指定 date 的月份
DAY(date)获取指定 date 的日期
DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔 expr 后的时间值
DATEDIFF(date1, date2)返回起始时间 date1 和 结束时间 date2 之间的天数
3.6.4 流程函数
函数功能
IF(value, t, f)如果 value 为 true，则返回 t，否则返回 f
IFNULL(value1, value2)如果 value1 不为空，返回 value1，否则返回 value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END如果 val1 为 true，返回 res1，否则返回 default 默认值
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END如果 expr 的值等于 val1，返回 res1，…否则返回 default 默认值

3.7 约束

概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

目的：保证数据库中数据的正确、有效性和完整性

分类：

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

[!CAUTION]

约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

3.7.1 外键约束

概念

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

语法

添加外键

CREATE TABLE 表名(
	字段名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

删除/更新行为

行为说明
NO ACTION当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新 (与 RESTRICT 一致)
RESTRICT当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新 (与 NO ACTION 一致)
CASCADE当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
SET NULL当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为 null (这就要求该外键允许取 null)
SET DEFAULT父表有变更时，子表将该外键列设置成一个默认的值(Innodb不支持)
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCASE

3.8 多表查询

3.8.1 多表关系

概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以在各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多（多对一）

    实现：在多的一方建立外键，指向一的一方的主键

  • 多对多

    实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

  • 一对一

    实现：在任意一方加入外键，关联另一方的主键，并设置外键为唯一 (UNIQUE)

3.8.2 概述

概述：指从多张表中查询数据

笛卡尔积：笛卡尔乘积时指在数学中，两个集合 A 集合 和 B 集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

分类

  • 连接查询

    内连接：相当于查询 A、B交集部分数据

    外连接：

    ​ 左外连接：查询左表所有数据，以及两张表交集部分数据

    ​ 右外连接：查询右表所有数据，以及两张表交集部分数据

  • 子查询

3.8.3 内连接

内连接查询语法

隐式内连接

SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...

显式内连接

SELECT 字段列表 FROM [INNER] JOIN 表2 ON 连接条件 ...

[!CAUTION]

内连接查询的是两张表交集的部分

3.8.4 外连接

外连接查询语法

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...

[!CAUTION]

相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...

[!CAUTION]

相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据

3.8.5 自连接

自连接查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...

[!CAUTION]

自连接查询，可以是内连接查询，也可以是外连接查询

3.8.6 联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...

[!CAUTION]

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

3.8.7 子查询

概念：SQL 语句中嵌套 SELECT 语句，称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1=(SELECT column FROM t2)

[!CAUTION]

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

分类

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）

根据子查询位置，分为：WHERE 之后、FROM 之后、SELECT 之后

3.8.7.1 标量子查询

标量子查询

子查询结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询

常用的操作符：= <> > >= < <=

3.8.7.2 列子查询

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询

常用的操作符：IN 、NOT IN、ANY、SOME、ALL

操作符描述
IN在指定的集合范围之内，多选一
NOT IN不在指定的集合范围之内
ANY子查询返回列表中，有任意一个满足即可
SOME与 ANY 等同，使用 SOME 的地方都可以使用 ANY
ALL子查询返回列表的所有值都必须满足
3.8.7.3 行子查询

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

常用的操作符：= 、<> 、IN 、NOT IN

3.8.7.4 表子查询

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

常用的操作符：IN

3.9 事务

事务简介

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么 同时成功，要么同时失败

在这里插入图片描述

[!CAUTION]

默认 MySQL 的事务是自动提交的，也就是说，当执行一条 DML 语句，MySQL 会立即隐式的提交事务

查看 / 设置事务提交方式

SELECT @@autocommit;
SET @@autocommit = 0; //设置手动提交

提交事务

COMMIT;

回滚事务

ROLLBACK;

开启事务

START TRANSACTION 或 BEGIN
3.9.1 四大特性 - ACID
  • 原子性（Atomicity）：事务时不可分割的最小操作单元，要么全部成功，要么全部失败
  • 一致性（Consistency）： 事务完成时，必须使所有的数据都保持一致状态
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
3.9.2 事务并发问题
问题描述
脏读一个事务读取到另一个事务还没有提交的数据
不可重复读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了"幻影"
3.9.3 事务隔离级别
隔离级别脏读不可重复读幻读
Read uncommitedTRUETRUETRUE
Read commitedFALSETRUETRUE
Repeatable Read(默认)FALSEFALSETRUE
SerializableFALSEFALSEFALSE
--查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION

--设置事务隔离级别
SET [SESSION|GLOCAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITED|READ COMMITED|REPEATABLE READ|SERIALIZABLE}

4. 进阶

4.1 主要内容

  • 存储引擎
  • 索引
  • SQL 优化
  • 视图 / 存储过程 /触发器
  • 锁
  • InnoDB 引擎
  • MySQL 管理

4.2 存储引擎

MySQL 体系结构
在这里插入图片描述

  • 连接层

    最上层时一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限

  • 服务层

    第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储的功能也在这一层实现，如 过程、函数等。

  • 引擎层

    存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

  • 存储层

    主要是将数据存储在文件系统上，并完成与存储引擎的交互。

存储引擎简介

存储引擎就是存储数据、建立索引、更新 / 查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

​ 在创建表时，指定存储引擎

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	...
	字段n 字段n类型 [COMMENT 字段n注释]
)ENDING = INNODB [COMMENT 表注释]

​ 查看当前数据库支持的存储引擎

SHOW ENGINGS;

存储引擎特点

  • InnoDb

    介绍

    InnoDB 是一种兼顾高可靠和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 存储引擎

    特点

    DML 操作遵循ACID模型，支持事务

    行级锁，提高并发访问性能

    支持 外键 FOREIGN KEY 约束，保证数据的完整性和正确性

    文件

    xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。

    参数：innodb_file_per_table

    在这里插入图片描述

  • MyISAM

    介绍

    MyISAM 是 MySQL 早期的默认存储引擎

    特点

    不支持事务，不支持外键

    支持表锁，不支持行锁

    访问速度快

    文件

    xxx.sdi：存储表结构信息

    xxx.MYD：存储数据

    xxx.MYI：存储索引

  • Memory

    介绍

    Memory 引擎的表数据时存储在内存中，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

    特点

    内存存放

    hash索引（默认）

    文件

    xxx.sdi：存储表结构信息

特点InnoDBMyISAMMemory
存储限制64TB有有
事务安全支持--
锁机制行锁表锁表锁
B+tree索引支持支持支持
Hash索引--支持
全文索引支持（5.6版本之后）支持-
空间使用高低N/A
内存使用高低中等
批量插入速度低高高
支持外键支持--

存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB：是 MySQL 的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
  • MyISAM：如果应用是以读和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
  • MEMORY: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的完全性。

4.3 索引

4.3.1 概述

  • 介绍

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

  • 优缺点

    优势劣势
    提高数据检索的效率，降低数据库的IO成本索引列也需要占用空间
    通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗索引大大提高查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。
4.3.2 索引结构

MySQL 的索引实在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含一下几种：

索引结构描述
B+Tree索引最常见的索引类型，大部分引擎都支B+树索引
Hash索引底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引)空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene, Solr, ES
索引InnoDBMyISAMMemory
B+tree索引支持支持支持
Hash索引不支持不支持支持
R-tree索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

[!CAUTION]

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引

4.3.2.1 Btree

在这里插入图片描述

在这里插入图片描述

具体B-Tree动态变化的过程可以参考网站：https://www.cs.usfca.edu/~galles/visualzation/BTree.html

4.3.2.2 B+tree

在这里插入图片描述

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能

在这里插入图片描述

4.3.2.3 hash

在这里插入图片描述

Hash

  • Hash 索引特点

    Hash索引只能用于对等比较（=，in），不支持访问查询（between，>，<，…）

    无法利用索引完成排序操作

    查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

  • 存储引擎支持

    在 MySQL中，支持 hash 索引的是 Memory 引擎，而 InnoDB 中具有自适应 hash 功能，hash 索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

思考
为什么 InnoDB 存储引擎选择 B+tree 索引结构？
  • 相对于二叉树，层级更少，搜索效率高
  • 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
  • 相对 Hash 索引，B+tree 支持范围匹配及排序操作
4.3.3 索引分类
分类含义特点关键字
主键索引针对表中主键创建的索引默认自动创建，且只能有一个PRIMARY KEY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键字，而不是比较索引中的值可以有多个FULLTEXT

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

分类含义特点
聚集索引(Clustered index)将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据必须有，而且只有一个
二级索引(Secondary index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个

在这里插入图片描述

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
  • 如果表中没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引
    支持范围匹配及排序操作
4.3.3 索引分类
分类含义特点关键字
主键索引针对表中主键创建的索引默认自动创建，且只能有一个PRIMARY KEY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键字，而不是比较索引中的值可以有多个FULLTEXT

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

分类含义特点
聚集索引(Clustered index)将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据必须有，而且只有一个
二级索引(Secondary index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
  • 如果表中没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引
范忠煜
关注
  • 28
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql入门到精通学习笔记
06-06
sql mysql入门到精通学习笔记，详细记录初学者需要掌握的主要知识
学习笔记(01):Hadoop大数据从入门到精通-Hadoop的介绍及基本概念
01-20
立即学习:...2.当作服务软件，C/S架构，例如MySQL（使用JDBC调用），缓存服务redis，索引服务solr。可以存储、分析数据。 Hadoop的历史： 1.google三篇论文:GFS/MAPRE
mysql primary unique foreign_Mysql中主键（Primary Key）、外键（Foreign Key）、唯一键（ Unique Key）、索引（Index）的说明...
weixin_39858298的博客
02-07 1269
本文主要介绍Mysql几种键和索引：MySQL中有三种Key和一个Index: Primary Key(主键), Unique Key(唯一键),Foreign Key(外键)和 Index(索引)四者的区别如下：定义：主键(Primary Key)：唯一标识一条记录，不能有重复，不允许为空。外键(Foreign Key)：表的外键是另一表的主键, 外键可以有重复, 可以是空值。唯一键( Uniq...
2024年【MySQL】临时表 &amp；视图_mysql 临时视图(1)，腾讯架构师深入讲解C C++开发
最新发布
2401_84976182的博客
05-13 964
答案是不行的，如果仅仅通过这 3 个表之间的连接进行查询，我们可能会丢失某些数据。为了解决这个问题， 我们需要把商品信息表的放到左边，与其他表进行左连接， 确保所以商品都包含在结果集中。而且，连接结束之后，临时表会自动删除，也不用担心大量无用的中间数据会 残留在数据库中。直接用查询语句创建临时表比较快捷，而且连接结束后临时表就会被自动删除，不需要过多考虑表的结构设计问题（比如冗余、效率等）。我们可以把一段查询语句作为视图存储在数据库中，在需要的时候， 可以把视图看做一个表，对里面的数据进行查询。
SpringSecurity-从入门到精通
热门推荐
Lifelong learning
04-10 3万+
SpringSecurity从入门到精通 介绍 0. 简介 ​ Spring Security 是 Spring 家族中的一个安全管理框架。相比与另外一个安全框架Shiro，它提供了更丰富的功能，社区资源也比Shiro丰富。 ​ 一般来说中大型的项目都是使用SpringSecurity 来做安全框架。小项目有Shiro的比较多，因为相比与SpringSecurity，Shiro的上手更加的简单。 ​ 一般Web应用的需要进行认
Mysql底层学习笔记-从入门到精通（字符集篇）
ciel
07-18 148
重要字符集记录： ASCII字符集 ISO 8859-1字符集 GB2312字符集 GBK字符集 utf8字符集 utf8mb4字符集
狂神--springCloud框架学习笔记-从入门到精通
解决bug的路上
08-10 3016
SpringCloud阶段的学习 笔记 ​ 作者：小杰 搭配狂神的springCloud的教学学习–点这里观看 跟着狂神学习敲的springCloud项目–点这里下载 微服务架构4个核心问题 服务很多，客户端怎么访问？ 这么多服务，服务之间如何通信？ 这么多服务，如何治理？ 服务挂了怎么办? 核心问题解决方案 springcloud 生态 Spring Cloud NetFlix 一站式解决方案 api网关，zuul组件 Feign—基于Ht
PHP从入门到精通完整笔记301页.pdf
05-12
【PHP从入门到精通完整笔记】是一份涵盖了HTML、CSS以及PHP基础知识的全面教程，旨在帮助初学者系统地学习这三门技术。这份笔记共有301页，内容包括了HTML+CSS的前22讲以及PHP的第23至149讲。 **HTML基础知识** 1. ...
MySQL5.7 从入门到精通 2 2021-12-24.pdf
12-24
MySQL学习手写笔记2
MySQL从入门到精通1 2021-12-24 10.51.pdf
12-24
MySQL学习手写笔记
java从入门到精通(韩顺平视频)笔记整理
06-11
这只是Java从入门到精通学习过程中的冰山一角，后续的章节会深入探讨封装、继承、多态等面向对象特性，异常处理，集合框架，IO流，网络编程，反射，设计模式等复杂主题。通过不断的学习和实践，开发者可以逐步掌握这...
mysql的全套9个学习笔记.zip
02-11
1.入门到精通 2.多怜惜才是王道 3.多运用到实际开发中
mysql书籍学习笔记.rar
07-06
用了5天的时间把这本mysql数据库应用入门到精通看完了，虽然有些不是很理解但是开阔了自己的眼界。
PHP初级入门笔记
03-08
PHP 6与MYSQL 5基础教程的学习笔记，适合初学者学习
Java 从入门到精通全程笔记(JavaSE+JavaEE :史上最全笔记）
02-18
JavaSE+JavaEE企业级开发 全程笔记免费下载，内容详尽，doc文档,共173页,六号字体，双栏排版。 *、Java 基础， *、web， *、数据库（Mysql Oracle jdbc Hibernate hql/sql）， *、JSP， *、Ajax *、Struts， *、...
MySQL优化笔记+资料.zip
07-04
包含内容：MySQL入门到精通经典教程；MySQL优化讲义；MySQL优化笔记；SQL优化思路；索引学习；MySQL服务器调优思路；
Javaweb最新全套项目实战视频教程-JDBC课堂笔记
12-18
寻找适合自己的学习资源，现在不需要如此麻烦了，动力节点全新整理Javaweb阶段全套教程已经上线，内涵网络通信、HTML、CSS、JavaScript、MySQL、JDBC、JSP、Servlet等技术，Javaweb从入门到精通全套教程一站搞定，...
Javaweb最新全套项目实战视频教程-MVC课堂笔记
12-18
寻找适合自己的学习资源，现在不需要如此麻烦了，动力节点全新整理Javaweb阶段全套教程已经上线，内涵网络通信、HTML、CSS、JavaScript、MySQL、JDBC、JSP、Servlet等技术，Javaweb从入门到精通全套教程一站搞定，...
mysql从入门到精通李波
07-27
### 回答1： MySQL是一种流行的关系型数据库管理系统，具有易学易用、高性能和开源的特点。要想从入门到精通MySQL，需要掌握以下几个方面。 首先，了解MySQL的基本概念和架构。理解数据库、表、字段、索引和查询等基础概念，并了解MySQL的客户端-服务器架构以及数据的存储和查询过程。 其次，学习MySQL的安装和配置。学会如何下载、安装和配置MySQL服务器，并了解MySQL的配置文件和常见配置选项。 然后，熟悉MySQL的SQL语言。学习如何使用SQL语句进行数据的插入、更新、删除和查询。掌握SQL语句的基本语法和常用的查询操作，如JOIN、GROUP BY和ORDER BY等。 接着，了解MySQL的优化和调优技巧。学习如何通过创建合适的索引、优化查询语句和调整服务器参数等方式提高MySQL的性能。掌握查询执行计划的分析方法和索引的设计原则。 最后，深入学习MySQL的高级特性和扩展功能。学习如何使用存储过程、触发器和视图等高级特性，以及如何使用复制、分区和集群等扩展功能。同时，了解MySQL的安全机制和备份恢复策略，确保数据的安全性和可靠性。 通过以上的学习和实践，就可以从入门逐步进阶，最终掌握MySQL的各种特性和技巧，成为一名MySQL的专家。当然，实际的学习过程中还需要大量的实践和项目经验积累，不断提升自己的技术能力和解决问题的能力。 ### 回答2： 《MySQL从入门到精通》是一本由李波撰写的MySQL学习指南。该书详细介绍了MySQL数据库的基础知识和高级应用技巧，适合初学者和进阶学习者阅读。 该书的前几章主要介绍了数据库的基本概念和MySQL的安装与配置方法。作者通过简明扼要的语言，帮助读者了解数据库的概念、结构和基本操作，以及如何在各种操作系统上安装和配置MySQL。 接下来的几章主要介绍了SQL语言的基本知识和常用操作。作者通过实例演示和练习题，详细讲解了SQL的语法、查询、插入、更新和删除操作，以及各种约束和函数的使用。 随后的章节逐渐深入，介绍了MySQL数据库的高级应用技巧和性能优化方法。作者详细介绍了索引的原理和使用方式，以及如何设计和优化数据库模型，提高查询和执行效率。此外，作者还介绍了如何进行备份和恢复数据库，以及如何保护数据库的安全性。 最后几章则介绍了MySQL数据库在Web开发中的应用。作者详细介绍了如何使用PHP和MySQL进行数据交互，以及如何利用MySQL实现用户认证和权限控制等功能。 总的来说，该书通过丰富的示例和实践操作，帮助读者系统全面地了解和掌握MySQL数据库的使用。无论是初学者还是有一定经验的开发人员，都可以通过阅读该书有效提升自己的MySQL水平。 ### 回答3： 《MySQL从入门到精通李波》是一本以MySQL数据库为主题的书籍，旨在帮助读者从零开始学习MySQL，并逐步成为MySQL专家。 首先，书中会介绍MySQL数据库的基本概念、结构和工作原理，以及在不同操作系统下的安装和配置方法。读者可以从中了解MySQL的基本特性，如数据类型、表、索引等，以及MySQL与其他数据库系统的差异。 第二，书中会逐步引导读者学习SQL语言，这是使用MySQL的必备知识。读者将学习SQL的基本语法和常用操作，如查询数据、插入、更新和删除等。此外，书中还会介绍高级的SQL操作和性能优化技巧，以提升数据库的效率和查询速度。 第三，书中会详细介绍MySQL的高级功能和特性。读者将学习如何进行数据库的备份与恢复、事务管理以及数据安全等方面的知识。同时，还会介绍MySQL的集群和分布式架构，以及如何进行数据库的水平和垂直拆分，以满足大规模应用的需求。 最后，书中还会介绍MySQL的性能调优和故障处理方法。读者将学习如何监控数据库的性能指标，以及如何调整数据库配置和优化查询语句，以提高数据库的性能和稳定性。同时，还会介绍如何处理数据库故障和恢复数据的方法。 总之，通过《MySQL从入门到精通李波》，读者可以系统地学习MySQL数据库的基本知识和高级技巧，从而成为一名熟练的MySQL专家。无论是初学者还是有一定经验的开发人员，都可以通过这本书提升自己的数据库技能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值