走近复杂数据库计算型软件的设计与制作(3)

摘要：接着昨天的数据库的数据表的设计,

今天叙述数据视图的设计。

数据视图的设计看似简单，不就是一个Select

语句吗？是的，就是一个SQL

语句。在这个SQL

语句中包含了需要选择的列和相关联的表之间的连接关系，有的是左连接，有的是右连接，有的是全连接。这些关系大部分的教科书上都有，但是如何才能用好却不是那么简单。在连接类型的选择上需要根据业务规则来定，要靠具体情况具体应用。下面讲解一下我在数据视图设计中遇到的一些问题。

首先讲解以下Distinct

的问题，视图是一个查看数据的定义，也就是经常所说的虚表。在查看数据时，会自动消除重复，也就是说如果多行完全相同的情况下，视图会保留其中的一行，而把其他的行过滤掉，笔者曾经遇到这样的问题，使用服务器同时注册5

条记录，注册的时候获得系统的默认时间，其中有两条记录注册之后系统的时间值竟然完全相同。在展示数据的时候只出现了4

条，导致客户的抱怨。所以建议如果想完全选择所有的符合条件的记录，不妨把主键也包含近来，即使他没有用处。有的时候，本来想要其中的一条记录，偏偏选择出来的是多条重复的记录，这在什么情况下出现，笔者也没有总结出规律，但是有一个解决办法就是使用Distinct

可以消除重复。下面就使用Distinct

的创建视图的定义

create view shop_view

with encryption

select m.m_id,sh.rm_id,sh.enabled,m.name+'('+m.m_id+')' as result

from shop sh,member m

where sh.m_id=m.m_id

union

select distinct m.m_id,s.rm_id,s.enabled, m.name+'('+m.m_id+')'

from member m,stock s

where m.m_id=s.m_id

and m.user_level=4

视图在创建的时候添加with encryption

选项可以使得你的SQL

定义不会被别人看到，增加安全性。对于程序员，只提供视图的定义而非原始表也减少了被攻击的概率。

整个系统中最关键的就是奖金的查询，上一节说过，奖金类型繁多，使用了两个表来解决存储问题，奖金表可以被看作是竖表，而分类显示需要把表结构给横过来，也就是说把一个表由竖表变成一个横表，

就需要使用同一个表的别名进行一大串的表连接。为了避免这个问题，在了解业务的需求之后，笔者把几种类型的奖金综合起来，创建视图，下面是相关的SQL

语句

create view oldplan_award

with encryption

select *

from award_level

where award_type not in(0,1,3,5,6,7,9,10,37,38,39)

分类后要综合查询总数:

create view commendMemberAward_view

with encryption

select m_id,turns,sum(money)as award_num

from award_level

where award_type=10

and enabled=1

group by m_id,turns

create view ShopRentalAwardB_view

with encryption

select m_id,turns,sum(money)as award_num

from award_level

where award_type in(37,38,39)

and enabled=1

group by m_id,turns

create view educationShopAwardB_view

with encryption

select m_id,turns,sum(money)as award_num

from award_level

where award_type=7

and enabled=1

group by m_id,turns

分类之后需要综合成一个横表

如下定义，因为有可能是空值所以进行了处理

create view shopAwardB_view

with encryption

select a.m_id,a.turns,

case when a1.award_num is null then 0 else a1.award_num end as commendAward,

case when a2.award_num is null then 0 else a2.award_num end as rentalAward,

case when a3.award_num is null then 0 else a3.award_num end as educationAward

from award_else a left join commendMemberAward_view a1 on(a.m_id=a1.m_id)

left join ShopRentalAwardB_view a2 on(a.m_id=a2.m_id and a.turns = a2.turns)

left join educationShopAwardB_view a3 on(a.m_id=a3.m_id and a.turns = a3.turns)

where a.c_times>0

另外有一个视图，其作用是提供一个显示树状结构的数据，比如三叉树或者其他多叉树，每行都是一个节点相关的信心，当前的节点ID

，上级节点ID,

需要显示的信息。定义如下：

create view matrixGraph_view

with encryption

select s1.s_id,s1.ps_id,s1.m_id,s1.enabled,m.name+'('+m.m_id+')' as result,s1.position-head+1 as position

from stock s1,member m,stock s2,misc

where s1.m_id=m.m_id

and s1.ps_id=s2.S_ID

在这里有一点需要说明，result

列对应的数据是显示在节点上的，如果客户的需求发生变化，比如要显示的内容包括ID

号，在此只需要改变视图的定义即可，不用改变界面程序，或者数据表的定义，从而隔离了数据表和程序，稍微有点3

层架构的思想。下面的图显示了上面的定义，如果客户需要显示Position

信息，那就在result

列的定义上修改即可。

今天就先到这里，明天接着讲述函数的设计，如果你能学到一些东西或者指出笔者的一些不足之处，笔者会十分高兴。

本文转自凌辉博客51CTO博客，原文链接http://blog.51cto.com/tianli/42594如需转载请自行联系原作者

lili00okok