Operations and Expressions

Part:   1  2  3  

(Continued from previous part...)

Operation Examples

Example 1 - NumericOps.sql

-- NumericOps.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT 1 + 2 AS LINE_1;
SELECT 2 / 3 AS LINE_2;
SELECT 2.0000 / 3 AS LINE_3;
SELECT 1234567890/10000 AS LINE_4;
SELECT 1234567890/1000000000 AS LINE_5;
SELECT 1234567890/1e10 AS LINE_6;
SELECT 1234567890/1e13 AS LINE_7;
SELECT 1234567890/1e14 AS LINE_8;
SELECT 1 / 0 AS LINE_9;
SELECT 1.2345 - 0.2345 AS LINE_10;
SELECT 2.00 / 1 AS LINE_11;
SELECT 2.00 - 1 AS LINE_12;

Output:

LINE_1
3
LINE_2
0.67
LINE_3
0.666667
LINE_4
123456.79
LINE_5
1.23
LINE_6
0.123456789
LINE_7
0.000123456789
LINE_8
1.23456789e-005
LINE_9
NULL
LINE_10
1.0000
LINE_11
2.0000
LINE_12
1.00

Notes:

• As you can see from the output, the data type of the resulting value depends on the data types of the operands.
• Line 1 tells us that if both operands are integers and the resulting value is a whole number, the resulting data type will be integer.
• Line 2 and 4 tell us that if both operands are integers and the resulting value is not a whole number, the resulting data type will be exact number with a scale of 2.
• Line 10 is a surprise to me. Why MySQL is not giving a "divide-by-zero" exception? Or a "overflow" exception?
• It seems to be no rules to describe the behaviors of the other lines.

Example 2 - PredicateOps.sql

-- PredicateOps.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT 1 = 1 AS LINE_1;
SELECT 'abc' = 'ABC' AS LINE_2;
SELECT 'abc' < 'xyz' AS LINE_3;
SELECT 'abc' < 'abcd' AS LINE_4;
SELECT 0.67 = 2/3 AS LINE_5;
SELECT 2/3 BETWEEN 0.66 AND 0.67 AS LINE_6;
SELECT 'APPLE' IN ('apple', 'orange', 'banana') AS LINE_7;
SELECT 'abc' LIKE 'Abc' AS LINE_8;
SELECT '3.14' LIKE '_.__' AS LINE_9;
SELECT '3.14159' LIKE '3.%' AS LINE_10;
SELECT 'herong_yang' LIKE 'herong\_yang' AS LINE_11;
SELECT '4.75%' LIKE '_.__\%' AS LINE_12;

Output:

LINE_1
1
LINE_2
1
LINE_3
1
LINE_4
1
LINE_5
0
LINE_6
1
LINE_7
1
LINE_8
1
LINE_9
1
LINE_10
1
LINE_11
1
LINE_12
1

Notes:

• Line 1 confirms that MySQL is not case sensitive with character string operations. If you want to compare character strings with case, you can use BINARY() function. For example, BINARY('abc') = BINARY('ABC') will return false.
• Line 4 tells us that string comparison also consider the length of the string. The longer string has a higher order, if the common portion is a match.
• Line 5 is interesting. It tells us that expression "2/3" is evaluated with a scale higher than 2. And resulting value is not truncated to 0.67 when it is used in the predicate operation.

Example 3 - DateTimeOps.sql

Examples:

-- DateTimeOps.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT DATE '1999-01-01' + INTERVAL '3' MONTH AS LINE_1;
SELECT DATE '2000-03-01' - INTERVAL '1' DAY AS LINE_2;
SELECT INTERVAL '48' HOUR + TIMESTAMP '1999-01-01 00:00:00' AS LINE_3;

Output:

LINE_1
1999-04-01
LINE_2
2000-02-29
LINE_3
1999-01-03 00:00:00

Part:   1  2  3