where

2024年06月12日

柏拉文

越努力，越幸运

一、认识

select field1, field2,...fieldN 
from table_name1, table_name2...
where 条件一 [AND [OR]] 条件二.....;

基础实例: 查询 good_price 值 >= 3150 的数据

select good_name,good_desc,good_price 
from good
where good_price >= 3150;

逻辑且条件示例:

通过 and
select good_name,good_desc,good_price 
from good 
where good_price >= 3150 and good_name = "剑姬";

通过 &&
select good_name,good_desc,good_price 
from good 
where good_price >= 3150 && good_name = "剑姬";

逻辑或条件实例:

通过 or
select good_name,good_desc,good_price 
from good 
where good_price >= 3150 or good_name = "剑姬";

通过 ||
select good_name,good_desc,good_price 
from good 
where good_price >= 3150 || good_name = "剑姬";

逻辑非条件实例:

通过 !
select good_name,good_desc,good_price 
from good 
where good_name != "剑姬";

不忽略大小写实例: 默认忽略大小写

select good_name,good_desc,good_price 
from good 
where binary good_name_english = "YaoJi";

in(x,y,………)、not in(x,y,……) 实例: in(x,y,………) 可以为x、y中的一个; 而 not in(x,y,……) 不为 x、y中的任何一个

in
select 
	event_id,
	operator 
from `base_point`
where 
	operator in ('小明','小兰');

not in
select 
	event_id,
	operator 
from `base_point`
where 
	operator not in ('小明','小兰');

between x and y 实例: 介于 x 和 y 之间 , 且 x 必须小于 y

select
	good_name,
	good_price
from 
	good
where 
	good_price between 3150 and 6300
order by 
	good_price desc;

等效于

select
	good_name,
	good_price
from 
	good
where 
	good_price >= 3150 
	and good_price <= 6300
order by 
	good_price desc;

筛选字段值为 null 的实例

通过 is null 处理为 null 的数据
select 
	good_name as `name`,
	good_name_english as `english`
from good 
where 
	good_name_english is null;

通过 is not null 处理不为 null 的数据
select 
	good_name as `name`,
	good_name_english as `english`
from good 
where 
	good_name_english is not null;

函数条件实例

使用 concat() 拼接
select 
	event_id,
	operator 
from `base_point`
where 
	concat(year,month,day) between '2022601' and '2022615';