Get more data compare features with special MySQL operators

Source: Internet
Author: User
Tags range

If you have recently made a select or update query, you may have used one or more MySQL comparison operators to limit the query's output. Comparisons are an integral part of most select queries, and MySQL provides many functions for this function; According to the most recent statistics, it has more than 20 such operators and functions, from the famous = and like to the more difficult to understand not in and strcmp ().

This article focuses on some of the less common data comparison functions and operators, discussing how they can be used in the application to compare strings, numbers, and date/time or user-supplied values in a table field.

BETWEEN

The between operator is a useful way to test whether a numeric or date value exists within a range. This operator accepts two parameters-both the maximum and the minimum-and tests whether the supplied value is within the range of the two values. If within this range, the operator returns a Boolean value-true; otherwise, it returns a pseudo value. Here's an example:

Mysql> SELECT 2350 BETWEEN and 10000;

+----------------------------+

| 2350 BETWEEN 10000 |

+----------------------------+

| 1 |

+----------------------------+

1 row in Set (0.18 sec)

The following two examples use the date value:

mysql> SELECT 20060405 BETWEEN 20060101 and 20070101;

+----------------------------------------+

| 20060405 BETWEEN 20060101 and 20070101 |

+----------------------------------------+

| 1 |

+----------------------------------------+

1 row in Set (0.00 sec)

mysql> 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 for a set of numbers or time values. You'll know the effect of these two operators as soon as you see the name--here is an example of using the greatest operator for a set of dates:

Mysql> SELECT Greatest (20000601, 20000529, 20000604);

+----------------------------------------+

| Greatest (20000601, 20000529, 20000604) |

+----------------------------------------+

| 20000604 |

+----------------------------------------+

1 row in Set (0.00 sec)

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

mysql> SELECT Least (100, 200, 50,-6,-73, 1000);

+------------------------------------+

| Least (100, 200, 50,-6,-73, 1000) |

+------------------------------------+

| -73 |

+------------------------------------+

1 row in Set (0.03 sec)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.