使用的数据库:
https://blog.csdn.net/weixin_44953902/article/details/100169739
一、比较运算符
-- select .... from 表名 where ....
大于
-- >
-- 查询大于18的信息
select * from students where age>18;
小于
-- <
-- 查询小于18岁的信息
select * from students where age<18;
大于等于/小于等于
-- <=
-- >=
-- 查询小于或等于18岁的信息
select * from students where age<=18;
等于
-- =
-- 查询年龄为18岁的信息
select * from students where age=18
不等于
-- != 或者<>
select * from students where age!=18;
例如:
-- 查询大于18的信息
select * from students where age>18;
二、逻辑运算符
and
-- 18到28之间的所有学生信息
select * from students where age>18 and age<28;
-- 失败select * from students where age>18 and <28;
-- 18岁以上的女性
select * from students where age>18 and gender="女";
select * from students where age>18 and gender=2;
or
-- 18岁以上或者身高超过180(包括)以上
select * from students where age>18 or high>=180;
not
-- 不在18以上的女性 这个范围的信息
select * from students where not age>18 and gender=2;
-- not(加括号最保险)
-- 不在18以上的女性 这个范围的信息
select * from students where not age>18 and gender=2;
select * from students where (not age>18) and gender=2;
-- 年龄不是小于或者等于18,并且是女性
select * from students where not (age<=18 and gender=2);
三、模糊查询
like(用的比较少,因为性能低)
-- % 替换一个或多个
-- _ 替换一个
-- 查询姓名中以"小"开始的名字
select * from students where name like"小%";
-- 查询姓名中有"小"的所有名字
select * from students where name like"%小%";
-- 查询两个字的名字
select * from students where name like"__";
-- 查询三个字的名字
select * from students where name like"___";
-- 查询至少有两个字的名字
select * from students where name like"__%";
rlike 正则
-- 查询以周开始的姓名
select name from students where name rlike "^周.*";
-- 查询以周开始,伦结尾的姓名
select name from students where name rlike "^周.*伦$";
四、范围查询
in
-- in (1,3,8)表示在一个非连续范围内
-- 查询 年龄为12,18,34的姓名
-- select name,age from students where age=12 or age=18 or age=34;
select name,age from students where age in(12,18,34);
not in
-- not in 不在非连续范围之内
-- 年龄不是18,34的姓名
select name,age from students where age not in(18,34)
between … and …
-- between ... and ...表示在一个连续的范围内
-- 查询年龄在18到34之间的信息
select name,age from students where between 18 and 34;
not between … and …
-- not between ... and ...表示在一个连续的范围内
-- 查询年龄不在18到34之间的信息
select name,age from students where age not between 18 and 34;
--也可以select name,age from students where not (age between 18 and 34);
-- 失败select name,age from students where age not (between 18 and 34);
-- 因为not between 是一种用法,它们是一个整体,不能加括号
五、空判断
is null
-- 判断 is null
-- 查询身高为空的信息
select * from students where high is null;
is not null
-- 判非空 is not null
-- 查询身高不为空的信息
select * from students where high is not null;