MySQL服务器常用命令（带你如何去查询MySQL自带的官方资料）_mysql require none

最近我根据上述的技术体系图搜集了几十套腾讯、头条、阿里、美团等公司21年的面试题，把技术点整理成了视频（实际上比预期多花了不少精力），包含知识脉络 + 诸多细节，由于篇幅有限，这里以图片的形式给大家展示一部分

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

register\_time datetime(0) NULL DEFAULT NULL COMMENT ‘注册时间’,
login\_time datetime(0) NULL DEFAULT NULL COMMENT ‘上一次登录时间’,
pic varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘头像地址’,
look int(10) NULL DEFAULT NULL COMMENT ‘查看数’,
PRIMARY KEY (id)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


* 存储过程：

create procedure test_insert(count int)
begin
declare i int default 1;
while i<=count do
INSERT INTO userinfo values(
i, – id
uuid(), – username
CEILING(RAND()*90+10), – age
FLOOR(RAND()*100000000000), – phone
round(FORMAT(rand(),1)), – gender
uuid(), – desc
now(), – register_time
now(), – login_time
uuid(), – pic
CEILING(RAND()*90+10) – look
);
set i=i+1;
end while;
end;


## MySQL 命令


### 1.1 MySQL帮助文档


我们在操作MySQL时，经常会忘记一些语法，比如`show`、`alter`、`grant`等命令，或者想查询一些函数的使用、说明文档等。MySQL提供了一套非常完善的帮助文档工具，方便我们快捷的查询。


#### 1.1.1 查看根目录


如果不知道帮助文档能够提供些什么, 可以用`? contents`命令来显示所有可供查询的分类

mysql> ? contents;
You asked for help about help category: “Contents”
For more information, type ‘help ’, where is one of the following
categories:
Account Management
Administration
Compound Statements
Contents
Data Definition
Data Manipulation
Data Types
Functions
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Storage Engines
Table Maintenance
Transactions
User-Defined Functions
Utility

mysql>


#### 1.1.2 选择分类查询


我们可以在跟目录里面选择某个组进行详细查询


##### 1.1.2.1 查询数据类型

mysql> ? Data Types;
You asked for help about help category: “Data Types”
For more information, type ‘help ’, where is one of the following
topics:
AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INTEGER
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR DATA TYPE

mysql>


接着还可以查询具体的某个类型的详细介绍：

mysql> ? INT
Name: ‘INT’
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-syntax.html

mysql>


##### 1.1.2.2 查询用户管理

mysql> ? Account Management;
You asked for help about help category: “Account Management”
For more information, type ‘help ’, where is one of the following
topics:
ALTER USER
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD

mysql>


还可以查询更详细的用法：

mysql> ? ALTER USER;
Name: ‘ALTER USER’
Description:
Syntax:
ALTER USER [IF EXISTS]
user [auth_option] [, user [auth_option]] …
[REQUIRE {NONE | tls_option [[AND] tls_option] …}]
[WITH resource_option [resource_option] …]
[password_option | lock_option] …

ALTER USER [IF EXISTS]
USER() IDENTIFIED BY ‘auth_string’
…


比如我们选择几个常用而且容易忘记命令查询：


* **alter**：

mysql> ? alter
Many help items for your request exist.
To make a more specific request, please type ‘help ’,
where is one of the following
topics:
ALTER DATABASE
ALTER EVENT
ALTER FUNCTION
ALTER INSTANCE
ALTER LOGFILE GROUP
ALTER PROCEDURE
ALTER SCHEMA
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER USER
ALTER VIEW
GRANT
SPATIAL INDEXES

mysql>


查询更详细的说明：

mysql> ? ALTER DATABASE;
Name: ‘ALTER DATABASE’
Description:
Syntax:
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification …
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME

alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
…


* **show**：

mysql> ? show
Name: ‘SHOW’
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
…


查询更详细的用法说明：

mysql> ? SHOW CREATE TABLE
Name: ‘SHOW CREATE TABLE’
Description:
Syntax:
SHOW CREATE TABLE tbl_name

…

Examples:
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id int(11) NOT NULL AUTO_INCREMENT,
s char(60) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql>



> 
> 总之，MySQL提供的帮助文档，哪里不会问哪里（?）。
> 
> 
> 


