前言

在mysql的使用过程中, 可能经常会遇到以下问题:

某个操作语法忘记了, 如何快速查找?
如何快速知道当前版本上某个字段类型的取值范围?
当前版本都支持哪些函数?希望有例子说明..
当前版本是否支持某个功能?

对于上面列出的问题, 我们可能想到的方法是查找MySQL的文档, 这些问题在官方文档都可以很清楚地查到, 但是却要耗费大量的时间和精力.
便携的解决办法就是使用MySQL安装后自带的帮助文档, 这样在遇到问题时就可以方便快捷地进行查询.
本文档适用于Mysql5.7及同期MariaDB

循序渐进查内置帮助

登陆mysql后，从键入help开始
表演help开始
1. 获取分类主题大岗 help contents
2. 获取分类章节 help <categories item>
3. 获取详细小节 help <topics item>
走个示例
1. CREATE USER

#关键指令已高亮便于定位
[root@centos76 ~]# mysql -uroot -p -S /tmp/mysql.sock
或
[root@centos76 ~]# mysql -uroot -p -P3306
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 204
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> \h

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> 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 [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see )

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

The CREATE USER statement creates new MySQL accounts. It enables
authentication, SSL/TLS, resource-limit, and password-management
properties to be established for new accounts, and controls whether
accounts are initially locked or unlocked.

To use CREATE USER, you must have the global CREATE USER privilege, or
the INSERT privilege for the mysql system database. When the read_only
system variable is enabled, CREATE USER additionally requires the SUPER
privilege.

An error occurs if you try to create an account that already exists. If
the IF NOT EXISTS clause is given, the statement produces a warning for
each named account that already exists, rather than an error.

URL: http://dev.mysql.com/doc/refman/5.7/en/create-user.html
mysql>

牛刀小试


mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create  user testUser IDENTIFIED BY 'auth_string' ACCOUNT LOCK;
Query OK, 0 rows affected (0.03 sec)

mysql> select user,host,IDENTIFIED_STRING from user;
ERROR 1054 (42S22): Unknown column 'IDENTIFIED_STRING' in 'field list'
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *CB75BC4D1C974CFEE*********************** |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | %         | *CB75BC4D1C974CF51B0********************* |
| testUser      | %         | *67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF |
+---------------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> select user,host,authentication_string,account_locked  from user;
+---------------+-----------+-------------------------------------------+----------------+
| user          | host      | authentication_string                     | account_locked |
+---------------+-----------+-------------------------------------------+----------------+
| root          | localhost | *CB75BC4D1C974CF51B040D****************** | N              |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y              |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y              |
| root          | %         | *CB75BC4D1C974CF51B040******************* | N              |
| testUser      | %         | *67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF | Y              |
+---------------+-----------+-------------------------------------------+----------------+
7 rows in set (0.00 sec)

mysql> create  user testVser@localhost IDENTIFIED BY 'auth_string' ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string,account_locked  from user;
+---------------+-----------+-------------------------------------------+----------------+
| user          | host      | authentication_string                     | account_locked |
+---------------+-----------+-------------------------------------------+----------------+
| root          | localhost | *CB75BC4D1C974CF51B0********************* | N              |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y              |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y              |
| root          | %         | *CB75BC4D1C974CF51B0********************* | N              |
| testUser      | %         | *67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF | Y              |
| testVser      | localhost | *67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF | N              |
+---------------+-----------+-------------------------------------------+----------------+
8 rows in set (0.00 sec)

mysql>

窥伺全貌

帮助大纲

help contents

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
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins                                                   # show plugins 显示启用插件
   Procedures
   Storage Engines                                           # 无内置帮助
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility
mysql># 查看帮助主题分类的帮助示例
mysql>help Account Management
mysql>help Administration

Administration运维管理

主要包括子主题有如下

show状态查询
flush、binlog
主从状态
服务管理：kill 、shutdown

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>

help BINLOG

