Operators

Operators are the building blocks of complex queries. Logical operators (such as AND and OR) allow you to relate numbers of conditions in various ways. Arithmetic operators (such as are + or *) allow you to perform basic mathematical operations in your queries. Comparison operators (such as > or <) allow you to compare values, and narrow result sets in this way. Finally, bit operators, while not often used, allow you to work at a bit level in your queries.

Logical Operators

Logical operators reduce to either true (1) or false (0). For example, if you are male, and I ask whether you're male OR female (assume I'm asking for a yes/no answer), the answer would be yes, or true. If I ask whether you're male AND female, the answer would be no, or false. The AND and OR in the questions are logical operators. Table 3.1 describes the operators in more detail.

Table 3.1: Logical Operators

Operator

Syntax

Description

AND, &&

c1 AND c2, c1 && c2

Only true if both conditions c1 and c2 are true.

OR, ||

c1 OR c2, c1 || c2

True if either c1 or c2 is true.

!, NOT

! c1, NOT c1

True if c1 is false, false if c1 is true.

Instead of populating a table and running queries against this, the following examples will produce either a 1 or a 0. Each row in the tables you query will also reduce to a 1 or a 0. 1s will be returned, and 0s will not be. If you understand this, you can apply the principles to any of your own tables. If you're going through these operators for the first time, see if you can predict the results based on Table 3.1.

mysql> SELECT 1 AND 0; +---------+ | 1 AND 0 | +---------+ |       0 | +---------+ mysql> SELECT NOT(1 AND 0); +--------------+ | NOT(1 AND 0) | +--------------+ |            1 | +--------------+ mysql> SELECT !((1 OR 0) AND (0 OR 1)); +--------------------------+ | !((1 OR 0) AND (0 OR 1)) | +--------------------------+ |                        0 | +--------------------------+

Remember that conditions inside the innermost parentheses are evaluated first. So, MySQL simplifies the complex statement in the previous example as follows:

!((1 OR 0) AND (0 OR 1)) !((1) AND (1)) !(1) 0 

Arithmetic Operators

Arithmetic operators are used to perform basic mathematical operations. For example, when I say that 2 + 3 = 5, the plus sign (+) is an arithmetic operator. Table 3.2 describes the arithmetic operators available in MySQL.

Table 3.2: Arithmetic Operators

Operator

Syntax

Description

+

a + b

Adds a and b together, returning the sum of both

-

a - b

Subtracts b from a, returning the difference

*

a * b

Multiplies a and b, returning the product of both

/

a / b

Divides a by b, returning the quotient

%

a % b

a modulus b, returning the remainder after a / b

For example, adding together two columns of type INT will produce an INT:

mysql> SELECT 2+1; +-----+ | 2+1 | +-----+ |   3 | +-----+ mysql> SELECT 4-2/4; +-------+ | 4-2/4 | +-------+ |  3.50 | +-------+

This returns 3.5, not 0.5, because the division is performed first (remember the rules of precedence you learned at school?). However, you should always use parentheses to make clear which operations are to be performed first to someone who doesn't know the rules. To make the previous query simpler, rewrite it as follows:

mysql> SELECT 4-(2/4); +---------+ | 4-(2/4) | +---------+ |    3.50 | +---------+ 
Note 

Even though all the values in this query are integers, because the result has a non-integer element, it is returned as a floating-point number.

This next example demonstrates the modulus operator:

mysql> SELECT 5 % 3; +-------+ | 5 % 3 | +-------+ |     2 | +-------+

Modulus returns the remainder of a division. In the previous example, 5 divided by 3 is 1, remainder 2.

Comparison Operators

Comparison operators are used when making comparisons between values. For example, I can make the statement that 34 is greater than 2. The is greater than part is a comparison operator. Table 3.3 lists and describes the comparison operators used in MySQL.

Table 3.3: Comparison Operators

Operator

Syntax

Description

=

a = b

True if both a and b are equal (excluding NULL).

!=, <>

a != b, a <> b

True if a is not equal to b.

