Neo4j - CQL introduction

Neo4j's Cypher language is built to process graphic data, and CQL stands for Cypher query language. Like Oracle database with query language SQL, neo4j has CQL as query language

• It is the query language of Neo4j graphics database
• Is a declarative pattern matching language
• Follow SQL syntax
• Grammar is a very simple, human and readable format

CQL command	effect
CREATE	Create nodes, relationships, and attributes
MATCH	Retrieve data about nodes, relationships, and attributes
RETURN	Return query results
WHERE	Provide conditional filtering to retrieve data
DELETE	Delete nodes and relationships
REMOVE	Delete attributes of nodes and relationships
ORDER BY	Sort and retrieve data
SET	Add or update labels

Common commands

Initialize CQL

create (Laojiumen:teleplay {name:'Laojiumen',author:"Third uncle of Southern Sect",bron:2016})
create (The Lost Tomb:teleplay {name:'The Lost Tomb',author:"Third uncle of Southern Sect",bron:2016})
CREATE (Zhang Qishan:person {name:'Zhang Qishan',actor:'Chen Weiting',level:'Upper three doors'})
CREATE (February red:person {name:'February red',actor:'Zhang Yixing',level:'Upper three doors'})
CREATE (Half prune:person {name:'Half prune',actor:'Li Naiwen',level:'Upper three doors'})
CREATE (Tangerine peel four:person {name:'Tangerine peel four',actor:'Hu Yunhao',level:'Pingsanmen'})
CREATE (Wu Laogou:person {name:'Wu Laogou',actor:'Zhang Luyi',level:'Pingsanmen'})
CREATE (Black backed old six:person {name:'Black backed old six',actor:'Li Zonghan',level:'Pingsanmen'})
CREATE (Jie Jiuye:person {name:'Jie Jiuye',actor:'Yang Zizhen',level:'Lower three doors'})
CREATE (Huo Jinxi:person {name:'Huo Jinxi',actor:'Wang Meimei',level:'Lower three doors'})
CREATE (Qi Tiezui:person {name:'Qi Tiezui',actor:'Ying Haoming',level:'Lower three doors'})
CREATE (Yin Xinyue:person {name:'Yin Xinyue',actor:'Zhao Liying'})
CREATE (Adjutant Zhang:person {name:'Adjutant Zhang',actor:'Zhang MingEn'})
CREATE (Girl:person {name:'Girl',actor:'Yuan Bingyan'})
CREATE (Li Sidi:person {name:'Li Sidi',actor:'Wu Yitao'})
CREATE (Chen Wenjin:person {name:'Chen Wenjin',actor:'Tao Luoyi'})
CREATE (Grandma tangerine peel:person {name:'Grandma tangerine peel',actor:'Tong Xiaomei'})
CREATE (Wu Yiqiong:person {name:'Wu Yiqiong'})
CREATE (Wu Erbai:person {name:'Wu Erbai',actor:'Wang Jinsong'})
CREATE (Three provinces of Wu:person {name:'Three provinces of Wu',actor:'Zhang Zhiyao'})
CREATE (Deconnection:person {name:'Deconnection',actor:'Yao Yichen'})
CREATE (Huo Xiangu:person {name:'Huo Xiangu',actor:'Liu Xuehua'})
CREATE (Qi Yu:person {name:'Qi Yu'})
CREATE (Zhang Qiling:person {name:'Zhang Qiling',actor:'Yang Yang'})
CREATE (Wu Xie:person {name:'Wu Xie',actor:'Li Yifeng'})
CREATE (Xie Yuchen:person {name:'Xie Yuchen',actor:'Qiao Zhenyu'})
CREATE (Huo Ling:person {name:'Huo Ling',actor:'Luo Chenshu'})
CREATE (Wang pangzi:person {name:'Wang pangzi',actor:'Liu Tianzuo'})
CREATE (Huo XiuXiu:person {name:'Huo XiuXiu',actor:'Liu Ruoyan'})
CREATE 
(Laojiumen)-[:role]->(Zhang Qishan),
(Laojiumen)-[:role]->(February red),
(Laojiumen)-[:role]->(Half prune),
(Laojiumen)-[:role]->(Tangerine peel four),
(Laojiumen)-[:role]->(Wu Laogou),
(Laojiumen)-[:role]->(Black backed old six),
(Laojiumen)-[:role]->(Jie Jiuye),
(Laojiumen)-[:role]->(Huo Jinxi),
(Laojiumen)-[:role]->(Qi Tiezui),
(Laojiumen)-[:role]->(Yin Xinyue),
(Laojiumen)-[:role]->(Adjutant Zhang),
(Laojiumen)-[:role]->(Girl),
(Laojiumen)-[:role]->(Grandma tangerine peel),
(The Lost Tomb)-[:role]->(Wu Xie),
(The Lost Tomb)-[:role]->(Xie Yuchen),
(The Lost Tomb)-[:role]->(Zhang Qiling),
(The Lost Tomb)-[:role]->(Wang pangzi),
(Zhang Qishan)-[:companion in adversity]->(February red),
(Zhang Qishan)-[:spouse]->(Yin Xinyue),
(Adjutant Zhang)-[:adjutant]->(Zhang Qishan),
(Zhang Qishan)-[:Zhang family]->(Zhang Qiling),
(February red)-[:spouse]->(Girl),
(February red)-[:master]->(Tangerine peel four),
(Half prune)-[:unknown]->(Li Sidi),
(Tangerine peel four)-[:unknown]->(Chen Wenjin),
(Wu Laogou)-[:father and son]->(Wu Yiqiong),
(Wu Laogou)-[:father and son]->(Wu Erbai),
(Wu Laogou)-[:father and son]->(Three provinces of Wu),
(Wu Yiqiong)-[:father and son]->(Wu Xie),
(Three provinces of Wu)-[:lover]->(Chen Wenjin),
(Wu Laogou)-[:Matchmaker]->(Jie Jiuye),
(Jie Jiuye)-[:father and son]->(Deconnection),
(Deconnection)-[:Adoptive father and son]->(Xie Yuchen),
(February red)-[:master]->(Xie Yuchen),
(Huo Jinxi)-[:Unrequited love]->(February red),
(Huo Jinxi)-[:Aunt and nephew]->(Huo Xiangu),
(Huo Xiangu)-[:mother and daughter]->(Huo Ling),
(Huo Ling)-[:Aunt and nephew]->(Huo XiuXiu),
(Huo Xiangu)-[:old sweetheart]->(Wu Laogou),
(Wu Xie)-[:Iron triangle]->(Zhang Qiling),
(Wu Xie)-[:Iron triangle]->(Wang pangzi),
(Zhang Qiling)-[:Iron triangle]->(Wu Xie),
(Zhang Qiling)-[:Iron triangle]->(Wang pangzi),
(Wang pangzi)-[:Iron triangle]->(Zhang Qiling),
(Wang pangzi)-[:Iron triangle]->(Wu Xie),
(Qi Tiezui)-[:unknown]->(Qi Yu),
(Tangerine peel four)-[:grandson]->(Grandma tangerine peel)

