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     }