| Haddam |
2014-11-26 06:43 PM |
mysql 帮助文档使用
mysql有很完善的帮助文档，在mysql命令行下可以通过？ 或者help 加上命令可以查看改命令的帮助信息，
我们查下几个常用的help命令
第一层帮助信息：
？ contents;
Code:
mysql> ? 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
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;
Code:
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> 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 <item>', where <item> 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 <item>', where <item> 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 <item>', where <item> 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;
Code:
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支持的数据类型，
Code:
mysql> ? Data Types;
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> 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 <item>', where <item> is one of the following
topics:
DESCRIBE
EXPLAIN
HELP STATEMENT
USE
通过帮助文档还是很方便查到mysql的所有名称，已经每个命令的使用方法和语法。
可以查看mysql内置函数列表，以及每个函数的功能和使用方法：
Code:
mysql> ? Functions;
You asked for help about help category: "Functions"
For more information, type 'help <item>', where <item> 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 <item>', where <item> 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的插件信息和事务之类的其他信息
Code:
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 <item>', where <item> is one of the following
topics:
ISOLATION
LOCK
SAVEPOINT
START TRANSACTION
mysql>
通过帮助文档可以查询到mysql的所有命令及每个命令的功能，语法。 |
