orientdb 常用语句

orientdb启动后可以在 http://localhost:2480 打开前端管理界面操作,也可以在shell里操作

orientdb的很多语句很像mysql和neo4j,学习的时候可以用来对比

常用语句

orientdb mysql
连接数据库 CONNECT remote:127.0.0.1 root root mysql -u root -p root
查看所有数据库 LIST DATABASES show databases
创建数据库 CREATE DATABASE remote:127.0.0.1/db_test_wkq root root create database db_test_wkq
使用某个数据库 CONNECT remote:127.0.0.1/db_test_wkq root root use db_test_wkq
查询节点类型(表)个数 LIST CLASSES
select expand(classes) from metadata:schema
show tables
创建某类节点 CREATE CLASS TestVertex extends V create table table1
查看某个类型的结构 INFO CLASS OUser desc table1
查看某个类型的数据 BROWSE CLASS OUser select * from OUser limit 20
CREATE DATABASE <database-url> [<user>] [<password>] [<storage-type>] [<db-type>] [<[options]>]

创建数据库 CREATE DATABASE PLOCAL:/usr/local/orientdb/databases/db_test_wkq
CREATE DATABASE remote:127.0.0.1/db_test_wkq root root plocal graph

orientdb mysql
新增节点(CRUD) insert into v1 (brand, name) values (“宝马”, “车型1”) insert into v1 (brand, name) values (“宝马”, “车型1”)
新增节点(Graph) CREATE VERTEX V1 SET brand = ‘宝马’, name = ‘车型1’
更新节点(CRUD) UPDATE Employee SET local=TRUE WHERE city=’London’ UPDATE Employee SET local=TRUE WHERE city=’London’
更新节点(Graph) UPDATE Employee MERGE { local : TRUE } WHERE city=’London’
删除节点 DELETE FROM Employee WHERE city <> ‘London’ DELETE FROM Employee WHERE city <> ‘London’
新增边 CREATE EDGE E FROM #22:33 TO #22:55 CONTENT { “name”: “Jay”, “surname”: “Miner” }
更新边 UPDATE EDGE hasAssignee SET foo = ‘bar’ UPSERT WHERE id = 56
删除边 DELETE EDGE #22:38482
查询 MATCH {class: Person, as: people, where: (name = ‘John’)} RETURN people
查看某个类型的数据 SELECT FROM Person

基础操作

连接数据库

// mysql -u root -p root
CONNECT remote:localhost root root

查看有哪些库

mysql show databses ;
orientdb list databases ;

1
2
3
4
5
6
7
orientdb {server=remote:localhost}> list databases;

Found 3 databases:

* test
* demodb
* test_wkq

创建数据库

create database remote:localhost/mydb root root plocal graph -restore=/tmp/backup
create database remote:localhost/test root root

1
2
3
4
5
6
7
orientdb {server=remote:localhost/test}> create database remote:localhost/test root admin

Creating database [remote:localhost/test] using the storage type [PLOCAL]...
Database created successfully.

Current database is: remote:localhost/test
orientdb {db=test}> list databases;

使用某个库

// user test_wkq

1
2
3
orientdb {server=remote:localhost}> CONNECT remote:localhost/test_wkq root root

Connecting to database [remote:localhost/test_wkq] with user 'root'...OK

查看节点类型个数

// show tables;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
orientdb {db=test_wkq}> LIST CLASSES;

CLASSES
+----+-------------------+-------------+---------------------------------------------------------+-----+
|# |NAME |SUPER-CLASSES|CLUSTERS |COUNT|
+----+-------------------+-------------+---------------------------------------------------------+-----+
|0 |_studio | |_studio(17),_studio_1(18),_studio_2(19),_studio_3(20) | 1|
|1 |E | |e(13),e_1(14),e_2(15),e_3(16) | 0|
|2 |FriendOf |[E] |friendof(25),friendof_1(26),friendof_2(27),friendof_3(28)| 2|
|3 |OFunction | |ofunction(6) | 0|
|4 |OGeometryCollection|[OShape] |- | 0|
|5 |OIdentity | |- | 0|
|6 |OLineString |[OShape] |- | 0|
|7 |OMultiLineString |[OShape] |- | 0|
|8 |OMultiPoint |[OShape] |- | 0|
|9 |OMultiPolygon |[OShape] |- | 0|
|10 |OPoint |[OShape] |- | 0|
|11 |OPolygon |[OShape] |- | 0|
|12 |ORectangle |[OShape] |- | 0|
|13 |ORestricted | |- | 0|
|14 |ORole |[OIdentity] |orole(4) | 3|
|15 |OSchedule | |oschedule(8) | 0|
|16 |OSequence | |osequence(7) | 0|
|17 |OShape | |- | 0|
|18 |OTriggered | |- | 0|
|19 |OUser |[OIdentity] |ouser(5) | 3|
|20 |Person |[V] |person(21),person_1(22),person_2(23),person_3(24) | 3|
|21 |V | |v(9),v_1(10),v_2(11),v_3(12) | 0|
+----+-------------------+-------------+---------------------------------------------------------+-----+
| |TOTAL | | | 12|
+----+-------------------+-------------+---------------------------------------------------------+-----+

