MSYQL SELECT and WHERE tutorial

MSYQL SELECT and WHERE tutorial
Select_expression or where_definition in an SQL statement can consist of any expression that uses the function described below.

An expression containing null always results in a null value, unless the actions and functions in the expression have a different description in the document.

Note: There must be no spaces between the name of a function and the parentheses following it. This helps the MySQL parser to differentiate between function calls and references to tables or columns that happen to have the same name as the function. However, the space between the left and right sides of the parameter is allowed.

You can force MySQL to accept the form of a space after the function name, either by starting the mysqld with the--ansi option or by using Client_ignore_space in mysql_connect (), but in this case all the function names will become reserved words. View Chapter 1.8.2 running MySQL in ANSI mode.

For simplicity, the example output from the MySQL program is shown in abbreviated form. So:

Mysql> SELECT MOD (29,9);
1 rows in Set (0.00 sec)

+-----------+
| MoD (29,9) |
+-----------+
| 2 |
+-----------+
will be displayed as this:

Mysql> SELECT MOD (29,9);
-> 2
6.3.1 special operators and functions with no type
6.3.1.1 parentheses
( ... )

Bracket, which is used to enforce the order in which an expression is evaluated.

Mysql> SELECT 1+2*3;
-> 7
Mysql> SELECT (1+2) *3;
-> 9
6.3.1.2 comparison operator
The result of the comparison operator is 1 (TRUE), 0 (FALSE), or NULL. These functions can work on numbers and strings. As needed, the 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 arguments are null, the result of the comparison is null, except for the <=> operator.
If both parameters are strings in a comparison operation, they are compared as strings.
If two parameters are integers, they are compared as integers.
A hexadecimal value is treated as a binary string if it is not compared to a number.
If one of the arguments is a TIMESTAMP or DATETIME column, and the other is a constant, the constant is converted to a timestamp before the comparison is performed. This is done in order to be more friendly to ODBC.
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 the case of letters (the default character set is Iso-8859-1 Latin1, which works well with English).

The following example illustrates the conversion of a comparison operation string to a number:

mysql> SELECT 1 > ' 6x ';
-> 0
Mysql> SELECT 7 > ' 6x ';
-> 1
mysql> SELECT 0 > ' x6 ';
-> 0
mysql> SELECT 0 = ' x6 ';
-> 1
=
Equals:
mysql> SELECT 1 = 0;
-> 0
Mysql> SELECT ' 0 ' = 0;
-> 1
mysql> SELECT ' 0.0 ' = 0;
-> 1
mysql> SELECT ' 0.01 ' = 0;
-> 0
Mysql> SELECT '. 01 ' = 0.01;
-> 1
<>
!=
Not equal to:
Mysql> SELECT '. ' <> ' 0.01 ';
-> 1
Mysql> SELECT. <> ' 0.01 ';
-> 0
mysql> SELECT ' Zapp ' <> ' zappp ';
-> 1
<=
Less than or equal to:
Mysql> SELECT 0.1 <= 2;
-> 1
<
Less than:
Mysql> SELECT 2 < 2;
-> 0
>=
Greater than or equal to:
Mysql> SELECT 2 >= 2;
-> 1
>
Greater than:
Mysql> SELECT 2 > 2;
-> 0
<=>
NULL value security equals:
mysql> SELECT 1 <=> 1, null <=> NULL, 1 <=> null;
-> 1 1 0
Is NULL
is not NULL
Tests whether a value is or is not NULL:
mysql> SELECT 1 is null, 0 are null, NULL is NULL;
-> 0 0 1
Mysql> SELECT 1 is isn't null, 0 is isn't null, NULL is NOT NULL;
-> 1 1 0
In order to be able to work better with other programs, using is NULL to be MySQL supports the following additional selectivity:
Through it, you can find the last line of records inserted:
SELECT * from Tbl_name WHERE Auto_col is NULL
This operation can be prevented by setting the sql_auto_is_null=0. View Chapter 5.5.6 SET syntax.
For not-NULL DATE and DATETIME columns, you can find special values by using the following statements 0000-00-00:
SELECT * from Tbl_name WHERE Date_column is NULL
This requires some ODBC applications to work (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, 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 rule, but it should be applied to all three parameters. Note that the parameters are converted to the type of expr before the MySQL 4.0.5.
mysql> SELECT 1 BETWEEN 2 and 3;
       -> 0
mysql> SELECT ' B ' BETWEEN ' A ' and ' C ';
  &nb sp;    -> 1
mysql> SELECT 2 BETWEEN 2 and ' 3 ';
      & nbsp -> 1
mysql> SELECT 2 BETWEEN 2 and ' x-3 ';
       -> 0
Expr not being TWEEN min and Max
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 it returns 0. If all values are constants, then all values are computed and sorted according to the type of expr. The search for the project is then completed in a binary search. This means that if the in list is entirely composed of constants, in will be very fast. If expr is a letter-case-sensitive string expression, string comparisons are performed in case-sensitive fashion:
Mysql> SELECT 2 in (0,3,5, ' WEFWF ');
-> 0
mysql> SELECT ' WEFWF ' in (0,3,5, ' WEFWF ');
-> 1
Starting with MySQL 4.1 (conforming to the SQL-99 standard), if the left-hand side of the expression is null, or if a matching value is not found in the list, and an 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 it returns 0:
Mysql> SELECT ISNULL (1+1);
-> 0
Mysql> SELECT ISNULL (1/0);
-> 1
Note that using = for NULL values is always false!
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 If n < N1 returns 0, if n < N2 returns 1, and so on. All parameters are treated as integers. For functions 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, 1000);
-> 2
Mysql> SELECT INTERVAL (22, 23, 30, 44, 200);
-> 0
If you compare a case-insensitive string with any of the standard operators (=, <> ..., except like), the trailing white space (space, TAB, and newline) is ignored.