Chapter05S2升Y2TP05

第五章事务、视图、索引、备份和恢复,预习检查,SQL语句如何提交一个事务？事务具有的4个特性是什么？索引的作用是什么？数据库备份的命令是什么？,集中测试,2/61,回顾与作业点评2-1,下列语句实现了什么功能？请使用IN子查询改写上述语句？请使用连接查询改写上述语句,SELECT*FROMstudentWHEREstudentNoIN(SELECTstudentNoFROMresult);,SELECT*FROMstudentINNERJOINresultONstudent.studentNo=result.studentNo;,SELECT*FROMstudentWHEREEXISTS(SELECT*FROMresultWHEREstudentNo=student.studentNo);,查询参加考试的学生,提问,3/61,点评作业的提交情况和共性问题,回顾与作业点评2-2,作业点评,4/61,本章任务,批量插入学生考试成绩办理毕业学生离校手续查看学生各科目考试成绩平均分创建学生表和成绩表索引并查看索引myschool数据库的备份和恢复科目表数据的导出和导入,5/61,本章目标,使用事务保证操纵数据的完整性掌握如何创建并使用视图掌握如何创建并使用索引掌握如何进行数据库的备份和恢复,6/61,为什么需要事务5-1,银行转账问题假定资金从账户A转到账户B，至少需要两步账户A的资金减少然后账户B的资金相应增加,银行转账,账户A,账户B,示例,7/61,为什么需要事务5-2,假定张三的账户有1000元，李四账户有1元,CREATEDATABASEmybank;USEmybank;CREATETABLEbank(customerNameCHAR(10),#用户名currentMoneyDECIMAL(10,2)#当前余额);/*插入数据*/INSERTINTObank(customerName,currentMoney)VALUES(张三,1000);INSERTINTObank(customerName,currentMoney)VALUES(李四,1);,创建账户表，存放用户的账户信息,张三开户，开户金额为1000元；李四开户，开户金额1元,示例,8/61,为什么需要事务5-3,目前两个账户的余额总和：1000+1=1001元,示例,9/61,为什么需要事务5-4,模拟实现转账从张三的账户转账500元到李四的账户正常情况下转账后两账户总和：500+501=1001元,问题,/*-转账测试:张三希望通过转账,直接汇钱给李四500元-*/#张三的账户减500元,李四的账户增500元UPDATEbankSETcurrentMoney=currentMoney-500WHEREcustomerName=张三;UPDATEbankSETcurrentMoney=currentMoney+500WHEREcustomerName=李四;,如果转账过程中出现问题是什么情况？,10/61,为什么需要事务5-5,张三账户：1000元,更新成功,李四账户：1元,张三账户：500元,故障,账户金额总和：500+1=501,如何解决可能发生的数据不一致问题？,11/61,什么是事务,事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作多个操作作为一个整体向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元,转账过程就是一个整体它需要两条UPDATE语句来完成，这两条语句是一个整体如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元,12/61,事务的特性,事务必须具备以下四个属性，简称ACID属性原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持久性（Durability）,事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行,当事务完成时，数据必须处于一致状态,并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务,事务完成后,它对数据库的修改被永久保持,13/61,如何创建事务,MySQL中支持事务的存储引擎有InnoDB和BDB开始事务提交事务回滚（撤销）事务,ROLLBACK;,COMMIT;,BEGIN;或STARTTRANSACTION;,语法,14/61,BEGIN;/*-转账:张三的账户减少500元,李四的账户增加500元-*/UPDATEbankSETcurrentMoney=currentMoney-500WHEREcustomerName=张三;UPDATEbankSETcurrentMoney=currentMoney+500WHEREcustomerName=李四;COMMIT;,使用事务解决银行转账问题3-1,从张三的账户转出500元，存入李四的账户中,开始事务（指定事务从此处开始，后续的SQL语句都是一个整体）,提交事务，事务结果,示例,15/61,如何创建事务2-2,事务过程中事务执行完毕,示例,使用事务解决银行转账问题3-2,16/61,示例,BEGIN;UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三;ROLLBACK;,如何创建事务2-2,如何创建事务2-2,回滚事务，数据恢复到原始状态,使用事务解决银行转账问题3-3,17/61,自动关闭和开启事务2-1,默认情况下，每条单独的SQL语句视为一个事务关闭默认提交状态后，可手动开启、关闭事务关闭/开启自动提交状态值为0：关闭自动提交值为1：开启自动提交,语法,SETautocommit=0|1;,关闭自动提交后，从下一条SQL语句开始则开启新事务，需使用COMMIT或ROLLBACK语句结束该事务,!,18/61,示例,自动关闭和开启事务2-2,SETautocommit=0;/*-转账:张三的账户减少500元,李四的账户增加500元-*/UPDATEbankSETcurrentMoney=currentMoney-500WHEREcustomerName=张三;UPDATEbankSETcurrentMoney=currentMoney+500WHEREcustomerName=李四;COMMIT;UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=张三;ROLLBACK;SETautocommit=1;,关闭自动提交，以下视为一个事务,提交事务,回滚事务,开启自动提交，恢复默认状态,19/61,学员操作批量插入学生成绩2-1,训练要点使用事务向表中插入多条记录需求说明批量插入参加今天“LogicJava”课程考试的十名学生成绩如果输入的成绩大于100分，则取消操作,指导,20/61,学员操作批量插入学生成绩2-2,实现思路开启事务插入3条正确数据，在未提交时，打开另一会话查看成绩数据是否变化提交事务打开另一会话查看成绩数据是否变化开启事务插入2条错误数据，回滚事务，查看成绩数据是否变化,指导,21/61,学员操作办理学生离校手续,需求说明将毕业学生的基本信息和考试成绩分别保存到历史表中使用事务完成以下操作查询result表中所有Y2学员的考试成绩，保存到表historyResult中删除result表中所有Y2学员的考试成绩查询student表中所有Y2学员的记录，保存到表historyStudent中删除student表中所有Y2学员的记录提交事务，查看各表中数据的变化回滚事务，查看各表中数据的变化,练习,提示,22/61,常见问题及解决办法代码规范问题调试技巧,共性问题集中讲解,23/61,为什么需要视图,不同的人员关注不同的数据保证信息的安全性,老板,出纳,人力资源主管,员工,拒绝访问,24/61,什么是视图3-1,教师需要的视图：方便查看学生的成绩,基于学生信息表和成绩表创建视图,班主任需要的视图：方便查看学生的档案,示例,25/61,什么是视图3-2,视图是一张虚拟表表示一张表的部分数据或多张表的综合数据其结构和数据是建立在对表的查询基础上视图中不存放数据数据存放在视图所引用的原始表中一个原始表，根据不同用户的不同需求，可以创建不同的视图,26/61,什么是视图3-3,视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库,27/61,如何创建视图2-1,使用SQL语句创建视图,CREATEVIEWview_nameAS;,使用SQL语句删除视图,DROPVIEWIFEXISTSview_name;,使用SQL语句查看视图,SELECT字段1,字段2,FROMview_name;,删除前判断视图是否存在,语法,语法,语法,28/61,如何创建视图2-2,创建方便教师查看成绩的视图,USEmyschool;DROPVIEWIFEXISTSview_student_result;CREATEVIEWview_student_resultASSELECT*FROMview_student_result;,创建视图,查看视图,删除视图,示例,29/61,使用视图注意事项,视图中可以使用多个表一个视图可以嵌套另一个视图对视图数据进行添加、更新和删除操作直接影响所引用表中的数据当视图数据来自多个表时，不允许添加和删除数据查看所有视图,使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询,!,提示,USEinformation_schema;SELECT*FROMviewsG;,经验,30/61,学员操作查看学生各科目考试成绩平均分2-1,训练要点使用视图获取多表中数据需求说明统计每个学生所参考课程的平均成绩,指导,31/61,指导,学员操作查看学生各科目考试成绩平均分2-2,实现思路创建视图，查询语句需关联多张表，可使用表连接或子查询方式编码查看视图的运行结果,32/61,常见问题及解决办法代码规范问题调试技巧,共性问题集中讲解,33/61,什么是索引3-1,汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等我们可以根据拼音或偏旁部首，快速查找某个字词,34/61,什么是索引3-2,IndexesUseKeyValuestoLocateData（根据索引键查找定位数据行）,IndexPages（索引页）,35/61,什么是索引3-3,索引是一种有效组合数据的方式，为快速查找到指定记录作用大大提高数据库的检索速度改善数据库性能MySQL索引按存储类型分类B-树索引：InnoDB、MyISAM均支持哈希索引,36/61,常用索引,37/61,如何创建/删除索引2-1,创建索引,CREATEUNIQUE|FULLTEXT|SPATIALINDEXindex_nameONtable_name(column_namelength);,唯一索引、全文索引或空间索引，可选,删除索引,DROPINDEXindex_nameONtable_name;,删除表时，该表的所有索引同时会被删除,语法,语法,38/61,如何创建/删除索引2-2,在student表的studentName列创建普通索引,USEmyschool;CREATEINDEXindex_student_studentNameONstudent(studentName);,示例,39/61,索引的优缺点,优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新,40/61,创建索引的指导原则,按照下列标准选择建立索引的列频繁搜索的列经常用作查询选择的列经常排序、分组的列经常用作连接的列（主键/外键）请不要使用下面的列创建索引仅包含几个不同值的列表中仅包含几行,41/61,使用索引时注意事项,查询时减少使用*返回全部列，不要返回不需要的列索引应该尽量小，在字节数小的列上建立索引WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前避免在ORDERBY子句中使用表达式根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理,经验,42/61,查看索引,SHOWINDEXFROMtable_name;,语法,示例,查看myschool数据库中全部索引信息,USEmyschool;SHOWINDEXFROMstudentG;,Table：创建索引的表Non_unique：索引是否非唯一Key_name：索引的名称Column_name：定义索引的列字段Seq_in_index：该列在索引中的位置Null：该列是否能为空值。Index_type：索引类型,43/61,需求说明为提高以下查询的速度，为学生表和成绩表添加适合的索引，并查看索引按学生名和年级编号组合查询学生身份证号是唯一的按成绩区间范围查找学生考试信息可使用唯一索引、普通索引、组合索引提高查询效率,学员操作创建学生表和成绩表索引并查看索引,练习,提示,44/61,常见问题及解决办法代码规范问题调试技巧,共性问题集中讲解,45/61,为什么进行数据库备份,数据库故障突然断电病毒入侵误操作导致数据丢失,如何避免意外状况导致的数据丢失？,问题,46/61,使用mysqldump命令备份数据库4-1,mysqldump命令MySQL一个常用的备份工具将CREATE和INSERTINTO语句保存到文本文件属于DOS命令,mysqldumpoptionsuusernamehhostppassworddbnametbname1,tbname2filename.sql,用户名,登录主机名,密码,备份后的文件名称,需备份的数据库名,需备份的表名（省略则备份所有表）,mysqldump是DOS系统下的命令，在使用时无须进入mysql命令行，否则将无法执行,!,注意,选项参数,47/61,使用root账户登录到MySQL服务器，备份myschool数据库下的student表,使用mysqldump命令备份数据库4-2,示例,mysqldumpurootpmyschoolstudentd:backupmyschool_20160808.sql,输入密码,为保证账户密码安全，命令中可不写密码，但参数“-p”必须有，回车后根据提示写密码,!,注意,48/61,使用mysqldump命令备份数据库4-3,常用参数选项,49/61,备份文件包含的主要信息备份后文件包含信息MySQL及mysqldump工具版本号备份账户的名称主机信息备份的数据库名称SQL语句注释和服务器相关注释CREATE和INSERT语句备份文件,使用mysqldump命令备份数据库4-4,50/61,恢复数据库3-1,方法一：使用mysql命令恢复数据库,mysqluusernamepdbnamefilename.sql,用户名,数据库名,备份文件名,1.mysql为DOS命令2.在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在恢复数据库过程将会出错,!,注意,51/61,为何报错？,使用备份文件将myschool数据库中student表信息恢复到schoolDB数据库中,示例,mysqlurootpschoolDBd:backupmyschool_20160808.sql,恢复数据库3-2,52/61,方法二：使用source命令恢复数据库,恢复数据库3-3,语法,sourcefilename;,示例,CREATEDATABASEmyschoolDB2;#创建数据库USEmyschoolDB2;#选择数据库sourced:backupmyschool_20160808.sql;#恢复数据库,登录MySQL服务后使用执行该命令前，先创建并选择恢复后的目标数据库,注意,53/61,通过复制文件实现数据备份和恢复,直接复制MySQL数据库的存储目录及文件进行备份,此方法不是最优的数据库备份和恢复方案！,!,54/61,表数据导出到文本文件,导出/导入数据可实现数据库服务器间移动数据将成绩表中“LogicJava”课程的成绩信息导出到文本文件,SELECT*FROMtablenameWHEREcontionINTOOUTFILEfilenameOPTION,语法,示例,SELECT*FROMresultWHEREsubjectNo=(SELECTsubjectNoFROMsubjectWHEREsubjectName=LogicJava)INTOOUTFILEd:/backup/result_Java.txt;,导出的目标文件,55/61,文本文件导入到数据表,语法,LOADDATAINFILEfilenameINTOTABLEtablenameOPTION,将数据从文本文件导入到myschoolDB的result表,LOADDATAINFILEd:/back/result_Java.txtINTOTABLEresult