1.客户端命令介绍
1.1mysql 数据库连接工具
a.将sql语句发送到服务器
b.管理数据库
c.命令自带帮助
1.2mysqladmin
a.命令行管理工具
b.关闭数据库
c.修改密码
1.3mysqldump
a.备份表和数据管理工具
2.获取帮助的方法
| \h help ? | 帮助：会打印出程序自带的命令 |
| \G | 行与列转换 |
| \T tee | 将命令行中执行的命令保存在设置文件中，退出不保存 |
| \c Ctrl-c | 退出当前行 |
| \s status | 查看系统状态信息 |
| \. source命令 | 导入sql脚本执行，通常会用于数据恢复 |
| \u use | 查看库、表 |
| 快捷键 |
上下翻页 ==> 上下键 tab补全 ===> use world; desc city; Ctrl +l ===> 清屏 \q ===> 退出 |
2.1显示所有支持的sql语句类型
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
2.2 显示所有授权grant语句
mysql> help grant Name: 'GRANT' Description: Syntax: GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user [auth_option] [, user [auth_option]] ... [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] [WITH {GRANT OPTION | resource_option} ...] GRANT PROXY ON user TO user [, user] ... [WITH GRANT OPTION] object_type: { TABLE | FUNCTION | PROCEDURE } priv_level: { * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name } user: (see http://dev.mysql.com/doc/refman/5.6/en/account-names.html) auth_option: { IDENTIFIED BY 'auth_string' | IDENTIFIED BY PASSWORD 'hash_string' | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin AS '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 } The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. When the read_only system variable is enabled, GRANT additionally requires the SUPER privilege. The REVOKE statement is related to GRANT and enables administrators to remove account privileges. See [HELP REVOKE]. Each account name uses the format described in http://dev.mysql.com/doc/refman/5.6/en/account-names.html. For example: GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; The host name part of the account, if omitted, defaults to '%'. Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'; GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
2.3显示所有create语句
mysql> help create; 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: CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE FUNCTION UDF CREATE INDEX CREATE LOGFILE GROUP CREATE PROCEDURE CREATE SERVER CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW SHOW SHOW CREATE DATABASE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW CREATE TABLE SPATIAL
2.4显示所有create database 语句
mysql> help create database Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. URL: http://dev.mysql.com/doc/refman/5.6/en/create-database.html
2.5.显示所有create tables语句
mysql> help create table; Name: 'CREATE TABLE' Description: Syntax: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] [IGNORE | REPLACE] [AS] query_expression CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) } create_definition: col_name column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | CHECK (expr) column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] [reference_definition] data_type: BIT[(length)] | TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL] | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL] | DATE | TIME[(fsp)] | TIMESTAMP[(fsp)] | DATETIME[(fsp)] | YEAR | CHAR[(length)] [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | VARCHAR(length) [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | BINARY[(length)] | VARBINARY(length) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | TEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | MEDIUMTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | LONGTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | ENUM(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] | SET(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] | spatial_type index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' reference_definition: REFERENCES tbl_name (index_col_name,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION table_options: table_option [[,] table_option] ... table_option: ENGINE [=] engine_name | AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET [=] charset_name | CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] 'string' | CONNECTION [=] 'connect_string' | DATA DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | INDEX DIRECTORY [=] 'absolute path to directory' | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | STATS_AUTO_RECALC [=] {DEFAULT|0|1} | STATS_PERSISTENT [=] {DEFAULT|0|1} | STATS_SAMPLE_PAGES [=] value | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] | UNION [=] (tbl_name[,tbl_name]...) partition_options: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) | RANGE{(expr) | COLUMNS(column_list)} | LIST{(expr) | COLUMNS(column_list)} } [PARTITIONS num] [SUBPARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) } [SUBPARTITIONS num] ] [(partition_definition [, partition_definition] ...)] partition_definition: PARTITION partition_name [VALUES {LESS THAN {(expr | value_list) | MAXVALUE} | IN (value_list)}] [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [NODEGROUP [=] node_group_id] [(subpartition_definition [, subpartition_definition] ...)] subpartition_definition: SUBPARTITION logical_name [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [NODEGROUP [=] node_group_id] query_expression: SELECT ... (Some valid select or union statement) CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table. Rules for permissible table names are given in http://dev.mysql.com/doc/refman/5.6/en/identifiers.html. By default, the table is created in the default database, using the InnoDB storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist. URL: http://dev.mysql.com/doc/refman/5.6/en/create-table.html
2.6.alter database 语句
mysql> help alter database ; Name: 'ALTER DATABASE' Description: Syntax: ALTER {DATABASE | SCHEMA} [db_name] alter_specification ... ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name ALTER DATABASE enables you to change the overall characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need the ALTER privilege on the database. ALTER SCHEMA is a synonym for ALTER DATABASE. The database name can be omitted from the first syntax, in which case the statement applies to the default database. National Language Characteristics The CHARACTER SET clause changes the default database character set. The COLLATE clause changes the default database collation. http://dev.mysql.com/doc/refman/5.6/en/charset.html, discusses character set and collation names. You can see what character sets and collations are available using, respectively, the SHOW CHARACTER SET and SHOW COLLATION statements. See [HELP SHOW CHARACTER SET], and [HELP SHOW COLLATION], for more information. If you change the default character set or collation for a database, stored routines that use the database defaults must be dropped and recreated so that they use the new defaults. (In a stored routine, variables with character data types use the database defaults if the character set or collation are not specified explicitly. See [HELP CREATE PROCEDURE].) Upgrading from Versions Older than MySQL 5.1 The syntax that includes the UPGRADE DATA DIRECTORY NAME clause updates the name of the directory associated with the database to use the encoding implemented in MySQL 5.1 for mapping database names to database directory names (see http://dev.mysql.com/doc/refman/5.6/en/identifier-mapping.html). This clause is for use under these conditions: o It is intended when upgrading MySQL to 5.1 or later from older versions. o It is intended to update a database directory name to the current encoding format if the name contains special characters that need encoding. o The statement is used by mysqlcheck (as invoked by mysql_upgrade). For example, if a database in MySQL 5.0 has the name a-b-c, the name contains instances of the - (dash) character. In MySQL 5.0, the database directory is also named a-b-c, which is not necessarily safe for all file systems. In MySQL 5.1 and later, the same database name is encoded as a@002db@002dc to produce a file system-neutral directory name. When a MySQL installation is upgraded to MySQL 5.1 or later from an older version,the server displays a name such as a-b-c (which is in the old format) as #mysql50#a-b-c, and you must refer to the name using the #mysql50# prefix. Use UPGRADE DATA DIRECTORY NAME in this case to explicitly tell the server to re-encode the database directory name to the current encoding format: ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; After executing this statement, you can refer to the database as a-b-c without the special #mysql50# prefix. URL: http://dev.mysql.com/doc/refman/5.6/en/alter-database.html
2.7.alter table语句
mysql> help alter table ; Name: 'ALTER TABLE' Description: Syntax: ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options] alter_specification: table_options | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...) | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | ADD SPATIAL [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | ALGORITHM [=] {DEFAULT|INPLACE|COPY} | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO|AS] new_tbl_name | ORDER BY col_name [, col_name] ... | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | FORCE | ADD PARTITION (partition_definition) | DROP PARTITION partition_names | TRUNCATE PARTITION {partition_names | ALL} | COALESCE PARTITION number | REORGANIZE PARTITION partition_names INTO (partition_definitions) | EXCHANGE PARTITION partition_name WITH TABLE tbl_name | ANALYZE PARTITION {partition_names | ALL} | CHECK PARTITION {partition_names | ALL} | OPTIMIZE PARTITION {partition_names | ALL} | REBUILD PARTITION {partition_names | ALL} | REPAIR PARTITION {partition_names | ALL} | REMOVE PARTITIONING index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' table_options: table_option [[,] table_option] ... (see CREATE TABLE options) partition_options: (see CREATE TABLE options) ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change characteristics such as the storage engine used for the table or the table comment. Following the table name, specify the alterations to be made. If none are given, ALTER TABLE does nothing. The syntax for many of the permissible alterations is similar to clauses of the CREATE TABLE statement. See [HELP CREATE TABLE], for more information. table_options signifies table options of the kind that can be used in the CREATE TABLE statement, such as ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, MAX_ROWS, or ROW_FORMAT. For a list of all table options and a description of each, see [HELP CREATE TABLE]. However, ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY table options. Use of table options with ALTER TABLE provides a convenient way of altering single table characteristics. For example, if t1 is currently not an InnoDB table, this statement changes its storage engine to InnoDB: ALTER TABLE t1 ENGINE = InnoDB; To change the InnoDB table to use compressed row-storage format: ALTER TABLE t1 ROW_FORMAT = COMPRESSED; To reset the current auto-increment value: ALTER TABLE t1 AUTO_INCREMENT = 13; To change the default table character set: ALTER TABLE t1 CHARACTER SET = utf8; To add (or change) a table comment: ALTER TABLE t1 COMMENT = 'New table comment'; To verify that the table options were changed as you intend, use SHOW CREATE TABLE. partition_options signifies options that can be used with partitioned tables for repartitioning, for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance. It is possible for an ALTER TABLE statement to contain a PARTITION BY or REMOVE PARTITIONING clause in an addition to other alter specifications, but the PARTITION BY or REMOVE PARTITIONING clause must be specified last after any other specifications. The ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REORGANIZE PARTITION, EXCHANGE PARTITION, ANALYZE PARTITION, CHECK PARTITION, and REPAIR PARTITION options cannot be combined with other alter specifications in a single ALTER TABLE, since the options just listed act on individual partitions. For more information about partition options, see [HELP CREATE TABLE], and http://dev.mysql.com/doc/refman/5.6/en/alter-table-partition-operations .html. For information about and examples of ALTER TABLE ... EXCHANGE PARTITION statements, see http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange .html. Some operations may result in warnings if attempted on a table for which the storage engine does not support the operation. These warnings can be displayed with SHOW WARNINGS. See [HELP SHOW WARNINGS]. For information on troubleshooting ALTER TABLE, see http://dev.mysql.com/doc/refman/5.6/en/alter-table-problems.html. URL: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html
2.8.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 http://dev.mysql.com/doc/refman/5.6/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 Starting in MySQL 5.6.2, SELECT supports explicit partition selection using the PARTITION keyword 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 http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html. In MySQL 5.6.6 and later, 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. See http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-locki ng.html, for more information. 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 http://dev.mysql.com/doc/refman/5.6/en/expressions.html, and http://dev.mysql.com/doc/refman/5.6/en/functions.html. SELECT can also be used to retrieve rows computed without reference to any table. URL: http://dev.mysql.com/doc/refman/5.6/en/select.html
2.9.update语句
mysql> help update; Name: 'UPDATE' Description: Syntax: Single-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] Multiple-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [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. See http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html, for more information and examples. where_condition is an expression that evaluates to true for each row to be updated. For expression syntax, see http://dev.mysql.com/doc/refman/5.6/en/expressions.html. table_references and where_condition are specified as described in http://dev.mysql.com/doc/refman/5.6/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 keyword, 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 keyword, 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. URL: http://dev.mysql.com/doc/refman/5.6/en/update.html
2.10.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,...)] [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 http://dev.mysql.com/doc/refman/5.6/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 http://dev.mysql.com/doc/refman/5.6/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] Or: 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]. The speed of delete operations may also be affected by factors discussed in http://dev.mysql.com/doc/refman/5.6/en/delete-speed.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 Tables Beginning with MySQL 5.6.2, DELETE supports explicit partition selection using the PARTITION option, which takes a comma-separated list of the 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. See http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html, for more information and examples. URL: http://dev.mysql.com/doc/refman/5.6/en/delete.html
2.11.insert 语句
mysql> help insert ; Name: 'INSERT' Description: Syntax: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name,...)] [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] Or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name,...)] SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] Or: INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name,...)] [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] 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 ... SELECT is discussed further in [HELP INSERT SELECT]. In MySQL 5.6.2 and later, when inserting into a partitioned table, you can control which partitions and subpartitions accept new rows. The PARTITION option takes a comma-separated list of the 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. See http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html, for more information and examples. URL: http://dev.mysql.com/doc/refman/5.6/en/insert.html
2.12.create user语句
mysql> help create user; Name: 'CREATE USER' Description: Syntax: CREATE USER user_specification [, user_specification] ... user_specification: user [ identified_option ] auth_option: { IDENTIFIED BY 'auth_string' | IDENTIFIED BY PASSWORD 'hash_string' | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin AS 'hash_string' } The CREATE USER statement creates new MySQL accounts. An error occurs if you try to create an account that already exists. An account when first created has no privileges. To use CREATE USER, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. When the read_only system variable is enabled, CREATE USER additionally requires the SUPER privilege. For each account, CREATE USER creates a new row in the mysql.user table with no privileges and assigns the account an authentication plugin. Depending on the syntax used, CREATE USER may also assign the account a password. Each user_specification clause consists of an account name and information about how authentication occurs for clients that use the account. This part of CREATE USER syntax is shared with GRANT, so the description here applies to GRANT as well. Each account name uses the format described in http://dev.mysql.com/doc/refman/5.6/en/account-names.html. For example: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; If you specify only the user name part of the account name, a host name part of '%' is used. The server assigns an authentication plugin and password to each account as follows, depending on whether the user specification clause includes IDENTIFIED WITH to specify a plugin or IDENTIFIED BY to specify a password: o With IDENTIFIED WITH, the server assigns the specified plugin and the account has no password. If the optional AS 'hash_string' clause is also given, the string is stored as is in the authentication_string column (it is assumed to be already hashed in the format required by the plugin). o With IDENTIFIED BY, the server assigns the plugin implicitly and assigns the specified password. o With neither IDENTIFIED WITH nor IDENTIFIED BY, the server assigns the plugin implicitly and the account has no password. If the account has no password, the Password column in the account's mysql.user table row remains empty, which is insecure. To set the password, use SET PASSWORD. See [HELP SET PASSWORD]. For implicit authentication plugin assignment, the server uses these rules: o As of MySQL 5.6.6, the server assigns the default plugin to the account. This plugin becomes the value of the plugin column in the account's mysql.user table row. The default plugin is mysql_native_password unless the --default-authentication-plugin option is set otherwise at server startup. o Before MySQL 5.6.6, the server assigns no plugin to the account. The plugin column in the account's mysql.user table row remains empty. For client connections that use a given account, the server invokes the authentication plugin assigned to the account and the client must provide credentials as required by the authentication method that the plugin implements. If the server cannot find the plugin, either at account-creation time or connect time, an error occurs. If an account's mysql.user table row has a nonempty plugin column: o The server authenticates client connection attempts using the named plugin. o Changes to the account password using SET PASSWORD with PASSWORD() must be made with the old_passwords system variable set to the value required by the authentication plugin, so that PASSWORD() uses the appropriate password hashing method. If the plugin is mysql_old_password, the password can also be changed using SET PASSWORD with OLD_PASSWORD(), which uses pre-4.1 password hashing regardless of the value of old_passwords. If an account's mysql.user table row has an empty plugin column: o The server authenticates client connection attempts using the mysql_native_password or mysql_old_password authentication plugin, depending on the hash format of the password stored in the Password column. o Changes to the account password using SET PASSWORD can be made with PASSWORD(), with old_passwords set to 0 or 1 for 4.1 or pre-4.1 password hashing, respectively, or with OLD_PASSWORD(), which uses pre-4.1 password hashing regardless of the value of old_passwords. CREATE USER examples: o To specify an authentication plugin for an account, use IDENTIFIED WITH auth_plugin. The plugin name can be a quoted string literal or an unquoted name. 'auth_string' is an optional quoted string literal to pass to the plugin. The plugin interprets the meaning of the string, so its format is plugin specific and it is stored in the authentication_string column as given. (This value is meaningful only for plugins that use that column.) Consult the documentation for a given plugin for information about the authentication string values it accepts, if any. CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password; The server assigns the given authentication plugin to the account but no password. Clients must provide no password when they connect. However, an account with no password is insecure. To ensure that an account uses a specific authentication plugin and has a password with the corresponding hash format, specify the plugin explicitly with IDENTIFIED WITH, then use SET PASSWORD to set the password: CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password; SET old_passwords = 0; SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass'); Changes to the account password using SET PASSWORD with PASSWORD() must be made with the old_passwords system variable set to the value required by the account's authentication plugin, so that PASSWORD() uses the appropriate password hashing method. Therefore, to use the sha256_password or mysql_old_password plugin instead, name that plugin in the CREATE USER statement and set old_passwords to 2 or 1, respectively, before using SET PASSWORD. (Use of mysql_old_password is not recommended. It is deprecated and support for it will be removed in a future MySQL release.) o To specify a password for an account at account-creation time, use IDENTIFIED BY with the literal cleartext password value: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; The server assigns an authentication plugin to the account implicitly, as described previously, and assigns the given password. Clients must provide the given password when they connect. If the implicitly assigned plugin is mysql_native_password, the old_passwords system variable must be set to 0. Otherwise, CREATE USER does not hash the password in the format required by the plugin and an error occurs: mysql> SET old_passwords = 1; mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. mysql> SET old_passwords = 0; mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; Query OK, 0 rows affected (0.00 sec) o To avoid specifying the cleartext password if you know its hash value (the value that PASSWORD() would return for the password), specify the hash value preceded by the keyword PASSWORD: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689'; The server assigns an authentication plugin to the account implicitly, as described previously, and assigns the given password. The password hash must be in the format required by the assigned plugin. Clients must provide the password when they connect. o To enable the user to connect with no password, include no IDENTIFIED BY clause: CREATE USER 'jeffrey'@'localhost'; The server assigns an authentication plugin to the account implicitly, as described previously, but no password. Clients must provide no password when they connect. However, an account with no password is insecure. To avoid this, use SET PASSWORD to set the account password. As mentioned previously, implicit plugin assignment depends on the default authentication plugin. Permitted values of --default-authentication-plugin are mysql_native_plugin and sha256_password, but not mysql_old_password. This means it is not possible to set the default plugin so as to be able to create an account that uses mysql_old_password with CREATE USER ... IDENTIFIED BY syntax. To create an account that uses mysql_old_password, use CREATE USER ... IDENTIFIED WITH to name the plugin explicitly, then set the password: CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_old_password; SET old_passwords = 1; SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass'); However, the preceding procedure is not recommended because mysql_old_password is deprecated. For additional information about setting passwords and authentication plugins, see http://dev.mysql.com/doc/refman/5.6/en/assigning-passwords.html, and http://dev.mysql.com/doc/refman/5.6/en/pluggable-authentication.html. URL: http://dev.mysql.com/doc/refman/5.6/en/create-user.html
2.12.drop user语句
mysql> help drop user; Name: 'DROP USER' Description: Syntax: DROP USER user [, user] ... The DROP USER statement removes one or more MySQL accounts and their privileges. It removes privilege rows for the account from all grant tables. An error occurs for accounts that do not exist. To use DROP USER, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. When the read_only system variable is enabled, DROP USER additionally requires the SUPER privilege. Each account name uses the format described in http://dev.mysql.com/doc/refman/5.6/en/account-names.html. For example: DROP USER 'jeffrey'@'localhost'; If you specify only the user name part of the account name, a host name part of '%' is used. URL: http://dev.mysql.com/doc/refman/5.6/en/drop-user.html