MySQL - Commonly used SQL commands

Most commonly used MySQL specific SQL Commands - Now I don't have to google for them every time I forget the syntax.


GRANT

The GRANT statement enables system administrators to create MySQL user accounts and to grant rights to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. The REVOKE statement is related and enables administrators to remove account privileges. To determine what privileges an account has, use SHOW GRANTS.

GRANT

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

TO user [IDENTIFIED BY [PASSWORD] 'password']

  [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]

[WITH with_option [with_option] ...]



object_type:

TABLE

| FUNCTION

| PROCEDURE



priv_level:

*

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name



with_option:

GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count



ssl_option:

SSL

| X509

| CIPHER 'cipher'

| ISSUER 'issuer'

| SUBJECT 'subject'

REVOKE

SHOW VARIABLES

SHOW [GLOBAL | SESSION] VARIABLES

 [LIKE 'pattern' | WHERE expr]

SHOW VARIABLES shows the values of MySQL system variables. This information also can be obtained using mysqladmin command. The LIKE clause, if present, indicates which variable names to match. The WHERE clause can be given to select rows using more general conditions.

With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. With SESSION, it displays the values that are in effect for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

SHOW STATUS
SHOW STATUS provides server status information. The LIKE clause, if present, indicates which variable names to match

SHOW [GLOBAL | SESSION] STATUS

    [LIKE 'pattern' | WHERE expr]

With a LIKE clause, the statement displays only rows for those variables with names that match the pattern:

mysql> SHOW STATUS LIKE 'Key%';

+--------------------+----------+

| Variable_name      | Value    |

+--------------------+----------+

| Key_blocks_used    | 14955    |

| Key_read_requests  | 96854827 |

| Key_reads          | 162040   |

| Key_write_requests | 7589728  |

| Key_writes         | 3813196  |

+--------------------+----------+

SHOW SLAVE STATUS



This statement provides status information on essential parameters of the slave  threads.

If you issue this statement using  the `mysql` client, you can use a \G  statement terminator

rather than a semicolon to obtain a more readable vertical  layout:



mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

 Slave_IO_State: Waiting for master to send event

    Master_Host: localhost

    Master_User: root

    Master_Port: 3306

  Connect_Retry: 3





SHOW BINARY LOGS / SHOW MASTER LOGS



Lists the binary log files on the server



mysql> SHOW BINARY LOGS;

+---------------+-----------+

| Log_name      | File_size |

+---------------+-----------+

| binlog.000015 |    724935 |

| binlog.000016 |    733481 |

+---------------+-----------+





SHOW [STORAGE] ENGINES



SHOW ENGINES displays status information about the server's storage engines.

This is particularly useful for checking whether a storage engine is

supported, or to see what the default engine is. 

SHOW TABLE TYPES is a deprecated synonym.





mysql> SHOW ENGINES\G

*************************** 1. row ***************************

Engine: MyISAM

Support: DEFAULT

Comment: Default engine as of MySQL 3.23 with great performance

*************************** 2. row ***************************

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

*************************** 3. row ***************************

Engine: HEAP

Support: YES

Comment: Alias for MEMORY



--



*************************** 4. row ***************************

Engine: InnoDB

Support: YES

SHOW ERRORS

SHOW ERRORS [LIMIT [offset,] row_count]

SHOW COUNT(*) ERRORS

This statement is similar to SHOW WARNINGS, except that instead of displaying errors, warnings, and notes, it displays only errors.

SHOW WARNINGS

SHOW WARNINGS [LIMIT [offset,] row_count]

SHOW COUNT(*) WARNINGS

show warnings shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session. It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list.

Warnings are generated for DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE.

SHOW many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following:

SHOW CHARACTER SET [like_or_where]

SHOW COLLATION [like_or_where]

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]

SHOW CREATE DATABASE db_name

SHOW CREATE FUNCTION func_name

SHOW CREATE PROCEDURE proc_name

SHOW CREATE TABLE tbl_name

SHOW DATABASES [like_or_where]

SHOW ENGINE engine_name {LOGS | STATUS }

SHOW [STORAGE] ENGINES

SHOW ERRORS [LIMIT [offset,] row_count]

SHOW FUNCTION CODE func_name

SHOW FUNCTION STATUS [like_or_where]

SHOW GRANTS FOR user

SHOW INDEX FROM tbl_name [FROM db_name]

SHOW INNODB STATUS

SHOW PROCEDURE CODE proc_name

SHOW PROCEDURE STATUS [like_or_where]

SHOW [BDB] LOGS

SHOW MUTEX STATUS

SHOW OPEN TABLES [FROM db_name] [like_or_where]

SHOW PRIVILEGES

SHOW [FULL] PROCESSLIST

SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]

SHOW PROFILES

SHOW [GLOBAL | SESSION] STATUS [like_or_where]

SHOW TABLE STATUS [FROM db_name] [like_or_where]

SHOW TABLES [FROM db_name] [like_or_where]

SHOW TRIGGERS [FROM db_name] [like_or_where]

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

SHOW WARNINGS [LIMIT [offset,] row_count]



like_or_where:

LIKE 'pattern'

| WHERE expr





ALTER TABLE

ALTER [IGNORE] TABLE tbl_name

    alter_specification [, alter_specification] ...



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_type]

| ADD [CONSTRAINT [symbol]] PRIMARY KEY

  [index_type] (index_col_name,...) [index_type]

| ADD [CONSTRAINT [symbol]]

  UNIQUE [INDEX|KEY] [index_name]

  [index_type] (index_col_name,...) [index_type]

| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]

  (index_col_name,...) [index_type]

| ADD [CONSTRAINT [symbol]]

  FOREIGN KEY [index_name] (index_col_name,...)

  reference_definition

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

| CHANGE [COLUMN] old_col_name new_col_name column_definition

  [FIRST|AFTER col_name]

| 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] 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



index_col_name:

col_name [(length)] [ASC | DESC]



index_type:

USING {BTREE | HASH | RTREE}



table_options:

table_option [[,] table_option] ...





To be continued...