通用搜索查询

1. select field

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

2. elasticsearch中exists的表达方式

select * from apple where provider is not null;

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

match -> ~=     // not match -> !~=
match_all -> 没有where条件查询
match_phrase -> ~==   // not match_phrase -> !~==
match_phrase_prefix -> ~===  //not match_phrase -> !~===
multi_match -> (,,)~=
query_string -> query by 'text....'
# not support simple_query_string
simple_query_string 

以上使用sql分别为

select * from apple where provider~='苹果';
select * from apple;
select * from apple where provider~=='苹果';
select * from apple where provider~==='苹果';
select * from student where (name,age) ~= 'hahah'
select * from apple where query by '苹果';
--- not support simple_query_string

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

term
terms
fuzzy
prefix
regexp
wildcard

以上使用sql分别为

select * from apple where provider='苹果'   // not equal -> !=
select * from apple where provider in ('苹果','apple'); 
select * from apple where provider fuzzy like 'ca'; --fuzzy
select * from apple where provider prefix like 'ca'; --prefix
select * from apple where provider wildcard like 'ki*y'; --wildcard
select * from apple where provider like 'k.*y';  -- regexp
select * from apple where provider regexp like 'k.*y';  -- regexp

5. elasticsearch中的must not查询

语法: where not [expression]

示例

select * from aaa where (not provider ~=== 'apple') or provider !~='microsoft'