### 1.2 MySQL 常用工具


#### 1.2.1 mysql命令


* 查看mysql命令详细用法：

mysql -?
或者
mysql --help


![在这里插入图片描述](https://img-blog.csdnimg.cn/20200602104605513.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)


* 语法：

mysql [OPTIONS] [database]


* options：mysql命令的携带参数，常用参数如下：
	+ -h（`--host=name`）：mysql服务器的IP
	+ -P（`--port`）：mysql服务器的端口
	+ -u（`--user=name`）：mysql用户名
	+ -p（`--password=name`）：mysql密码
	+ -D（`--database`）：指定连接的数据库
* database：连接的数据库


完整语法示例：

简写：
mysql -h127.0.0.1 -P 3306 -uroot -padmin -D test

全写：
mysql --host=127.0.0.1 --port 3306 --user=root --password=admin --database test


#### 1.2.2 mysqladmin命令


mysqladmin命令用于临时执行某些操作，比如创建一个数据库，删除一个数据库，查看数据库状态等。


* 查看帮助文档：

mysqladmin -?


![在这里插入图片描述](https://img-blog.csdnimg.cn/20200602104737289.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg=,size_16,color_FFFFFF,t_70)


* 语法：

mysqladmin [OPTIONS] command command…


示例：


* 创建、删除数据库

[root@localhost ~]# mysqladmin -uroot -padmin create test01;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysqladmin -uroot -padmin drop test01;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the ‘test01’ database [y/N] y
Database “test01” dropped
[root@localhost ~]#


* 查看数据库版本：

[root@localhost ~]# mysqladmin -uroot -padmin version;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin Ver 8.42 Distrib 5.7.30, for Linux on x86_64
Copyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 5.7.30
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 5 hours 59 min 31 sec

Threads: 2 Questions: 734 Slow queries: 0 Opens: 260 Flush tables: 1 Open tables: 234 Queries per second avg: 0.034
[root@localhost ~]#


#### 1.2.3 mysqldump


mysqldump命令主要用于数据库的备份。


* 语法：

mysqldump [OPTIONS] database [tables]


参数：  
 options：


* -h：mysql服务器的IP
* -P：mysql服务器的端口
* -u：mysql用户名
* -p：mysql密码
* -n（`--no-create-db`）：不包含创建数据库语句
* -t（`--no-create-info`）：不包含创建表数据
* -d（`--no-data`）：不包含数据
* -B（`--database`）：指定导出的数据库
* -A（`--all-databases`）：导出所有数据库


##### 1.2.3.1 导出表：


* 先查看当前数据库有哪些表：  
 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200602105014432.png)

mysqldump -hip地址 -P端口 -u用户名 -p密码数据库表1 表2… > 文件路径

mysqldump -h127.0.0.1 -P3306 -uroot -padmin test test2 > /root/test.sql


* 如果没有写需要导出的表则默认导出库中所有的表：

mysqldump -uroot -padmin test > /root/test.sql



> 
> -h 默认值为127.0.0.1 -P 默认值为3306
> 
> 
> 


##### 1.2.3.2 导出库：

mysqldump -u用户名 -p密码 --databases 库1 库2… > 文件路径

mysqldump -uroot -padmin --databases test > test.sql
mysqldump -uroot -padmin -B test > test.sql



> 
> 导出库和导出表的区别在于导出库的sql语句里面加了`create database db_name`语句。
> 
> 
> 


* 导出当前数据库服务器的所有数据库：



# 最后总结

搞定算法，面试字节再不怕，有需要文章中分享的这些**二叉树、链表、字符串、栈和队列等等各大面试高频知识点及解析**

最后再分享一份**终极手撕架构的大礼包(学习笔记)：分布式+微服务+开源框架+性能优化**

![image](https://img-blog.csdnimg.cn/img_convert/c6c2bbdd004d59bd71ad243a35aff5fb.webp?x-oss-process=image/format,png)



> **本文已被[CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】](https://bbs.youkuaiyun.com/forums/4f45ff00ff254613a03fab5e56a57acb)收录**

**[需要这份系统化的资料的朋友，可以点击这里获取](https://bbs.youkuaiyun.com/forums/4f45ff00ff254613a03fab5e56a57acb)**

-databases test > test.sql
mysqldump -uroot -padmin -B test > test.sql