SQLServer图数据库有独到之处

    落得亦然篇简要介绍了图数据库的一些为主内容(新认识SQL Server2017
图数据库(一)),本篇通过对比关系型一些语法来体现图数据库模式之局部亮点,比如查询好,语句易理解等。

每当图数据库模型上构建查询的优势:

T-SQL
带被图表查询部分初的语法。在SELECT语句被我们发出一部分特别之报告词来关联点和限。让咱们来排一些,构建查询语句检索发帖和还原,如下:

  1. 咱找每个记录之有数单部分,发帖和恢复,因此我们要以FROM子句被引用两差ForumPosts’表,以此地方可以以部分生出意义之号:

    

FROM dbo.ForumPosts ReplyPost, dbo.ForumPosts RepliedPost

 

  1. 尽管我们会选其他别名,但是在处理图对象时最好好选择来含义之名字。
  2. 我们需要“posts”之间的涉嫌,而这个涉及就是是说明Reply_to’。语法如下:

    

FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost

 

  1. 在WHERE
    子句被,我们用关联有的阐明,用脚这种MATCH语句来落实关系:

    FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost

    WHERE MATCH(ReplyPost-(Reply_to)->RepliedPost)

 

  1. 以此语法很有意思:“-”破折号表示边的$From_id字段代表关系,然后“->”破折号和不止号用边的$To_id字段表示关系。
  2. 为知道那个别名有reply,那个别名有replied
    post,我们可构建一个询问字段列表:

    FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost

    WHERE MATCH(ReplyPost-(Reply_to)->RepliedPost)

 

  1. 在关系型模型中同效果的查询如下:
select RepliedPost.PostId,RepliedPost.PostTitle,

   ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle

  from Forum.ForumPosts ReplyPost, Forum.ForumPosts RepliedPost

  where ReplyPost.PostId=RepliedPost.ReplyTo

 

  1. 这些查询好相像,当然MATCH的语法更易理解。
  2. 实践完毕上面语句询问结果如下:

 

  1. 咱俩添加写这回复贴人的名。需要以FROM子句被上加‘ForumMembers’节点和‘Written_By’斯界限。语句如下:

    FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost, dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By

 

  1. 还要添加MATCH语句的中关系:
WHERE MATCH(ReplyPost-(Reply_to)->RepliedPost-(RepliedWritten_by)->RepliedMember)

 

  1. 顿时便足以于SELECT列表中上加回帖人的名字,最终之询问如下:
-- Posts 、members 和replies

   SELECT RepliedPost.PostId,RepliedPost.PostTitle,RepliedMember.MemberName,

   ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle

      FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost,

        dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By

   WHERE MATCH(ReplyPost-(Reply_to)->RepliedPost-(RepliedWritten_by)->RepliedMember)

 

  1. 在关系型模型中之应和查询如下:

    SELECT RepliedPost.PostId,RepliedPost.PostTitle,

    ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle,

    RepliedMember.MemberName

    FROM Forum.ForumPosts ReplyPost, Forum.ForumPosts RepliedPost,

    Forum.ForumMembers RepliedMember

    WHERE ReplyPost.PostId=RepliedPost.ReplyTo

        and RepliedPost.OwnerId=RepliedMember.MemberId
    

 

  1. 结果如下所示:

 

  1. 尚不够回复对象的名。像面一样多‘ForumMembers’
     ‘Written_By’在FROM子句中:

    From dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost,

    dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By,

    dbo.ForumMembers ReplyMember, Written_By ReplyWritten_By

 

  1. 接下来,修改MATCH子句,‘ReplyMember’要关联‘ReplyPost’,不过什么去处理是关系要休影响其他关系?需要因此不同之法子来贯彻:

    WHERE MATCH(ReplyMember<-(ReplyWritten_By)-ReplyPost-(Reply_to)->RepliedPost-(RepliedWritten_by)->RepliedMember)

 

  1. 瞩目是标记“<-”与前面的反方向,但是意义是相同的:一个当边表的$to_id同节点表的关系。
  2. 末段,还需要增加写在过来的积极分子姓名,代码如下:

    — Posts and members and their replies and members

    SELECT RepliedPost.PostId, RepliedPost.PostTitle,RepliedMember.MemberName,

    ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle,

    ReplyMember.MemberName [ReplyMemberName]

      FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost,
    
        dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By,
    
        dbo.ForumMembers ReplyMember, Written_By ReplyWritten_By
    
      WHERE MATCH(ReplyMember<-(ReplyWritten_By)-ReplyPost-(Reply_to)->RepliedPost-(RepliedWritten_by)->RepliedMember)
    

 

  1. 结果集如下:

 

  1. 以干项目查询的应和语句:

    SELECT RepliedPost.PostId,RepliedPost.PostTitle,

         RepliedMember.MemberName, ReplyPost.PostId as ReplyId,
    
      ReplyPost.PostTitle as ReplyTitle, ReplyMember.MemberName
    

    FROM Forum.ForumPosts ReplyPost, Forum.ForumPosts RepliedPost,

       Forum.ForumMembers RepliedMember, Forum.ForumMembers ReplyMember
    

    WHERE ReplyPost.PostId=RepliedPost.ReplyTo

        and RepliedPost.OwnerId=RepliedMember.MemberId
    
        and ReplyPost.OwnerId=ReplyMember.MemberId
    

 

  1. 当斯时候,可能在关系型模式中就关系的加码读取就见面愈加紧,而以觊觎数据模式中MATCH子句子相对就爱多。让我们看一下在觊觎数据模式遭遇有些好玩而实惠之地方。

