MySQL8 比较运算符

比较运算符： = <=>  <> !=  < <=  > >=

SELECT 1 = 2, 1 != 2, 1 = '1', 1 = 'a'

FROM DUAL;

mysql> SELECT 1 = 2, 1 != 2, 1 = '1', 1 = 'a'
    -> FROM DUAL;
+-------+--------+---------+---------+
| 1 = 2 | 1 != 2 | 1 = '1' | 1 = 'a' |
+-------+--------+---------+---------+
|     0 |      1 |       1 |       0 |
+-------+--------+---------+---------+
1 row in set, 1 warning (0.00 sec)

字符串存在隐式转换，如果转换数值不成功，则看作0

IS NULL \ IS NOT NULL \ ISNULL

mysql> SELECT last_name,salary,commission_pct
    -> FROM employees
    -> WHERE commission_pct IS NOT NULL;
+------------+----------+----------------+
| last_name  | salary   | commission_pct |
+------------+----------+----------------+
| Russell    | 14000.00 |           0.40 |
| Partners   | 13500.00 |           0.30 |
| Errazuriz  | 12000.00 |           0.30 |
| Cambrault  | 11000.00 |           0.30 |
| Zlotkey    | 10500.00 |           0.20 |

SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;

LEAST() \ GREATEST

mysql> SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
    -> FROM DUAL;
+------------------------+---------------------------+
| LEAST('g','b','t','m') | GREATEST('g','b','t','m') |
+------------------------+---------------------------+
| b                      | t                         |
+------------------------+---------------------------+
1 row in set (0.00 sec)

区间查询：BETWEEN ... ADN (包含起始和结束值)

SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;

mysql> SELECT employee_id,last_name,salary
    -> FROM employees
    -> WHERE salary BETWEEN 6000 AND 8000;
+-------------+-----------+---------+
| employee_id | last_name | salary  |
+-------------+-----------+---------+
|         104 | Ernst     | 6000.00 |
|         111 | Sciarra   | 7700.00 |
|         112 | Urman     | 7800.00 |
|         113 | Popp      | 6900.00 |
|         120 | Weiss     | 8000.00 |