ES 实用查询、url search 整理、sql 查询对比

这里整理用浏览器窗口查询 URL SEARCH，es数据，对比sql方便直观记录

• 说明：索引库名称 users ，模拟数据在最下方
• 属性：name、age、sex、birthday、position

查询所有，不指定字段

select * from users;

http://127.0.0.1:9200/users/_search?pretty

查询所有，指定字段

select name,age from users;

http://127.0.0.1:9200/users/_search?_source=name,age&pretty

单条件查询

• 如果字段非keyword类型，会查询分词，有类似模糊查询的效果
• 注 term精确匹配查询是代表完全匹配，搜索之前不会对你搜索的关键字进行分词，对你的关键字去文档分词库中去匹配内容

select * from users where age=20

http://127.0.0.1:9200/users/_search?q=age:20&pretty

范围查询

select * from users where age>=20

http://127.0.0.1:9200/users/_search?q=age:>=20&pretty

http://127.0.0.1:9200/users/_search?q=birthda:>=1997-01-01&pretty

select * from users where birthday BETWEEN '1998-01-01' and '1999-01-01'

http://127.0.0.1:9200/users/_search?q=birthday:[1998-01-01 TO 1999-01-01]&pretty

排序查询

select * from users order by age desc

http://127.0.0.1:9200/users/_search?sort=age:desc&pretty

查询分词内容

http://127.0.0.1:9200/users/_doc/2/_termvectors?fields=name

---

这里开始不属于简易URL SEARCH 👇

分组查询（聚会查询）

• 性别分组，并显示个数

select sex,count(*) from users GROUP BY sex

GET /users/_search
{
  "size": 0, 
  "aggs": {
    "sex_group_count": {
      "terms": {
        "field": "sex"
      }
    }
  }
}

索引库信息查询

• 查询所有索引库
• 模糊查询索引库
• 查询某索引库字段信息

http://127.0.0.1:9200/_cat/indices

http://127.0.0.1:9200/_cat/indices/*user*

http://127.0.0.1:9200/users/_mappings?pretty

模拟数据

# 创建索引库，并指定数据结构
PUT /users
{
  "settings": {
    "number_of_shards": 1,  
    "number_of_replicas": 1
  },
  "mappings": {				
    "properties": {         
      "name":{			
        "type": "text",    
        "analyzer": "ik_max_word", 
        "index": true,     
        "store": false     
      },
      "sex":{
         "type":"keyword"  
      },
      "age":{
       "type":"integer"	
      },
      "birthday":{
        "type":"date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd"
      },
      "position":{			
        "type":"keyword"
      }
    }
  }
}

#模拟数据创建

POST /users/_doc/1
{
    "name" : "小白",
    "sex": "男",
    "age" : 20,
    "birthday" : "1998-05-10",
    "position": "程序员"
}


POST /users/_doc/2
{
    "name" : "小蓝",
    "sex": "女",
    "age" : 18,
    "birthday" : "2000-03-15",
    "position": "UI设计"
}

POST /users/_doc/3
{
    "name" : "小芳",
    "sex": "女",
    "age" : 16,
    "birthday" : "2002-07-10",
    "position": "前端工程师"
}

POST /users/_doc/4
{
    "name" : "小黑",
    "sex": "男",
    "age" : 22,
    "birthday" : "1996-12-01",
    "position": "程序员"
}

POST /users/_doc/5
{
    "name" : "小虎",
    "sex": "男",
    "age" : 26,
    "birthday" : "1995-12-01",
    "position": "产品经理"
}

CREATE TABLE `users`  (
  `id` int(11) PRIMARY KEY,
  `name` varchar(16),
  `age` int(11),
  `birthday` date,
  `position` varchar(24),
  `sex` char(1)
);

INSERT INTO `users` VALUES (1, '小白', 20, '1998-05-10', '程序员', '男');
INSERT INTO `users` VALUES (2, '小蓝', 18, '2000-03-15', 'UI设计', '女');
INSERT INTO `users` VALUES (3, '小芳', 16, '2002-07-10', '前端工程师', '女');
INSERT INTO `users` VALUES (4, '小黑', 22, '1996-12-01', '程序员', '男');
INSERT INTO `users` VALUES (5, '小虎', 26, '1995-12-01', '产品经理', '男');