Operators - Appendixes - MySQL in a Nutshell (2008)

MySQL in a Nutshell (2008)

Part V. Appendixes

Appendix B. Operators

Operators are used in mathematical or logical operations. An operator is typically placed between two values (i.e., numbers, strings, columns, or expressions) that you want to compare or evaluate. There are four types of operators: arithmetic, relational, logical, and bitwise. This appendix provides a listing of operators grouped into these four types. This appendix also includes a list of special pattern-matching characters and constructs for regular expressions.

Arithmetic Operators

The arithmetic operators in MySQL work only on numbers, not on strings. However, MySQL will convert a string into a number when in a numeric context if it can. If it can’t convert a particular string, it will return 0. Table B-1 lists the arithmetic operators allowed.

The minus sign may be used for subtracting numbers or for setting a number to a negative. The DIV operator converts values to integers and returns only integers. It doesn’t round fractions that would be returned, but rather truncates them.

Table B-1. Arithmetic operators

Operator

Use

+

Addition

Subtraction and negation

*

Multiplication

/

Division

DIV

Division of integers

%

Modulo division

Relational Operators

Relational operators are used for comparing numbers and strings. If a string is compared to a number, MySQL will try to convert the string to a number. If a TIMESTAMP column is compared to a string or a number, MySQL will attempt to convert the string or number to a timestamp value. If it’s unsuccessful at converting the other value to a timestamp, it will convert the TIMESTAMP column’s value to a string or a number. TIME and DATE columns are compared to other values as strings. Table B-2 lists the logical and relational operators allowed in MySQL.

The minus sign may be used for subtracting numbers or for setting a number to a negative. The equals sign is used to compare two values. If one value is NULL, though, NULL will be returned. The <=> operator is used to compare values for equality; it’s NULL-safe. For example, an SQL statement containing something like IF(col1 <=> col2), where the values of both are NULL, will return 1 and not NULL.

Table B-2. Relational operators

Operator

Use

<

Less than

>

Greater than

<=

Less than or equal to

>=

Greater than or equal to

expression BETWEEN n AND n

Between first and second number

expression NOT BETWEEN n AND n

Not between first and second number

IN (...)

In a set

NOT IN (...)

Not in a set

=

Equal to

<=>

Equal to (for comparing NULL values)

LIKE

Matches a pattern

SOUNDS LIKE

Matches a sound pattern (see SOUNDEX() function described in Chapter 11)

NOT LIKE

Doesn’t match a pattern

REGEXP, RLIKE

Matches a regular expression

NOT REGEXP

Doesn’t match a regular expression

!=

Not equal to

<>

Not equal to

NOT, !

Negates

IS NULL

NULL

IS NOT NULL

Not NULL

Logical Operators

Logical operators are used for evaluating values or expressions for true, false, or unknown. Table B-3 lists allowable logical operators.

The operators IS and IS NOT are added in version 5.0.2 of MySQL. A boolean value of TRUE, FALSE, or UNKNOWN should immediately follow these operators.

Table B-3. Logical operators

Operator

Use

AND

Logical AND

&&

Logical AND

IS boolean

Logical equal

IS NOT boolean

Logical equal

OR

Logical OR

||

Logical OR

NOT

Logical NOT

!

Logical NOT

XOR

Logical XOR

Bitwise Operators

Bitwise operators are used for comparing numbers based on their binary digits. These operators are listed in Table B-4.

The tilde (~) may be used to invert the bits of a value.

Table B-4. Bitwise operators

Operator

Use

|

OR

^

XOR

&

AND

<<

Shift bits to left

>>

Shift bits to right

~

NOT or invert bits

Regular Expressions

When using the operators REGEXP, RLIKE, and NOT REGEXP, you may need special characters and parameters to be able to search for data based on regular expressions. Table B-5 lists the special characters, and Table B-6 shows special constructs that may be used. In keeping with convention, patterns to match are given within quotes. As an example of a regular expression used with a SELECT statement, suppose that we want to find the name of a particular student in a college’s database, but we can’t quite remember his last name. All we remember is that it’s something like Smith, but it could be Smithfield or maybe Smyth. We could run an SQL statement like the following to get a list of possibilities:

SELECT student_id,

CONCAT(name_first, SPACE(1), name_last) AS Student

FROM students

WHERE name_last REGEXP 'Smith.*|Smyth';

As an example using a pattern-matching construct, suppose that we suspect there are a few student records in which the name columns contain numeric characters. Suppose also that there are some student records in which the social_security column contains characters other than numbers or dashes. We could search for them by executing an SQL statement like the following:

SELECT student_id, soc_sec,

CONCAT(name_first, SPACE(1), name_last) AS Student

FROM students

WHERE CONCAT(name_first, name_last) REGEXP '[[:digit:]]+'

OR soc_sec REGEXP '[[:alpha:]]+';

As an example of a construct using a character name, suppose that the column containing Social Security tax identification numbers (i.e., soc_sec) shouldn’t contain the usual hyphen separator (i.e., 443-78-8391). We could enter an SQL statement like the following to find records with hyphens in that column:

SELECT student_id, soc_sec,

CONCAT(name_first, SPACE(1), name_last) AS Student

FROM students

WHERE soc_sec REGEXP '[[.hyphen.]]+';

To find any rows that do not specifically meet the format for the Social Security number (i.e., nnn-nn-nnnn), we could use this longer but more specific regular expression:

SELECT student_id, soc_sec,

CONCAT(name_first, SPACE(1), name_last) AS Student

FROM students

WHERE soc_sec NOT REGEXP

'[[:digit:]]{3}[[.hyphen.]]{1}[[:digit:]]{2}[[.hyphen.]]{1}[[:digit:]]{4}';

Notice that this statement uses the curly braces after each construct to specify the exact number of characters or digits permitted.

Table B-5. Pattern-matching characters

Character

Use

^

Matches the beginning of the string.

$

Matches the beginning of the string.

.

Matches any character, space, or line ending.

*

Matches zero or more of the characters immediately preceding.

+

Matches one or more of the characters immediately preceding.

?

Matches zero or one of the characters immediately preceding.

|

An OR operator; matches the characters before or after it (e.g., 'Russell|Rusty').

(characters)*

Matches zero or more occurrences of the sequence of characters given in parentheses.

{number}

Specifies the number of occurrences of the previous pattern given.

{number,number}

Specifies the minimum number of occurrences of the previous pattern given, followed by the maximum number of occurrences. If only the minimum number is omitted, 0 is assumed. If just the maximum number is omitted, unlimited is assumed.

[x-x]

Specifies a range of characters in alphabetical order (e.g., '[a-g]' for the first seven lowercase letters), or numbers in numeric sequence (e.g., '[0-9]' for all numbers).

Table B-6. Pattern-matching constructs

Construct

Use

[.character.]

Matches the given character or character name (e.g., backslash, carriage return, newline, tab).

[=character=]

Matches characters of the same class as the character given.

[[:<:]]

Matches the beginning of a word.

[[:>:]]

Matches the end of a word.

[:alnum:]

Matches alphanumeric characters.

[:alpha:]

Matches alphabetical characters.

[:blank:]

Matches a blank or whitespace characters.

[:cntrl:]

Matches control characters.

[:digit:]

Matches digits.

[:lower:]

Matches lowercase alphabetical characters.

[:print:]

Matches graphic and space characters.

[:punct:]

Matches punctuation characters.

[:space:]

Matches space, carriage return, newline, and tab characters.

[:upper:]

Matches uppercase alphabetical characters.

[:xdigit:]

Matches hexadecimal characters.