Comprehensive notes DDL commands in MySQL IP class 11

In this article, you will learn about DDL commands in MySQL IP class 11 topic. In the previous article, we have seen DML commands. Follow the below given to read the article, if you missed it!

DDL commands in MySQL IP class 11

As you are familiar with creating a database and create a table which is one of the DDL commands of MySQL. Some other DDL commands of MySQL given in your CBSE curriculum are :

1. Alter
2. Drop

So let us start the article DDL commands in MySQL IP class 11.

The alter table command

The alter table command is used to add or remove column or constraints in the table as well as if you want to modify the table structure then also it can be helpful. After creating a table if you wish do any changes like add a column, modify the table data type of size , remove column or constraints etc. the alter table command is useful.

The alter table command syntax is as following:

alter table <table_name> add/modify/drop column/constraint,...

Add a column with alter table command

First of all, you will learn how to add columns with alter table command. Sometimes while working you need to add a column after table creation. So this command will be helpful. Just observe the following syntax:

alter table <table_name> add column <column_specification>

As you have seen it is a very simple command of MySQL. Starts with alter table followed by <tablename> and add column keyword. Now see the below given example, I have added a column into stduents table.

alter table students add column remarks varchar(20);

I have added a new column remarks with varchar datatype. Observe the screeshot.

Add multiple columns together

You can add a number of columns by separating them with commas. Observe this command:

alter table students add column (pre_school varchar(40), TC char(1));

Now have a look at this screenshot:

Add constraints

You can add constraints also in similar way you have added columns:

alter table students add primary key (rollno);

Here I have used only one example for the constraint but you can use any constraints.

Modify the structure of the table

You can modify the column structure and change the data type or size anytime. The syntax for modify is:

alter table <table_name> modify column <column_name column_structure>;

Now see the example for modify the column structure.

alter table students modify column email varchar(35);

Rename column in MySQL

You can use alter table command to rename columns as well. The syntax will be as:

alter table <table_name> rename column <old_column> to <new_column>;

Observe this example:

alter table students rename column email to email_id;

This will be something like this:

You can also use alter table change column command to rename a column. Just have a look at the following command:

 alter table students change column pre_school previous_school varchar(35);

Alter table drop column

If you want to remove any column from the table the alter table drop column is used. The general form of this command is:

alter table <table_name> drop column/constraint <column_name/csontraint_name>;

Now observe the following example:

alter table students drop column email_id;

The command in MySQL is something like this:

Drop constraints

You can remove any constraints from the table using alter table drop command. Observe this example:

 alter table stduents drop primary key;

Drop Command

The drop command is used to remove the table or database. The syntax is like:

drop table/database <table_name/database_name>;

Have a look at the following example:

drop table students;

That’s all from the DDL commands in mysql ip class 11. Follow the below-given link to read the NCERT solution for mysql part.

If you have any doubt or query regarding this article DDL commands in MySQL IP class 11, feel free to ask in the comment section.

Thank you reading this article.