使用 T-SQL 建立圖形資料庫並執行一些模式比對查詢

適用於：Microsoft Fabric 中的 SQL Server 2017 （14.x） 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體 SQL Database

此範例提供 Transact-SQL 腳本來建立具有節點和邊緣的圖形資料庫，然後使用新的 MATCH 子句來比對某些模式並周遊圖形。 此範例腳本適用於 Azure SQL 資料庫 和 SQL Server 2017 （14.x） 和更新版本。

範例架構

此範例會為具有 People、 Restaurant和 City 節點的假設社交網路建立圖表架構。 這些節點會使用 Friends、 Likes、 LivesIn和 LocatedIn 邊緣彼此連線。 下圖顯示具有restaurant、、 person city節點和LivesIn、 LocatedInLikes 邊緣的範例架構。

範例指令碼

下列範例腳本會使用新的 T-SQL 語法來建立節點和邊緣數據表。 瞭解如何使用 INSERT 語句將數據插入節點和邊緣數據表，並示範如何使用 MATCH 子句進行模式比對和流覽。

此指令碼會執行下列步驟：

1. 建立名為 GraphDemo的資料庫。
2. 建立節點數據表。
3. 建立邊緣數據表。

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

現在，我們會插入數據來表示關聯性。

1. 將數據插入節點數據表。
   1. 插入節點數據表與插入一般數據表相同。
2. 在此案例中，將數據插入邊緣數據表，讓每個人喜歡的餐館都加入 likes 邊緣。
   1. 在插入邊緣數據表時，請提供 $node_id from $from_id 和資料 $to_id 行。
3. 將數據插入 livesIn 邊緣，以將人員與其居住的城市產生關聯。
4. 將數據插入 locatedIn 邊緣，以將餐廳與其所在城市產生關聯。
5. 將數據插入邊緣至 friendOf 相關聯的朋友。

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

接下來，我們會查詢數據，以從數據中尋找見解。

1. 使用圖表 MATCH 函式來尋找 John 喜歡的餐館。
2. 找到約翰的朋友喜歡的餐館。
3. 尋找喜歡同一個城市餐廳的人，他們住在同一個城市。

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

最後，更進階的查詢會尋找朋友的朋友。 此查詢會排除關聯性「回送」的案例。 例如，Alice 是 John 的朋友;約翰是瑪麗的朋友：瑪麗反過來又是愛麗絲的朋友。 這會導致「迴圈」回到Alice。 在許多情況下，必須明確檢查這類迴圈，並排除結果。

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

清除

清除在 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

清除在 Azure SQL 資料庫 中為範例建立的架構和資料庫。

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

下一步

• 圖形處理
• CREATE TABLE (SQL Graph)
• INSERT （SQL Graph）
• MATCH （Transact-SQL）