MySQL常用指令

mysql全局变量

#查看mysql所有全局变量

show global variables;   或   show variables;

#修改mysql全局变量

set global variableName = 值;

mysql字符集

#查看当前数据库字符集

show variables like '%char%';   或   show variables like 'character%';

#修改字符集

set variableName = 值;

mysql密码规则配置(前提：mysql需要安装validate_password组件)

#查看用户密码配置规则

show variables like 'validate_password%';

#若查询不到用户密码配置规则，则下载 validate_password 组件

install component 'file://component_validate_password';

#卸载 validate_password 组件

uninstall component 'file://component_validate_password';

#修改mysql全局变量

set global variableName = 值;

#将mysql密码安全等级改为低级（即满足密码最小长度）

set global validate_password.policy = 0;   或   set global validate_password.policy = 'LOW';

#将mysql密码安全等级改为中级（即满足密码最小长度、特殊字符个数、大小写个数、数字个数）

set global validate_password.policy = 1;  

或   set global validate_password.policy = 'MEDIUM';

#将mysql密码安全等级改为高级（即满足中级所有要求、且连续4个字符不能是字典中的字符）

set global validate_password.policy = 2;  

或   set global validate_password.policy = 'STRONG';

#刷新权限

flush privileges;

忘记密码如何登录

#第一步，在mysql配置文件中添加跳过密码认证指令

skip-grant-tables

#第二步，重启mysql服务

service mysql restart

#第三步，无密码进入mysql

mysql

#第四步，将忘记密码的用户的 authentication_string 字段置空，并刷新权限

use mysql;

update user set authentication_string = '' where user = 'userName';

flush privileges;

#第五步，将配置文件中跳过密码认证指令取消，重启mysql服务

#第六步，重新登录mysql，需要指明用户，但不用输密码

mysql -uroot -p

#第七步，修改登录用户的密码

use mysql;

alter user 'userName'@'host' identified by 'newPassword';

mysql用户管理

#方式一：创建新用户并赋予权限（适用于mysql版本5.7之前）

grant privileges on 数据库.表 to 'userName'@'host' identified by 'password' with grant option;

#方式二：创建新用户并赋予权限（适用于mysql版本8.0之后）

create user 'userName'@'host' identified by 'password';

grant privileges on 数据库.表 to 'userName'@'host';

#修改用户密码

alter user 'userName'@'host' identified by 'password';

• mysql 5.7及以前

use mysql;

update user set authentication_string = password("newPassword") where user = 'username';

• mysql 8.0

use mysql;

update user set authentication_string = sha1("newPassword") where user = 'username';

#删除用户

use mysql;

delete from user where user = 'username' and host = 'host';

#修改用户的远程登录权限

use mysql;

update user set host = 'host' where user = 'userName';

TCL(事务控制语言)

rollback  |   commit  |  set transaction

DCL(数据库控制语言)

grant  |  revoke (取消授权)  |  create user  |  flush privileges

DML(数据操纵语言)

select  |  update  |  insert  |  delete  |  

DQL(数据查询语言)

#合并多个查询语句的结果集

select语句1 union select语句2;

注：每条select语句查询结果中字段的数量必须相同、字段的顺序必须相同、字段的数据类型一致；union合并后的结果集自动去重；union all合并后的结果集允许重复；

DDL(数据库定义语言)

create  |  drop  |  alter  |  comment  |  rename  |  show tables  |  desc  |  use  |  truncate (先删表，再重建表)

#创建数据库

create database databaseName;

#删除数据库

drop database databaseName;

#查看存在多少个数据库

show databases;

#查看数据库的编码

show create database databaseName;

#修改数据库字符集

alter database databaseName default character set 字符集

#进入数据库

use databaseName;

#创建表

create table tableNmae (字段1, 字段2, ...);

#创建表并设置表的存储引擎、字符集、行格式、备注

create table tableNmae (字段1, 字段2, ...) engine=Innodb charset=utf8mb3 row_format=DYNAMIC comment='表的描述';

#为字段单独设置字符集和排序规则

字段名 数据类型 character set 字符集 collate 字符集排序规则

#显示简略表结构(字段)

desc tableName;

#显示详细表结构(字段)

show full fields from tableName;

#查看数据表的编码

show create table tableName;

#显示表中已建立的索引

show index from tableName;

#删除表

drop table tableName;

