某一天，有心留意到登陆mysql的提示，那么就从这里启航吧
[root@centos ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 178 Server version: 5.7.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
mysql> ?; For information about MySQL products and services, visit: http://www.mysql.com/ For developer information, including the MySQL Reference Manual, visit: http://dev.mysql.com/ To buy MySQL Enterprise support, training, or other products, visit: https://shop.mysql.com/ List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. resetconnection(\x) Clean session context. For server side help, type 'help contents' mysql>
帮助大纲
mysql>help Account Management mysql>help Administration
mysql> help contents You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> is one of the following categories: Account Management Administration Compound Statements Data Definition # DDL CREATE、ALTER、DROP等.DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用 Data Manipulation # DML 增删改查等，SELECT、UPDATE、INSERT、DELETE Data Types # 数据类型(创建表时) Functions # 函数 Functions and Modifiers for Use with GROUP BY # 可与GROUP BY共用的函数与修饰符 Geographic Features Help Metadata Language Structure Plugins Procedures Storage Engines Table Maintenance Transactions User-Defined Functions Utility mysql># 查看帮助主题分类的帮助示例 mysql>help Account Management mysql>help Administration
主要包括子主题有如下
mysql> help Administration You asked for help about help category: "Administration" For more information, type 'help <item>', where <item> is one of the following topics: BINLOG CACHE INDEX FLUSH FLUSH QUERY CACHE HELP COMMAND # 这个帮助命令是说：help show、help select...等可以显示sql帮助等。是直达命令的帮助，后面也有示例。 KILL LOAD INDEX RESET SET SET CHARACTER SET SET NAMES SHOW SHOW BINARY LOGS SHOW BINLOG EVENTS SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW CREATE DATABASE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW CREATE TABLE SHOW CREATE TRIGGER SHOW CREATE USER SHOW CREATE VIEW SHOW DATABASES SHOW ENGINE SHOW ENGINES SHOW ERRORS SHOW EVENTS SHOW FUNCTION CODE SHOW FUNCTION STATUS SHOW GRANTS SHOW INDEX SHOW MASTER STATUS SHOW OPEN TABLES SHOW PLUGINS SHOW PRIVILEGES SHOW PROCEDURE CODE SHOW PROCEDURE STATUS SHOW PROCESSLIST SHOW PROFILE SHOW PROFILES SHOW RELAYLOG EVENTS SHOW SLAVE HOSTS SHOW SLAVE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW TRIGGERS SHOW VARIABLES SHOW WARNINGS SHUTDOWN # 停止后，mysql端口监听停止，可能console未断开，但已经无法操作了。需要通过正常的启动服务等其它方式从外部启动服务监听端口。 mysql>
显示线程状态
mysql> SHOW PROCESSLIST; +----+-----------------+----------------------+--------------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+----------------------+--------------+---------+------+------------------------+------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 1269 | Waiting on empty queue | NULL | | 3 | root | 100.100.100.62:37736 | kaixin | Sleep | 14 | | NULL | | 4 | root | 100.100.100.62:37738 | kaixin | Sleep | 14 | | NULL | | 5 | root | 100.100.100.62:37740 | kaixin | Sleep | 15 | | NULL | | 8 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | +----+-----------------+----------------------+--------------+---------+------+------------------------+------------------+ 5 rows in set (0.00 sec) mysql> help SHOW PROCESSLIST; Name: 'SHOW PROCESSLIST' Description: Syntax: SHOW [FULL] PROCESSLIST SHOW PROCESSLIST shows which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field. URL: http://dev.mysql.com/doc/refman/5.7/en/show-processlist.html # 关于此命令的在线文档 mysql>
内置权限说明
mysql> SHOW PRIVILEGES; +-------------------------+---------------------------------------+-------------------------------------------------------+ | Privilege | Context | Comment | +-------------------------+---------------------------------------+-------------------------------------------------------+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | | Create | Databases,Tables,Indexes | To create new databases and tables | | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | | Create temporary tables | Databases | To use CREATE TEMPORARY TABLE | | Create view | Tables | To create new views | | Create user | Server Admin | To create new users | | Delete | Tables | To delete existing rows | | Drop | Databases,Tables | To drop databases, tables, and views | | Event | Server Admin | To create, alter, drop and execute events | | Execute | Functions,Procedures | To execute stored routines | | File | File access on server | To read and write files on the server | | Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess | | Index | Tables | To create or drop indexes | | Insert | Tables | To insert data into tables | | Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) | | Process | Server Admin | To view the plain text of currently executing queries | | Proxy | Server Admin | To make proxy user possible | | References | Databases,Tables | To have references on tables | | Reload | Server Admin | To reload or refresh tables, logs and privileges | | Replication client | Server Admin | To ask where the slave or master servers are | | Replication slave | Server Admin | To read binary log events from the master | | Select | Tables | To retrieve rows from table | | Show databases | Server Admin | To see all databases with SHOW DATABASES | | Show view | Tables | To see views with SHOW CREATE VIEW | | Shutdown | Server Admin | To shut down the server | | Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. | | Trigger | Tables | To use triggers | | Create tablespace | Server Admin | To create/alter/drop tablespaces | | Update | Tables | To update existing rows | | Usage | Server Admin | No privileges - allow connect only | +-------------------------+---------------------------------------+-------------------------------------------------------+ 31 rows in set (0.00 sec) mysql>
mysql> help SHOW PROCEDURE CODE
Name: 'SHOW PROCEDURE CODE'
Description:
Syntax:
SHOW PROCEDURE CODE proc_name
This statement is a MySQL extension that is available only for servers
that have been built with debugging support. It displays a
representation of the internal implementation of the named stored
procedure. A similar statement, SHOW FUNCTION CODE, displays
information about stored functions (see [HELP SHOW FUNCTION CODE]).
To use either statement, you must be the owner of the routine or have
SELECT access to the mysql.proc table.
If the named routine is available, each statement produces a result
set. Each row in the result set corresponds to one "instruction" in the
routine. The first column is Pos, which is an ordinal number beginning
with 0. The second column is Instruction, which contains an SQL
statement (usually changed from the original source), or a directive
which has meaning only to the stored-routine handler.
URL: http://dev.mysql.com/doc/refman/5.7/en/show-procedure-code.html
Examples:
mysql> DELIMITER //
mysql> CREATE PROCEDURE p1 ()
-> BEGIN
-> DECLARE fanta INT DEFAULT 55;
-> DROP TABLE t2;
-> LOOP
-> INSERT INTO t3 VALUES (fanta);
-> END LOOP;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW PROCEDURE CODE p1//
+-----+----------------------------------------+
| Pos | Instruction |
+-----+----------------------------------------+
| 0 | set fanta@0 55 |
| 1 | stmt 9 "DROP TABLE t2" |
| 2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" |
| 3 | jump 2 |
+-----+----------------------------------------+
4 rows in set (0.00 sec)
mysql>