# 查询中的运算符

## 1、算术运算符

```
+ 加
- 减
* 乘
/ 除
% 取余/取模
```

通常不在条件中使用，而是用于结果运算（select字段中）

null进行任何算术运算，结果都为null

除法中除数如果为0，结果为null

```sql
mysql> select 1+1, 2-1, 2*3, 6/2, 7%2, 1/0, 1/null;
+-----+-----+-----+--------+------+------+--------+
| 1+1 | 2-1 | 2*3 | 6/2    | 7%2  | 1/0  | 1/null |
+-----+-----+-----+--------+------+------+--------+
|   2 |   1 |   6 | 3.0000 |    1 | NULL |   NULL |
+-----+-----+-----+--------+------+------+--------+
```
## 2、比较运算符

```
>  大于
>= 大于等于
<  小于
<= 小于等于
=  等于 (<=>)
<> 不等于
```

用来在条件中限定结果

```sql
select '1' = 1, 0.02 = 0;
+---------+----------+
| '1' = 1 | 0.02 = 0 |
+---------+----------+
|       1 |        0 |
+---------+----------+
```

- MySQL 中没有规定select必须有数据表
- MySQL中数据会先自动转成同类型，再比较
- MySQL中没有bool类型，1代表true, 0代表false

计算闭区间

```sql
-- 条件1 需要比 条件2小
字段 between 条件1 and 条件2
```

```sql
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
|  8 | 关羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+

-- 查找年龄在[19, 21]区间的学生
mysql> select * from my_student where age between  19 and 21;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
+----+--------+----------+------+--------+
```

## 3、逻辑运算符

```
and 逻辑与
or 逻辑或
not 逻辑非
```

```sql
-- 查找年龄在[19, 21]区间的学生
select * from my_student where age >= 19 and age <= 21;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
+----+--------+----------+------+--------+

-- 查找年龄大于20或者是男性的学生
select * from my_student where age > 20 or gender = 1;
mysql> select * from my_student where age > 20 or gender = 1;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  2 | 李四   |        1 |   19 |      1 |
|  7 | 张飞   |        2 |   21 |      1 |
|  8 | 关羽   |        1 |   22 |      2 |
+----+--------+----------+------+--------+
```

## 4、in运算符

用来替代等号，判断集合

基本语法

```
in (数值1, 数值2...)
```

```sql
-- 按照学号查找学生
mysql> select * from my_student where id in (1, 3);
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  3 | 王五   |        2 |   20 |      2 |
+----+--------+----------+------+--------+
```

## 5、is运算符

判断字段值是否为null

基本语法
```sql
is null
is not null
```


```sql
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  7 | 张飞   |        2 |   21 |      1 |
|  8 | 关羽   |        1 |   22 |      2 |
|  9 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+

-- 查询为null的数据
mysql> select * from my_student where gender is null;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  9 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+

```

## 6、like运算符

模糊匹配


like 匹配模式

占位符：
- 下划线`_` 匹配单个字符
- 百分号`%` 匹配多个字符

```sql
mysql> select * from my_student where name like '曹_';
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  9 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+

mysql> select * from my_student where name like '曹%';
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  9 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+
```

https://www.bilibili.com/video/BV1Vx411g7uJ?p=40&spm_id_from=pageDriver