创建某种节点

orientdb {db=test_wkq}> CREATE CLASS Table1 extends V

Class created successfully.

orientdb> CREATE CLASS Student

Class created successfully. Total classes in database now: 15

赋值

orientdb> CREATE PROPERTY Student.name STRING

Property created successfully with id=1

orientdb> CREATE PROPERTY Student.surname STRING

Property created successfully with id=2

orientdb> CREATE PROPERTY Student.birthDate DATE

Property created successfully with id=3

查看某个类型的结构

// desc OUser
orientdb {db=test_wkq}> INFO CLASS OUser

CLASS ‘OUser’

Records…………..: 3
Super classes……..: [OIdentity]
Default cluster……: ouser (id=5)
Supported clusters…: ouser(5)
Cluster selection….: round-robin
Oversize………….: 0.0

PROPERTIES
+—-+——–+—————–+———+——–+——–+—-+—-+——-+——-+
|# |NAME |LINKED-TYPE/CLASS|MANDATORY|READONLY|NOT-NULL|MIN |MAX |COLLATE|DEFAULT|
+—-+——–+—————–+———+——–+——–+—-+—-+——-+——-+
|0 |password| |true |false |true | | |default| |
|1 |roles |ORole |false |false |false | | |default| |
|2 |name | |true |false |true |1 | |ci | |
|3 |status | |true |false |true | | |default| |
+—-+——–+—————–+———+——–+——–+—-+—-+——-+——-+

INDEXES (1 altogether)
+—-+———-+———-+
|# |NAME |PROPERTIES|
+—-+———-+———-+
|0 |OUser.name|[name] |
+—-+———-+———-+

orientdb> ALTER PROPERTY Student.name MIN 3
Property updated successfully

orientdb> ALTER PROPERTY Student.name MANDATORY true

查看某个类型的数据(默认最多显示20条)

// select * from OUser limit 20 ;
orientdb {db=test_wkq}> BROWSE CLASS OUser

+—-+—-+——+——+——+——+—————————————————————————————————————–+
|# |@RID|@CLASS|name |status|roles |password |
+—-+—-+——+——+——+——+—————————————————————————————————————–+
|0 |#5:0|OUser |admin |ACTIVE|[#4:0]|{PBKDF2WithHmacSHA256}AFD543552898111E1C874350E7A09398E20A15B098E2010E:E42843B52BE6EDA36DA6282920E75876C7DDFBA…|
|1 |#5:1|OUser |reader|ACTIVE|[#4:1]|{PBKDF2WithHmacSHA256}9339EAE81055AE1CA400CBC511AAD42DE5E416841B709D53:D2F810E5E1D2EFA7E7E7DA65E7D1338F2F76904…|
|2 |#5:2|OUser |writer|ACTIVE|[#4:2]|{PBKDF2WithHmacSHA256}67CED97F7AF9C6B445E120CF4B06EA4D40A745DB330D42D6:A5B4D1A71C166578EDF78B4873A5F74E1D5330B…|
+—-+—-+——+——+——+——+—————————————————————————————————————–+

查看第一条记录

// select * from table order by id asc limit 1 ;
orientdb {db=test_wkq}> DISPLAY RECORD 0

