MySQL Commands

I see and I remember

From the Chinese proverb:

I hear and I forget

I see and I remember

I do and I understand.

This is work in progress; I copy snippets to this page from time to time.

Up-to-date documentation is at The MySQL website.

Tip

The mysql command line client and MySQL Control Center both support name completion: Just type <tab> after entering the first characters of your table or column name.

You might need to type rehash (re-)enable the completion.

ALTER TABLE `users` ADD PRIMARY KEY (`name`);
ALTER TABLE `users` MODIFY `password` VARCHAR(64) BINARY NOT NULL DEFAULT "";
ALTER TABLE `mob_object` ADD UNIQUE (`brand`, `model`);
ALTER TABLE `mob_tag` CONVERT TO CHARACTER SET utf8;

/* Create a local user: */
GRANT USAGE ON *.* TO 'michelangelo'@'localhost' IDENTIFIED BY 'michelangelo'; 
GRANT SELECT, INSERT, UPDATE, DELETE ON `carthago`.* TO 'michelangelo'@'localhost';
GRANT CREATE, INDEX, LOCK TABLES, RELOAD, DROP ON `carthago`.* TO 'michelangelo'@'localhost';
GRANT FILE ON `carthago`.* TO 'scipio'@'localhost';
GRANT DELETE ON carthago.* TO caesar;
FLUSH PRIVILEGES; 

CREATE TABLE `bookreview` (
  `isbn` varchar(64) NOT NULL,
  `author` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `publisher` varchar(255) NOT NULL default '',
  `review_title` varchar(255) NOT NULL default '',
  `review_text` mediumtext NOT NULL,
  `review_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`isbn`),
  KEY `last_modified` (`last_modified`),
  KEY `review_date` (`review_date`),
) TYPE=MyISAM;
 

MySQL Definitions

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

or CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (LIKE old_table_name);

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
  or    FULLTEXT [INDEX] [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  or    CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  or    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  or    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  or    INT[(length)] [UNSIGNED] [ZEROFILL]
  or    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  or    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  or    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  or    NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  or    CHAR(length) [BINARY]
  or    VARCHAR(length) [BINARY]
  or    DATE
  or    TIME
  or    TIMESTAMP
  or    DATETIME
  or    TINYBLOB
  or    BLOB
  or    MEDIUMBLOB
  or    LONGBLOB
  or    TINYTEXT
  or    TEXT
  or    MEDIUMTEXT
  or    LONGTEXT
  or    ENUM(value1,value2,value3,...)
  or    SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
	TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
or	AUTO_INCREMENT = #
or	AVG_ROW_LENGTH = #
or	CHECKSUM = {0 | 1}
or	COMMENT = "string"
or	MAX_ROWS = #
or	MIN_ROWS = #
or	PACK_KEYS = {0 | 1 | DEFAULT}
or	PASSWORD = "string"
or	DELAY_KEY_WRITE = {0 | 1}
or      ROW_FORMAT= { default | dynamic | fixed | compressed }
or	RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
or	UNION = (table_name,[table_name...])
or	INSERT_METHOD= {NO | FIRST | LAST }
or      DATA DIRECTORY="absolute path to directory"
or      INDEX DIRECTORY="absolute path to directory"

select_statement:
	[IGNORE | REPLACE] SELECT ...  (Some legal select statement)
 

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD [COLUMN] (create_definition, create_definition,...)
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ADD FULLTEXT [index_name] (index_col_name,...)
  or	ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
               [FIRST | AFTER column_name]
  or    MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    DISABLE KEYS
  or    ENABLE KEYS
  or    RENAME [TO] new_tbl_name
  or    ORDER BY col
  or    table_options 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT row_count]

or: DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       table_name[.*] [, table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]

or: DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM table_name[.*] [, table_name[.*] ...]
       USING table-references
       [WHERE where_definition]
      

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        5A
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=(expression | DEFAULT), ...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ] 
      

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

or: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
 

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY [PASSWORD] 'password']
        [, user_name [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
        MAX_UPDATES_PER_HOUR # |
        MAX_CONNECTIONS_PER_HOUR #]] 

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name] ... 

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name [, user_name] ... 

Privileges:

ALL [PRIVILEGES]
ALTER
CREATE
CREATE TEMPORARY TABLES
DELETE
DROP
EXECUTE
FILE
INDEX
INSERT
LOCK TABLES
PROCESS
REFERENCES
RELOAD
REPLICATION CLIENT
REPLICATION SLAVE
SELECT
SHOW DATABASES
SHUTDOWN
SUPER
UPDATE
USAGE
GRANT OPTION