Data Manipulation Statements

Data Manipulation Statement is used to retrieve, insert, update and delete the records in a database. All database users will use these commands during routine operation of the database. In this section we are describing the following Data Manipulation Statements :

Select Statement
Insert Statement
Update Statement
Delete Statement

SELECT Statement

The SELECT statement is used to retrieve a result set of records from one or more tables, temporary tables or views in a database. It is the most commonly used DML command. In this statement the user can specify a description of the required result set. There are some keywords uses in SELECT statement that are described in the following table

Keywords	Description
SELECT	SELECT statement is used to retrieve fields from one or more tables.
FROM	Tables containing to the fields.
WHERE	The WHERE clause is used to describe the criteria to restrict the records retrieved.
GROUP BY	The GROUP BY clause is used to determine how the records should be grouped.
HAVING	HAVING clause used with GROUP BY to define the criteria for grouped records
ORDER BY	The ORDER BY clause is used to described the criteria for ordering the record.
LIMIT	The LIMIT clause is used to restrict the limit of number of records retrieved.

The simple SELECT statement is used to retrieve the all records from table. By the following example you can retrieve the full list of Emp table.

mysql> SELECT * FROM Emp;
+-----+--------+--------+-------------------+--------+
| Eid | Ename  | City   | Designation       | Salary |
+-----+--------+--------+-------------------+--------+
| 1   | Rahul  | Delhi  | Manager           | 10000  |
| 2   | Gaurav | Mumbai | Assistant Manager | 10000  |
+-----+--------+--------+-------------------+--------+
2 rows in set (0.00 sec)

If you want to retrieve only some fields from a table, then you have to provide a comma separated list of column names. By the following example you select Name, City and Age fields from the Emp table.

mysql> SELECT Eid, Ename, Salary FROM Emp;
+-----+--------+--------+
| Eid | Ename  | Salary |
+-----+--------+--------+
| 1   | Rahul  | 10000  |
| 2   | Gaurav | 10000  |
+-----+--------+--------+
2 rows in set (0.02 sec)

WHERE Clause

The WHERE clause is used to limit the number of records. The comparison operators are used with WHERE clause to limit the number of records. Comparison operator?s list are given below:

Operator	Description
=	Equal to
<> or !=	Not equal to
<	Less then
< =	Less then or equal to
>	Greater then
> =	Greater then or equal to
Like	Used for comparing string
Between	Used for checking value between a range.
IN	Used to check values in a list
NOT IN	Used to check the value is not in the list.

% Character - If you are working with Strings, then % character can be used as a wildcard. By the following example you can retrieve the all fields from Emp table where the Designation field contain the text, 'Manager'.

mysql> SELECT * FROM Emp WHERE Designation LIKE '%Manager%';
+-----+--------+--------+-------------------+--------+
| Eid | Ename  | City   | Designation       | Salary |
+-----+--------+--------+-------------------+--------+
| 1   | Rahul  | Delhi  | Manager           | 10000  |
| 2   | Gaurav | Mumbai | Assistant Manager | 10000  |
+-----+--------+--------+-------------------+--------+
2 rows in set (0.02 sec)

_ character - The underscore character can be used as a placeholder. By the following example you can selects the all records from the table Emp, where the Name starts with ?C? followed by Six characters. For this we have to use Six underscores.

mysql> SELECT * FROM Emp WHERE Ename LIKE 'C______';
+-----+---------+----------+-------------+--------+
| Eid | Ename   | City     | Designation | Salary |
+-----+---------+----------+-------------+--------+
| 3   | Chandan | Banglore | Team Leader | 15000  |
+-----+---------+----------+-------------+--------+
1 row in set (0.00 sec)

BETWEEN Clause - The BETWEEN clause can be used with numbers, dates and text. The following example is used to retrieve all fields Emp table where the Salary is between 8000 AND 10000.

