最近发布
随机推荐
在mysql的使用过程中, 可能经常会遇到以下问题:
某个操作语法忘记了, 如何快速查找?
如何快速知道当前版本上某个字段类型的取值范围?
当前版本都支持哪些函数?希望有例子说明..
当前版本是否支持某个功能?
#关键指令已高亮便于定位
[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>
帮助大纲
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
主要包括子主题有如下
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>
# 帮助不多，仅是一段概念解读
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>
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>
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>
显示线程状态
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>
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>
没错它就是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>
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>
主要包括以下五大类(此处浅尝辄止列一下)：
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>
MySQL数据库提供了很多函数包括：
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>
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>
在MySQL中，Geometry等几何对象可以用来表示地理位置，即用几何对象表示地理空间数据。
在MySQL中支持的几何数据类型包括Geometry、Point、LineString、Polygon以及集合类型的MultiPoint、MultiLineString、MultiPolygon、GeometryCollection。其中Geometry可以表示任意一种几何类型，即在MySQL中，如果一个字段类型是Geometry，则可以存储Point、LineString等其它几何类型的值。其他的几种则需要固定有效的表示格式。
在MySQL中有3种表达几何对象的格式：
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>
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>
是少了四个….
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
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
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
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>
A：你是一个数据库…???
B: 嗯嗯，俺是一个数据库…
豫公网安备 41148102000279号