Use special MySQL operators to obtain more data comparison functions

Source: Internet
Author: User

Use special MySQL operators to obtain more data comparison functions

If you have recently performed SELECT or UPDATE queries, you may have used one or more MySQL comparison operators to limit the query output results. Comparison is an integral part of most SELECT queries, and MySQL 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 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 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> 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> 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 of a group of numbers or time values. When you look at the names, you will know the functions of these two operators-The following is an example of using the GREATEST operator for a group of dates:

Mysql> 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> 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)

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.