Select database

USE database;

Create table

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type,
    ...
);

View table

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

View table structure

SHOW COLUMNS FROM tbl_name;

insert record

INSERT [INTO] tbl_name [(col_name, ...)] VALUES(val, ...);

• Whether the field can be empty: NULL or NOT NULL
• Auto increment
• PRIMARY KEY: PRIMARY KEY
• Uniqueness: UNIQUE KEY
• DEFAULT: DEFAULT
• Foreign key: foreign key (column name) references TBL name (column name) [on delete rule];
  
  • CASCADE: delete or update from parent table automatically delete or update rows in child table
  • SET NULL: update or delete from the parent table. Set the child table NULL automatically. The child table must have no NOT NULL
  • RESTRICT: refuse to update or delete parent table
  • NO ACTION: refuse to update or delete the parent table

Query record

SELECT expr, ... FROM tbl_name;

View index

SHOW INDEXS FROM tbl_name[\G];

Modify data table

• Add single column

  ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];
  

• Add multiple columns

  ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition, ...);
  

• Delete column

  ALTER TABLE tbl_name DROP [COLUMN] col_name;
  

• Add primary key constraint

  ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...);
  

• Add unique constraint

  ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name, ...);
  

• Add foreign key constraint

  ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition;
  

• Add or remove default constraints

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

• Delete primary key constraint

  ALTER TABLE tbl_name DROP PRIMARY KEY;
  

• Delete unique constraint

  ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
  

• Delete foreign key constraint

  ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
  

• Modify column definition

  ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];
  

• Modify column name

  ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];
  

• Modify data table

  ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
  
  RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...;