# 帮助不多，仅是一段概念解读

mysql> help binlog;
Name: 'BINLOG'
Description:
Syntax:
BINLOG 'str'

BINLOG is an internal-use statement. It is generated by the mysqlbinlog
program as the printable representation of certain events in binary log
files. (See https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html.)
The 'str' value is a base 64-encoded string the that server decodes to
determine the data change indicated by the corresponding event. This
statement requires the SUPER privilege.

URL: https://dev.mysql.com/doc/refman/5.7/en/binlog.html


mysql>

help CACHE INDEX

mysql> help CACHE INDEX
Name: 'CACHE INDEX'
Description:
Syntax:
CACHE INDEX {
      tbl_index_list [, tbl_index_list] ...
    | tbl_name PARTITION (partition_list)
  }
  IN key_cache_name

tbl_index_list:
  tbl_name [{INDEX|KEY} (index_name[, index_name] ...)]

partition_list: {
    partition_name[, partition_name] ...
  | ALL
}

The CACHE INDEX statement assigns table indexes to a specific key
cache. It applies only to MyISAM tables, including partitioned MyISAM
tables. After the indexes have been assigned, they can be preloaded
into the cache if desired with LOAD INDEX INTO CACHE.

The following statement assigns indexes from the tables t1, t2, and t3
to the key cache named hot_cache:

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

URL: https://dev.mysql.com/doc/refman/5.7/en/cache-index.html

mysql>

CACHE INDEX语句将 table 索引分配给特定的 key 缓存。它仅适用于MyISAM表，包括分区的MyISAM表。分配索引后，如果需要，可以使用负荷指数进入高速缓存将它们预加载到缓存中。
默认情况下，table 索引分配给在服务器启动时创建的主(默认)key 缓存。销毁 key 缓存时，分配给它的所有索引都会重新分配给默认的 key 缓存。
索引分配会全局影响服务器：如果一个 client 为给定缓存分配索引，则无论哪个 client 发出查询，此缓存都将用于涉及索引的所有查询。
分区的MyISAM表支持CACHE INDEX。您可以将一个，多个或所有分区的一个或多个索引分配给给定的 key 缓存。

help SHOW BINARY LOGS

mysql> help SHOW BINARY LOGS
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS

Lists the binary log files on the server. This statement is used as
part of the procedure described in [HELP PURGE BINARY LOGS], that shows
how to determine which logs can be purged. A user with the SUPER or
REPLICATION CLIENT privilege may execute this statement.

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+

URL: https://dev.mysql.com/doc/refman/5.7/en/show-binary-logs.html
mysql>

help SHOW PROCESSLIST

显示线程状态


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>

help SHUTDOWN

mysql> help SHUTDOWN;      # 从内部执行shutdown可以关闭mysql，关闭后，就要通过服务命令来启动了。比如systemctl start mysql
Name: 'SHUTDOWN'
Description:
Syntax:
SHUTDOWN

This statement stops the MySQL server. It requires the SHUTDOWN
privilege.

SHUTDOWN was added in MySQL 5.7.9. It provides an SQL-level interface
to the same functionality available using the mysqladmin shutdown
command or the mysql_shutdown() C API function.

URL: https://dev.mysql.com/doc/refman/5.7/en/shutdown.html
mysql>

Data Definition 数据定义语DDL

没错它就是DDL，建表、改表（增减字段），删表都在这里。对的，操作需要权限（ privilege ）,只操作字段（列），不操作行（记录）。
DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。

mysql> help 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 INSTANCE
   ALTER LOGFILE GROUP
   ALTER PROCEDURE
   ALTER SCHEMA
   ALTER SERVER
   ALTER TABLE
   ALTER TABLESPACE
   ALTER VIEW
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE INDEX
   CREATE LOGFILE GROUP
   CREATE PROCEDURE
   CREATE SCHEMA
   CREATE SERVER
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE VIEW
   DROP DATABASE
   DROP EVENT
   DROP FUNCTION
   DROP INDEX
   DROP PROCEDURE
   DROP SCHEMA
   DROP SERVER
   DROP TABLE
   DROP TABLESPACE
   DROP TRIGGER
   DROP VIEW
   FOREIGN KEY
   RENAME TABLE
   TRUNCATE TABLE

