Data Manipulation Language in SQL is SQL commands that deals with manipulation of data present in database.

In this post, SQL commands that we are going to learn –

1. SELECT command
2. INSERT command
3. UPDATE command
4. DELETE command

1. SELECT command

This is one of the most commonly used SQL statement and you have to be proficient with how to construct the SELECT statement. SELECT is used to fetch the records that are present in the table. We will see the usage of SELECT statement with some examples.

Example 1

SELECT statement to retrieve all the records from the particular table. Let’s say we want to retrieve all the columns of a table named department.

mysql> SELECT * FROM department;

Result

4 rows in set (0.00 sec)

Example 2

To retrieve only specific columns.

mysql> SELECT eid, dob, address FROM employee;

Result

7 rows in set (0.00 sec)

Example 3

Retrieving specific rows and columns depending upon the condition.

mysql> SELECT eid, dob, address FROM employee WHERE fname= 'Sharada';

Result

Example 4

Total number of records in a table.

mysql> SELECT count(*) FROM employee;

Result

Example 5

Retrieve all fnames including duplicates if exists.

mysql> SELECT all fname FROM employee;

7 rows in set (0.00 sec)

Example 6

Retrieve all fnames excluding duplicates if exists.

mysql> SELECT distinct fname FROM employee;

6 rows in set (0.00 sec)

Example 7

Total number of distinct values in a specified column.

mysql> SELECT count(distinct fname) FROM employee;

1 row in set (0.05 sec)

Example 8

Same above example but aliasing the column name while retrieving.

mysql> SELECT count(distinct fname) "Co_Di_Fn" FROM employee;

Result

Next command in data manipulation language is INSERT command.

2. INSERT command

• Once the table is created, you load the table with data to be manipulated. The INSERT INTO statement is used to do this.
• When inserting a single row of data into the table, the insert operation creates a new empty row in the table and then loads the values passed by the INSERT statement into the specified columns.
• In the INSERT INTO statement, columns and values have one to one relationship i.e. the first value described is inserted into the first column and the second value into the second column and so on.
• If you are inserting similar number of values as the number of columns and the sequence of values is exactly in accordance with the data type of the columns, then there is no need to specify the column names in the INSERT INTO statement.
• But if there are fewer values than the number of columns, then it is necessary to specify the column names and its corresponding values in the INSERT INTO statement.

There are two ways to insert values, first is by specifying the column names and second is without specifying the column names.

Example 1

Specify the column names as the value is not entered into the column dnum.

mysql> INSERT into employee(eid, fname, lname, DOB, address, sex, salary) values(‘101’, ‘Mohit’, ‘Kalokhe’, ‘1982-02-10’, ‘Akurdi, Pune’, ‘M’, 30000);

Result
Query OK, 1 row affected (0.06 sec)

Example 2

Insert values in all columns.

mysql> INSERT into department values (1, ‘Research’, ‘103’);

Result
Query OK, 1 row affected (0.02 sec)

Next command in data manipulation language is UPDATE command.

3. UPDATE command

This command is used to change or modify the data values. The SET clause indicates which column data should be modified and the new values that they should hold. The WHERE clause if given, specifies which rows should be updated. Otherwise all table rows are updated.

Example 1

Updating information using “=” in where.

mysql> UPDATE employee SET dnum=1 WHERE eid= ‘e105’;

Result
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Example 2

Updating information using like operator

mysql> UPDATE studentDetails SET gender= ‘M’ WHERE name like ‘P%’

Result
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Next command in data manipulation language is DELETE command.

4. DELETE command

The delete statement allows you to delete records from the table.

Example 1

Delete all records (Empty the table completely)

mysql> DELETE from employee;

Or

Mysql> TRUNCATE employee;

Example 2

Delete specific records.

mysql> DELETE from employee WHERE fname= "Arnav"

That’s end of post on Data Manipulation Language in SQL.