Data Definition Language

CREATE statements

CREATE is used to create new tables, database, procedures, functions, triggers, views etc.

To keep it simple we will limit ourselves to just creating databases and tables. We will learn advance topic later.

• CREATE DATABASE

  It is used to create a new database.

  CREATE DATABASE db_name;
  

• CREATE TABLE

  It is used to create a new table.

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name (
  	column1 datatype,
  	column2 datatype,
  	...
  	columnN datatype
  );
  

DROP statements

DROP is used to delete tables, database, procedures, indexes, functions, triggers, views etc.

Lets just begin with dropping tables and databases.

• DROP TABLE

  It is used to delete a table.

  DROP [TEMPORARY] table_name 
  [IF EXISTS] table_name[,table_name1,...];
  

• DROP DATABASE

  It is used to delete a database.

  DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
  

ALTER

It is used to alter the structure of a table. It is used to add, delete and modify existing columns in a table. It can also be used to change or delete applied CONSTRAINTS in a table.

We will talk more about contraints later.

• Adding a column

  ALTER TABLE table_name ADD column_name datatype;
  

• Modifying a column

  ALTER TABLE table_name MODIFY COLUMN column_name datatype;
  

  MODIFY and CHANGE can be used interchangeably.

  MODIFY cannot rename a column but CHANGE can rename it.

  ALTER TABLE table_name CHANGE 
  old_column_name new_column_name datatype(length);
  

  While using CHANGE you always have to give old and new column names.

• Deleting a column

  ALTER TABLE table_name DROP COLUMN column_name;
  

RENAME

It is used to rename tables created earlier.

RENAME TABLE table_name TO new_table_name
    [, table_name2 TO new_table_name2] ...

TRUNCATE

It is used to delete the data of the table preserving its structure. After truncate command all the data will be deleted from the table but the fields(columns) will not be deleted.

TRUNCATE [TABLE] table_name;