《SQL项目实例教程》-模块七

模块七使用视图任务描述知识要点返回任务描述在网上书店“ｅｂｏｏｋ”数据库中，有以下需要解决的问题：①数据表存储的数据内容中有保密信息时，如管理员密码、联系电话等内容，这些信息不是任何用户都可以查看的，只有特定的人才可以查看这些信息；②用户所关心的数据没有集中在一个表中，在进行数据查询和数据的导出时不方便；③管理员只关心各类书籍订购的相关信息，用户只关心个人感兴趣的书籍信息等。为解决此类问题，我们提议使用视图解决以上问题。下一页返回任务描述视图常用于集中数据、简化查询和定制显示指定数据信息，为用户从不同角度使用数据提供方便，还可以为用户屏蔽数据的复杂性，简化用户对数据请求的操作，保护数据的安全。本模块主要实现用视图解决以上问题的方法，通过创建和使用视图对相关数据信息进行屏蔽，以保护数据安全性；对数据进行拆分和整合，以简化查询、便于用户使用。ＳＱＬＳｅｒｖｅｒ２００８为了保证数据的安全性，只有系统管理员（ｓａ）、数据库所有者（ｄｂｏ）、数据库对象的所有者及被授予权限的用户才能对数据库中的视图进行创建和管理操作。上一页下一页返回任务描述任务１创建视图任务１.１创建单基表视图本任务要求在“ｅｂｏｏｋ”数据库中创建名为“Ｖ＿ｂｏｏｋ”的视图。本视图要求以“Ｂｏｏｋ”表为基表，可以查看所有书籍的书籍编号、ＩＳＢＮ、书名、作者、出版社、价格等信息，屏蔽书籍的图片、描述、书籍类别编号等信息。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ创建视图①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中，展开“数据库”节点，选择对应的数据库“ｅｂｏｏｋ”。上一页下一页返回任务描述②展开“ｅｂｏｏｋ”数据库节点，右击“视图”节点，在弹出的快捷菜单中选择“新建视图”命令，如图７.１所示。③在弹出的“添加表”对话框中，为添加“Ｖ＿ｂｏｏｋ”视图中所需要的“Ｂｏｏｋ”基表，先单击选中“Ｂｏｏｋ”表，再单击“添加”按钮即可，最后单击“关闭”按钮离开，如图７.２所示。“添加表”对话框包括表、视图、函数、同义词四个选项卡，分别显示了当前数据库的用户表、用户视图和函数，以及用来给数据库对象定义别名的同义词。上一页下一页返回任务描述选定所需要的基表后，单击“添加”按钮，可以添加创建视图的基表，重复此操作可以添加多个基表，也可以在按住Ｃｔｒｌ键的同时选定多个基表，再单击“添加”按钮，最后单击“关闭”按钮离开。④添加完基表后，将打开“视图设计器”窗口，可在本窗口关系图窗格中看到新添加的基表以及基表之间的主键和外键的引用关系。由于本任务只涉及一个基表，所以看不到引用关系。“视图设计器”窗口中包含四个窗格，分别是关系图窗格、条件窗格、ＳＱＬ窗格和结果窗格。每个窗口的作用和设置如下所述：上一页下一页返回任务描述•在关系图窗格中，基表的每一列的左侧都有一个复选框，通过是否勾选复选框，可以指定该列在视图中是否被引用，根据任务要求勾选ｂｏｏｋＩｄ、ｉｓｂｎ、ｂｏｏｋＮａｍｅ、ａｕｔｈｏｒ、ｐｕｂ⁃ｌｉｓｈｅｒ、ｐｒｉｃｅ。•在条件窗格中，可以实现对引用列的设置，如设置别名、是否在结果中输出、指定排序类型、设定筛选条件等，还可根据任务要求将作为条件且不需要在结果中显示的列前面的对钩去掉以不显示该列，然后在它的筛选器项写入筛选条件。•在ＳＱＬ窗格中，可以看到在前两个窗格中设置时所产生的相应ＳＱＬ代码，也可以自行在本窗格中书写代码。上一页下一页返回任务描述•在结果窗格中，可以查看到执行该视图后的查询结果。视图创建设置与执行结果如图７.３所示。⑤测试正常后，可在工具栏上单击“保存”按钮，弹出“选择名称”对话框，为新建视图命名，在该对话框中填入“Ｖ＿ｂｏｏｋ”，如图７.４所示。单击“确定”按钮后完成创建视图操作，并将视图保存到“ｅｂｏｏｋ”数据库中。刷新视图列表后，可在对象资源管理器中看到该视图。方法二：使用Ｔ－ＳＱＬ语句创建视图可使用ＣＲＥＡＴＥＶＩＥＷ语句创建“Ｖ＿ｂｏｏｋ”视图，在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述任务１.２创建多基表视图本次任务以买家的需求角度出发，创建名为“Ｖ＿ｃｏｍｐｕｔｅｒ＿ｂｏｏｋ”的视图，通过本视图可以查询到清华大学出版社出版的所有计算机类型书籍的书名、作者、价格，要求按价格的降序排列，并为书名字段起别名为“ｃｏｍｐｕｔｅｒ＿ｂｏｏｋＮａｍｅ”。上一页下一页返回任务描述方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ创建视图在使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ创建“Ｖ＿ｃｏｍｐｕｔｅｒ＿ｂｏｏｋ”视图时，涉及含有多基表的视图操作，具体操作步骤如下：①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中，展开“数据库”节点，选择对应的数据库“ｅｂｏｏｋ”。展开“ｅｂｏｏｋ”数据库节点，右击“视图”节点，在弹出的快捷菜单中选择“新建视图”命令。上一页下一页返回任务描述②在“添加表”对话框中，按住Ｃｔｒｌ键单击选中任务涉及的“Ｂｏｏｋ”和“ＢｏｏｋＴｙｐｅ”两个基表，然后单击“添加”按键完成基表选择，如图７.５所示。这样，在打开的“视图设计器”窗口的关系图窗格中就可以看到两个基表以及基表之间的主键和外键的引用关系。•如果在第一次添加基表操作时添加的基表不全，则可在关系图窗格的空白处右击，在弹出的快捷菜单中选择“添加表”命令重新打开“添加表”对话框。继续进行添加基表操作，直到将所需基表全部添加完成。右键菜单如图７.６所示。上一页下一页返回任务描述•如果添加了不需要的表，则可右击该表的标题栏，在右键菜单中单击“删除”命令删除该表，如图７.７所示。③设置视图设计器，如图７.８所示。按照任务要求进行如下设置：•在关系图窗格中，根据任务要求依次勾选“Ｂｏｏｋ”表的ｂｏｏｋＮａｍｅ、ａｕｔｈｏｒ、ｐｒｉｃｅ、和ｐｕｂｌｉｓｈｅ列，以及“ＢｏｏｋＴｙｐｅ”表的ｂｏｏｋＴｙｐｅＮａｍｅ列。上一页下一页返回任务描述•在条件窗格中，根据任务要求将作为条件且不需要在结果中显示的ｐｕｂｌｉｓｈｅ和ｂｏｏｋ⁃ＴｙｐｅＮａｍｅ列前面的对钩去掉，以不显示该列；在ｐｕｂｌｉｓｈｅ列对应的筛选器项写入筛选条件“＝′清华大学出版社′”，在ｂｏｏｋＴｙｐｅＮａｍｅ列对应的筛选器项写入筛选条件“＝′计算机′”；然后在ｐｒｉｃｅ列的“排序类型”处选择“降序”；最后在ｂｏｏｋＮａｍｅ列的“别名”处写入“ｃｏｍｐｕｔｅｒ＿ｂｏｏｋＮａｍｅ”。•在ＳＱＬ窗格中，可以看到在前两个窗格中设置时所产生的相应ＳＱＬ代码；上一页下一页返回任务描述•在结果窗格中，先单击工具栏中的“验证Ｔ－ＳＱＬ”按钮以检查Ｔ－ＳＱＬ语法，语法正确后再单击工具栏中的“执行”按钮，则可看到使用视图查询后的结果。④测试正常后，可在工具栏上单击“保存”按钮，弹出“选择名称”对话框，为新建视图命名。在该对话框中填入“Ｖ＿ｃｏｍｐｕｔｅｒ＿ｂｏｏｋ”，单击“确定”按钮后完成创建视图操作，并将视图保存到“ｅｂｏｏｋ”数据库中。刷新视图列表后，可在对象资源管理器中看到该视图。方法二：使用Ｔ－ＳＱＬ语句创建视图上一页下一页返回任务描述要创建“Ｖ＿ｃｏｍｐｕｔｅｒ＿ｂｏｏｋ”视图，需在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述任务１.３创建具有计算字段的视图本次任务以卖方的角度出发，创建视图“Ｖ＿ｔｏｔａｌＤＷ”，用来汇总书名为《网页制作案例教程》的书籍售出总价。要求显示书名、单价、售出总数、售出总价。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ创建视图在使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ创建“Ｖ＿ｔｏｔａｌＤＷ”视图时，涉及含有计算列的视图操作，具体步骤如下：上一页下一页返回任务描述①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中，展开“数据库”节点，选择对应的数据库“ｅｂｏｏｋ”。展开“ｅｂｏｏｋ”数据库节点，右击“视图”节点，在弹出的快捷菜单中选择“新建视图”命令。②在“添加表”对话框中，按住Ｃｔｒｌ键单击选中任务所涉及的“Ｂｏｏｋ”和“ＯｒｄｅｒＤｅｔａｉｌ”两个基表，然后单击“添加”按键完成基表选择。③设置视图设计器，如图７.９所示。按照任务要求进行如下设置：上一页下一页返回任务描述方法二：使用Ｔ－ＳＱＬ语句创建视图要创建“Ｖ＿ｔｏｔａｌＤＷ”视图，需在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述本任务中的筛选条件既可写在ＨＡＶＩＮＧ子句中，也可以写在ＷＨＥＲＥ子句中。视图中计算字段的别名既可写在ＳＥＬＥＣＴ子句中，也可以写在视图名之后并用圆括号括住。如果写在视图名之后，则ＳＥＬＥＣＴ子句的所有字段必须全部出现在视图名之后，且需与ＳＥＬＥＣＴ子句中的字段一一对应。具体语句如下：上一页下一页返回任务描述任务１.４创建加密视图创建加密视图“Ｖ＿Ａｄｍ”，用来查询管理员密码。要求显示管理员用户的管理员编号和密码。要创建“Ｖ＿Ａｄｍ”视图，需在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述任务１.５创建具有数据约束的视图创建具有数据约束的视图“Ｖ＿ｏｒｄｅｒ”，用来查询订单编号为“１”的订单详情。要求显示订单的订单编号、收货地址、联系电话、下单时间、订书总量。要创建“Ｖ＿ｏｒｄｅｒ”视图，需在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述上一页下一页返回任务描述任务２管理视图任务２.１修改视图任务２.１.１修改视图定义１）将视图“Ｖ＿ｂｏｏｋ”修改为只能查看清华大学出版社出版的书籍基本信息。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ修改视图定义①在ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ中，通过“对象资源管理器”窗口，依次展开“数据库”节点、“ｅｂｏｏｋ”数据库节点、“视图”节点。上一页下一页返回任务描述②右击要修改的视图“Ｖ＿ｂｏｏｋ”，在弹出的快捷菜单中选择“设计”命令，打开视图设计器进行修改。③编辑视图设计器，在条件窗格中为“ｐｕｂｌｉｓｈｅｒ”列设置筛选条件为“＝′清华大学出版社′”。④保存设置，完成视图修改。方法二：使用Ｔ－ＳＱＬ语句修改视图定义要修改“Ｖ＿ｂｏｏｋ”视图，需在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述２）修改视图“Ｖ＿ｔｏｔａｌＤＷ”，用来汇总所有书籍售出总价。要求显示每种书籍的书名、单价、售出数量、售出总价。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ修改视图定义上一页下一页返回任务描述①在ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ中，通过“对象资源管理器”窗口，依次展开“数据库”节点、“ｅｂｏｏｋ”数据库节点、“视图”节点。②右击要修改的视图“Ｖ＿ｔｏｔａｌＤＷ”，在弹出的快捷菜单中选择“设计”命令，打开视图设计器进行修改。③编辑视图设计器，在条件窗格中删除“ｂｏｏｋＮａｍｅ”列的筛选条件即可。④保存设置，完成视图修改。方法二：使用Ｔ－ＳＱＬ语句修改视图定义上一页下一页返回任务描述要修改“Ｖ＿ｔｏｔａｌＤＷ”视图，需在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述３）修改视图“Ｖ＿ｏｒｄｅｒ”，用来查询所有订单的详情。要修改“Ｖ＿ｏｒｄｅｒ”视图，需在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述任务２.１.２重命名视图１）将视图“Ｖ＿ｂｏｏｋ”更名为“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ重命名视图①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中，展开“数据库”节点，选择对应的数据库“ｅｂｏｏｋ”。②依次展开“ｅｂｏｏｋ”数据库节点和“视图”节点，右击要更名的视图“Ｖ＿ｂｏｏｋ”，在弹出的快捷菜单中选择“重命名”命令。上一页下一页返回任务描述③此时视图名称处于编辑状态，直接进行修改，将视图名称改为“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”，单击回车键确认修改成功。方法二：使用Ｔ－ＳＱＬ语句重命名视图将视图“Ｖ＿ｂｏｏｋ”更名为“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”，需在“查询编辑器”窗口中输入并执行如下语句：２）将视图“Ｖ＿ｔｏｔａｌＤＷ”更名为“Ｖ＿ｔｏｔａｌ”。将视图“Ｖ＿ｔｏｔａｌＤＷ”更名为“Ｖ＿ｔｏｔａｌ”，需在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述任务２.２查看视图信息查看视图“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”的定义信息、依赖信息及文本信息。要查看视图“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”的相关信息，需在“查询编辑器”窗口中输入并依次执行如下语句：上一页下一页返回任务描述分别执行以上语句后，将得到如图７.１０～图７.１２所示信息。任务３使用视图查询数据使用任务１创建的视图进行数据检索。任务３.１使用视图“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”查询数据使用视图“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”查询清华大学出版社出版的书籍基本信息。方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ查询上一页下一页返回任务描述①启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中展开“数据库”节点，选择对应的数据库“ｅｂｏｏｋ”。②依次展开“ｅｂｏｏｋ”数据库节点和“视图”节点，右击要使用的视图“Ｖ＿ｑｉｎｇｈｕａ⁃ｂｏｏｋ”，在弹出的快捷菜单中选择“编辑前２００行”命令，如图７.１３所示。③使用视图进行查询的结果如图７.１４所示。方法二：使用Ｔ－ＳＱＬ语句查询上一页下一页返回任务描述使用视图“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”查询清华大学出版社出版的书籍基本信息，需在“查询编辑器”窗口中输入并执行如下语句：任务３.２使用视图“Ｖ＿ｃｏｍｐｕｔｅｒ＿ｂｏｏｋ”查询数据使用视图“Ｖ＿ｃｏｍｐｕｔｅｒ＿ｂｏｏｋ”查询到清华大学出版社出版的关于ＪＡＶＡ的书籍信息。上一页下一页返回任务描述在“查询编辑器”窗口中输入并执行如下语句：任务３.３使用视图“Ｖ＿ｔｏｔａｌ”查询数据使用视图“Ｖ＿ｔｏｔａｌ”查询书名为《Ｃ语言程序设计》的书籍出售情况。在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述任务３.４使用视图“Ｖ＿ｏｒｄｅｒ”查询数据１）使用视图“Ｖ＿ｏｒｄｅｒ”查询２０１５年６月下单的所有订单信息。上一页下一页返回任务描述在“查询编辑器”窗口中输入并执行如下语句：２）使用视图“Ｖ＿ｏｒｄｅｒ”查询所有来自内蒙古的订单。在“查询编辑器”窗口中输入并执行如下语句：上一页下一页返回任务描述任务４删除视图删除视图“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”。上一页下一页返回任务描述方法一：使用ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ查询启动ＳＱＬＳｅｒｖｅｒＭａｎａｇｅｍｅｎｔＳｔｕｄｉｏ，在“对象资源管理器”窗口中，展开“数据库”节点，选择对应的数据库“ｅｂｏｏｋ”。展开“ｅｂｏｏｋ”数据库的“视图”节点，右击要删除的视图“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”，在弹出的快捷菜单中选择“删除”命令。在打开的“删除对象”对话框中，单击“确定”按钮以完成删除视图操作。方法二：使用Ｔ－ＳＱＬ语句查询上一页下一页返回任务描述删除视图“Ｖ＿ｑｉｎｇｈｕａｂｏｏｋ”，需在“查询编辑器”窗口中输入并执行如下语句：上一页返回知识要点一、视图１.视图概述视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制，是ＳＱＬＳｅｒｖｅｒ中重要的数据库对象。视图是从一个或多个基本表（或视图）导出的虚拟表，其内容由ＳＥＬＥＣＴ查询语句定义，数据库中只存储该视图的定义信息。对用户来说，视图的使用方式与表的相同，即可以通过视图来查询数据和更新数据，但它实际上并不存储数据，视图中的数据是视图在被使用时动态生成的。从某种意义上讲，视图就像一个窗口，用户可以通过它来观测数据库中自己感兴趣的数据及其变化。下一页返回知识要点因此，视图实际是把一些查询语句封装起来呈现给数据库用户的。２.视图的作用与优点使用视图主要是为用户提供一个快捷的数据访问方法，它还可以屏蔽用户对真实数据源的直接访问。视图具有很多优点，主要集中表现在聚焦特定数据、简化查询操作、定制用户数据、便于组织数据导出和较高的数据安全性等方面。①聚焦特定数据。视图可以使用户只关心自己感兴趣的某些特定数据，这是对业务数据的一种整合。②简化查询操作。上一页下一页返回知识要点通过将较为复杂的查询（如多表查询）定义为视图，可大大简化用户对数据的操作，不必在每次需要时都提供所需条件、限制等。③定制用户数据。视图可以让不同的用户以不同的方式看到不同或相同的数据集，这对有不同兴趣或技术水平的用户在使用同一数据库时特别有用。④便于组织数据导出。当需要将多个表中的相关数据导出时，可以将数据集中到一个视图内，通过视图将相关数据导出，从而简化了数据的交换操作。⑤较高的数据安全性。上一页下一页返回知识要点视图提供了一个简单而有效的安全机制。通过视图，用户只能查看或修改他们所能看到的数据，其他数据则被保护起来不可访问。二、创建视图１.创建视图的语法格式使用Ｔ－ＳＱＬ语言中的ＣＲＥＡＴＥＶＩＥＷ语句可以创建视图，其基本语法格式为：上一页下一页返回知识要点２.创建视图的注意事项①只有在当前数据库中才能创建视图。②视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即多不同用户，即使定义相同的视图，也必须使用不同的名字。③不能把规则、默认值定义绑定在视图之上，不能将触发器与视图相关联。④不能在视图上建立任何索引，包括全文索引。⑤一个视图最多可以引用１０２４个列。⑥视图可以嵌套定义在其他视图的基础上（最多可以嵌套３２层）。上一页下一页返回知识要点⑦定义视图的查询语句中不能包括ＯＲＤＥＲＢＹ、ＣＯＭＰＵＴＥ、ＣＯＭＰＵＴＥＢＹ子句或是ＩＮＴＯ等关键字。⑧不能创建临时视图，也不能在临时表上创建视图。⑨不能对视图进行全文查询。⑩默认状态下，视图中的列继承它们在基表中的名称。存在以下情况时，在创建视图时需要明确给出每一列的名称：•视图中的某些列来自表达式、函数或常数。•视图中两个或多个列在不同基表中具有相同的名称，即来自不同基表的同名字段。•希望在视图中的列使用不同于基表中的列名。上一页下一页返回知识要点•希望在视图中的列使用不同于基表中的列名。三、修改视图１.视图重命名使用系统存储过程ｓｐ＿ｒｅｎａｍｅ可以修改视图的名称，其语法格式如下：［ＥＸＥＣ］ｓｐ＿ｒｅｎａｍｅ′ｏｌｄ＿ｎａｍｅ′，′ｎｅｗ＿ｎａｍｅ′２.修改视图定义使用ＡＬＴＥＲＶＩＥＷ命令可以修改视图的定义内容，其基本语法格式如下：上一页下一页返回知识要点不论视图是否加密，都可以通过此语句对视图定义进行修改。四、删除视图对于不需要的视图，可以用Ｔ－ＳＱＬ语句中的ＤＲＯＰＶＩＥＷ命令将其删除。删除视图后，其所对应的数据不会受到影响。上一页下一页返回知识要点但如果有其他数据库对象是以此视图为基础建立的，则删除此视图后再使用那些数据库对象时将会发生错误。使用ＤＲＯＰＶＩＥＷ命令删除视图的语法格式如下：ＤＲＯＰＶＩＥＷ｛ｖｉｅｗ＿ｎａｍｅ｝［，……ｎ］使用该语句可同时删除多个视图，视图名之间用逗