MySQL SQL Statements and Search Conditions
Implementing Database in MySQL – Part 6
Foreword: In this part of the series, we look at search conditions in MySQL SQL Statements.
By: Chrysanthus Date Published: 14 Apr 2015
We saw the following syntax in the previous part of the series:
Here, expr stands for expression. It is also a search condition in the sense that it is used to search information in the database. We shall see many practical examples, later on. In this part of the series, we learn the operators of a search condition.
In a search condition you may need what is called a comparison operator. There are a good number of them, whose symbols I give here and their meanings:
= equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
!= not equal to
An example of a search-condition is:
price > 0
where price is the column name for price. In the column-definition when creating a table, you would type this in the CHECK constraint as,
CHECK (price > 0)
This means, each price value in the price column in the table should always be greater than zero (it is not the alphabet O).
Note: Search-conditions are used in many places; not only in the CHECK constraint.
For simplicity, consider a NULL value as an empty cell in a table. Examples of ways you can use NULL in conditions are as follows, where salary is the name of the column:
Salary IS NULL
Salary IS NOT NULL
Here, NULL, NOT, and IS are reserved words.
Another kind of operators falls in the class of logical operators. The names of the operators are, AND, OR and NOT. These three words are reserved words.
You may be dealing with the customer table and you want rows whose CustonerIDs are greater than 5 and at the same time less than 10; you would type the following:
(CustomerID > 5) AND (CustomerID < 10)
The use of the AND operator can be that simple. Looking at this piece of code, there is an expression on the left of the AND operator in brackets, and an expression on the right of the operator in brackets. You use brackets for expressions that you want to be computed first. Always use brackets like this, otherwise the order in which the whole expression will be computed will not be what you want, and you will have wrong answers. This use of brackets is applicable to the OR and NOT operators, as well.
If from the customer table, you want rows whose CustomerIDs are less than 5 OR greater than 10, you would type:
(CustomerID < 5) OR (CustomerID > 10)
If you want rows whose CustomerIDs are not less than 5, you would type:
NOT (CustomerID < 5)
There are other reserved words used in search-conditions. You will see them as we go along in the series. Let us continue in the next part.
Related LinksImplementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course