通用搜索查询

1. select field

select productName from apple;
select * from apple where minPrice > 5000;
select ^productName,minPrice from apple;
select `^product*`,`min*` from apple

🐖 如果from后面跟 带有【-】或者【*】，必须用【``】将索引通配符包含起来

2. elasticsearch中exists的表达方式

select * from apple where provider is not null;

3. elasticsearch中的全文搜索的表达方式

match
match_all
match_phrase
match_phrase_prefix
multi_match
query_string
simple_query_string

以上使用sql分别为

select * from apple where match(provider,'苹果');
select * from apple;
select * from apple where match_phrase(provider,'苹果');
select * from apple where match_phrase_prefix(provider,'苹果');
select * from apple where multi_match(provider,'苹果,果品,平果果');
select * from apple where query_string('苹果');
select * from apple where simple_query_string('苹果');

4. elasticsearch中的关键词搜索的表达方式

term
terms
fuzzy
prefix
regexp
wildcard

以上使用sql分别为

select * from apple where provider='苹果'; ---等价于 select * from apple where term(provider,'苹果');
select * from apple where provider in ('苹果','apple'); ---等价于 select * from apple where terms(provider,'苹果,apple'); 
select * from apple where fuzzy(provider,'苹果');
select * from apple where prefix(provider,'苹果');
select * from apple where regexp(provider,'*苹*');
select * from apple where wildcard(provider,'苹果?')