Msyql SELECT and WHERE tutorial

Msyql SELECT and WHERE tutorial
Select_expression or where_definition in an SQL statement can be composed of any expressions that use the functions described below.

An expression that contains NULL always returns a NULL value unless the operations and functions in the expression are described in this document.

Note: there must be no space between a function name and the brackets that follow it. This helps the MySQL syntax analysis program distinguish between function calls and references that happen to be the same as the function name or column. However, spaces on both sides of the parameter are allowed.

You can force MySQL to accept the space format after the function name, which requires starting mysqld with the -- ansi option or using CLIENT_IGNORE_SPACE in mysql_connect (). However, in this case, all function names are reserved words. View chapter 1.8.2 run MySQL in ANSI mode.

For the sake of conciseness, examples output from mysql programs are shown in abbreviations. Therefore:

Mysql> select mod (29,9 );
1 rows in set (0.00 sec)

+ ----------- +
| Mod (29,9) |
+ ----------- +
| 2 |
+ ----------- +
Will be displayed as follows:

Mysql> select mod (29,9 );
-> 2
6.3.1 non-typed Special operators and functions
6.3.1.1 parentheses
(...)

Parentheses, which are used to force the calculation order of an expression.

Mysql> SELECT 1 + 2*3;
-> 7
Mysql> SELECT (1 + 2) * 3;
-> 9
6.3.1.2 comparison Operators
The comparison operator returns 1 (TRUE), 0 (FALSE), or NULL. These functions can work on numbers and strings. As needed, strings are automatically converted to numbers, and numbers are converted to strings (for example, in Perl ).

MySQL uses the following rules for comparison:

If one or two parameters are NULL, the comparison result is NULL except the <=> operator.
If both parameters are strings in a comparison operation, they will be compared as strings.
If both parameters are integers, they are compared as integers.
If the hexadecimal value is not compared with a number, it is treated as a binary string.
If one of the parameters is a TIMESTAMP or DATETIME column and the other is a constant, the constant is converted to a TIMESTAMP before the comparison is executed. This is to make ODBC more friendly.
In all other cases, the parameter is compared as a floating point (real) number.
By default, strings are compared using the current character set to ignore uppercase/lowercase letters (the default character set is ISO-8859-1 Latin1, which is very good at English ).

The following example demonstrates the conversion from a comparison string to a number:

Mysql> SELECT 1> '6x ';
-> 0
Mysql> SELECT 7> '6x ';
-> 1
Mysql> SELECT 0> 'x6 ';
-> 0
Mysql> SELECT 0 = 'x6 ';
-> 1
=
Equal:
Mysql> SELECT 1 = 0;
-> 0
Mysql> SELECT '0' = 0;
-> 1
Mysql> SELECT '0. 0' = 0;
-> 1
Mysql> SELECT '0. 0' = 0;
-> 0
Mysql> SELECT '. 01' = 0.01;
-> 1
<>
! =
Not equal:
Mysql> SELECT '. 01' <> '0. 01 ';
-> 1
Mysql> SELECT. 01 <> '0. 01 ';
-> 0
Mysql> SELECT 'zapp' <> 'zappp ';
-> 1
<=
Less than or equal:
Mysql> SELECT 0.1 <= 2;
-> 1
<
Less:
Mysql> SELECT 2 <2;
-> 0
> =
Greater than or equal:
Mysql> SELECT 2> = 2;
-> 1
>
Greater:
Mysql> SELECT 2> 2;
-> 0
<=>
NULL values are safe equal:
Mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1 1 0
IS NULL
IS NOT NULL
Test whether a value is NULL or not:
Mysql> SELECT 1 is null, 0 is null, null is null;
-> 0 0 1
Mysql> SELECT 1 is not null, 0 is not null, null is not null;
-> 1 1 0
To work better with other programs, MySQL supports the following additional options when using is null:
You can find the last inserted record row:
SELECT * FROM tbl_name WHERE auto_col IS NULL
This operation can be disabled by setting SQL _AUTO_IS_NULL = 0. View chapter 5.5.6 SET syntax.
For the DATE and DATETIME columns of not null, you can use the following statement to find the special value 0000-00-00:
SELECT * FROM tbl_name WHERE date_column IS NULL
This requires some ODBC applications (because ODBC does not support a 0000-00-00 date)

Expr BETWEEN min AND max
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1; otherwise, 0 is returned. It is equivalent to the expression (min <= expr AND expr <= max), as long as all parameters are of the same type. Otherwise, the type will be converted according to the above rules, but it is applied to all three parameters. Note: Before MySQL 4.0.5, the parameter is converted to the expr type.
Mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
Mysql> SELECT 'B' BETWEEN 'a 'AND 'C ';
-> 1
Mysql> SELECT 2 BETWEEN 2 AND '3 ';
-> 1
Mysql> SELECT 2 BETWEEN 2 AND 'x-3 ';
-> 0
Expr not between min AND max
It is equivalent to NOT (expr BETWEEN min AND max ).

Expr IN (value ,...)
If expr is a value IN the IN list, it returns 1; otherwise, 0. If all values are constants, all values are calculated and sorted according to the expr type. Then, a binary search method is used to complete the project search. This means that if the IN list is completely composed of constants, IN will be very fast. If expr is a case-sensitive string expression, string comparison is performed in uppercase/lowercase mode:
Mysql> SELECT 2 IN (0, 3, 5, 'wefwf ');
-> 0
Mysql> SELECT 'wefwf 'IN (0, 3, 5, 'wefwf ');
-> 1
Starting from MySQL 4.1 (compliant with SQL-99 standards), if the expression on the left side is NULL, or if no matching value is found in the list and one expression in the list is NULL, IN returns NULL.

Expr not in (value ,...)
Equivalent to NOT (expr IN (value ,...)).

ISNULL (expr)
If expr is NULL, ISNULL () returns 1; otherwise, return 0:
Mysql> select isnull (1 + 1 );
-> 0
Mysql> select isnull (1/0 );
-> 1
Note: It is always false to use = to compare NULL values!
COALESCE (list)
Returns the first non-NULL element in the list:
Mysql> select coalesce (NULL, 1 );
-> 1
Mysql> select coalesce (NULL, NULL, NULL );
-> NULL
INTERVAL (N, N1, N2, N3 ,...)
Returns 0 if N <N1, 1 if N <N2, and so on. All parameters are treated as integers. In order for the function to work correctly, it requires N1 <N2 <N3 <... <Nn. This is because it uses a binary search (very fast ):
Mysql> select interval (23, 1, 15, 17, 30, 44,200 );
-> 3
Mysql> select interval (10, 1, 10,100,100 0 );
-> 2
Mysql> select interval (22, 23, 30, 44,200 );
-> 0
If any standard operator (=, <> ..., except for LIKE), the blank space (space, TAB, and line feed) at the end of the string is ignored.