Search Conditions in Mysql Sql Statements

Implementing Database in MySQL – Part 6

Introduction
This is part 6 of my series, Implementing Database in MySQL. I assume you have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at search conditions in the MySQL SQL Statements.

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.

NULL in Conditions
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

Tutorials in the Series
1 Implementing Database in MySQL
2 Creating a Database in MySQL
3 MySQL Data Types
4 MySQL Data Definition Basics
5 MySQL CREATE TABLE SQL Statement
6 Search Conditions in MySQL SQL Statements
7 Altering and Dropping Tables in MySQL
8 Creating User and granting rights in MySQL
9 Data Manipulation in MySQL
10 Querying MySQL Database
11 The SELECT Statement Basics in MySQL
12 Implementing Computed Values and Sub Queries in MySQL

The Must Know in Database Design and MySQL Server
The Must Know in Database Design and MySQL Server

About Author

Leave A Reply