MySQL - SPLessons

MySQL DML

Chapter 9

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL DML

MySQL DML

shape 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:

shape Conceptual
figure

Insert

shape 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.

shape Syntax

Insert into <table_name> values(<value1>,<value2>,<value3>…….);

table name => The accurate table.

values => values are the column values.

shape Examples

By viewing the below example, the concept of insert command can be understood easily.

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)

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.

Update

shape Description

By using update command, the column values can be updated i.e, changing the name of the employee and increasing/decreasing the salary.

shape 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.

shape Examples

By viewing the below example, the concept of update command can be easily understand.

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)

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.

Delete

shape Description

By using delete command one can delete the rows of an existing table based on the given condition.

shape Syntax

Delete from <table_name> where <condition>;

table_name => Any accurate table.

condition => condition is a logic to get a specific record.

shape Examples

By viewing the below example, the concept of delete command can be understand easily.

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)

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.

Summary

shape 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.