使用java实现数据库编程

第五章

事务、视图、索引、备份和恢复预习检查SQL语句如何提交一个事务？事务具有的4个特性是什么？索引的作用是什么？数据库备份的命令是什么？集中测试2/61回顾与作业点评2-1下列语句实现了什么功能？

是否能够使用IN子查询改写上述语句？请使用连接查询改写上述语句SELECT*FROM`student`WHERE`studentNo`IN(SELECT`studentNo`FROM`result`);SELECT*FROM`student`INNERJOIN`result`ON`student`.`studentNo`=`result`.`studentNo`;SELECT*FROM`student`WHEREEXISTS(SELECT*FROM`result`WHERE`studentNo`=`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;CREATETABLE`bank`(`customerName`CHAR(10),#用户名

`currentMoney`DECIMAL(10,2)#当前余额 );/*插入数据*/INSERTINTO`bank`(`customerName`,`currentMoney`)VALUES('张三',1000);INSERTINTO`bank`(`customerName`,`currentMoney`)VALUES('李四',1);创建账户表，存放用户的账户信息张三开户，开户金额为1000元；李四开户，开户金额1元示例8/61为什么需要事务5-3目前两个账户的余额总和：1000+1=1001元示例演示示例1：创建账户表及测试数据9/61为什么需要事务5-4模拟实现转账从张三的账户转账500元到李四的账户正常情况下转账后两账户总和：500+501=1001元问题/*--转账测试:张三希望通过转账,直接汇钱给李四500元--*/#张三的账户减500元,李四的账户增500元UPDATE`bank`SET`currentMoney`=`currentMoney`-500WHERE`customerName`='张三';UPDATE`bank`SET`currentMoney`=`currentMoney`+500WHERE`customerName`='李四';如果转账过程中出现问题是什么情况？10/61为什么需要事务5-5张三账户：1000元更新成功李四账户：1元张三账户：500元故障账户金额总和：500+1=501如何解决可能发生的数据不一致问题？演示示例2：未使用事务进行转账11/61什么是事务事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作多个操作作为一个整体向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元转账过程就是一个整体它需要两条UPDATE语句来完成，这两条语句是一个整体如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元12/61事务的特性事务必须具备以下四个属性，简称ACID属性原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持久性（Durability）事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行当事务完成时，数据必须处于一致状态并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务事务完成后,它对数据库的修改被永久保持13/61如何创建事务MySQL中支持事务的存储引擎有InnoDB和BDB开始事务提交事务回滚（撤销）事务ROLLBACK;COMMIT;BEGIN;或STARTTRANSACTION;语法14/61BEGIN;/*--转账:张三的账户减少500元,李四的账户增加500元--*/UPDATE`bank`SET`currentMoney`=`currentMoney`-500WHERE`customerName`='张三';UPDATE`bank`SET`currentMoney`=`currentMoney`+500WHERE`customerName`='李四';COMMIT;使用事务解决银行转账问题3-1从张三的账户转出500元，存入李四的账户中开始事务（指定事务从此处开始，后续的SQL语句都是一个整体）提交事务，事务结果示例15/61如何创建事务2-2事务过程中事务执行完毕示例使用事务解决银行转账问题3-216/61示例BEGIN;UPDATE`bank`SET`currentMoney`=`currentMoney`-1000WHERE`customerName`='张三';ROLLBACK;如何创建事务2-2如何创建事务2-2回滚事务，数据恢复到原始状态演示示例3：使用事务进行转账使用事务解决银行转账问题3-317/61自动关闭和开启事务2-1默认情况下，每条单独的SQL语句视为一个事务关闭默认提交状态后，可手动开启、关闭事务关闭/开启自动提交状态值为0：关闭自动提交值为1：开启自动提交语法SETmit=0|1;关闭自动提交后，从下一条SQL语句开始则开启新事务，需使用COMMIT或ROLLBACK语句结束该事务!18/61示例自动关闭和开启事务2-2SETmit=0;/*--转账:张三的账户减少500元,李四的账户增加500元--*/UPDATE`bank`SET`currentMoney`=`currentMoney`-500WHERE`customerName`='张三';UPDATE`bank`SET`currentMoney`=`currentMoney`+500WHERE`customerName`='李四';COMMIT;UPDATE`bank`SET`currentMoney`=`currentMoney`-1000WHERE`customerName`='张三';ROLLBACK;SETmit=1;关闭自动提交，以下视为一个事务提交事务回滚事务开启自动提交，恢复默认状态演示示例4：关闭自动提交19/61学员操作——批量插入学生成绩2-1训练要点使用事务向表中插入多条记录需求说明批量插入参加今天“LogicJava”课程考试的十名学生成绩如果输入的成绩大于100分，则取消操作指导讲解需求说明20/61学员操作——批量插入学生成绩2-2实现思路开启事务插入3条正确数据，在未提交时，打开另一会话查看成绩数据是否变化提交事务打开另一会话查看成绩数据是否变化开启事务插入2条错误数据，回滚事务，查看成绩数据是否变化指导完成时间：20分钟21/61学员操作——办理学生离校手续需求说明将毕业学生的基本信息和考试成绩分别保存到历史表中使用事务完成以下操作查询result表中所有Y2学员的考试成绩，保存到表historyResult中删除result表中所有Y2学员的考试成绩查询student表中所有Y2学员的记录，保存到表historyStudent中删除student表中所有Y2学员的记录提交事务，查看各表中数据的变化回滚事务，查看各表中数据的变化练习提示完成时间：20分钟22/61常见问题及解决办法代码规范问题调试技巧共性问题集中讲解共性问题集中讲解23/61雇员ID姓名薪金职务绩效E10004IanSmith43000技术BE10005RickyM.32000营销A………………………………………………………………………………为什么需要视图不同的人员关注不同的数据保证信息的安全性雇员数据库老板出纳人力资源主管员工拒绝访问24/61什么是视图3-1教师需要的视图：

方便查看学生的成绩基于学生信息表和成绩表创建视图班主任需要的视图：

方便查看学生的档案示例25/61什么是视图3-2视图是一张虚拟表表示一张表的部分数据或多张表的综合数据其结构和数据是建立在对表的查询基础上视图中不存放数据数据存放在视图所引用的原始表中一个原始表，根据不同用户的不同需求，可以创建不同的视图26/61什么是视图3-3视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库27/61如何创建视图2-1使用SQL语句创建视图CREATEVIEWview_name

AS<SELECT语句>;使用SQL语句删除视图DROPVIEW[IFEXISTS]view_name;使用SQL语句查看视图SELECT

字段1,字段2,……FROM

view_name;删除前判断视图是否存在语法语法语法28/61如何创建视图2-2创建方便教师查看成绩的视图USEmyschool;DROPVIEWIFEXISTS`view_student_result`;CREATEVIEW`view_student_result`AS

……SELECT*FROM`view_student_result`;创建视图查看视图删除视图示例演示示例5：创建并查询视图29/61使用视图注意事项视图中可以使用多个表一个视图可以嵌套另一个视图对视图数据进行添加、更新和删除操作直接影响所引用表中的数据当视图数据来自多个表时，不允许添加和删除数据查看所有视图使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询!提示USEinformation_schema;SELECT*FROMviews\G;经验30/61学员操作——查看学生各科目考试成绩平均分2-1训练要点使用视图获取多表中数据需求说明统计每个学生所参考课程的平均成绩指导讲解需求说明31/61指导学员操作——查看学生各科目考试成绩平均分2-2实现思路创建视图，查询语句需关联多张表，可使用表连接或子查询方式编码查看视图的运行结果完成时间：20分钟32/61常见问题及解决办法代码规范问题调试技巧共性问题集中讲解共性问题集中讲解33/61什么是索引3-1汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等我们可以根据拼音或偏旁部首，快速查找某个字词34/61什么是索引3-2IndexesUseKeyValuestoLocateData（根据索引键查找定位数据行）DataPages（数据页）…IndexPages（索引页）

35/61什么是索引3-3索引是一种有效组合数据的方式，为快速查找到指定记录作用大大提高数据库的检索速度改善数据库性能MySQL索引按存储类型分类B-树索引：InnoDB、MyISAM均支持哈希索引36/61常用索引类型普通索引基本索引类型允许在定义索引的列中插入重复值和空值唯一索引索引列数据不重复允许有空值主键索引主键列中的每个值是非空、唯一的一个主键将自动创建主键索引复合索引将多个列组合作为索引全文索引支持值的全文查找允许重复值和空值空间索引对空间数据类型的列建立的索引37/61如何创建/删除索引2-1创建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtable_name(column_name[length]…);唯一索引、全文索引或空间索引，可选删除索引DROPINDEXindex_nameONtable_name;删除表时，该表的所有索引同时会被删除语法语法38/61如何创建/删除索引2-2在student表的studentName列创建普通索引USEmyschool;CREATEINDEX`index_student_studentName`ON`student`(`studentName`);示例演示示例6：创建索引39/61创建索引的指导原则按照下列标准选择建立索引的列频繁搜索的列经常用作查询选择的列经常排序、分组的列经常用作连接的列（主键/外键）请不要使用下面的列创建索引仅包含几个不同值的列表中仅包含几行40/61使用索引时注意事项查询时减少使用*返回全部列，不要返回不需要的列索引应该尽量小，在字节数小的列上建立索引WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前避免在ORDERBY子句中使用表达式经验41/61查看索引SHOWINDEXFROMtable_name;语法示例查看myschool数据库中全部索引信息USEmyschool;SHOWINDEXFROM`student`\G;Table：创建索引的表Non_unique：索引是否非唯一Key_name：索引的名称Column_name：定义索引的列字段Seq_in_index：该列在索引中的位置Null：该列是否能为空值Index_type：索引类型演示：查看索引42/61需求说明为提高以下查询的速度，为学生表和成绩表添加适合的索引，并查看索引按学生名和年级编号组合查询学生身份证号是唯一的按成绩区间范围查找学生考试信息可使用唯一索引、普通索引、组合索引提高查询效率学员操作——创建学生表和成绩表索引并查看索引练习完成时间：20分钟提示43/61常见问题及解决办法代码规范问题调试技巧共性问题集中讲解共性问题集中讲解44/61为什么进行数据库备份数据库故障突然断电病毒入侵误操作导致数据丢失……如何避免意外状况导致的数据丢失？问题45/61使用mysqldump命令备份数据库4-1mysqldump命令——MySQL一个常用的备份工具将CREATE和INSERTINTO语句保存到文本文件属于DOS命令mysqldump[options]

–uusername–hhost–ppassworddbname[tbname1[,tbname2……]]>filename.sql用户名登录主机名密码备份后的文件名称需备份的数据库名需备份的表名（省略则备份所有表）mysqldump是DOS系统下的命令，在使用时无须进入mysql命令行，否则将无法执行!注意选项参数46/61使用root账户登录到MySQL服务器，备份myschool数据库下的student表

使用mysqldump命令备份数据库4-2示例mysqldump–uroot–pmyschoolstudent>d:\backup\myschool_20160808.sql输入密码为保证账户密码安全，命令中可不写密码，但参数“-p”必须有，回车后根据提示写密码!注意47/61使用mysqldump命令备份数据库4-3参数描述-add-drop-table在每个CREATETABLE语句前添加DROPTABLE语句，默认是打开的，可以用-skip-add-drop-table来取消--add-locks该选项会在INSERT语句中捆绑一个LOCKTABLE和UNLOCKTABLE语句好处：防止记录被再次导入时，其他用户对表进行的操作-t或-no-create-info只导出数据，而不添加CREATETABLE语句-c或plete-insert在每个INSERT语句的列上加上列名，在数据导入另一个数据库时有用-d或--no-data不写表的任何行信息，只转储表的结构常用参数选项48/61备份文件包含的主要信息备份后文件包含信息MySQL及mysqldump工具版本号备份账户的名称主机信息备份的数据库名称SQL语句注释和服务器相关注释CREATE和INSERT语句备份文件使用mysqldump命令备份数据库4-4演示示例7：mysqldump备份数据库49/61恢复数据库3-1方法一：使用mysql命令恢复数据库mysql–uusername–p[dbname]<filename.sql用户名数据库名备份文件名1.mysql为DOS命令2.在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在恢复数据库过程将会出错!注意50/61为何报错？使用备份文件将myschool数据库中student表信息恢复到schoolDB数据库中示例mysql–uroot–pschoolDB<d:\backup\myschool_20160808.sql恢复数据库3-251/61方法二：使用source命令恢复数据库恢复数据库3-3语法sourcefilename;示例CREATEDATABASEmyschoolDB2;#创建数据库USEmyschoolDB2;#选择数据库sourced:\backup\myschool_20160808.sql;#恢复数据库演示示例8：mysql/source恢复数据库登录MySQL服务后使用执行该命令前，先创建并选择恢复后的目标数据库注意52/61通过复制文件实现数据备份和恢复直接复制MySQL数据库的存储目录及文件进行备份缺点复制数据前停止服务不适合InnoDB数据库MySQL版本不同不兼容优点操作简单此方法不是最优的数据库备份和恢复方案！!53/61表数据导出到文本文件导出/导入数据——可实现数据库服务器间移动数据将成绩表中“LogicJava”课程的成绩信息导出到文本文件SELECT*FROMtablename[WHEREcontion]

INTOOUTFILE'filename'[OPTION]语法示例SELECT*FROM`result`WHERE`subjectNo`=(SELECT`subjectNo`FROM`subject`WHERE`subjectName`='LogicJava')INTOOUTFILE'd:/backup/result_Java.txt';导出的目标文件54/61文本文件导入到数据表语法LOADDATAINFILEfilenameINTOTABLEtablename[OPTION]将数据从文本文件导入到myschoolDB的result表LOADDATAINFILE'd:/back/result_Java.txt'INTOTABLEresult;导入数据前应确保目标表已存在！!注意演示示例9：表数