ArangoDB之SQL/AQL-比较

翻译：https://arangodb.com/why-arangodb/sql-aql-comparison/

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

2	UPDATE document IN collection options UPDATE keyExpression WITH document IN collection options

添加新字段/属性（计算值）

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      }