Događaj
Steknite certifikat za Microsoft Fabric – besplatno!
19. stu 23 - 10. pro 23
Tim Microsoft Fabric Community na ograničeno vrijeme nudi besplatne vaučere za ispit DP-600.
Pripremite se sadaOvaj preglednik više nije podržan.
Prijeđite na Microsoft Edge, gdje vas čekaju najnovije značajke, sigurnosna ažuriranja i tehnička podrška.
Applies to: SQL Server 2017 (14.x) and later Azure SQL Database Azure SQL Managed Instance
This sample provides a Transact-SQL script to create a graph database with nodes and edges and then use the new MATCH clause to match some patterns and traverse through the graph. This sample script works on both Azure SQL Database and SQL Server 2017 (14.x) and later versions.
This sample creates a graph schema for a hypothetical social network that has People
, Restaurant
and City
nodes. These nodes are connected to each other using Friends
, Likes
, LivesIn
and LocatedIn
edges. The following diagram shows a sample schema with restaurant
, city
, person
nodes and LivesIn
, LocatedIn
, Likes
edges.
The following sample script uses the new T-SQL syntax to create node and edge tables. Learn how to insert data into node and edge tables using INSERT
statement and also shows how to use MATCH
clause for pattern matching and navigation.
This script performs the following steps:
GraphDemo
.-- Create a GraphDemo database
IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'graphdemo')
CREATE DATABASE GraphDemo;
GO
USE GraphDemo;
GO
-- Create NODE tables
CREATE TABLE Person (
ID INTEGER PRIMARY KEY,
name VARCHAR(100)
) AS NODE;
CREATE TABLE Restaurant (
ID INTEGER NOT NULL,
name VARCHAR(100),
city VARCHAR(100)
) AS NODE;
CREATE TABLE City (
ID INTEGER PRIMARY KEY,
name VARCHAR(100),
stateName VARCHAR(100)
) AS NODE;
-- Create EDGE tables.
CREATE TABLE likes (rating INTEGER) AS EDGE;
CREATE TABLE friendOf AS EDGE;
CREATE TABLE livesIn AS EDGE;
CREATE TABLE locatedIn AS EDGE;
Now, we'll insert data to represent the relationships.
likes
edge.
$node_id
from $from_id
and $to_id
columns.livesIn
edge to associate persons with the city where they live.locatedIn
edge to associate restaurants with the city where they are located.friendOf
edge to associated friends.-- Insert data into node tables. Inserting into a node table is same as inserting into a regular table
INSERT INTO Person (ID, name)
VALUES (1, 'John')
, (2, 'Mary')
, (3, 'Alice')
, (4, 'Jacob')
, (5, 'Julie');
INSERT INTO Restaurant (ID, name, city)
VALUES (1, 'Taco Dell','Bellevue')
, (2, 'Ginger and Spice','Seattle')
, (3, 'Noodle Land', 'Redmond');
INSERT INTO City (ID, name, stateName)
VALUES (1,'Bellevue','WA')
, (2,'Seattle','WA')
, (3,'Redmond','WA');
-- Insert into edge table. While inserting into an edge table,
-- you need to provide the $node_id from $from_id and $to_id columns.
/* Insert which restaurants each person likes */
INSERT INTO likes
VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM Restaurant WHERE ID = 1), 9)
, ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM Restaurant WHERE ID = 2), 9)
, ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
, ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
, ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9);
/* Associate in which city live each person*/
INSERT INTO livesIn
VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM City WHERE ID = 1))
, ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM City WHERE ID = 2))
, ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM City WHERE ID = 3))
, ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM City WHERE ID = 3))
, ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM City WHERE ID = 1));
/* Insert data where the restaurants are located */
INSERT INTO locatedIn
VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 1), (SELECT $node_id FROM City WHERE ID =1))
, ((SELECT $node_id FROM Restaurant WHERE ID = 2), (SELECT $node_id FROM City WHERE ID =2))
, ((SELECT $node_id FROM Restaurant WHERE ID = 3), (SELECT $node_id FROM City WHERE ID =3));
/* Insert data into the friendOf edge */
INSERT INTO friendOf
VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2))
, ((SELECT $NODE_ID FROM Person WHERE ID = 2), (SELECT $NODE_ID FROM Person WHERE ID = 3))
, ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1))
, ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2))
, ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 4));
Next, we'll query the data to find insights from the data.
-- Find Restaurants that John likes
SELECT Restaurant.name
FROM Person, likes, Restaurant
WHERE MATCH (Person-(likes)->Restaurant)
AND Person.name = 'John';
-- Find Restaurants that John's friends like
SELECT Restaurant.name
FROM Person person1, Person person2, likes, friendOf, Restaurant
WHERE MATCH(person1-(friendOf)->person2-(likes)->Restaurant)
AND person1.name='John';
-- Find people who like a restaurant in the same city they live in
SELECT Person.name
FROM Person, likes, Restaurant, livesIn, City, locatedIn
WHERE MATCH (Person-(likes)->Restaurant-(locatedIn)->City AND Person-(livesIn)->City);
Finally, a more advanced query finds the friends of friends of friends. This query excludes those cases where the relationship "loops back". For example, Alice is a friend of John; John is a friend of Mary; and Mary in turn is a friend of Alice. This causes a "loop" back to Alice. In many cases, it is necessary to explicitly check for such loops and exclude the results.
-- Find friends-of-friends-of-friends, excluding those cases where the relationship "loops back".
-- For example, Alice is a friend of John; John is a friend of Mary; and Mary in turn is a friend of Alice.
-- This causes a "loop" back to Alice. In many cases, it is necessary to explicitly check for such loops and exclude the results.
SELECT CONCAT(Person.name, '->', Person2.name, '->', Person3.name, '->', Person4.name)
FROM Person, friendOf, Person as Person2, friendOf as friendOffriend, Person as Person3, friendOf as friendOffriendOfFriend, Person as Person4
WHERE MATCH (Person-(friendOf)->Person2-(friendOffriend)->Person3-(friendOffriendOfFriend)->Person4)
AND Person2.name != Person.name
AND Person3.name != Person2.name
AND Person4.name != Person3.name
AND Person.name != Person4.name;
Clean up the schema and database created for the sample in SQL Server.
USE graphdemo;
go
DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS Restaurant;
DROP TABLE IF EXISTS City;
DROP TABLE IF EXISTS friendOf;
DROP TABLE IF EXISTS livesIn;
DROP TABLE IF EXISTS locatedIn;
USE master;
go
DROP DATABASE graphdemo;
go
Clean up the schema and database created for the sample in Azure SQL Database.
--Connect to the graphdemo database
DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS Person;
DROP TABLE IF EXISTS Restaurant;
DROP TABLE IF EXISTS City;
DROP TABLE IF EXISTS friendOf;
DROP TABLE IF EXISTS livesIn;
DROP TABLE IF EXISTS locatedIn;
--Connect to the master database
DROP DATABASE graphdemo;
go
Događaj
Steknite certifikat za Microsoft Fabric – besplatno!
19. stu 23 - 10. pro 23
Tim Microsoft Fabric Community na ograničeno vrijeme nudi besplatne vaučere za ispit DP-600.
Pripremite se sada