单元7事务、视图和索引

事务、视图和索引,单元7,回顾与作业点评3-1,下面的SQL语句有错吗？实现了什么功能？上述语句可以用下面的联接替换吗？,可以,SELECT*FROMStudentWHEREStudentNoIN,SELECTStudent.*FROMStudentINNERJOINResultONStudent.StudentNo=Result.StudentNo,(SELECT*FROMResult),(SELECTStudentNoFROMResult),回顾与作业点评3-2,下列语句实现了什么功能？上述语句可以用下面的联接替换吗？,查看没有参加考试的学生,有的子查询不能用联接替换。这就是典型的例子,SELECT*FROMStudentWHEREStudentNoNOTIN(SELECTStudentNoFROMResult),SELECT*FROMStudentINNERJOINResultONStudent.StudentNoResult.StudentNo,不可以,回顾与作业点评3-3,下列语句实现了什么功能？请使用IN子查询改写上述语句？请使用联接查询改写上述语句,SELECT*FROMStudentWHEREStudentNoIN(SELECTStudentNoFROMResult),SELECT*FROMStudentINNERJOINResultONStudent.StudentNo=Result.StudentNo,SELECT*FROMStudentWHEREEXISTS(SELECT*FROMResultWHEREStudentNo=Student.StudentNo),查询参加考试的学生,预习检查,T-SQL语句如何提交一个事务？事务具有的四个特性是什么？视图是虚拟表吗？索引的作用是什么？,本章任务,模拟实现银行转帐功能批量插入学生考试成绩办理毕业学生离校手续查看学生成绩查看学生档案,本章目标,使用事务保证操纵数据的完整性掌握如何创建并使用视图,为什么需要事务5-1,银行转账,银行转账问题：假定资金从账户A转到账户B，至少需要两步：账户A的资金减少然后账户B的资金相应增加,账户A,账户B,假定张三的账户直接转账1000元到李四的账户,为什么需要事务5-2,CREATETABLEbank(customerNameCHAR(10),-顾客姓名currentMoneyMONEY-当前余额)GOALTERTABLEbankADDCONSTRAINTCK_currentMoneyCHECK(currentMoney=1)GOINSERTINTObank(customerName,currentMoney)VALUES(张三,1000)INSERTINTObank(customerName,currentMoney)VALUES(李四,1),创建账户表，存放用户的账户信息,添加约束：根据银行规定，账户余额不能少于1元，否则视为销户,张三开户，开户金额为1000元；李四开户，开户金额1元,为什么需要事务5-3,目前两个账户的余额总和为：1000+1=1001元,为什么需要事务5-4,模拟实现转账从张三的账户转账1000元到李四的账户,/*-转账测试：张三转账1000元给李四-*/-我们可能会这样编写语句-张三的账户少1000元，李四的账户多1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=李四GO-再次查看转账后的结果SELECT*FROMbankGO,请问：执行转账语句后，张三、李四的账户余额分别是多少？,张三的账户没有减少但李四的账户却多了1000元100010012001元总额多出了1000元！,为什么需要事务5-5,-张三的账户减少1000元，李四的账户增加1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=李四GO,发生错误的原因,执行失败，张三账户余额还是1000元,继续往下执行：执行成功，李四账户余额变为1001元,如何解决呢？使用事务,UPDATE语句违反约束：余额=1元,什么是事务,事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作多个操作作为一个整体向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元,转账过程就是一个事务它需要两条UPDATE语句来完成，这两条语句是一个整体如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元,事务的特性,事务必须具备以下四个属性，简称ACID属性：原子性（Atomicity）一致性（Consistency）隔离性（Isolation）永久性（Durability）,事务是一个完整的操作事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行,当事务完成时，数据必须处于一致状态,并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务,事务完成后，它对数据库的修改被永久保持,如何创建事务2-1,使用SQL语句管理事务开始事务提交事务回滚（撤销）事务,ROLLBACKTRANSACTION,COMMITTRANSACTION,BEGINTRANSACTION,如何创建事务2-2,一旦事务提交或回滚，则事务结束判断某条语句执行是否出错：使用全局变量ERRORERROR只判断当前一条T-SQL语句执行是否有错为了判断事务中所有T-SQL语句是否有错，可以对错误进行累计如：事务可以嵌套,SETerrorSum=errorSum+ERROR,事务分类,显式事务用BEGINTRANSACTION明确指定事务的开始最常用的事务类型隐性事务通过设置SETIMPLICIT_TRANSACTIONSON语句，将隐性事务模式设置为打开其后的T-SQL语句自动启动一个新事务提交或回滚一个事务后，下一个T-SQL语句又将启动一个新事务自动提交事务SQLServer的默认模式每条单独的T-SQL语句视为一个事务,BEGINTRANSACTION/*-定义变量，用于累计事务执行过程中的错误-*/DECLAREerrorSumINTSETerrorSum=0-初始化为0，即无错误/*-转账：张三的账户减少1000元，李四的账户增加1000元*/UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三SETerrorSum=errorSum+ERROR-累计是否有错误UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=李四SETerrorSum=errorSum+ERROR,使用事务解决银行转账问题4-1,从张三的账户转出1000元，存入李四的账户中,开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体）,累计是否有错误,IFerrorSum0-如果SQL语句执行出错BEGINPRINT交易失败，回滚事务ROLLBACKTRANSACTIONENDELSEBEGINPRINT交易成功，提交事务，写入硬盘，永久的保存COMMITTRANSACTIONENDGOPRINT查看转账事务后的余额SELECT*FROMbankGO,使用事务解决银行转账问题4-2,根据执行是否有错误，决定提交事务，或撤销事务,如果有错，则回滚操作，事务结束,如果成功，则提交操作，事务结束,从张三的账户转出1000元，存入李四的账户中,使用事务解决银行转账问题4-3,转账事务前,转账事务过程中,转账事务结束后,转账1000元，转账失败的情况,使用事务解决银行转账问题4-4,转账事务前,转账事务过程中,转账事务结束后,转账800元，转账成功的情况,指导批量插入学生考试成绩2-1,训练要点：使用事务向表中插入多条记录需求说明：批量插入参加今天“JavaLogic”课程考试的十名学生成绩如果输入的成绩大于100分，将违反约束,讲解需求说明,指导批量插入学生考试成绩2-2,实现思路：使用显式事务完成批量插入10个学生考试成绩的操作使用全局变量ERROR判断插入操作是否成功使用IF语句判断ERROR值。如果插入成功，提交事务；否则回滚事务,BEGINTRANSACTIONDECLAREerrorSumINTSETerrorSum=0/*-插入数据-*/INSERTINTOResult(StudentNo,SubjectNo,ExamDate,StudentResult)VALUES(10012,1,2009-5-20,102)-分数违反约束SETerrorSum=errorSum+ERRORIF(errorSum0)-如果有错误BEGINPRINT插入失败，回滚事务ROLLBACKTRANSACTIONENDELSEBEGINPRINT插入成功，提交事务COMMITTRANSACTIONEND,完成时间：20分钟,练习办理毕业学生离校手续,需求说明：将毕业学生的基本信息和考试成绩分别保存到历史表中提示：使用显式事务查询Result表中所有三年级学生的考试成绩，保存到表HistoreResult中删除Result表中所有三年级学生的考试成绩查询Student表中所有三年级的学生记录，保存到表HistoryStudent中删除Studet表中所有三年级学生记录,完成时间：20分钟,BEGINTRANSACTIONDECLAREerrorSumINTSETerrorSum=0/*-查询Result表中所有三年级学生的考试成绩，保存到新表HistoreResult*/SELECTResult.*INTOHistoreResultFROMResultINNERJOINStudentONResult.StudentNo=Student.StudentNoINNERJOINGradeONGrade.GradeId=Student.GradeIdWHEREGradeName=三年级SETerrorSum=errorSumERROR/*-删除Result表中所有三年级学生的考试成绩*/DELETEResultFROMResultJOINStudentONResult.StudentNo=Student.StudentNoINNERJOINGradeONGrade.GradeId=Student.GradeIdWHEREGradeName=三年级SETerrorSum=errorSum+ERROR/*-将Student表中所有三年级的学生记录，保存到新表HistoryStudent*/*-删除Studet表中所有三年级学生记录*/*-根据是否有错误，确定事务是提交还是撤销-*/,共性问题讲解,常见调试问题及解决办法代码规范问题,共性问题集中讲解,为什么需要视图,不同的人员关注不同的数据保证信息的安全性,老板,出纳,人力资源主管,员工,拒绝访问,什么是视图3-1,教师需要的视图：方便查看学生的成绩,CREATEVIEWvw_Student_ResultASSELECT姓名=stuName,学号=Student.StudentNo,基于学生信息表和成绩表创建视图,班主任需要的视图：方便查看学生的档案,什么是视图3-2,视图是一张虚拟表，表示一张表的部分数据或多张表的综合数据其结构和数据是建立在对表的查询基础上视图中不存放数据数据存放在视图所引用的原始表中一个原始表，根据不同用户的不同需求，可以创建不同的视图,什么是视图3-3,视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库,如何创建视图3-1,使用管理器创建视图：名称：vw_Student_Result,如何创建视图3-2,使用T-SQL语句创建视图,CREATEVIEWview_nameAS,使用T-SQL语句删除视图,IFEXISTS(SELECT*FROMsysobjectsWHEREname=view_name)DROPVIEWview_name,使用T-SQL语句查看视图,SELECT*FROMview_name,需要使用一对单引号括起,如何创建视图3-3,创建方便教师查看成绩的视图,IFEXISTS(SELECT*FROMsysobjectsWHEREname=vw_student_result)DROPVIEWvw_student_resultGOCREATEVIEWvw_student_resultASGOSELECT*FROMvw_student_result,检测是否存在,创建视图,查看视图,删除视图,使用视图注意事项,视图中可以使用多个表一个视图可以嵌套另一个视图视图定义中的SELECT语句不能包括下列内容：ORDERBY子句，除非在SELECT语句的选择列表中也有一个TOP子句INTO关键字引用临时表或表变量,指导查看学生各学期总成绩2-1,训练要点：使用视图获取多表中数据需求说明：统计每个学生各学期（大一、大二、大三）所有课程的总成绩,讲解需求说明,指导查看学生各学期总成绩2-2,实现思路：创建视图编写代码查询每个学生各学期参加考试的总成绩，每门课成绩以该生参加的最后一次考试为准编译执行视图，获得学生各学期的总成绩,CREATEVIEWvw_student_result_infoASSELECT姓名=StudentName,学号=Student.StudentNo,联系电话=Phone,学期=GradeName,成绩=TotalFROMStudentLEFTOUTERJOIN(SELECTr.StudentNo,GradeName,SUM(StudentResult)TotalFROMResultrINNERJOIN(SELECTStudentNo,SubjectNo,MAX(ExamDate)ExamDateFROMResultGROUPBYStudentNo,SubjectNo)tmpONr.ExamDate=tmp.ExamDateANDr.SubjectNo=tmp.SubjectNoANDr.StudentNo=tmp.StudentNoINNERJOINSubjectsubONsub.SubjectNo=r.SubjectNoINNERJOINGradegONg.GradeId=sub.GradeIdGROUPByr.StudentNo,GradeName)TmpResult2ONStudent.StudentNo=TmpResult2.StudentNoGROUPBYStudentName,Student.StudentNo,Phone,GradeName,TotalGOSELECT*FROMvw_student_result_info,完成时间：20分钟,SQLServer的安全模型,登录方式,登录验证有两种方式：SQL身份验证：适合于非windows平台的用户或Internet用户，需要提供帐户和密码Windows身份验证：适合于windows平台用户，不需要提供密码，和windows集成验证登录帐户相应有两种：SQL帐户和Windows帐户,创建登录,添加Windows登录帐户EXECsp_grantloginjbtrainingS26301添加SQL登录帐户EXECsp_addloginzhangsan,1234,演示创建登录,EXEC表示调用存储过程，存储过程类似C语言的函数,内置的系统管理员帐户sa密码默认为空，建议修改密码,域名用户名,用户名,密码,创建登录,创建数据库用户,创建数据库用户需要调用系统存储过程sp_grantdbaccess，其用法为：,EXECsp_grantdbaccess登录帐户名，数据库用户名其中，“数据库用户“为可选参数，默认为登录帐户，即数据库用户默认和登录帐户同名。,USEstuDBGOEXECsp_grantdbaccessjbtrainingS26301,S26301DBUserEXECsp_grantdbaccesszhangsan,zhangsanDBUser,在stuDB数据库中添加两个用户,创建数据库用户,系统内置的数据库用户,dbo用户表示数据库的所有者（DBOwner）无法删除dbo用户，此用户始终出现在每个数据库中guest用户适用于没有数据库用户的登录帐号访问每个数据库可有也可删除,向数据库用户授权,deleteFROMEmployees,SELECT*FROMEmployees,权限的类型,授权的语法为：GRANT权限ON表名TO数据库用户,USEstuDBGO/*-为zhangsanDBUser分配对表stuInfo的select,insert,update权限-*/GRANTselect,insert,updateONstuInfoTOzhangsanDBUser/*-为S26301DBUser分配建表的权限-*/GRANTcreatetableTOS26301DBUser,权限的类型,没有授予delete权限,共性问题讲解,常见调试问题及解决办法代码规范问题,共性问题集中讲解,什么是索引3-1,汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等我们可以根据拼音或偏旁部首，快速查找某个字词,什么是索引3-2,IndexesUseKeyValuestoLocateData（根据索引键查找定位数据行）,IndexPages（索引页）,什么是索引3-3,SQLServer中的数据也是按页存放索引：是SQLServer编排数据的内部方法。它为SQLServer提供一种方法来编排查询数据索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能,索引类型,唯一索引：唯一索引不允许两行具有相同的索引值主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个,如何创建索引3-1,使用SQLServerManagementStudio创建索引,如何创建索引3-2,使用T-SQL语句创建索引,CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEXindex_nameONtable_name(column_name)WITHFILLFACTOR=x,唯一索引,聚集索引或非聚集索引,填充因子（系数）:指定一个0100之间的值，表示索引页填充的百分比,使用T-SQL语句删除索引,DROPINDEXtable_name.index_name,删除表时，该表的所有索引同时会被删除,表名和索引名称之间，用“.”分隔,如何创建索引3-3,在Student表的StudentName列创建非聚集索引,USEMySchoolGO/*-检测是否存在该索引(索引存放在系统表sysindexes中)-*/IFEXISTS(SELECTnameFROMsysindexesWHEREname=IX_Student_StudentName)DROPINDEXStudent.IX_Student_StudentName-删除索引GO/*-学生姓名列创建非聚集索引：填充因子为30-*/CREATENONCLUSTEREDINDEXIX_Student_StudentNameONStudent(StudentName)WITHFILLFACTOR=30GO,检测索引是否存在：索引存放在系统表sysindexes中,按指定的索引查询数据,使用索引查询“李”姓的学生信息,/*-指定按索引：IX_Student_StudentName查询-*/SELECT*FROMStudentWITH(INDEX=IX_Student_StudentName)WHEREStudentNameLIKE李%,索引的优缺点,优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新,创建索引的指导原则,按照下列标准选择建立索引的列频繁搜索的列经常用作查询选择的列经常排序、分组的列经常用作联接的列（主键/外键）请不要使用下面的列创建索引仅包含几个不同值的列表中仅包含几行,使用索引时注意事项,查询时减少使用*返回全部列，不要返回不需要的列索引应该尽量小，在字节数小的列上建立索引WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前避免在ORDERBY子句中使用表达式根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理,查看索引,使用系统存储过程sp_helpIndex查看有关表或视图上索引的信息,EXECsp_helpI