项目6 学生成绩管理数据库视图及索引的应用.ppt

项目6学生成绩管理数据库视图及索引的应用,1.能利用SSMS进行视图的创建、管理。2.能利用T-SQL语句进行视图的创建、管理。3.能利用SSMS进行索引的创建、管理。4.能利用T-SQL语句进行索引的创建、管理。5能结合实际需求灵活地运用视图、索引，提高数据的存取性能和操作速度。,能力目标,项目描述,数据表的设计要满足范式的要求，因而会造成一个实体的所有信息保存在多个表中。当用户检索数据时，往往在一个表中不能够得到想要的所有信息，而使查询工作显得较为繁琐；或者，出于安全，用户只允许访问一个表中部分数据。因而，如何让用户安全、方便、快捷地浏览用户感兴趣的数据，就成了一个问题，而要较好地解决这个问题，则需要引入视图。本项目首先介绍视图的概念、然后通过学生成绩管理数据库视图的创建、管理，学会在SSMS环境及用T-SQL命令创建、管理视图的方法。为了提高查询的速度，充分发挥数据库的优越性，就需要引入索引。所以，接下来介绍索引的概念、索引的创建及管理的方法。,学习任务,任务6.1：视图的概述任务6.2：学生成绩管理数据库视图的创建任务6.3：学生成绩管理数据库视图的管理任务6.4：学生成绩管理数据库索引的创建和管理,项目描述及学习任务,6.1视图的概述,视图是关系数据库系统提供给用户以多角度观察数据库中数据的非常重要的机制。引入视图，可以使得查询更为简捷、方便，同时更加的安全。本学习任务主要介绍视图的概念、作用及使用视图的注意点。,任务描述,6.1.1视图的概述,视图是一种数据库对象，可以看作定义在SQLServer上的虚拟表，视图正如其名字的含义一样，是一个移动的窗口，通过它用户可以方便地看到感兴趣的数据,而不需要知道底层表结构及其之间的关系。视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，也可以来自另外的视图，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的行和列数据，行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基本表来说，视图的作用类似于筛选。视图由视图名和视图定义两部分组成，但是，数据库中只存储视图的定义，并不存储视图对应的数据，这些数据仍放在原来的基本表中。所以基本表中数据发生变化，从视图中查询的数据也将随之变化。,视图常见的示例有：（1）基本表的行和列的子集。（2）两个或多个基本表的连接。（3）基本表和另一个视图或视图子集的结合。（4）基本表的统计概要。,6.1.1视图的概述,【例6.1】创建一个学生姓名查询视图：VIEW_name，包含了学号、姓名、课名、成绩等信息。CREATEVIEWVIEW_nameASSELECT学生表.学号,姓名,课名,成绩from学生表a,选课表b,课程表cWhewa.学号=b.学号andb.课程号=c.课程号,执行上述命令后，则创建了视图VIEW_name。若以后要查询“王小芳”的各门课成绩，就可以输入：SELECT*FROMVIEW_namewhere姓名=王小芳查找“李铁成”的各门成绩，就可以在查询编辑器中输入：SELECT*FROMVIEW_namewhere姓名=李铁成若没有创建该视图，则查找“李铁成”的各门成绩，要编写：SELECT学生表.学号,姓名,课名,成绩from学生表a,选课表b,课程表cWhewa.学号=b.学号andb.课程号=c.课程号and姓名=李铁成,6.1.2视图的作用,视图具有以下几个优点：（1）能分割数据，简化结构。可以通过select和where来定义视图，从而可以分割数据基表中某些对于用户不关心的数据，使用户把注意力集中到所关心的数据列，进一步简化浏览数据的工作。（2）简化查询。可以将一些经常用到的查询语句定义为视图，这样不需要进行重复编写复杂的查询语句，直接调用视图就能实现。（3）为数据提供一定的逻辑独立性。如果为某一个基表定义一个视图，即使以后基本表的内容发生改变，也不会影响“视图定义”所得到的数据。（4）提供自动的安全保护功能。视图能像基本表一样授予或撤消访问许可权。（5）适当的利用视图可以更清晰的表达查询。,6.1.3使用视图要注意的地方,创建视图时要注意以下几点：（1）视图的名称必须是唯一的，而且视图的名称不能与当前数据库中的表的名称重复。（2）只能在当前数据库中创建视图。（3）一个视图最多只能引入1024列。（4）如果视图中某一列是函数、数学表达式、常量、或者来自多个表的列名相同，则必须为视图中的列定义名称。（5）如果视图所基于的数据库表被删除了，那么该视图不能再使用。,6.2学生成绩管理数据库视图的创建,视图在数据库中应用很广，用户可以根据自己的需要在SQLServerManagementStudio中和T-SQL命令两种方法创建视图。本学习任务主要介绍如何在SSMS和用T-SQL命令创建视图。,任务描述,6.2.1在SSMS中创建视图,在SQLServerManagementStudio中创建视图的，其重点是视图设计器。,【例6.2】在SSMS中创建一个学生选课信息视图，包含了学号、姓名、性别、专业、课名、成绩等信息，并按成绩的高低进行排序。,【例6.3】在SSMS中创建一个学生平均成绩视图，包含了学号、姓名、性别、专业、各门课的平均成绩等信息。,【例6.4】在SSMS中创建一个计算机应用专业的视图，名为：学生专业视图。,6.2.2T-SQL命令创建视图,1用T-SQL命令创建视图的语法格式CREATEVIEW,WITHENCRYPTIONAS查询语句WITHCHECKOPTION说明：（1）列名：视图中所使用的列名，当视图中使用与源表（或视图）相同的列名时，不必给出列名，但在以下情况时必须指定列名：当列是从算术表达式、函数或常量派生的。两个或更多的列可能具有的相同的名称（通常是因为联接）。视图中的某列被赋予了不同于派生来源列的名称时，列名也可以在SELECT语句中通过别名指派。（2）WITHENCRYPTION：对包含CREATEVIEW语句文本的条目进行加密。,6.2.2T-SQL命令创建视图,（3）WITHCHECKOPTION：指在视图上所进行的修改都要符合查询语句所指定的限制条件，这样可以确保数据修改后仍可通过视图看到修改的数据。（4）查询语句：用来创建视图的SELECT语句。但对SELECT语句有以下的限制：定义视图的用户必须对所参照的表或视图有查询权限，即可执行SELECT语句。不能使用COMPUTE或COMPUTEBY子句。不能使用ORDERBY子句。不能使用INTO子句。不能在临时表或表变量上创建视图。,6.2.2T-SQL命令创建视图,2应用【例6.5】创建一个计算机应用专业的视图：学生专业1。分析：有一个筛选条件：计算机应用专业。一般在创建创视图时，首先测试查询语句是否能正确执行，测试成功后，再执行整个创建视图语句。所以先执行查询语句：SELECT学号,姓名,性别,专业,出生年月,家庭地址,联系电话,总学分FROM学生表WHERE专业=计算机应用测试正确后，再输入并执行如下语句：CREATEVIEW学生专业1ASSELECT学号,姓名,性别,专业,出生年月,家庭地址,联系电话,总学分FROM学生表WHERE专业=计算机应用。,6.2.2T-SQL命令创建视图,【例6.6】创建一个计算机应用专业的视图：学生专业2，并要求进行修改和插入操作时仍需保证该视图只有计算机应用专业的学生。分析：由于要求进行修改和插入操作时仍需保证该视图只有计算机应用专业的学生，所以在创建视图时要加上：WITHCHECKOPTION。在查询编辑器中输入并执行如下语句：CREATEVIEW学生专业2ASSELECT学号,姓名,性别,专业,出生年月,家庭地址,联系电话,总学分FROM学生表WHERE专业=计算机应用WITHCHECKOPTION,说明：在创建时出现了：WITHCHECKOPTION对“学生专业2”视图的进行插入操作时，自动检查专业是不是“计算机应用”，若不是，拒绝该记录插入。对“学生专业2”视图的记录进行删除、更改操作时，自动加上：专业=计算机应用。,6.2.2T-SQL命令创建视图,【例6.7】创建所有学生学号、姓名及年龄的信息视图：stu_info。分析：由于年龄要通过year(GETDATE()-YEAR(出生年月)计算得到，所以此计算列要指定列名。先执行查询语句：SELECT姓名,学号,year(GETDATE()-YEAR(出生年月)as年龄From学生表测试通过后，再在查询编辑器输入并执行如下的语句：CREATEVIEWstu_infoASSELECT姓名,学号,year(GETDATE()-YEAR(出生年月)as年龄From学生表,说明：若对计算列不指定列名，即：CREATEVIEWstu_infoASSELECT姓名,学号,year(GETDATE()-YEAR(出生年月)From学生表执行该语句会出现错误,6.2.2T-SQL命令创建视图,【例6.8】创建年龄大于20的学生的学号、姓名及年龄的视图：stu_age，并保证对视图文本的修改都要符合年龄大于20这个条件。分析：增加了一个条件：年龄大于20，且要求对视图文本的修改都要符合年龄大于20这个条件，所以需加入WITHCHECKOPTION在查询编辑器中输入并执行如下语句：CREATEVIEWstu_ageASSELECT姓名,学号,year(GETDATE()-YEAR(出生年月)as年龄From学生表WhereYEAR(GETDATE()-YEAR(出生年月)20WITHCHECKOPTION,6.2.2T-SQL命令创建视图,【例6.9】创建一个：学生选课信息视图1，包含了学号、姓名、性别、专业、课名、成绩等信息，并对创建的文本的条目进行加密。分析：因为视图中包含：学号、姓名、性别、专业、课名、成绩信息，所以要用到“学生表”、“选课表”、“课程表”；同时，同时要对创建的文本进行加密，还需要加上：WITHENCRYPTION。先执行查询语句：SELECTa.学号,姓名,性别,专业,课名,成绩FROM学生表a,选课表b,课程表cWHEREa.学号=b.学号andb.课程号=c.课程号在保证查询语句正确后，再在查询编辑器中执行如下的语句：CREATEVIEW学生选课信息视图1WITHENCRYPTIONASselecta.学号,姓名,性别,专业,课名,成绩from学生表a,选课表b,课程表cwherea.学号=b.学号andb.课程号=c.课程号,6.2.2T-SQL命令创建视图,【例6.9】创建一个：学生选课信息视图1，包含了学号、姓名、性别、专业、课名、成绩等信息，并对创建的文本的条目进行加密。或者：SELECTa.学号,姓名,性别,专业,课名,成绩FROM学生表aJOIN选课表bONa.学号=b.学号JOIN课程表cONb.课程号=c.课程号在保证查询语句正确后，再在查询编辑器中执行如下的语句：CREATEVIEW学生选课信息视图1WITHENCRYPTIONASSELECTa.学号,姓名,性别,专业,课名,成绩FROM学生表aJOIN选课表bONa.学号=b.学号JOIN课程表cONb.课程号=c.课程号,这样创建的视图，是对文本进行加密的，即若执行：sp_helptext学生选课信息视图1会显示：对象学生选课信息视图1的文本已加密。,6.2.2T-SQL命令创建视图,【例6.10】创建一个学生平均成绩视图1，包含了学号、姓名、性别、专业、各门课的平均成绩等信息。分析：因为视图中包含了：学号、姓名、性别、专业，所以要用到“学生表”，而要显示平均成绩，则要用到“选课表”。由于平均成绩要计算得到，所以要指定列名。先执行查询语句：SELECTa.学号,姓名,性别,专业,AVG(成绩)平均成绩FROM学生表aJOIN选课表bONa.学号=b.学号GROUPBYa.学号,姓名,性别,专业,因为平均成绩前的列有：学号、姓名、性别、专业，所以在分组中要体现出来，写成：GROUPBYa.学号,姓名,性别,专业。,在保证查询语句正确后，再在查询编辑器中执行如下的语句：CREATEVIEW学生平均成绩视图1ASSELECTa.学号,姓名,性别,专业,AVG(成绩)平均成绩FROM学生表aJOIN选课表bONa.学号=b.学号GROUPBYa.学号,姓名,性别,专业,6.2.2T-SQL命令创建视图,视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表和视图上。,【例6.11】利用【例6.5】建立的视图：“学生专业1”及学生成绩管理数据库中的数据表，创建一个计算机应用专业并选修了C语言程序设计课程的学生视图。分析：【例6.5】建立的视图：学生专业1，是计算机应用专业的学生，所以可以选择：学生专业1，选课表，课程表。在查询编辑器中执行如下的语句：CREATEVIEW计算机应用专业_C语言视图ASselecta.学号,姓名,性别,专业,课名,出生年月,家庭地址,联系电话from学生专业1a,选课表b,课程表cwherea.学号=b.学号andb.课程号=c.课程号and课名=C语言程序设计,6.3学生成绩管理数据库视图的管理,在视图使用过程中，可能经常会发生基本表的改变，而使视图无法正常工作，这样，需要重新修改视图的定义；或者，一个视图可能不再具有使用价值，需要将其删除。因而，作为数据库管理员，还必须学会视图的管理。本学习任务主要学习如何利用SQLServerManagementStudio及T-SQL语句对视图进行管理。,任务描述,6.3.1查看视图定义,1利用SQLServerManagementStudio查看视图定义,在SQLServerManagementStudio中展开“数据库”“视图”，用鼠标右键点击要查看的视图，在弹出的快捷菜单中选择“设计”，再在弹出视图设计器后就可以查看。,【例6.12】查看【例6.7】创建的视图：stu_info。在SQLServerManagementStudio中，展开“数据库”“学生成绩管理”“视图”，右击stu_info，在弹出的快捷菜单中选择“设计”，出现视图设计器，如图所示。说明：不能查看加密的视图的设计。在【例6.8】中创建的“学生选课信息视图1”是加密的，若对它右击，在弹出的快捷菜单中发现“设计”菜单的颜色是灰色的，不能使用。,6.3.1查看视图定义,2利用系统存储过程查看视图信息,（1）sp_help用于返回视图的详细信息。格式：EXECsp_help视图名【例6.13】用sp_help查看【例6.7】创建的视图：stu_info。在查询编辑器中执行如下的命令：sp_helpstu_info执行结果如图所示。,6.3.1查看视图定义,2利用系统存储过程查看视图信息,（2）sp_helptext查看视图的定义文本。格式：EXECsp_helptext视图名【例6.14】查看【例6.7】创建的视图：stu_info的定义文本。在查询编辑器中执行如下的命令：sp_helptextstu_info执行结果如图所示。对已加密文本的视图，则是看不到其定义的文本。,6.3.1查看视图定义,2利用系统存储过程查看视图信息,【例6.15】查看在【例6.9】中创建的已对文本的条目进行加密的学生选课信息视图1。在查询编辑器中执行如下的命令：sp_helptext学生选课信息视图1执行结果如图所示。,6.3.2修改视图定义,1.在“视图设计器”中直接修改,在对象资源管理器中右击要查看的视图，在弹出的快捷菜单中选择“设计”，然后在出现的视图设计器中修改。如果要添加表、视图，则可以在关系图窗口中右击，在弹出的快捷菜单中选择“添加表”，也可以通过菜单：“查询设计器”“添加表”选择项。,【例6.17】将【例6.7】创建的视图stu_info改为：学号、姓名、性别及出生年月。（1）启动SSMS，在对象资源管理器中，展开“数据库”“学生成绩管理”“视图”，右击stu_info，在弹出的快捷菜单中选择“设计”，出现视图设计器。（2）在关系图窗口中选择“性别”复选框。（3）单击工具栏中的按钮，执行SQL语句，在“结果”窗口将显示包含在视图中的数据行。（4）单击保存按钮，保存视图即可。说明：若在定义视图时，对其文本进行加密，则无法在SQLServerManagementStudio中修改。,6.3.2修改视图定义,2.利用T-SQL语句修改视图定义,利用ALTERVIEW语句可以修改视图定义，该命令的基本语法如下：ALTERVIEW,WITHENCRYPTIONAS查询语句WITHCHECKOPTION其中，参数的含义与创建视图CREATEVIEW命令中的参数含义相同。,6.3.2修改视图定义,1.在“视图设计器”中直接修改,【例6.18】将【例6.17】更改的视图stu_info：改为，学号、姓名、性别、专业及年龄。在查询编辑器中输入并执行如下的命令：ALTERVIEWstu_infoASSELECT姓名,学号,性别,专业,year(GETDATE()-YEAR(出生年月)as年龄From学生表,6.3.3更改视图名,1使用SSMS更改视图名在SQLServerManagementStudio中，选择要更名的视图，单击鼠标右键，然后从弹出的快捷菜单中选择“重命名”命令。2利用系统存储过程更名视图利用系统提供的存储过程sp_rename可以对视图进行重命名，其语法格式如下：sp_rename原视图名,新视图名,【例6.19】将视图stu_info改名为stu_information在查询编辑器中输入并执行如下的命令：sp_renamestu_info,stu_information在执行结果中友情提示：更改对象名的任一部分都可能会破坏脚本和存储过程。刷新对象资源管理器，可以看到视图名进行了更改。,【例6.20】将视图stu_information再次改名为stu_info在查询编辑器中输入并执行如下的命令：sp_renamestu_information,stu_info,6.3.4删除视图,当一个视图不再需要时，可对其进行删除操作，以释放存储空间。1使用SSMS删除视图在SQLServerManagementStudio中，选择要删除的视图，单击鼠标右键，然后从弹出的快捷菜单中选择“删除”命令。2用DROPVIEW语句删除视图的语法格式如下：DROPVIEW视图名【例6.21】删除视图：学生专业2。在查询编辑器中输入并执行如下的命令：DROPVIEW学生专业2,6.3.5利用视图管理数据,对视图进行更新操作时，还要注意基本表对数据的各种约束和规则要求。（1）创建视图的select语句中没有聚合函数，且没有top、groupby、having及distinct关键字；（2）创建视图的select语句的各列必须来自于基表（视图）的列，不能是表达式；（3）视图定义必须是一个简单的SELECT语句，不能带连接、集合操作。即SELECT语句的FROM子句中不能出现多个表，也不能有JOIN等。,在创建视图之后，可以通过视图来对基表的数据进行管理。更新视图是指通过视图来插入（Insert）、修改（update）和删除（delete）数据。由于视图是虚表，所以无论在什么时候对视图的数据进行管理，实际上都是在对视图对应的数据表中的数据进行管理。,6.3.5利用视图管理数据,【例6.22】对【例6.5】创建的视图：学生专业1，插入一条新的记录。学号为03000104，姓名为：王无，性别为：男，专业为：计算机应用，出生年月为：1992-05-07，家庭地址：为杭州市中山路35号，联系电话为总学分为：60。在查询编辑器中输入：Insertinto学生专业1Values(03000104,王无,0,计算机应用,1992-05-07,杭州市中山路35号60)等价于：Insertinto学生表Values(03000104,王无,0,计算机应用,1992-05-07,杭州市中山路35号60),1在视图中插入数据使用insert语句通过视图向基本表插入数据时，如果视图不包括表中的所有字段，则对视图中那些没有出现的字段无法显式插入数据，假如这些字段不接受系统指派的null值，那么插入操作将失败。,6.3.5利用视图管理数据,【例6.23】将【例6.8】创建的视图stu_age，插入一条记录，学号为：03000105、姓名为：赵铁路，性别为：男，出生年月为：1990-9-8在查询编辑器中输入：Insertintostu_ageValues(03000105,赵铁路,0,1990-9-8)执行命令，则显示如图6-19所示结果，因为视图stu_age中的年龄是计算得到的列，所以无法插入。,6.3.5利用视图管理数据,【例6.24】对【例6.9】中创建的视图：学生选课信息视图1，插入一条记录，学号03000104、姓名：赵铁路、性别为：男，专业为：计算机应用、课名为：大学英语、成绩：85的信息。在查询编辑器中输入：Insertinto学生选课信息视图1Values(03000104,赵铁路,0,计算机应用,大学英语,85)执行命令，则显示如图6-20所示结果，因为视图学生选课信息视图1是从多个表中导出的，所以无法插入。,6.3.5利用视图管理数据,【例6.25】对【例6.5】创建的视图：stu_info中学号为“03000104”的学生姓名改为“王好好”updatestu_infoset姓名=王好好where学号=03000104等价于：update学生表set姓名=王好好where学号=03000104,2通过视图更新数据使用UPDATE语句可以通过视图修改基本表的数据。,6.3.5利用视图管理数据,【例6.26】将视图stu_is中学号为“03000102”的学生姓名改为“陈军备”，其中视图：stu_is是这样创建的：CREATEVIEWstu_isASSELECT学号,姓名,出生年月from学生表Where专业=计算机应用and性别=男WITHCHECKOPTION解：进行修改语句为：updatestu_isset姓名=陈军备where学号=03000102等价于：update学生表set姓名=陈军备where学号=03000102and专业=计算机应用and性别=0说明：若更新视图时只影响其中一个表，同时新数据值中含有主键字，系统将接受这个修改操作。,6.3.5利用视图管理数据,【例6.27】将视图：stu_is_c1中学号为“02000102”的学生成绩改为75，其中视图stu_is_c1是这样创建的：CREATEVIEWstu_is_c1ASSELECTa.学号,姓名,专业,成绩FROM学生表a,选课表bWHEREa.学号=b.学号and课程号=1001解：进行更改的命令为：Updatestu_is_c1Set成绩=75Where学号=02000102等价于：Update选课表Set成绩=75Where学号=02000102and课程号=1001,6.3.5利用视图管理数据,【例6.28】删除视图stu_is中学号为“03000102”的学生记录DELETEFROMstu_isWHERE学号=03000102等价于：DELETEFROM学生表WHERE学号=03000102AND专业=计算机应用AND性别=0,3通过视图删除数据使用DELETE语句可以通过视图删除基本表的数据。但对于依赖于多个基本表的视图，不能使用DELETE语句。,【例6.29】删除视图stu_is_c1中学号为“03000102”的学生记录。DELETEFROMstu_is_c1WHERE学号=03000102执行此命令，则出现：视图或函数stu_is_c1不可更新，因为stu_is_c1的创建用到了多表，修改会影响多个基表。说明：视图stu_is_c1是基于两个表生成的，所以不能用DELETE语句删除。,6.3.5利用视图管理数据,4视图更新表数据的限制条件通过前面的学习，已经了解到：通过视图来访问数据，其优点是非常明显的。如可以起到数据保密、保证数据的逻辑独立性、简化查询操作等等。但是，话说回来，SQLServer数据库中的视图并不是万能的，它跟表这个基本对象还是有重大的区别。在使用视图的时候，需要遵守四大限制。,6.3.5利用视图管理数据,限制条件一：视图数据的更改。不是所有视图都可以进行更改。如下面的这些视图，在SQLServer数据库中就不能够直接对其内容进行更新，否则，系统会拒绝这种非法的操作。（1）如在一个视图中，若采用GroupBy子句，对视图中的内容进行了汇总。则用户就不能够对这张视图进行更新。这主要是因为采用GroupBy子句对查询结果进行汇总之后，视图中就会丢失这条记录的物理存储位置。如此，系统就无法找到需要更新的记录。若用户想要在视图中更改数据，则数据库管理员就不能够在视图中添加这个GroupBY分组语句。（2）如不能够使用Distinct关键字。这个关键字的用途就是去除重复的记录。如没有添加这个关键字的时候，视图查询出来的记录有250条。添加了这个关键字后，数据库就会剔除重复的记录，只显示不重复的50条记录。此时，若用户要改变其中一个数据，则数据库就不知道其到底需要更改哪条记录。因为视图中看起来只有一条记录，而在基础表中可能对有的记录录有几十条。为此，若在视图中采用了Distinct关键字的话，就无法对视图中的内容进行更改。（3）如果在视图中有AVG、MAX等函数，则也不能够对其进行更新。如在一张视图中，其采用了SUN函数来汇总员工的工资时，此时，就不能够对这张表进行更新。这是数据库为了保障数据一致性所添加的限制条件。可见，试图虽然方便、安全，但是，其仍然不能够代替表的地位。,6.3.5利用视图管理数据,限制条件二：定义视图的查询语句中不能够使用某些关键字。视图其实就是由一组查询语句组成。在查询语句中，可以通过一些关键字来格式化显示的结果。如我们在平时工作中，经常会需要把某张表中的数据跟另外一张表进行合并。此时，可以利用SelectInto语句来完成。先把数据从某个表中查询出来，然后再添加到某个表中。当经常需要类似的操作时，是否可以把它制作成一张视图。每次有需要的时候，只需要运行这个视图即可，而不用每次都进行重新书写SQL代码。不过可惜的是，结果是否定的。在SQLServer数据库的视图中，是不能够带有Into关键字。如果要实现类似的功能，只有通过函数或者过程来实现。在SQLServer数据库中创建视图的时候，还有一个额外的限制。就是不能够在创建视图的查询语句中，使用orderby排序语句。,6.3.5利用视图管理数据,限制条件三：要对某些列取别名，并保证列名的唯一。在表关联查询的时候，当不同表的列名相同时，只需要加上表的前缀即可。不需要对列名另外进行命名。但是，在创建视图时就会出现问题，数据库会提示“duplicatecolumnname”的错误提示，警告用户有重复的列名。查询语句跟创建视图的查询语句还有很多类似的差异。如有时候，我们在查询语句中，可能会比较频繁的采用一些算术表达式；或者在查询语句中使用函数等等。在查询的时候，我们可以不给这个列“取名”。数据库在查询的时候，会自动给其命名。但是，在创建视图时，数据库系统就会提醒为列取别名。所以，虽然视图是对SQL语句的封装，但是，两者仍然有差异。创建视图的查询语句必须要遵守一定的限制。如要保证视图的各个列名的唯一；如果视图中某一列是一个算术表达式、函数或者常数的时候，要给其取名字等等。,6.3.5利用视图管理数据,限制条件四：权限上的双重限制。为了保障基本表数据的安全性，在视图创建的时候，其权限控制比较严格。一方面，若用户需要创建视图，则必须要有数据库视图创建的权限。这是视图建立时必须遵循的一个基本条件。在大型数据库设计中，往往会对数据库管理员进行分工。建立基本表的就只管建立基本表；负责创建视图的就只有创建视图的权限。其次，在具有创建视图权限的同时，用户还必须具有访问对应表的权限。如某个数据库管理员，已经有了创建视图的权限。此时，若其需要创建一张员工工资信息的视图，还不一定会成功。这还要这个数据库管理员有跟工资信息相关的基本表的访问权限。第三，就是视图权限的继承问题。如上面的例子中，这个数据库管理员不是基本表的所有者。但是经过所有者的授权，他就可以对这个基本表进行访问，就可以以此为基础建立视图。但是，这个数据库管理员有没有把对这个基本表的访问权限再授权给其他人呢？如他能否授权给A用户访问员工考勤信息表呢？答案是不一定。默认情况下，数据库管理员不能够再对其他用户进行授权。但是，若基本表的所有者，把这个权利给了数据库管理员之后，则他就可以对用户进行重新授权。让数据库管理员可以给A用户进行授权，让其可以进行相关的操作。,6.3.5利用视图管理数据,可见，视图虽然灵活，安全，方便，但是其仍然有比较多的限制条件。一般在报表、表单等等工作上，采用视图会更加的合理。因为其SQL语句可以重复使用。而在基本表更新上，包括记录的更改、删除或者插入上，往往是直接对基本表进行更新。对于一些表的约束，可以通过触发器、规则等等来实现；甚至可以通过前台SQL语句直接实现约束。作为数据库管理员，要有这个能力，能够判断在什么时候使用视图，什么时候直接调用基本表。,6.4学生成绩管理数据库索引的创建和管理,用户对数据库最频繁的操作之一是查询数据。一般情况下，数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时，搜索数据就需要花费很长的时间，这就造成了服务器的资源浪费。为了提高检索数据的能力，数据库引入了索引机制。本学习任务主要学习索引的概念、作用、创建及管理索引的方法。,任务描述,6.4.1索引的概述,索引就是加快检索表中数据的方法。数据库的索引类似于书籍的目录。在书籍中，用户查找内容，可以从第一页出发，一页一页地查，也可以根据目录找到相关内容的页码，然后按照页码迅速找到内容。显然，使用目录查找内容要比一页一页的查找速度快很多。在数据库中查找数据，可以从表的第一行开始逐行扫描，直到找到所需信息，也可以使用索引查找数据，即从索引对象中获得索引列信息的存储位置，然后再直接去其存储位置查找所需信息，这样不必对表进行逐行扫描，可以快速找到所需数据。例如这样一个查询：select*from表1whereid=44。如果没有索引，必须遍历整个表，直到ID等于44的这一行被找到为止；有了索引之后（当然，必须是在ID这一列上建立的索引），直接在索引里面找44（也就是在ID这一列找），就可以得知这一行的位置，根据这一行的地址，就可以很快找到这一行。即，索引是用来定位的。,6.4.1索引的概述,1索引的优点为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组（GROUPBY）和排序（ORDERBY）子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。,6.4.1索引的概述,2索引不足之处也许会有人要问：既然索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这是因为，索引有许多优点，但增加索引也有许多不利的一个方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。,6.4.1索引的概述,3应建立索引的列索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如：在经常需要搜索的列上，可以加快搜索的速度。在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。在经常使用在WHERE子句中的列上创建索引，加快条件的判断速度。,6.4.1索引的概述,4不应建立索引的列同样，对于有些列不应该创建索引。一般来说，不应该创建索引的这些列具有下列特点：第一，对于那些在查询中很少使用的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如“学生表”的“性别”列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。第三，对于那些定义为Text，Image和Bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。,6.4.1索引的概述,5索引的分类从两个方面分类：（1）从列的使用角度可将索引分为单列索引、唯一索引、复合索引三类。（2）从是否改变基本表记录的物理位置角度可分为聚集索引和非聚集索引两类。说明：（1）单列索引：是对基本表的某一单独的列进行索引，通常应对每个基本表的主关键字建立单列索引。（2）复合索引：是针对基本表中两个或两个以上列建立的索引。（3）唯一索引：一旦在一个或多个列上建立了唯一索引，则不允许在表中相应的列上插入任何相同的取值，即，唯一索引中不能出现重复的值，索引列中的数据必须是唯一的。唯一索引可以确保所有数据行中任意两行的被索引列不包括NULL在内的重复值。如果是复合唯一索引，则此索引可以确保索引列中每个组合都是唯一的。（4）聚集索引是指数据行的物理存储顺序与索引顺序完全相同。每个表只能有一个聚集索引，但是聚集索引可以包含多个列，此时称为复合索引。虽然聚集索引可以包含多个列，但是最多不能超过16个。当表中有主键约束时，则系统自动生成了一个聚集索引。只有当表包含聚集索引时，表内的数据行才按一定的排列顺序存储。如果表没有聚集索引，则其数据行按堆集方式存储。（5）非聚集索引具有完全独立于数据行的结构，它不改变表中数据行的物理存储顺序。,6.4.2索引的创建和管理,在SQLServer中通常可以使用两种方法创建管理索引。一种是在SSMS中创建管理索引，另一种是使用T-SQL语句创建管理索引。,1在SSMS中创建索引【例6.30】在学生成绩管理数据库中，为“课程表”创建一个基于“课名”列的，升序的，唯一非聚集索引Index_km。,【例6.31】在“学生成绩管理”数据库中，为“教师表”创建一个基于“职称、部门”列的复合非聚集索引Index_zcbm。其中，职称为升序，部门为降序。,6.4.2索引的创建和管理,2在SSMS中更改索引属性启动SSMS，在对象资源管理器中右击要管理的索引名（或者双击），在弹出的对话框中选择属性，在弹出的“索引属性”对话框中，选择要更改的属性后，再单击确定即可。,【例6.32】在【例6.30】中为“课程表”创建一个基于“课名”列的，升序的，唯一非聚集索引Index_km属性修改为基于“