SQL Syntax

• CREATE DATABASE syntax
• DROP DATABASE syntax
• CREATE TABLE syntax
• ALTER TABLE syntax
• DROP TABLE syntax
• OPTIMIZE TABLE syntax
• CHECK TABLE syntax
• ANALYZE TABLE syntax
• REPAIR TABLE syntax
• DELETE syntax
• SELECT syntax
• JOIN syntax
• INSERT syntax
• REPLACE syntax
• LOAD DATA INFILE syntax
• UPDATE syntax
• USE syntax
• FLUSH syntax (clearing caches)
• KILL syntax
• SHOW syntax (Get information about tables, columns,...)
• EXPLAIN syntax (Get information about a SELECT)
• DESCRIBE syntax (Get information about columns)
• BEGIN/COMMIT/ROLLBACK syntax
• LOCK TABLES/UNLOCK TABLES syntax
• SET [OPTION] SQL_VALUE_OPTION= value, ...
• GRANT and REVOKE syntax
• CREATE INDEX syntax
• DROP INDEX syntax
• Comment syntax

---

CREATE DATABASE syntax

CREATE DATABASE [IF NOT EXISTS] db_name

DROP DATABASE syntax

DROP DATABASE [IF EXISTS] db_name

CREATE TABLE syntax

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

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    [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 = {ISAM | MYISAM | HEAP}
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}
or      PASSWORD = "string"
or      DELAY_KEY_WRITE = {0 | 1}
or      ROW_FORMAT= { default | dynamic | static | compressed }
or      RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#;

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

ALTER TABLE syntax

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    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
  or    MODIFY [COLUMN] create_definition
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    RENAME [AS] new_tbl_name
  or    table_options

DROP TABLE syntax

DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]

OPTIMIZE TABLE syntax

OPTIMIZE TABLE tbl_name

CHECK TABLE syntax

CHECK TABLE tbl_name[,tbl_name...] [TYPE = [QUICK | FAST | EXTEND | CHANGED]]

ANALYZE TABLE syntax

ANALYZE TABLE tbl_name[,tbl_name...]

REPAIR TABLE syntax

REPAIR TABLE tbl_name[,tbl_name...] [TYPE = QUICK]

DELETE syntax

DELETE [LOW_PRIORITY] FROM tbl_name [WHERE where_definition] [LIMIT rows]

SELECT syntax

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
        [WHERE where_definition]
        [GROUP BY {unsigned_integer | col_name | formula}]
        [HAVING where_definition]
        [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
        [LIMIT [offset,] rows]
        [PROCEDURE procedure_name] ]

JOIN syntax

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference join_condition
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference LEFT [OUTER] JOIN table_reference
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }

Where table_reference is defined as table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)] and join_condition is defined as

ON conditional_expr |
USING (column_list)

INSERT syntax

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES (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, col_name=expression, ...

REPLACE syntax

    REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...)
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression,...

LOAD DATA INFILE syntax

LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 't']
        [OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\' ]]
    [LINES TERMINATED BY 'n']
    [IGNORE number LINES]
    [(col_name,...)]

UPDATE syntax

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

USE syntax

USE db_name

FLUSH syntax (clearing caches)

FLUSH flush_option [,flush_option]

Where flush_option can be:

• HOSTS
• LOGS
• PRIVILEGES
• TABLES
• TABLES WITH READ LOCK
• STATUS.

KILL syntax

KILL thread_id

SHOW syntax (Get information about tables, columns,...)

   SHOW DATABASES [LIKE wild]
or SHOW TABLES [FROM db_name] [LIKE wild]
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW STATUS [LIKE wild]
or SHOW VARIABLES [LIKE wild]
or SHOW [FULL] PROCESSLIST
or SHOW GRANTS FOR user

EXPLAIN syntax (Get information about a SELECT)

    EXPLAIN tbl_name
or  EXPLAIN SELECT select_options

DESCRIBE syntax (Get information about columns)

{DESCRIBE | DESC} tbl_name {col_name | wild}

BEGIN/COMMIT/ROLLBACK syntax

BEGIN
COMMIT
ROLLBACK

LOCK TABLES/UNLOCK TABLES syntax

LOCK TABLES tbl_name [AS alias] {READ | [READ LOCAL] | [LOW_PRIORITY] WRITE}
            [, tbl_name {READ | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES

SET [OPTION] SQL_VALUE_OPTION= value, ...

SET [OPTION] SQL_VALUE_OPTION= value, ...
CHARACTER SET character_set_name | DEFAULT 
PASSWORD = PASSWORD('some password')
PASSWORD FOR user = PASSWORD('some password')
SQL_AUTO_IS_NULL = 0 | 1
SET AUTOCOMMIT= 0 | 1
SQL_BIG_TABLES = 0 | 1
SQL_BIG_SELECTS = 0 | 1
SQL_BUFFER_RESULT = 0 | 1
SQL_LOW_PRIORITY_UPDATES = 0 | 1
SQL_MAX_JOIN_SIZE = value | DEFAULT
SQL_SAFE_MODE = 0 | 1
SQL_SELECT_LIMIT = value | DEFAULT
SQL_LOG_OFF = 0 | 1
SQL_LOG_UPDATE = 0 | 1
TIMESTAMP = timestamp_value | DEFAULT
LAST_INSERT_ID = #
INSERT_ID = #

GRANT and REVOKE syntax

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY 'password']
        [, user_name [IDENTIFIED BY 'password'] ...]
    [WITH GRANT OPTION]

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

Where priv_type can be one of the following:

• ALL PRIVILEGES
• FILE
• RELOAD
• ALTER
• INDEX
• SELECT
• CREATE
• INSERT
• SHUTDOWN
• DELETE
• PROCESS
• UPDATE
• DROP
• REFERENCES
• USAGE

CREATE INDEX syntax

CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length)],... )

DROP INDEX syntax

DROP INDEX index_name ON tbl_name

Comment syntax

The MySQL server supports the # to end of line, -- to end of line and /* in-line or multiple-line */ comment styles.

---