Skip to content

查询中的运算符

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 |
+----+--------+----------+------+--------+