MYSQL数据库
数据库的基本概念
数据
数据是事实或观察的结果，它是对客观事物的逻辑归纳,是信息的表现形式和载体,可以是符号、文字、数字、语音、图像、视频等。
数据的本身并没有意义，并不能完全表达其内容，只有经过处理解释后才有意义。
数据库管理技术
数据管理是数据处理的中心问题，是数据库的核心任务，主要包括对数据进行分类、组织、编码、存储、检索和维护。
数据管理技术发展的三个阶段：
数据库
数据库是按照数据结构来组织、存储和管理数据的仓库。
数据库是长期储存在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。
基本特征
结构化 易扩展 可共享 独立性高 冗余度小
数据库的分类
根据数据模型的不同，数据库通常被分为层次数据库、网状数据库和关系数据库。
对数据库的划分常见的还有另外一种，即关系型数据库和非关系型数据库。像我们经常听说的Redis、MongoDB就属于非关系型数据库。
数据库管理系统
数据库管理系统（Database Management System，简称DBMS）是专门用于管理数据库的计算机软件。
数据库管理系统能够为数据库提供数据的定义、建立、维护、查询和统计等操作功能，并完成对数据完整性、安全性进行控制的功能，它位于用户和操作系统之间，是一层数据管理软件。
我们通常所说的数据库，其实是指数据库管理系统。
数据库管理系统与数据库的关系
RDBMS介绍
RDBMS是Relational Database Management System的简称，即关系型数据库管理系统，它是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库，用户通过查询来检索数据库中的数据。
RDBMS的特点：
- 数据以表格的形式出现
- 每行为一条记录
- 每列为记录名称所对应的数据域（Field）
- 许多的行和列组成一张单表（Table）
- 若干单表组成数据库（Database）
- 查询方式：关系型数据库采用结构化查询语言（即SQL）来对数据库进行查询
- 事务性：关系型数据库强调ACID规则，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability））
- 读写性能：关系型数据库十分强调数据的一致性，并为此降低读写性能付出了巨大的代价，在面对海量数量处理、高并发数据读写等场景时性能下降的非常厉害
主流的RDBMS
市面上能够接触到的关系型数据库有很多，主流的有以下几种。
SQL
SQL简介
SQL是Structured Query Language的缩写，即结构化查询语言。SQL是一门标准的计算机语言，用于访问和操作数据库，其主要包括数据定义、数据操纵、数据查询和数据控制。
SQL已成为RDBMS的标准语言，但不同的RDBMS使用的SQL版本有一些差异，它们都有一些私有扩展。
SQL能做什么？
- SQL可以创建和管理数据库、数据表、存储过程、视图等
- SQL可以向数据库中插入新的记录，并可进行修改、删除
- SQL可以查询数据库中的记录
- SQL可以对数据库进行事务控制和权限管理
SQL语言的分类
按照功能用途，可以将SQL语言分为4类：DDL、DML、DQL和DCL。
DDL：数据定义语言Data Definition Language，DDL用于数据库、表、视图等的建立、删除。DDL包括 CREATE、ALTER、DROP等
DML：数据操纵语言Data Manipulation Language，DML用于添加、删除和修改数据表中的记录。DML包括 INSERT、DELETE和UPDATE等
DCL：数据控制语言Data Control Language，DCL包括数据库对象的权限管理和事务管理。DCL包括COMMIT、ROLLBACK、GRANT等。
DQL：数据查询语言Data Query Language，查询是数据库的基本功能。DQL中使用SELECT查询数据表中的记录。
MySQL存储引擎
存储引擎，就是如何存储数据、如何更新数据、如何查询数据、如何为存储的数据建立索引等一系列技术的实现方法。
show engines;查看mysql支持的存储引擎
MyISAM 拥有较快的插入、查询速度，但不支持事务
InnoDB 支持ACID事务，支持行级锁，支持外键；MySQL 5.5版本后的默认存储引擎
MRG_MYISAM 将一组结构相同的MyISAM表聚合成一个整体，再进行增删改查操作
Memory 所有数据存储在内存中，响应快；MySQL重启时数据会全部丢失
Archive 归档，且有压缩机制，适用于历史数据归档
CSV 逻辑上由逗号分隔数据，会为每张表创建一个.csv文件
MySQL数据类型
MySQL支持多种数据类型，但大致可以分为3类：数值、日期/时间和字符串（字符）类型。
数值类型
日期和时间类型
字符串类型
数据库与表的基本操作
DDL之数据库
在DDL中，对数据库的操作主要有两种：创建和删除。
CREATE DATABASE 数据库名 //创建数据库
DROP DATABASE 数据库名 //删除数据库
show databases;//看一下当前系统有哪些数据库
create database mydb;//创建一个名为mydb的数据库
drop database mydb//删除一个名为mydb的数据库
select database();//查看当前环境在哪个数据库下工作
use mydb;//使用mydb数据库
DDL之数据表
在DDL中，对数据表的操作主要有3种：创建、修改和删除。
创建数据表，需要定义的信息主要包括：表名、字段名、字段类型。
#MySQL的建表语法
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [(create_definition,…)] [table_options][select_statement]
说明
TEMPORARY：表示创建临时表，在当前会话结束后将自动消失
IF NOT EXISTS：在建表前，先判断表是否存在，只有该表不存在时才创建
create_definition：建表语句的关键部分，用于定义表中各列的属性
table_options：表的配置选项，例如：表的默认存储引擎、字符集
select_statement：通过select语句建表
对于已经存在的表，可以使用alter命令添加、修改、删除字段，也可以对表进行删除操作。
show tables;//有那些表已经被创建
create table contacts(//创建表
id int primary key,
name varchar(30),
phone varchar(11)
);
drop table contacts;//删除表
desc contacts;//查询表字段信息
alter table contacts add sex char(1);//添加字段
alter table contacts modify sex int;//修改字段
alter table contacts drop column sex;//删除字段
DML:插入，修改，删除数据
在MySQL中
使用insert into语句向数据表中插入数据。
使用update语句来修改数据表中的数据。
使用delete语句来删除数据表中的数据。
select * from contacts;//查看内容
create table contacts(
id int not null auto_increment primary key,
name varchar(50),
sex tinyint default 1,
phone varchar(20)
);
insert into contacts(name, sex, phone) values ('张三', 1, '12345678910');
insert into contacts(name, sex, phone) values ('Tom\'s cat', 1, '12345678911');
insert into contacts(name, sex, phone) values ("Lili's cta", 1, '12345678911');
insert into contacts(name, sex, phone) values ("李四", 1, '12345678912'),("王五", 1, '12345678913');
insert into contacts(name, phone) values ("Lili's cta", '12345678911');
update contacts set sex = 2;
update contacts set sex = 2 where id = 3;
update contacts set sex = 2, phone = '12345678921' where id = 5;
delete from contacts;//所有数据都没有了
delete from contacts where id = 4;//删除特定数据
数据完整性
数据完整性是指存储在数据库中的数据，应该保持一致性和可靠性。
关系模型允许定义三类数据约束，它们是实体完整性、参照完整性以及用户定义的完整性约束，其中前两种完整性约束由关系数据库系统自动支持。
实体完整性:实体就是现实世界中的某个对象，RDBMS中一行代表一个实体。实体完整性就是保证每一个实体都能被区别
域完整性:域完整性主要是对列的输入有要求，通过限制列的数据类型、格式或值的范围来实现
参照完整性:主要是表与表之间的关系，可以通过外键来实现
用户自定义完整性:借助存储过程和触发器实现
实体完整性要求每张表都有唯一标识符，每张表中的主键字段不能为空且不能重复
约束方法：唯一性约束、主键约束、标识列
域完整性是针对某一具体关系数据库的约束条件，它保证表中某些列不能输入无效的值。
域完整性指列的值域的完整性，如数据类型、格式、值域范围、是否允许空值等。
约束方法：限制数据类型、检查约束、默认值、非空约束
参照完整性要求关系中不允许引用不存在的实体。
约束方法：外键约束
用户自定义完整性是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。
约束方法：规则、存储过程、触发器
唯一性约束
在MySQL中，可以使用关键字 UNIQUE 实现字段的唯一性约束，从而保证实体的完整性。
UNIQUE意味着任何两条数据的同一个字段不能有相同值。
一个表中可以有多个 UNIQUE 约束。
create table person(
id int not null auto_increment primary key comment '主键id',
name varchar(30) comment '姓名',
id_number varchar(18) unique comment '身份证号'
);
外键约束
外键（FOREIGN KEY）约束定义了表之间的一致性关系，用于强制参照完整性。
外键约束定义了对同一个表或其他表的列的引用，这些列具有PRIMARY KEY或UNIQUE约束。
主表
create table stu(
stu_no int not null primary key comment '学号',
stu_name varchar(30) comment '姓名'
);
从表
create table sc(
id int not null auto_increment primary key comment '主键id',
stu_no int not null comment '学号',
course varchar(30) comment '课程',
grade int comment '成绩',
foreign key(stu_no) references stu(stu_no)
);
在插入数据时，必须先向主表插入，再向从表插入。删除数据时正好相反。
数据查询语言DQL
简单查询
使用select查询数据
在MySQL官网 https://dev.mysql.com/doc/refman/8.0/en/select.html 可以查到支持的select语法。
select * from person;
+----+------+------------+
| id | name | id_number |
+----+------+------------+
| 1 | 张三 | 1234567890 |
| 3 | 李四 | 1234567891 |
+----+------+------------+
select id,name from person;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 3 | 李四 |
+----+------+
select id,name from person where name = "李四";
+----+------+
| id | name |
+----+------+
| 3 | 李四 |
+----+------+
1 row in set (0.00 sec)
不仅如此，命令行工具还可以当成计算器来使用
select 8*9;
+-----+
| 8*9 |
+-----+
| 72 |
+-----+
条件查询
where子句（单条件查询）
在SQL中, insert、update、 delete和select后面都能带where子句 ,用于插入、修改、删除或查询指定条件的记录。
SQL语句中使用where子句语法
SELECT column_name FROM table_name WHERE column_name运算符value
where子句（多条件查询）
在where子句中,使用and、or可以把两个或多个过滤条件结合起来。
and、or运算符语法
SELECT column_name FROM table_name WHERE condition1 AND condition2 OR condition3
素材
create table employee(
id int not null auto_increment primary key,
name varchar (30) comment '姓名',
sex varchar(1) comment '性别',
salary int comment '薪资(元)'
);
insert into employee (name, sex, salary) values('张三' ,'男',5500);
insert into employee (name, sex, salary) values(' 李洁','女',4500);
insert into employee (name, sex, salary) values(' 欧阳辉', '男', 7500);
insert into employee (name, sex, salary) values(' 李芳','女',8500);
insert into employee (name, sex, salary) values(' 张江','男',6800);
insert into employee (name, sex, salary) values(' 李四','男',12000);
insert into employee (name, sex, salary) values(' 王五','男',3500);
insert into employee (name, sex, salary) values(' 马小龙', '男',6000);
insert into employee (name, sex, salary) values(' 马小龙', '女',4000);
insert into employee (name, sex, salary) values(' 龙五', '男',8000);
insert into employee (name, sex, salary) values(' 冯小芳', '女',10000);
insert into employee (name, sex, salary) values(' 李小梅','女' ,4200);
select * from employee where sex = "男";
select * from employee where sex = "男" and salary >= 10000;
select * from employee where sex = "男" or salary >= 10000;
select * from employee where salary between 10000 and 12000;
in和like的使用
运算符in的使用
运算符IN允许我们在WHERE子句中过滤某个字段的多个值
where子句使用in语法
SELECT column_name FROM table_name WHERE column_name IN(value1, value2, ..
mysql> select * from employee where id in (1,2,3);
+----+---------+------+--------+
| id | name | sex | salary |
+----+---------+------+--------+
| 1 | 李小梅 | 女 | 4200 |
| 2 | 张三 | 男 | 5500 |
| 3 | 李洁 | 女 | 4500 |
+----+---------+------+--------+
3 rows in set (0.00 sec)
运算符like的使用
在where子句中，有时候我们需要查询包含xxx 字符串的所有记录，这时就需要用到运算符like。
where子句使用like语法
SELECT column_name FROM table_name WHERE column_name LIKE ‘%value%’
说明：
1、LIKE子句中的%类似于正则表达式中的*，匹配任意0个或多个字符
2、LIKE子句中的_匹配任意单个字符
3、LIKE子句中如果没有%和_，就相当于运算符=的效果
mysql> select * from employee where name like "张_";
+----+------+------+--------+
| id | name | sex | salary |
+----+------+------+--------+
| 1 | 张三 | 男 | 5500 |
+----+------+------+--------+
MySQL常用函数讲解
MySQL内置函数
我们通常说的MySQL函数指的是MySQL数据库提供的内置函数，包括数学函数、字符串函数、日期和时间函数、聚合函数、条件判断函数等，这些内置函数可以帮助用户更方便地处理表中的数据，简化用户的操作。
函数now()
函数now()用于返回当前的日期和时间。
应用场景：
在实际应用中，大多数业务表都会带一个创建时间create_time字段，用于记录每一条数据的产生时间。在向表中插入数据时，就可以在insert语句中使用now()函数。
示例如下：
insert into user(id, name, create_time) values(1, 'zhangsan', now());
函数date_format()
应用场景：
在实际应用中，一般会按照标准格式存储日期/时间，如 2019-12-13 14:15:16 。在查询使用数据时，往往又会有不同的格式要求，这时就需要使用date_format()函数进行格式转换。
示例如下：
select name, date_format(birthday, '%Y/%m/%d') from user;
聚合函数
聚合函数是对一组值进行计算，并返回单个值。
MySQL常用的聚合函数有5个，分别是count、sum、avg、min和max
函数ifnull()
函数ifnull()用于处理NULL值。
ifnull(v1,v2)，如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
case when
case when是流程控制语句，可以在SQL语句中使用case when来获取更加准确和直接的结果。
SQL中的case when类似于编程语言中的if else或者switch。
#case when的语法有2种
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
CASE WHEN [expr] THEN [result1]…ELSE [default] END
MySQL常用函数讲解（练）
查询结果排序与分页
排序的应用场景
我们已经掌握使用select语句结合where查询条件获取需要的数据，但在实际的应用中，还会遇到下面这类需求，又该如何解决？
order by的使用
在SQL中，使用order by对查询结果集进行排序，可以按照一列或多列进行排序。
#order by语法
SELECT column_name1, column_name2
FROM table_name1, table_name2
ORDER BY column_name, column_name [ASC|DESC]
说明：
- ASC表示按升序排列，DESC表示按降序排列。
- 默认情况下，对列按升序排列。
limit的使用
在SELECT语句中使用LIMIT子句来约束要返回的记录数，通常使用LIMIT实现分页。
#limit语法
SELECT column_name1, column_name2
FROM table_name1, table_name2
LIMIT [offset,] row_count
说明：
- offset指定要返回的第一行的偏移量。第一行的偏移量是0，而不是1。
- row_count指定要返回的最大行数。
【经验分享】limit的分页公式：
limit (page-1)*row_count, row_count
GROUP BY与HAVING的使用
group by的应用场景
我们已经掌握使用select语句结合where查询条件获取需要的数据，但在实际的应用中，还会遇到下面这类需求，又该如何解决？
group by的使用
从字面上理解，group by表示根据某种规则对数据进行分组，它必须配合聚合函数进行使用，对数
据进行分组后可以进行count、sum、avg、max和min等运算。
#group by语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
说明：
- aggregate_function表示聚合函数。
- group by可以对一列或多列进行分组。
GROUP_CONCAT函数的使用
group_concat的应用场景
使用group by可以分组统计每个部门有多少员工。假如，除了统计每个部门的员工数量之外，还想知道具体是哪些员工（员工列表），又该如何实现呢？
group_concat的使用
group_concat配合group by一起使用，用于将某一列的值按指定的分隔符进行拼接，MySQL默认的分隔符为逗号。
#group_concat语法
group_concat([distinct] column_name [order by column_name asc/desc ] [separator '分隔符'])
使用DISTINCT去除重复数据
distinct的使用
distinct用于在查询中返回列的唯一不同值（去重复），支持单列或多列。在实际的应用中，表中的某一列含有重复值是很常见的，如employ表的dept列。如果在查询数据时，希望得到某列的所有不同值，可以使用distinct。
#distinct语法
SELECT DISTINCT column_name, column_name
FROM table_name;
素材
create table footprint (
id int not null auto_increment primary key,
username varchar (30) comment '用户名',
city varchar (30) comment' 城市',
visit_date varchar (10) comment ' 到访日期'
);
insert into footprint (username, city, visit_date) values ('lisis','贵阳','2019-12-05' );
insert into footprint (username, city, visit_date) values('lisis', ' 贵阳','2020-01-15' );
insert into footprint (username, city, visit_date)values('lisis','北京','2018-10-10' );
insert into footprint (username, city, visit_date) values ('zhangsan','上海','2020-01-01') ;
insert into footprint (username, city, visit_date) values ('zhangsan','上海','2020-02-02' );
insert into footprint (username, city, visit_date) values('lisi','拉萨' ,'2016-12-20' );
mysql> select distinct city from footprint;
+------+
| city |
+------+
| 贵阳 |
| 上海 |
| 北京 |
| 拉萨 |
+------+
mysql> select distinct city,username from footprint;
+------+----------+
| city | username |
+------+----------+
| 贵阳 | lisis |
| 上海 | lisis |
| 北京 | liufeng |
| 拉萨 | lisi |
| 上海 | zhangsan |
+------+----------+
表连接（内连接、外连接、自连接）
什么是表连接
表连接（JOIN）是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。
#表连接语法
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; （“=”可以被替换）
表连接的几种方式
内连接：join或inner join
外连接：左外连接，left join
右外连接，right join
全外连接，full join
自连接：同一张表内的连接
各种表连接的区别
交叉连接（cross join）：没有用where子句的交叉连接将产生笛卡尔积，第一个表的行数乘以第二个表的行数等于笛卡尔积和结果集的大小
select A.stu_no,A.name,B.course,B.score
from student A
join score B on(A.stu_no = B.stu_no);
select A.stu_no,A.name,B.course,B.score
from student A
inner join score B on(A.stu_no = B.stu_no);
select A.stu_no,A.name,B.course,B.score
from student A, score B
where A.stu_no = B.stu_no;//内连接的另一种写法
select A.stu_no,A.name,B.course,B.score
from student A
left join score B on(A.stu_no = B.stu_no);//左连接
select A.stu_no,A.name,B.course,B.score
from student A, score B;//交叉连接
表连接在实际工作中，内连接和左连接较多。
自连接查询的场景及使用
自连接是一种特殊的表连接，它是指相互连接的表在物理上同为一张表，但是逻辑上是多张表。自连接通常用于表中的数据有层次结构，如区域表、菜单表、商品分类表等。
#自连接语法
SELECT A.column, B.column
FROM table A, table B
WHERE A.column = B.column;
mysql> select A.id, A.name, B.name as provinceName
-> from area A,area B
-> where A.pid = B.id and A.pid <> 0;
+----+------+--------------+
| id | name | provinceName |
+----+------+--------------+
| 2 | 贵阳 | 贵州省 |
| 3 | 遵义 | 贵州省 |
| 5 | 广州 | 广东省 |
| 6 | 深圳 | 广东省 |
+----+------+--------------+
子查询EXISTS和IN的使用
子查询in
之前的课程中，我们已经学习过运算符 IN，它允许我们在 WHERE 子句中过滤某个字段的多个值。
#where子句使用in语法
SELECT column_name FROM table_name WHERE column_name IN(value1, value2, …)
如果运算符 in 后面的值是来源于某个查询结果，并非是指定的几个值，这时就需要用到子查询。子
查询又称为内部查询或嵌套查询，即在 SQL 查询的 WHERE 子句中嵌入查询语句。
#子查询in语法
SELECT column_name FROM table_name
WHERE column_name IN(
SELECT column_name FROM table_name [WHERE]
);
子查询exists
EXISTS是子查询中用于测试内部查询是否返回任何行的布尔运算符。将主查询的数据放到子查询中
做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否保留。
#where子句使用exists语法
SELECT column_name1
FROM table_name1
WHERE EXISTS (SELECT * FROM table_name2 WHERE condition);
查询所有选修了课程的学生
select A.*
from student A
where A.stu_no in (select B.stu_no from score B);
查询所有选修离散数学课程的学生
select A.*
from student A
where A.stu_no in (select B.stu_no from score B where B.course = "离散数学");
查询所有选修了课程的学生
select A.*
from student A
where exists (select * from score B where A.stu_no = B.stu_no);
数据控制语言DCL
用户管理
SQL语言按功能用途分为4类，分别是DDL、DML、DQL和DCL。其中，DCL是数据控制语言，主要用于管理用户和权限。在企业中，这部分工作通常是由DBA完成，一般开发人员很少接触。
DCL主要能做什么？
1. 创建用户
2. 删除用户
3. 修改密码
4. 给用户赋予权限
5. 撤销用户权限
MySQL的权限体系大致分为5个层级，全局层级、数据库层级、表层级、列层级和子程序层级。
MySQL的权限信息主要存储在以下几张表中，当用户连接数据库时，MySQL会根据这些表对用户进行权限验证。
用户管理
在MySQL中，使用CREATE USER来创建用户，用户创建后没有任何权限
#创建用户
CREATE USER '用户名' [@'主机名'] [IDENTIFIED BY '密码'];
注意：MySQL的用户账号由两部分组成：用户名和主机名，即用户名@主机名，主机名可以是IP或机器名称，主机名为%表示允许任何地址的主机远程登录MySQL数据库。
#删除用户
DROP USER '用户名' [@'主机名'];
#修改密码
ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';
在命令行中输入ipconfig可以查看自己的IP地址
create user 'abcd'@"%" identified by "123456";
create user "zs"@"192.168.1.123" identified by "123456";
注意：不能在@前后加空格
权限管理
在MySQL数据库中，使用grant命令授权、revoke命令撤销授权。
#授权
grant all privileges on databaseName.tableName to '用户名' [@'主机名'] ;
#撤销授权
revoke all privileges on databaseName.tableName from '用户名' [@'主机名'] ;
#刷新权限
FLUSH PRIVILEGES;
#查看权限
show grants for '用户名' [@'主机名'] ;
MySQL权限列表
使用grant和revoke进行授权、撤销授权时，需要指定具体是哪些权限，这些权限大体可以分为3类，数据类、结构类和管理类。
create user 'abcd'@"%" identified by "123456";
create user "zbc"@"192.118.1.123" identified by "123456";
show grant for "abcd"@"%";
show grant for "zbc"@"192.118.1.123";
grant select on mydb.* to "abcd"@"%";
revoke select on mydb.* from "abcd"@"%";
禁止ROOT用户远程登录
为什么要禁止root远程登录
在实际工作中，公司的DBA都会设置禁止root用户远程登录，请问这是为什么？
- root是MySQL数据库的超级管理员，几乎拥有所有权限，一旦泄露后果非常严重；
- root是MySQL数据库的默认用户，所有人都知道，如果不禁止远程登录，可以针对root用户暴力破解密码
可以采取下面方式应对
mysql> select user, host from user;
可以显示
忘记root用户密码的解决方法
忘记root密码的解决方法
在MySQL中，如果只是忘记普通用户的密码很好解决，只需要使用root用户登录后重置其密码即可。但是，如果忘记了root用户的密码，该怎么解决？
1.关闭Mysql服务
2.重启MySQL时关闭权限验证
3.修改root密码
4.正常启动
MySQL服务
重启MySQL时关闭权限验证
#关闭权限验证
mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --console --skip-granttables --shared-memory
文件位置根据实际情况而定
修改root用户密码
MySQL关闭权限验证后，直接通过 mysql 命令即可连接到数据库，并可正常执行各类操作。
#刷新权限
FLUSH PRIVILEGES;
#修改root用户的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
flush priviledges;
alter user "root"@"" identified by "123456";
索引的使用
慢查询日志
MySQL的日志类型
认识慢查询日志
慢查询日志用于记录MySQL数据库中响应时间超过指定阈值的语句。慢查询日志通常也被称之为慢日志，因为它不仅仅只针对SELECT语句，像INSERT、UPDATE、DELETE等语句，只要响应时间超过所设定阈值都会记录在慢查询日志中。
如何开启慢查询日志
慢查询日志可以通过命令临时设置，也可以修改配置文件永久设置。
#查看是否开启慢查询日志
show variables like 'slow%';
#临时开启慢查询日志
set slow_query_log='ON';
set long_query_time=1;
#慢查询日志文件所在位置
show variables like '%datadir%';
查询分析器EXPLAIN
explain简介
explain命令可以查看SQL语句的执行计划。当explain与SQL语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理语句，包括有关如何联接表以及以何种顺序联接表的信息。
explain的使用
explain的使用很简单，只需要在SQL语句之前加上explain命令即可，除select语句外，explain也能分析insert、update和delete语句。
explain结果解析
mysql> explain select * from employee where id = 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> explain select * from employee where name = '张三'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
索引的基本使用
常见的索引种类
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定记录。
数据库的索引好比一本书的目录，能够加快数据库的查询速度；
索引是快速搜索的关键，如果不加索引，查找任何一条特定的数据都会进行一次全表扫描。
索引的使用
1、创建索引
# 创建普通索引
CREATE INDEX indexName ON tableName(columnName(length));
# 创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName(length));
# 创建复合索引
CREATE INDEX indexName ON tableName(columnName1, columnName2, …);
2、删除索引
DROP INDEX [indexName] ON tableName;
3、查看索引
SHOW INDEX FROM tableName;
Q
mysql> create index idx_name on employee(name);
mysql> explain select * from employee where name = "张三";
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employee | NULL | ref | idx_name | idx_name | 123 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
mysql> drop index idx_name on employee;
mysql> explain select * from employee where name = "李四";
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employee | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
复合索引前导列特性
复合索引前导列特性
在MySQL中，如果创建了复合索引(name, salary, dept)，就相当于创建了(name, salary, dept)、(name, salary)和(name)三个索引，这被称为复合索引前导列特性，因此在创建复合索引时应该将最常用作查询条件的列放在最左边，依次递减。
未使用索引
select * from employee where salary=8800;
select * from employee where dept='部门A';
select * from employee where salary=8800 and dept='部门A';
使用索引
select * from employee where name='liufeng';
select * from employee where name='liufeng' and salary=8800;
select * from employee where name='liufeng' and salary=8800 and dept='部门A';
mysql> create index idx_name_salary_dept on employee(name, salary,dept);
覆盖索引
什么是覆盖索引
覆盖索引又称之为索引覆盖，即select的数据列只从索引中就能得到，不必读取数据行，也就是只需扫描索引就可以得到查询结果。
关于覆盖索引的几点说明：
- 使用覆盖索引，只需要从索引中就能检索到需要的数据，而不要再扫描数据表；
- 索引的体量往往要比数据表小很多，因此只读取索引速度会非常快，也会极大减少数据访问量；
- MySQL的查询优化器会在执行查询前判断，是否有一个索引可以覆盖所有的查询列；
- 并非所有类型的索引都可以作为覆盖索引，覆盖索引必须要存储索引列的值。像哈希索引、空间索引、全文索引等并不会真正存储索引列的值。
如何判断使用了覆盖索引
当一个查询使用了覆盖索引，在查询分析器EXPLAIN的Extra列可以看到“Using index” 。
MYSQL进阶
前面的内容是基础，后面的内容是有提升自己的空间。
事务控制
什么是事务控制
事务（Transaction）是指作为一个逻辑工作单元执行的一系列操作，这些操作要么全部成功，要么全部失败。事务确保对多个数据的修改作为一个单元来处理。
简单来说，如果有不放心的操作，想改回来，利用事务可以撤销固定部分操作。
注意事项:
在MySQL中，只有使用了Innodb存储引擎的数据库或表才支持事务。
事务用于维护数据库的完整性，保证成批的sql语句要么都执行，要么都不执行。
事务用于管理INSERT、UPDATE和DELETE语句。
事务的四个特性
如果某个数据库声称支持事务，那么该数据库必须具备ACID四个特性，即Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）和Durability（持久性）。
原子性:事务必须是原子工作单元，事务中包含的各操作要么都做，要么都不做。
一致性:事务在执行完成时，必须使所有的数据都保持一致状态。
持续性:事务执行完成之后，它对系统的影响是永久性的。
隔离性:事务独立运行。多个事务之间相互隔离，互不干扰。事务的100%隔离，会牺牲速度。
MySQL的事务控制
在默认情况下，MySQL是自动提交事务的，即每一条INSERT、UPDATE、DELETE的SQL语句提交后会立即执行COMMIT操作。
所以，要开启一个事务，可以使用start transaction或begin，或者将autocommit的值设置为0。
默认情况下，autocommit的值为1，表示自动提交事务。
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.01 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql> start transaction;
或者
mysql> begin;
使用start transaction或begin开启事务
mysql> rollback;
mysql> commit;
对于一个事务，要么回滚，要么提交。
分区表
什么是分区表
分区表就是按照某种规则将同一张表的数据分段划分到多个位置存储。对数据的分区存储提高了数据库的性能，被分区存储的数据在物理上是多个文件，但在逻辑上仍然是一个表，对表的任何操作都跟没分区之前一样。在执行增、删、改、查等操作时，数据库会自动找到对应的分区，然后执行操作。
注：MySQL从5.1.3开始支持分区（Partition）
在MySQL 8.0中，只有InnoDB和NDB两个存储引擎支持分区
什么时候分区呢
索引如果建的合适 一千万是没什么问题的 如果改表写入比较频繁 那就稍微早点采取分表操作，分不分看业务繁忙程度，看资源使用，看业务需求，不是多少记录多大数据文件来评判的
分区表的好处
存储更多
与单个磁盘或文件系统分区相比，可
以存储更多的数据。
便于管理
很容易根据分区删除失去保存意义的
历史数据。
并行处理
涉及到sum()、count()等聚合函数的
查询，可以很容易进行并行处理。
提升查询效率
一些查询可以极大地优化，查询仅从
某个或某几个分区中获取数据。
提高查询吞吐
通过跨多个磁盘来分散数据查询，来
获得更大的查询吞吐量。
分区表的四种类型
MySQL支持的分区类型包括Range、List、Hash和Key，其中Range最常用。
Range分区 允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
List分区 允许系统通过预定义的列表的值来对数据进行分割。
Hash分区 允许通过对表的一个或多个列的HashKey进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。
Key分区 对Hash模式的一种延伸，这里的HashKey是MySQL系统产生的。
Range分区
ange分区是基于属于一个给定连续区间的列值，把多行分配给分区。
#Range分区示例
create table user_range(
id int not null auto_increment,
name varchar(30) ,
age int,
birthday date,
province int,
primary key(id, age)//主键必须包含分区字段
)
partition by RANGE(age) (
partition p1 VALUES LESS THAN (20) DATA DIRECTORY = 'c:/data/p1',
partition p2 VALUES LESS THAN (40) DATA DIRECTORY = 'c:/data/p2',
partition p3 VALUES LESS THAN (60) DATA DIRECTORY = 'c:/data/p3',
partition p4 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'c:/data/p4'
);
List分区
List分区是基于列值匹配一个离散值集合中的某个值来进行选择。
#List分区示例
create table user_list(
id int not null auto_increment,
name varchar(30) ,
age int,
birthday date,
province int,
primary key(id, province)
)
partition by List(province) (
partition p1 VALUES IN (1,3,5,7,9,11,13,15,17,19,21),
partition p2 VALUES IN (2,4,6,8,10,12,14,16,18,20,22),
partition p3 VALUES IN (23,24,25,26,27,28,29,30,31,32,33,34)
);
Hash分区
Hash分区是基于用户定义的表达式的返回值来进行选择的分区。
#Hash分区示例
create table user_hash(
id int not null auto_increment,
name varchar(30) ,
age int,
birthday date,
province int,
primary key(id, birthday)
)
partition by HASH(YEAR(birthday))
partitions 5;
Key分区
Key分区类似于Hash分区，但这里的Hash Key是由MySQL系统产生的。
#Key分区示例
create table user_key(
id int not null auto_increment,
name varchar(30) ,
age int,
birthday date,
province int,
primary key(id, age)
)
partition by KEY (age)
partitions 5;
OTHER
#新增分区
alter table `user` add partition(partition p5 VALUES LESS THAN MAXVALUE);
#对已存在的表进行分区
alter table `user` partition by RANGE(age) (
partition p1 VALUES LESS THAN (20) DATA DIRECTORY = 'c:/data/p1',
partition p2 VALUES LESS THAN (40) DATA DIRECTORY = 'c:/data/p2',
partition p3 VALUES LESS THAN (60) DATA DIRECTORY = 'c:/data/p3',
partition p4 VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'c:/data/p4'
);
#删除分区（分区下的数据也会被删除）
alter table `user` drop partition p5;
#移除分区（数据不会被删除）
ALTER TABLE `user` REMOVE PARTITIONING ;
视图
什么是视图
视图是一个虚拟表，其内容由select查询语句定义。和真实的表一样，视图也包含行和列，对视图的操作与对表的操作基本一致。视图中的数据是在使用视图时动态生成，视图中的数据都存储在基表中。
视图的本意就是对数据的封装，基本上不会对视图进行增删改等操作。
对单表创建视图后，对视图的操作，对原表也会有影响，对原表的操作，对视图也会有影响；
对多表创建视图后，对视图的操作，对原表不会有影响，对原表的操作，对视图也会有影响。
用处
可读性: 简化了复杂的查询，使复杂的查询更易于理解和使用
安全性: 视图可以隐藏一些敏感的信息，可以把权限限定到行列级别，可以看成封装
重用性: 视图是对复杂查询语句的封装，对数据库重构，不会影响程序的运行
视图的基本操作
#创建视图
CREATE VIEW view_name AS SELECT…;
#修改视图
ALTER VIEW view_name AS SELECT…;
#查看视图创建语句
SHOW CREATE VIEW view_name;
#查看有哪些视图
SHOW TABLE STATUS WHERE comment='view';
#删除视图
DROP VIEW view_name;
存储过程
什么是存储过程
其类似于编程语言中的方法或函数，存储过程（Stored Procedure）是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程的优点：
1. 存储过程是对SQL语句的封装，增强可复用性
2. 存储过程可以隐藏复杂的业务逻辑、商业逻辑
3. 存储过程支持接收参数，并返回运算结果
存储过程的缺点：
1. 存储过程的可移植性较差，如果更换数据库，要重写存储过程
2. 无法使用Explain对存储过程进行分析
3. 《阿里巴巴Java开发手册》中禁止使用存储过程
存储过程示例
存储过程定义：求两数之和
delimiter //
create procedure my_sum(in a int, in b int, out result int)
begin
set result = a + b;
end
//
delimiter ;
#存储过程调用
call my_sum(10, 20, @result);
select @result;
存储过程示例：计算1+2+...+n的和
delimiter //
create procedure my_n_sum(in n int, out result int)
begin
declare i int default 1;
declare sum int default 0;
while i<=n do
set sum = sum + i;
set i = i + 1;
end while;
set result = sum;
end;
//
delimiter ;
删除存储过程
存储过程被创建后，就会一直保存在数据库服务器上，直至被删除。当 MySQL 数据库中存在废弃的存储过程时，我们需要将它从数据库中删除。
MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下：
DROP PROCEDURE [ IF EXISTS ] <过程名>
语法说明如下：
过程名：指定要删除的存储过程的名称。
IF EXISTS：指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。
注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。
删除后，可以通过查询 information_schema 数据库下的 routines 表来确认上面的删除是否成功。SQL 语句和运行结果如下：
mysql> SELECT * FROM information_schema.routines WHERE routine_name='procedure_name';
Empty set (0.03 sec)
结果显示，没有查询出任何记录，说明存储过程已经被删除了。
存储过程实战：给指定用户发邮件通知
问题描述
根据传入的用户id和内容发送邮件。
测试数据
drop table if exists user_info;
drop table if exists email_info;
create table user_info(
id int not null auto_increment primary key,
name varchar(30),
email varchar(50)
);
insert into user_info(id, name, email) values(1, '张三', 'liufeng@qq.com');
insert into user_info(id, name, email) values(2, '李四', 'zhangsan@qq.com');
create table email_info(
id int not null auto_increment primary key,
email varchar(50),
content text,
send_time datetime
);
存储过程实现
delimiter //
create procedure send_email(in user_id int, in content text)
begin
/* 根据用户id查询邮箱email */
set @user_email=(select email from user_info where id=user_id);
/* 模拟发送邮件 */
insert into email_info(email, content, send_time) values(@user_email, content, now());
end;
//
delimiter ;
call send_email(1, '你好MySQL');
触发器
什么是触发器
触发器（trigger）用于监视某种情况并触发某种操作，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，而是由事件来触发。例如，当对某张表进行insert、delete、update操作时就会触发执行它。
存储过程由程序来调用，触发器由事件来触发。也就是说它可以监听表数据的状态，当发生了增删改的时候就会触发相应的操作。这样可以前端的一些业务，由后端的DBA来执行。
视图，存储过程和触发器都是对sql语句的一个封装。
#创建触发器语法
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW
trigger_stmt
参数说明：
trigger_name：触发器名称
trigger_time：触发时间，取值有before、after
trigger_event：触发事件，取值有insert、update、delete
table_name：触发器监控的表名
trigger_stmt：触发执行的语句，可以使用OLD、NEW来引用变化前后的记录内容
NEW.columnName：获取INSERT触发事件中新插入的数据
OLD.columnName：获取UPDATE和DELETE触发事件中被更新、删除的数据
给新用户发邮件
drop table if exists user_info;
drop table if exists email_info;
create table user_info(
id int not null auto_increment primary key,
name varchar(30),
email varchar(50)
);
insert into user_info(id, name, email) values(1, '李四', 'lisi@qq.com');
insert into user_info(id, name, email) values(2, '张三', 'zhangsan@qq.com');
create table email_info(
id int not null auto_increment primary key,
email varchar(50),
content text,
send_time datetime
);
#触发器实战：给新用户发邮件
delimiter //
CREATE TRIGGER send_email_trigger AFTER INSERT ON user_info FOR EACH ROW
BEGIN
insert into email_info(email, content, send_time) values(NEW.email, '欢迎加入MySQL阵营！ ', now());
END
//
delimiter ;
预处理（绑定变量）
什么是预处理
从MySQL 4.1开始，就支持预处理语句（Prepared statement），这大大提高了客户端和服务器端数据传输的效率。当创建一个预定义SQL时，客户端向服务器发送一个SQL语句的原型；服务器端接收到这个SQL语句后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄，以后每次执行这条SQL，客户端都指定使用这个句柄。
即时SQL（一次编译、一次运行）
1、语法和词义的解析；
2、优化SQL语句，制定执行计划；
3、执行SQL语句并返回结果；
1、语法和词义的解析；
2、优化SQL语句，制定执行计划；
3、执行SQL语句并返回结果；
… …
预处理SQL（一次编译、多次运行）
1、语法和词义的解析；
2、优化SQL语句，制定执行计划；
3、执行SQL语句并返回结果；
3、执行SQL语句并返回结果；
… …
预处理的优势
高效执行重复SQL:在服务器端只需要解析一次SQL,在服务器端某些优化器的工作只需要执行一次，它会缓存一部分执行计划
更加安全:使用预处理语句，无须在应用程序中处理转义，也大大减少了SQL注入和攻击的风险。
减少网络开销:对于重复执行的SQL，后续只需要将参数发送到服务器端，而不是整个SQL语句，因此网络开销会更小。
预处理的基本使用
MySQL支持SQL接口的预处理，即不使用二进制传输协议也可以直接以SQL的方式使用预处理。预
处理的语法如下。
# 定义预处理语句
PREPARE stmt_name FROM preparable_stmt;
# 执行预处理语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;
mysql> prepare pre_employee from 'select * from employee where name = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> set @name1="张三";
Query OK, 0 rows affected (0.00 sec)
mysql> set @name2="李四";
Query OK, 0 rows affected (0.00 sec)
mysql> execute pre_employee using @name1;
+----+--------+------+--------+
| id | name | sex | salary |
+----+--------+------+--------+
| 1 | 张三 | 男 | 5500 |
+----+--------+------+--------+
1 row in set (0.00 sec)
mysql> execute pre_employee using @name2;
+----+--------+------+--------+
| id | name | sex | salary |
+----+--------+------+--------+
| 6 | 李四 | 男 | 12000 |
+----+--------+------+--------+
1 row in set (0.00 sec)
查询缓存
什么是查询缓存
很多数据库产品都能够缓存查询的执行计划，对于相同类型的SQL就可以跳过SQL解析和执行计划分成阶段。MySQL在某种场景下也可以实现，但是MySQL还有另一种不同的缓存类型：缓存完成的SELECT查询结果，也就是查询缓存。
MySQL将缓存存放在一个引用表中，类似于HashMap的数据结构，Key查询SQL语句，Value则是查询结果。当发起查询时，会使用SQL语句去缓存中查询，如果命中则立即返回缓存的结果集。
mysql> show variables like "%query_cache%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | NO |
+------------------+-------+
- 可以使用 SQL_NO_CACHE 在 SELECT 中禁止缓存查询结果，例如：SELECT SQL_NO_CACHE ...
- MySQL 8.0已删除查询缓存功能，因为性能损耗问题。
复制表的几种方式
为什么要复制表，如果对表的操作出现了差错，可以给自己留下后路。
只复制表结构
create table tableName like someTable;
只复制表结构，包括主键、索引，但不会复制表数据
只关注表数据
create table tableName select * from someTable;
复制表的大体结构及全部数据，不会复制主键、索引等
完整复制表结构+数据
create table tableName like someTable;
insert into tableName select * from someTable;
分两步完成，先复制表结构，再插入数据。
导出数据
select…into outfile
SELECT * FROM employee INTO OUTFILE 'D:\\employee.txt' //文件存储路径
FIELDS TERMINATED BY ',' //字段间分隔符
ENCLOSED BY '"' //值用双引号引起
LINES TERMINATED BY '\r\n'; //行间分隔符
mysql> show variables like "%secure_file_priv%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql命令重定向查询结果
通常，我们使用mysql命令连接数据库，mysql命令有一个-e选项，可以执行指定的SQL语句，再结合DOS的重定向操作符”>”可以将查询结果导出到文件。
C:\Windows\System32>mysql -h localhost -u root -p -D mydb -e "select * from employee" > D:\employee_.txt
Enter password: ******
使用mysqldump导出数据（较为常用）
mysqldump是MySQL用于转存储数据库的实用程序，它主要产生一个SQL脚本，其中包含创建数据库、创建数据表、插入数据所必需的SQL语句。
# 导出mydb数据库（含数据）
mysqldump -h localhost -u root -p mydb > d:/mydb.sql
# 导出mydb数据库（不含数据）
mysqldump -h localhost -u root -p mydb --no-data > d:/mydb.sql
# 导出mydb.employee数据表
mysqldump -h localhost -u root -p mydb employee > d:/employee.sql
# 导出mydb数据库，忽略xxx表
mysqldump -h localhost -u root -p mydb --ignore-table mydb.xxx > d:/employee.sql
定时备份数据库
备份非常重要，留后路，定时备份更加实用
定时备份数据库的解决方案
Windows上实现定时备份MySQL
schtasks.exe用于安排命令和程序在指定时间内运行或定期运行，它可以从计划表中添加和删除任务、按需要启动和停止任务、显示和更改计划任务。
#备份数据库的脚本mysql_mydb_backup.bat
mysqldump -h localhost -uroot -p123456 mydb > d:\backup\mydb.sql
#创建计划任务（每隔指定时间备份一次MySQL）
schtasks /create /sc minute /mo 1 /tn 定期备份MySQL /tr d:\backup\mysql_mydb_backup.bat
#删除计划任务
schtasks /delete /tn 定期备份MySQL
Linux上实现定时备份MySQL（1）
MySQL备份脚本mysql_mydb_backup.sh
#!/bin/bash
#备份目录
backup_dir=/home/liufeng/backup
#备份文件名
backup_filename=“mydb-`date +%Y%m%d`.sql“
#进入备份目录
cd $backup_dir
#备份数据库
mysqldump -h localhost -uroot -p123456 mydb > ${backup_dir}/${backup_filename}
#删除7天以前的备份
find ${backup_dir} -mtime +7 -name "*.sql" -exec rm -rf {} \;
Linux上实现定时备份MySQL（2）
crontab是一个命令，常见于Unix和类Unix的操作系统之中，用于周期性执行任务。
#每天凌晨01:30执行shell脚本（备份数据库）
30 1 * * * bash /home/liufeng/backup/mysql_mydb_backup.sh
字符集之GB2312
什么是字符集
字符（Character）是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。字符集（Character set）是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同，常见的字符集有ASCII、GB2312、GBK、 GB18030、Unicode等。计算机要准确的处理各种字符集文字，就需要进行字符编码，以便计算机能够识别和存储各种文字。
可以用show character set来查看mysql支持的字符集
设置字符集
如果安装mysql的时候用的是直装版，mysql8.0默认使的字符集是utf-8mb4，如果是压缩版，则在my.ini中配置默认字符集。
尽管提前默认了，在建数据库或者在建表时，也可以自己设置该库，表的字符集。
1、数据库
# 创建数据库时指定字符集
CREATE DATABASE databaseName CHARSET utf8 COLLATE utf8_general_ci;
# 查看数据库的字符集
SHOW CREATE DATABASE databaseName;
2、表
# 创建表时指定字符集
CREATE TABLE tableName(…) DEFAULT CHARSET=utf8;
# 查看数据库的字符集
SHOW CREATE TABLE tableName;
3、字段
CREATE TABLE tableName(…, name varchar(50) not null CHARSET utf8, …);
修改已存在表的支持的字符集
alter table t1 convert to character set utf8mb4;
SQL注入
什么是SQL注入
SQL注入（SQL Injection）是指应用程序对用户输入数据的合法性没有判断、没有过滤，攻击者可以在应用程序中通过表单提交特殊的字符串，该特殊字符串会改变SQL的运行结果，从而在管理员毫不知情的情况下实现非法操作，以此来实现欺骗数据库执行非授权的任意查询。
广泛性: 任何一个基于SQL语言的数据库都可能受到SQL注入攻击。很多开发人员都为了省事不对表单参数进行校验。
隐蔽性: SQL注入语句一般都嵌入在普通的HTTP请求中，很难与正常语句区分开，SQL注入也有很多变种。
操作简单: 互联网上有很多SQL注入工具，简单易懂，攻击过程简单，不需要太多专业知识。
危害大: 攻击者通过SQL注入能够获取到更多数据，如管理员密码、整个系统的用户数据、他人的隐私数据、完整的数据库。
如何进行SQL注入
利用or语句
select * from user
where username='abc' and password='' or '1'='1';
验证账号密码是否匹配
测试语句
create table user(
id int not null auto_increment primary key,
username varchar(30) comment '用户名',
password varchar(30) comment '密码'
);
insert into user(username, password) values('admin', '123456');
insert into user(username, password) values('test', '123456');
如何恢复误删的数据
什么是binlog日志
MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
万一遇到数据丢失的紧急情况下，可以使用binlog日志进行数据恢复（定时全备份+binlog日志恢复增量数据部分）。
可以用show variables like 'log_bin%';
使用binlog恢复误删的数据
#查看所有二进制日志列表
show master logs;
#查看正在使用的二进制日志
show master status;
#刷新日志（重新开始新的binlog日志文件）
flush logs
#查询指定的binlog
show binlog events in '***-bin.000001' from 10668\G;
#导出恢复数据用的sql
mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\***-bin.000057" --start-position 528 --stop-position 1191 > d:\backup\test.sql
#恢复数据
source d:\backup\test.sql
审计功能
MySQL审计功能
审计是数据库安全中很重要的一个环节，它能够实时记录数据库的操作记录，帮助数据库管理员对数据库异常行为进行分析审核。审计会详细记录谁、在什么时间、执行了什么操作。MySQL社区版没有自带的审计功能或插件，MySQL商业版中有审计功能。
例
<AUDIT_RECORD
"NAME"="Query"
"RECORD"="1292_2020-03-03T00:04:42"
"TIMESTAMP"="2020-03-03T12:45:07 UTC"
"COMMAND_CLASS"="select"
"CONNECTION_ID"="32"
"STATUS"="1146"
"SQLTEXT"="select * from employee"
"USER"="root[root] @ localhost []"
"HOST"="localhost"
"OS_USER"=""
"IP"=""
/>
本文来自博客园，作者：NeverLateThanBetter，转载请注明原文链接：https://www.cnblogs.com/do-it-520/p/MySql.html
韶华易逝，不能虚度年华。