>

a > b

True if a is greater than b.

<

a < b

True if a is less than b.

>=

a >= b

True if a is greater than or equal to b.

<=

a <= b

True if a is less than or equal to b.

<=>

a <=> b

True if a and b are equal (including NULL).

IS NULL

a is NULL

True if a contains a NULL value.

IS NOT NULL

a IS NOT NULL

True if a does not contain a NULL value.

BETWEEN

a BETWEEN b and c

True if a is between the values of b and c, inclusive.

NOT BETWEEN

a NOT BETWEEN b and c

True if a is not between the values of b and c, inclusive.

LIKE

a LIKE b

True if a matches b on an SQL pattern match.

NOT LIKE

a NOT LIKE b

True if a does not match b on an SQL pat-tern match. The two acceptable wildcard characters are % (which means any number of characters) and _ (which means one character).

IN

a IN (b1, b2, b3…)

True if a is equal to anything in the list.

NOT IN

a NOT IN (b1,b2,b3…)

True if a is not equal to anything in the list.

REGEXP, RLIKE

a REGEXP b, a RLIKE b

True if a matches b with a regular expression.

NOT REGEXP, NOT RLIKE

a NOT REGEXP b, a NOT RLIKE B

True if a does not match b with a regular expression.

With comparison operators, you're also going to use 1 and 0 to represent true and false. Remember you'll be replacing these with constants of your own and fields from database tables. For example, take a table with two rows, as shown in Figure 3.1.


Figure 3.1: TABLE1

The following code represents the = comparison operator:

SELECT * FROM TABLE1 WHERE FIELD1 = 13.

Each row in the table is then compared to see whether the condition is true or false. For the first row, the expression reduces to this:

15 = 13

This is false, so the row is not returned. For the second row, the expression reduces to the following:

13 = 13

This is true, so this time the row is returned.

Once you understand this, you can apply it to your own tables.

Another example:

mysql> SELECT 13=11; +-------+ | 13=11 | +-------+ |   0   | +-------+ 

If you come from a programming background, you'll probably know the complexities of comparing different types (such as strings with numbers). For example, what sort of answer do you expect when you ask if the string 'thirty' is less than the number 29? MySQL tries to be as helpful as possible when you want to compare values of different types (converting the types as well as it can, called type conversions). If you are comparing strings and numerics, or floating-point numbers and integers, MySQL will compare them as if they were the same type. For example:

mysql> SELECT '4200' = 4200.0; +-----------------+ | '4200' = 4200.0 | +-----------------+ |               1 | +-----------------+

The string '4200' converted to a number is equal to 4200.0. However, the two strings '4200' and '4200.0' are not the same:

mysql> SELECT '4200' = '4200.0'; +-------------------+ | '4200' = '4200.0' | +-------------------+ |                 0 | +-------------------+

The next example demonstrates the case-insensitive nature of string comparisons:

mysql> SELECT 'abc' = 'ABC'; +---------------+ | 'abc' = 'ABC' | +---------------+ |             1 | +---------------+

In the following example, a trailing space is ignored for purposes of equality in a case-insensitive search:

mysql> SELECT 'abc' = 'ABC '; +---------------+ | 'abc' = 'ABC' | +---------------+ |             1 | +---------------+ 1 row in set (0.00 sec)

The following example is an important one to note; the result is not 0 (false), it's NULL:

mysql> SELECT NULL=0; +--------+ | NULL=0 | +--------+ |   NULL | +--------+ 

To evaluate NULL rows, you'd need to use the following:

mysql> SELECT NULL<=>0; +----------+ | NULL<=>0 | +----------+ |        0 | +----------+

NULL is basically a third possible result of an evaluation: There's true, false, and then there's NULL. None of the following queries provides useful results when compared to a NULL value:

mysql> SELECT 200 = NULL, 200 <> NULL, 200 < NULL, 200 > NULL; +------------+-------------+------------+------------+ | 200 = NULL | 200 <> NULL | 200 < NULL | 200 > NULL | +------------+-------------+------------+------------+ |       NULL |        NULL |       NULL |       NULL | +------------+-------------+------------+------------+

