MySQL Table Ins & Outs

MySQL Table Ins & Outs

MySQL CREATE TABLE

To create a new table into database we have to use CREATE TABLE command. A table requires three things: 1. Name of the table, 2. Names of fields, 3. Definitions for each field.

Syntax:

    CREATE TABLE table_name (column_name column_type...);   

Example:

    CREATE TABLE cus_tbl(  
cus_id INT NOT NULL AUTO_INCREMENT,
cus_firstname VARCHAR(100) NOT NULL,
cus_surname VARCHAR(100) NOT NULL,
PRIMARY KEY ( cus_id )
);

MySQL ALTER Table

When we want to change our table name or any table field, then we have to use ALTER statement. To add or delete an existing column in a table, ALTER statement also works there. ALTER statement always used with “ADD“, “DROP” and “MODIFY” commands.

1) ADD a column in the table

Syntax:

ALTER TABLE table_name  
ADD new_column_name column_definition
[ FIRST | AFTER column_name ];

Here column_definition specifies the data type of the column (NULL or NOT NULL, etc). And FIRST | AFTER column_name is optional which tells where in the table to create the column. If this parameter is not specified, the new column will be added end of the table.

Example:

    ALTER TABLE cus_tbl  
ADD cus_age varchar(40) NOT NULL;

2) Add multiple columns in the table

Syntax:

     ALTER TABLE table_name  
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
... … …
;

Example:

    ALTER TABLE cus_tbl  
ADD cus_address varchar(100) NOT NULL
AFTER cus_surname,
ADD cus_salary int(100) NOT NULL
AFTER cus_age ;

3) MODIFY column in the table

Syntax:

    ALTER TABLE table_name  
MODIFY column_name column_definition
[ FIRST | AFTER column_name ];

Example:

    ALTER TABLE cus_tbl  
MODIFY cus_surname varchar(50) NULL;

4) DROP column in table

Syntax:

    ALTER TABLE table_name  
DROP COLUMN column_name;

Example:

    ALTER TABLE cus_tbl  
DROP COLUMN cus_address;

5) RENAME column in table

Syntax:

    ALTER TABLE table_name  
CHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ]

Example:

     ALTER TABLE  cus_tbl  
CHANGE COLUMN cus_surname cus_title
varchar(20) NOT NULL;

6) RENAME table

Syntax:

    ALTER TABLE table_name  
RENAME TO new_table_name;

Example:

ALTER TABLE cus_tbl  
RENAME TO cus_table;

MySQL TRUNCATE Table

To remove a table’s complete data without removing its structure, we have to use TRUNCATE statement.
Syntax:

    TRUNCATE TABLE  table_name;  

Example:

    TRUNCATE TABLE  cus_tbl;  

MySQL DROP Table

To remove complete data of a table with structure, we have to use DROP table statement.
Syntax:

    DROP TABLE  table_name;  

Example:

    DROP TABLE  cus_tbl;  
Post a Comment