MySql从入门到精通-EW帮帮网

第一部分基础篇

1.概述

1.1 启动与停止MySql

启动

net start mysql80
停止

net stop mysql80

注意： mysql开机默认启动

1.2 客户端连接

方法一：使用MySQL提供的命令行客户端
方法二：系统自带的命令行工具执行指令
mysql [-h 127.0.0.1] [-p 3306] -u root -p

1.3 CentOS下MYSQL的登录、启动、停止、重启命令

MySql登录

mysql -u root -p

MySql配置文件

/etc/my.cnf
可配置端口3306，sql-model等信息。

在CentOS根目录下执行命令

启动

1、使用 service 启动：service mysql start

service mysqld start

systemctl start mysqld.service

2、使用 mysqld 脚本启动：/etc/inint.d/mysql start

3、使用 safe_mysqld 启动：safe_mysql&

停止

1、使用 service 启动：service mysql stop

service mysqld stop

systemctl stop mysqld.service

2、使用 mysqld 脚本启动：/etc/inint.d/mysql stop

3、mysqladmin shutdown

重启

1、使用 service 启动：service mysql restart

service mysqld restart

systemctl restart mysqld.service

2、使用 mysqld 脚本启动：/etc/inint.d/mysql restart

状态查询

1、使用 service 启动：service mysql status

service mysqld status

systemctl status mysqld.service

2.SQL语句

注释单行注释用–或者#，多行注释用/* */
SQL语句一般以分号结尾
MySql数据库的SQL语句不区分大小写
SQL语句分类
- DDL ：Data DefinitionLanguage 数据定义语言，用来定义数据库对象(数据库，表，字段)
- DML：Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改
- DQL： Data Query Language 数据查询语言，用来查询数据库中表的记录
- DCL： Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限。

2.1 DDL 用来定义数据库对象(数据库，表，字段)

2.1.1 DDL-数据库操作

在这里插入图片描述

2.1.2 DDL-表-操作

在这里插入图片描述

2.1.3 DDL-表-增删修改

对字段进行操作

增加字段
ALTER TABLE 表名 ADD 字段名类型（长度）【comment 注释】【约束】；
修改数据类型
ALTER TABLE 表名 MODIFY 字段名新的类型（长度）；
修改字段名和数据类型
ALTER TABLE 表名 CHANGE 原字段名新字段名数据类型（长度）；
删除字段
ALTER TABLE 表名 DROP 字段名

对表进行操作

修改表名
ALTER TABLE 表名 RENAME TO 新表名
删除指定表
DROP TABLE [IF EXISTS] 表名
删除指定表，并且重新创建该表（表的数据内容会被清除）
TRUNCATE TABLE 表名

2.2 DML 用来对数据库表中的数据进行增删改

2.2.1 DML-添加数据

在这里插入图片描述
注意：

插入的值应该和字段顺序一致
插入的字符串和日期数据应该要用引号

2.2.2 DML-修改数据

UPDATE 表名 SET 字段名1=值1，字段名2=值2，……[WHERE 条件]
注意：

没有加where条件默认修改整张表上该字段名的所有值

2.2.3 DML-删除数据

DELETE FROM 表名 [WHERE 条件]
注意：

没有加where条件默认删除整张表上的所有数据
delete语句不能删除某一个字段的值，可以用update语句将该字段修改为null

2.3 DQL 用来查询数据库中表的记录

2.3.1 DQL-基础查询

在这里插入图片描述

2.3.2 DQL-条件查询

在这里插入图片描述
注意：

between …and… 需要小的数字放在前面，大的数字放在后面

2.3.3 DQL-聚合函数

在这里插入图片描述
注意：

所有的null值不参与聚合函数的计算

2.3.4 DQL-分组查询

在这里插入图片描述
注意：

having 指分组之后在过滤的条件
执行顺序：where>聚合函数>having
分组之后，查询的字段一般为聚合函数或分组字段，查询其他字段无任何意义

2.3.5 DQL-排序查询

在这里插入图片描述
注意：

如果按多个字段查询，当第一个字段的值相同时，按照第二个字段排序

2.3.5 DQL-分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,分页记录数；
注意：

起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
分页查询是数据库的方言，不同数据库有不同的实现，MySql中使用limit实现
如果起始索引是0，可以省略不写，例如limit 10;

2.3.6 DQL-执行顺序

编写顺序
执行顺序

2.4 DCL 用来创建数据库用户、控制数据库的访问权限

