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 ;
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
在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