MySQL utility的用法_mysql 帮助文档使用

mysql有很完善的帮助文档，在mysql命令行下可以通过？ 或者help 加上命令可以查看改命令的帮助信息，

我们查下几个常用的help命令

第一层帮助信息：

？ 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

Data Definition

Data Manipulation

Data Types

Functions

Functions and Modifiers for Use with GROUP BY

Geographic Features

Help Metadata

Language Structure

Plugins

Table Maintenance

Transactions

User-Defined Functions

Utility

再查看具体每一层的帮助信息

mysql> ? Account Management;

You asked for help about help category: "Account Management"

For more information, type 'help ', where is one of the following

topics:

CREATE USER

DROP USER

GRANT

RENAME USER

REVOKE

SET PASSWORD

mysql> ? Administration

You asked for help about help category: "Administration"

For more information, type 'help ', where is one of the following

topics:

BINLOG

CACHE INDEX

CHANGE MASTER TO

DEALLOCATE PREPARE

EXECUTE STATEMENT

FLUSH

FLUSH QUERY CACHE

HELP COMMAND

KILL

LOAD INDEX

PREPARE

PURGE BINARY LOGS

RESET

RESET MASTER

RESET SLAVE

SET

SET GLOBAL SQL_SLAVE_SKIP_COUNTER

SET SQL_LOG_BIN

SHOW

SHOW AUTHORS

SHOW BINARY LOGS

SHOW BINLOG EVENTS

SHOW CHARACTER SET

SHOW COLLATION

SHOW COLUMNS

SHOW CONTRIBUTORS

SHOW CREATE DATABASE

SHOW CREATE EVENT

SHOW CREATE FUNCTION

SHOW CREATE PROCEDURE

SHOW CREATE TABLE

SHOW CREATE TRIGGER

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

START SLAVE

STOP SLAVE

mysql> ? Compound Statements;

You asked for help about help category: "Compound Statements"

For more information, type 'help ', where is one of the following

topics:

BEGIN END

CASE STATEMENT

CLOSE

DECLARE CONDITION

DECLARE CURSOR

DECLARE HANDLER

DECLARE VARIABLE

FETCH

IF STATEMENT

ITERATE

LEAVE

LOOP

OPEN

REPEAT LOOP

RESIGNAL

RETURN

SELECT INTO

SET VARIABLE

SIGNAL

WHILE

mysql> ? Data Definition;

You asked for help about help category: "Data Definition"

For more information, type 'help ', where is one of the following

topics:

ALTER DATABASE

ALTER EVENT

ALTER FUNCTION

ALTER PROCEDURE

ALTER SERVER

ALTER TABLE

ALTER VIEW

CONSTRAINT

CREATE DATABASE

CREATE EVENT

CREATE FUNCTION

CREATE INDEX

CREATE PROCEDURE

CREATE SERVER

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

DROP DATABASE

DROP EVENT

DROP FUNCTION

DROP INDEX

DROP PROCEDURE

DROP SERVER

DROP TABLE

DROP TRIGGER

DROP VIEW

MERGE

RENAME TABLE

TRUNCATE TABLE

这里可以根据不同的分类显示每个分类下支持的mysql指令

mysql> ? START SLAVE;

Name: 'START SLAVE'

Description:

Syntax:

START SLAVE [thread_type [, thread_type] ... ]

START SLAVE [SQL_THREAD] UNTIL

MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

START SLAVE [SQL_THREAD] UNTIL

RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos

thread_type: IO_THREAD | SQL_THREAD

START SLAVE with no thread_type options starts both of the slave

threads. The I/O thread reads events from the master server and stores

them in the relay log. The SQL thread reads events from the relay log

and executes them. START SLAVE requires the SUPER privilege.

If START SLAVE succeeds in starting the slave threads, it returns

without any error. However, even in that case, it might be that the

slave threads start and then later stop (for example, because they do

not manage to connect to the master or read its binary log, or some

other problem). START SLAVE does not warn you about this. You must

check the slave's error log for error messages generated by the slave

threads, or check that they are running satisfactorily with SHOW SLAVE

STATUS.

URL: http://dev.mysql.com/doc/refman/5.5/en/start-slave.html

mysql> ? MERGE

Name: 'MERGE'

Description:

The MERGE storage engine, also known as the MRG_MyISAM engine, is a

collection of identical MyISAM tables that can be used as one.

"Identical" means that all tables have identical column and index

information. You cannot merge MyISAM tables in which the columns are

listed in a different order, do not have exactly the same columns, or

have the indexes in different order. However, any or all of the MyISAM

tables can be compressed with myisampack. See

http://dev.mysql.com/doc/refman/5.5/en/myisampack.html. Differences in