2.4.1 DCL-用户管理

在这里插入图片描述
注意：

主机名可以用%通配，表示任意地址都可以
这类SQL开发人员操作的较少，主要是DBA（Database Administrator数据库管理人员）使用

2.4.2 DCL-权限控制

在这里插入图片描述

注意：

多个权限要用逗号分割
授权时，数据库名和表名可以使用*通配，表示所有

3.函数

3.1 字符串函数

MySql有很多内置字符串函数
在这里插入图片描述

3.2 数值函数

在这里插入图片描述

3.3 日期函数

在这里插入图片描述

3.4 流程控制函数

在这里插入图片描述

4.约束

4.1 约束-概述

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

约束

描述

关键字

非空约束

限制该字段的数据不能为null

NOT FULL

唯一约束

保证该字段的所有数据都是唯一的，不重复的

UNIQUE

主键约束

主键是一行数据的唯一标识，要求非空且唯一

PRIMARY KEY

默认约束

保存数据时，如果未指定该字段的值，则采用默认值

DEFAULT

检查约束

保证字段值满足某一个条件

CHECK

外键约束

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

FOREIGEN KEY

注意：约束是作用于表中字段的，可以在创建表和修改表是增加

4.2增加约束

4.2.1 创建表时增加约束-案例分析

在这里插入图片描述

create table teachers(
    id int primary key  auto_increment comment 'id',
    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 '驾驶表';

4.2.2 对已有的表增加约束

增加主键/唯一约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名) / UNIQUE(字段名)
增加非空/自动增长/默认约束(修改字段的数据类型)
ALTER TABLE 表名 MODIFY 字段名数据类型 NOT NULL/AUTO_INCREMENT/DEFAULT '默认值'
增加检查约束
ALTER TABLE 表名 ADD CONSTRIANT 字段名 check（条件）

4.3 外键约束

4.3.1 增加外键

创建表时增加外键
对已经创建过的表增加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表（主表列名）

4.3.2 删除外键

ALTER TABLE 表名 DROP CONSTRAINT 外键名称

4.3.3 外键删除和更新规则（行为）

在这里插入图片描述
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表（主表列名）ON DELETE 规则 ON UPDATE 规则

5.多表查询

5.1 多表查询-概述

5.1.1 多表关系分类

在这里插入图片描述

5.1.2 多表查询-分类

连接查询
- 内连接：查询两表交集部分数据
- 外连接
  - 左外连接：查询左表所有数据，以及两表交集部分数据
  - 右外连接：查询右表所有数据，以及两表交集部分数据
- 自连接：当前表与自身连接查询，自连接必须使用别名
子查询

5.2 多表查询-连接查询

5.2.1 连接查询-内连接

内连接查询语法：

隐式内连接
SELECT 字段列表 FROM 表一，表二 WHERE 条件
显示内连接
SELECT 字段列表 FROM 表一 [INNER] JOIN 表二 ON 连接条件

5.2.2 连接查询-外连接

在这里插入图片描述

5.2.3 连接查询-自连接

在这里插入图片描述

5.3 联合查询

在这里插入图片描述
注意：

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
union all 会将所有的数据直接合并在一起，union 会对合并之后的数据去重

5.4 子查询

在这里插入图片描述

5.4.1 标量子查询

子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式
常用的操作符：= <> > >= < <=

案例一
案例二

5.4.2 列子查询

子查询返回的结果是一列（可以是多行）
常用的操作符： IN、NOT、ANY、SOME、ALL
在这里插入图片描述

案例一
案例二
案例三

5.4.3 行子查询

子查询返回的结果是一行（可以是多列）
常用的操作符： =、<>、IN、NOT IN

案例一

5.4.4 表子查询

子查询返回的结果是多行多列
常用的操作符： IN

案例一
案例二

注意：
如果返回的子表需要作为一张新的表被查询，要给这张子表起别名，否则会Every derived table must have its own alias报错。

6.事务

6.1 事务简介

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

开启事务
如果执行没有异常，则提交事务
如果执行中出现异常，回滚事务，退到执行事务前。
默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式地提交事务。

事务四大特性（ACID）

在这里插入图片描述

6.2 事务操作

方式一
方式二

6.3 并发事务问题

在这里插入图片描述

脏读
不可重复读
幻读

6.4 事务的隔离级别

在这里插入图片描述

隔离级别越高数据越安全，执行效率越低。

第二部分进阶篇

1.存储引擎

1.1 MySql体系结构

