Contents from the laboratory building: MySQL Basic Course 

1. Data Definition Statement:

1. alter table:

   ALTER [IGNORE] TABLE tbl_name
       alter_specification [, alter_specification] ...
   
   alter_specification:
       ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
     | ADD [COLUMN] (column_definition,...)
     | ADD INDEX [index_name] [index_type] (index_col_name,...)
     | ADD [CONSTRAINT [symbol]]
           PRIMARY KEY [index_type] (index_col_name,...)
     | ADD [CONSTRAINT [symbol]]
           UNIQUE [index_name] [index_type] (index_col_name,...)
     | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
     | 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 column_definition
           [FIRST|AFTER col_name]
     | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
     | DROP [COLUMN] col_name
     | DROP PRIMARY KEY
     | DROP INDEX index_name
     | DROP FOREIGN KEY fk_symbol
     | DISABLE KEYS
     | ENABLE KEYS
     | RENAME [TO] new_tbl_name
     | ORDER BY col_name
     | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
     | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
     | DISCARD TABLESPACE
     | IMPORT TABLESPACE
     | table_options
     | partition_options
     | ADD PARTITION partition_definition
     | DROP PARTITION partition_names
     | COALESCE PARTITION number
     | REORGANIZE PARTITION partition_names INTO (partition_definitions)
     | ANALYZE PARTITION partition_names
     | CHECK PARTITION partition_names
     | OPTIMIZE PARTITION partition_names
     | REBUILD PARTITION partition_names
     | REPAIR PARTITION partition_names

    

2. create database:

   CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
       [create_specification [, create_specification] ...]
   
   create_specification:
       [DEFAULT] CHARACTER SET charset_name
     | [DEFAULT] COLLATE collation_name

    

3. create index:

   CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
       [USING index_type]
       ON tbl_name (index_col_name,...)
   
   index_col_name:
       col_name [(length)] [ASC | DESC]
   CREATE INDEX Mapped to a ALTER TABLE Statement to create an index.

   The following statement creates the index part_of_name of the first ten characters of the name column in the customer table:
   CREATE INDEX part_of_name ON customer (name(10));
   If the first 10 characters in a column are indexed, the indexing speed should not be slower than that of the entire name column. In addition, indexing with column prefixes can make indexed files smaller, which can save a lot of disk space and accelerate INSERT operations.

4. create table:

   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_tbl_name [)];
   
   create_definition:
       column_definition
     | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
     | KEY [index_name] [index_type] (index_col_name,...)
     | INDEX [index_name] [index_type] (index_col_name,...)
     | [CONSTRAINT [symbol]] UNIQUE [INDEX]
           [index_name] [index_type] (index_col_name,...)
     | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
     | [CONSTRAINT [symbol]] FOREIGN KEY
           [index_name] (index_col_name,...) [reference_definition]
     | CHECK (expr)
   
   column_definition:
       col_name type [NOT NULL | NULL] [DEFAULT default_value]
           [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
           [COMMENT 'string'] [reference_definition]
   
   type:
       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
     | TIMESTAMP
     | DATETIME
     | CHAR(length) [BINARY | ASCII | UNICODE]
     | VARCHAR(length) [BINARY]
     | TINYBLOB
     | BLOB
     | MEDIUMBLOB
     | LONGBLOB
     | TINYTEXT [BINARY]
     | TEXT [BINARY]
     | MEDIUMTEXT [BINARY]
     | LONGTEXT [BINARY]
     | ENUM(value1,value2,value3,...)
     | SET(value1,value2,value3,...)
     | spatial_type
   
   index_col_name:
       col_name [(length)] [ASC | DESC]
   
   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|TYPE} = engine_name
     | AUTO_INCREMENT = value
     | AVG_ROW_LENGTH = value
     | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
     | CHECKSUM = {0 | 1}
     | COMMENT = 'string'
     | CONNECTION = 'connect_string'
     | MAX_ROWS = value
     | MIN_ROWS = value
     | PACK_KEYS = {0 | 1 | DEFAULT}
     | PASSWORD = 'string'
     | DELAY_KEY_WRITE = {0 | 1}
     | ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
     | UNION = (tbl_name[,tbl_name]...)
     | INSERT_METHOD = { NO | FIRST | LAST }
     | DATA DIRECTORY = 'absolute path to directory'
     | INDEX DIRECTORY = 'absolute path to directory'
   
   partition_options:
       PARTITION BY
              [LINEAR] HASH(expr)
           |  [LINEAR] KEY(column_list)
           |  RANGE(expr)
           |  LIST(column_list)
       [PARTITIONS num]
       [  SUBPARTITION BY
              [LINEAR] HASH(expr)
            | [LINEAR] KEY(column_list)
         [SUBPARTITIONS(num)]
       ]
       [(partition_definition), [(partition_definition)], ...]
   
   partition_definition:
       PARTITION partition_name
           [VALUES {
                     LESS THAN (expr) | 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]
   
   select_statement:
       [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

   Copy the table (create another empty table with the same column attributes and index):
   CREATE TABLE new_tbl LIKE orig_tbl;
   Completely clone the content and structure of a table:
   CREATE TABLE new_tbl SELECT * FROM orig_tbl;

5. DROP DATABASE: DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

6. DROP INDEX: DROP INDEX index_name ON tbl_name

7. DROP TABLE: DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]​​​​​​​

8. RENAME TABLE: RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

2. Data Operation Statement:

1. DELETE grammar:
   Single Form Grammar:

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

   Multi-table grammar:

   DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       tbl_name[.*] [, tbl_name[.*] ...]
       FROM table_references
       [WHERE where_definition]
   
   or: 
   
   DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM tbl_name[.*] [, tbl_name[.*] ...]
       USING table_references
       [WHERE where_definition]

   When you don't want to know the number of rows deleted, there's a faster way, even with TRUNCATE TABLE.
   If the DELETE statement contains the ORDER BY clause, then deleting rows in the order specified in the clause, combined with the LIMIT statement, will be very advantageous:
   DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;