orientdb 常用语句

发表于 更新于 分类于 阅读次数： Waline： 本文字数： 8.8k 阅读时长 ≈ 8 分钟

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 ;

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

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

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

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

查看节点类型个数

// show tables;

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}>

合并重复节点

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

查询某个类别(表)结构

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