搜索
数据库系统原理与应用第八讲上节课的回顾E-R模型的设计及向关系模型的转换E-R模型中的三个组织成分：实体、属性、联系实体之间的三种联系把E-R图转换成关系模式第十二章视图12.1创建和删除视图12.2使用视图12.3视图定义的修改和信息查看12.1创建视图和删除视图视图在数据库中是作为一个独立的对象进行存储的。视图和查询很相似，但也有区别：存储方式：视图存储为数据库设计的一部分，而查询则不是更新结果：对视图和查询的结果集更新限制是不同的排序结果：可以排序任何查询结果，而视图只有包括TOP子句时才能排序参数设置：可以为查询创建参数，但不能为视图创建参数加密：可以加密视图，但不能加密查询。12.1创建视图和删除视图一、创建视图SQLServer2005提供了2种方法来创建视图：使用SQLServer控制管理器使用CREATEVIEW语句12.1创建视图和删除视图CREATEVIEWview_name[(column[,…n])][WITHview_attribute[,…n]]ASselect_statement[WITHCHECKOPTION]其中WITHCHECKOPTION是强制视图上执行的所有数据修改都必须符合select_statement设置的准则。view_attribute:ENCRYPTION，防止将视图作为SQLServer复制的一部分发布；SCHEMABINDING，把视图绑定到架构上；VIEW_METADATA，指定为引用视图的查询请求浏览模式的元数据时，返回有关视图的元数据信息，而不是基表或表。12.1创建视图和删除视图书上例12.2创建名为st1_degree的视图，包括所有学生的姓名、课程和成绩。12.1创建视图和删除视图二、删除视图SQLServer2005提供了2种方法来删除视图：使用SQLServer控制管理器使用DROPVIEW语句DROPVIEW{view_name}[,…n]可以一次删除一个或多个视图12.2使用视图1.使用视图进行数据查询SELECT*FROMst1_degree2.通过视图向基表中插入数据一般只针对简单的视图。要符合下面条件：需要有插入数据的权限对于视图中未引用的字段，数据表应该允许为空或有默认值视图不能包含多个字段值的组合以及统计函数视图不能包含DISTINCT或GROUPBY不能在一个语句中对多个基表使用数据修改语句12.2使用视图书上例12.512.2使用视图3.通过视图修改基表中的数据12.2使用视图4.通过视图删除基表中的数据12.3视图定义的修改和信息查看一、视图定义的修改SQLServer2005提供了2种方法来修改视图：使用SQLServer控制管理器使用ALTERVIEW语句ALTERVIEWview_name[(column[,…n])][WITHview_attribute[,…n]]ASselect_statement[WITHCHECKOPTION]12.3视图定义的修改和信息查看二、视图重命名SQLServer2005提供了2种方法来重命名视图：使用SQLServer控制管理器使用存储过程sp_renamesp_rename[@objname=]‘object_name’,[@newname=]‘new_name’[,[@objtype=]‘object_type’]其中：object_name为视图的当前名称new_name为视图的新名称object_type为重命名对象的类型，默认值为NULL12.3视图定义的修改和信息查看三、查看视图的信息SQLServer2005提供了2种方法来查看视图信息：使用SQLServer控制管理器使用存储过程sp_helptextsp_helptext[@objname=]‘object_name’,第十三章数据库完整性数据库完整性是确保数据库中的数据的一致性和正确性13.1约束13.2默认值13.3规则13.1约束SQLServer2005提供了多种强制数据完整性的机制PRIMARYKEY约束FOREIGNKEY约束UNIQUE约束CHECK约束NOTNULL约束13.1约束1.PRIMARYKEY约束可以约束标识列或列集，这些列或列集的值唯一地标识表中的行。CREATETABLEdepartment/*部门表*/(dnointPRIMARYKEY,/*部门号为主键*/dnamechar(20),/*部门名*/)13.1约束如在创建表时指定了主键，SQLServer会自动创建一个“PK_”且后跟表名的主键索引，这个索引默认是聚集索引，同时也是一个唯一索引。13.1约束13.1约束2.FOREIGNKEY约束可以约束表之间的关系，为表的一列或多列数据提供参照完整性。CREATETABLEworker/*职工表*/(nointPRIMARYKEY,/*编号为主键*/namechar(8),/*姓名*/sexchar(2),/*性别*/dnoint/*部门号*/FOREIGNKEYREFERENCESdepartment(dno)ONDELETENOACTION,addresschar(30),/*地址*/)ONACTION:指定删除因错误而失败。另一个选项CASCADE:指定删除已删除行的外键的所有行。13.1约束13.1约束3.UNIQUE约束可以列集内强制执行值的唯一性。如果列有UNIQUE约束，SQLSerevr将确保数据除NULL外的所有值均唯一。CREATETABLEtable5(c1intUNIQUE,c2int)13.1约束13.1约束4.CHECK约束通过限制用户输入的值来加强域完整性。指定应用于列中输入的所有值的布尔条件，拒绝所有不为TRUE的值。CREATETABLEtable6(f1int,f2intNOTNULLCHECK(f2=0andf2=100))限定f2只能为0到10013.1约束13.1约束5.列约束和表约束列约束被指定为列定义的一部分，且仅适用于那个列表约束的声明与列的定义无关，适用于表中一个以上的列当一个约束中必须包含一个以上的列时，必须使用表约束13.2默认值SQLServer2005提供了两种使用默认值的方法创建表时指定默认值使用CREATEDEFAULT创建默认对象，然后使用存储过程sp_bindefault来绑定到指定列上13.2默认值1.创建表时指定默认列1)使用SQLServer管理控制器性别默认为男13.2默认值2)使用T-SQL语言默认为10默认为当前日期也可以通过ALTERTABLE来给列加上默认值：ALTERTABLEtable8ADDCONSTRAINTcon1DEFAULTgetdate()FORC313.2默认值2.使用默认对象默认对象是单独存储的，删除表时DEFAULT约束会自动删除，但默认对象不会删除。13.3规则规则限制了可以存储在表中或者用户定义数据类型的值。规则与CHECK约束功能基本相同，但一个列只能应用一个规则，却可以应用多个CHECK约束。CHECK可作为CREATETABLE语句的一部分进行指定，而规则以单独的对象创建，然后绑定到列上。13.3规则第十四章存储过程14.1概述14.2创建存储过程14.3执行存储过程14.4存储过程的参数14.5存储过程的管理14.1概述存储过程是在数据库服务器端执行的一组T-SQL语句的集合，经编译后存放在数据库服务器端。SQLServer2005提供3种存储过程：用户存储过程：用户编写的可重复用的T-SQL功能模块系统存储过程：由系统提供，常用sp_为前缀扩展存储过程：用户使用编程语言创建的外部例程14.2创建存储过程1.使用SQLServer管理控制器创建14.2创建存储过程2.使用CREATEPROCEDURE创建CREATEPROC[EDURE]存储过程名[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]14.2创建存储过程例：创建存储过程stud_degree用于检索所有学生的成绩记录14.3执行存储过程[EXEC[UTE]][@return_status=]{存储过程名[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,…n][WITHRECOMPILE]其中OUTPUT指定存储过程必须返回一个参数DEFAULT根据过程的定义提供参数的默认值WITHRECOMPILE强制编译新的计划14.3执行存储过程例：执行存储过程stud_degree14.4存储过程的参数1.在存储过程中使用参数例：设计存储过程maxno，以学号为参数，输出指定学号学生的所有课程中最高分和对应的课程名。14.4存储过程的参数执行存储过程方式1：EXECmaxno105方式2：EXECmaxno@no=10514.4存储过程的参数2.在存储过程中使用默认参数例：设计存储过程maxno1，以学号为参数，学号默认为101，输出指定学号学生的所有课程中最高分和对应的课程名。14.4存储过程的参数3.在存储过程中使用返回参数例：设计存储过程average，返回指定学号的代表姓名和平均分的2个参数@st_name、@st_avg表示返回参数14.4存储过程的参数例：设计存储过程stud1_degree，能根据学号和课程号来返回成绩等级。14.4存储过程的参数4.存储过程的返回值存储过程在执行后会返回一个整型值，如为0则表示执行成功，如为负则表示出错。例：EXEC@i=stud1_degree'101','3-105',@djOUTPUT14.5存储过程的管理1.查看存储过程使用SQLServer管理控制器使用系统存储过程1)sp_helpname显示存储过程的参数及数据类型2)sp_helptextname显示存储过程的源代码3)sp_dependsname显示和存储过程相关的数据库对象4)sp_stored_procedures返回当前数据库中的存储过程列表14.5存储过程的管理2.修改存储过程ALTERPROC[EDURE]存储过程名[参数列表]ASsql_statement3.重命名存储过程sp_rename原存储过程名,新存储过程名4.删除存储过程DROPPROCEDURE存储过程列表自学要求与上课安排●自学与上机内容：自学第15章●上机内容：完成上机实习：做完上机练习7的第七部分。做的快的同学可做上机练习7的第八部分。上机练习514.编写一个程序交叉表查询的程序，生成下图的成绩大表查询。方法1:利用SLELECT中PIVOT用于将列值旋转为列名（即行转列）语法：table_sourcePIVOT(聚合函数（value_column）FORpivot_columnIN(column_list))结果方法2:利用游标和临时表来完成.1.按学生表中的学号和姓名生成一个临时表2.按选课表中的课程号,分别对临时表增加字段3.按选课表中的每一条记录,分别修改临时表特定学生的特定课程成绩相关SQL命令:●机号、学号、姓名。你上机的完成情况，各部分做出几小题，没完成的题号。●上机练习5中：二、利用上次上机的学生_课程数据库(前12题以机号除以6来选二题，13-16中任选一)上机练习6中：一、书上练习题10(22-24中选一)二、书上上机实验题5(5-7中选一，8-10中选一)上机练习7中：三、书上上机实验题8(1-5中以机号除以5来选一题)上机练习8中：一、书上上机实验题9(1-2中以机号除以2来选一题)二、书上上机实验题10(1-2中以机号除以2来选一题)要求写出题目、SQL