统计每篇帖子的回复数

  SELECT distinct RepliedPost.PostID,RepliedPost.PostTitle,

          RepliedPost.PostBody,

          count(ReplyPost.PostID) over(partition by RepliedPost.PostID)

              as TotalReplies

   FROM dbo.ForumPosts ReplyPost, Reply_To, dbo.ForumPosts RepliedPost

   WHERE MATCH(ReplyPost-(Reply_To)->RepliedPost)

 

在这个话中我们统计了每一样首回复的数据,但是仅仅以一个范畴中,并无是于全部还原的栽培结构里。

根贴(主贴)的列表

咱们通过下不下MATCH的话语获得所有的根贴:

SELECT Post1.PostId,Post1.PostTitle

  FROM dbo.ForumPosts Post1

  WHERE $node_id not in (select $from_id from dbo.Reply_To

 

MATCH语法只是同意我们干三独或另行多之实业(比如简单单节点和一个干)。当我们仅仅想干中有数独之时段,只待一个正规的连接要子查询。如上面的言辞一样。

以结果吃补充加‘Level’字段

累加一个‘Level’字段,显示树结构。在T-SQL中产生一个大概的语法,叫做CTE实现递归。但是来一个题材,不克运用MATCH语法在一个派生表上,此时足采取CTE。如果发生必要,可以于CTE中以MATCH,但是反的便可怜了,有这般的限量。下面展示一下动正规的涉嫌才使用CTE来迭代,代码如下:

 with root as

  ( select $node_id as node_id,RootPosts.PostId,

           RootPosts.PostTitle,

           1 as Level, 0 as ReplyTo

     from dbo.ForumPosts RootPosts

     where $node_id not in (select $from_id from dbo.reply_to)

  union all

     select $node_id,ReplyPost.PostId, ReplyPost.PostTitle,

            Level+1 as [Level], root.PostId as ReplyTo

     from dbo.ForumPosts ReplyPost, reply_to, root

     where ReplyPost.$node_id=reply_to.$from_id

           and root.node_id=reply_to.$to_id

  )

  select PostId,PostTitle, Level, ReplyTo

  from root

 

 

探寻一个帖子中的装有回复

利用CTE递归语法,我们好据此平等种树结构检索一个帖子的享有回复。如果采用正规的语法不克于摸帖子1底早晚找贴子3,因为3凡是对准2之死灰复燃,而2是对1底恢复。使用CTE.当查询帖子1之装有回复时会检索贴子3。代码如下:

 with root as

  ( select $node_id as node_id,RootPosts.PostId,RootPosts.PostTitle,

           1 as Level, 0 as ReplyTo

     from dbo.ForumPosts RootPosts

     where PostId=1  

  union all

     select $node_id,ReplyPost.PostId, ReplyPost.PostTitle,

            Level+1 as [Level],root.PostId as ReplyTo

     from dbo.ForumPosts ReplyPost, reply_to, root

     where ReplyPost.$node_id=reply_to.$from_id

           and root.node_id=reply_to.$to_id

  )

  select PostId,PostTitle, Level, ReplyTo

  from root

 

咱吧得以回做,在树状结构被本梯次检索所有父贴。由于CTE不支持OUTER
join,所以只要于外表添加,代码如下:

with root as

  ( select LeafPost.$node_id as node_id,LeafPost.PostId,

           LeafPost.PostTitle

     from dbo.ForumPosts LeafPost

     where LeafPost.PostId=3  -- Single post

  union all

     select RepliedPost.$node_id as node_id,RepliedPost.PostId,

            RepliedPost.PostTitle

     from dbo.ForumPosts RepliedPost, Reply_to, root

     where root.node_id=Reply_to.$from_id

           and Reply_to.$to_id=RepliedPost.$node_id

  )

  select root.PostId,root.PostTitle,

         RepliedPost.PostId ParentPostId

  from root

  left join reply_to

       on root.node_id=reply_to.$from_id

  left join dbo.ForumPosts RepliedPost

       on reply_to.$to_id=RepliedPost.$node_id

找一个用户拥有帖子

询问一个因此装有的信,与帖子不同,这不待培育,要简单多:

--  Peter回复的所有帖子

   SELECT distinct RepliedPost.PostID,RepliedPost.PostTitle,

           RepliedPost.PostBody

   FROM dbo.ForumPosts ReplyPost, Reply_To, dbo.ForumPosts RepliedPost,

        dbo.ForumMembers Members,Written_By

   WHERE MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)->RepliedPost)

   and Members.MemberName='Peter'

  -- Peter发的所有帖子

   SELECT ReplyPost.PostID,ReplyPost.PostTitle,ReplyPost.PostBody,

          RepliedPost.PostId ReplyTo

   FROM dbo.ForumPosts ReplyPost, Reply_To, dbo.ForumPosts RepliedPost,

        dbo.ForumMembers Members,Written_By

   WHERE MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)->RepliedPost)

   and Members.MemberName='Peter'

 

