MYSQL DML Commands

DML Statements/Commands

Some DML statements are as follows

INSERT Statement

The INSERT statement is used to insert a new row/data in a table.
Syntax
INSERT INTO table_name VALUES (value1, value2, value3,…);
or
INSERT INTO table_name(column1, column2, column3,…)VALUES(value1, value2, value3,…);

example:

INSERT INTO PERSONS VALUES (4,‘Nilsen’, Johan’, ‘Bakken 2’, ‘Stavanger’);

Insert Data Only in Specified Columns

INSERT INTO PERSONS(P_Id, LastName, FirstName) VALUES (5, ‘Tjessem’, ‘Jakob’);

mysql> INSERT INTO Student VALUES (‘s1’,’Amitabh’, ‘Harivansh’,’1955-10-25’, ‘Mumbai’, 12);

mysql> INSERT INTO Student VALUES (‘s2’,’Sharukh Khan’, NULL,’1972-5-25’, ‘Delhi’, 10);

mysql> INSERT INTO Student (StID, FName, Name, Class) VALUES (‘s3’,’Amitabh’, ’Abhishek’, 10);

SELECT Statement

The SELECT statement is used to select data from a database or view table information. The result is stored in a result table, called the result set.
Syntax
To select some specify columns
SELECT column_name, column_name(s) FROM table_name;
or
To select all columns 
SELECT * FROM table_name;

Column Alias

Column alias is used to temporarily rename a table’s column for the purpose of a particular query. This renaming is a temporary change and the actual column name does not change in the database.

Syntax SELECT AS FROM ;

Alias_name specifies the reference name of the specified column.

For example, to select the DOJ of all the employees referenced as Joining_Date from the table COMPANY the query would be like

mysql> SELECT DOJ AS Joining_Date FROM COMPANY;

Operators in SQL

SQL supports different types of operators, some of them are described below

Arithmetic Operators
These operators are used to perform mathematical
calculations, such as addition, subtraction, multiplication, division and remainder.
Some most important arithmetic operators used in SQL are

OPERATOR DESCRIPTION
+ (Addition) Add the two arguments together
− (Subtraction) Subtract the second argument from the first argument
* (Multiplication) Multiplies the two arguments
/ (Division) Divide the first argument by the second argument 
% (Modulo) Divide the first argument from the second argument and provides the remainder of that operation

Comparison Operators

These operators are used to test or compare the value of two operands, i.e., between two variables or between a variable and a constant.
If the condition is false, then the result is zero (0) and if the condition is true, then the result is non-zero. These operators are also called relational operators.
Some of the comparison/relational operators used in SQL are as follows

OPERATOR DESCRIPTION

= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to

<> or != Not equal to 
!< Not less than 
!> Not greater than 

Logical Operators

The logical operators compare two conditions at a time to determine whether a row can be selected for the output.
Logical operators are also called boolean operators, because these operators return a boolean data type value as TRUE, or FALSE.

AND

Logical AND compares two expressions and return true, when both expressions are true

OR

Logical OR compares two expressions and return true, when atleast one of the expressions is true
NOT

NOT takes a single expression as an argument and changes its value from false to true or from true to false. You can use an exclamation point (!) in place of this operator

For example, query to display EMP_CODE and EMP_NAME for those employees whose EMP_DEPT_NO
is D05 and EMP_SALARY is greater than 22000.
mysql> SELECT EMP_CODE,EMP_NAME FROM COMPANY WHERE (EMP_DEPT_NO=‘D05’AND EMP_SALARY >22000);

Selecting all columns
If you want to view all columns of the student table, then you should give the following command-
mysql> SELECT * FROM Student ;

Selecting columns

If you want to view only Name and City columns of the student table
mysql> SELECT Name, City FROM Student ;

mysql> SELECT City, Name FROM Student ;

Eliminating Duplicate values in a column – DISTINCT

mysql> SELECT City FROM Student ;

mysql> SELECT DISTINCT City FROM Student ;

Doing simple calculations

We can also perform simple calculations with SQL Select command. SQL provide
a dummy table named DUAL, which can be used for this purpose.
mysql> SELECT 4*3 ;
We can also extend this idea with a columns of the existing table.
mysql> SELECT Name, Sal *12 FROM EMP ;

Using Column Aliases

We can give a different name to a column or expression (Alias) in the
output of a query.

