MySQL幫助使用

starive發表於2014-08-29
MySql
在使用MySQL過程中，可能會遇到：
1  某個操作語法忘記了，如何查詢？
2 如何快速知道當前版本上某個欄位型別的取值範圍？
3 當前版本都支援那些函式？ 希望有例子說明。
4 當前版本是否支援某個功能？


按照層次看幫助

用 ? content

  1. mysql> ? contents;
  2. You asked for help about help category: \"Contents\"
  3. For more information, type \'help \', where <item> is one of the following
  4. categories:
  5.    Account Management
  6.    Administration
  7.    Compound Statements
  8.    Data Definition
  9.    Data Manipulation
  10.    Data Types
  11.    Functions
  12.    Functions and Modifiers for Use with GROUP BY
  13.    Geographic Features
  14.    Help Metadata
  15.    Language Structure
  16.    Plugins
  17.    Procedures
  18.    Storage Engines
  19.    Table Maintenance
  20.    Transactions
  21.    User-Defined Functions
  22.    Utility

  24. mysql> ? data types
  25. You asked for help about help category: \"Data Types\"
  26. For more information, type \'help \', where <item> is one of the following
  27. topics:
  28.    AUTO_INCREMENT
  29.    BIGINT
  30.    BINARY
  31.    BIT
  32.    BLOB
  33.    BLOB DATA TYPE
  34.    BOOLEAN
  35.    CHAR
  36.    CHAR BYTE
  37.    DATE
  38.    DATETIME
  39.    DEC
  40.    DECIMAL
  41.    DOUBLE
  42.    DOUBLE PRECISION
  43.    ENUM
  44.    FLOAT
  45.    INT
  46.    INTEGER
  47.    LONGBLOB
  48.    LONGTEXT
  49.    MEDIUMBLOB
  50.    MEDIUMINT
  51.    MEDIUMTEXT
  52.    SET DATA TYPE
  53.    SMALLINT
  54.    TEXT
  55.    TIME
  56.    TIMESTAMP
  57.    TINYBLOB
  58.    TINYINT
  59.    TINYTEXT
  60.    VARBINARY
  61.    VARCHAR
  62.    YEAR DATA TYPE

  64. mysql> ? int
  65. Name: \'INT\'
  66. Description:
  67. INT[(M)] [UNSIGNED] [ZEROFILL]

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

  72. URL: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

快速查閱幫助

