The main object of this lab is to be familiar with the DML(Data Manipulation Language)

DBMS LAB 4

The main object of this lab is to be familiar with the DML(Data Manipulation Language)

Deleting Records

            To delete a record MySql uses the DELETE statement. It is similar to SELECT, except that as the entire record is deleted, there is no need to specify any columns. You need the table name and condition.

The general syntax of the DELETE command is

msql> DELETE from table_name where condition;

Example:

mysql > DELETE from employee_data where [condition]

NOTE: condition is optional.If condition is not given all the data from the table are deleted. So be careful to use a condition with the DELETE statements.Simply entering DELETE from table_name would have deleted all records in the table. There is no undo option

Changing Records in Table

UPDATE statement is used to update the records in table. The general syntax for the UPDATE statement is

mysql>UPDATE table_name SET column_name=value where condition;

In this statement the column_name is the name of the column to be updated and the value is the new value that is given to the column

Example:

mysql> UPDATE employee_data SET f_name=”Rahul” where emp_id=10;

NOTE: We have to be very careful to apply a condition. Without the where clause, we would have changed everybody’s name to Rahul.

INSERT, SELECT, UPDATE and DELETE make up the four standard statements for manipulating data. They are part of SQL’s data manipulation Language (DML)

Dropping Tables and Databases

To remove or drop the table and database we use the DROP command. The general syntax for the drop statement is

mysql> DROP table_name;

NOTE: No warning, no notification, the table and all the data in it has been dropped. So we have to be very careful with this statement

Similarly to drop the database we can issue the following command

mysql> drop database_name;

Changing Table Structure

The DDL statement ALTER allows us to change the structure of tables. We can add columns, change column definition, rename tables and drop columns.

1)      Adding a new column in the existing table

   

      To add a new column in the existing table we use the ALTER command. The general syntax of ALTER command for adding new column in the existing table is

mysql> alter table table_name ADD new_column_name  column_type;

In the above command the new_column_name is the name of the new column that we are going to add in the table table_name and the column type is the type of the column like int, varchar,float etc

Example : mysql>AlTER TABLE employee_data ADD phone_no int;

In this example the phone_no is the name of the new column that we are going to add in the table employee_data and the type of that column is int

2)      Changing the Column Defination

  We can change the name of the existing column of a table with the help of ALTER command using the CHANGE clause. The general syntax of  ALTER command for changing the name of the column is:

mysql>ALTER TABLE table_name CHANGE old_column_name new_column_name column_type;

After the CNAGE clause comes the old column name, then the new column name, followed by it’s definition. To change the definition but not the name of column, we would simply keep then name as before followed by the column type.

mysql>ALTER TABLE table_name CHANGE old_column_name new_column_type;

Example: mysql> ALTER TABLE employee_data CHANGE emp_id employee_number int;

3)      Renaming a Table

 To rename an existing table we use ALTER TABLE command with RENAME clause

mysql>ALTER TABLE old_table_name RENAME new_table_name;

The above command can also be written as

mysql>ALTER TABLE old_table_name RENAME TO new_table_name;

or

mysql>ALTER TABLE RENAME old_table_name TO new_table_name;

Example

 mysql>ALTER TABLE employee_data RENAME emp_table;

4)      Dropping a Column

    To remove the unwanted column we use ALTER………..DROP command as follows.

mysql>ALTER TABLE table_name DROP column_name;

Example

 mysql>ALTER TABLE employee_data DROP salary;