mysql> SELECT * FROM Emp WHERE Salary BETWEEN 8000 AND 10000;
+-----+---------+--------+-------------------+--------+
| Eid | Ename   | City   | Designation       | Salary |
+-----+---------+--------+-------------------+--------+
| 1   | Rahul   | Delhi  | Manager           | 10000  |
| 2   | Gaurav  | Mumbai | Assistant Manager | 10000  |
| 4   | Santosh | Delhi  | Designer          | 8000   |
+-----+---------+--------+-------------------+--------+
3 rows in set (0.01 sec)

OR Clause - The OR clause is used to check the values against the range of values that have been specified. The following example retrieves the list of all records where the Designation is either 'Designer' or City is 'Mumbai' in the Emp table.

mysql> SELECT * FROM Emp WHERE Designation='Designer' OR City='Mumbai';
+-----+---------+--------+-------------------+--------+
| Eid | Ename   | City   | Designation       | Salary |
+-----+---------+--------+-------------------+--------+
| 2   | Gaurav  | Mumbai | Assistant Manager | 10000  |
| 4   | Santosh | Delhi  | Designer          | 8000   |
+-----+---------+--------+-------------------+--------+
2 rows in set (0.44 sec)

IN Clause - The IN clause is used to check the values against to many values that have been specified in IN clause. The following example retrieves the list of all records where the Designation is either 'Manager' or 'Assistant' in the Emp table.

mysql> SELECT * FROM Emp WHERE Designation IN ('Manager','Designer');
+-----+---------+-------+-------------+--------+
| Eid | Ename   | City  | Designation | Salary |
+-----+---------+-------+-------------+--------+
| 1   | Rahul   | Delhi | Manager     | 10000  |
| 4   | Santosh | Delhi | Designer    | 8000   |
+-----+---------+-------+-------------+--------+
2 rows in set (0.03 sec)

NOT IN Clause - You can use the NOT modifier with IN clause for checking the values, which are not within the list. The following example retrieves the list of all records where the Designation is not equal to 'Manager' or 'Assistant' in the Emp table.

mysql> SELECT * FROM Emp WHERE Designation NOT IN ('Manager','Designer');
+-----+---------+----------+-------------------+--------+
| Eid | Ename   | City     | Designation       | Salary |
+-----+---------+----------+-------------------+--------+
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10000  |
| 3   | Chandan | Banglore | Team Leader       | 15000  |
+-----+---------+----------+-------------------+--------+
2 rows in set (0.81 sec)

GROUP BY Clause

The GROUP BY clause is an optional clause. A query has a GROUP BY clause is known as GROUPING QUERY. A Grouping Query is a special type of query that is used to groups and summarized the rows. It operators on the rows from the FROM Clause as filtered by the WHERE clause. It is used to collects the rows into groups that?s based on the common values in the grouping columns. The general syntax of GROUP BY clause is :
SELECT * FROM tbl_name GROUP BY column_name;

Example :

mysql> SELECT * FROM Emp GROUP BY Ename;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 3   | Chandan | Banglore | Team Leader       | 15000  | 999   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10000  | 853   |
| 1   | Rahul   | Delhi    | Manager           | 10000  | 853   |
| 4   | Santosh | Delhi    | Designer          | 8000   | 825   |
+-----+---------+----------+-------------------+--------+-------+
4 rows in set (0.25 sec)

Aggregate Function

The following list shows you a Aggregate Function that available in MySQL. Our Table records are listed below and on this table we performing these aggregate function :

Table :

mysql> SELECT * FROM Emp;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10000  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10000  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 15000  | 999   |
| 4   | Santosh | Delhi    | Designer          | 8000   | 825   |
+-----+---------+----------+-------------------+--------+-------+
4 rows in set (0.00 sec)

AVG( );
The AVG( ) function returns the average value in a group of records. Example of the AVG( ) function:-

mysql> SELECT AVG(Perks) FROM Emp GROUP BY Perks;
+------------+
| AVG(Perks) |
+------------+
| 825.0000   |
| 853.0000   |
| 999.0000   |
+------------+
3 rows in set (0.02 sec)

COUNT( );
The COUNT( ) function returns the number of records in a group of records. Example of the COUNT( ) function:-

