Mysql教程（四）---过滤数据

“无意中发现了一个巨牛的人工智能教程，忍不住分享一下给大家。教程不仅是零基础，通俗易懂，而且非常风趣幽默，像看小说一样！觉得太牛了，所以分享给大家。点这里可以跳转到教程。”。

目录

1、WHERE过滤

2、BETWEEN

3、NULL空值

4、多过滤条件组合

5、IN与NOT IN过滤

6、LIKE与通配符过滤

---

备注：Mysql教程（一）—本教程数据准备

1、WHERE过滤

select * from house_prices where Home >=10 and Home <=20;     -- 并且
select * from house_prices where Home <=10 or Home >=100;     -- 或者
select * from house_prices where Bedrooms != 2;  -- 不等于
select * from house_prices where Bedrooms <> 2;   -- 标准写法不等于

2、BETWEEN

BETWEEN 是左闭合，右闭合。下面两句相等

select * from house_prices where Home >=10 and Home <=20 order by Home;   -- 这两句相等
SELECT * FROM house_prices where Home BETWEEN 10 and 20 order by Home; -- BETWEEN 是左闭合，右闭合

3、NULL空值

1. 空值和空字符串的区别
2. 字符串排序：null 值最小，空值第二，字符串是按照字段排序法

-- select * from comcat_text where `code` is null;   --空值获取
-- select * from comcat_text where `code` is not null;  --排除空值
select * from comcat_text where `code` = '';   --获取空字符

4、多过滤条件组合

1. <> 和 != 为 不等于
2. 优先级 not > and >or
3. 可以使用括号解决优先级

-- select * from comcat_text where city <> '北京'; 
-- select * from comcat_text where city = '北京' or city ='杭州';
-- select * from comcat_text where city != '北京';
-- select * from comcat_text where city = '北京';
select * from comcat_text where city =  '北京' and not city =  '上海' ;

5、IN与NOT IN过滤

select *  from house_prices where Bedrooms = 2 or Bedrooms =3 or Bedrooms =4; -- Bedrooms 等于2,3,4
select *  from house_prices where Bedrooms in (2,3,4); -- Bedrooms 等于2,3,4
select *  from house_prices where Bedrooms != 2 and Bedrooms !=3 and Bedrooms !=4;  -- Bedrooms 不等于2,3,4
select *  from house_prices where Bedrooms not in (2,3,4);  -- Bedrooms 不等于2,3,4

6、LIKE与通配符过滤

1. 百分号【%】可以匹配多个，可以放在前-中-后
2. 下划线【_】只能匹配一个个，可以放在前-中-后，一个下划线只能匹配一个字符串
3. 百分号和下划线可以同时使用

select *  from house_prices where Neighborhood LIKE 'Ea%'; -- 【百分号%】可以匹配后面所有字符
select *  from house_prices where Neighborhood LIKE 'Ea__';-- 【下划线_】一个下划线只能匹配一个字符串

转载：https://blog.csdn.net/silentwolfyh/article/details/105913459