Search Conditions in MySQL SQL Statements
Implementing Database in MySQL – Part 6
Division 4
Forward: In this part of the series, we look at search conditions in the MySQL SQL Statements.
By: Chrysanthus Date Published: 6 Aug 2012
Introduction
We saw the following syntax in the previous part of the series:
CHECK (expr)
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.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Comparison Operators
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 a column:
Salary IS NULL
Salary IS NOT NULL
Here, NULL, NOT, and IS are reserved words.
Logical Operators
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 wholes 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 a statement 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.
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. We shall see them as we go along in the series. Let us continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT