MySQL数据库（19）（高级数据操作-查询数据） _Mysql数据库

提兵百万西湖上，立马吴山第一峰！这篇文章主要讲述MySQL数据库（19）：高级数据操作-查询数据相关的知识，希望能为你提供帮助。
查询中的运算符 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 between19 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运算符【MySQL数据库（19）（高级数据操作-查询数据）】模糊匹配
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 | +----+--------+----------+------+--------+