MySQL CREATE, DROP AND ALTER TABLE

  



CREATE TABLE



The CREATE TABLE statement is used to create a new table. 

SYNTAX:



CREATE  TABLE  table_name (column1_name datatype, 

column2_name datatype, 

column3_name datatype);



EXAMPLE:


The following SQL statement creates a Table called "student_details"  That contains four columns like student_id, name, father_name, address. 



CREATE  TABLE students_details (student_id int, name nvarchar(200), 

father_name nvarchar(200), addredd nvarchar(200)); 


The student_id will hold an interger value

Remaining Three columns will hold an characters and the minimum length for ther fields 200 characters



DROP TABLE



The DROP TABLE Statement is used to delete a existing Table in a database

SYNTAX:


 

DROP  TABLE table_name;



EXAMPLE:


The following SQL statement drop a table called "students_details":



DROP  TABLE  students_details;





TRUNCATE TABLE



The TRUNCATE TABLE statement is used to delete the date inside a table 

SYNTAX:


 

TRUNCATE  TABLE table_name;





ALTER TABLE



The ALTER TABLE statememnt used to add, delete or mofify columns in an existing table.  


ADD COLUMN

SYNTAX:


 

ALTER  TABLE  table_name ADD column_name datatype;



EXAMPLE:


The following SQL statement add a table column called "students_email":



ALTER  TABLE  student_details ADD (students_email nvarchar(200));



DROP COLUMN

SYNTAX:


 

ALTER  TABLE  table_name DROP COLUMN column_name;



EXAMPLE:


The following SQL statement drop a table column called "students_email":



ALTER  TABLE  student_details DROP  COLUMN students_email;



RENAME COLUMN

SYNTAX:



ALTER TABLE table_name RENAME COLUMN column_oldname to column_newname;



EXAMPLE:


The following SQL statement rename a table column called "students_address to students_lastname":



ALTER TABLE student_details RENAME COLUMN students_address to students_lastname;