You need to use the IS NULL (or IS NOT NULL) comparison instead:

mysql> SELECT NULL IS NULL; +--------------+ | NULL IS NULL | +--------------+ |            1 | +--------------+ mysql> SELECT 4.5 BETWEEN 4 and 5; +---------------------+ | 4.5 BETWEEN 4 and 5 | +---------------------+ |                   1 | +---------------------+

The next example demonstrates a common mistake when using BETWEEN.

mysql> SELECT 5 BETWEEN 6 and 4; +-------------------+ | 5 BETWEEN 6 and 4 | +-------------------+ |                 0 | +-------------------+
Warning 

MySQL does not sort the two values after a BETWEEN, so if you get the order wrong, the results for all rows will be false. Make sure that the first number is the lower number.

Because a appears earlier in the alphabet than b, the result of the following is true. String comparisons are performed from left to right, one character at a time:

mysql> SELECT 'abc' < 'b'; +-------------+ | 'abc' < 'b' | +-------------+ |           1 | +-------------+ 

In the following example, b is less than or equal to b; however, the next b is not less than or equal to nothing (the second character on the right string):

mysql> SELECT 'bbc' <= 'b'; +--------------+ | 'bbc' <= 'b' | +--------------+ |            0 | +--------------+

The IN() function can be used to test one value against a number of possible values. The field can match any of the comma-separated values listed inside the parentheses, as demonstrated in the next example:

mysql> SELECT 'a' IN ('b','c','a'); +----------------------+ | 'a' in ('b','c','a') | +----------------------+ |                    1 | +----------------------+

Using LIKE with SQL Pattern Matches

Often used together with the comparison operators are the wildcard characters, listed in Table 3.4. These allow you to compare against a character (or number of characters) you're not sure about instead of specific ones you know.

Table 3.4: Wildcard Characters

Character

Description

%

Any number of characters

_

One character

The following example demonstrates the usage of the % wildcard:

mysql> SELECT 'abcd' LIKE '%bc%'; +--------------------+ | 'abcd' LIKE '%bc%' | +--------------------+ |                  1 | +--------------------+ 

The % wildcard returns any number of characters, so the following would also match:

mysql> SELECT 'abcd' LIKE '%b%'; +-------------------+ | 'abcd' LIKE '%b%' | +-------------------+ |                 1 | +-------------------+ mysql> SELECT 'abcd' LIKE 'a_ _ _'; +----------------------+ | 'abcd' LIKE 'a_ _ _' | +----------------------+ |                  1   | +----------------------+

An underscore (_) matches only one character exactly, so if you'd only had two underscores, instead of the three as shown, it would not have matched. For example:

mysql> SELECT 'abcd' LIKE 'a_ _'; +--------------------+ | 'abcd' LIKE 'a_ _' | +--------------------+ |                 0  | +--------------------+

Regular Expressions

Regular expressions allow you to perform complex comparisons in MySQL. Hearing the term regular expressions, for many people, is like mentioning the plague to a medieval doctor. Immediately the face frowns, the excuses are prepared, and all hope is lost. And it can be complicated—entire books have been written on the topic. But using them in MySQL is not difficult, and they add a useful flexibility to comparisons in MySQL. Table 3.5 describes the regular expression operators available in MySQL.

Table 3.5: Regular Expressions (REGEXP, RLIKE)

Character

Description

*

Matches zero or more instances of the string preceding it

+

Matches one or more instances of the string preceding it

?

Matches zero or one instances of the string preceding it

.

Matches any single character

[xyz]

Matches any of x, y, or z (the characters within the brackets)

[A-Z]

Matches any uppercase letter

[a-z]

Matches any lowercase letter

[0-9]

Matches any digit

^

Anchors the match from the beginning

$

Anchors the match to the end

|

Separates strings in the regular expression

{n,m}

String must occur at least n times, but no more than n

{n}

String must occur exactly n times

