Description
Data Manipulation Language is utilized to manage data within the table i.e.,
MySQL DML statements are used to manage data inside the schema objects, and includes most common standard query language statements such as Select command and Insert command. MySQL DML allows to add and modify the database table.
Following are the DML statements:
Description
By using insert command, new record can be inserted in the existing database table. i.e, inserting field value like emp_id, emp_name and salary can be inserted.
Syntax
Insert into <table_name> values(<value1>,<value2>,<value3>.......);
table name => The accurate table.
values => values are the column values.
Examples
By viewing the below example, the concept of insert command can be understood easily.
[sql]mysql> create table employee(emp_id int,ename varchar(255),salary int);
Query OK, 0 rows affected (0.32 sec)
mysql> insert into employee values(1001,'jack',12000);
ERROR 1146 (42S02): Table 'employee.employee' doesnt exist
mysql> create table employee(emp_id int,ename varchar(255),sal int);
Query OK, 0 rows affected (0.63 sec)
mysql> insert into employee values(1001,'jack',12000);
Query OK, 1 row affected (0.10 sec)
mysql> insert into employee values(1002,'mack',13000);
Query OK, 1 row affected (0.39 sec)
mysql> select * from employee;
+--------+-------+-------+
| emp_id | ename |salary |
+--------+-------+-------+
| 1001 | jack | 12000 |
| 1002 | mack | 13000 |
+--------+-------+-------+
2 rows in set (0.00 sec)
mysql> insert into employee values(1003,'kate',15000);
Query OK, 1 row affected (0.39 sec)
mysql> select * from employee;
+--------+-------+-------+
| emp_id | ename |salary |
+--------+-------+-------+
| 1001 | jack | 12000 |
| 1002 | mack | 13000 |
| 1003 | kate | 15000 |
+--------+-------+-------+
3 rows in set (0.00 sec)
[/sql]
The above example tells that, when insert command is performed on the table employee then it will accept the values and stores in the table database.
Description
By using update command, the column values can be updated i.e, changing the name of the employee and increasing/decreasing the salary.
Syntax
Update<table_name> set <column_name>=value where <condition>;
Table_name => Any accurate table.
condition => condition is a logic to get a specific record.
Examples
By viewing the below example, the concept of update command can be easily understand.
[sql]mysql> select * from employee;
+--------+-------+-------+
| emp_id | ename |salary |
+--------+-------+-------+
| 1001 | maddi | 12000 |
| 1002 | jack | 13000 |
+--------+-------+-------+
2 rows in set (0.00 sec)
mysql> update employee set sal=sal+100 where ename='jack';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+--------+-------+-------+
| emp_id | ename |salary |
+--------+-------+-------+
| 1001 | maddi | 12000 |
| 1002 | jack | 13100 |
+--------+-------+-------+
2 rows in set (0.00 sec)
mysql> update employee set ename='mike' where emp_id=1001;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+--------+-------+-------+
| emp_id | ename |salary |
+--------+-------+-------+
| 1001 | mike | 12000 |
| 1002 | jack | 13100 |
+--------+-------+-------+
2 rows in set (0.00 sec)[/sql]
The above example tells how to update the existing data from the table i.e before updating the salary it was 13000 and after updating the result is 13100.
Description
By using delete command one can delete the rows of an existing table based on the given condition.
Syntax
Delete from <table_name> where <condition>;
table_name => Any accurate table.
condition => condition is a logic to get a specific record.
Examples
By viewing the below example, the concept of delete command can be understand easily.
[sql]mysql> select * from employee;
+--------+-------+-------+
| emp_id | ename |salary |
+--------+-------+-------+
| 1001 | maddi | 12000 |
| 1002 | jack | 13100 |
+--------+-------+-------+
2 rows in set (0.00 sec)
mysql> delete from employee where ename='maddi';
Query OK, 1 row affected (0.16 sec)
mysql> select * from employee;
+--------+-------+-------+
| emp_id | ename |salary |
+--------+-------+-------+
| 1002 | jack | 13100 |
+--------+-------+-------+
1 row in set (0.00 sec)
mysql> delete from employee where emp_id=1001;
Query OK, 1 row affected (0.16 sec)
mysql> select * from employee;
+--------+-------+-------+
| emp_id | ename |salary |
+--------+-------+-------+
1 row in set (0.00 sec)[/sql]
The above example tells that, when delete command is performed on a table employee and wants to delete ename=maddi, then it delete the entire details of maddi and gives the output of remaining employees in the table.
Key Points
- MySQL DML commands are used to change the data within the database table.
- Inserting new records is done by Insert command.
- Updating a record is done by Update command.
- Deleting a record is done by Delete command.