#清空表中所有记录,并把 auto_increment 计数置空

truncate table tableName;

#如果表存在则删除

drop table if exists tableNmae;

#查看存在多少张表

show tables;

#修改表名称

rename table oldTableName to newTableName;

#修改表的字符集(包括已存在字段的字符集)

alter table tableName convert to character set 字符集

#修改表的字符集(已存在字段的字符集不受影响)

alter table tableName charset = 字符集;   或

alter table tableName default character set 字符集;

#修改表的字符集排序规则

alter table tableName collate 字符集排序规则

#在表中新增字段

alter table tableName add 字段 数据类型 约束;

#删除表中字段

alter table tableName drop column columnName;

#修改表中字段

alter table tableName change columnName newColumnName 数据类型 约束;

完整式:  alter table tableName change columnName newColumnName 数据类型 character set 字符集 collate 字符集排序规则 约束;

#为表中的字段添加索引

create index indexName on tableName(字段1,字段2,...);    或

alter table tableName add index indexName(字段1,字段2,...);

#删除表中索引

drop index indexName on tableName;   或

alter table tableName drop index indexName;

查看MySQL服务器性能

#语句格式

show [global | session] status like '参数';

• global : 全局的
• session : 当前会话的

参数

• connection : 连接服务器次数
• uptime : MySQL服务器的运行时间
• slow_queries : 慢查询次数
• innodb_rows_read : select语句的返回行数
• innodb_rows_inserted : insert语句插入的行数
• innodb_rows_updated : update语句更新的行数
• innodb_rows_deleted : delete语句删除的行数
• com_select : 查询操作执行的次数
• com_insert : 插入操作执行的次数，批量插入只算一次
• com_update : 更新操作执行的次数
• com_delete : 删除操作执行的次数

慢查询日志

#查看慢查询功能是否开启

show variables like '%slow%';

#开启慢查询日志并设置时间阀值及日志文件路径

set slow_query_log = 'on';

set long_query_time = 秒数;

set slow_query_log_file = 路径;

#筛选慢查询日志记录

mysqldumpslow -s 参数 -t 数字 日志文件

-s 代表按照某种规则排序（默认降序），参数如下：

• l  代表按照 Lock_time排序
• t  代表按照 Query_time排序
• r  代表按照 Rows_sent排序
• c  代表按照相同类型的sql语句的执行次数排序

-t  代表返回前几条记录

-r  升序

-a  查询条件显示字段完整内容，而不是用‘S’表示字符串，用‘N’表示数值

查看SQL语句执行步骤及资源占用

#查看是否开启profiling

show variables like '%profiling%';

#开启profiling

set profiling = 'on';

#查看sql语句的query_id 和总耗时

show profiles;

#查看sql语句的执行步骤及资源占用

show profile 参数 for query query_id

参数：

• ALL：所有性能开销
• BLOCK IO：I/O开销
• CPU：cpu开销
• MEMORY：内存开销
• IPC：发送和接收的开销
• CONTEXT SWITCHES：上下文切换的开销
• SOURCE：显示和 Source_function、Source_file、Source_line 相关的开销信息

查看SQL语句的执行计划

#格式

explain sql语句;

#执行计划包含的信息

id

select_type

table

type

possible_keys

key

key_len

ref

row

extr

#id的含义

id表明数据表的读取顺序，id值越大——优先加载，id值相等——按序加载

#select_type的含义

simple	单表查询
primary	嵌套查询情况下，最外层的查询
subquery	子查询
derived	衍生表（临时表）
union	合并两个select的结果集
union result	从两个select语句合并后的结果集中查询

#type的含义(访问类型)

system	系统表（表中只有一条记录）
const	常量查询
eq_ref	唯一性索引扫描，结果只有一行记录
ref	非唯一性索引扫描，结果可以匹配多行记录
range	范围查找，只检索给定范围的行记录
index	扫描全表的索引文件
all	扫描全表所有数据

#possible_keys的含义

可能用到的索引类型

#key的含义

实际用到的索引类型

#key_len的含义

实际用到的索引值大小

#ref的含义

实际用到的索引值类形

#row的含义

估计被查询优化器检索的行数

#extra的含义

Using where	使用where条件
Using index	使用索引读取数据
Using filesort	使用文件自排序
Using index condition	使用索引条件
Backward index scan	使用倒序索引
Using temporary	使用临时表