Comparison between MySQL operators and obtaining more related data

The following article describes how to compare the actual operations of MySQL operators with the functions of obtaining more related data. If you have recently queried SELECT or UPDATE, in practice, you may have used one or more MySQL (the best combination with PHP) Comparison MySQL operators to limit the query output results.

Comparison is an integral part of most SELECT queries, while MySQL (the best combination with PHP) provides many functions for this function. According to the last statistics, it has more than 20 such operators and functions, from the famous = and LIKE to the more difficult not in and STRCMP ().

This article focuses on some less common MySQL (the best combination with PHP) data comparison functions and operators, discusses how they can be used to compare strings, numbers, dates, times, or user-supplied values in table fields in an application.

BETWEEN

The BETWEEN operator is a useful method to test whether a value or a date value exists in a certain range. This MySQL operator accepts two parameters: maximum and minimum, and tests whether the provided values are within the range of these two values. If it is within this range, the operator returns a Boolean Value -- true; otherwise, it returns a pseudo value. The following is an example:

 
 

  
  
MySQL (best combination with PHP)> SELECT 2350 BETWEEN 100 AND 10000;
  
  
+ ---------------------------- +
  
  
| 2350 BETWEEN 100 AND 10000 |
  
  
+ ---------------------------- +
  
  
| 1 |
  
  
+ ---------------------------- +
  
  
1 row in set (0.18 sec)
 
 

The following two examples use the date value:

 
 

  
  
MySQL (best combination with PHP)> SELECT 20060405 BETWEEN 20060101 AND 20070101;
  
  
+ ---------------------------------------- +
  
  
| 20060405 BETWEEN 20060101 AND 20070101 |
  
  
+ ---------------------------------------- +
  
  
| 1 |
  
  
+ ---------------------------------------- +
  
  
1 row in set (0.00 sec)
  
  
MySQL (best combination with PHP)> SELECT 20060405 BETWEEN 20060101 AND 20060401;
  
  
+ ---------------------------------------- +
  
  
| 20060405 BETWEEN 20060101 AND 20060401 |
  
  
+ ---------------------------------------- +
  
  
| 0 |
  
  
+ ---------------------------------------- +
  
  
1 row in set (0.00 sec)
  
  
GREATEST and LEAST
 
 

The GREATEST and LEAST operators provide a convenient way to determine the maximum and minimum values of a group of numbers or time values. When you look at the names, you will know the functions of these two MySQL operators-The following is an example of using the GREATEST operator for a group of dates:

 
 

  
  
MySQL (best combination with PHP)> select greatest (20000601,200 20000604 );
  
  
+ ---------------------------------------- +
  
  
| GREATEST (20000601,200 20000604,) |
  
  
+ ---------------------------------------- +
  
  
| 1, 20000604 |
  
  
+ ---------------------------------------- +
  
  
1 row in set (0.00 sec)
 
 

The following is an example of using the LEAST operator for a set of values:

 
 

  
  
MySQL (best combination with PHP)> select least (100,200, 50,-6,-73,100 0 );
  
  
+ ------------------------------------ +
  
  
| LEAST (100,200, 50,-6,-73,100 0) |
  
  
+ ------------------------------------ +
  
  
|-73 |
  
  
+ ------------------------------------ +
  
  
1 row in set (0.03 sec)
 
 

The above content introduces more data comparison functions for MySQL operators.