DQL 命令
DQL 用于检索或查询数据库中的数据。
1.全表查询
0: jdbc:dingo::///> select * from student;
+----+-------+-----+--------+----------+------------+
| ID | NAME | AGE | AMOUNT | CLASS | BIR |
+----+-------+-----+--------+----------+------------+
| 1 | lily | 25 | 92.0 | class_03 | 2000-02-23 |
| 2 | lisa | 25 | 92.0 | class_03 | 2000-02-23 |
| 3 | laice | 18 | 99.0 | class_01 | 2003-02-23 |
| 4 | Ben | 19 | 90.0 | class_03 | 2002-01-11 |
| 5 | aaa | 18 | 99.5 | class_03 | 2003-11-25 |
| 6 | Susan | 19 | 98.5 | class_02 | 2003-02-23 |
| 7 | nancy | 18 | 99.0 | class_01 | 2003-02-23 |
| 8 | Bob | 19 | 90.0 | class_03 | 2002-01-11 |
| 9 | Jan | 18 | 99.5 | class_03 | 2003-11-25 |
| 10 | Marry | 19 | 98.5 | class_02 | 2003-02-23 |
+----+-------+-----+--------+----------+------------+
10 rows selected (0.072 seconds)
2.向量索引查询
select
*
from
vector(test, feature, array[0.5,0.6,0.3], 10, map[efSearch, 1]);
在示例中，test 是表名，feature 是向量索引，array 是要查询的向量，10 是要查询的行数，map 是查询的附加参数（例如，hnsw 的 efSearch）。
3.全文索引查询
新的
text_search函数，用于全文索引bm25的调用；Text_search参数说明：第一个参数是表名；
第二个参数是全文索引名称。
第三个参数是用于检索的表达式，需要满足
Tantivy的要求，请注意，表达式是否合法由Tantivy检查。(如果未写入第三个参数，则默认为空字符）。第四个参数类似于向量的 “top 5”，返回得分最高的前 5 行。(如果不包含这四个参数，默认值为 1）。
注意：当使用逻辑运算 “OR”、“AND ”时，第三个参数将检索用于检索多个条款的表达式，该表达式必须大写。如果是小写，语义将有所不同。tantivy 将被视为要匹配的相应字段的多个单词，而不是要匹配的多个子句。
select description, category, rating, text_index$rank_bm25 from text_search(test, text_index, '(description:keyboard OR category:electronics) AND rating:>2', 5) order by test_index$rank_bm25 desc;
4.多路召回查询
提供 “hybrid_search ”函数，用于实现全文索引和向量的混合搜索；
第一个参数是全文索引查询；
第二个参数是一个向量索引查询；
第三个参数是全文索引搜索的权重；
第四个参数是向量搜索的权重。(如果省略第三和第四参数，默认值为 0.5）。
相似度_限制_n、bm25_限制_n、Dingo 由相应函数中的top参数指定。
SELECT m.description, m.category, m.embedding, m.rating, s.rank_hybrid
FROM mock_items m
LEFT JOIN (
SELECT * FROM hybrid_search(
text_search(test, text_index, '(description:keyboard OR category:electronics) AND rating:>2', 5),
vector(test, feature, array[0.8894774317741394, 0.7277960181236267, 0.692345142364502, 0.47235092520713806, 0.8568729162216187, 0.6647433042526245, 0.3333759307861328, 0.5181455016136169], 5),
0.9,
0.1
)
) s
ON m.id = s.id where m.rating > 4
LIMIT 5;
5.索引前过滤查询
首先根据整个表的标量字段过滤结果集，然后根据该结果集进行向量查询。
select /*+ vector_pre */ id,name,feature_id,feature_index$distance //query field
from
vector(demo3, feature, array[0.8894774317741394, 0.7277960181236267, 0.692345142364502, 0.47235092520713806, 0.8568729162216187, 0.6647433042526245, 0.3333759307861328, 0.5181455016136169], 3)
where name='we'
order by feature_index$distance
limit 3;
6.索引后向过滤查询
高级行向量查询生成结果集，然后根据该结果集进行标量字段筛选。
select
id,name,feature_id,feature_index$distance
from
vector(demo3, feature, array[0.8894774317741394, 0.7277960181236267, 0.692345142364502, 0.47235092520713806, 0.8568729162216187, 0.6647433042526245, 0.3333759307861328, 0.5181455016136169], 5)
where name=''
order by feature_index$distance
limit 5;
7.查询指定列
0: jdbc:dingo::///> select name from student;
+-------+
| NAME |
+-------+
| lily |
| lisa |
| laice |
| Ben |
| aaa |
| Susan |
| nancy |
| Bob |
| Jan |
| Marry |
+-------+
10 rows selected (0.122 seconds)
8.条件筛选查询
单一条件筛选
0: jdbc:dingo::///>select * from student where class='class_03';
+----+------+-----+--------+----------+------------+
| ID | NAME | AGE | AMOUNT | CLASS | BIR |
+----+------+-----+--------+----------+------------+
| 1 | lily | 25 | 92.0 | class_03 | 2000-02-23 |
| 2 | lisa | 25 | 92.0 | class_03 | 2000-02-23 |
| 4 | Ben | 19 | 90.0 | class_03 | 2002-01-11 |
| 5 | Joy | 18 | 99.5 | class_03 | 2003-11-25 |
| 8 | Bob | 19 | 90.0 | class_03 | 2002-01-11 |
| 9 | Jan | 18 | 99.5 | class_03 | 2003-11-25 |
+----+------+-----+--------+----------+------------+
6 rows selected (0.116 seconds)
多种条件筛选
0: jdbc:dingo::///> select * from student where class='class_03' and age=19;
+----+------+-----+--------+----------+------------+
| ID | NAME | AGE | AMOUNT | CLASS | BIR |
+----+------+-----+--------+----------+------------+
| 4 | Ben | 19 | 90.0 | class_03 | 2002-01-11 |
| 8 | Bob | 19 | 90.0 | class_03 | 2002-01-11 |
+----+------+-----+--------+----------+------------+
2 rows selected (0.083 seconds)
9.重复数据删除查询
对指定列进行重复数据删除查询
0: jdbc:dingo::///> select distinct age from student;
+-----+
| AGE |
+-----+
| 18 |
| 19 |
| 25 |
+-----+
3 rows selected (0.545 seconds)
10.内容匹配
常见用法
LIKE “指定字符串”：用于在字段中搜索与指定内容匹配的内容。
NOT LIKE'指定字符串'：用于搜索字段内容与指定数据字符串不匹配。
LIKE BINARY “指定字符串”： BINARY 用于区分大小写，LIKE 关键字默认忽略大小写，如果只需搜索 A 字符，则使用 BINARY 关键字执行搜索命令。
通配符
示例：查询指定字符串开头的值： LIKE '% of the specified string'。LIKE '%SpecifiedString' 查询以指定字符串结尾的值。LIKE '%SpecifiedString%' 查询包含指定字符串的值。查询指定中间字符串的值：LIKE '%specified string' 查询指定字符串的值： LIKE'%指定字符串%指定字符串%指定字符串
_：只能表示单个字符，字符长度不能为 0；通配符 _ 一般用于特定情况，需要多少位就有几个通配符，可与 % 通配符结合使用。语法 LIKE '%specified string_specified string%'
[ ]: 表示括号中列出的字符之一。指定需要匹配的字符、字符串或范围。语法： LIKE '[指定字符串]指定字符串' 例： select * from table where name like '[Zhang Li Wang] San' ; Zhang San, Li San, Wang San
[^]: 表示未包含在括号中的单个字符。其值与 [ ]相同，但要求匹配的对象必须是指定字符中的任意一个。语法： LIKE '[^specified string]specified string' 示例： select * from table where name like '[^ZhangLiWang]San'; LiuSan, YangSan
备注
如果模糊匹配字符串包含一些正则符号（例如：? * +），则需要使用转义字符“\”来转义。
用例
0: jdbc:dingo::///> select * from STUDENT;
+----+-------+-----+--------+----------+-----------------------+
| ID | NAME| AGE | AMOUNT | CLASS | BIR |
+----+-------+-----+--------+----------+---------------+
| 1 | lisa | 25 | 92.0| class_03 | 2000-02-23 18:00:00.0 |
| 2 | alice| 18 | 99.0 | class_01 | 2003-02-23 17:00:00.0 |
| 3 | Ben | 19 | 90.0 | class_03 | 2002-01-11 16:00:00.0 |
| 4 | Joy | 18 | 99.5 | class_03 | 2003-11-25 23:00:00.0 |
| 5 | Susan | 19 | 98.5| class_02 | 2003-02-23 23:05:00.0 |
+----+-------+-----+--------+----------+----------------+
5 rows selected (0.494 seconds)
0: jdbc:dingo::///> select * from student where name like 'e%';
+----+------+-----+--------+-------+-----+
| ID | NAME | AGE | AMOUNT | CLASS | BIR |
+----+------+-----+--------+-------+-----+
No rows selected (0.529 seconds)
0: jdbc:dingo::///> select * from student where name like '%e';
+----+-------+-----+------+----------+-----------------------+
| ID | NAME | AGE |AMOUNT| CLASS | BIR |
+----+-------+-----+------+----------+-----------------------+
| 2 | alice | 18 | 99.0 | class_01 | 2003-02-23 17:00:00.0 |
+----+-------+-----+------+----------+-----------------------+
1 row selected (0.052 seconds)
0: jdbc:dingo::///> select * from student where name like '%e%';
+----+-------+-----+------+----------+-----------------------+
| ID | NAME | AGE |AMOUNT| CLASS | BIR |
+----+-------+-----+------+----------+-----------------------+
| 2 | alice | 18 | 99.0 | class_01 | 2003-02-23 17:00:00.0 |
| 3 | Ben | 19 | 90.0 | class_03 | 2002-01-11 16:00:00.0 |
+----+-------+-----+------+----------+-----------------------+
2 rows selected (0.04 seconds)
0: jdbc:dingo::///> select * from student where name like 'b%';
+----+------+-----+------+----------+-----------------------+
| ID | NAME | AGE |AMOUNT| CLASS | BIR |
+----+------+-----+------+----------+-----------------------+
| 3 | Ben | 19 | 90.0 | class_03 | 2002-01-11 16:00:00.0 |
+----+------+-----+------+----------+-----------------------+
1 row selected (0.037 seconds)
11.分组
0: jdbc:dingo::///> select class,count(amount) from student group by class;