mysql>

Data Manipulation 数据操纵语句DML

DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）

mysql> help Data Manipulation;
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:
   CALL
   DELETE
   DO
   DUAL
   HANDLER
   INSERT
   INSERT DELAYED
   INSERT SELECT
   JOIN
   LOAD DATA
   LOAD XML
   REPLACE
   SELECT
   UNION
   UPDATE

mysql>

Data Types数据类型

主要包括以下五大类(此处浅尝辄止列一下)：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

mysql> help 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>

Functions函数

MySQL数据库提供了很多函数包括：

数学函数
字符串函数 : mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
日期和时间函数
条件判断函数
系统信息函数
加密函数
格式化函数

mysql> help Functions;
You asked for help about help category: "Functions"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Bit Functions
   Comparison Operators
   Control Flow Functions
   Date and Time Functions
   Encryption Functions
   GROUP BY Functions and Modifiers
   Information Functions
   Locking Functions
   Logical Operators
   Miscellaneous Functions
   Numeric Functions
   Spatial Functions
   String Functions

mysql> help Numeric Functions
You asked for help about help category: "Numeric Functions"
For more information, type 'help <item>', where <item> is one of the following
topics:
   %
   *
   +
   - BINARY
   - UNARY
   /
   ABS
   ACOS
   ASIN
   ATAN
   ATAN2
   CEIL
   CEILING
   CONV
   COS
   COT
   CRC32
   DEGREES
   DIV
   EXP
   FLOOR
   LN
   LOG
   LOG10
   LOG2
   MOD
   PI
   POW
   POWER
   RADIANS
   RAND
   ROUND
   SIGN
   SIN
   SQRT
   TAN
   TRUNCATE

mysql> help 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
   FROM_BASE64
   HEX
   INSERT FUNCTION
   INSTR
   LCASE
   LEFT
   LENGTH
   LIKE
   LOAD_FILE
   LOCATE
   LOWER
   LPAD
   LTRIM
   MAKE_SET
   MATCH AGAINST
   MID
   NOT LIKE
   NOT REGEXP
   OCT
   OCTET_LENGTH
   ORD
   POSITION
   QUOTE
   REGEXP
   REPEAT FUNCTION
   REPLACE FUNCTION
   REVERSE
   RIGHT
   RPAD
   RTRIM
   SOUNDEX
   SOUNDS LIKE
   SPACE
   STRCMP
   SUBSTR
   SUBSTRING
   SUBSTRING_INDEX
   TO_BASE64
   TRIM
   UCASE
   UNHEX
   UPDATEXML
   UPPER
   WEIGHT_STRING

mysql>

Table Maintenance维护表

mysql> help Table Maintenance
You asked for help about help category: "Table Maintenance"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ANALYZE TABLE
   CHECK TABLE
   CHECKSUM TABLE
   OPTIMIZE TABLE
   REPAIR TABLE

mysql> help CHECKSUM TABLE
Name: 'CHECKSUM TABLE'
Description:
Syntax:
CHECKSUM TABLE tbl_name [, tbl_name] ... [QUICK | EXTENDED]

CHECKSUM TABLE reports a checksum for the contents of a table. You can
use this statement to verify that the contents are the same before and
after a backup, rollback, or other operation that is intended to put
the data back to a known state.
# 可对表备份前后进行检验
This statement requires the SELECT privilege for the table.

This statement is not supported for views. If you run CHECKSUM TABLE
against a view, the Checksum value is always NULL, and a warning is
returned.

For a nonexistent table, CHECKSUM TABLE returns NULL and generates a
warning.

During the checksum operation, the table is locked with a read lock for
InnoDB and MyISAM.

