查询中的运算符
1、算术运算符
+ 加 - 减 * 乘 / 除 % 取余/取模
通常不在条件中使用，而是用于结果运算（select字段中）
null进行任何算术运算，结果都为null
除法中除数如果为0，结果为null
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、比较运算符
> 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 (<=>) <> 不等于
用来在条件中限定结果
select '1' = 1, 0.02 = 0; +---------+----------+ | '1' = 1 | 0.02 = 0 | +---------+----------+ | 1 | 0 | +---------+----------+
MySQL 中没有规定select必须有数据表
MySQL中数据会先自动转成同类型，再比较
MySQL中没有bool类型，1代表true, 0代表false
计算闭区间
-- 条件1 需要比 条件2小 字段 between 条件1 and 条件2
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 逻辑非
-- 查找年龄在[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...)
-- 按照学号查找学生 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
基本语法
is null is not null
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 匹配模式
占位符：
下划线_ 匹配单个字符
百分号% 匹配多个字符
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 | +----+--------+----------+------+--------+