table options such as AVG_ROW_LENGTH, MAX_ROWS, or PACK_KEYS do not

matter.

URL: http://dev.mysql.com/doc/refman/5.5/en/merge-storage-engine.html

Examples:

mysql> CREATE TABLE t1 (

-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-> message CHAR(20)) ENGINE=MyISAM

mysql> CREATE TABLE t2 (

-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-> message CHAR(20)) ENGINE=MyISAM

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1')

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2')

mysql> CREATE TABLE total (

-> a INT NOT NULL AUTO_INCREMENT,

-> message CHAR(20), INDEX(a))

-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST

mysql> ? create table;

Name: 'CREATE TABLE'

Description:

Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_options]

[partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

[table_options]

[partition_options]

select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

{ LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:

col_name column_definition

| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

[index_option] ...

| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)

[index_option] ...

| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]

[index_name] [index_type] (index_col_name,...)

[index_option] ...

| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)

[index_option] ...

| [CONSTRAINT [symbol]] FOREIGN KEY

[index_name] (index_col_name,...) reference_definition

| CHECK (expr)

column_definition:

data_type [NOT NULL | NULL] [DEFAULT default_value]

[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

[COMMENT 'string']

[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]

[reference_definition]

data_type:

BIT[(length)]

| TINYINT[(length)] [UNSIGNED] [ZEROFILL]

| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]

| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

| INT[(length)] [UNSIGNED] [ZEROFILL]

| INTEGER[(length)] [UNSIGNED] [ZEROFILL]

| BIGINT[(length)] [UNSIGNED] [ZEROFILL]

| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]

| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]

| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]

| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]

| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]

| DATE

| TIME

| TIMESTAMP

| DATETIME

| YEAR

| CHAR[(length)]

[CHARACTER SET charset_name] [COLLATE collation_name]

| VARCHAR(length)

[CHARACTER SET charset_name] [COLLATE collation_name]

| BINARY[(length)]

| VARBINARY(length)

| TINYBLOB

| BLOB

| MEDIUMBLOB

| LONGBLOB

| TINYTEXT [BINARY]

[CHARACTER SET charset_name] [COLLATE collation_name]

| TEXT [BINARY]

[CHARACTER SET charset_name] [COLLATE collation_name]

| MEDIUMTEXT [BINARY]

[CHARACTER SET charset_name] [COLLATE collation_name]

| LONGTEXT [BINARY]

[CHARACTER SET charset_name] [COLLATE collation_name]

| ENUM(value1,value2,value3,...)

[CHARACTER SET charset_name] [COLLATE collation_name]

| SET(value1,value2,value3,...)

[CHARACTER SET charset_name] [COLLATE collation_name]

| spatial_type

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH}

index_option:

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT 'string'

reference_definition:

REFERENCES tbl_name (index_col_name,...)

[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]

[ON DELETE reference_option]

[ON UPDATE reference_option]

reference_option:

RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:

table_option [[,] table_option] ...

table_option:

ENGINE [=] engine_name

| AUTO_INCREMENT [=] value

| AVG_ROW_LENGTH [=] value

| [DEFAULT] CHARACTER SET [=] charset_name

| CHECKSUM [=] {0 | 1}

| [DEFAULT] COLLATE [=] collation_name

| COMMENT [=] 'string'

| CONNECTION [=] 'connect_string'

| DATA DIRECTORY [=] 'absolute path to directory'

| DELAY_KEY_WRITE [=] {0 | 1}

| INDEX DIRECTORY [=] 'absolute path to directory'

| INSERT_METHOD [=] { NO | FIRST | LAST }

| KEY_BLOCK_SIZE [=] value

| MAX_ROWS [=] value

| MIN_ROWS [=] value

| PACK_KEYS [=] {0 | 1 | DEFAULT}

| PASSWORD [=] 'string'

| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

| UNION [=] (tbl_name[,tbl_name]...)

partition_options:

PARTITION BY

{ [LINEAR] HASH(expr)

| [LINEAR] KEY(column_list)

| RANGE{(expr) | COLUMNS(column_list)}

| LIST{(expr) | COLUMNS(column_list)} }

[PARTITIONS num]

[SUBPARTITION BY

{ [LINEAR] HASH(expr)

| [LINEAR] KEY(column_list) }

[SUBPARTITIONS num]

]

[(partition_definition [, partition_definition] ...)]

partition_definition:

PARTITION partition_name

[VALUES

{LESS THAN {(expr | value_list) | MAXVALUE}

|

IN (value_list)}]

[[STORAGE] ENGINE [=] engine_name]

[COMMENT [=] 'comment_text' ]

[DATA DIRECTORY [=] 'data_dir']

[INDEX DIRECTORY [=] 'index_dir']

