MySQL中比较运算符的使用

1. 加号 “+” 在MySQL中没有拼接的意思

如果是数值和字符 相+ 则字符会隐式转换为0
1

mysql> select 1+'hello' from dual;
+-----------+
| 1+'hello' |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.04 sec)

mysql>


1
2
3
4
5
6
7
8
9
10
11

2. 只要有null值参与，运算结果都为null

mysql> select null = null from dual;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

3. 安全等于 <=> 为null而生！！

mysql> select null <=> null from dual;
+---------------+
| null <=> null |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

4. IS NULL 或 ISNULL() 为null的值

练习：查询表中commission_pct为null的数据有哪些？

mysql> select last_name,salary,commission_pct from employees
    -> where commission_pct is null;
或
mysql> select last_name,salary,commission_pct from employees
    -> where isnull(commission_pct );
    
+-------------+----------+----------------+
| last_name   | salary   | commission_pct |
+-------------+----------+----------------+
| King        | 24000.00 |           NULL |
| Hartstein   | 13000.00 |           NULL |
| Gietz       |  8300.00 |           NULL |
+-------------+----------+----------------+
......
72 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

5. IS NOT NULL 不为空的值

练习：查询表中commission_pct不为null的数据有哪些？

mysql> select last_name,salary,commission_pct from employees
    -> where commission_pct is not null;
或
mysql> select last_name,salary,commission_pct from employees
    -> where not commission_pct <=> null;    
    
+------------+----------+----------------+
| last_name  | salary   | commission_pct |
+------------+----------+----------------+
| Russell    | 14000.00 |           0.40 |
| Partners   | 13500.00 |           0.30 |
| Errazuriz  | 12000.00 |           0.30 |
+------------+----------+----------------+
......
35 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

6. least(获取最小值)

mysql> select least(1,2,3,4,5) from dual;

+------------------+
| least(1,2,3,4,5) |
+------------------+
|                1 |
+------------------+
1 row in set (0.11 sec)
1
2
3
4
5
6
7
8

7. greatest(获取最大值)

mysql> select greatest(1,2,3,4,5) from dual;

+---------------------+
| greatest(1,2,3,4,5) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

8. between…and… 查询区间

(1、查询中，between 后面跟小的数，and 后面跟大的数！！！)
(2、查询结果 含头也含尾！！！)

练习：查询工资6000到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 |
|         203 | Mavris    | 7500.00 |
|         179 | Johnson   | 6200.00 |
|         202 | Fay       | 8000.00 |
+-------------+-----------+---------+
......
24 rows in set (0.22 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

练习：查询工资不在6000到8000的员工信息

mysql> select employee_id,last_name,salary
    -> from employees
    -> where salary not between 6000 and 8000;
    
+-------------+-------------+----------+
| employee_id | last_name   | salary   |
+-------------+-------------+----------+
|         100 | King        | 24000.00 |
|         206 | Gietz       |  8300.00 |
+-------------+-------------+----------+
......
83 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

9. in(查询具体值的区间)

练习：查询部门为10，20，30号部门的员工信息

mysql> select last_name,salary,department_id
    -> from employees
    -> where department_id in(10,20,30);
    
+------------+----------+---------------+
| last_name  | salary   | department_id |
+------------+----------+---------------+
| Whalen     |  4400.00 |            10 |
| Hartstein  | 13000.00 |            20 |
| Fay        |  6000.00 |            20 |
| Himuro     |  2600.00 |            30 |
| Colmenares |  2500.00 |            30 |
+------------+----------+---------------+
......
9 rows in set (0.13 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

10. not in(查询不在具体值的区间)

练习：查询部门工资不为6000，7000，8000的员工信息

mysql> select last_name,salary,department_id
    -> from employees
    -> where salary not in(6000,7000,8000);
    
+-------------+----------+---------------+
| last_name   | salary   | department_id |
+-------------+----------+---------------+
| King        | 24000.00 |            90 |
| Kochhar     | 17000.00 |            90 |
| De Haan     | 17000.00 |            90 |
| Hunold      |  9000.00 |            60 |
| Gietz       |  8300.00 |           110 |
+-------------+----------+---------------+
106 rows in set (0.34 sec)
......
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

11. like 模糊查询

（1、%代表任意字符）
（2、_代表一个字符）
（3、\ 代表转意字符）

练习1：查询last_name中包含字母 a 的员工姓名

mysql>  select last_name from employees
    -> where last_name like '%a%';
+------------+
| last_name  |
+------------+
| Kochhar    |
| Whalen     |
| Hartstein  |
| Fay        |
| Mavris     |
| Baer       |
+------------+
.....
56 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

练习2：查询第二个字符是a的员工信息

mysql>  select last_name from employees
    -> where last_name like '_a%';
1
2
3
4

练习3：查询第二个字符是 _ 的员工信息

mysql>  select last_name from employees
    -> where last_name like '_\_%';
1
2
3
4

12. MySQL在Dos命令窗口清屏使用：system cls

13. regexp\rlike :正则表达式

（1、^a, 匹配与a开头的字符串）

mysql> select
    -> 'hello_mysql' regexp '^h'
    -> from dual;
+---------------------------+
| 'hello_mysql' regexp '^h' |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

（2、a$, 匹配以a结尾的字符串）

mysql> select
    -> 'hello_mysql' rlike 'l$'
    -> from dual;
+--------------------------+
| 'hello_my' rlike 'y$' |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

（3、a.b 匹配以a和b之间有任意一个单字符的字符串）

mysql> select
    -> 'hello_java' regexp 'he..o'
    -> from dual;
+-----------------------------+
| 'hello_java' regexp 'he..o' |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

（4、[abc], 匹配包含字符 “a” 或 “b” 或 “c” 的字符串）

mysql> select
    -> 'huangzuang' rlike '[hz]'
    -> from dual;
+---------------------------+
| 'huangzuang' rlike '[hz]' |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9