MYSQL  DROP  to remove Column,Table,DataBase

We have seen how to remove records from a table by using  truncate sql command. Now we will learn how to use DROP query command to delete a table or a field or some other properties associated with the table. Let us start with deleting a field by DROP command.

Deleting a Colunm of a Table

ALTER TABLE  USER  DROP  UserType ;

Here user is our table name and usertype is one of the field of this table. By this command we can delete the field usertype  of the table User.

Deleting a Table

We can use DROP command to remove a table inside the database. Now here is the code to delete the table User.

DROP  TABLE  USER  

Checking before deleting

We can check whether table is there or not before giving any delete command. Without ensuring the presence of table ( non existence table ) delete command will generate an error message.

DROP TABLE IF EXISTS  USER  ;

Deleting multiple tables

We can use drop command to delete more than one table. Here is the command to remove 4 tables.

DROP TABLE  User,New_User,City,State;

The above command will delete four tables.

DROP ING DATABASE

DROP  DATABASE  USER _MNG

Read more ...

MYSQL COPY TABLE Command

We can copy a table structure and records to another new table. The CREATE TABLE command will create a table with same structure of the old table and add all the records. To export data to an existing table you can use insert command.

create table  New_users  SELECT * FROM users

Copy table structure only

WE can copy only structure and create a new table like this.

create table New_users like users

Create table if not exists

Note that all the above quires will return error if the table is already exist, so to prevent this error message we can add the command IF NOT EXISTS to the query.

create table  New_users  SELECT * FROM users WHERE UserType=’Admin’

Here the table will be created only if the table is not there before.

What we will do if we want to delete the old table and create a new table ?

DROP TABLE IF EXISTS

Some time we may not be sure if the table exists or not so we can drop the table if exist by adding one more query before creating the table. Here it is

DROP TABLE IF EXISTS ` New_users `;

The advantage of the above command over using a simple drop table command is here no error message saying unknown table is generated even if the table is not there.

Copy with extra features like auto_increment

To copy the extra features of the column like auto_increment , unique etc we have to use like this

create table  New_users  (recID INT(3) auto_increment primary key) SELECT * student.name,student. UserType, student.Password FROM users

Read more ...