CREATE create

Create statement is a model creation statement, which is used to create a data model, similar to database insert

Create node:

#Create a simple node
create (n);
#Create multiple nodes
create (n),(m);
#Create a node with labels and attributes and return the node
create (n:teleplay {name:'Laojiumen del',bron:2016}) return n;

Create relationship:

Neo4j relationship is divided into two main types: ① one-way relationship ② two-way relationship

#Use the new node to create relationships and present them
CREATE (n:person {name:'February red del'})-[r:companion in adversity]->(m:person {name:'Zhang Qishan del'}) RETURN n,r,m
#Create a relationship with attributes using known nodes
CREATE (n:person {name:'Zhang Qishan del'}),(m:person{name:'February red del'}) CREATE (n)-[r:companion in adversity{relation:'Iron Man'}]->(m) RETURN n,r,m;
#Retrieve the details of the relationship node
CREATE (n:person)-[r]-(m:person) RETURN n,m

Create full path:

CREATE p=(:person{name:'Yin Xinyue del'})-[:spouse]->(:person{name:'Zhang Qishan del'})<-[:adjutant]-(:person {name:'Adjutant Zhang del'}) RETURN p

MATCH query

The MATCH command is used to: ① obtain data about nodes and attributes from the database; ② obtain data about nodes, relationships and attributes from the database
Similar to database select

# Paging query 25 nodes
MATCH (n) RETURN n LIMIT 25
# Query the last three people
MATCH (n:person{level:"Upper three doors"}) RETURN n
 perhaps
MATCH (m{level:"Upper three doors"}) RETURN m
# Query Zhang Qishan's network
match (n)-[*]->(m:person{name:"Zhang Qishan"})-[*]->(l) return n,m,l

RETURN return

The RETURN clause is used to: ① retrieve some attributes of the node ② retrieve all attributes of the node ③ retrieve some attributes of the node and the association relationship ④ retrieve all attributes of the node and the association relationship

# Inquire about friends in need
match (n)-[r:companion in adversity]->(m) return n,r,m
# Query tomb robbing note author and role
match (n:teleplay{name:"The Lost Tomb"})-[r:role]->(m) return n.author,m.name

DELETE delete

The DELETE command is used to: ① DELETE nodes ② DELETE nodes and related nodes and relationships

# Delete a node (premise: the node does not have a relationship)
MATCH (n:teleplay{name:"Laojiumen del"}) delete n
# Delete relationship
MATCH (n:person{name:"February red del"})<-[r]-(m) delete r return type(r)

REMOVE delete

The REMOVE command is used to: ① delete the label of a node or relationship; ② delete the attribute of a node or relationship