mysql> SELECT COUNT(Perks) FROM Emp GROUP BY Perks;
+--------------+
| COUNT(Perks) |
+--------------+
| 1            |
| 2            |
| 1            |
+--------------+
3 rows in set (0.00 sec)

MAX( );
The MAX( ) function return the maximum value in a group of records. Example of the MAX( ) function:-

mysql> SELECT MAX(Salary) FROM Emp;
+-------------+
| MAX(Salary) |
+-------------+
| 15000       |
+-------------+
1 row in set (0.00 sec)

MIN( );
The MIN( ) function returns minimum value in a group of records. Example of the MIN( ) function:-

mysql> SELECT MIN(Salary) FROM Emp;
+-------------+
| MIN(Salary) |
+-------------+
| 8000        |
+-------------+
1 row in set (0.00 sec)

SUM( );
The SUM( ) function return the sum of the field. Example of the SUM() function :

mysql> SELECT SUM(Perks) FROM Emp;
+------------+
| SUM(Perks) |
+------------+
| 3530       |
+------------+
1 row in set (0.01 sec)

The HAVING Clause

As you know the WHERE clause is used to restrict the records in a query. But if you want to restrict the records by using the Aggregate function then you have to use the HAVING clause. The HAVING clause restricts the records after they have been grouped. The following example shows the list of all Employees who got the perks more than 900 on average.

mysql> SELECT * FROM Emp GROUP BY Salary HAVING AVG(Perks)>900;
+-----+---------+----------+-------------+--------+-------+
| Eid | Ename   | City     | Designation | Salary | Perks |
+-----+---------+----------+-------------+--------+-------+
| 3   | Chandan | Banglore | Team Leader | 15000  | 999   |
+-----+---------+----------+-------------+--------+-------+
1 row in set (0.00 sec)

The ORDER BY Clause

The ORDER BY clause can be used to set the order of the retrieved records. The following example shows the list of all employees in the Emp table in alphabetical order. In this clause we can use the ASC or DESC modifiers to set the order of records in ascending or descending order. If any modifier is not provided then the records are listed in ascending order.

mysql> SELECT * FROM Emp ORDER BY Eid DESC;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 4   | Santosh | Delhi    | Designer          | 8000   | 825   |
| 3   | Chandan | Banglore | Team Leader       | 15000  | 999   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10000  | 853   |
| 1   | Rahul   | Delhi    | Manager           | 10000  | 853   |
+-----+---------+----------+-------------------+--------+-------+
4 rows in set (0.00 sec)

The LIMIT Clause

The LIMIT clause can be used to limit the number of records that have been returned by the SELECT statement. You can specify the start row, and number of records retrieved.

mysql> SELECT * FROM Emp LIMIT 0,2;
+-----+--------+--------+-------------------+--------+-------+
| Eid | Ename  | City   | Designation       | Salary | Perks |
+-----+--------+--------+-------------------+--------+-------+
| 1   | Rahul  | Delhi  | Manager           | 10000  | 853   |
| 2   | Gaurav | Mumbai | Assistant Manager | 10000  | 853   |
+-----+--------+--------+-------------------+--------+-------+
2 rows in set (0.00 sec)

INSERT Statement

The INSERT statement is used to add one or more records to a existing table in a database. This statement can insert the data into only a single table. The general syntax of the INSERT Statement is :

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] INTO tbl_name (column1, [column2, .. ]) VALUES (value1, [value2, ..])
and
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] INTO tbl_name VALUES (value1, [value2, ...])

If we are specifying the column name in INSERT statement then the number of columns and values must be same. In this situation any column is not specified then the default value for the column is used. The values are specified in the INSERT command must specify the all-applicable constraints like primary keys, NOT NULL and CHECK Constraints. If any constraints are violated then it occurs syntax error and new row is not added. Example :

mysql> INSERT INTO Emp(Ename,City,Designation,Salary,Perks) VALUES('Tapan','Pune',
'Developer',20000,1111);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO Emp VALUES(6,'Rajesh','Hyderabad','Developer',18000,1222);
Query OK, 1 row affected (0.41 sec)

INSERT Statement supports the following modifiers :

