一、开胃菜：认识MySQL

1.1 安装MySQL

1.2 使用MySQL 之登录与退出命令

1.3 使用MySQL 之常见到的符号

1.4 使用MySQL 之修改MySQL 提示符

1.5 SQL语句介绍及编码规范

1.6 使用MySQL 之认识字符集

二、正菜：上手使用MySQL

2.1 使用MySQL之常用命令及建库语句

2.2 MySQL 数据类型之数值型

2.3 MySQL 基础数据类型之日期和时间类型

2.4 MySQL 基础数据类型之字符串类型

2.5 MySQL 数据表操作之建立数据表

2.6 MySQL 数据表操作之查看数据表

2.7 MySQL 数据表操作之查看数据表结构

2.8 MySQL 数据表操作之记录的操作与查找

2.9 MySQL 基础数据类型之超出范围处理

2.10 MySQL 字段约束

2.11 MySQL 字段约束之主键约束

2.12 MySQL 字段约束之唯一约束

2.13 MySQL 字段约束之外键约束

2.14 MySQL 字段约束之外键约束的要求

2.15 MySQL 字段约束之外键约束参照操作

2.16 MySQL 字段操作之修改数据表

2.17 MySQL 字段操作之修改数据表

2.18 MySQL 字段操作之修改数据表

三、MySQL8 操作数据表中的记录

3.1 MySQL 插入数据INSERT

3.2 MySQL 单表更新记录 UPDATE

3.3 MySQL 单表删除记录DELETE

3.4 MySQL 数据操作之查询表达式解析 SELECT

3.5 MySQL 数据操作之WHERE 语句进行条件查询

3.6 MySQL 数据操作之 GROUP BY 语句对查询结果分组

3.7 MySQL 数据操作之 HAVING 语句设置分组条件

3.8 MySQL 数据操作之 ORDER BY 语句对查询结果排序

3.9 MySQL 数据操作之 LIMIT 语句限制查询数量

四、MySQL 数据操作之子查询简介

4.1 MySQL 数据操作之由比较运算符引发的子查询

4.2 MySQL 数据操作之由 [NOT] IN EXISTS 引发的子查询

4.3 MySQL 数据操作之多表更新

4.4 MySQL 数据操作之多表更新一步到位

五、MySQL 数据操作之连接的语法结构

5.1 MySQL 数据操作之内连接 INNER JOIN

5.2 MySQL 数据操作之外连接 OUTER JOIN

5.3 MySQL 数据操作之外连接 OUTER JOIN

5.4 MySQL 数据操作之多表连接

5.5 MySQL 数据操作之关于连接的几点说明

5.6 MySQL 数据操作之自身连接查询

5.7 MySQL 数据操作之多表删除

六、MySQL8 运算符、函数、存储过程及新增数据类型

6.1 MySQL8 字符函数

6.2 MySQL8 中数值运算符与函数

6.3 MySQL8 比较运算符与函数

6.4 MySQL8 日期时间函数

6.5 MySQL8 信息函数

6.6 MySQL8 聚合函数

6.7 MySQL8 加密函数

6.8 MySQL8 自定义函数

6.9 MySQL8 创建带有参数的函数

6.10 MySQL8 创建具有复合结构函数体的自定义函数

七、MySQL8 之存储过程简介

7.1 MySQL8 之存储过程语法结构解析

7.2 MySQL8 之创建不带参数的存储过程

7.3 MySQL8 之存储过程创建带有IN类型参数的存储过程

7.4 MySQL8之创建带有IN 和OUT参数存储过程

7.5 MySQL8之存储过程创建带有多个OUT 类型参数的存储过程

7.6 MySQL8 之存储过程与自定义函数的区别

八、MySQL8 新增JSON数据类型

8.1 MySQL8 之新增JSON 类型

九、MySQL8 之认识存储引擎

十、MySQL8 之锁策略

十一、MySQL8 之事务处理

十二、MySQL8 数据库之 - 主从复制

12.1 MySQL8 基于日志点复制

12.2 MySQL8 基于GTID的复制

十三、MySQL8 数据库操作之备份、还原

十四、MySQL8 数据库操作之数据库工具的介绍

【免费赠送】个人整理关于MySQL知识的思维导图：MySQL_思维导图(全面).xmind.zip

【免费赠送】个人整理关于MySQL索引的PPT：MySQL索引原理及如何建立高效索引.pptx

一、开胃菜：认识MySQL

MySQL 是一个关系型数据库管理系统
1. 关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL 由瑞典MySQL AB 公司开发
后期被Oracle 收购，属于Oracle 旗下产品
MySQL 是最流行的关系型数据库管理系统之一
1. 在WEB 应用方面，MySQL 是最好的关系型数据库管理系统应用软件之一。
MySQL 是一款开源、免费的数据库管理系统
MySQL 分为社区版与企业版

1.1 安装MySQL

安装文件下载：https://dev.mysql/downloads/mysql/
Linux版本MySQL8安装
Windows版本MySQL8安装

1.2 使用MySQL 之登录与退出命令

登录MySQL命令：
1. mysql [-h 数据库服务器地址] -u 用户名 -p 密码
退出命令
1. \q
2. exit
3. quit
认识登录成功提示
1. Welcome to the MySQL monitor.(欢迎来到mysql终端)
2. Command end with ; or \g. (命令执行符是 ; 或者 \g)
3. Your MySQL connection id is 18 (mysql 被链接次数)
4. Server version: 8.0.16 MySQL Community Server - GPL (mysql 版本号)
5. Type 'help;' or '\h' for help. (mysql帮助命令\h 或者help)
6. Type '\c' to clear the current input statement. (清除等待命令\c)

1.3 使用MySQL 之常见到的符号

-> 表示当前命令没有命令执行符或者说等待命令执行符来确认
'> 表示当前的SQL 命令缺少单引号
"> 表示当前的SQL命令缺少双引号

1.4 使用MySQL 之修改MySQL 提示符

参数	描述
\D	完整的日期
\d	当前数据库
\h	服务器名称
\u	当前用户

连接客户端时通过参数指定
- mysql -u root -p 密码 --prompt 提示符
连接上客户端后，通过prompt 命令修改
- PROMPT 提示符
- 例如：PROMPT \u@\h \d>当前用户@服务器名称当前数据库

1.5 SQL语句介绍及编码规范

SQL 语句结构化查询语句，主要分为4大类：

DDL 数据库定义语言（CREATE、DROP、ALTER等）
DML 数据库操纵语言（INSERT、DELETE、UPDATE等）
DQL 数据库查询语言（SELECT、WHERE等）
DCL 数据库控制语言（了解）(GRANT、REVOKE等)

编码规范【必须记住、要求】：

关键字与函数名称全部大写
数据库名称、表名称、字段名称全部小写
SQL 语句必须以分号结尾

1.6 使用MySQL 之认识字符集

常用中文字符集
字符集类型	占用空间大小	描述
GB2312	双字节编码	早期标准，不推荐使用
GBK	双字节编码	中期标准，不是国际，但支持系统很多，而且GB2312基础上增加了很多偏僻生字
UTF-8	1~4字节的编码	互联网广泛使用，亚洲通用字符集；国际标准化，支持任何语言，在MySQL中写成utf8; 在MySQL8中写成utf8mb4，老版本之前是utf8mb3

utf-8 与 gbk 的区别

存储长度不一样，GB系统一个汉字占位2个字节，utf-8占位3个字节
推荐使用UTF-8 (支持语言更多)

数据库字符集依赖关系