mysql> SELECT Name, Sal*12 AS ‘Annual Salary’ FROM EMP;
mysql> SELECT Name, DOB AS ‘Date of Birth’ FROM Student;
mysql> SELECT 22/7 AS PI FROM Dual;

Selecting Specific Records – WHERE clause

WHERE <Condition>
We can select specific records by specifying conditions with WHERE clause.

mysql> SELECT * FROM Student WHERE City=‘Mumbai’;

mysql> SELECT Name, Fname, City from Student WHERE Class >10;

Specifying Range of Values – BETWEEN Operator
mysql> SELECT * FROM Emp WHERE Sal BETWEEN 5000 AND 10000 ;

The same query can also be written as –
mysql> SELECT * FROM Emp WHERE Sal >= 5000 AND Sal<=10000 ;

Other Logical operators also can be applied-
mysql> SELECT * FROM Emp WHERE NOT Sal BETWEEN 5000 AND 10000 ;

Specifying List – IN Operator

mysql> SELECT * FROM Emp WHERE Sal IN (5000, 10000) ;
The same query can also be written as –
mysql> SELECT * FROM Emp WHERE Sal = 5000 OR Sal =10000 ;

mysql> SELECT * FROM Student WHERE City IN (‘Mumbai’, ‘Delhi’,’Kanpur’) ;

Pattern Matching – LIKE Operator

A string pattern can be used in SQL using the following wild card
% Represents a substring in any length
_ Represents a single character

Example:
‘A%’ represents any string starting with ‘A’ character.
‘_ _A’ represents any 3 character string ending with ‘A’.
‘_B%’ represents any string having second character ‘B’
‘_ _ _’ represents any 3 letter string.

A pattern is case sensitive and can be used with LIKE operator.

mysql> SELECT * FROM Student WHERE Name LIKE ‘A%’;
mysql> SELECT * FROM Student WHERE Name LIKE ‘%Singh%’;
mysql> SELECT Name, City FROM Student WHERE Class>=9 AND Name LIKE ‘%Kumar%’ ;

Searching NULL Values – IS Operator

• mysql> SELECT * FROM Student WHERE City IS NULL ;

The NOT Operator can also be applied –

• mysql> SELECT * FROM Student WHERE City IS NOT NULL;

Ordering Query Result – ORDER BY Clause
A query result can be orders in ascending (A-Z) or descending (Z-A)order as per any column. Default is Ascending order.

• mysql> SELECT * FROM Student ORDER BY City;

To get descending order use DESC key word.

• mysql> SELECT * FROM Student ORDER BY City DESC;
• mysql> SELECT Name, Fname, City FROM Student Where Name LIKE ‘R%’ ORDER BY Class;
• mysql> SELECT Name, Basic+DA AS ‘PAY’ FROM Student ORDER BY PAY;

Inserting Records from Other Table

You can insert all or selected record(s) in the table from another table by using Select … command in place of Values.
Suppose a table named NEWSTUDENT has been created and records to be inserted from OLDSTUDENT table having the
same structure of columns.

mysql> INSERT INTO Newstudent VALUES (SELECET * FROM Oldstudent);

mysql>INSERT INTO Newstudent VALUES (SELECT * FROM Oldstudent WHERE City=‘Mumbai’);

mysql> INSERT INTO Newstudent (StID, Name, Class) VALUES (Select StID, Name,Class FROM Oldstudent  WHERE Class>=11);

Deleting Records from the Table

You can delete all or selected record(s) from the table by using the following DML command.
DELETE FROM <Table Name> [WHERE <Condition>]

mysql> DELETE FROM Student ;

mysql> DELETE FROM Student WHERE City=‘Mumbai’ ;
mysql> DELETE FROM Student WHERE Class >=11 ;
mysql> DELETE FROM Student WHERE Class <9 AND City=‘Delhi’;

Modifying Records –UPDATE Command

You can modify the values of columns of all or selected records in the table by using the following DML command.

UPDATE <Table Name>  
SET <Column> = <Expression>
[WHERE <Condition>]

mysql> UPDATE Student SET Class =10 ;

mysql> UPDATE Student SET FName= CONACT(‘Mr.’, FName’) ;

mysql> UPDATE Emp SET Sal = Sal+(Sal*10/100);

mysql> UPDATE Emp SET Sal = Sal+(Sal*10/100) WHERE Sal <=10000;

mysql> UPDATE Emp SET City = ‘Dehradun’ WHERE CITY IS NULL;