查询操作符
选择
从表中读取数据。
1. SELECT all
dingo> select * from TEST;
+----+-------+--------+
| ID | NAME | AMOUNT |
+----+-------+--------+
| 1 | Alice | 3.5 |
| 2 | Betty | 4.0 |
| 3 | Cindy | 4.5 |
| 4 | Doris | 5.0 |
| 5 | Emily | 5.5 |
| 6 | Alice | 6.0 |
| 7 | Betty | 6.5 |
| 8 | Alice | 7.0 |
| 9 | Cindy | 7.5 |
+----+-------+--------+
2. SELECT vector
select * from vector(table_name, vector_index,m,n,y);
参数
参数 |
描述 |
|---|---|
table_name |
表名。 |
vector_index |
向量指数。 |
m |
要查询的向量。 |
n |
指要查询的行数。 |
y |
指附加查询参数，如 HNSW 算法中的 efSearch。 |
3. SELECT clause
dingo> SELECT amount FROM TEST;
+--------+
| AMOUNT |
+--------+
| 3.5 |
| 4.0 |
| 4.5 |
| 5.0 |
| 5.5 |
| 6.0 |
| 6.5 |
| 7.0 |
| 7.5 |
+--------+
4. WHERE
dingo> SELECT * FROM TEST WHERE id > 5;
+----+-------+--------+
| ID | NAME | AMOUNT |
+----+-------+--------+
| 6 | Alice | 6.0 |
| 7 | Betty | 6.5 |
| 8 | Alice | 7.0 |
| 9 | Cindy | 7.5 |
+----+-------+--------+
5. Distinct
dingo>insert into Test values
(14, 'QWE', 3.5),
(11, 'ASE', 3.5);
dingo>select * from TEST;
+----+-------+--------+
| ID | NAME | AMOUNT |
+----+-------+--------+
| 1 | Alice | 3.5 |
| 2 | Betty | 4.0 |
| 3 | Cindy | 4.5 |
| 4 | Doris | 5.0 |
| 5 | Emily | 5.5 |
| 6 | Alice | 6.0 |
| 7 | Betty | 6.5 |
| 8 | Alice | 7.0 |
| 9 | Cindy | 7.5 |
| 15 | QWE | 3.5 |
| 16 | ASE | 3.5 |
+----+-------+--------+
11 rows selected (0.107 seconds)
dingo> select distinct amount from Test;
+--------+
| AMOUNT |
+--------+
| 7.5 |
| 3.5 |
| 7.0 |
| 6.5 |
| 6.0 |
| 5.5 |
| 5.0 |
| 4.5 |
| 4.0 |
+--------+
9 rows selected (0.499 seconds)
6. LIKE
dingo> SELECT * FROM TEST;
+----+-------+-----+--------+----------+-----------------------+
| 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 |
+----+-------+-----+--------+----------+-----------------------+
dingo> SELECT * FROM TEST 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 |
+----+-------+-----+--------+----------+-----------------------+
7. GROUP BY
dingo> SELECT class,count(amount) FROM TEST group by class;
+----------+--------+
| CLASS | EXPR$1
+----------+--------+
| class_01 | 2 |
| class_03 | 6 |
| class_02 | 2 |
+----------+--------+
连接
1. Inner Join
内部连接条件中的关系是等量关系，返回满足连接条件的所有数据。如果找不到匹配的数据，查询结果将返回空结果集。
语法
SELECT <column>
FROM <table1>
Inner JOIN <table2>
ON table1.column = table2.column;
返回类型
Result set
实例
dingo::///> SELECT student_01.name,student_01.age,student_01.amount,student_01.class, student_02.name,student_02.age,student_02.amount,student_02.school
FROM student_01
Inner JOIN student_02
ON student_01.age = student_02.age;
+-----+----+-------+------+------+----+-------+---------+
| NAME | AGE | AMOUNT | CLASS | NAME | AGE | AMOUNT | SCHOOL |
+-----+----+-------+------+------+----+-------+---------+
| Alice | 15 | 98.5 | class01 | zhangsna | 15 | 86.0 | haidianyizhong |
| Cindy | 18 | 100.0 | class01 | liuliu | 18 | 100.0 | shiyanzhognxue |
| Doris | 17 | 100.0 | class03 | yangyi | 17 | 100.0 | shiyanyizhong |
+-----+----+-------+------+------+----+-------+---------+
2. Inner Join (non-equivalent)
内连接条件中的关系是一种非等价关系，会返回满足连接条件的所有数据。如果找不到匹配数据，查询结果将返回空结果集。
语法
SELECT <column>
FROM <table1>
Inner JOIN <table2>
ON table1.column （>、<、<> ) table2.column;
返回类型
Result set
实例
dingo::///>SELECT student_01.name,student_01.age,student_01.amount,student_01.class, student_02.name,student_02.age,student_02.amount,student_02.school
FROM student_01
Inner JOIN student_02
ON student_01.age = student_02.age AND student_01.amount > student_02.amount;
+-----+----+-------+------+------+----+-------+---------+
| NAME | AGE | AMOUNT | CLASS | NAME | AGE | AMOUNT | SCHOOL |
+-----+----+-------+------+------+----+-------+---------+
| Alice | 15 | 98.5 | class01 | zhangsna | 15 | 86.0 | haidianyizhong |
+-----+----+-------+------+------+----+-------+---------+
3. Full join
连接表将包含两个表中的所有记录。连接语句相当于将两个表相乘，并使用 NULL 值作为双方缺失匹配的结果。
语法
SELECT <column
FROM <table1>
FULL JOIN <table2>
ON table1.column = table2.column;
返回类型
结果集
实例
dingo::///> SELECT student_01.*,student_02.* FROM student_01 FULL JOIN student_02 ON student_01.age = student_02.age;
+------+-------+------+--------+---------+-----------+------+----------+------+---------+---------+----------------+
| ID | NAME | AGE | AMOUNT | CLASS | ADDRESS | ID0 | NAME0 | AGE0 | AMOUNT0 | CLASS0 | SCHOOL |
+------+-------+------+--------+---------+-----------+------+----------+------+---------+---------+----------------+
| 1 | Alice | 15 | 98.5 | class01 | beijing | 1 | zhangsna | 15 | 86.0 | class01 | haidianyizhong |
| 2 | Joy | 13 | 95.0 | class02 | beijing | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | Cindy | 18 | 100.0 | class01 | shanghai | 4 | liuliu | 18 | 100.0 | class01 | shiyanzhognxue |
| 4 | Betty | 22 | 99.0 | class01 | guangzhou | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | Doris | 17 | 100.0 | class03 | shenzhen | 5 | yangyi | 17 | 100.0 | class03 | shiyanyizhong |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | lisi | 19 | 72.0 | class02 | rendafuzhong |
| NULL | NULL | NULL | NULL | NULL | NULL | 3 | wangzi | 16 | 90.0 | class01 | qinghuafuzhong |
+------+-------+------+--------+---------+-----------+------+----------+------+---------+---------+----------------+
4. Cross join
返回连接表的笛卡尔乘积。
语法
SELECT <column>
FROM <table1>
CROSS JOIN <table2>
[WHERE ... ]；
OR
SELECT <column>
FROM <table1>, <table2>
[WHERE ... ] ；
返回类型
Result set
实例
dingo::///> select student_01.*,student_02.* from student_01 cross join student_02
WHERE student_01.amount = student_02.amount;
+----+-------+-----+--------+---------+----------+-----+--------+------+---------+---------+----------------+
| ID | NAME | AGE | AMOUNT | CLASS | ADDRESS | ID0 | NAME0 | AGE0 | AMOUNT0 | CLASS0 | SCHOOL |
+----+-------+-----+--------+---------+----------+-----+--------+------+---------+---------+----------------+
| 3 | Cindy | 18 | 100.0 | class01 | shanghai | 4 | liuliu | 18 | 100.0 | class01 | shiyanzhognxue |
| 3 | Cindy | 18 | 100.0 | class01 | shanghai | 5 | yangyi | 17 | 100.0 | class03 | shiyanyizhong |
| 5 | Doris | 17 | 100.0 | class03 | shenzhen | 4 | liuliu | 18 | 100.0 | class01 | shiyanzhognxue |
| 5 | Doris | 17 | 100.0 | class03 | shenzhen | 5 | yangyi | 17 | 100.0 | class03 | shiyanyizhong |
+----+-------+-----+--------+---------+----------+-----+--------+------+---------+---------+----------------+
5. Left join
获取左表中的所有记录，即使右表中没有相应的匹配数据。如果未找到匹配数据，查询结果将返回空结果集。
语法
SELECT <column>
FROM <table1>
LEFT JOIN <table2>
ON table1.column = table2.column;
返回类型
Result set
实例
dingo::///> SELECT student_01.name,student_01.age,student_01.address,student_02.name,student_02.age,student_02.school FROM student_01 LEFT JOIN student_02 ON student_01.age = student_02.age;
+-----+--- +--------+-------+-----=+--------+
| NAME | AGE | ADDRESS | NAME | AGE | SCHOOL |
+-----+--- +--------+-------+-----=+--------+
| Alice | 15 | beijing | zhangsna | 15 | haidianyizhong |
| Joy | 13 | beijing | NULL | NULL | NULL |
| Cindy | 18 | shanghai | liuliu | 18 | shiyanzhognxue |
| Betty | 22 | guangzhou | NULL | NULL | NULL |
| Doris | 17 | shenzhen | yangyi | 17 | shiyanyizhong |
+-----+--- +--------+-------+-----=+--------+
6. Right join
获取右侧数据表中的所有数据，即使左侧数据表中没有相应数据。如果未找到匹配数据，查询结果将返回空结果集。
语法
SELECT <column>
FROM <table1>
RIGHT JOIN <table2>
ON table1.column = table2.column;
返回类型
Result set
实例
dingo::///> SELECT student_01.name,student_01.age,student_01.address,student_02.name,student_02.age,student_02.school
FROM student_01
RIGHT JOIN student_02
ON student_01.age = student_02.age;
+-----+--- +--------+-------+-----=+--------+
| NAME | AGE | ADDRESS | NAME | AGE | SCHOOL |
+-----+--- +--------+-------+-----=+--------+
| Alice | 15 | beijing | zhangsna | 15 | haidianyizhong |
| Cindy | 18 | shanghai | liuliu | 18 | shiyanzhognxue |
| Doris | 17 | shenzhen | yangyi | 17 | shiyanyizhong |
| NULL | NULL | NULL | lisi | 19 | rendafuzhong |
| NULL | NULL | NULL | wangzi | 16 | qinghuafuzhong |
+-----+--- +--------+-------+-----=+--------+