内容字符集 --> 字段字符集 --> 表字符集 --> 库字符集

二、正菜：上手使用MySQL

2.1 使用MySQL之常用命令及建库语句

显示当前服务器版本

SELECT VERSION();

显示当前用户

SELECT USER();

显示当前日期时间

SELECT NOW();

建库语句（DDL）

CREATE {DATABASE | SCHEMA}   [IF NOT EXISTS]   db_NAME
[DEFAULT]   CHARACTER SET  [=]  charset_name

显示数据库创建命令（DDL）

SHOW CREATE DATABASE 库名；

修改数据库

ALTER  {DATABASE  |  SCHEMA}   [db_name]  [DEFAULT]  CHARACTER  SET  [=]  charset_name

删库语句（DDL）

DROP   {DATABASE  |  SCHEMA}  [IF EXISTS]  db_NAME

查看数据库命令

SHOW DATABASES;

2.2 MySQL 数据类型之数值型

MySQL 数据类型之数值型汇总
序号	数据列类型	存储空间	说明	取值范围
1	TINYINT	1字节	非常小的整数	带符号值：-128 ~ 127 无符号值：0 ~ 255
2	SMALLINT	2字节	较小的整数	带符号值：-32 768 ~ 32767 无符号值：0 ~ 65535
3	MEDIUMINT	3字节	中等大小的整数	带符号值：-8 388 608 ~ 8 388 607 无符号值：0 ~ 16 777 215
4	INT	4字节	标准整数	带符号值：-2 147 483 648 ~ 2 147 483 647 无符号值：0 ~ 4 294 967 295
5	BIGINT	8字节	大整数	带符号值：-9 223 372 036 854 775 808 ~ 9233 372 036 854 775 807 无符号值：0 ~ 18 446 744 073 709 551 615
6	FLOAT	4字节	单精度浮点数	最小非零值： ±1.175494351E-38 最大非零值： ±3.402823466E+38
7	DOUBLE	8字节	双精度浮点数	最小非零值： ±2.2250738585072014E-308 最大非零值： ±1.7976931348623157E+308
8	DECIMAL	自定义	以字符串形式表示的浮点数	取决于存储单元字节数，安全/并不会四舍五入【在金融跟金钱相关的时间，建议使用】

实操数值型SQL示例：

