MySQL基础之高效使用帮助信息

背景

对于特定版本的MySQL，有很多语法细节和功能特性需要了解；对于不同版本的MySQL，语法特性和功能特征又可能有差异。我们无法对每一细节都了如指掌，因此，在需要的时候如何高效的使用MySQL的帮助信息，对我们来说非常重要。

按层次查看帮助信息

举个例子，你想了解一下MySQL关于用户及权限管理相关的内容，那么就可以首先使用"help contents"查看一下帮助信息中提供的目录，很容易的找到Account Management的章节；继续使用help帮助命令，查看其具体内容"help account management"，会显示具体和账户管理相关的命令；使用help命令可以继续查看具体命令的帮助信息。

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
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql> help Account Management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ALTER USER
   CREATE USER
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET PASSWORD

mysql> help create USER
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER user_specification [, user_specification] ...

user_specification:
    user
    [
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
        IDENTIFIED BY [PASSWORD] 'password'
    ]
......

按关键字快速查询

在实际应用中，我们最常碰到的应该是查阅具体的语法特性，那么我们就可以使用help + 关键字来快速获取帮助信息。例如，你想查看级联操作cascade的用法，可以使用下列方法来快速获取帮助信息。

mysql> help cascade
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CONSTRAINT
   CREATE TABLE
   DROP TABLE
   DROP VIEW

mysql> help constraint
Name: 'CONSTRAINT'
Description:
MySQL supports foreign keys, which let you cross-reference related data
across tables, and foreign key constraints, which help keep this
spread-out data consistent. The essential syntax for a foreign key
constraint definition in a CREATE TABLE or ALTER TABLE statement looks
like this:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

URL: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Examples:
CREATE TABLE product (
    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=INNODB;

CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)   ENGINE=INNODB;

mysql>