ArangoDB 学习手记

最近做项目要用到ArangoDB，语法和之前PLSQL区别很大，极其不顺手，现在记录下代码片段，以备学习使用

统计记录总数

// AQL 语法
FOR t IN words 
	FILTER t.type == "01" AND t.isDelete == "0" 
	COLLECT WITH count INTO length 
	RETURN length

// SQL语法
SELECT COUNT(1) length 
	FROM words t
	WHERE t.type = '01' AND t.isDelete = '0'  

Group by 统计

// AQL
FOR t IN words 
	FILTER t.type == "01" AND t.isDelete == "0" 
	COLLECT dataState = t.dataState WITH COUNT INTO len 
	RETURN {'dataState': dataState, 'num': len}

//SQL
SELECT t.dataState, COUNT(1) 
	FROM words t 
	WHERE t.type = '01' AND t.isDelete = '0' 
	GROUP BY  t.dataState

查询区间记录（分页）

// AQL 语法
FOR t IN words FILTER t.type == "01" AND t.isDelete == "0" SORT t._id DESC LIMIT 0,10 RETURN t

// SQL语法
SELECT * FROM words t WHERE t.type = '01' AND t.isDelete = '0' ORDER BY t.id DESC LIMIT 0,10  

Distinct 关键字的用法

// AQL 语法
FOR t IN words 
	FILTER t.type == "01" AND t.isDelete == "0" 
	RETURN DISTINCT { name: t.name }

// SQL语法
SELECT DISTINCT t.name 
	FROM words t
	WHERE t.type = '01' AND t.isDelete = '0'  

更新数据

// AQL
FOR t IN words FILTER t._id IN ["10001","100002"] UPDATE t WITH {isDelete: "1"} IN words

// SQL
UPDATE words SET isDelete=1 WHERE id in ('10001','100002')

删除数据

// AQL
FOR t IN words FILTER t._id IN ["10001","100002"] REMOVE t IN words

// SQL
DELETE FROM words WHERE id in ('10001','100002')

关联查询后更新数据

// AQL
FOR r in (
	FOR v, e, p IN 1..1 OUTBOUND 'words/10001' relation
		FILTER e.type IN ['1','2']
		COLLECT id  = e._key WITH count INTO count
	RETURN { "id": id, "count": count }
)
FOR t IN words FILTER t._id == r.id UPDATE t WITH { "leaf": r.count == 0 } IN words

// SQL
UPDATE relation t INNER JOIN (
	SELECT ID, COUNT(1) FROM TABLE1 a
		LEFT JOIN TABLE2 b ON a.type = b.type
	WHERE a.type IN ('1','2') AND a._key = 'words/10001'
)  r
ON t._id = t.id 
SET t.heaf = t.count = 0