在这里插入图片描述

1.2 存储引擎

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

1.3 存储引擎的特点

1.3.1 InnoDB

在这里插入图片描述

1.3.2 MyISAM

在这里插入图片描述

1.3.3 Memory

在这里插入图片描述

1.3.4 区别

在这里插入图片描述

1.3.5 存储引擎的选择

在这里插入图片描述

2.索引

2.1 索引-概述

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

在这里插入图片描述

2.2 索引结构

2.2.1 B+Tree索引

在这里插入图片描述

2.2.2 Hash索引

在这里插入图片描述

2.3 索引分类

在这里插入图片描述

回表查询：先通过二级索引找到主键，然后通过主键

2.4 索引-语法

在这里插入图片描述

案例

2.5 索引-性能分析

2.5.1 SQL执行频率

在这里插入图片描述
（7个下划线）

2.5.2 慢查询日志

在这里插入图片描述
慢查询日志需要手动打开

方式二：进入MySQL控制台，用命令行设置

mysql -u root -p

#查看一下默认为慢查询的时间10秒
show variables like "%long%";
#设置成2秒
set global long_query_time=2;
#查看一下慢查询是不是已经开启
show variables like "%slow%";
#启用慢查询
set global slow_query_log='ON';

/usr/local/mysql/data/localhost-slow.log

2.5.3 show profiles

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling ;
可以通过set语句在session/global级别开启profiling：
set profiling = 1;
开关打开后，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。

#查看每一条SQL的耗时基本情况
show profiles;
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

2.5.4 explain执行计划

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：(直接在select语句前面加上关键字explain/desc)
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
在这里插入图片描述

Explain 执行计划中各个字段的含义:

字段

含义

select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。

select_type

表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等

type

表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。

possible_key

显示可能应用在这张表上的索引，一个或多个。

key

实际使用的索引，如果为NULL，则没有使用索引。

key_len

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows

MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

MySql从入门到精通

第一部分 基础篇

1.概述

1.1 启动与停止MySql

1.2 客户端连接

1.3 CentOS下MYSQL的登录、启动、停止、重启命令

MySql登录

MySql配置文件

在CentOS根目录下执行命令

启动

停止

重启

状态查询

2.SQL语句

2.1 DDL 用来定义数据库对象(数据库，表， 字段)

2.1.1 DDL-数据库操作

2.1.2 DDL-表-操作

2.1.3 DDL-表-增删修改

对字段进行操作

对表进行操作

2.2 DML 用来对数据库表中的数据进行增删改

2.2.1 DML-添加数据

2.2.2 DML-修改数据

2.2.3 DML-删除数据

2.3 DQL 用来查询数据库中表的记录

2.3.1 DQL-基础查询

2.3.2 DQL-条件查询

2.3.3 DQL-聚合函数

2.3.4 DQL-分组查询

2.3.5 DQL-排序查询

2.3.5 DQL-分页查询

2.3.6 DQL-执行顺序

2.4 DCL 用来创建数据库用户、控制数据库的访问权限

2.4.1 DCL-用户管理

2.4.2 DCL-权限控制

3.函数

3.1 字符串函数

3.2 数值函数

3.3 日期函数

3.4 流程控制函数

4.约束

4.1 约束-概述

4.2增加约束

4.2.1 创建表时增加约束-案例分析

4.2.2 对已有的表增加约束

4.3 外键约束

4.3.1 增加外键

4.3.2 删除外键

4.3.3 外键删除和更新规则（行为）

5.多表查询

5.1 多表查询-概述

5.1.1 多表关系分类

5.1.2 多表查询-分类

5.2 多表查询-连接查询

5.2.1 连接查询-内连接

5.2.2 连接查询-外连接

5.2.3 连接查询-自连接

5.3 联合查询

5.4 子查询

5.4.1 标量子查询

5.4.2 列子查询

5.4.3 行子查询

5.4.4 表子查询

6.事务

6.1 事务简介

事务四大特性（ACID）

6.2 事务操作

6.3 并发事务问题

6.4 事务的隔离级别

第二部分 进阶篇

1.存储引擎

1.1 MySql体系结构

1.2 存储引擎

1.3 存储引擎的特点

1.3.1 InnoDB

1.3.2 MyISAM

1.3.3 Memory

1.3.4 区别

1.3.5 存储引擎的选择

2.索引

第一部分基础篇

2.1 DDL 用来定义数据库对象(数据库，表，字段)

第二部分进阶篇