If we are using the DELAYED keyword then the server puts the row into a buffer and the client issues the INSERT command can then continue immediately. If table is in use then server can holds the rows and when the table is free then only server can begins inserting the rows. And its also used to insert from many clients are bundled together and written in one block.
If we are using the LOW_PRIORITY keyword then the execution of INSERT statement is delayed until no other clients are reading from the table. It can be possible a client, which issues a INSERT LOW_PRIORITY statement have to wait for a long time(forever also) in a read heavy environment.
But if we are using HIGH_PRIORITY then it overrides the effect of LOW_PRIORITY updates option.
If we are using IGNORE keyword then the execution of INSERT statement error can be treated as a warnings. Means with IGNORE the row still is not inserted but no error is occurred.

UPDATE Statement

The UPDATE statement is used to modify the record contained with in a table either one row or more than one row. The general syntax of UPDATE Statement is :

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

The set clause is used to indicates which columns have to modify and the values they should be given. If WHERE clause is given then it specifies the conditions that identify which rows to modify. If ORDER BY clause is given that the rows are modify in the order that is specified. If LIMIT clause is given then it places a limit on the number of rows that can be modified.

The UPDATE statement supports the following modifiers:

If LOW_PRIORITY keyword is using, then execution of UPDATE command is delayed until no other clients are reading from the table.
If IGNORE keyword is using and error occurs during the execution of update command then the update statement does not abort.

In the following example we are updating the salary of those employees their perks is more than 800. Example : 0

mysql> SELECT * FROM Emp;
+-----+---------+-----------+-------------------+--------+-------+
| Eid | Ename   | City      | Designation       | Salary | Perks |
+-----+---------+-----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi     | Manager           | 10000  | 853   |
| 2   | Gaurav  | Mumbai    | Assistant Manager | 10000  | 853   |
| 3   | Chandan | Banglore  | Team Leader       | 15000  | 999   |
| 4   | Santosh | Delhi     | Designer          | 8000   | 825   |
| 5   | Tapan   | Pune      | Developer         | 20000  | 1111  |
| 6   | Rajesh  | Hyderabad | Developer         | 18000  | 1222  |
+-----+---------+-----------+-------------------+--------+-------+
6 rows in set (0.00 sec)
mysql> UPDATE Emp
    -> SET Salary = Salary*1.03 WHERE Perks > 800;
Query OK, 6 rows affected (0.22 sec)
Rows matched: 6  Changed: 6  Warnings: 0
mysql> SELECT * FROM Emp;
+-----+---------+-----------+-------------------+--------+-------+
| Eid | Ename   | City      | Designation       | Salary | Perks |
+-----+---------+-----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi     | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai    | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore  | Team Leader       | 15450  | 999   |
| 4   | Santosh | Delhi     | Designer          | 8240   | 825   |
| 5   | Tapan   | Pune      | Developer         | 20600  | 1111  |
| 6   | Rajesh  | Hyderabad | Developer         | 18540  | 1222  |
+-----+---------+-----------+-------------------+--------+-------+
6 rows in set (0.00 sec)

DELETE Statement

The DELETE Statement is used the remove the records from the table. The general syntax of DELETE Statement is :

DELETE [LOW_PRIORITY] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count];

The DELETE Statement is used for deleting the rows from the given table. If the WHERE clause is given then it specifies the condition for identying which rows have to delete. If ORDER BY clause is given then it specifies the rows are deleted in the order that is specified. The LIMIT clause is used to place a Limit on the number of rows which can be deleted. 1

The DELETE statement supports the following modifiers:

If LOW_PRIORITY keyword is using, then execution of DELETE command is delayed until no other clients are reading from the table.
If IGNORE keyword is using and error occurs during the execution of DELETE command then the errors are returns as warnings.

In the following example we are deleting the records of those employees their salary is less than 10000. Example :