{n,|

String must occur at least n times

Regular expression matches (REGEXP) can produce similar results to SQL matches (LIKE), but there are some important differences, too. A regular expression, unless you specify otherwise, matches anywhere in the string. It is unnecessary to use wildcard characters on either side, as with LIKE. Note the difference between these two results:

mysql> SELECT 'abcdef' REGEXP 'abc'; +-----------------------+ | 'abcdef' REGEXP 'abc' | +-----------------------+ |                     1 | +-----------------------+ mysql> SELECT 'abcdef' LIKE 'abc'; +---------------------+ | 'abcdef' LIKE 'abc' | +---------------------+ |                   0 | +---------------------+

To get the equivalent with LIKE, you'd have had to use % wildcards at the end:

mysql> SELECT 'abcdef' LIKE 'abc%'; +----------------------+ | 'abcdef' LIKE 'abc%' | +----------------------+ |                    1 | +----------------------+

The following matches where a is the first character:

mysql> SELECT 'abc' REGEXP '^a'; +-------------------+ | 'abc' REGEXP '^a' | +-------------------+ |                 1 | +-------------------+ 

However, the following does not match, as the plus sign (+) indicates that g had to appear one or more times:

mysql> SELECT 'abcdef' REGEXP 'g+'; +----------------------+ | 'abcdef' REGEXP 'g+' | +----------------------+ |                    0 | +----------------------+

The next query does match because the asterisk (*) indicates zero or more. Effectively, this would match anything:

mysql> SELECT 'abcdef' REGEXP 'g*'; +----------------------+ | 'abcdef' REGEXP 'g*' | +----------------------+ |                    1 | +----------------------+

We could also try use the asterisk to try matching against the name ian or the alternative spelling, iain. Anything other letter after a should cause the match to fail. For example:

mysql> SELECT 'ian' REGEXP 'iai*n'; +----------------------+ | 'ian' REGEXP 'iai*n' | +----------------------+ |                    1 | +----------------------+

But the problem is that this also matches 'iaiiiin', as the asterisk matches any number of characters, as follows:

mysql> SELECT 'iaiiiiin' REGEXP 'iai*n'; +---------------------------+ | 'iaiiiiin' REGEXP 'iai*n' | +---------------------------+ |                         1 | +---------------------------+

To correct this, you'd have to limit the match on the 'i' to either zero or one. Changing the asterisk to a question mark character achieves this. It still matches 'ian' and 'iain,' but not 'iaiiin,' as follows:

mysql> SELECT 'iaiiiiin' REGEXP 'iai?n'; +---------------------------+ | 'iaiiiiin' REGEXP 'iai?n' | +---------------------------+ |                         0 | +---------------------------+ 

The following matches because {3,} means the a must occur at least three times:

mysql> SELECT 'aaaa' REGEXP 'a{3,}'; +-----------------------+ | 'aaaa' REGEXP 'a{3,}' | +-----------------------+ |                     1 | +-----------------------+

At first glance, you may think the following should not match because the a matches four times, but {3} means it should match exactly three times. It does, however, match three times, as well as twice, once, and four times:

mysql> SELECT 'aaaa' REGEXP 'a{3}'; +----------------------+ | 'aaaa' REGEXP 'a{3}' | +----------------------+ |                    1 | +----------------------+

If you'd wanted only aaa to match, you'd need to do the following:

mysql> SELECT 'aaaa' REGEXP '^aaa$'; +-----------------------+ | 'aaaa' REGEXP '^aaa$' | +-----------------------+ |                     0 | +-----------------------+

The caret (^) anchors the start, and the dollar sign ($) the end; omitting either would cause the match to succeed.

The following match fails because the {3} only refers to the c, not the entire abc :

mysql> SELECT 'abcabcabc' REGEXP 'abc{3}'; +-----------------------------+ | 'abcabcabc' REGEXP 'abc{3}' | +-----------------------------+ |                           0 | +-----------------------------+

So, the following does match:

mysql> SELECT 'abccc' REGEXP 'abc{3}'; +-------------------------+ | 'abccc' REGEXP 'abc{3}' | +-------------------------+ |                       1 | +-------------------------+ 

To match abcabcabc, you need to use parentheses, as follows:

mysql> SELECT 'abcabcabc' REGEXP '(abc){3}'; +-------------------------------+ | 'abcabcabc' REGEXP '(abc){3}' | +-------------------------------+ |                             1 | +-------------------------------+

Note the difference between curly braces and square brackets in the next example. The curly braces group abc as a whole, and the square brackets would have allowed any of a, or b, or c to match, allowing a whole range of other possibilities, such as the following:

mysql> SELECT 'abcbbcccc' REGEXP '[abc]{3}'; +-------------------------------+ | 'abcbbcccc' REGEXP '[abc]{3}' | +-------------------------------+ |                             1 | +-------------------------------+

The following uses parentheses to achieve the same effect, with the vertical bar character (|) being used to group alternate substrings:

mysql> SELECT 'abcbbcccc' REGEXP '(a|b|c){3}'; +---------------------------------+ | 'abcbbcccc' REGEXP '(a|b|c){3}' | +---------------------------------+ |                               1 | +---------------------------------+

Bit Operators

To understand how bit operations work, you'll need to know a little bit about Boolean numbers and Boolean arithmetic. These kinds of queries aren't often used, but any self-respecting "guru2be" needs to have them as part of their repertoire. Table 3.6 describes the bit operators.

Table 3.6: Bit OPerators

Operator

Syntax

Description

&

a & b

Bitwise AND

|

a | b

Bitwise OR

<<

a << b

Left shift of a by b bit positions

>>

a >> b

Right shift of a by b bit positions

The ordinary number system, called the decimal number system, works off a base 10. You have 10 fingers, after all, so it makes sense. You count from zero to nine, and then when you hit 10, you move to the "tens" column, and start at zero again:

00 01 02 03 04 05 06 07 08 09 10

The decimal number system has 10 digits, from zero to nine. But people working with computers have often found it useful to work with a number system based on two digits, zero and one. These represent the two states of an electrical connection, on and off:

00 01 10 11

Instead of moving to the "tens" column when you run out of digits (in decimal, after 9 comes 10), you move into the "twos" column (in binary, after 1 comes 10, which is pronounced "one-zero" to avoid confusion with the decimal number).

In decimal, the columns increase in size by powers of 10, as shown in Figure 3.2.

click to expand
Figure 3.2: Powers of 10

So the number in this figure, which reads as four million, three hundred ninety-two thousand, four hundred and twenty-one could also be displayed as:

4 * 100 000 000 +

3 * 100 000 +

9 * 10 000 +

2 * 1000 +

4 * 100 +

2 * 10 +

1 * 1

If you can follow all that (imagining that you are a child learning to count in decimal helps), you'll see how to apply the same concepts to binary numbers.

In binary, columns increase in size by powers of 2, as shown in Figure 3.3.


Figure 3.3: Powers of 2

The previous binary number—1111111—when converted to decimal, reads as the following:

1 * 64 +

1 * 32 +

1 * 16 +

1 * 8 +

1 * 4 +

1 * 2 +

1 * 1

which is equivalent to 64 + 32 + 16 + 8 + 4 + 2 + 1, which in turn is 127.

Similarly, the binary number 101001 would be 1 * 1 + 1 * 8 + 1 * 32 = 41.

So, converting binary numbers to decimal is easy enough, but how about the reverse, converting decimal numbers to binary? It's equally simple. To convert the number 18 to binary, start with Figure 3.4.


Figure 3.4: Step 1, Drawing up the columns

Starting on the left, there are clearly no 64s in 18, and no 32s. There is, however, one 16 in 18. So you write a 1 in the 16 column, as shown in Figure 3.5.


Figure 3.5: Step 2, Filling in the values

You've accounted for 16 of your 18, so you now subtract 16 from 18, leaving you with 2. Continuing to the right, there are no 8s, no 4s, and one 2, in 2. And since 2 minus 2 is 0, you stop once you've written the 1 in the two column, as shown in Figure 3.6.


Figure 3.6: Step 3, Converting decimal to binary

In binary, 18 is then 10010. With larger numbers, you just use more columns to the left (representing 128s, 256s, and so on). Binary numbers can get very long very quickly, which is why you don't usually store numbers that way. Octal (base 8) and hexadecimal (base 16) are two other number systems that are more convenient.

Let's get back to the bit operators; take two numbers, 9 and 7. In binary they are 1001 and 111, respectively. The bit operators work on the individual bits of the binary number that make up the numbers 8 and 7.

For a bitwise AND operation, both bits need to be 1 for the result to be 1 (just like an ordinary AND). Figure 3.7 shows the two binary numbers.


Figure 3.7: Bitwise AND operation: 9&7

Starting on the left, 1 AND 0 is 0, so the leftmost column (the eights) is 0. Moving right, 0 AND 1 is 0, and again, 0 AND 1 is 0. Only in the rightmost column do you see 1 AND 1, which is 1.

So, the result of a bitwise AND between 7 and 9 is 1! For example:

mysql> SELECT 9&7; +-----+ | 9&7 | +-----+ |   1 | +-----+
Note 

A bitwise AND is the same no matter which way around you do it—in other words, 9&7 is the same as 7&9.

For a bitwise OR, either digit should be 1 for the result to be 1. So, Figure 3.8 shows a bitwise OR performed on the same 9 and 7.


Figure 3.8: Bitwise OR operation: 9|7

All columns have at least one 1 present, so the result for each is a 1 overall. And 1111 is equivalent to 15 in binary:

mysql> SELECT 9|7; +-----+ | 9|7 | +-----+ |  15 | +-----+ 

The << is the left shift operator, so << b means that the bits of a are shifted left by b columns. For example: 2 << 1. In binary, 2 is 10. If this is shifted left 1 bit, you get 100, which is 4. For example:

mysql> SELECT 2 << 1; +--------+ | 2 << 1 | +--------+ |      4 | +--------+ mysql> SELECT 15 << 4; +---------+ | 15 << 4 | +---------+ |     240 | +---------+

Now you have 15, which is 1111; when shifted 4 bits left, you get 11110000. Convert this to decimal in the usual way, as in Figure 3.9.

click to expand
Figure 3.9: Converting the binary number 11110000 to decimal

Now total this:

128 + 64 + 32 + 16 = 240

Bitwise operations are performed as BIGINTs, meaning there's a limit of 64 bits. Shifting beyond 64 bits, or with a negative number, just returns 0. For example:

mysql> SELECT 3 << 64; +---------+ | 3 << 64 | +---------+ |       0 | +---------+

The >> is the right shift operator, so a >> b shifts the bits of a right by b columns. Bits shifted beyond the "ones" column are lost. And, again, shifting by a negative number returns 0.
For example:

mysql> SELECT 3 >> 1; +--------+ | 3 >> 1 | +--------+ |      1 | +--------+ 

In binary, 3 is 11, shifted right by 1 with 1 floating past the ones column (or 1.1 if you'd like, although there is no decimal point in binary notation). Because you're dealing with integers, the numbers to the right of the "decimal point" are dropped (perhaps we should call it the binary point, but there's probably a Hollywood movie coming out by that name), and you're left with 1 (in both binary and decimal). For example:

mysql> SELECT 19 >> 3; +---------+ | 19 >> 3 | +---------+ |       2 | +---------+

In the example above, 19 is 10011, shifted right by 3 is 10, with 011 dropping away. 10 is 2 in decimal.

mysql> SELECT 4 >> 3; +--------+ | 4 >> 3 | +--------+ |      0 | +--------+

This one shifts too far to the right, losing all the bits.



Mastering MySQL 4
Mastering MySQL 4
ISBN: 0782141625
EAN: 2147483647
Year: 2003
Pages: 230
Authors: Ian Gilfillan

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net