DOCUMENT @class:OUser @rid:#5:0 @version:1
+—-+——–+—————————————————————————————————————————–+
|# |NAME |VALUE |
+—-+——–+—————————————————————————————————————————–+
|0 |password|{PBKDF2WithHmacSHA256}AFD543552898111E1C874350E7A09398E20A15B098E2010E:E42843B52BE6EDA36DA6282920E75876C7DDFBA4A135503E:65536|
|1 |roles |[#4:0] |
|2 |name |admin |
|3 |status |ACTIVE |
+—-+——–+—————————————————————————————————————————–+

orientdb {db=test_wkq}> SELECT Version FROM OUser;

+—-+———+
|# |Version|
+—-+———+
|0 | |
|1 | |
|2 | |
+—-+———+

3 item(s) found. Query executed in 0.029 sec(s).
orientdb {db=test_wkq}>

合并重复节点

1
2
3
4
select expand($c)
let $a = ( SELECT EXPAND(out('E1').out('E3')) FROM V1 WHERE id = <someIdThatV1Has>),
$b = ( SELECT EXPAND(out('E1').out('E2').out('E3')) FROM V1 WHERE id = <someIdThatV1Has>),
$c = unionAll( $a, $b )

查询所有类别

select expand(classes) from metadata:schema

查询某个类别(表)结构

1
2
3
4
5
6
7
select expand(properties) from (
select expand(classes) from metadata:schema
) where name = 'OUser'

select customFields from (
select expand(classes) from metadata:schema
) where name="OUser"

查询所有索引

select expand(indexes) from metadata:indexmanager

Querying database metadata

SELECT FROM metadata:database

Querying storage metadata

SELECT FROM metadata:storage

// 创建节点类型
CREATE CLASS V1 EXTENDS V
// 插入节点
CREATE VERTEX V1 SET brand = ‘maruti’, name = ‘swift’

CREATE EDGE USER_RELATION_FRIEND FROM
(SELECT FROM userList where id = ${input.FROM_ID}) TO (SELECT FROM userList where id = ${input.TO_ID})
set weight=${input.WEIGHT}”}

SSELECT

SELECT FROM OUser

SELECT

SELECT FROM CLUSTER:Ouser

记录ID要对一个或多个记录ID执行,请使用标识符作为目标

SELECT FROM #10:3SELECT FROM [#10:1, #10:30, #10:5]

索引要对索引执行查询,请INDEX为目标名称添加前缀。

SELECT VALUE FROM INDEX:dictionary WHERE key='Jay'

模糊匹配

SELECT FROM OUser WHERE name LIKE 'l%'

排序

SELECT FROM Employee WHERE city='Rome' ORDER BY surname ASC, name ASC

聚合

SELECT SUM(salary) FROM Employee WHERE age < 40 GROUP BY job

分页

SELECT FROM Employee WHERE gender='male' LIMIT 20

分页-2

SELECT FROM Employee WHERE gender='male' LIMIT 20SELECT FROM Employee WHERE gender='male' SKIP 20 LIMIT 20SELECT FROM Employee WHERE gender='male' SKIP 40 LIMIT 20

INSERT

INSERT语句将新数据添加到类和集群。OrientDB支持三种语法形式,用于将新数据插入数据库。

标准的ANSI-92语法

INSERT INTO Employee(name, surname, gender) VALUES('Jay', 'Miner', 'M')

简化的ANSI-92语法

INSERT INTO Employee SET name='Jay', surname='Miner', gender='M'

JSON语法

INSERT INTO Employee CONTENT {name : 'Jay', surname : 'Miner', gender : 'M'}

UPDATE

该UPDATE语句更改了类和集群中现有数据的值。在OrientDB中,有两种形式的语法用于更新数据库上的数据。

标准的ANSI-92语法

UPDATE Employee SET local=TRUE WHERE city='London'

与MERGE关键字一起使用的JSON语法,它将更改与当前记录合并

UPDATE Employee MERGE { local : TRUE } WHERE city='London'

DELETE

DELETE FROM Employee WHERE city <> 'London'

CREATE INDEX T.id ON T(id) UNIQUE

Tutorial-Working-with-graphs

在1.4.x版本中,OrientDB开始将一些边缘作为轻量级边缘进行管理。轻量级边缘没有记录ID,但物理存储为顶点内的链接。请注意,OrientDB仅在边缘没有属性时才使用轻量级边缘,否则它使用标准边缘。

从逻辑的角度来看,Lightweight Edges是所有效果中的边缘,因此所有图形函数都可以使用它们。这是为了提高性能并减少磁盘空间。

由于轻量级边缘不作为数据库中的单独记录存在,因此某些查询将无法按预期工作。例如,

orientdb> SELECT FROM E
对于大多数情况,使用边连接顶点,因此该查询不会特别导致任何问题。但是,它不会在结果集中返回Lightweight Edges。如果您需要直接查询边缘(包括没有属性的边缘),请禁用“轻量级边缘”功能。

要禁用轻量级边缘功能,请执行以下命令。

orientdb> ALTER DATABASE CUSTOM useLightweightEdges=FALSE
您只需要执行一次此命令。OrientDB现在生成新边缘作为标准边缘,而不是轻量级边缘。请注意,这不会影响现有边缘。

orientdb> CREATE PROPERTY Owns.out LINK Person

orientdb> CREATE PROPERTY Owns.in LINK Car

References

[1] SQL-Commands
[2] Console-Commands
[3] queries-demo
[4] Tutorial-Working-with-graphs