mysqlをコンソールで操作していて、
alter文などの構文を忘れてしまう時ってありますよね？
その度にgoogleで「mysql alter」などと打って調べるのはとても面倒です。
僕はmysqlにhelpコマンドがあることを知らずに、日々コマンドを忘れてググっていました。。
実際helpコマンドを使ってみるとググるよりずっと作業が早かったです。
helpの使い方は下の二通りです。
mysql> help alter
または
mysql> ? alter
どちらも同じ結果が表示されます。
実際にコマンドを打つと下のような結果になります。
mysql> ? alter 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: ALTER DATABASE ALTER EVENT ALTER FUNCTION ALTER PROCEDURE ALTER SERVER ALTER TABLE ALTER USER ALTER VIEW GRANT SPATIAL
alterで調べた場合沢山のtopicの候補がでてきました。
なので、この候補の中から自分の知りたい情報をさらにhelpコマンドで打ちます。
今回は候補の中からALTER TABLEについて調べてみます。
mysql> help ALTER TABLE
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER [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
| RENAME {INDEX|KEY} old_index_name TO new_index_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
| {WITHOUT|WITH} VALIDATION
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| DISCARD PARTITION {partition_names | ALL} TABLESPACE
| IMPORT PARTITION {partition_names | ALL} TABLESPACE
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
| 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
| UPGRADE 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.
partition_options signifies options that can be used with partitioned
tables for repartitioning, for adding, dropping, discarding, importing,
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, DISCARD PARTITION, IMPORT 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.7/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.7/en/partitioning-management-exchange
.html.
Prior to MySQL 5.7.6, partitioned InnoDB tables used the generic
ha_partition partitioning handler employed by MyISAM and other storage
engines not supplying their own partitioning handlers; in MySQL 5.7.6
and later, such tables are created using the InnoDB storage engine's
own (or "native") partitioning handler. Beginning with MySQL 5.7.9, you
can upgrade an InnoDB table that was created in MySQL 5.7.6 or earlier
(that is, created using ha_partition) to the InnoDB native partition
handler using ALTER TABLE ... UPGRADE PARTITIONING. (Bug #76734, Bug
#20727344) This version of ALTER TABLE does not accept any other
options and can be used only on a single table at a time.
*Note*: You can also use mysql_upgrade in MySQL 5.7.9 or later to
upgrade older partitioned InnoDB tables to the native partitioning
handler.
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.7/en/alter-table-problems.html.
URL: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
長いですが、一通りalter table時の構文が確認できます。
この他にも例えば関数の使い方を調べられたり、
mysql> help concat
Name: 'CONCAT'
Description:
Syntax:
CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. May
have one or more arguments. If all arguments are nonbinary strings, the
result is a nonbinary string. If the arguments include any binary
strings, the result is a binary string. A numeric argument is converted
to its equivalent nonbinary string form.
CONCAT() returns NULL if any argument is NULL.
URL: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html
Examples:
mysql> SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
-> NULL
mysql> SELECT CONCAT(14.3);
-> '14.3'
とても便利なので、これからガンガン使っていこうと思います。
Tags: mysql