CREATE DATABASE my_db;
USE my_db;
DROP TABLE IF EXISTS int_demo_table;
CREATE TABLE int_demo_table (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键key，【表必须字段】',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，【表必须字段】',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间，【表必须字段】',
  
  tinyint_field TINYINT,
  smallint_field SMALLINT,
  mediumint_field MEDIUMINT,
  int_field INT,
  bigint_field BIGINT,
  float_field FLOAT,
  double_field DOUBLE,
  decimal_field DECIMAL(10,2)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO int_demo_table (tinyint_field,smallint_field,mediumint_field,int_field,bigint_field,float_field,double_field,decimal_field) 
VALUES(1,2,3,4,5,6.66666,7.77777,8.88888);
SELECT * FROM int_demo_table;

运行示例结果截图：

2.3 MySQL 基础数据类型之日期和时间类型

一般我们是使用程序，如：Java、PHP程序获取时间后，将时间存入MySQL中。MySQL底层还是使用数值类型进行存储，便于计算。

MySQL 基础数据类型之日期和时间类型汇总
序号	类型	存储空间	说明	最大长度	说明
1	DATE	3字节	"YYYY-MM-DD" 格式表示的日期值	1000-01-01 ~ 9999-12-31
2	TIME	3字节	"hh:mm:ss" 格式表示的时间值	-835:59:59 ~ 838:59:59
3	DATETIME	8字节	"YYYY-MM-DD hh:mm:ss" 格式	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
4	TIMESTAMP	4字节	"YYYYMMDDhhmmss" 格式表示的时间戳	19700101000000 ~ 2037年的某个时刻	一般存储最多，便于计算。如计算5分钟前
5	YEAR	1字节	"YYYY" 格式的年份值	1901 ~ 2155	5.7 就已弃用

实操日期和时间类型SQL示例：

CREATE DATABASE my_db;
USE my_db;

CREATE TABLE IF NOT EXISTS date_time_table (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键key，【表必须字段】',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，【表必须字段】',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间，【表必须字段】',
    
  date_field DATE,
  time_field TIME,
  datetime_field DATETIME,
  timestamp_field TIMESTAMP,
  year_field YEAR
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO date_time_table (date_field, time_field, datetime_field, timestamp_field, year_field) 
VALUES(NOW(), NOW(), NOW(),NOW(), NOW());

示例运行结果截图：

2.4 MySQL 基础数据类型之字符串类型

MySQL 基础数据类型之字符串类型汇总
序号	类型	存储空间	说明	最大长度
1	CHAR[ (M) ]	M 字节	定长字符串	0 ~ 255
2	VARCHAR[ (M) ]	L + 1 字节	可变长字符串，L实际占用存储空间	65535
3	TINYBOLD, TINYTEXT	L + 1 字节	非常小的BLOB（二进制数大对象）和文本串	2^8 - 1 字节
4	BLOB, TEXT	L + 2 字节	小的BLOB和文本串	2^16 - 1 字节
5	MEDIUMBLOB, MEDIUMTEXT	L + 3 字节	中等的BLOB和文本串	2^24 - 1 字节
6	LONGBLOB, LONGTEXT	L + 4 字节	大的BLOB和文本串	2^32 - 1 字节
7	ENUM ( 'value1', 'value2', ...)	1 或 2 字节	枚举：可赋予某个枚举成员	65 535 个成员
8	SET ( 'value1', 'value2', ...)	1、2、3、4或8字节	集合：可赋予多个集合成员	64个成员

补充说明：

当我们能确定字符串长度时，使用CHAR，不能确定字符串长度时使用 VARCHAR
CHAR 存储的字符串长度不足时，会以空格填充；VARCHAR 则不会，是多少就是多少
CHAR 的查询效率要比 VARCHAR 要高
BLOB 存储的是二进制，一般是存储图片、视频
能存数字，尽量存数字；比如性别0代表男，1代表女
电话号码，不参与运算，存储字符串即可

实操字符类型SQL示例：

CREATE TABLE IF NOT EXISTS char_table_demo (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键key，【表必须字段】',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，【表必须字段】',
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间，【表必须字段】',
  
  char_field CHAR(10),
  varchar_field VARCHAR(20),
  tinytext_field TINYTEXT,
  blob_field BLOB,
  text_field TEXT,
  mediumblob_field MEDIUMBLOB,
  mediumtext_field MEDIUMTEXT,
  longblob_field LONGBLOB,
  longtext_field LONGTEXT,
  enum_field ENUM('small', 'big'),
  set_field SET('a1', 'b2', 'c3')
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO char_table_demo (char_field, varchar_field, tinytext_field, blob_field, text_field, mediumblob_field, mediumtext_field, longblob_field, longtext_field, enum_field, set_field)
VALUES('1', '2', '3', '4', '5', '6', '7', '8', '9', 'small', 'a1');
SELECT * FROM char_table_demo;

运行示例截图：

MYSQL类型与JAVA类型对应表【重要】
类型名称	显示长度	数据库类型	JAVA类型	JDBC类型索引(int)
VARCHAR	L+N	VARCHAR	java.lang.String	12
CHAR	N	CHAR	java.lang.String	1
BLOB	L+N	BLOB	java.lang.byte[]	-4
TEXT	65535	VARCHAR	java.lang.String	-1

INTEGER	4	INTEGER UNSIGNED	java.lang.Long	4
TINYINT	3	TINYINT UNSIGNED	java.lang.Integer	-6
SMALLINT	5	SMALLINT UNSIGNED	java.lang.Integer	5
MEDIUMINT	8	MEDIUMINT UNSIGNED	java.lang.Integer	4
BIT	1	BIT	java.lang.Boolean	-7
BIGINT	20	BIGINT UNSIGNED	java.math.BigInteger	-5
FLOAT	4+8	FLOAT	java.lang.Float	7
DOUBLE	22	DOUBLE	java.lang.Double	8
DECIMAL	11	DECIMAL	java.math.BigDecimal	3
BOOLEAN	1	同TINYINT

ID	11	PK (INTEGER UNSIGNED)	java.lang.Long	4

DATE	10	DATE	java.sql.Date	91
TIME	8	TIME	java.sql.Time	92
DATETIME	19	DATETIME	java.sql.Timestamp	93
TIMESTAMP	19	TIMESTAMP	java.sql.Timestamp	93
YEAR	4	YEAR	java.sql.Date	91

2.5 MySQL 数据表操作之建立数据表

数据表示数据库最重要的组成部分之一，是其他对象的基础。

1、打开数据库

-- 命令： USE 数据库名称;
use my_db;

2、查看当前所在的数据库

SELECT DATABASE();

3、创建数据表

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name1 date_type,
    column_name2 date_type,
    ......
) [ENGINE=表引擎 [DEFAULT] CHARSET=utf8]

示例：

-- 创建学生表
CREATE TABLE IF NOT EXISTS `student`(
    `id` BIGINT UNSIGNED AUTO_INCREMENT COMMENT '自增主键key，【表必须字段】',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，【表必须字段】',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间，【表必须字段】',
  	
    `s_id` VARCHAR(20) COMMENT '学生编号',
    `s_name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

2.6 MySQL 数据表操作之查看数据表

查看数据表列表

SHOW TABLES [FROM db_name];

2.7 MySQL 数据表操作之查看数据表结构

查看数据表结构

DESC table_name;
SHOW COLUMNS FROM table_name;

2.8 MySQL 数据表操作之记录的操作与查找

插入数据

INSERT [INTO] table_name [(col_name, ...)] VALUES (val, ...)

查找数据

SELECT * FROM table_name;
SELECT col_name, ... FROM table_name;

示例：

-- 插入学生表测试数据
INSERT INTO student (`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO student (`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO student (`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO student (`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('04' , '李云' , '1990-08-06' , '男');
INSERT INTO student (`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO student (`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO student (`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO student (`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('08' , '王菊' , '1990-01-20' , '女');

2.9 MySQL 基础数据类型之超出范围处理

有些时候，在创建表时定义的字段类型有一定的范围限制，如：大小范围、空间范围，如果向表中插入超出范围的数据，默认是会报错的，当然也可以关闭严格模式。

严格SQL模式【默认】

SET sql_mode = 'TRADITIONAL';

关闭SQL严格模式

SET sql_mode='';

2.10 MySQL 字段约束

有时只定义了字段的数据类型还不够，还要设置其他一些附加属性，如自动增量的设置、自动补0的设置和默认值的设置等一些特殊的设置。下面具体介绍这些特殊字段的属性。

1、UNSIGNED

该属性只能用于设置数据类型，不允许数据列出现负数。如果不需要向某字段中插入负数，则使用该属性修饰可以使用该字段的最大存储长度增加一倍。例如，正常情况下数据类型TINYINT的数值范围在 -128 ~ 127，而使用UNSIGNED 属性修饰以后最小值为0，最大值可以达到255。

2、ZEROFILL

该属性也只能用于设置数值类型，在数值之前自动用0补齐不足的位数。例如，将5插入一个声明为 int(3) ZEROFILL的字段，在之后查询输出时，输出的数据将是 "005" 。当给一定字段使用ZEROFILL修饰时，该字段自动应用UNSIGNED 属性。

3、AUTO_INCREMENT

该属性用于设置字段的自动增量属性，当数值类型的字段设置为自动增量时，每增加一条新记录，该字段的值就自动加1，而且此字段的值不允许重复。此修饰符只能修饰整数类型的字段。插入新记录时自增字段可以为NULL、0或留空，这时自增字段自动使用上次此字段的值加1，作为此次的值。插入时也可以为自增字段指定某一非零数值，这时，如果表中已经存在此值将出错；否则使用指定数值作为自增字段的值，并且下次插入时，下个字段的值将在此值的基础上加1。

4、NULL 和 NOT NULL

默认为NULL，即没有在此字段插入值。如果指定了NOT NULL，则必须在此字段插入值。

5、DEFAULT

可以通过此属性来指定一个默认值，如果没有在此列添加值，那么默认添加此值。例如，在学生表 student 中，可以将性别字段的默认值设置为 "男"。在为该列插入数据时，只在当用户为 "女" 时才需要指定，否则可以不为该字段指定值，默认值就为 "男"。

2.11 MySQL 字段约束之主键约束

主键约束 PRIMARY KEY
每张数据表中只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL
AUTO_INCREMENT 必段和 PRIMARY KEY 一起使用，但 PRIMARY KEY 不需要

2.12 MySQL 字段约束之唯一约束

唯一约束 UNIQUE KEY
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值（NULL）
每张数据表可以存在多个唯一约束

2.13 MySQL 字段约束之外键约束

约束保证数据的完整性和一致性
约束分为表约束和列级约束
约束类型包括
1. NOT NULL（非空约束）
2. PRIMARY KEY （主键约束）
3. UNIQUE KEY（唯一约束）
4. DEFAULT （默认约束）
5. FOREIGN KEY （外键约束）
外键约束 FOREIGN KEY
1. 保持数据一致性，完整性。
2. 实现一对一或一对多关系。

2.14 MySQL 字段约束之外键约束的要求

父表和子表必须使用相同的存储引擎，而且禁止使用临时表
数据表的存储引擎只能为 InnoDB
外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同
外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引
查看索引：

SHOW INDEXES FROM table_name;

2.15 MySQL 字段约束之外键约束参照操作

CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
1. 格式：ON UPDATE | DELETE CASCADE
SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项必须保证子表列没有指定NOT NULL
RESTRICT：拒绝对附表的删除或更新操作
NO ACTION：标准SQL的关键字，在 MySQL中与 RESTRICT 相同

2.16 MySQL 字段操作之修改数据表

添加单列

ALTER TABLE table_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];

添加多列

ALTER TABLE table_name ADD [COLUMN] (col_name column_definition, ...)

删除列

ALTER TABLE table_name DROP [COLUMN] col_name;

删除多列

ALTER TABLE table_name DROP [COLUMN] col_name, DROP [COLUMN] col_name ...;

添加主键约束

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name);

添加唯一约束

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name, ...);

添加外键约束

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY (index_name) REFERENCES ftab_name (index_name);

2.17 MySQL 字段操作之修改数据表

添加 / 删除默认约束

ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};

删除主键约束

ALTER TABLE table_name DROP PRIMARY KEY;

删除唯一约束

ALTER TABLE table_name DROP {INDEX | KEY} index_name;

2.18 MySQL 字段操作之修改数据表

修改列定义

ALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];

修改列名称

ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];

修改数据表

方法1：

ALTER TABLE table_name RENAME [TOIAS] new_tab_name;

方法2：

RENAME TABLE table_name TO new_table_name [table_name2 TO new_tab_name2]...

三、MySQL8 操作数据表中的记录

3.1 MySQL 插入数据INSERT

插入记录

方法1：

INSERT [INTO] table_name [(col_name, ...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...);

方法2：

INSERT [INTO] table_name SET col_name={expr | DEFAULT},...

说明：与第一种方式的区别在于此方法可以使用子查询（SubQuery)

方法3：

INSERT [INTO] table_name [col_name,...] SELECT ...

说明：此方法可以将查询结果结构插入到指定数据表中

3.2 MySQL 单表更新记录 UPDATE

更新记录（单表更新）

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}] ... [WHERE where_condition];

3.3 MySQL 单表删除记录DELETE

删除纪录（单表删除）

DELETE FROM table_name [WHERE where_condition];

3.4 MySQL 数据操作之查询表达式解析 SELECT

查询表达式

每个表达式表示想要的一列，必须有至少一个。

多个列之间以英文逗号分隔。

星号（*）表示所有列。table_name.* 可以表示该表名的所有列。

查询表达式可以使用 [AS] 为其赋值别名。

别名可用于GROUP BY，ORDER BY 或 HAVING 子句。

查找记录，顺序非常严格，必段遵守

SELECT select_expr [, select_expr ...]
[
    FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | position} [ASC | DESC], ...]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    
]

3.5 MySQL 数据操作之WHERE 语句进行条件查询

条件表达式

对记录进行过滤，如果没有指定 WHERE 子句，则显示所有记录。

在 WHERE 表达式中，可以使用 MySQL 支持的函数或者运算符。

3.6 MySQL 数据操作之 GROUP BY 语句对查询结果分组

查询结果分组

[ GROUP BY {col_name | position} [ASC | DESC], ... ]

3.7 MySQL 数据操作之 HAVING 语句设置分组条件

分组条件：在分组后作为帅选条件

[ HAVING where_condition]
SELECT age FROM user GROUP BY age HAVING count(id) > 2;

3.8 MySQL 数据操作之 ORDER BY 语句对查询结果排序

对查询结果进行排序

[ORDER BY {col_name | expr | position} [ASC | DESC],...]

SELECT * FROM user ORDER BY age ASC, id DESC;

3.9 MySQL 数据操作之 LIMIT 语句限制查询数量

限制查询结要返回的数量；使用：经常用于分页显示效果。

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

SELECT * FROM user LIMIT 3;
SELECT * FROM user LIMIT 3,4;

四、MySQL 数据操作之子查询简介

子查询（Subquery）是指出现在其他SQL 语句内的 SELECT 子句。

例如：

SELECT * FROM table1 WHERE col1= (SELECT col2 FROM table2);
其中 SELECT * FROM table1, 称为 Outer Query / Outer Statement
SELECT col2 FROM table2, 被称为SubQuery。

4.1 MySQL 数据操作之由比较运算符引发的子查询

使用比较运算符的子查询：=、>、<、>=、<=、<>、!=、<=>

语法结构

operand comparison_operator subquery

用 ANY、SOME或 ALL 修饰的比较运算符

operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)

ANY、SOME、ALL关键字
运算符/关键字	ANY	SOME	ALL
> 、 >=	最小值	最小值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值
<>、!=			任意值

4.2 MySQL 数据操作之由 [NOT] IN EXISTS 引发的子查询

语法结构

operand comparison_operator [NOT] IN (subquery)
=ANY 运算符与 IN 等效
!= ALL 或者 <> ALL 运算与NOT IN 等效

使用 [NOT] EXISTS 的子查询，如果子查询返回任何行，EXISTS 将返回TRUE；否则为 FALSE。

4.3 MySQL 数据操作之多表更新

UPDATE table_references SET col_name1 = {expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}] ... [WHERE where_condition];

4.4 MySQL 数据操作之多表更新一步到位

创建数据表同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] table_name 
[(create_definition, ...)]
select_statement;

CREATE TABLE class (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    className VARCHAR(20) NOT NULL
) SELECT className FROM user GROUP BY className;

五、MySQL 数据操作之连接的语法结构

MySQL 在SELECT 语句、多表更新、多表删除语句中都支持 JOIN 操作，语法结构

table_reference 
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

连接类型

INNER JOIN, 内连接
在 MySQL 中，JOIN、CROSS JOIN 和 INNER JOIN 是等价的。
LEFT [OUTER] JOIN，左外连接
RIGHT [OUTER] JOIN，右外连接

连接条件

使用 ON 关键字来设定连接条件，也可以使用 WHERE 来代替。
通常使用 ON 关键字来设定连接条件。
使用 WHERE 关键字进行结果集记录的过滤。

5.1 MySQL 数据操作之内连接 INNER JOIN

内连接：显示左表及右表符合连接条件的记录

SELECT user.id, user.username, class.className as cName FROM user INNER JOIN class ON user.id = class.id;

5.2 MySQL 数据操作之外连接 OUTER JOIN

左外连接：显增左表的全部记录及右表符合连接条件的记录，如果右表没有匹配上的字段显示为NULL

SELECT u.id, u.username, c.className FROM user AS u LEFT JOIN class AS c ON u.cid = c.id;

5.3 MySQL 数据操作之外连接 OUTER JOIN

右外连接：显示右表的全部记录及左表符合连接条件的记录，如果左表没有匹配的记录显示为NULL

SELECT u.id, u.username, c.className FROM user AS u RIGHT JOIN class AS c ON u.cid = c.id;

5.4 MySQL 数据操作之多表连接

SELECT u.id, u.username, u.pwd, u.sex, u.age, u.eduction, c.className, p.name, u.price 
FROM user 
AS u INNER JOIN CLASS as c ON c.id = u.cid INNER JOIN province AS p ON p.id = u.proid;
-- 等价于下条多WHERE 查询 SQL
SELECT u.id, u.username, u.pwd, u.sex, u.age, u.eduction, c.className, p.name, u.price 
FROM user 
AS u.class AS c.province as p WHERE u.cid = c.id AND u.proid = p.id;

5.5 MySQL 数据操作之关于连接的几点说明

A LEFT JOIN B join_condition

数据表B 的结果集依赖数据表A
数据表A 的结果集根据左连接条件依赖所有数据表（B表除外）
左外连接条件决定如何检索数据表B（在没有指定WHERE 条件的情况下）
如果数据表A 的某条记录符合 WHERE 条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。
如果使用内连接查找记录在连接数据表中不存在，并且在 WHERE 子句中尝试以下操作：col_name IS NULL时，如果col_name 被定义为NOT NULL，MySQL 将在找到符合连接执行条件的记录后停止搜索更多的行。

5.6 MySQL 数据操作之自身连接查询

同一个数据表对其自身进行连接

SELECT s.id, s.name, p.name FROM class_type AS s LEFT JOIN class_type AS s ON s.pid = p.id;

5.7 MySQL 数据操作之多表删除

DELETE table_name[.*] [, table_name[.*]] ... FROM table_references [WHERE where_condition]

DELETE t1 FROM user AS t1 LEFT JOIN (SELECT id, username 
FROM user GROUP BY username HAVING count(username) > 1) AS t2 ON t1.username = t2.username WHERE t1.id > t2.id;

六、MySQL8 运算符、函数、存储过程及新增数据类型

6.1 MySQL8 字符函数

MySQL8 字符函数
序号	函数名称	描述	示例
1	CONCAT()	字符连接	SELECT CONCAT(id, '-', age) AS path FROM user; SELECT CONCAT('id', '-', 'name')
2	CONCAT_WS()	使用指定的分割符进行字符串连接	SELECT CONCAT_WS('/', 'id', 'name', 'age', 'sex', 'phone')
3	FORMAT()	数字格式化	SELECT FORMAT(123123.567, 1)
4	LOWER()	转换成小写字母	SELECT LOWER('MySQL')
5	UPPER()	转换成大写字母	SELECT UPPER('MySQL')
6	LEFT()	获取左侧字符	SELECT LEFT('hello', 2)
7	RIGHT()	获取右侧字符	SELECT RIGHT('hello', 2)
8	LENGTH()	获取字符串长度	SELECT LENGTH('just do it')
9	LTRIM()	删除前导空格	SELECT LTRIM(' hello')
10	RTRIM()	删除后续空格	SELECT LTRIM('hello ')
11	TRIM()	删除前导和后续空格	SELECT TRIM(' hello ') SELECT TRIM(leading '?' FROM '???hello???'); -- 删除前导问号 SELECT TRIM(TRAILING '?' FROM '???hello???'); -- 删除后续问号 SELECT TRIM(BOTH '?' FROM '???hello???'); -- 删除前导和后续问号
12	SUBSTRING()	字符串截取	SELECT SUBSTRING('hello', 2) -- 返回 ello
13	[NOT] LIKE	模式匹配	SELECT 'world' LIKE '%r%' SELECT * FROM user WHERE username LIKE '%1%%' ESCAPE 1;
14	REPLACE()	字符串替换	SELECT REPLACE('hello?world', '?', '')

6.2 MySQL8 中数值运算符与函数

MySQL8 中数值运算符与函数
序号	名称	描述	示例
1	CEIL()	进一取整	SELECT CEIL(3.001); -- 答案是4，只要小数点后面有不为0的数字则进一
2	DIV	整数除法	SELECT 10 DIV 3; -- 答案是3
3	FLOOR()	舍去法取整	SELECT FLOOR(3.99); -- 答案是9
4	MOD	取余数（取模）	SELECT 10 % 3; -- 答案是1；相当于 SELECT 10 MOD 3;
5	POWER()	幂运算	SELECT POWER(2,3); -- 答案是8，2的3次方
6	ROUND()	四舍五入	SELECT ROUND(3.1415926, 2); -- 答案是3.14
7	TRUNCATE()	数字截取	SELECT TRUNCATE(123.567, 2); -- 答案是123.56；第二个参数可正（小数部分）可负（整数部分）

6.3 MySQL8 比较运算符与函数

MySQL8 比较运算符与函数
序号	名称	描述	示例
1	[NOT] BETWEEN ... AND ...	【不】在范围之内	SELECT 100 BETWEEN 100 AND 200; -- 返回1 ，是否在【100，200】之间
2	[NOT] IN()	【不】在列出值范围内	SELECT 1 IN (2, 3, 4, 5, 6); -- 查询1是否在2, 3, 4, 5, 6当中 SELECT * FROM user WHERE id IN(1,3,5); -- 查询id为1，3，5的用户信息
3	IS [NOT] NULL	【不】为空	SELECT NULL IS NULL; SELECT * FROM user WHERE username IS NULL;

6.4 MySQL8 日期时间函数

MySQL8 日期时间函数
序号	名称	描述	示例
1	NOW()	当前日期和时间	SELECT NOW(); -- 当前时间
2	CURDATE()	当前日期	SELECT CURDATE(); -- 当前日期
3	CURTIME()	当前时间	SELECT CURTIME(); -- 当前时间
4	DATE_ADD()	日期变化	SELECT DATE_ADD('2019-11-11', INTERVAL 365 DAY); -- 添加365天 SELECT DATE_ADD('2019-11-11', INTERVAL 1 YEAR); -- 添加1年
5	DATEDIFF()	日期差值	SELECT DATEDIFF('2020-11-11', '2019-11-11'); -- 计算两个时间差值
6	DATE_FORMAT()	日期格式化	SELECT DATE_FORMAT('2019-11-19', '%m/%d/%Y');

特别说明：通常在实际的开发项目中，我们在数据库存储是的时间戳，再由编程语言加载计算；并不会通过MySQL来计算时间。因为数据库已经帮我们完成数据的存储，尽量减少数据的计算。

6.5 MySQL8 信息函数

MySQL8 信息函数
序号	名称	描述	示例
1	CONNECTION_ID()	连接ID	SELECT CONNECTION_ID();
2	DATABASE()	当前数据库	SELECT DATABASE();
3	LAST_INSERT_ID()	最后插入记录的ID	SELECT LAST_INSERT_ID();
4	USER()	当前用户	SELECT USER();
5	VERSION()	版本信息	SELECT VERSION();

6.6 MySQL8 聚合函数

MySQL8 聚合函数
序号	名称	描述	示例
1	AVG()	平均值	SELECT AVG(price) FROM products; -- 求商品平价格
2	COUNT()	计数	SELECT COUNT(id) FROM users; -- 求用户数
3	MAX()	最大值	SELECT productname FROM products WHERE price = (SELECT MAX(price) FROM user); -- 求最大商品价格及名称
4	MIN()	最小值	SELECT productname FROM products WHERE price = (SELECT MIN(price) FROM user); -- 求最小商品价格及名称
5	SUM()	求和	SELECT SUM(price) FROM products; -- 求商品总价格

6.7 MySQL8 加密函数

MySQL8 加密函数
序号	名称	描述	示例
1	MD5()	信息摘要算法	SELECT MD5('123'); -- 对字符串加密
2	PASSWORD()	密码算法8.0.11版本删除

6.8 MySQL8 自定义函数

自定义函数：用户自定义函数（user-defined function, UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。

自定义函数的两个必要条件：参数、返回值；函数可以返回任意类型的值，同样可以接受这些类型的参数

创建自定义函数

CREATE FUNCTION function_name
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
routine_body;

关于函数体：

函数体由合法的SQL语句构成；
函数体可以是简单的SELECT 或 INSERT 语句；
函数体如果为复合结构则使用 BEGIN ... END 语句；
复合结构可以包含声明，循环，控制结构等。

删除函数：

DROP FUNCTION [IF EXISTS] function_name;

应用示例：

SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点%i分%s秒');
SET GLOBAL log_bin_trust_function_creators = 1;

CREATE FUNCTION myDate() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点%i分%s秒');

SELECT myDate();

6.9 MySQL8 创建带有参数的函数

DROP FUNCTION IF EXISTS myAvg; -- 删除之前存在的函数名
CREATE FUNCTION myAvg(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10, 2) UNSIGNED
RETURN (num1 + num2) / 2;

SELECT myAvg(1, 3); -- 应用求平均值

6.10 MySQL8 创建具有复合结构函数体的自定义函数

配置BEGIN 、END一起使用

DELIMITER //
CREATE FUNCTION add_class(class VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT class(className) VALUES(class);
RETURN LAST_INSERT_ID();
END
//

DELIMITER ;

七、MySQL8 之存储过程简介

存储过程：

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可以由应用程序调用执行，允许用户声明变量以及流程控制，存储过程可以接受参数，可以接收输入类型参数、也可以接收输出类型参数，并且可以存在多个返回值。

存储过程的优点：

增强SQL 语句的功能和灵活性；
实现较快的执行速度；第一次需要编译，第二次则不再需要编译；
减少网络流量；

7.1 MySQL8 之存储过程语法结构解析

创建存储过程

CREATE
[DEFINER={user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter;
[IN | OUT | INOUT] param_name type

参数说明：

IN，表示该参数的值必须在调用存储过程时指定
OUT，表示该参数的值可以被存储过程改变，并且可以返回
INOUT，表示该参数在调用时指定，并且可以被改变和返回

特性：

COMMENT：注释
CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句
NO SQL：不包含SQL语句
READS SQL DATA：包含读数据的语句
MODIFIES SQL DATA：包含写数据的语句
SQL SECURITY {DEFINER | INVOKER} 指明谁有权限来执行

过程体：

过程体由合法的SQL语句构成；
过程体可以是任意SQL语句【增、删、改、查】；
过程体如果为复合结构则使用BEGIN...END语句；
复合结构可以包含声明，循环，控制结构

7.2 MySQL8 之创建不带参数的存储过程

-- 创建不带参数的存储过程
CREATE PROCEDURE sp1() SELECT VERSION();
-- 调用sp1存储过程
CALL sp1();

7.3 MySQL8 之存储过程创建带有IN类型参数的存储过程

DELIMITER //
CREATE PROCEDURE removePid(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM products WHERE id = p_id;
END
//

7.4 MySQL8之创建带有IN 和OUT参数存储过程

DELIMITER //
CREATE PROCEDURE removeById(IN p_id INT UNSIGNED, OUT p_nums INT UNSIGNED)
BEGIN
DELETE FROM class WHERE id=p_id;
SELECT COUNT(id) class INTO p_nums;
END;
// 
DELIMITER ;

上述定义好后，下面则是对应遥使用示例：

CALL removeById(9, @num);
SELECT @num;

定义变量，使用变量示例：

-- 定义变量，使用变量
SET @data = 'hello world!!!';
SELECT @data;

7.5 MySQL8之存储过程创建带有多个OUT 类型参数的存储过程

按年龄删除用户，并返回影响行数、剩余行数，示例：

DELIMITER //
CREATE PROCEDURE removeUserByAge(IN p_age TINYINT UNSIGNED, OUT delUser TINYINT UNSIGNED, OUT userCounts TINYINT UNSIGNED)
BEGIN
DELETE FROM USER WHERE age = p_age;
SELECT ROW_COUNT() INTO delUser;
SELECT COUNT(id) FROM USER INTO userCounts;
END
//
DELIMITER ;

使用存储过程，示例：

-- 使用存储过程
CALL removeUserByAge(20, @delUser, @userCounts);
-- 查看删除记录数，即影响行数
SELECT @delUser;
-- 查看剩余记录数
SELECT @userCounts;

7.6 MySQL8 之存储过程与自定义函数的区别

存储过程实现的功能要复杂一些；而函数的针对性更强
存储过程可以返回多个值；函数只能有一个返回值
存储过程一般独立的来执行；而函数可以作为其他SQL 语句的组成部分来出现

八、MySQL8 新增JSON数据类型

8.1 MySQL8 之新增JSON 类型

MySQL8 新增类型之JSON 类型相关函数
序号	名称	描述	示例
1	JSON_ARRAY()	返回JSON 数组	SELECT JSON_ARRAY(1,2,3,4,5,'aa','bb');
2	JSON_OBJECT()	返回JSON对象	SELECT JSON_OBJECT('id', 10, 'name', 'xiaoming', 'sex', 1, 'age', 20);
3	JSON_ARRAY_INSERT()	将数据插入到JSON数组中，不替换现有值	SET @a = JSON_ARRAY(1,2,3,4,5,'aa','bb'); SELECT @a; SET @a = json_array_insert(@a, '$[1]', 'x'); SELECT @a;
4	JSON_INSERT()	将数据插入到JSON文档中，不替换现有值	SET @b = JSON_OBJECT('id', 10, 'name', 'zhangsan', 'sex', 1); SELECT @b; SELECT JSON_INSERT(@b, '$.id', 20); -- 不生效，不能替换已有的字段 SELECT JSON_INSERT(@b, '$.age', 20); -- 生效
5	JSON_ARRAY_APPEND()	值附加到JSON文档中指定数组的末尾并返回结果	SELECT JSON_ARRAY_APPEND(@b, '$.name', 'lisi');
6	JSON_SET()	替换现有的值，并增加不存储在的值【常用】	SELECT JSON_SET(@b, '$.age', 20, '$.sex', '2');
7	JSON_REPLACE()	仅替换现有值【常用】	SELECT JSON_REPLACE(@b, '$.age', 20, '$.sex', '2');
8	JSON_REMOVE()	删除指定JSON 值	SELECT JSON_REMOVE(@b, '$.sex'); UPDATE json_table SET content = JSON_REMOVE(content, '$.sex') WHERE id = 1;
9	JSON_EXTRACT()	从JSON 中返回数据	SELECT JSON_EXTRACT(content, '$.name') FROM json_table; SELECT content->'$.name' FROM json_table; -- 等价于
10	JSON_PRETTY()	提供漂亮的JSON值打印
11	JSON_CONTAINS_PATH()	返回JSON 文档是否包含指定的路径	SELECT JSON_CONTAINS_PATH(@b, 'one', '$.id'); SELECT JSON_CONTAINS_PATH(@b, 'one', '$.id', '$[1]');
12	JSON_CONTAINS()	返回查找数据是否在指定的json 文档中	SET @a = JSON_ARRAY(1,2,3,4,5,'aa','bb'); SELECT JSON_CONTAINS(@a, '2'); -- 给两个参数代表全局搜索 SELECT JSON_CONTAINS(@a, '3', '$[3]'); -- 给3个参数代表从3位置开始往后找
13	JSON_LENGTH()	返回JSON文档的长度	SET @b = JSON_OBJECT('id', 10, 'name', 'zhangsan', 'sex', 1); SELECT JSON_LENGTH(@b);

JSON数据示例

{
    "sites": [
    { "name":"菜鸟教程" , "url":"www.runoob" }, 
    { "name":"google" , "url":"www.google" }, 
    { "name":"微博" , "url":"www.weibo" }
    ]
}

CREATE TABLE tabname (
  ...
  Json_name JSON ...
  ...
)

创建含有json字段的表

CREATE TABLE json_table (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content JSON NOT NULL
)

测试JSON_ARRAY功能：

SELECT JSON_ARRAY(1,2,3,4,5,'aa','bb');

测试JSON_OBJECT功能：

SELECT JSON_OBJECT('id', 10, 'name', 'xiaoming', 'sex', 1, 'age', 20);

向含有json 字段的表插入数据，示例：

INSERT INTO json_table (content)VALUES(JSON_OBJECT('id', 10, 'name', 'xiaoming', 'sex', 1, 'age', 20));

SELECT * FROM json_table;

九、MySQL8 之认识存储引擎

查看数据库的创建命令：

SHOW CREATE TABLE table_name;

示例截图：

MySQL 可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。每一种存储引擎使用不同的存储机制、索引技巧、锁定水平、最终提供广泛且不同的功能。

存储引擎：

MyISAM 【主要】
InnoDB 【主要】
Memory
csv
Archive

十、MySQL8 之锁策略

并发控制

当多个连接对记录进行修改时保证数据的一致性和完整性。

锁

共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源、读取过程中数据不会发生任何变化。
排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

锁颗粒

表锁，是一种开销最小的锁策略
行锁，是一种开销最大的锁策略

十一、MySQL8 之事务处理

事务：事务用于保证数据库的完整性

实现步骤：

保证当前用于金额大于等于500的情况下，从当前账户减掉500元。
在对方账户增加500元。

事务的特性：

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

外键：是保证数据一致性的策略。

索引：是对数据表中一列或多列的值进行排序的一种结构。是帮助MySQL高效快速查找数据的一种数据结构。

MySQL8 数据库之- 各种存储引擎的特点
特点	MyISAM	InnoDB	Memory	Archive
存储限制	256TB	64TB	有	无
事务安全	-	支持	-	-
支持索引	支持	支持	支持
锁颗粒	表锁	行锁	表锁	行锁
数据压缩	支持	-	-	支持
支持外键	-	支持	-	-

设置数据表的存储引擎：

通过修改MySQL 配置文件实现：-default-storage-engine = engine
通过创建数据表命令实现：

CREATE TABLE table_name(
  ...
  ...
) ENGINE = enging;

十二、MySQL8 数据库之 - 主从复制

为什么要做主从复制？

在业务复杂的系统中，有这么一个情景，有一句sql 语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使用主库出现了锁表的情况，通过读从库也可以保证业务的正常运作。
做数据的热备。
架构的扩展。随着业务量越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O 性能。

12.1 MySQL8 基于日志点复制

原理图，如下：

基于日志点的复制配置步骤：

1、在主服务器上创建复制用户、赋值权限

CREATE user 'user_name'@'x.x.x.x' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'%';

2、配置主服务器MySQL 配置文件my.ini

设置bin_log：bin_log = mysql 日志文件路径
设置server_id：server_id = 可以选择主机IP地址后段，表示唯一编号

3、配置从服务器MySQL 配置文件my.ini

设置bin_log：bin_log = mysql 日志文件路径
设置server_id：server_id = 可以选择主机IP地址后段，表示唯一编号
设置 relay_log 中继日志：relay_log = mysql-relay-bin
[可选] 设置log_slave_updates 允许日志记录到服务器本机的二进制文件中
[可选] 设置read_only 只读属性，可以控制没有权限的用户进行写操作：read_only = on

4、初始化从服务器数据

mysqldump --master-data=2 --single-transaction --triggers --routines --all-databases -u root -p >> 导出文件路径

参数介绍：

--master-data 可以把binlog的位置和文件名添加到输出中，设置等于2会加上注释前缀
--single-transaction 设置事务的隔离级别、重复读取。不会对数据造成影响
--triggers 备份所有的触发器
--routines 备份存储过程和函数
--all-databases 所有库
--flush-logs 刷新日志

5、启动复制连路

CHANGE MASTER TO
MASTER_HOST = 'master_host_ip',
MASTER_USER = 'master_user',
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE = 'mysql_log_filename',
MASTER_LOG_POS = 值;

表示从库从主库的什么位置开始备份二进制文件名及偏移量

6、启动从服务

启动或停止命令：START SLAVE | STOP SLAVE
查看从服务状态：SHOW SLAVE STATUS;

基于日志点的主从复制实操

优点：是MySQL 最早支持的复制技术、BUG相对较少；对SQL查询没有任何限制，故障处理比较容易

缺点：故障转移时重新获取新的日志点信息比较因难

1、先创建从库同步用户；在master上配置复制所需要的账户，这里创建一个copy_user的用户，%表示任何远程地址的copy_user用户都可以连接master主机

CREATE USER 'copy_user'@'172.19.180.%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO copy_user@'172.19.80.%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

2、更改配置 my.ini 文件

[mysqld]
basedir="C:\ProgramData\MySQL\MySQL Server 5.7"
datadir="C:\ProgramData\MySQL\MySQL Server 5.7\data"
port=3306
server_id=137
log_bin=C:\ProgramData\MySQL\MySQL Server 5.7\log\mysql_log.log

[client]
port=3306
default-character-set=utf8

[mysql_safe]
timezone="CST"

[mysql]
default-character-set=utf8

3、重启MySQL 服务，观察日志目录是否有数据文件生成

4、修改从数据库配置 my.ini 文件；记得新建log 目录

[mysqld]
basedir="C:\ProgramData\MySQL\MySQL Server 5.7"
datadir="C:\ProgramData\MySQL\MySQL Server 5.7\data"
port=3306
server_id=138
log_bin=C:\ProgramData\MySQL\MySQL Server 5.7\log\mysql_log.log
relay_log=C:\ProgramData\MySQL\MySQL Server 5.7\log\mysql_relay_log.log
log_slave_updates=on
read_only=on

[client]
port=3306
default-character-set=utf8

[mysql_safe]
timezone="CST"

[mysql]
default-character-set=utf8

5、从库MySQL 服务重启

6、主库备份数据，mysqldump --single-transaction --master-data --all-databases -u root -p >> all.sql 输入密码即可

6、从库从all.sql 导入数据：mysql -u root -p < all.sql

7、链路的配置：

-- 在windows中cmd命令下，mysql -u root -p
CHANGE MASTER TO
MASTER_HOST = '172.19.180.41'
MASTER_USER = 'copy_user'
MASTER_PASSWORD = '123456'
MASTER_LOG_FILE = 'mysql_log.000001', MASTER_LOG_POS=155;

8、查看从库状态信息

-- 在windows中cmd命令下，mysql -u root -p
SHOW SLAVE STATUS\G

9、防火墙需要关闭

10、用户的加密规则修改

ALTER USER 'copy_user'@'172.19.180.%' IDENTIFIED WITH mysql_native_password BY '123456';

11、启动从库的SLAVE

-- 在windows中cmd命令下，mysql -u root -p
START SLAVE

验证，向主库添加数据，在从库查询数据；如果能在从库中查询出新增的数据，则搭建成功

12.2 MySQL8 基于GTID的复制

基于GTID 的复制是MySQL v5.6 版本以后支持
基于GTID 的复制与基于日志点的复制存在很大差异

什么是GTID?

GTID 即全局事务ID，其保证为每一个在主库上提交的事务在复制集群中可以生成一个唯一的ID

GTID = source_id:transaction_id

基于GTID的复制步骤

1、在主服务器上创建复制用户、赋值权限

CREATE USER 'my_user_copy'@'172.19.180.%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'my_user_copy'@'172.19.180.%';

2、修改主服务器配置文件，修改好后，需要重启服务

[mysqld]
Log_bin = mysql 二进制日志
Server_id = 唯一id
Gtid_mode = on  【重要】
Enforce-gtid-consistency = on 强制GTID 一致性（安全）  【重要】
        CREATE TABLE ... SELECT
        CREATE tempporary table

Log-slave-updates = on  【重要】在从服务器中记录传过来的主服务器修改日志

3、修改从服务器配置文件，修改好后，需要重启服务

[mysqld]
Log_bin = mysql 二进制日志
Server_id = 唯一id
Relay_log = 中继日志
Gtid_mode = on  【重要】
Enforce-gtid-consistency = on   【重要】强制GTID 一致性（安全）
Log-slave-updates = on     【重要】[在从服务器中记录传过来的主服务器修改日志]
Read_only = on    【重要】[建议]
Master_info_repository = TABLE    【重要】[建议]
Relay_log_info_repository = TABLE    【重要】[建议]

4、主库数据导出

mysqldump --single-transaction --master-data=2 --all-databases -u root -p >> sql.sql

5、从库数据导入，将sql.sql 文件拷贝至从库服务器的磁盘目录中

如果之前是从库，需要停用：STOP SLAVES;
清理数据：reset slave; reset master

mysql -u root -p < sql.sql

6、设置从库服务器，需要登录进mysql里mysql -u root -p

CHANGE MASTER TO
MASTER_HOST = '172.19.180.41',
MASTER_USER = 'my_copy_user',
MASTER_PASSWORD = '123456',
MASTER_AUTO_POSITION = 1;

7、查看SLAVE是否启动

SHOW SLAVE STATUS\G; -- 查看从服务库状态
START SLAVE; -- 启动从服务库
SHOW SLAVE STATUS\G; -- 查看从服务库状态

8、向主库插入数据，从库查询数据

-- 主库插入数据
INSERT INTO products(name) VALUES('茅台王子酒');
-- 从库查询数据
SELECT * FROM products;

基于GTID的复制优缺点

优点：

可以很方便的进行故障转移
从库不会丢失主库上的任何修改

缺点：

故障处理比较复杂
对执行的SQL有一定限制

十三、MySQL8 数据库操作之备份、还原

导出：mysqldump 参数 >> 导出SQL文件路径

导入：mysql 参数 < 导入SQL文件路径

将binlog 文件导出SQL文件

[mysqld]
basedir="C:\ProgramData\MySQL\MySQL Server 5.7"
datadir="C:\ProgramData\MySQL\MySQL Server 5.7\data"
port=3306
server_id=138
log_bin=C:\ProgramData\MySQL\MySQL Server 5.7\log\mysql_log.log
relay_log=C:\ProgramData\MySQL\MySQL Server 5.7\log\mysql_relay_log.log
log_slave_updates=on
read_only=on

[client]
port=3306
default-character-set=utf8

[mysql_safe]
timezone="CST"

[mysql]
default-character-set=utf8

进入mysql

RESET MASTER; -- 清空数据

CREATE DATABASE my_db;
USE my_db;

CREATE TABLE user{
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL
);

INSERT user(name) VALUES('xiaomei'), ('xiaocang'), ('xiaogao');

-- 查询数据
SELECT * FROM user;

导出数据

mysqldump -u root -p --master-data=2 my_db > my_db.sql

测试验证

1、再次添加数据

INSERT user(name) VALUES('xiao1'), ('xiao2'), ('xiao3');

2、删除库

DROP DATABASE my_db;

3、查看binlog 数据

mysqlbinlog --no-defaults mysql_log.000002

4、将binlog 数据导出为sql文件

mysqlbinlog --no-defaults mysql_log.000002 >> ../../002bin.sql

5、打开002bin.sql文件，将DROP DATABASE my_db; 删除

6、【可选】创建my_db 库

CREATE DATABASE my_db;

7、导入数据

mysql -u root -p my_db < my_db.sql
mysql -u root -p my_db < 002bin.sql

实际工作中，我们通过工具来做备份。

十四、MySQL8 数据库操作之数据库工具的介绍

phpMyAdmin
Navicat Premium

文章最后，给大家推荐一些受欢迎的技术博客链接：

JAVA相关的深度技术博客链接
Flink 相关技术博客链接
Spark 核心技术链接
设计模式 —— 深度技术博客链接
机器学习 —— 深度技术博客链接
Hadoop相关技术博客链接
超全干货--Flink思维导图，花了3周左右编写、校对
深入JAVA 的JVM核心原理解决线上各种故障【附案例】
请谈谈你对volatile的理解？--最近小李子与面试官的一场“硬核较量”
聊聊RPC通信，经常被问到的一道面试题。源码+笔记，包懂
深入聊聊Java 垃圾回收机制【附原理图及调优方法】

欢迎扫描下方的二维码或搜索公众号“大数据高级架构师”，我们会有更多、且及时的资料推送给您，欢迎多多交流！

更多推荐

最新最全MySQL 8知识,关注这篇就够了！

最新最全MySQL 8知识，关注这篇就够了！

一、开胃菜：认识MySQL

1.1 安装MySQL

1.2 使用MySQL 之登录与退出命令

1.3 使用MySQL 之常见到的符号

1.4 使用MySQL 之修改MySQL 提示符

1.5 SQL语句介绍及编码规范

1.6 使用MySQL 之认识字符集

二、正菜：上手使用MySQL

2.1 使用MySQL之常用命令及建库语句

2.2 MySQL 数据类型之数值型

2.3 MySQL 基础数据类型之日期和时间类型

2.4 MySQL 基础数据类型之字符串类型

2.5 MySQL 数据表操作之建立数据表

2.6 MySQL 数据表操作之查看数据表

2.7 MySQL 数据表操作之查看数据表结构

2.8 MySQL 数据表操作之记录的操作与查找

2.9 MySQL 基础数据类型之超出范围处理

2.10 MySQL 字段约束

2.11 MySQL 字段约束之主键约束

2.12 MySQL 字段约束之唯一约束

2.13 MySQL 字段约束之外键约束

2.14 MySQL 字段约束之外键约束的要求

2.15 MySQL 字段约束之外键约束参照操作

2.16 MySQL 字段操作之修改数据表

2.17 MySQL 字段操作之修改数据表

2.18 MySQL 字段操作之修改数据表

三、MySQL8 操作数据表中的记录

3.1 MySQL 插入数据INSERT

3.2 MySQL 单表更新记录 UPDATE

3.3 MySQL 单表删除记录DELETE

3.4 MySQL 数据操作之查询表达式解析 SELECT

3.5 MySQL 数据操作之WHERE 语句进行条件查询

3.6 MySQL 数据操作之 GROUP BY 语句对查询结果分组

3.7 MySQL 数据操作之 HAVING 语句设置分组条件

3.8 MySQL 数据操作之 ORDER BY 语句对查询结果排序

3.9 MySQL 数据操作之 LIMIT 语句限制查询数量

四、MySQL 数据操作之子查询简介

4.1 MySQL 数据操作之由比较运算符引发的子查询

4.2 MySQL 数据操作之由 [NOT] IN EXISTS 引发的子查询

4.3 MySQL 数据操作之多表更新

4.4 MySQL 数据操作之多表更新一步到位

五、MySQL 数据操作之连接的语法结构

5.1 MySQL 数据操作之内连接 INNER JOIN

5.2 MySQL 数据操作之外连接 OUTER JOIN

5.3 MySQL 数据操作之外连接 OUTER JOIN

5.4 MySQL 数据操作之多表连接

5.5 MySQL 数据操作之关于连接的几点说明

5.6 MySQL 数据操作之自身连接查询

5.7 MySQL 数据操作之多表删除

六、MySQL8 运算符、函数、存储过程及新增数据类型

6.1 MySQL8 字符函数

6.2 MySQL8 中数值运算符与函数

6.3 MySQL8 比较运算符与函数

6.4 MySQL8 日期时间函数

6.5 MySQL8 信息函数

6.6 MySQL8 聚合函数

6.7 MySQL8 加密函数

6.8 MySQL8 自定义函数

6.9 MySQL8 创建带有参数的函数

6.10 MySQL8 创建具有复合结构函数体的自定义函数

七、MySQL8 之存储过程简介

7.1 MySQL8 之存储过程语法结构解析

7.2 MySQL8 之创建不带参数的存储过程

7.3 MySQL8 之存储过程创建带有IN类型参数的存储过程

7.4 MySQL8之创建带有IN 和OUT参数存储过程

7.5 MySQL8之存储过程创建带有多个OUT 类型参数的存储过程

7.6 MySQL8 之存储过程与自定义函数的区别

八、MySQL8 新增JSON数据类型

8.1 MySQL8 之新增JSON 类型

九、MySQL8 之认识存储引擎

十、MySQL8 之锁策略

十一、MySQL8 之事务处理

十二、MySQL8 数据库之 - 主从复制

12.1 MySQL8 基于日志点复制

12.2 MySQL8 基于GTID的复制

十三、MySQL8 数据库操作之备份、还原

十四、MySQL8 数据库操作之数据库工具的介绍

相关文章

发布评论取消回复