URL: https://dev.mysql.com/doc/refman/5.7/en/checksum-table.html


mysql>

GEOMETRY HIERARCHY 地理特性

在MySQL中，Geometry等几何对象可以用来表示地理位置，即用几何对象表示地理空间数据。

在MySQL中支持的几何数据类型包括Geometry、Point、LineString、Polygon以及集合类型的MultiPoint、MultiLineString、MultiPolygon、GeometryCollection。其中Geometry可以表示任意一种几何类型，即在MySQL中，如果一个字段类型是Geometry，则可以存储Point、LineString等其它几何类型的值。其他的几种则需要固定有效的表示格式。

数据格式

在MySQL中有3种表达几何对象的格式：

WKT（文本格式）
WKB（二进制格式）
MySQL内部存储格式

WKT是文本格式，因此可以直接使用文本来表示几何数据，实现数据的插入与编辑。

常用函数

ST_GEOMFROMTEXT 用于将几何数据从可读的文本类型转换成内部存储的二进制类型。

mysql> help GEOMETRY HIERARCHY
Name: 'GEOMETRY HIERARCHY'
Description:
Geometry is the base class. It is an abstract class. The instantiable
subclasses of Geometry are restricted to zero-, one-, and
two-dimensional geometric objects that exist in two-dimensional
coordinate space. All instantiable geometry classes are defined so that
valid instances of a geometry class are topologically closed (that is,
all defined geometries include their boundary).

The base Geometry class has subclasses for Point, Curve, Surface, and
GeometryCollection:

o Point represents zero-dimensional objects.

o Curve represents one-dimensional objects, and has subclass
  LineString, with sub-subclasses Line and LinearRing.

o Surface is designed for two-dimensional objects and has subclass
  Polygon.

o GeometryCollection has specialized zero-, one-, and two-dimensional
  collection classes named MultiPoint, MultiLineString, and
  MultiPolygon for modeling geometries corresponding to collections of
  Points, LineStrings, and Polygons, respectively. MultiCurve and
  MultiSurface are introduced as abstract superclasses that generalize
  the collection interfaces to handle Curves and Surfaces.

Geometry, Curve, Surface, MultiCurve, and MultiSurface are defined as
noninstantiable classes. They define a common set of methods for their
subclasses and are included for extensibility.

Point, LineString, Polygon, GeometryCollection, MultiPoint,
MultiLineString, and MultiPolygon are instantiable classes.

URL: https://dev.mysql.com/doc/refman/5.7/en/gis-geometry-class-hierarchy.html


mysql>

Utility 帮助的帮助

mysql> help Utility
You asked for help about help category: "Utility"
For more information, type 'help <item>', where <item> is one of the following
topics:
   DESC
   DESCRIBE
   EXPLAIN
   HELP STATEMENT
   USE

mysql> help USE;
Name: 'USE'
Description:
Syntax:
USE db_name

The USE db_name statement tells MySQL to use the db_name database as
the default (current) database for subsequent statements. The database
remains the default until the end of the session or another USE
statement is issued:

USE db1;
SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable;   # selects from db2.mytable

The database name must be specified on a single line. Newlines in
database names are not supported.

URL: https://dev.mysql.com/doc/refman/5.7/en/use.html


mysql> help HELP STATEMENT
Name: 'HELP STATEMENT'
Description:
Syntax:
HELP 'search_string'