[MAX_ROWS [=] max_number_of_rows]

[MIN_ROWS [=] min_number_of_rows]

[(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:

SUBPARTITION logical_name

[[STORAGE] ENGINE [=] engine_name]

[COMMENT [=] 'comment_text' ]

[DATA DIRECTORY [=] 'data_dir']

[INDEX DIRECTORY [=] 'index_dir']

[MAX_ROWS [=] max_number_of_rows]

[MIN_ROWS [=] min_number_of_rows]

select_statement:

[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)

CREATE TABLE creates a table with the given name. You must have the

CREATE privilege for the table.

Rules for permissible table names are given in

http://dev.mysql.com/doc/refman/5.5/en/identifiers.html. By default,

the table is created in the default database, using the InnoDB storage

engine. An error occurs if the table exists, if there is no default

database, or if the database does not exist.

URL: http://dev.mysql.com/doc/refman/5.5/en/create-table.html

mysql>

至于上面一层的命令，我们还可以查看mysql支持的数据类型，

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> ? Utility;

You asked for help about help category: "Utility"

For more information, type 'help ', where is one of the following

topics:

DESCRIBE

EXPLAIN

HELP STATEMENT

USE

通过帮助文档还是很方便查到mysql的所有名称，已经每个命令的使用方法和语法。

可以查看mysql内置函数列表，以及每个函数的功能和使用方法：

mysql> ? Functions;

You asked for help about help category: "Functions"

For more information, type 'help ', where is one of the following

topics:

PROCEDURE ANALYSE

categories:

Bit Functions

Comparison operators

Control flow functions

Date and Time Functions

Encryption Functions

Information Functions

Logical operators

Miscellaneous Functions

Numeric Functions

String Functions

mysql> ? String Functions;

You asked for help about help category: "String Functions"

For more information, type 'help ', where is one of the following

topics:

ASCII

BIN

BINARY OPERATOR

BIT_LENGTH

CAST

CHAR FUNCTION

CHARACTER_LENGTH

CHAR_LENGTH

CONCAT

CONCAT_WS

CONVERT

ELT

EXPORT_SET

EXTRACTVALUE

FIELD

FIND_IN_SET

FORMAT

HEX

INSERT FUNCTION

INSTR

LCASE

LEFT

LENGTH

LIKE

LOAD_FILE

LOCATE

LOWER

LPAD

LTRIM

MAKE_SET

MATCH AGAINST

MID

NOT LIKE

NOT REGEXP

OCTET_LENGTH

ORD

POSITION

QUOTE

REGEXP

REPEAT FUNCTION

REPLACE FUNCTION

REVERSE

RIGHT

RPAD

RTRIM

SOUNDEX

SOUNDS LIKE

SPACE

STRCMP

SUBSTR

SUBSTRING

SUBSTRING_INDEX

TRIM

UCASE

UNHEX

UPDATEXML

UPPER

mysql> ? QUOTE;

Name: 'QUOTE'

Description:

Syntax:

QUOTE(str)

Quotes a string to produce a result that can be used as a properly

escaped data value in an SQL statement. The string is returned enclosed

by single quotation marks and with each instance of backslash ("\"),

single quote ("'"), ASCII NUL, and Control+Z preceded by a backslash.

If the argument is NULL, the return value is the word "NULL" without

enclosing single quotation marks.

URL: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html

Examples:

mysql> SELECT QUOTE('Don\'t!')

-> 'Don\'t!'

mysql> SELECT QUOTE(NULL)

-> NULL

mysql>

mysql的插件信息和事务之类的其他信息

mysql> ? Plugins;

Name: 'SHOW PLUGINS'

Description:

Syntax:

SHOW PLUGINS

SHOW PLUGINS displays information about server plugins. Plugin

information is also available in the INFORMATION_SCHEMA.PLUGINS table.

See http://dev.mysql.com/doc/refman/5.5/en/plugins-table.html.

Example of SHOW PLUGINS output:

mysql> SHOW PLUGINS\G

*************************** 1. row ***************************

Name: binlog

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 2. row ***************************

Name: CSV

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 3. row ***************************

Name: MEMORY

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

*************************** 4. row ***************************

Name: MyISAM

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: GPL

...

URL: http://dev.mysql.com/doc/refman/5.5/en/show-plugins.html

mysql> ? Transations;

Nothing found

Please try to run 'help contents' for a list of all accessible topics

mysql> ? Transactions;

You asked for help about help category: "Transactions"

For more information, type 'help ', where is one of the following

topics:

ISOLATION

LOCK

SAVEPOINT

START TRANSACTION

mysql>

通过帮助文档可以查询到mysql的所有命令及每个命令的功能，语法。