或者你放在心上到上面两单查询的不比,就是于亮字段上是不是以DISTINCT。这个去重是盖Peter回复同一个帖子可以超越同样次于。

于范中检索Likes(点赞)

这个查询是有趣的:‘Likes’边是成员及发帖表的关系。每一个关乎都是唯一的,并无给外关联影响。代码如下:

--点赞的帖子或者被别人点赞的帖子。

  SELECT Post.PostID,Post.PostTitle,Member.MemberName

  FROM   dbo.ForumPosts Post, Likes,

         dbo.ForumMembers Member

  WHERE MATCH(Member-(Likes)->Post)

  -- 点赞的人或者被人点赞

  SELECT Member.MemberId,Member.MemberName LikeMember,

         LikedMember.MemberName LikedMemberName

  FROM dbo.ForumMembers Member, Likes, dbo.ForumMembers LikedMember

  WHERE MATCH(Member-(Likes)->LikedMember)

 

尚得死容易地凑信息,以获每个帖子或每个成员的毕竟的Likes。

--每个帖子总的likes

  select Post.PostId,Post.PostTitle,

         count(*) totalLikes

  from dbo.ForumPosts Post,Likes,

       dbo.ForumMembers Members

  where Match(Members-(Likes)->Post)

  group by PostId,PostTitle

  --每个成员总的点赞数select LikedMembers.MemberId,LikedMembers.MemberName,

         count(*) totalLikes

  from dbo.ForumMembers Members,Likes,

       dbo.ForumMembers LikedMembers

  where Match(Members-(Likes)->LikedMembers)

  group by LikedMembers.MemberId,

           LikedMembers.MemberName

 

用户点赞并且恢复帖子

俺们为足以创建有更好玩之查询,例如,查找这些点赞并回复的人头,如下:

  SELECT Member.MemberName,Member.Memberid,

         LikedPost.PostId,LikedPost.PostTitle,

         ReplyPost.PostTitle ReplyTitle

  FROM dbo.ForumPosts LikedPost, Reply_To, dbo.ForumPosts ReplyPost,

       Likes, dbo.ForumMembers Member, Written_By

  WHERE MATCH(Member-(Likes)->LikedPost<-(Reply_To)-ReplyPost-(Written_By)->Member)

 

注意,对于‘Member’节点使用了简单浅在同一个MATCH表达式中。这形成了扳平栽过滤:点赞并且发生东山再起的分子,需要以‘LikedPost’和‘ReplyPost’中还发记录才得。

那当关系型模式被代码如下:

select Likes.MemberId,Members.MemberName

  from Forum.Likes Likes, Forum.ForumPosts Posts,

       Forum.ForumMembers Members

  where Likes.MemberId=Posts.OwnerId

   and Posts.ReplyTo=Likes.PostId

   and Members.MemberId=Likes.MemberId

 

圈起这种写法更难以知晓与朗诵懂。

回帖给多只帖子的分子

SELECT Members.MemberId, Members.MemberName,

         Count(distinct RepliedPost.PostId) as Total

  FROM   dbo.ForumPosts ReplyPost, Reply_To, dbo.ForumPosts RepliedPost,

         Written_By,dbo.ForumMembers Members

  WHERE  MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)->RepliedPost)

  GROUP BY MemberId, Members.MemberName

  Having Count(RepliedPost.PostId) >1

 

回帖个一个帖子多次底积极分子:

SELECT Members.MemberId, Members.MemberName,

         RepliedPost.PostId RepliedId,count(*) as TotalReplies

  FROM   dbo.ForumPosts ReplyPost, Reply_To, dbo.ForumPosts RepliedPost,

       Written_By,dbo.ForumMembers Members

  WHERE MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)->RepliedPost)

  GROUP BY MemberId,MemberName,RepliedPost.PostId

  Having count(*) >1

 

上述两种语句被唯一的例外便是亮结果的会师。

总结

  经上述构建以图数据模式下之询问与涉及,对比了常规语词以及以论及模式下的同样查询,不难发现任当易读性,逻辑理解上还是以性质达到还来死老增强。当然这只有是第一独版本,所以难免出诸多题目,
下一致首我说介绍这本子在的如出一辙有的题目。

相关文章