看show 命令和 create table命令

  1. mysql> ? show
  2. Name: \'SHOW\'
  3. Description:
  4. SHOW has many forms that provide information about databases, tables,
  5. columns, or status information about the server. This section describes
  6. those following:

  8. SHOW AUTHORS
  9. SHOW {BINARY | MASTER} LOGS
  10. SHOW BINLOG EVENTS [IN \'log_name\'] [FROM pos] [LIMIT [offset,] row_count]
  11. SHOW CHARACTER SET [like_or_where]
  12. SHOW COLLATION [like_or_where]
  13. SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
  14. SHOW CONTRIBUTORS
  15. SHOW CREATE DATABASE db_name
  16. SHOW CREATE EVENT event_name
  17. SHOW CREATE FUNCTION func_name
  18. SHOW CREATE PROCEDURE proc_name
  19. SHOW CREATE TABLE tbl_name
  20. SHOW CREATE TRIGGER trigger_name
  21. SHOW CREATE VIEW view_name
  22. SHOW DATABASES [like_or_where]
  23. SHOW ENGINE engine_name {STATUS | MUTEX}
  24. SHOW [STORAGE] ENGINES
  25. SHOW ERRORS [LIMIT [offset,] row_count]
  26. SHOW EVENTS
  27. SHOW FUNCTION CODE func_name
  28. SHOW FUNCTION STATUS [like_or_where]
  29. SHOW GRANTS FOR user
  30. SHOW INDEX FROM tbl_name [FROM db_name]
  31. SHOW MASTER STATUS
  32. SHOW OPEN TABLES [FROM db_name] [like_or_where]
  33. SHOW PLUGINS
  34. SHOW PROCEDURE CODE proc_name
  35. SHOW PROCEDURE STATUS [like_or_where]
  36. SHOW PRIVILEGES
  37. SHOW [FULL] PROCESSLIST
  38. SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
  39. SHOW PROFILES
  40. SHOW SLAVE HOSTS
  41. SHOW SLAVE STATUS
  42. SHOW [GLOBAL | SESSION] STATUS [like_or_where]
  43. SHOW TABLE STATUS [FROM db_name] [like_or_where]
  44. SHOW [FULL] TABLES [FROM db_name] [like_or_where]
  45. SHOW TRIGGERS [FROM db_name] [like_or_where]
  46. SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
  47. SHOW WARNINGS [LIMIT [offset,] row_count]

  49. like_or_where:
  50.     LIKE \'pattern\'
  51.   | WHERE expr

  53. If the syntax for a given SHOW statement includes a LIKE \'pattern\'
  54. part, \'pattern\' is a string that can contain the SQL \"%\" and \"_\"
  55. wildcard characters. The pattern is useful for restricting statement
  56. output to matching values.

  58. Several SHOW statements also accept a WHERE clause that provides more
  59. flexibility in specifying which rows to display. See
  60. http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.

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


  65. mysql> ? create table
  66. Name: \'CREATE TABLE\'
  67. Description:
  68. Syntax:
  69. CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  70.     (create_definition,...)
  71.     [table_options]
  72.     [partition_options]

  74. Or:

  76. CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  77.     [(create_definition,...)]
  78.     [table_options]
  79.     [partition_options]
  80.     select_statement

  82. Or:

  84. CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  85.     { LIKE old_tbl_name | (LIKE old_tbl_name) }

  87. create_definition:
  88.     col_name column_definition
  89.   | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  90.       [index_option] ...
  91.   | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
  92.       [index_option] ...
  93.   | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
  94.       [index_name] [index_type] (index_col_name,...)
  95.       [index_option] ...
  96.   | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
  97.       [index_option] ...
  98.   | [CONSTRAINT [symbol]] FOREIGN KEY
  99.       [index_name] (index_col_name,...) reference_definition
  100.   | CHECK (expr)

  102. column_definition:
  103.     data_type [NOT NULL | NULL] [DEFAULT default_value]
  104.       [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
  105.       [COMMENT \'string\']
  106.       [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
  107.       [STORAGE {DISK|MEMORY|DEFAULT}]
  108.       [reference_definition]

  110. data_type:
  111.     BIT[(length)]
  112.   | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  113.   | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  114.   | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  115.   | INT[(length)] [UNSIGNED] [ZEROFILL]
  116.   | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  117.   | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  118.   | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  119.   | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  120.   | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  121.   | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  122.   | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  123.   | DATE
  124.   | TIME
  125.   | TIMESTAMP
  126.   | DATETIME
  127.   | YEAR
  128.   | CHAR[(length)]
  129.       [CHARACTER SET charset_name] [COLLATE collation_name]
  130.   | VARCHAR(length)
  131.       [CHARACTER SET charset_name] [COLLATE collation_name]
  132.   | BINARY[(length)]
  133.   | VARBINARY(length)
  134.   | TINYBLOB
  135.   | BLOB
  136.   | MEDIUMBLOB
  137.   | LONGBLOB
  138.   | TINYTEXT [BINARY]
  139.       [CHARACTER SET charset_name] [COLLATE collation_name]
  140.   | TEXT [BINARY]
  141.       [CHARACTER SET charset_name] [COLLATE collation_name]
  142.   | MEDIUMTEXT [BINARY]
  143.       [CHARACTER SET charset_name] [COLLATE collation_name]
  144.   | LONGTEXT [BINARY]
  145.       [CHARACTER SET charset_name] [COLLATE collation_name]
  146.   | ENUM(value1,value2,value3,...)
  147.       [CHARACTER SET charset_name] [COLLATE collation_name]
  148.   | SET(value1,value2,value3,...)
  149.       [CHARACTER SET charset_name] [COLLATE collation_name]
  150.   | spatial_type

  152. index_col_name:
  153.     col_name [(length)] [ASC | DESC]

  155. index_type:
  156.     USING {BTREE | HASH}

  158. index_option:
  159.     KEY_BLOCK_SIZE [=] value
  160.   | index_type
  161.   | WITH PARSER parser_name
  162.   | COMMENT \'string\'

  164. reference_definition:
  165.     REFERENCES tbl_name (index_col_name,...)
  166.       [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
  167.       [ON DELETE reference_option]
  168.       [ON UPDATE reference_option]

  170. reference_option:
  171.     RESTRICT | CASCADE | SET NULL | NO ACTION

  173. table_options:
  174.     table_option [[,] table_option] ...

  176. table_option:
  177.     ENGINE [=] engine_name
  178.   | AUTO_INCREMENT [=] value
  179.   | AVG_ROW_LENGTH [=] value
  180.   | [DEFAULT] CHARACTER SET [=] charset_name
  181.   | CHECKSUM [=] {0 | 1}
  182.   | [DEFAULT] COLLATE [=] collation_name
  183.   | COMMENT [=] \'string\'
  184.   | CONNECTION [=] \'connect_string\'
  185.   | DATA DIRECTORY [=] \'absolute path to directory\'
  186.   | DELAY_KEY_WRITE [=] {0 | 1}
  187.   | INDEX DIRECTORY [=] \'absolute path to directory\'
  188.   | INSERT_METHOD [=] { NO | FIRST | LAST }
  189.   | KEY_BLOCK_SIZE [=] value
  190.   | MAX_ROWS [=] value
  191.   | MIN_ROWS [=] value
  192.   | PACK_KEYS [=] {0 | 1 | DEFAULT}
  193.   | PASSWORD [=] \'string\'
  194.   | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  195.   | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  196.   | UNION [=] (tbl_name[,tbl_name]...)

  198. partition_options:
  199.     PARTITION BY
  200.         { [LINEAR] HASH(expr)
  201.         | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
  202.         | RANGE{(expr) | COLUMNS(column_list)}
  203.         | LIST{(expr) | COLUMNS(column_list)} }
  204.     [PARTITIONS num]
  205.     [SUBPARTITION BY
  206.         { [LINEAR] HASH(expr)
  207.         | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
  208.       [SUBPARTITIONS num]
  209.     ]
  210.     [(partition_definition [, partition_definition] ...)]

  212. partition_definition:
  213.     PARTITION partition_name
  214.         [VALUES
  215.             {LESS THAN {(expr | value_list) | MAXVALUE}
  216.             |
  217.             IN (value_list)}]
  218.         [[STORAGE] ENGINE [=] engine_name]
  219.         [COMMENT [=] \'comment_text\' ]
  220.         [DATA DIRECTORY [=] \'data_dir\']
  221.         [INDEX DIRECTORY [=] \'index_dir\']
  222.         [MAX_ROWS [=] max_number_of_rows]
  223.         [MIN_ROWS [=] min_number_of_rows]
  224.         [TABLESPACE [=] tablespace_name]
  225.         [NODEGROUP [=] node_group_id]
  226.         [(subpartition_definition [, subpartition_definition] ...)]

  228. subpartition_definition:
  229.     SUBPARTITION logical_name
  230.         [[STORAGE] ENGINE [=] engine_name]
  231.         [COMMENT [=] \'comment_text\' ]
  232.         [DATA DIRECTORY [=] \'data_dir\']
  233.         [INDEX DIRECTORY [=] \'index_dir\']
  234.         [MAX_ROWS [=] max_number_of_rows]
  235.         [MIN_ROWS [=] min_number_of_rows]
  236.         [TABLESPACE [=] tablespace_name]
  237.         [NODEGROUP [=] node_group_id]

  239. select_statement:
  240.     [IGNORE | REPLACE] [AS] SELECT ... (Some valid select statement)

  242. CREATE TABLE creates a table with the given name. You must have the
  243. CREATE privilege for the table.

  245. Rules for permissible table names are given in
  246. http://dev.mysql.com/doc/refman/5.5/en/identifiers.html. By default,
  247. the table is created in the default database, using the InnoDB storage
  248. engine. An error occurs if the table exists, if there is no default
  249. database, or if the database does not exist.

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

常用的網路資源

http://dev.mysql.com/downloads 是MySQL的官方網站，可以下載各個版本的MySQL以及相關客戶端開發工具等。

http://dev.mysql.com/doc 提供了目前最權威的MySQL資料庫及工具的線上手冊。

http://bugs.mysql.com這裡可以檢視到MySQL已經發布的bug列表，或者向MySQL提交bug報告。

http://www.mysql.com/news-and-events/newsletter通常會發布各種關於MySQL的最新訊息


查詢後設資料資訊

資料庫information_schema，用來mysql中的後設資料資訊。後設資料指的是資料的資料，比如表名、列名、列型別、索引名等表的各種屬性名稱。
常用表有：schemata, tables, columns, statistics.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26435490/viewspace-1260056/，如需轉載，請註明出處，否則將追究法律責任。

相關文章