The ‘Where’ Clause is the most treasured conditional statement in MySQL according to me. In due course of experience, you will know that the most used condition will be under the syntax of ‘Where’ Clause.
The General Syntax is:
SELECT column1, column2, … FROM table_name WHERE columnN condition;
Let us try to obtain the details of Students who failed in the class having ‘F’ grade and some more conditions subsequently.
There is a series of Queries on the use of WHERE Clause that will make you understand the concept crystal clear!
SELECT name,marks,grade,mobile FROM school WHERE grade = “F”;
SELECT roll, name,marks FROM school WHERE marks>80;
SELECT state,govt,population FROM country WHERE population>100000000;
SELECT product, name, quantity FROM store WHERE exp<curdate();
SELECT product, name, exp FROM store WHERE price>1000;
SELECT * FROM railways WHERE train_no = 12437;
SELECT train_no, train_name, costperkm FROM railways WHERE costperkm<100;
SELECT service_no, name, ranks, unit FROM air_force WHERE service_no<800000;
The ganeral Syntax is
SELECT column1, column2, … FROM table_name WHERE columnN LIKE pattern;
The LIKE Clause has two important operators namely, ‘%’ and ‘_’ where % represents any number of characters including zero or one but _ represents only one character.
The LIKE Clause is mainly designed for string matching and comparison and all the names you have can be manipulated using the LIKE Clause.
SELECT state, capital, govt FROM country WHERE govt LIKE ”%BJP%”;
If there is a special need of finding the Products of a Store which starts with the letter ‘S’, then this can be achieved by the following Query:
SELECT product, name FROM store WHERE name LIKE “S%”;
If there is a need of finding a String that has a particular number of Characters from the Column. In fact, all the specific pattern can be obtained from the use of these two operators.
Let’s imagine the teachers wants to know which students have 5 letters in their name.
SELECT roll, name FROM school WHERE name LIKE “_____”;
The former Query shows a result of Empty set because there is no such name in the Name List whose name is like that format, if the name like “n a m e” was present, the query would have shown that.
But, the LIKE Clause doesn’t get restricted to Strings as well. The Integers can also be compared using that. If you want to check a 3-digit Quantity in the Store list, then the following Query would help:
SELECT * FROM store WHERE quantity LIKE "___";
This segment will relate you to the actual implementations of the Operators and the 2 Clauses. You may feel the way how you can filter the results in Net when the user gives a specific condition. How to show the correct results where the user starts typing the actual word and much more!