#Delete attribute
MATCH (n:person {name:"Adjutant Zhang del"}) remove n.name return n
#Create node
CREATE (m:role:person {name:"February red del"})
#delete a tap
match (m:role:person {name:"February red del"}) remove m:person return m

SET clause

The SET command is used to: ① add new attributes to existing nodes or relationships; ② add or update attribute values

# Add or change Zhang Qishan del age to 26
MATCH (n:person {name:"Zhang Qishan del"}) set n.age=26 return n

ORDER BY sort

The ORDER BY command is used to sort the results returned by the MATCH query. You can sort rows in ascending or descending order. By default, rows are sorted in ascending order. If you want to sort the results in descending order, you need to use the DESC clause

# Descending by id
MATCH (n:person) RETURN id(n),n.name order by id(n) desc

UNION clause

Like SQL, Neo4j CQL has two clauses that combine two different results into a set of results:
① UNION: combines the common rows in two sets of results and returns them to one set of results. It does not return duplicate rows from two nodes
Restriction: the result column type and the name from the two sets of results must match, which means that the column name should be the same and the column data type should be the same
② UNION ALL: it combines and returns all rows of two result sets into a single result set. It also returns rows that are repeated by two nodes
Limitation: the result column type and the names from the two result sets must match, which means that the column name should be the same and the column data type should be the same

# UNION
MATCH (n:role) RETURN n.name as name UNION MATCH (m:person) RETURN m.name as name

# UNION ALL
MATCH (n:role) RETURN n.name as name UNION all MATCH (m:person) RETURN m.name as name

LIMIT and SKIP clauses

CQL provides LIMIT clause and SKIP to filter or LIMIT the number of rows returned by query
LIMIT: returns the first few lines
SKIP: ignore the first few lines

# Return to the first 10 lines
MATCH (n:person) RETURN n LIMIT 10
# Ignore the first 5 lines
MATCH (n:person) RETURN n SKIP 5

NULL value

Neo4j CQL treats NULL values as missing or undefined values for the attributes of nodes or relationships. When creating a node with an existing node label name but no attribute value is specified, it will create a new node with NULL attribute value

match (n:person) where n.name is null return id(n),n.name

IN operator

In is used to provide a collection of values, similar to in in SQL

match (n:person) where n.name in['Zhang Qishan','February red'] return id(n),n.name

INDEX index

Neo4j SQL supports indexes on node or relational attributes to improve application performance. You can create indexes for the attributes of all nodes with the same label name, and then use these index columns on the MATCH or WHERE or IN operator to improve the execution of CQL commands

Index operation:
① CREATE INDEX create index
② DROP INDEX drop index

# Create index
create index on :person (name)
# Delete index
drop index on :person (name)

UNIQUE constraint

Function of UNIQUE constraint: ① avoid duplicate records (UNIQUE constraint) ② enforce data integrity rules

#Create unique constraint
create constraint on (n:teleplay) assert n.name is unique
#Delete unique constraint
drop constraint on (n:teleplay) assert n.name is unique

DISTINCT

The usage is like the distinct keyword in SQL, which returns all different values

match (n:person) return distinct(n.name)

Common functions

function	effect
String string	Used to use String literals
Aggregation	Used to perform some aggregation operations on CQL query results
Relationship relationship	It is used to obtain the details of the relationship, such as startnode, endnode, etc

String function

function	effect
UPPER	Use to change all letters to uppercase letters
LOWER	Use to change all letters to lowercase
SUBSTRING	Used to get the substring of the given String
REPLACE	Substring used to replace a string

MATCH (n) RETURN id(n),n.name,substring(n.name,0,2)

AGGREGATION aggregation

The aggregate function used in the RETURN clause is similar to the GROUP BY clause in SQL. You can use the RETURN + aggregate function in the MATCH command to process a set of nodes and RETURN some aggregate values

function	effect
COUNT	Returns the number of lines returned by the MATCH command
MAX	A set of lines returned from the MATCH command returns the maximum value
MIN	Returns the minimum value of a set of rows returned by the MATCH command
SUM	Returns the sum value of all rows returned by the MATCH command
AVG	Returns the average of all rows returned by the MATCH command

MATCH (n) RETURN count(n)

Relation function

Know the details of the relationship when obtaining the details of the start node and end node

function	effect
STARTNODE	Used to know the start node of the relationship
ENDNODE	Used to know the end node of the relationship
ID	ID used to know the relationship
TYPE	TYPE used to know a relationship in a string representation

match (m)-[r] ->(n) return id(r),type(r)

Neo4j admin use

Database backup

When backing up, restoring, and migrating neo4j data, close neo4j

#Close neo4j
neo4j stop
#Backup graph.db is the backup database, d:/graph_backup.dump is the backup address
neo4j-admin dump --database=graph.db --to=d:/graph_backup.dump

Database recovery

Before restoring and migrating neo4j, close the neo4j service

#Data import
neo4j-admin load --from=d:/graph_backup.dump --database=graph.db --force
#Restart service
neo4j start