aql 与 sql
SQL/AQL-比较
ArangoDB查询语言(AQL)与结构化查询语言(SQL)的目的类似。两者都支持读取和修改收集数据,但是AQL不支持数据定义操作,比如创建和删除数据库、集合和索引。
尽管一些关键字重叠,但AQL语法与SQL不同。例如,SQL WHERE和AQL FILTER子句是等价的,它们都定义了返回结果的条件。但是,SQL使用预定义的序列来确定WHERE子句必须在语句中出现的位置。在AQL中,子句从左到右执行,因此查询中的FILTER子句的位置决定了它的优先级。
术语
下面是两个系统的术语表。
SQL |
AQL |
database |
database |
table |
collection |
row |
document |
column |
attribute |
table joins |
collection joins |
primary key |
primary key (automatically present on _key attribute) |
index |
index |
INSERT
INSERT关键字将新文档添加到一个集合中。它使用以下语法:
INSERT document INTO collection options
插入单行/单文件
SQL:
1 2 |
INSERT INTO users (name, gender) VALUES ("John Doe", "m"); |
AQL:
1 2 |
INSERT { name: "John Doe", gender: "m" } INTO users |
插入单行/单文件
SQL:
1 2 3 |
INSERT INTO users (name, gender) VALUES ("John Doe", "m"), ("Jane Smith", "f"); |
AQL:
1 2 3 4 5 |
FOR user IN [ { name: "John Doe", gender: "m" }, { name: "Jane Smith", gender: "f" } ] INSERT user INTO users |
从表或集合中数据迁移
SQL:
1 2 3 4 |
INSERT INTO backup (uid, name, gender) SELECT uid, name, gender FROM users WHERE active = 1; |
AQL:
1 2 3 |
FOR user IN users FILTER user.active == 1 INSERT user INTO backup |
循环导入
SQL:
使用脚本或者存储过程实现
AQL:
1 2 3 4 5 6 |
FOR i IN 1..1000 INSERT { name: CONCAT("test", i), gender: (i % 2 == 0 ? "f" : "m") } INTO users |
UPDATE
2 |
UPDATE document IN collection options UPDATE keyExpression WITH document IN collection options |
更新单行/文件
SQL:
1 2 3 |
UPDATE users SET name = "John Smith" WHERE id = 1; |
AQL:
1 2 3 |
UPDATE { _key: "1" } WITH { name: "John Smith" } IN users |
添加新字段/属性(默认值)
SQL:
1 2 3 |
ALTER TABLE users ADD COLUMN numberOfLogins INTEGER NOT NULL default 0; |
AQL:
1 2 3 |
FOR user IN users UPDATE user WITH { numberOfLogins: 0 } IN users |
添加新字段/属性(计算值)
SQL:
1 2 3 4 5 6 7 8 9 |
ALTER TABLE users ADD COLUMN numberOfLogins INTEGER NOT NULL default 0; UPDATE users SET numberOfLogins = ( SELECT COUNT(*) FROM logins WHERE user = users.id ) WHERE active = 1; |
AQL:
1 2 3 4 5 6 7 8 9 10 11 |
FOR user IN users FILTER user.active == 1 UPDATE user WITH { numberOfLogins: LENGTH( FOR login IN logins FILTER login.user == user._key COLLECT WITH COUNT INTO numLogins RETURN numLogins ) } IN users |
删除字段/属性
SQL:
1 2 |
ALTER TABLE users DROP COLUMN numberOfLogins; |
AQL:
1 2 3 4 |
FOR user IN users UPDATE user WITH { numberOfLogins: null } IN users OPTIONS { keepNull: false } |
删除具体行的字段/属性
SQL: *
1 2 3 4 |
UPDATE users SET isImportantUser = NULL, dateBecameImportant = NULL WHERE isImportantUser = 1 AND active = 0; |
AQL:
1 2 3 4 5 6 7 8 9 10 |
FOR user IN users FILTER user.isImportantUser == 1 AND user.active == 0 UPDATE user WITH { isImportantUser: null, dateBecameImportant: null } IN users OPTIONS { keepNull: false } |
REPLACE
1 2 |
REPLACE document IN collection options REPLACE keyExpression WITH document IN collection options |
替换某行/文件
SQL:
1 2 3 |
REPLACE INTO users (name, gender) VALUES ("Jane Smith", "f") WHERE id = 1; |
AQL:
1 2 3 4 5 6 |
REPLACE { _key: "1" } WITH { name: "Jane Smith", gender: "f" } IN users |
替换多行/文件
SQL:
1 2 |
REPLACE INTO users (name, gender) SELECT name, gender FROM backup |
AQL:
1 2 3 4 5 6 7 |
FOR user IN backup REPLACE user WITH { name: backup.name, gender: backup.gender } IN users |
DELETE/REMOVE
SQL使用DELETE语句来从表中删除行。在AQL中,REMOVE关键字允许您从集合中删除文档.
删除单行/文件
SQL:
1 2 |
DELETE FROM users WHERE id = 1; |
AQL:
1 2 |
REMOVE { _key:"1" } IN users |
删除多行/文件
SQL:
1 2 |
DELETE FROM users WHERE active = 1; |
AQL:
1 2 3 |
FOR user IN users FILTER user.active == 1 REMOVE user IN users |
QUERY
当您想从SQL中的表中检索行时,您可以使用SELECT语句查询数据库。在AQL中,使用FOR和RETURN关键字查询来自集合的文档。
这里,FOR在集合中对文档进行迭代。RETURN确定查询返回给客户端。
查询所有
SQL:
1 2 |
SELECT * FROM users; |
AQL:
1 2 |
FOR user IN users RETURN user |
过滤查询
SQL:
1 2 3 |
SELECT CONCAT(firstName, " ", lastName) AS name, gender FROM users WHERE active = 1; |
AQL:
1 2 3 4 5 6 7 |
FOR user IN users FILTER user.active == 1 RETURN { name: CONCAT(user.firstName, " ", user.lastName), gender: user.gender } |
排序查询
SQL:
1 2 3 |
SELECT * FROM users WHERE active = 1 ORDER BY name, gender; |
AQL:
1 2 3 4 |
FOR user IN users FILTER user.active == 1 SORT user.name, user.gender RETURN user |
AGGREGATION
在SQL和AQL中都有一系列函数和子句用于组或进一步细化结果集以获取所需的信息。例如,计算文档,查找最小值或最大值,等等。
分组统计
SQL:
1 2 3 |
SELECT gender, COUNT(*) AS number FROM users WHERE active = 1 GROUP BY gender; |
AQL:
1 2 3 4 5 6 7 8 |
FOR user IN users FILTER user.active == 1 COLLECT gender = user.gender WITH COUNT INTO number RETURN { gender: gender, number: number } |
分组
SQL:
1 2 3 4 5 6 7 |
SELECT YEAR(dateRegister) AS year, MONTH(dateRegister) AS month, COUNT(*) AS number FROM users WHERE active = 1 GROUP BY year, month HAVING number > 20; |
AQL:
1 2 3 4 5 6 7 8 9 10 11 12 |
FOR user IN users FILTER user.active == 1 COLLECT year = DATE_YEAR(user.dateRegistered), month = DATE_MONTH(user.dateRegistered) WITH COUNT INTO number FILTER number > 20 RETURN { year: year, month: month, number: number } |
最大/最小计算
SQL:
1 2 3 4 |
SELECT MIN(dateRegistered) AS minDate, MAX(dateRegistered) AS maxDate FROM users WHERE active = 1; |
AQL:
1 2 3 4 5 6 |
FOR user IN users FILTER user.active == 1 COLLECT AGGREGATE minDate = MIN(user.dateRegistered), maxDate = MAX(user.dateRegistered) RETURN { minDate, maxDate } |
分组汇集
SQL: *
1 2 3 4 |
SELECT gender, GROUP_CONCAT(id) AS userIds FROM users WHERE active = 1 GROUP BY gender; |
AQL:
1 2 3 4 5 6 7 8 |
FOR user IN users FILTER user.active == 1 COLLECT gender = user.gender INTO usersByGender RETURN { gender: gender, userIds: usersByGender[*].user._key } |
JOINS
与关系数据库中的连接类似,ArangoDB也有自己的连接实现
内连接(inner join)
SQL:
1 2 3 |
SELECT * FROM users INNER JOIN friends ON (friends.user = users.id); |
AQL:
可以通过嵌套FOR循环和使用filter语句:在AQL中轻松地表达内部连接。
1 2 3 4 |
FOR user IN users FOR friend IN friends FILTER friend.user == user._key RETURN MERGE(user, friend) |
注:
在AQL中,首选方法是从各个子属性的不同集合中返回文档部分,以避免属性名称冲突,例如:
1 2 3 4 |
FOR user IN users FOR friend IN friends FILTER friend.user == user._key RETURN { user: user, friend: friend } |
还可以将匹配文档返回到水平列表中:
1 2 3 4 5 6 7 8 9 |
FOR user IN users RETURN { user: user, friends: ( FOR friend IN friends FILTER friend.user == user._key RETURN friend ) } |
外连接(outer join)
SQL:
1 2 3 |
SELECT * FROM users LEFT JOIN friends ON (friends.user = users.id); |
AQL:
在AQL中没有直接支持外部连接,但是可以使用子查询实现:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
FOR user IN users LET friends = ( FOR friend IN friends FILTER friend.user == user._key RETURN friend ) FOR friendToJoin IN ( LENGTH(friends) > 0 ? friends : [ { /* no match exists */ } ] ) RETURN { user: user, friend: friend } |
推荐阅读
-
Spring Boot 视频网站:技术选择与架构设计
-
近日,由厦门市应急大队与厦门市电子科技有限公司合作建设的厦门市应急大队三维电子沙盘顺利通过专家验收。
-
[机器学习和神经网络荣获诺贝尔奖] 科学边界的扩展与技术创新 - IV.个人对机器学习和神经网络的看法
-
H.266 与 H.265、AV1、H.264 的比较
-
Docker:Mysql 8.0 解决了 sql_mode = only_full_group_by 问题
-
【论文阅读】分离与联合:用于少量学习的简单元传输-摘要
-
ROS 理论与实践学习笔记 - 5 ROS 机器人系统模拟 URDF 集成 Gazebo
-
使用 beeline 命令将 DataGrip 远程连接到 Spark-sql和 hive
-
Oracle 数据库技术分享] BLOB 和 CLOB 的选择与应用
-
查看 SQL 执行计划解释