Practical 14 - MySQL
Update & Delete records / Add New Columns & Drop Existing Columns
update command - This command is used to change the contents of the table.
The Syntax of this command is as follows -
mysql> update tableName set ColumnName = NewValue where Condition;
delete command - This command is used to change the contents of the table.
The Syntax of this command is as follows -
mysql>delete from tableName where Condition;
alter table – this command is used to change the structure of the table. i.e. to add a new
column or remove an existing column, Changing the name and/or data type of a
column.
Syntax - alter table tableName add column ColumnName DataType Constraints;
alter table tableName drop column ColumnName;
Introduction – we are operating on college database having table staff. The staff table
contains the fields – tno,fname ,sname , post and salary.
mysql> use college;
Database changed
mysql> select * from staff;
+-----+-----------+-----------+-----------+--------+
| tno | fname | sname | post | salary |
+-----+-----------+-----------+-----------+--------+
| 101 | Shekhar | Godala | Professor | 56000 |
| 102 | Narender | Yadav | Reader | 52000 |
| 103 | Ragi | Udaykumar | Lecturer | 55000 |
| 104 | Shawon | Das | Lecturer | 59000 |
| 105 | Meenakshi | Saini | Reader | 55000 |
+-----+-----------+-----------+-----------+--------+
5 rows in set (0.01 sec)
mysql> update staff set post = 'Professor' where tno = 102;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from staff;
+-----+-----------+-----------+-----------+--------+
| tno | fname | sname | post | salary |
+-----+-----------+-----------+-----------+--------+
| 101 | Shekhar | Godala | Professor | 56000 |
| 102 | Narender | Yadav | Professor | 52000 |
| 103 | Ragi | Udaykumar | Lecturer | 55000 |
| 104 | Shawon | Das | Lecturer | 59000 |
| 105 | Meenakshi | Saini | Reader | 55000 |
+-----+-----------+-----------+-----------+--------+
5 rows in set (0.00 sec)
mysql> update staff set salary = salary + 0.25 * salary
-> where post = 'Lecturer';
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from staff;
+-----+-----------+-----------+-----------+--------+
| tno | fname | sname | post | salary |
+-----+-----------+-----------+-----------+--------+
| 101 | Shekhar | Godala | Professor | 56000 |
| 102 | Narender | Yadav | Professor | 52000 |
| 103 | Ragi | Udaykumar | Lecturer | 68750 |
| 104 | Shawon | Das | Lecturer | 73750 |
| 105 | Meenakshi | Saini | Reader | 55000 |
+-----+-----------+-----------+-----------+--------+
5 rows in set (0.00 sec)
mysql> delete from staff where tno = 105;
Query OK, 1 row affected (0.07 sec)
mysql> select * from staff;
+-----+----------+-----------+-----------+--------+
| tno | fname | sname | post | salary |
+-----+----------+-----------+-----------+--------+
| 101 | Shekhar |Godala | Professor | 56000 |
| 102 | Narender | Yadav | Professor | 52000 |
| 103 | Ragi | Udaykumar | Lecturer | 68750 |
| 104 | Shawon | Das | Lecturer | 73750 |
+-----+----------+-----------+-----------+--------+
4 rows in set (0.00 sec)
mysql> alter table staff add column gender varchar(5);
Query OK, 4 rows affected (0.36 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from staff;
+-----+----------+-----------+-----------+--------+--------+
| tno | fname | sname | post | salary | gender |
+-----+----------+-----------+-----------+--------+--------+
| 101 | Shekhar |Godala | Professor | 56000 | NULL |
| 102 | Narender | Yadav | Professor | 52000 | NULL |
| 103 | Ragi | Udaykumar | Lecturer | 68750 | NULL |
| 104 | Shawon | Das | Lecturer | 73750 | NULL |
+-----+----------+-----------+-----------+--------+--------+
4 rows in set (0.01 sec)
mysql> alter table staff drop column gender;
Query OK, 4 rows affected (0.20 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from staff;
+-----+----------+-----------+-----------+--------+
| tno | fname | sname | post | salary |
+-----+----------+-----------+-----------+--------+
| 101 | Shekhar |Godala | Professor | 56000 |
| 102 | Narender | Yadav | Professor | 52000 |
| 103 | Ragi | Udaykumar | Lecturer | 68750 |
| 104 | Shawon | Das | Lecturer | 73750 |
+-----+----------+-----------+-----------+--------+
4 rows in set (0.01 sec)
mysql> alter table staff add column gender varchar(5) after sname;
Query OK, 4 rows affected (0.22 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from staff;
+-----+----------+-----------+--------+-----------+--------+
| tno | fname | sname | gender | post | salary |
+-----+----------+-----------+--------+-----------+--------+
| 101 | Shekhar |Godala | NULL | Professor | 56000 |
| 102 | Narender | Yadav | NULL | Professor | 52000 |
| 103 | Ragi | Udaykumar | NULL | Lecturer | 68750 |
| 104 | Shawon | Das | NULL | Lecturer | 73750 |
+-----+----------+-----------+--------+-----------+--------+
4 rows in set (0.01 sec)
mysql> update staff set gender = 'Male' where tno = 101;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update staff set gender = 'Male' where tno = 102;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update staff set gender = 'Male' where tno = 103;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update staff set gender = 'Female' where tno = 104;
ERROR 1406 (22001): Data too long for column 'gender' at row 1
mysql> alter table staff modify column gender varchar(6);
Query OK, 4 rows affected (0.23 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> update staff set gender = 'Female' where tno = 104;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from staff;
+-----+----------+-----------+--------+-----------+--------+
| tno | fname | sname | gender | post | salary |
+-----+----------+-----------+--------+-----------+--------+
| 101 | Shekhar |Godala | Male | Professor | 56000 |
| 102 | Narender | Yadav | Male | Professor | 52000 |
| 103 | Ragi | Udaykumar | Male | Lecturer | 68750 |
| 104 | Shawon | Das | Female | Lecturer | 73750 |
+-----+----------+-----------+--------+-----------+--------+
4 rows in set (0.00 sec)
mysql> update staff set gender = 'Male' where tno = 104;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from staff;
+-----+----------+-----------+--------+-----------+--------+
| tno | fname | sname | gender | post | salary |
+-----+----------+-----------+--------+-----------+--------+
| 101 | Shekhar |Godala | Male | Professor | 56000 |
| 102 | Narender | Yadav | Male | Professor | 52000 |
| 103 | Ragi | Udaykumar | Male | Lecturer | 68750 |
| 104 | Shawon | Das | Male | Lecturer | 73750 |
+-----+----------+-----------+--------+-----------+--------+
4 rows in set (0.00 sec)