The HELP statement returns online information from the MySQL Reference
Manual. Its proper operation requires that the help tables in the mysql
database be initialized with help topic information (see
https://dev.mysql.com/doc/refman/5.7/en/server-side-help-support.html).

The HELP statement searches the help tables for the given search string
and displays the result of the search. The search string is not
case-sensitive.

The search string can contain the wildcard characters % and _. These
have the same meaning as for pattern-matching operations performed with
the LIKE operator. For example, HELP 'rep%' returns a list of topics
that begin with rep.

URL: https://dev.mysql.com/doc/refman/5.7/en/help.html


mysql>

至此占到即止

是少了四个….

INSERT增

mysql> help INSERT;
Name: 'INSERT'
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

INSERT inserts new rows into an existing table. The INSERT ... VALUES
and INSERT ... SET forms of the statement insert rows based on
explicitly specified values. The INSERT ... SELECT form inserts rows
selected from another table or tables. INSERT with an ON DUPLICATE KEY
UPDATE clause enables existing rows to be updated if a row to be
inserted would cause a duplicate value in a UNIQUE index or PRIMARY
KEY.

For additional information about INSERT ... SELECT and INSERT ... ON
DUPLICATE KEY UPDATE, see [HELP INSERT SELECT], and
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html.

In MySQL 5.7, the DELAYED keyword is accepted but ignored by the
server. For the reasons for this, see [HELP INSERT DELAYED],

Inserting into a table requires the INSERT privilege for the table. If
the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes
an UPDATE to be performed instead, the statement requires the UPDATE
privilege for the columns to be updated. For columns that are read but
not modified you need only the SELECT privilege (such as for a column
referenced only on the right hand side of an col_name=expr assignment
in an ON DUPLICATE KEY UPDATE clause).

When inserting into a partitioned table, you can control which
partitions and subpartitions accept new rows. The PARTITION option
takes a list of the comma-separated names of one or more partitions or
subpartitions (or both) of the table. If any of the rows to be inserted
by a given INSERT statement do not match one of the partitions listed,
the INSERT statement fails with the error Found a row not matching the
given partition set. For more information and examples, see
https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html.

URL: https://dev.mysql.com/doc/refman/5.7/en/insert.html

DELETE删

mysql> help DELETE;
Name: 'DELETE'
Description:
Syntax:
DELETE is a DML statement that removes rows from a table.

Single-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

The DELETE statement deletes rows from tbl_name and returns the number
of deleted rows. To check the number of deleted rows, call the
ROW_COUNT() function described in
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html.

Main Clauses

The conditions in the optional WHERE clause identify which rows to
delete. With no WHERE clause, all rows are deleted.

where_condition is an expression that evaluates to true for each row to
be deleted. It is specified as described in
https://dev.mysql.com/doc/refman/5.7/en/select.html.

If the ORDER BY clause is specified, the rows are deleted in the order
that is specified. The LIMIT clause places a limit on the number of
rows that can be deleted. These clauses apply to single-table deletes,
but not multi-table deletes.

Multiple-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Privileges

You need the DELETE privilege on a table to delete rows from it. You
need only the SELECT privilege for any columns that are only read, such
as those named in the WHERE clause.

Performance

When you do not need to know the number of deleted rows, the TRUNCATE
TABLE statement is a faster way to empty a table than a DELETE
statement with no WHERE clause. Unlike DELETE, TRUNCATE TABLE cannot be
used within a transaction or if you have a lock on the table. See [HELP
TRUNCATE TABLE] and [HELP LOCK TABLES].

The speed of delete operations may also be affected by factors
discussed in
https://dev.mysql.com/doc/refman/5.7/en/delete-optimization.html.

To ensure that a given DELETE statement does not take too much time,
the MySQL-specific LIMIT row_count clause for DELETE specifies the
maximum number of rows to be deleted. If the number of rows to delete
is larger than the limit, repeat the DELETE statement until the number
of affected rows is less than the LIMIT value.

Subqueries

You cannot delete from a table and select from the same table in a
subquery.

Partitioned Table Support

DELETE supports explicit partition selection using the PARTITION
option, which takes a list of the comma-separated names of one or more
partitions or subpartitions (or both) from which to select rows to be
dropped. Partitions not included in the list are ignored. Given a
partitioned table t with a partition named p0, executing the statement
DELETE FROM t PARTITION (p0) has the same effect on the table as
executing ALTER TABLE t TRUNCATE PARTITION (p0); in both cases, all
rows in partition p0 are dropped.

PARTITION can be used along with a WHERE condition, in which case the
condition is tested only on rows in the listed partitions. For example,
DELETE FROM t PARTITION (p0) WHERE c < 5 deletes rows only from
partition p0 for which the condition c < 5 is true; rows in any other
partitions are not checked and thus not affected by the DELETE.

The PARTITION option can also be used in multiple-table DELETE
statements. You can use up to one such option per table named in the
FROM option.

For more information and examples, see
https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html.

URL: https://dev.mysql.com/doc/refman/5.7/en/delete.html

UPDATE改

mysql> help UPDATE;
Name: 'UPDATE'
Description:
Syntax:
UPDATE is a DML statement that modifies rows in a table.

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

For the single-table syntax, the UPDATE statement updates columns of
existing rows in the named table with new values. The SET clause
indicates which columns to modify and the values they should be given.
Each value can be given as an expression, or the keyword DEFAULT to set
a column explicitly to its default value. The WHERE clause, if given,
specifies the conditions that identify which rows to update. With no
WHERE clause, all rows are updated. If the ORDER BY clause is
specified, the rows are updated in the order that is specified. The
LIMIT clause places a limit on the number of rows that can be updated.

For the multiple-table syntax, UPDATE updates rows in each table named
in table_references that satisfy the conditions. Each matching row is
updated once, even if it matches the conditions multiple times. For
multiple-table syntax, ORDER BY and LIMIT cannot be used.

For partitioned tables, both the single-single and multiple-table forms
of this statement support the use of a PARTITION option as part of a
table reference. This option takes a list of one or more partitions or
subpartitions (or both). Only the partitions (or subpartitions) listed
are checked for matches, and a row that is not in any of these
partitions or subpartitions is not updated, whether it satisfies the
where_condition or not.

*Note*:

Unlike the case when using PARTITION with an INSERT or REPLACE
statement, an otherwise valid UPDATE ... PARTITION statement is
considered successful even if no rows in the listed partitions (or
subpartitions) match the where_condition.

For more information and examples, see
https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html.

where_condition is an expression that evaluates to true for each row to
be updated. For expression syntax, see
https://dev.mysql.com/doc/refman/5.7/en/expressions.html.

table_references and where_condition are specified as described in
https://dev.mysql.com/doc/refman/5.7/en/select.html.

You need the UPDATE privilege only for columns referenced in an UPDATE
that are actually updated. You need only the SELECT privilege for any
columns that are read but not modified.

The UPDATE statement supports the following modifiers:

o With the LOW_PRIORITY modifier, execution of the UPDATE is delayed
  until no other clients are reading from the table. This affects only
  storage engines that use only table-level locking (such as MyISAM,
  MEMORY, and MERGE).

o With the IGNORE modifier, the update statement does not abort even if
  errors occur during the update. Rows for which duplicate-key
  conflicts occur on a unique key value are not updated. Rows updated
  to values that would cause data conversion errors are updated to the
  closest valid values instead. For more information, see
  https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#ignore-strict-c
  omparison.

URL: https://dev.mysql.com/doc/refman/5.7/en/update.html

SELECT查

mysql> help SELECT;
Name: 'SELECT'
Description:
Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables, and
can include UNION statements and subqueries. See [HELP UNION], and
https://dev.mysql.com/doc/refman/5.7/en/subqueries.html.

The most commonly used clauses of SELECT statements are these:

o Each select_expr indicates a column that you want to retrieve. There
  must be at least one select_expr.

o table_references indicates the table or tables from which to retrieve
  rows. Its syntax is described in [HELP JOIN].

o SELECT supports explicit partition selection using the PARTITION with
  a list of partitions or subpartitions (or both) following the name of
  the table in a table_reference (see [HELP JOIN]). In this case, rows
  are selected only from the partitions listed, and any other
  partitions of the table are ignored. For more information and
  examples, see
  https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html.

  SELECT ... PARTITION from tables using storage engines such as MyISAM
  that perform table-level locks (and thus partition locks) lock only
  the partitions or subpartitions named by the PARTITION option.

  For more information, see
  https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-lock
  ing.html.

o The WHERE clause, if given, indicates the condition or conditions
  that rows must satisfy to be selected. where_condition is an
  expression that evaluates to true for each row to be selected. The
  statement selects all rows if there is no WHERE clause.

  In the WHERE expression, you can use any of the functions and
  operators that MySQL supports, except for aggregate (summary)
  functions. See
  https://dev.mysql.com/doc/refman/5.7/en/expressions.html, and
  https://dev.mysql.com/doc/refman/5.7/en/functions.html.

SELECT can also be used to retrieve rows computed without reference to
any table.

URL: https://dev.mysql.com/doc/refman/5.7/en/select.html


mysql>

好了，还有什么可以查到

select * from table where in like and order by limit;

mysql> help COMMAND;

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

mysql> help FROM
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中都有它的身影
topics:
   DELETE
   SELECT
   SHOW
   SHOW BINLOG EVENTS
   SHOW COLUMNS
   SHOW FIELDS
   SHOW INDEX
   SHOW RELAYLOG EVENTS
   TRIM

mysql> help IN
Name: 'IN'
Description:
Syntax:
expr IN (value,...)

Returns 1 if expr is equal to any of the values in the IN list, else
returns 0. If all values are constants, they are evaluated according to
the type of expr and sorted. The search for the item then is done using
a binary search. This means IN is very quick if the IN value list
consists entirely of constants. Otherwise, type conversion takes place
according to the rules described in
https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html, but
applied to all the arguments.

URL: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html

Examples:
mysql> SELECT 2 IN (0,3,5,7);
        -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
        -> 1

mysql> help WHERE
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中都有它的身影
topics:
   DELETE
   HANDLER
   SHOW COLUMNS
   SHOW FIELDS
   SHOW INDEX
   UPDATE

mysql> help ORDER
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中都有它的身影
topics:
   ALTER TABLE
   DELETE
   GROUP_CONCAT
   SELECT
   UPDATE

mysql> help LIMIT;
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中都有它的身影
topics:
   DELETE
   HANDLER
   SELECT
   SHOW BINLOG EVENTS
   SHOW RELAYLOG EVENTS
   UPDATE

mysql> help *;
Name: '*'
Description:
Syntax:
*

Multiplication:

URL: https://dev.mysql.com/doc/refman/5.7/en/arithmetic-functions.html

Examples:
mysql> SELECT 3*5;
        -> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
mysql> SELECT 18014398509481984*18014398509481984;
        -> out-of-range error

mysql> help and;
Name: 'AND'
Description:
Syntax:
AND, &&

Logical AND. Evaluates to 1 if all operands are nonzero and not NULL,
to 0 if one or more operands are 0, otherwise NULL is returned.

URL: https://dev.mysql.com/doc/refman/5.7/en/logical-operators.html

Examples:
mysql> SELECT 1 AND 1;
        -> 1
mysql> SELECT 1 AND 0;
        -> 0
mysql> SELECT 1 AND NULL;
        -> NULL
mysql> SELECT 0 AND NULL;
        -> 0
mysql> SELECT NULL AND 0;
        -> 0

mysql> help like;
Name: 'LIKE'
Description:
Syntax:
expr LIKE pat [ESCAPE 'escape_char']

Pattern matching using an SQL pattern. Returns 1 (TRUE) or 0 (FALSE).
If either expr or pat is NULL, the result is NULL.

The pattern need not be a literal string. For example, it can be
specified as a string expression or table column.

URL: https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

Examples:
mysql> SELECT 'David!' LIKE 'David_';
        -> 1
mysql> SELECT 'David!' LIKE '%D%v%';
        -> 1

mysql>

END

A：你是一个数据库…???
B: 嗯嗯，俺是一个数据库…

目录

前言