mysql> SELECT * FROM Emp;
+-----+---------+-----------+-------------------+--------+-------+
| Eid | Ename   | City      | Designation       | Salary | Perks |
+-----+---------+-----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi     | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai    | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore  | Team Leader       | 15450  | 999   |
| 4   | Santosh | Delhi     | Designer          | 8240   | 825   |
| 5   | Tapan   | Pune      | Developer         | 20600  | 1111  |
| 6   | Rajesh  | Hyderabad | Developer         | 18540  | 1222  |
+-----+---------+-----------+-------------------+--------+-------+
6 rows in set (0.00 sec)
mysql> DELETE FROM Emp WHERE Salary<10000;
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM Emp;
+-----+---------+-----------+-------------------+--------+-------+
| Eid | Ename   | City      | Designation       | Salary | Perks |
+-----+---------+-----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi     | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai    | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore  | Team Leader       | 15450  | 999   |
| 5   | Tapan   | Pune      | Developer         | 20600  | 1111  |
| 6   | Rajesh  | Hyderabad | Developer         | 18540  | 1222  |
+-----+---------+-----------+-------------------+--------+-------+
5 rows in set (0.00 sec)

REPLACE Statement

REPLACE Statement works exactly same as the INSERT Statement, except only then if an old record in the table has the same value as in new row for a PRIMARY KEY or UNIQUE index then old row is deleted and new row is inserted. The general syntax of REPLACE Statement is : 2

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),...

The REPLACE Statement returns a count for indicating the number of records affected. It is the sum of rows deleted and inserted. When the count is 1 that means one record was inserted and no records were deleted. But if the count is greater than one that means one or more old records were deleted before inserted the new row. For using REPLACE command need the both INSERT and DELETE privileges for the table.

In the following example we are replacing the record that's having the Eid is 6. 3

mysql> SELECT * FROM Emp;
+-----+---------+-----------+-------------------+--------+-------+
| Eid | Ename   | City      | Designation       | Salary | Perks |
+-----+---------+-----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi     | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai    | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore  | Team Leader       | 15450  | 999   |
| 5   | Tapan   | Pune      | Developer         | 20600  | 1111  |
| 6   | Rajesh  | Hyderabad | Developer         | 18540  | 1222  |
+-----+---------+-----------+-------------------+--------+-------+
5 rows in set (0.00 sec)
mysql> REPLACE INTO Emp VALUES(6,'Amar','Chennai','Developer',16000,1124);
Query OK, 2 rows affected (0.06 sec)
mysql> SELECT * FROM Emp;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 15450  | 999   |
| 5   | Tapan   | Pune     | Developer         | 20600  | 1111  |
| 6   | Amar    | Chennai  | Developer         | 16000  | 1124  |
+-----+---------+----------+-------------------+--------+-------+
5 rows in set (0.00 sec)

TRUNCATE Statement

TRUNCATE Statement is also used to empty the table completely. The general syntax of TRUNCATE Statement is :

TRUNCATE tbl_name;

Logically TRUNCATE Statement is same as DELETE Statement, which deletes all rows. But practically they have some differences : 4

TRUNCATE command drop the table and recreate the definition of table, which is much faster than deleting the rows one by one.
TRUNCATE command operation are not transaction safe.
TRUNCATE command does not return the number of deleted rows.
If table format file tbl_name.frm is valid, then the table can be recreated if its empty by the TRUNCATE command even if the data or index files have become corrupted.

In the following example we truncating the Emp1 table.

mysql> select * from Emp1;
+--------+--------+--------+-----------+------+------+--------+-------+
| emp_id | f_name | l_name | title     | age  | yos  | salary | perks |
+--------+--------+--------+-----------+------+------+--------+-------+
| 1      | Rahul  | Jain   | Manager   | 22   | 1    | 10000  | 5000  |
| 2      | Rajesh | Kumar  | Developer | 25   | 2    | 20000  | 500   |
+--------+--------+--------+-----------+------+------+--------+-------+
2 rows in set (0.00 sec)
mysql> TRUNCATE Emp1;
Query OK, 2 rows affected (0.20 sec)
mysql> select * from Emp1;
Empty set (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_employee |
+--------------------+
| emp                |
| emp1               |
+--------------------+
2 rows in set (0.00 sec)

Data Manipulation Statements