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

MySQL 入门到精通

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

1.数据库相关概念

2. 概述

2.1 启动及停止

启动

net start mysql[版本]
1

停止

net stop mysql[版本]
1

[!CAUTION]

Mysql 默认开机自动启动

2.2 客户端连接

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

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

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

[!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 分类

3.2 DDL

3.2.1 DDL - 数据库操作

查询：

查询所有数据库

SHOW DATABASES;
1

查询当前数据库

SELECT DATABASE();
1

创建：

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

删除：

DROP DATABASE [IF EXISTS] 数据库名
1

使用：

USE 数据库名
1

3.2.2 DDL - 表操作

查询：

查询当前数据库所有表

SHOW TABLES
1

查询表结构

DESC 表名
1

查询指定表的建表语句

SHOW CREATE TABLE 表名
1

创建：

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

[!CAUTION]

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

数据类型

数值类型：

字符串类型：

[!CAUTION]

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

日期类型

修改：

添加字段

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

修改数据类型

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

修改字段名和字段类型

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

删除字段

ALTER TABLE 表名 DROP 字段名
1

修改表名

ALTER TABLE 表名 RENAME TO 新表名
1

删除：

删除表

DROP TABLE [IF EXISTS] 表名
1

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

TRUNCATE TABLE 表名
1

[!CAUTION]

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

3.3 DML

介绍

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

添加数据（INSERT）

给指定字段添加数据

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

给全部字段添加数据

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

批量添加数据

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

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

[!CAUTION]

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

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

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

修改数据（UPDATE）

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

[!CAUTION]

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

删除数据（DELETE）

DELETE FROM 表名 [WHERE 条件]
1

[!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
	分页参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.4.1 基本查询

查询多个字段

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

SELECT * FROM 表名
1

设置别名

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

去除重复值

SELECT DISTINCT 字段列表 FROM 表名
1

3.4.2 条件查询

语法

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

条件

3.4.3 聚合函数

介绍

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

常见聚合函数

语法

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

[!CAUTION]

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

3.4.4 分组查询

语法

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

WHERE 和 HAVING 区别

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

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

[!CAUTION]

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

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

3.4.5 排序查询

语法

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

排序方式

ASC: 升序(默认值)

DESC: 降序

[!CAUTION]

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

3.4.6 分页查询

语法

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

[!CAUTION]

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

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

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

执行顺序

FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
SELECT 
	字段列表
ORDER BY
	排序字段列表
LIMIT
	分页参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.5 DCL

介绍

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

3.5.1 用户管理

查询用户

USE mysql;
SELECT * FROM user
1
2

创建用户

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

修改用户密码

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

删除用户

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

[!CAUTION]

主机名可以使用%调配

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

3.5.2 权限控制

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

查询权限

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

授予权限

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

撤销权限

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

[!CAUTION]

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

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

3.6 函数

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

3.6.1 字符串函数

3.6.2 数值函数

3.6.3 日期函数

3.6.4 流程函数

3.7 约束

概述

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

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

分类：

[!CAUTION]

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

3.7.1 外键约束

概念

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

语法

添加外键

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

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

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
1

删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCASE
1

3.8 多表查询

3.8.1 多表关系

概述

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

• 一对多（多对一）

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

• 多对多

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

• 一对一

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

3.8.2 概述

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

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

分类

• 连接查询

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

  外连接：

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

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

• 子查询

3.8.3 内连接

内连接查询语法

隐式内连接

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

显式内连接

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

[!CAUTION]

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

3.8.4 外连接

外连接查询语法

左外连接

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

[!CAUTION]

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

右外连接

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

[!CAUTION]

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

3.8.5 自连接

自连接查询语法：

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

[!CAUTION]

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

3.8.6 联合查询

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

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

[!CAUTION]

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

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

3.8.7 子查询

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

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

[!CAUTION]

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

分类

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

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

3.8.7.1 标量子查询

标量子查询

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

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

3.8.7.2 列子查询

列子查询

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

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

3.8.7.3 行子查询

行子查询

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

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

3.8.7.4 表子查询

表子查询

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

常用的操作符：IN

3.9 事务

事务简介

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

[!CAUTION]

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

查看 / 设置事务提交方式

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

提交事务

COMMIT;
1

回滚事务

ROLLBACK;
1

开启事务

START TRANSACTION 或 BEGIN
1

3.9.1 四大特性 - ACID

• 原子性（Atomicity）：事务时不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性（Consistency）： 事务完成时，必须使所有的数据都保持一致状态
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

3.9.2 事务并发问题

3.9.3 事务隔离级别

--查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION

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

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 表注释]
1
2
3
4
5

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

SHOW ENGINGS;
1

存储引擎特点

• 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：存储表结构信息

存储引擎选择

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

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

4.3 索引

4.3.1 概述

• 介绍

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

• 优缺点

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

4.3.2 索引结构

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

[!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 索引分类

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

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果表中没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引
  支持范围匹配及排序操作

4.3.3 索引分类

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

聚集索引选取规则：

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