文章目录
MYSQL概要
查看已有数据库：
show databases；
创建数据库：
create database 数据库名字 default charset utf8 collate utf8_general_ci;
删除数据库：
drop database 数据库名字；
进入数据库（进入文件夹）
use 数据库名字；
查看文件夹下所有的数据表（文件）:
show tables；
数据表的管理（文件）
创建表：
create table user(
user_id int not null primary keyauto_increment,
username varchar(16) not null,
password char(8) not null,
sexy char(4) not null
)default charset=utf8;
查看表详细信息：desc 表名称;
查看表：select * from 表名称；
删除表：
drop table 表名称;
插入数据：
insert into 表名（字段名1，字段名2） values （1000，18）；
insert into 表名（字段名1，字段名2） values （1000，18），（133，55）；
常用数据类型：
整型：
tinyint：
有符号，取值范围-128~127【默认】
无符号，0~255【 tinyint unsigned】
int：
有符号：-2147483648~2147483647
无符号：…
bigint
小数：
float
double
decimal：
准确的小数值，m是数字总个数（负号不算），d是小数点后个数，m最大值65，d最大30
如：
create table 表名称{
salary decimal（8，2） 不超过8个数字，小数点后2个
}default charset=utf8；
字符串：
char（m）：查询速度快 m最大255个字符
定长字符串：char（11） 固定11个字符串进行存储，哪怕没有11个，也会存储11个
varchar（m）：节省空间 m最大是65535字节
变长字符串：varchar（11）
text（m）： m最大 65535个字符
mediumtext
longtext
时间：
datetime：
YYYY-MM-DD HH:MM:SS
date：
YYYY-MM-DD
数据行操作
新增数据：
insert into 表名（字段名1，字段名2） values （值1，值2），（值1，值2）；
删除数据：
delete from 表名；
delete from 表名 where 条件；id in（1，3）等于id=1 or id=3；
修改数据：
update 表名 set 列=值，列=值 where 条件；
查询数据：
select * from 表名称；
select 字段名，字段名 from 表名称 where 条件；
数据库范式
- 第一范式：
有主键，具有原子性，字段不可分割 - 第二范式：前提是满足第一范式，表中非主键列对主键列是完全依赖，要求每一个表只描述一件事情。也就是说在一个数据库表中，一个表只能保存一种数据，不可用把多种数据保存在同一个数据库表中。
表的每个字段都必须与主键具有直接关系，而非间接关系 - 第三范式：前提是满足第二范式，表与表之间的关系通过主外键进行关联，总而言之，第三范式就是属性不依赖于其它非主属性(一张表的非主关键字来自另一张表的主关键字或者本张表的主关键词）。
表中的字段与主键不能有传递依赖，必须是直接依赖，不满足第三范式会出现大量的数据冗余
SELECT语法
查询执行顺序
这才是完整的SELECT查询
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
JOIN another_table
ON mytable.column = another_table.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT count OFFSET COUNT;
1. FROM 和 JOINs
FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)
2. WHERE
我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式
3. GROUP BY
如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.
4. HAVING
如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.
5. SELECT
确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据.
6. DISTINCT
如果数据行有重复DISTINCT 将负责排重.
7. ORDER BY
在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.
LIMIT / OFFSET
最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.
特殊
-
distinct
DISTINCT 查询不重复的内容
select distinct 字段 from 表; -
FULL JOIN、LEFT JOIN、RIGHT JOIN
- FULL JOIN：将两个表数据1-1连接，保留A或B的原有行，如果某一行在另一个表不存在，会用 NULL来填充结果数据。 - LEFT OUTER JOIN, RIGHT OUTER JOIN, 或 FULL OUTER JOIN, 和 LEFT JOIN, RIGHT JOIN, and FULL JOIN 等价. - 所有在用这三个JOIN时，你需要单独处理 NULL.你计算一些行的平均值的时候，如果是0会参与计算导致平均值差错，是NULL则不会参与计算。可以用IS NULL和 IS NOT NULL 来选在某个字段是否等于 NULL. -
AS 和 表达式筛选
AS不仅用在表达式别名上，普通的属性列甚至是表（table）都可以取一个别名1. as SELECT column AS better_column_name, … FROM a_long_widgets_table_name AS mywidgets INNER JOIN widget_sales ON mywidgets.id = widget_sales.widget_id; 2. 表达式 SELECT particle_speed / 2.0 AS half_particle_speed (对结果做了一个除2） FROM physics_data WHERE ABS(particle_position) * 10.0 >500 （条件要求这个属性绝对值乘以10大于500）; -
分组统计：GROUP BY
GROUP BY 数据分组语法可以按某个col_name对数据进行分组，如：GROUP BY Year指对数据按年份分组， 相同年份的分到一个组里。如果把统计函数和GROUP BY结合，那统计结果就是对分组内的数据统计了. GROUP BY 分组结果的数据条数，就是分组数量，比如：GROUP BY Year，全部数据里有几年，就返回几条数据， 不管是否应用了统计函数. -
HAVING
HAVING 和 WHERE 语法一样，只不过作用的结果集不一样. 在我们例子数据表数据量小的情况下可能感觉 HAVING没有什么用，但当你的数据量成千上万属性又很多时也许能帮上大忙 .
小贴士?
如果你不用GROUP BY语法, 简单的WHERE就够用了. -
mysql中的when语句SELECT role ,count(role),(case when Building is null then 0 else 1 end ) bn from Employees group by Role,bn;
必须要记住的
条件：数字(where)
当查找条件col是数字
select * from table where col = 1;
| Operator | Condition | SQL Example | 解释 |
|---|---|---|---|
| =, !=, < ,<=, >, >= | Standard numerical operators | col != 4 | 等于 大于 小于 |
| BETWEEN … AND … | Number is within range of two values (inclusive) | col BETWEEN 1.5 AND 10.5 | 在 X 和 X之间 |
| NOT BETWEEN … AND … | Number is not within range of two values (inclusive) | col NOT BETWEEN 1 AND10 | 不在 X 和 X之间 |
| IN (…) | Number exists in a list | col IN (2, 4, 6) | 在 X 集合 |
| NOT IN (…) | Number does not exist in a list | col NOT IN (1, 3, 5) | 不在 X 集合 |
条件：文本(where)
当查找条件col是文本
select * from table where col like ‘%jin’;
| Operator | Condition | SQL Example | 解释 |
|---|---|---|---|
| = | Case sensitive exact string comparison (notice the single equals) | col = “abc” | 等于 |
| != or <> | Case sensitive exact string inequality comparison | col != “abcd” | 不等于 |
| LIKE | Case insensitive exact string comparison | col LIKE “ABC” | 等于 |
| NOT LIKE | Case insensitive exact string inequality comparison | col NOT LIKE “ABCD” | 不等于 |
| % | Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) | col LIKE “%AT%” (matches “AT”, “ATTIC”, “CAT” or even “BATS”) | 模糊匹配 |
| _ | Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) | col LIKE “AN_” (matches “AND”, but not “AN”) | 模糊匹配单字符 |
| IN (…) | String exists in a list | col IN (“A”, “B”, “C”) | 在集合 |
| NOT IN (…) | String does not exist in a list | col NOT IN (“D”, “E”, “F”) | 不在集合 |
排序(rows)
需要对结果rows排序和筛选部分rows
select * from table where col > 1 order by col asc limit 2 offset 2
| Operator | Condition | SQL Example | 解释 |
|---|---|---|---|
| ORDER BY | . | ORDER BY col ASC/DESC | 按col排序 |
| ASC | . | ORDER BY col ASC/DESC | 升序 |
| DESC | . | ORDER BY col ASC/DESC | 降序 |
| LIMIT OFFSET | . | LIMIT num_limit OFFSET num_offset | 从offset开始取limit（不包括offset） |
| ORDER BY | . | ORDER BY col1 ASC,col2 DESC | 多列排序 |
join:连表(table)
当查找的数据在多张关联table里
select * from table1 left join table2 on table1.id = table2.id where col > 1
| Operator | Condition | SQL Example | 解释 |
|---|---|---|---|
| JOIN … ON … | . | t1 JOIN t2 ON t1.id = t2.id | 按ID连成1个表 |
| INNER JOIN | . | t1 INNER JOIN t2 ON t1.id = t2.id | 只保留id相等的row |
| LEFT JOIN | . | t1 LEFT JOIN t2 ON t1.id = t2.id | 保留t1的所有row |
| RIGHT JOIN | . | t1 RIGHT JOIN t2 ON t1.id = t2.id | 保留t2的所有row |
| IS/IS NOT NULL | . | col IS/IS NOT NULL | col是不是为null |
算式(select / where)
当需要对select的col 或 where条件的col 经过一定计算后才能使用
select * , col*2 from table where col/2 > 1
| Operator | Condition | SQL Example | 解释 |
|---|---|---|---|
| (+ - * / %) | . | col1 + col2 | col加减乘除 |
| substr | . | substr(col,0,4) | 字符串截取 |
| AS | . | col * 2 AS col_new | col取别名 |
| … | 还有很多 |
统计（select）
对查找的rows需要按col分组统计的情况
select count(*),avg(col),col from table where col > 1 group by col
| Operator | Condition | SQL Example | 解释 |
|---|---|---|---|
| COUNT(*), COUNT(column) | A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column. | count(col) | 计数 |
| MIN(column) | Finds the smallest numerical value in the specified column for all rows in the group. | min(col) | 最小 |
| MAX(column) | Finds the largest numerical value in the specified column for all rows in the group. | max(col) | 最大 |
| AVG(column) | Finds the average numerical value in the specified column for all rows in the group. | avg(col) | 平均 |
| SUM(column) | Finds the sum of all numerical values in the specified column for the rows in the group. | sum(col) | 求和 |
| GROUP BY | . | group by col,col2 | 分组 |
| HAVING | . | HAVING col>100 | 分组后条件 |
DML（针对表中数据的操作）
增
插入值必须按照顺序、数量、类型、约束条件，否则语法不通过
-
如果表名后不加列，表示插入所有列记录
INSERT INTO table_name[(column[,column...])] VALUES (value[,value...]) -
INSERT INTO 子查询
删
删除表中记录,DELETE后可加FROM，也可省略，删除记录，应从子表先开始删除，否则报错，不能删除被其他表引用的数据，删除还应注意联合外键的情况（Oracle可以不加from）
-
删除表中所有数据
DELETE FROM 表名; -
删除满足条件的数据
DELETE FROM 表名 WHERE 条件;
改
更新表中记录,不加WHERE子句，表示这字段所有记录的内容均被修改
-
改所有
UPDATE TABLE_NAME SET 字段=’‘ -
将此字段满足条件的记录修改
UPDATE TABLE_NAME SET 字段=’‘WHERE 条件
事务
指一系列DML操作的集合，不同窗口进行DML操作，不会直接作用到数据库服务，相当于SVN中的本地操作，只有事务结束才会作用到数据库服务。
- 开始：从执行第一条DML语句开始。
- 结束：
- 正常结束
commit操作（结束事务的同事，相当于另一个事务的开始）
- 异常结束
关闭sql、命令窗口、关闭plsql客户端、异常结束进程 - 优点：不同窗口进行DML操作，不会直接作用到数据库服务，相当于SVN中的本地操作，只有事务结束才会作用到数据库服务
- 保存回滚点
savepoint 命名回滚点的名称
- 回滚到指定位置
rollback to 回滚点的名称
注意:回滚只能从后向前，不能从前向后，后指的是后设置的后滚点，前指的是前一个设置的回滚点 - 特点：
1. 原子性（不可分性）
指所包含的操作要么全做，要么全不做
2.永久性
执行结束永久有效
3.隔离性
事务结束前对事务的操作只对当前操作窗口可见
4.一致性
事务结束后，数据保持一致
TRUNCATE 与 DELETE区别
- TRUNCATE不能回滚，DELETE可以回滚
- 在删除大量数据时，TRUNCATE比DELETE快
- TRUNCATE可以释放表空间，DELETE不可以释放表空间(表空间为逻辑空间)
- TRUNCATE操作对象只能是表，DELETE可以是表、视图、同义词
= TRUNCATE是DDL语句，DELETE是DML语句
DDL(针对表的)
表命令规则
表命名规则：以字母开头、长度限制为30字符、内容组成为：A-Z、a-z、0-9、_、$和#、不能为oracle保留字、同一用户下不能重名、不能和用户定义的其他对象重名
字段的数据类型
--VARCHAR2(size) 内存空间大小是随着存储值的长度而变化，其长度需要小于等于size的值，varchar数据类型会自动转换成varchar2类型
--CHAR(size) 内存空间大小为size的值，是固定的，不会随着存储的值长度变化而变化，不建议使用
--NUMBER(size) 数值有效位数为size的值
--NUMBER(p、s) p为数值的有效数字位，s为小数位数
--DATE 日期类型
--CLOB 可变的内存空间，存储大数据字符串，最高可存储2GB
--BLOB 存储较大的二进制数据，如图片等
约束constraints
--PRIMARY KEY 主键约束 PK
--FOREIGN KEY 外键约束 FK
--UNIQUE 唯一约束 表名_列名_UK
--NOT NULL 非空约束 NN
--CHECK 检查约束 表名_列名_CK
--不命名约束，oracle会自动产生特定的约束，名字以sys_为前缀
CONSTRAINT自定义约束(行业规范，必须自定义)
优点：通过约束的名称了解此约束是作用在哪张表、哪个字段，以及是什么类型的约束
--约束可以在表级别和列级别上定义
--列级别：约束类型紧跟在列后，约束和列间没有逗号分隔
--表级别：约束和列间通过逗号分隔，NOT NULL不能加在表级别上
自定义约束名
表名_字段名_约束缩写 约束名
外键约束
外键加在表级别上
constraint 自定义的约束名 foreign key(字段名) references 主表名(主表的主键)
外键加在列级别上
constraint 自定义的约束名 references 主表名(主表的主键)
联合主键
需要两个字段遵循 联合唯一 、联合非空
MYSQL数据类型概述
- 数据类型
| 类型 | 类型举例 |
|---|---|
| 整数 | TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）、BIGINT |
| 浮点 | FLOAT、DOUBLE |
| 定点数 | DECIMAL |
| 位类型 | BIG |
| 日期时间类型 | YEAR、TIME、DATETIME、TIMESTAP |
| 文本字符串 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT |
| 枚举类型 | ENUM |
| 集合类型 | SET |
| 二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB |
| json类型 | JSON对象、JSON数组 |
| 空间数据类型 | 单值类型：GEOMETRY、POINT、LINESTRING、POLYGON; 集合类型：MULTIPOINT、MULTILINESTRING、MULTPOLYGON、GEOMETRYCOLLECTION |
- 数据类型属性
| mysql关键字 | 含义 |
|---|---|
| NULL | 数据列可包含NULL值 |
| NOT NULL | 数据列不允许包含NULL值 |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMENT | 自动递增，适用于整数类型 |
| UNSIGNED | 无符号 |
| CHARACTER SET name | 指定一个字符集‘utf8’（数据库、表、字段都可以指定） |
- 整数
- 可选属性
- M表示显示宽度，
注意：1.显示宽度与类型可存储的范围无关。2.后面结合使用ZEROFILL,表示显示宽度不足的前面用0补齐
- 浮点
- M不能超出宽度,D超出宽度会四舍五入
浮点数是不准确的，所以避免使用 “=”来判断两个数是否相等。所以精确的数据类型使用decimal
- 定点数
- 日期和时间类型
使用CURRENT_TIME()或者NOW()插入当前系统的时间
- 文本字符串类型
- 二进制字符串类型
- TEXT和BLOB的使用注意事项
- BLOB和TEXT在删除表时会留下很大的“空洞”，以后填入这些空洞的记录可能长度不同，为了提升性能，建议定期使用
OPTIMIZE TABLE功能对这类表进行碎片整理 - 对大文本字段进行模糊查询时，mysql提供了前缀索引，但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。
- 把BLOB和TEXT列分离到单独的表中
- BLOB和TEXT在删除表时会留下很大的“空洞”，以后填入这些空洞的记录可能长度不同，为了提升性能，建议定期使用
- TEXT和BLOB的使用注意事项
具体操作
# 库操作（增、删、改、查）
create database 库名
drop database 库名
alter 库名
show databases
# 表操作
create table ...(
username varchar;
password varchar;
);
mysql架构篇
数据存放位置
windows：C:\ProgramData\Mysql\Mysql Server8.0\Data
linux: /var/lib/mysql/
mac os: /usr/local/mysql/data
linux下的操作
docker部署mysql
# 点击进入阿里云进行镜像加速后
# 再进行如下操作
docker search mysql
docker pull mysql
docker images
1. 启动快捷方式
docker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
2. (配置容器MySQL数据、配置、日志挂载宿主机目录)
# 宿主机创建数据存放目录映射到容器
mkdir /usr/local/docker_data/mysql/data
# 宿主机创建配置文件目录映射到容器
mkdir /usr/local/docker_data/mysql/conf # (建议在此目录创建my.cnf文件并进行相关MySQL配置)
# 宿主机创建日志目录映射到容器
mkdir /usr/local/docker_data/mysql/logs
docker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d -v /usr/local/docker_data/mysql/data:/var/lib/mysql -v /usr/local/docker_data/mysql/conf:/etc/mysql/ -v /usr/local/docker_data/mysql/logs:/var/log/mysql mysql:5.7
docker exec -it mysqlserver bash
mysql -uroot -p
开启远程访问权限
use mysql;
select host,user from user;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
# 下载ifconfig
apt update
apt install net-tools
ubuntu部署mysql服务器
#更新apt-get库
sudo apt-get update
#安装mysql
sudo apt-get install mysql-server (-y)
#初始化配置mysql
sudo mysql_secure_installation
sudo mysql -u root -p
use mysql;
select host,user,plugin from user;
# 配置远程连接
update user set host='%' , plugin='mysql_native_password' where user='root';
# 授予root用户全部权限
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
vim /etc/mysql/mysql.conf.d/mysqld.cnf
#注释掉#bind-address =127.0.01，或者将其修改为0.0.0.0
# 查看mysql的服务状态
systemctl status mysql
netstat -ntlp (可看运行的端口号)
netstat -tap | grep mysql
# 查看版本号
sudo mysql --version
mysql -V
# 如果没有设置密码，查看密码,password
sudo cat /etc/mysql/debian.cnf
# 配置远程连接
sudo mysql -u root -P
use mysql;
select host,user,plugin from user;
update user set host='%' , plugin='mysql_native_password' where user='root';
flush privileges;
## 修改配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
将bind-address= 127.0.0.1注释，或修改为0.0.0.0
字符集
# 查看默认使用的字符集
show variables like 'character%';
# mysql5.7默认是latin，mysql8默认是utf8mb4
-
字符集的与比较规则（了解）
- utf8mb3:阉割过的utf8字符集，只使用1-3个字节表示字符
- utf8mb4：正宗的utf8字符集，使用1-4个字节表示字符
- 在mysql中utf8mb3是utf8的别名，正常使用mb3就够了，但是一些emoji表情，就使用的是mb4.
- 比较规则
show charset;
#查看服务器的字符集和比较规则 show variables like '%_server'; # 查看数据库的字符集和比较规则 show variables like '%_database'; # 查看具体数据库的字符集 show create database ....; # 修改具体数据库的字符集 alter database ... default character set 'utf8' collate 'utf8_general_ci'
基础-多表查询
多表关系
一对多：在多的一方设置外键，关联一的一方的主键
多对多：建立第三张表中间表，中间表至少包含两个外键，分别关联两个表的主键
一对一：第二张表相当于第一张表的拓展表，第二张表的外键有unique属性。
多表查询
- 内连接
隐式，显式（inner join） - 外连接
左外：left join on条件
右外：right join on条件 - 自连接
一定要给表取别名 - 联合查询
注意：
1.对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
2.union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重
sql 语句
union（union all）
sql语句
- 子查询
标量子查询：=、<>、>、>=、<、<=
列子查询：in、not in、any=some、all
行子查询：=、<>、in、not in（多条件查询用元组一一对应）
表子查询：in
基础-事务
是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
查看/设置事务提交方式：SELECT @@autocommit;
SET @@autocommit=0;
开启事务：START TRANSACTION 或 BEGIN
提交事务： COMMIT
回滚事务： ROLLBACK
事务的四大特性（ACID）
- 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性（consistency）：事务完成时，必须使所有的数据都保持一致。
- 隔离性（isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性（durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
并发事务问题
1.脏读：一个事务读到另外一个事务还没有提交的数据
2.不可重复读：一个事务先后读取到同一条记录，但两次读取的数据不同，称之为不可重复读
3.幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| read uncommitted | √ | √ | √ |
| read committed | × | √ | √ |
| repeatable read（默认） | × | × | √ |
| serializable(串行化) | × | × | × |
--查看事务隔离级别
select @@transaction_isolation;
--设置事务隔离级别
set [session|global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable]
进阶- 存储引擎
mysql体系结构图
存储引擎
存储引擎就是存储数据、建立索引】更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
- 查看表存储引擎（默认是innoDB）
show create table table_name;
- 查看数据库所有支持的引擎
show engines;
存储引擎特点
-
InnoDB
- 介绍：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在mysql5.5之后，InnoDB是默认的mysql存储引擎。
- 特点：DML操作遵循ACID模型，支持
事务；行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性。 - 文件：xxx_ibd（二进制文件，想要查看，cmd->ibd2sdi account.ibd），xxx代表的是表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。参数innodb_file_per_table,on表示每张表都有一个引擎。 使用
show variables like 'innodb_file_per_table';查看 
-
MyISAM
- 介绍：mysql早期的默认存储引擎
- 特点：不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快
- 文件：xxx.sdi存储表结构信息；xxx.MYD存储数据；xxx.MYI存储索引
-
Memory
- 介绍：Memory引擎的表数据存储在内存中，由于收到硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。
- 特点：内存存放、hash索引（默认）
- 文件：xxx.sdi存储表结构信息
-
Archive引擎：用于数据存档（不可修改）
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 自带系统表使用 | Y | N |
| 关注点 | 性能：节省资源、消耗少、简单业务 | 事务：并发写、事务、更大资源 |
| 默认安装 | Y | Y |
| 默认使用 | N | Y |
进阶- 索引
索引概述
索引（index）是帮助mysql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的io成本 | 索引列也是要占空间的 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert、update、delete时，效率降低 |
索引结构
mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构
| 索引结构 | 描述 | 支持的存储引擎 |
|---|---|---|
B+tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 | InnoDB、MyISAM、Memory |
| hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 | Memory |
| R+tree（空间索引） | 空间索引是MYISAM引擎的一个特殊索引类型，主要用于地理空间数据类型 | MyISAM |
| FULL-text（全文索引） | 是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene，Solr，ES | InnoDB(5.6之后)、MyISAM |
-
B+tree树
所有的元素都会出现在叶子节点。
mysql索引数据结构对经典的b+tree进行了优化。在原b+tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的b+tree，提高区间访问的性能。
-
hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到了一个相同的槽位上，他们就产生了hash冲突（碰撞），可以采用链表来解决- 特点：1.hash索引只能用于对等比较（=，in），不支持范围查询（between、>,<,…）。2.无法利用索引完成排序操作。3.查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
- 存储引擎支持：在mysql中，支持hash索引的是memory引擎，而innodb中具有自适应hash功能，hash索引是存储引擎根据b+tree索引在指定条件下自动建的。
面试问题
为什么InnoDB存储引擎选择使用B+tree索引结构
- 相对于二叉树，层级更少，搜索效率高
- 对于B-tree，其无论是叶子节点还是非叶子节点，都会保存数据，但是因为一页的大小是固定的（16k），这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
- 相对hash索引，b+tree支持范围匹配即排序操作
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
分类|含义|特点
聚集索引（clustered index）|将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据|必须有，而且只有一个
二级索引（secondary）|将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键|可以存在多个
所以根据主键字段进行查询的效率，大于根据一般字段进行查询的效率（一般查询，先查询到主键，再进行回表查询）
索引语法
- 创建索引（索引名规范：idx_表名_字段名）
create [UNIQUE | FULLTEXT] index_name on table_name (index_col_name,…); - 查看索引
show index from table_name; - 删除索引
drop index index_name on table_name;
sql性能分析
查看语句的执行频次
查看当前数据库的insert、update、delete、select的访问频次。
show [session | global] status like ‘com_______’;
慢查询日志
慢查询日志记录了所有执行时间超过指定指定参数（long_query_time,单位：秒，默认10秒）的所有sql语句的日志。mysql的慢查询日志默认没有开启，需要在mysql的配置文件（/etc/mysql/my.cnf）中配置如下信息(slow_query_log = 1
long_query_time =2 ,但是我失败了，所以我用了下面的方法)
# 查看慢查询日志是否开启，且日志位置
show variables like '%slow_query_log%';
show variables like '%long_query_time%';
# 开启慢查询日志
set global slow_query_log=ON;
# 设置慢查询日志阈值时间
SET GLOBAL long_query_time = 2;
每次修改之后，都需要重新开一个新的会话查询，因为旧的会话查询出来的始终是打开时的值，不会直接同步。
# 设置未使用index的数据也添加到慢查询日志
# 先查看是否打开
show variables like 'log_queries_not_using_indexes';
# 再进行设置
set global log_queries_not_using_indexes=on;
注意：log_queries_not_using_indexes参数要跟参数log_throttle_queries_not_using_indexes配合使用，（og_throttle_queries_not_using_indexes：该参数决定每分钟记录未使用索引的SQL的数量上限，因为未使用索引的SQL可能会非常多，导致慢日志空间增长飞快。）
# 打开记录管理的语句
show variables like '%log_slow_admin_statements%';
set global log_slow_admin_statements=1
MySQL5.7慢查询日志时间与系统时间差8小时原因详解
查看日志的时间（显示为UTC）
show [global] variables like ‘log_timestamps’;
设置为系统时间
SET GLOBAL log_timestamps = SYSTEM;
show profile
show profilen能够在做sql优化时帮助我们了解时间都耗费到哪里去了。
# 查看当前数据库是否支持profile操作
select @@hava_profiling;(但是我执行报错了)
# 查看是否开启profiling
select @@profiling;
# 默认profiling是关闭的，可以通过set语句在session/global级别开启profiling
set profiling = 1;
执行一系列的业务sql的操作，然后通过如下指令查看指令的执行耗时：(好像不支持了)
# 查看每一条sql的耗时基本情况
show profiles;
# 查看指定query_id的sql语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的sql语句cpu的使用情况
show profile cpu for query_id;
explain执行计划
explain 或者desc 命令获取mysql如果执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序。
语法：直接在select 语句之前加上关键字explain/desc
explain执行计划各字段含义：
- id
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的值越大越好。
索引使用规则
验证索引效率
#在未建立索引之前，执行如下sql语句，查看sql的耗时,耗时时间为10秒
select * from tb_sku where sn='1232';
# 针对sn字段创建索引
create index idx_name on tb_sku(sn);
# 然后再进行查询，时间变为0.01sec。
索引失效情况
- 最左前缀法则
如果索引了多列（联合索引）,要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。 - 范围查询
联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效。（但是不包含>=,<=，这样的范围查询右侧列索引不失效） - 索引列运算
不要在索引列上进行运算操作，索引将失效 - 字符串不加引号
字符串类型索引字段使用时，不加引号，索引将失效。 - 模糊查询
如果仅仅时尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。 - or连接的条件
用or分割开的条件，如果or的只有一边的列有索引，那么涉及的索引都不会被用到。（解决办法就是给没有索引的列添加索引） - 数据分布影响
如果mysql评估使用索引比全表（不使用索引）更慢，则不使用索引。 - sql提示
sql提示，是优化数据库的一个重要手段，简单来说，就是在sql语句中加入一些人为的提示来达到优化操作的目的
use index:建议用此索引
ignore index：忽略
force index：强制
explain select * from tb_user force_index(idx_user_id) where id='1';
覆盖索引和回表查询
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引（二级索引）中已经全部能够找到），减少select 。
例如：extra字段中-using index condition/null表示查找使用了索引，但是需要回表查询数据。
using where；using index表示查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。
面试题：一张表，有四个字段（id，username，password，status），由于数据量大，需要对以下sql语句进行优化，该如何进行才是最优方案:
** select id,username,password from tb_user where username=‘sober’;*
对username和password建立一个联合索引，避免回表操作
前缀索引
当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘io，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：create index idx_xxx on table_name(column(n));
前缀长度：
可以根据索引的选择性来决定，而选择性时指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
求选择性的数值
select count(distinct name)/count(*) from sale_client;
select count(distinct substring(name,1,4)) / count(*) from sale_client;
单列索引与联合索引
单列索引：即一个索引只包含单个列
联合索引：即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
索引设计原则
1.针对于数据量较大（一百万以上），且查询比较频繁的表建立索引
2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率，占用磁盘空间
7.如果索引列不能存储null值，请在创建表时使用not null约束它。当优化器知道每列是否包含null值时，它可以更好的确定哪个索引最有效地用于查询。
进阶- sql优化
插入数据（insert优化）
- 批量插入
- 手动提交事务
- 主键顺序插入
- 大批量插入数据时
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用mysql数据库提供的load指令进行插入。操作如下：
# 客户端连接服务端时，加上参数--local-infile
mysql --local-infile -u root -P
# 查看local_infile 的参数
select @@local_infile;
# 设置全局参数local_infile 为 1，开启从本地加载文件导入数据的开关
set global local_infile =1 ;
# 查看数据有几行
wc -l xxx
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/xxxx' into table 'table_name' fields teminated by ',' lines teminated by '\n';
主键顺序插入性能高于乱序插入
主键优化
- 数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized tableIOT） - 页分裂（
主键乱序插入的情况下）
也可以为空，也可以填充一半，也可以填充100%，每个页包含了2-n行数据（如果一行数据过大，会行溢出），根据主键排列。 - 页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到merge_threshold(默认为页的50%)，InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
merge_threshold:合并页的阈值，可以自己设置，在创建表或者创建索引时指定 - 主键设计原则
1.满足业务需求的情况下，尽量降低主键的长度
2.插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键
3.尽量不要使用uuid做主键或者是其他自然主键，如身份证号（会出现页分裂）
4.业务操作时，避免对主键的修改。
order by优化
- Using filesort:
通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序 - Using index:
通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
# 创建一个升序，一个降序的索引
create index idx_age_ptone_ad on tb_user(age asc ,phone desc);
1.根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
2.尽量使用覆盖索引
3.多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
4.如果不可避免的出现filesort，大数据排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k),(show variables like ‘sort_buffer_size’😉
group by优化
1.在分组操作时，可以通过索引来提高效率
2.分组操作时，索引的使用也是满足最左前缀法则的
limit 优化
一个常见的又非常头疼的问题就是limit 2000000,10 ，此时需要mysql排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
explain select t.* from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
count 优化
- MyISAM 引擎把一个表地总行数存在磁盘上，因此执行count（*）的时候会直接返回这个数，效率很高
- innoDB引擎就麻烦了，他执行count（*）的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数
优化思路：自己计数，比如把结果保存在redis中。insert和drop后对总数进行计算。
s】
- count的几种用法
- count(主键)
InnoDB引擎会遍历整张表，把每一行的主键值都取出来，返回给服务器。服务层拿到主键后，直接按行进行累加（主键不可能为null）。 - count（字段）
没有not null字段：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。 - count（1）
InnoDB引擎会遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。 - count（*）
InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
按效率排序的话，count（字段）< count(主键id) < count(1) = count(*),所以一般用count（*）
- count(主键)
update优化
# 行级锁
update student set no = '123' where id =1 ;
# 表锁
update student set no = '123' where name ='sober' ;
innodb的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。
进阶- 视图/存储过程/触发器
进阶- 锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算机资源（cpu、RAM、I/O）的争用外，数据也是一种供很多用户共享的资源。如果保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
全局锁
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
# 进入数据库，加锁
flush tables with read lock;
# 备份
mysqldump -u root -p 123456 要备份的库 > backup.sql
# 解锁
unlock tables;
- 特点
数据库中加全局锁，是一个比较重的操作，存在以下问题：
1.如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆了
2.如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟
在innodb引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。
mysqldump -- single-transaction -u root -p 123456 要备份的库 > backup.sql
表级锁
- 表锁
对于表锁，分为两类：
1.表共享读锁（read lock）
2.表独占写锁（write lock）
# 加锁
lock tables 表名 read / write
# 释放锁
unlock tables / 客户端断开连接
读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写
- 元数据锁（meta data lock，MDL）
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
在mysql5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）。
| 对应的sql | 锁类型 | 说明 |
|---|---|---|
| lock tables xxx read/write | SHARED_READ_ONLY / SHARED_NO_READ_WRITE | |
| select 、select …lock in share mode | SHARED_READ | 与SHARED_READ、SHARED |
| insert、update、delete、select … for update | SHARED_WRITE | 与SHARED_READ、SHARED_WRITE兼容、与EXCLUSIVE互斥 |
| alter table … | EXCLUSIVE | 与其他的MDL都互斥 |
# 查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
- 意向锁
为了避免DML在执行时，加的行锁与表锁的冲突，在innodb中引入了意向锁，以使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
1.意向共享锁（IS）：由语句 select … lock in share mode添加。与表锁共享锁（read）兼容，与表锁排它锁（write）互斥。
2.意向排他锁（IX）：由insert、update、delete、select…for update添加。与表锁共享锁（read）及排它锁（write）都互斥，意向锁之间不会互斥。
# 查看意向锁及行锁的加锁情况：
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
行级锁
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在innodb存储引擎中。
- 行锁（record lock）：锁住单个记录的锁，防止其他事务对此进行update和delete。在RC、RR隔离级别中都支持。
1.共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
2.排它锁（X）: 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
| 请求锁 -------- 当前锁 | S | X |
|---|---|---|
| S | 兼容 | 冲突 |
| X | 冲突 | 冲突 |
| sql | 行级类型 | 说明 |
|---|---|---|
| insert… | 排他锁 | 自动添加 |
| update… | 排他锁 | 自动加锁 |
| delete… | 排他锁 | 自动加锁 |
| select(正常) | 不加任何锁 | |
| select … lock in share mode | 共享锁 | 需要手动在select 之后加lock in share mode |
| select … for update | 排他锁 | 需要手动在select之后加for update |
# 默认情况下，innodb在repeatable read事务隔离级别运行，innodb使用next-key锁进行搜索和索引扫描，以防止幻读。
1.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
2.innodb的行锁是针对于索引加的锁，不通过索引条件检索数据，那么innodb将对表中的所有记录加锁，此时`就会升级为表锁`
# 查看意向锁及行锁的加锁情况：
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
- 间隙锁（Gap lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
- 临键锁（Next-key lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别中支持。
默认情况下，innodb在repeatable read事务隔离级别运行，innodb使用next-key锁进行搜索和索引扫描，以防止幻读。
1.索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
2.索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
3.索引上的范围查询（唯一索引）-- 会访问到不满足条件的第一个值为止。
注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁
进阶- InnoDB引擎
进阶- mysql管理
系统数据库
mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：
| 数据库 | 含义 |
|---|---|
| mysql | 存储mysql服务器正常运行所需要的各种信息（时区、主从、用户、权限等） |
| information_schema | 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等 |
| performance_schema | 为mysql服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数 |
| sys | 包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图 |
常用工具
mysql
该mysql不是指mysql服务，而是指mysql的客户端工具
语法：mysql [options] [database]
选项：
-u, --user=name # 指定用户名
-p, --password[=name] # 指定密码
-h,--host=name # 指定服务器ip或域名
-P, --Post=post # 指定连接端口
-e,--execute=name # 执行sql语句并退出
-e选项可以在mysql客户端执行sql语句，而不用连接到mysql数据库再执行，对于一些批处理脚本，这种方式尤其方便。
示例：
mysql -h192.168.30.40 -P3306 -uroot -p123456 db01 -e"select * from stu";
mysqladmin
mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
# 查看帮助文档
mysqladmin --help
# 示例：
mysqlamdin -uroot -p123456 drop ‘test01’
mysqladmin -uroot -p123456 version
mysqladmin -uroot -p123456 password root123;
mysqlbinlog
由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具。
语法:
mysqlbinlog [options] log-file1 log-file2...
选项：
-d --database =name # 指定数据库名称，只列出指定的数据库相关操作。
-o,--offset=# # 忽略掉日志中的前n行命令
-v， # 将行事件（数据变更）重构为sql语句
-w， # 将行事件（数据变更）重构为sql语句，并输出注释信息
-r,--result-file=name # 将输出的文本格式日志输出到指定文件
-s,--short-form # 显示简单格式，省略掉一些信息。
--start-datatime=date1 --stop-datetime = date2
-- start-postion = pos1 --stop-position=pos2
mysqlshow
mysqlshow客户端对象查找工具，用来很快地查存在哪些数据库、数据库中的表、表中的列或者索引。
语法：
mysqlshow [options] [db_name [table_name [col_name]]]
选项：
--count 显示数据库及表的统计信息（数据库，表均可以不指定）
-i 显示指定数据库或者指定表的状态信息
示例：
# 查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p123456 --count
# 查询test库中每个表中的字段数，及行数
mysqlshow -uroot -p123456 test --count
# 查询test库中book表的详细情况
mysqlshow -uroot -p123456 test book --count
mysqldump(用于逻辑备份)
mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的sql语句。
语法：
mysqldump [options] db_name [tables]
mysqldump [options] --database /B db1[db2 db3...]
mysqldumo [options] --all-databases/-A
连接选项：
-u，--user = name # 指定用户名
-p，--password[=name] # 指定密码
-h,--host=name # 指定服务器ip或域名
-P，--port=# # 指定连接端口
输出参数：
--add-drop-database # 在每个数据库创建语句前加上drop database语句
--add-drop-table # 在每个表创建语句前加上drop table语句，默认开启；不开启（--skip-add-drop-table）
-n，no-create-db # 不包含数据库的创建语句
-t,--no-create-info # 不包含数据表的创建语句
-d，--no-data # 不包含数据,只有表结构
-T，--tab=name # 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件(注意：要使用show variables like 'secure_file_priv';查看允许写入.txt的文件目录，使用别的路径报错，路径写在-T后面)
完整示例：
# 备份一个数据库
mysqldump -u用户名称 -h主机名称 -p密码 待备份的数据库名称[tbname] > 备份文件路径名称.sql
# 备份全部数据库
mysqldump -uroot -pxxxxxx --all-database > all_database.sql
# 备份部分数据库
mysqldump -uroot -p --databases(-B) center1 center2 > two_database.sql
# 备份某一数据库中部分表
mysqldump -uroot -p center user book >part_tables.sql
# 备份表中的部分数据
mysqldump -uroot -p center user --where="id<10" > part_datas.sql
# 排除某些表的备份
mysqldump -uroot -p center --ignore-table=center.user > no_user.sql
mysqlimport/source
mysqlimport是客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件。
语法：
mysqlimport [options] db_name textfile1 [textfile2...]
示例：
mysqlimport-uroot -p123456 /var/lib/mysql-file/1.txt
# 如果需要导入sql文件，可以使用mysql中的source指令：
source /root/xxx.sql
# 直接导入sql文件
mysql -uroot -p123456 store < areas.sql
运维- 日志
错误日志
错误日志是mysql中最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志
# 该日志时默认开启的，默认存放目录/var/log/（但是我的不是）,默认的日志文件名为mysql.log,查看日志位置：
show variables like '%log_error%';
# 查看最新50条日志
tail -50 xxx
二进制日志
- 介绍
二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操作语言）语句，但不包含数据查询（select、show）语句。
作用：1.灾难时的数据恢复；2.mysql的主从复制。在mysql8版本中，默认二进制日志是开启着的，涉及到的参数如下：
show variables like '%log_error%';
- 日志格式
mysql服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：
| 日志格式 | 含义 |
|---|---|
| STATEMENT | 基于sql语句的日志记录，记录的是sql语句，对数据进行修改的sql都会记录在日志文件中。 |
| ROW | 基于行的日志记录，记录的是每一行的数据变更。（默认） |
| MIXED | 混合了STATAMENT 和 ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。 |
# 查看日志格式
show variables like '%binlog_format%';
-
日志查看
mysqlbinlog 日志文件 -
日志删除
对于业务繁忙的系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：
| 指令 | 含义 |
|---|---|
| reset master | 删除全部binlog日志，删除之后，日志编号，将从binlog.000001重新开始 |
| purge master logs to ‘binlog.000002’ | 删除000002编号之前的所有日志（不包括此日志） |
| purge master logs before ‘yyyy-mm-dd hh24:mi:ss’ | 删除日志为“yyyy-mm-dd:mi:ss”之前产生的所有日志 |
# mysql配置文件中配置二进制日志的过期时间，过了时间，二进制日志会自动删除
show variables like '%binlog_expire_logs_seconds%';
查询日志
查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的sql语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置：
# 查看查询日志的配置
show variables like '%general%';
# 开启
set global general_log = 1
# 设置文件路径
set global general_log_file = xxxx
或者修改mysql的配置文件/etc/my.cnf(但是跟我的系统文件路径不一致)，加上general_log =1, general_log_file = xxxx.
慢查询日志
见进阶-索引-sql性能分析
运维- 主从复制
概述
主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库中对这些日志重新执行（也叫重做），从而使得从库（slave）和主库（master）得数据保持同步。
mysql支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器得主库，实现链状复制。
- 优点：
1.主库出现问题，可以快速切换到从库提供服务。
2.实现读写分离，降低主库的访问压力。
3.可以在从库中执行备份，以避免备份期间影响主库服务。
原理
从上图来看，复制分成三步：
1.master主库在事务提交时，会把数据变更记录在二进制日志文件binlog中
2.从库读取主库的二进制日志文件binlog，写入到从库的中继日志relay log。
3.slave重做中继日志中的事件，将改变生成它自己的数据。
搭建
- 准备服务器
准备好至少两台服务器之后，在上述的两台服务器中分别安装好mysql，并完成基础的初始化工作
# 开发指定的3306端口：
firewall-cmd --zone=public --add-port=3306/tcp -permanent
firewall-cmd -reload
# 关闭服务器的防火墙
systemctl stop firewall
systemctl disable firewall
主库配置
- 修改配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf
# mysql服务id，保证整个集群环境中唯一，取值范围:1--2的32次方-1，默认为1
server-id=1
# 是否只读，1代表只读，0代表读写
read-only = 0
# 忽略的数据，指不需要同步的数据库
# binlog-ignore-db = mysql
# 指定同步的数据库
# binlog-do-db=db01
- 重启mysql服务器
systemctl restart mysql
- 登录mysql，创建远程连接的账号，并授予主从复制权限
# 授权密码权限放宽
set global validate_password_policy = 0;
set global validate_password_length = 1;
# 创建sober用户，并设置密码，该用户可在任意主机连接该mysql服务
create user 'sober'@'%' IDENTIFIED with mysql_native_password by '123456';
# 为'sober'@'%'用户分配主从复制权限
grant replication slave on *.* to 'sober'@'%' (identified by '123456');
- 通过指令，查看二进制日志坐标
show master status;
字段含义说明：
file：从哪个日志文件开始推送日志文件（master_log_file）
position：从哪个位置开始推送日志 （master_log_pos）
binlog_ignore_db：指定不需要同步的数据库
从库配置
- 修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf如果是docker 就是my.cnf
# mysql服务id，保证整个集群环境中唯一，取值范围:1--2的32次方-1，和主库不一样即可
server-id=2
# 普通用户是否只读，1代表只读，0代表读写
read-only =1
# 超级管理员是否只读，1代表只读，0代表读写
# super-read-only =1
- 重启mysql服务器
- 登录mysql，设置从库配置
change replication source to source_host='47.97.118.247',source_port=3306,source_user='root',source_password='123456',source_log_file='binlog.000026',source_log_pos=6692;
# 上述是8.0.23中的语法，如果mysql是8.0.23之前的版本，执行如下sql：
change master to master_host = '172.17.0.1', master_user='sober',master_password='123456',master_log_file='binlog.000071',master_log_pos=xxx;
| 参数名 | 含义 | 8.0.23之前 |
|---|---|---|
| source_host | 主库ip地址 | master_host |
| source_user | 连接主库的用户名 | master_user |
| source_password | 连接主库的密码 | master_password |
| source_log_file | binlog文件名 | master_log_file |
| source_log_pos | binlog日志文件位置 | master_log_pos |
- 开启同步操作
start replica; # 8.0.22之后
start slave； # 8.0.22之前
- 查看主从同步状态
show replica status\G; #8.0.22之后
show slave status; # 8.0.22之前
测试
- 在主库上创建数据库、表，并插入数据
create database db01;
use db01;
create table tb_user(
id int (11) PRIMARY key not null AUTO_INCREMENT,
name varchar(50) not null,
sex varchar(1)
)engine=innodb default charset=utf8mb4;
insert into tb_user(id,name,sex) values(null,'sober','1'),(null,'maohi','0');
- 在从库中查询数据，验证主从是否同步
注意
1.如果开启replica之后，IO线程显示为NO，那么可以尝试重启mysql
2.如果主数据库的表中已经有数据了，要先把数据移到从数据库的表中，再进行开启主从复制等操作
使用mysqldump完成备份主数据库的数据之后，使用sql或txt文件在从数据库恢复
# 如果备份的sql文件中有创建数据库的语句
mysql -uroot -p < center.sql
# 如果备份的sql文件中有没有创建数据库的语句
mysql -uroot -p center < center.sql
# 从全量备份中恢复单库：我们有整个实例的备份，但是我们只想恢复整个实例中某一个库,使用以下的语句生产新的sql文件
sed -n '/^-- Current Database: 'center'/ , /^--Current Database: '/p' all_databases.sql > center.sql
# 如果我们有一个库的备份，但是由于user表误操作，需要单独恢复这张表
# 用shell语法分离出创建表的语句及插入数据的语句后，再依次导出即可完成恢复
cat center.sql | sed -e '/ ./{H;$!d;}' -e 'x;CREATE TABLE 'class'/!d;q' > class_structure.sql
cat center.sql | grep --ignore-case 'insert into 'class'' > user_data.sql
use center；
source class_structure.sql;
source user_data.sql;
运维- 分库分表
介绍
- 问题分析
- IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率降低。请求数据太多，带宽不够，网络IO瓶颈。
- CPU瓶颈：排序、分组、连接查询、聚合统计等sql会耗费大量的cpu资源，请求数太多，cpu出现瓶颈。
分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。
# 为什么分库：
1.磁盘存储：业务量剧增，MySQL单机磁盘容量会撑爆，拆成多个数据库，磁盘使用率大大降低。
2.并发连接支撑：我们知道数据库连接是有限的。在高并发的场景下，大量请求访问数据库，MySQL单机是扛不住的！
# 为什么分表：数据量太大的话，SQL的查询就会变慢。如果一个查询SQL没命中索引，千百万数据量级别的表可能会拖垮整个数据库。
-
拆分策略
- 垂直分库
以表为依据，根据业务将不同表拆分到不同库中。
特点：
1.每个库的表结构都不一样
2.每个库的数据也不一样
3.所有库的并集是全量数据 - 垂直分表
以字段为依据，根据字段的属性将不同字段拆分到不同的表中。
1.每个表的结构都不一样
2.每个表的数据也不一样，一般通过一列（主键/外键）关联
3.所有表得并集是全量数据 - 水平分库
以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
1.每个库的表结构都一样
2.每个库的数据都不一样
3.所有库的并集是全量数据。 - 水平分表
以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
1.每个表的结构都一样。
2.每个表的数据都不一样
3.所有表的并集是全量数据
- 垂直分库
-
实现技术
- shardingJDBC：基于AOP原理，在应用程序中对本地执行的sql进行拦截，解析、改写、路由处理。需要自行编码配置完成，只支持java语言，性能较高
- Mycat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。
mycat 概述
- 介绍
mycat是开源的、活跃的、基于java语言编写的mysql数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在 - 优势
- 性能可靠稳定
- 强大的技术团队
- 体系完善
- 社区活跃
- 下载
下载地址：http://dl.mycat.org.cn/ - 安装
mycat是采用java语言开发的开源的数据中间件，支持windows和linux运行环境，下面介绍mycat的linux中的环境搭建。我们需要在准备好的服务器中安装mysql、jdk、mycat
| 服务器 | 安装软件 | 说明 |
|---|---|---|
| xxx.210 | jdk、mycat | mycat中间件 |
| xxx.210 | mysql | 分片服务器 |
| xxx.213 | mysql | 分片服务器 |
| xxx.214 | mysql | 分片服务器 |
# 解压jdk到指定目录
tar -zxvf jdk-xxx.tar.gz -C /usr/local/
# 配置环境变量vim /etc/profile
JAVA_HOME = /usr/local/jdk1.8xxxx
PATH=$JAVA_HOME/bin:$PATH
# 激活配置文件
source /etc/profile
# 查看jdk版本
java -version
# 解压mycat
tar -zxvf mycat-xxx.tar.gz -C /usr/local/
# 文件解析
bin： 存放可执行文件，用于启动停止mycat
conf：存放mycat的配置文件
lib： 存放mycat的项目依赖包（jar）
logs： 存放mycat的日志文件
- mycat核心概念
mycat 入门
- 需求
由于tb_user表中
mycat 配置
mycat 分片
mycat 管理及监控
运维- 读写分离
介绍
读写分离。简单地说是对数据库地读和写操作分开，以对应不同地数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库地压力
通过mycat即可轻松实现上述功能，不仅可以支持mysql，也可以支持oracle和sql server。
一主一从读写分离
- 配置
mycat控制后台数据库地读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制
负载均衡策略，目前有4种
| 参数值 | 含义 |
|---|---|
| 0 | 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上 |
| 1 | 全部的readhost与备用的writehost都参与select语句的负载均衡（主要针对与双主双从模式） |
| 2 | 所有的读写操作都随机在writehost，readhost上分发 |
| 3 | 所有的读请求随机分发到writehost对应的readhost上执行，writehost不负担读压力 |
- 测试
连接mycat ，并在mycat中执行DML、DQL查看是否能够进行读写分离
问题：主节点master宕机之后，业务系统就只能够读，而不能写入数据了
双主双从
- 介绍
一个主机master1用于处理所有写请求，它的从机slave1和应一个主机master2以及它的从机slave2负责所有读请求。当master1主机宕机后，master2主机负责写请求，master1、master2互为备机。
- 搭建
-
主库配置（master1）
``` # 修改配置文件 # mysql服务id，保证整个集群环境中唯一，取值范围：1-2的32次方-1，默认为1 server-id =1 # 指定同步的数据库 binlog-do-db= db01 binlog-do-db= db02 binlog-do-db= db03 # 在作为从数据库的时候，有写入操作也要更新二进制日志文件 log- slave-updates # 重启mysql服务器 ``` -
主库配置（master2）
``` # 修改配置文件 # mysql服务id，保证整个集群环境中唯一，取值范围：1-2的32次方-1，默认为1 server-id =3 # 指定同步的数据库 binlog-do-db= db01 binlog-do-db= db02 binlog-do-db= db03 # 在作为从数据库的时候，有写入操作也要更新二进制日志文件 log- slave-updates # 重启mysql服务器 ``` -
两台主库分别创建账号并授权
``` # 创建sober用户，并设置密码，该用户可在任意主机连接该mysql服务 create user 'sober'@'%' IDENTIFIED with mysql_native_password by '123456'; # 为'sober'@'%'用户分配主从复制权限 grant replication slave on *.* to 'sober'@'%'; # 查看两台主库的二进制日志坐标 show master status; ``` -
从库配置（slave1）
``` # 修改配置文件 server-id=2 # 重启mysql服务器 ``` -
从库配置（slave2）
``` # 修改配置文件 server-id=2 # 重启mysql服务器 ``` -
两台从库配置关联的主库
``` change replication source to source_host='XXX.xxx',source_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos='xxx'; # 上述是8.0.23中的语法，如果mysql是8.0.23之前的版本，执行如下sql： change master to master_host = '172.17.0.1', master_user='sober',master_password='123456',master_log_file='binlog.000071',master_log_pos=xxx; # 启动两台从库主从复制，查看从库状态 start slave; show slave status \ G; ``` `需要注意slave对应的是master1，slave2对应的是master2.` -
两台主库相互复制
master1复制master2，master2复制master1.``` change replication source to source_host='XXX.xxx',source_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos='xxx'; # 上述是8.0.23中的语法，如果mysql是8.0.23之前的版本，执行如下sql： change master to master_host = '172.17.0.1', master_user='sober',master_password='123456',master_log_file='binlog.000071',master_log_pos=xxx; # 启动两台从库主从复制，查看从库状态 start slave; show slave status \ G; ```
-
- 测试
分别在两台主库master1、master2上执行DDL、DML语句，查看涉及到的数据库服务器的数据同步情况。
双主双从读写分离
- 配置
- 测试
登录mycat，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确
当主库挂掉一个之后，是否能够自动切换。
Docker下载Mysql
docker search mysql
docker pull mysql:latest
docker images
# 运行
docker run -d \
--name mysql \
--restart=always \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:latest
docker ps
# 进入容器
docker exec -it 68a63f41845e bash
#登录
MySQL：mysql -u root -p
开启远程连接
mysql -u root -p
use mysql;
update user set host = '%' where user ='root';
flush privileges;
quit;
数据库的误删除后的补救措施
传统的高可用架构是不能预防误删操作的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。
- 误删操作分类：
1.使用delete 语句删除数据行
2.使用drop table或者truncate table语句误删数据表
3.使用drop database语句误删数据库
4.使用rm命令误删整个mysql实例
delete：误删行
- 处置措施1：数据恢复
使用flashback工具恢复数据。
原理：修改binlog内容，拿回原库重放。如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。
使用前提：binlog_format=row 和binlog_row_image=FULL - 处置措施2：预防
- 代码上线前，必须sql审查、审计
- 建议打开安全模式，把sql_safe_update参数设置为on。强制要求加where条件且where后需要是索引字段，否则必须使用limit。否则就会报错。
truncate/drop：误删库/表
- 背景：delete全表是很慢的，需要生成回滚日志、写redo、写binlog。所以，从性能角度考虑，优先考虑使用truncate或者drop table命令。
使用delete 命令删除的数据，你还可以用Flashback来恢复。而使用truncate/drop table和drop database 命令删除的数据，就没办法通过flashback来恢复了。因为，即使我们配置了binlog_format=row,执行这三个命令时，记录的binlog还是statement格式。binlog里面就只有一个truncate/drop语句，这些信息是恢复不出数据的。 - 方案：这种情况下恢复数据，需要使用全量备份与增量日志结合的方式。
方案的前提是：有定期的全量备份，并且实时备份binlog。
举例：有人误删了一个库，时间为下午三点。步骤如下：
1.取最近一次全量备份。假设设置数据库是一天一备份，最近备份数据是当天凌晨2点
2.用备份恢复出一个临时库；（注意：这里选择临时库，而不是直接操作主库）
3.取出凌晨2点后的binlog日志
4.剔除误删除数据的语句外，其他语句全部应用到临时库。
5.最后恢复到主库
- 预防使用truncate/drop误删库/表
1.权限分离：限制账户权限，不同账号的权限做限制和分离
2.制定操作规范：比如再删除数据之前，必须先对表做改名操作（比如加_to_be_deleted）。然后，观察一段时间后，确保对业务无影响以后再删除这张表。
3.设置延迟复制备库：简单的说延迟复制就是设置一个固定的延迟时间，比如1个小时，让从库落后主库一个小时。出现误删除1个小时内，到这个备库上执行stop replica,再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。这里通过CHANGE REPLICATION TO SOURCE_RELAY = N命令,可以指定这个备库持续跟主库有N秒的延迟。
rm：误删mysql实例
对于一个有高可用机制的mysql集群来说，不用担心rm删除数据。因为只删除其中某一个节点数据的话，HA系统就会选出一个新的主库，从而保证整个集群的正常工作。我们把整个节点上的数据恢复回来后，再接入整个集群就好了。
mysql in linux
官方文档：https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/#apt-repo-remove
apt安装mysql，并新建一个数据库
sudo apt-get install mysql-server
sudo mysql_secure_installation # 初始化密码，
mysql -uroot -p （出现无密码无法直接enter登录情况）
show databases;
# 新建数据库就指明编码：
create database if not exists center default charset = utf8;
# 看看是否创建成功：
show databases;
初始化密码遇到错误
… Failed! Error: SET PASSWORD has no significance for user ‘root’@‘localhost’ as the authentication method used doesn’t store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
sudo mysql
# 在sql命令行输入以下命令回车，你就可以把密码改成mynewpassword
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';
sudo mysql_secure_installation
解决首次安装登录后第一次登录无密码无法登录
step1:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
在配置文件中[mysqld]下方加入 skip-grant-tables 后wq保存退出。
systemctl restart mysql 重启mysql
step2：
1.use mysql;
2.ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY'自己的密码'; (如果执行失败，刷新权限)
3.flush privileges;
step3:
注释掉skip-grant-tables
重启mysql： /etc/init.d/mysql restart
配置远程登录(必须注释掉skip-grant-tables)
step1：
vim /etc/mysql/mysql.conf.d/mysqld.cnf
注释掉 ： bind-address = 127.0.0.1
重启mysql：systemctl restart mysql
mysql -uroot -p
use mysql;
show tables;
select host,user from user;
step2：
# 添加登录账号,允许远程访问'%'(如果是存在的用户直接grant)
create user 'root'@'%' identified with mysql_native_password by 'root';
create user 'dell'@'%' identified with mysql_native_password by '1!deshine';
grant all privileges on *.* to 'dell'@'%' ;
（DROP USER 'dell'@'%';）
# 直接赋予root用户
update user set user.Host='%' where user.User='root';
step3:
sudo ufw status # 检查ubuntu自带的防火墙状态
如果防火墙是开着：
sudo ufw disable #关闭防火墙
sudo ufw allow 3306 #添加一条规则让防火墙放行3306
ubuntu卸载mysql
1.我们需要查看mysql依赖项
dpkg --list | grep mysql
2.卸载删除依赖
sudo rm /var/lib/mysql/ -R 删除mysql的数据文件
sudo rm /etc/mysql/ -R 删除mysql的配置文件
sudo apt-get autoremove mysql* --purge
sudo apt-get remove apparmor
dpkg --list|grep mysql
卸载完成后再进行一次卸载：
sudo apt-get autoremove mysql* --purge
dpkg -l | grep ^rc| awk '{print$2}'| sudo xargs dpkg -P 清除残留数据
dpkg --list|grep mysql
（# 如果没删干净
rm -rf /etc/mysql/ #删除MySQL配置文件：
rm -rf /var/lib/mysql/ # 删除MySQL数据目录：
userdel -r mysql # 删除MySQL用户和组：
groupdel mysql）
3.检查依赖项，如果为空，彻底卸载干净了
dpkg --list | grep mysql
4.不为空那么我们还要继续进行删除卸载
apt autoremove --purge mysql-apt-config
实战
存储过程
BEGIN
DECLARE n int DEFAULT 40000012;
wile n < 40000015 do
INShERT into sample(type,create_time,update_time) values (0,now(),now());
set n = n+1;
end WHILE;
end
python操作mysql
pip install pymysql
import pymysql
插入数据
1.连接mysql
conn =pymysql.connect(host=‘127.0.0.1’,port=3306,user=‘root’,passwd=“123456”,charset=‘utf8’,db=‘users’)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
2.发送指令(切记：！！！！千万不要用字符串格式化去sql拼接，会被sql注入)
sql = “insert into user(username,password,sexy) values(%s,%s,%s)”
cursor.execute(sql,[“zhangsan”,“123456”,“male”])
# sql = “insert into user(username,password,sexy) values(%(n1)s,%(n2)s,%(n3)s)”
# cursor.execute(sql,{“n1”:“zhangsan”,“n2”:“123456”,“n3”:“female”})
conn.commit()
3.关闭连接
cursor.close()
conn.close()
查询数据
1.连接mysql
conn =pymysql.connect(host=‘127.0.0.1’,port=3306,user=‘root’,passwd=“123456”,charset=‘utf8’,db=‘users’)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
2.发送指令(切记：！！！！千万不要用字符串格式化去sql拼接，会被sql注入)
sql = “select * from user where user_id >%s”
cursor.execute(sql,[1])
data_list=cursor.fetchall()获取符合条件的所有数据-----没有数据是空列表
data_list=cursor.fetchone()获取符合条件的第一条数据----没有数据是None
print(data_list)
for row_dict in data_list:
print(row_dict)
conn.commit()
3.关闭连接
cursor.close()
conn.close()
删除数据
1.连接mysql
conn =pymysql.connect(host=‘127.0.0.1’,port=3306,user=‘root’,passwd=“123456”,charset=‘utf8’,db=‘users’)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
2.发送指令(切记：！！！！千万不要用字符串格式化去sql拼接，会被sql注入)
sql = “delete from user where user_id=%s”
cursor.execute(sql,[3,])
conn.commit()
3.关闭连接
cursor.close()
conn.close()
修改数据
1.连接mysql
conn =pymysql.connect(host=‘127.0.0.1’,port=3306,user=‘root’,passwd=“123456”,charset=‘utf8’,db=‘users’)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
2.发送指令(切记：！！！！千万不要用字符串格式化去sql拼接，会被sql注入)
sql = “update user set sexy=%s where username=%s”
cursor.execute(sql,[“female”,“zhangsan”])
conn.commit()
3.关闭连接
cursor.close()
conn.close()
注意！！！！
查可以没有commit，增、删、改必须要有commit，不然数据库中的数据不会发生改变
不要用python的字符串格式化进行拼接（会被sql注入），要用execute+参数
扫一扫
313
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
