mysql5.7命令小结

前记：mysql的自身的命令其实也不多。只不过用的比较少就特别生疏，也会觉得多而杂还老记不住（毕竟用的少）。现在梳理一下，将工作学习中遇到的和不熟悉的mysql 命令语句记录下来，以便快速查询。

下面的举例以student和user表为主

CREATE TABLE `student` (
  `sno` varchar(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `sex` varchar(20) NOT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

一.show相关命令语句

1.查看表的索引

 show index  from tbl_name;

• table:表名
• non_unique：索引是非唯一的？。0否，是唯一索引。1是，是非唯一索引。（ps：这让我想起了令我自挂东南枝的英语）
• key_name:索引名称
• seq_in_index: 索引中的起始序列号
• column_name:创建索引的名称
• collation: 排序规则。在索引中的排序方式，A升序，null不排序
• inde_type: 索引的存储结构。
• cardinality: 基数。索引中的数量（表中的数据的数量）

show index from student;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
student	0	PRIMARY	1	sno	A	6	NULL	NULL		BTREE

select count(*) from student;

count(*)
6

2.显示系统变量

show session variables  [like 'xxx'] //查询会话变量,    

show global variables  [like 'xx'] //查询全局变量 

查看会话事务隔离级别

show session variables like '%isolation%'

Variable_name	Value
transaction_isolation	REPEATABLE-READ
tx_isolation	REPEATABLE-READ

查看mysql服务器 一次可以接受的数据量大小

show VARIABLES like '%packet%';

变量名	大小（单位Byte）
max_allowed_packet	4194304 （4m）

不同版本的Mysql 默认值可能不一样

3.查询数据库|表|表字段

查看MySQL的数据库

show databases

显示数据库下的有哪些表。不写 db_name 默认是当前数据库。如果没有指定 from db_name，也没有切花数据库，则会报错。

show tables [from db_name], 

显示表的字段及一些属性(类型，默认值等)

show columns from tab_name

select columns from student;

Field	Type	Null	Key	Default	Extra
sno	varchar(20)	NO	PRI	NULL
name	varchar(20)	NO		NULL
sex	varchar(20)	NO		NULL

4 查看数据库引擎相关

show engines  //查看msql支持的所有数据库存储引擎

• engine：存储引擎的名字
• support: mysql是否支持该引擎。default 默认使用，yes 支持， no不支持。
• transactions：该引擎是否支持事务，yes 支持 no不支持
• savepoints: 是否支持回滚点，yes 支持 no不支持
• xa: 是否支持分布式事务

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL

5查看状态信息

show table status \G;//查看某个数据库下所有表的状态信息,所以第一步需要use dbName

• name:表名
• engine：该表使用的存储引擎。默认innodb
• rows:行数。对于使用支持事务的引擎，该值不准确。
• update_time:修改时间
• create_time:创建时间
• data-length:表的数据长度（bytes） 。
• avg_row_length:平均每行数据长度（bytes）同上不准确
• Auto_increment:：下一个序列的值
• Collation:字符集

show status  [like ' '] //查看mysql服务的状态信息。

mysql优化经常用到该命令。信息比较多，请参考

6显示触发器

show triggers [{from|in} db_name] [like 'pattern' |where expr]//显示某个数据库或者当前数据库下的所有的触发器,可以使用like 或where进行过滤

7 显示数据库或对象的创建

show create {table tal_name | function fun_name | procedure proc_name | trigger name | user user_name@host | view view_name | db_name}

 show create table user\G;
 
    CREATE TABLE `student` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(255) DEFAULT NULL,
   `age` int(11) DEFAULT NULL,
   `sex` varchar(2) DEFAULT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
   1 row in set (0.00 sec)

二.用户管理

1.修改密码

• set password for '用户名'@'主机名' =password('新密码');
• alter user 'userName'@'host' identified by 'newPassword';

mysql> set password for 'root'@'localhost'=password('111111');
Query OK, 0 rows affected, 1 warning (0.22 sec)

2.添加用户

CREATE USER [IF NOT EXISTS] user identified by 'password'

3.删除用户

drop user 'user_name'@'localhost' //drop user 'test'@'localhost'

4.查询用户

select * from mysql.user\G; //查询所有用户

三.实用

1.查询表结构

{describe| desc} tbl_name [col_name]

2.语句分析

explain  statemnt_expr	

explain用于分析sql 语句，是否运用了主键，是否全表查询等信息。对于慢sql的分析，经常使用该命令.

mysql> explain select * from student;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	student	NULL	ALL	NULL	NULL	NULL	NULL	6	100.00	NULL

使用了主键sno，进行查询

mysql > explain select * from student where sno='101';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	student	NULL	const	PRIMARY	PRIMARY	62	const	1	100.00	NULL

• id： 值越大，select执行的优先级越高。
• select_type: select类型。
• table: 查询的表名或者表别名。
• type: 查找方式。从最好到最差的连接类型为const、eq_reg、ref、range、index 和ALL
• possible_keys： 查询可能使用的索引。
• key: 实际使用的索引
• key_len： 在不损失精确性的情况下，长度越短越好
• ref：
• rows
• extra

比较重要的就是type:

• const ：对唯一索引使用精确查询。
• eq_reg： 对于多表联合查询。连接条件是另一个表的唯一索引
• ref ： 对于多表联合查询。连接条件是另一个表的索引
• range ：对索引字段使用范围查询
• index（ Full Index Scan）。全索引树扫描
• ALL（Full Table Scan）全表扫描

mysql数据语句优化经常用到该命令。一般优化的原则便是尽可能用到索引经可能避免表的全部扫描。详细内容可以参考

四数据库备份与恢复

msqldump备份

msqldump可以用数据的备份，其有两种方式的输出，取决于是否使用 --tab。

• 不使用 --tab,会保存数据和表的创建语句保存到一个文件中
• 使用–tab，会为每个备份的表生成两份文件，将表中的数据保存到文件tal_name.text中，将表的创建语句保存到文件tal_name.sql中。

ps:查看表的创建可以使用show create tab_name

语法： mysqldump [arguments]
--port=port_num, -P port_num        服务的端口号，默认3306
--host=host_name, -h host_name    连接的主机，默认127.0.0.1
--user=user_name, -u user_name     指定mysql连接的用户名
--password[=password], -p[password]    mysql连接的密码，如果密码直接写在命令行中，中间不要使用空格
-B   ,--databases   需要备份的数据库, 如果要备份多个数据库使用空格隔开。如果是单数据库的话可以省略
--add-drop-database   在执行create database 之前执行drop database
--add-drop-table    执行create table之前先执行drop table，默认使用
--skip-add-drop-table   每次create table之前不执行drop table

关于 mysqldump更多参数，参考mysql官网mysqldump — A Database Backup Program

 show databases; 

返回结果

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| test1          |
| user           |
| user_role      |
+----------------+

1）不使用–tab

备份指定的数据库

mysqldump -u用户名 -p[密码] [--databases db1,db2]  > 输出文件路径

备份所有数据库

mysqldump -uroot -p --all-databases  > E:/dump.sql

备份 test 数据库

mysqldump  -uroot -p  --databases test > E:/dump.sql

备份 数据库test下的 user 表

mysqldump  -uroot -p  --databases test --tables user> E:/dump1.sql

备份 数据库test下的 test、test1 表

mysqldump  -uroot -p  --databases test --tables test test1> E:/dump2.sql

dump1.sql 文件内容大致如下

-- MySQL dump 10.13  Distrib 5.7.21, for Win64 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.7.21

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'lili','123456','123456@163.com'),(2,'test','123456','123456@163.com'),(3,'wawa','123456','');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-11-22 11:42:42

2）使用–tab

C:\windows\system32>mysqldump -uroot -p  --databases test --tables user --tab=G:/dump
Enter password: ******

执行完上面的命令之后会发现 dump文件夹下多出了两个文件。.sql文件存储表的创建语句，.txt文件存储数据

数据库中表 user的数据：

txt文件中存储的数据

1	lili	123456	123456@163.com
2	test	123456	123456@163.com
3	wawa	123456	

默认情况下 数据表中一行数据 对应 TXT文件中的一行，每一列以 tab制表符隔开，每行以 换行符分隔。
可以通过以下参数修改这一，默认配置：

–fields-terminated-by=str

分隔列值的字符串，默认使用（tab）

–fields-enclosed-by=char

将列值括在其中的字符 (default: no character).

–fields-optionally-enclosed-by=char

将非数字列值括在其中的字符 (default: no character).

–fields-escaped-by=char

转义特殊字符的字符 (default: no escaping).

–lines-terminated-by=str

行终止字符串(default: newline).

比如，使用,分隔元素，使用 " 包裹元素。

mysqldump -uroot -p  --databases test --tables user --tab=G:/dump --fields-terminated-by=, --fields-enclosed-by=0x22

TXT文件中的输出结果：

"1","lili","123456","123456@163.com"
"2","test","123456","123456@163.com"
"3","wawa","123456",""

错误

在使用带选项 --tab 的mysqldump命令时，可能会出现以下错误（mysql压缩包版本）：

mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so
 it cannot execute this statement when executing 'SELECT INTO OUTFILE'

This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT … INTO OUTFILE statements and the LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.

secure_file_priv是mysql服务的系统变量，该变量用于限制 数据的导入、导出操作的效果。 这些操作只用当用户具有 文件权限时才被允许。

• 当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
• 当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
• 当secure_file_priv的值为空字符串时，表示不对mysqld 的导入|导出做限制

使用 mysqld --verbose --help可以查看mysql服务器启动时的配置信息项，会看到secure-file-priv值为 NULL。

所以只需要修改mysql配置文件my.ini 追加一行 secure-file-priv =再重新启动mysql服务就可以了。当然前提是你的mysql版本>=5.7.6

详情参考博客 解决mysqldump: Got error: 1290以及secure-file-priv option简解
、mysql官网 服务的系统变量

恢复

mysql -u用户名 -p < 文件路径

mysql -uroot -p < E:\text.text

msql> source 文件路径 //该方式需要先登陆，在导数据