SQL 之 外键与树状数据结构

先立个flag：看好多书上说，不提倡使用外键。想到一句话：应当是不提倡 [初学者] 使用外键。然后以讹传讹就变成了不提倡所有人使用了。大家谁都是从初学者过来的，但不要满足于初学者的水平啊！这个外键感觉在数据库中 树状数据结构 的操作很有用啊，可以好好学习一下。

---

关于外键的介绍：👉 w3school

---

master table

mId	mName
1	市场
2	人力
3	研发

slave table

sId	mName	mId
1	张三	2
2	李四	1
3	王五	3

在slave table 中，mId 就被称为 'slave table 的外键'。
对于外键可以建立一种约束关系，叫做外键约束，一般所说的外键的作用，多是指外键约束的作用：

1 预防破坏表间连接的动作；
2 预防非法数据插入到外键列，因为外键列的值只能取外键约束指向的表中(的列)的值之一；
3 可以设置Update 和 Delete 方法，在主表中被引用的列字段更新时，能自动更新从表中的主键值。

至于如何操作外键，还是参见👆面的地址。

---

个人认为：

1 外键对于不常删除数据的表，例如集团组织架构，用起来非常方便。尤其是在组织架构调整的时候;
2 对于存在外键的数据表，不建议存在软删除的操作；
3 对于外键的查询需要用到inner/left join，关于join 的用法也是一个很有意思的知识点。

---

关于数据库中树状数据结构的查询

在Oracle 数据库中好像有专门的语句（源自CSDN）：

select * from table
start with org_id = id值
connect by prior org_id = parent_id;

在SQL Server 可以用with as语句块：

testtable

nId	nName	pId
node Id	node Name	parent Id
节点Id	节点名称	父节点的Id

-- Alter  
Create Procedure testtable
(
  @Id Int
)
As
Begin
  with cte as
  (
    select nId,nName,pId,0 as lvl from testtable where nId = @Id 
    -- lvl => level 意为节点深度
    union all
    select d.nId,d.nName,d.pId,lvl+1 from cte c inner join testtable d
    on c.nId = d.pId
    -- 这里是根据父节点Id 查询所有子节点
    -- on c.pId = d.nId  
    -- 这里是根据子节点Id 查询所有父节点
  )
  select * from cte;
  -- with ... as 语句后要紧跟select 语句
End

据说with ... as 语句块在执行两次以上时，会自动添加temp 表，以加快查询速度。

---

即将到来的新活，将部门所有的代码重写一遍。想想都掉头发，哎~~~