MySQL运算符
MySQL运算符主要包括3大类:比较运算符、算术运算符、逻辑运算符
算术运算符
加+、减-、乘*、除/、求余%
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
1 row in set (0.00 sec)
mysql> select 1/2;
+--------+
| 1/2 |
+--------+
| 0.5000 |
+--------+
1 row in set (0.00 sec)
mysql> select 5%2;
+------+
| 5%2 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
特殊操作
mysql> select '5a'+2;
+--------+
| '5a'+2 |
+--------+
| 7 |
+--------+
1 row in set, 1 warning (0.00 sec)
mysql> select 'a5'+2;
+--------+
| 'a5'+2 |
+--------+
| 2 |
+--------+
1 row in set, 1 warning (0.00 sec)
mysql> select 123.45%2.52;
+-------------+
| 123.45%2.52 |
+-------------+
| 2.49 |
+-------------+
1 row in set (0.00 sec)
mysql> select -123.45%2.52;
+--------------+
| -123.45%2.52 |
+--------------+
| -2.49 |
+--------------+
1 row in set (0.00 sec)
比较运算符
运算符 | 语法 | 说明 |
---|---|---|
= | a=b | 如果参与计算的两个操作数相等则为true,否则false |
!=或者<> | a!=b或者a<>b | 如果两个操作数不相等则true[1],否则false[0] |
< | a<b | 如果a小于b则返回true,否则false |
> | a>b | 如果a大于b则true |
<= | a<=b | 小于等于 |
>= | a>=b | 大于等于 |
in或者not in
-
in用于判断某个列的取值是否为指定的值,使用in运算符时指定的值是离散的数据,不是连续值
-
select * from tb_student where age in(18,28,15)含义是 age=18 or age=28 or age=15
-
select * from tb_student where age not in(18,28,15)含义是 age!=18 and age!=28 and age!=15
-
查询孙权、黄盖、张三以及李四同学的成绩值
– 查询张三以及李四同学的成绩
select score from tb_student where name=‘张三’ or name=‘李四’
select score from tb_stuent where name in(‘张三’,‘李四’) – in中的数据个数没有限制
-
between/and
用于判断数据是否在指定的范围内,连续值
- 查询成绩及格并且不属于优秀的所有学生信息
-- 写法1:使用条件拼接
select * from tb_student where score>=60 and score<=85;
-- 写法2
select * from tb_student where score between 60 and 85;
--如果需要的是不在指定范围内部
select * from tb_student where score not between 50 and 85;
like/not like
主要针对字符串类型数据进行模糊查询,通配符_和%
查询不姓张的同学
select * from tb_student where name not like '张%'
regexp是在mysql中针对字符串类型进行正则式进行判断,not regexp
<=> 如果两数相同为true,即使是null
is null/is not null
判断是否为空,为空则返回true
逻辑运算符
语法 | 说明 | |
---|---|---|
&&或者 and | a and b或者 a&&b | 逻辑与,如果参与计算的两个操作数为true,否则false |
||或者or | a or b或者a | |
not或者! | not a或者!a | 逻辑非,如果操作数为false则结果为true |