数据库高级应用技术_第1页
数据库高级应用技术_第2页
数据库高级应用技术_第3页
数据库高级应用技术_第4页
数据库高级应用技术_第5页
已阅读5页,还剩316页未读, 继续免费阅读

付费下载

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

高等职业技术院校教材《数据库高级应用技术》

主编:温立辉wenlihui2004@163.com1三大范式本单元教学目标了解数据库范式的概念和分类理解数据库三大基本范式创建满足三大范式的数据库表2数据库范式的概念什么是数据库范式?为了减少数据冗余、避免操作异常,我们在设计数据库时必须遵循一定的规则,这种规则称为范式3数据库范式作用为什么要使用三大范式?满足这些规范的数据库:简洁、结构明晰写操作时不会发生异常插入(insert)删除(delete)更新(update)不满足这些规范的数据库形式上乱七八糟、面目可憎给数据库的编程人员制造麻烦可能存储了大量不需要的冗余信息4数据库范式分类第一范式(1NF)第二范式(2NF) 三大基本范式第三范式(3NF)Boyce-Codd范式(BCNF)第四范式(4NF)第五范式(5NF)5第一范式(1NF)定义:数据库表中的字段都是单一属性,字段不可再分同一列中不能有多个值这个单一属性由基本类型构成,包括:整型实数字符型逻辑型日期型其它类型6不符合第一范式的表7第二范式(2NF)定义:数据库表满足第一范式数据库中每张表均有主键单字段主键联合主键不能存在联合主键中某个主键字段决定非主键字段的情况如:表中有A、B、C、D、E五个字段若A与B为联合主键(A,B)如有A决定C的情况(AC)则不符合2NF8联合主键由两个或两个以上的字段共同组成的主键“客户”、“开户银行”两个字段为联合主键(客户客户电话)、(开户银行开户行地址)则此表不符合第二范式(2NF)9判断此表是否符合第二范式没有主键,不符合第二范式(2NF)10增加主键字段符合第二范式11判断此表是否符合第二范式存在某个主键字段决定非主键字段,不符合第二范式(2NF)12拆成三张表符合第二范式13第三范式(3NF)定义:数据库表满足第二范式数据表的非主键字段不存在传递依赖关系即:非主键字段不能决定其它非主键字段如:表中有A、B、C、D、E五个字段若A为主键如有C决定D的情况(CD)则不符合3NF14不符合第三范式的表“工号”字段为主键(pk)“部门”

“部门电话”“部门”

“部门主管”则存在非主键字段决定其它非主键字段因而此表不符合第三范式(3NF)15判断此表是否符合第三范式

存在某个非主键字段决定其它非主键字段,不符合第三范式(3NF)16拆成二张表符合第三范式

17课堂练习根据如下关系模式设计数据表一个管理员仅管理一个仓库一个仓库仅存放一类物品管理员归属于某一个岗位管理员受雇于某一个部门所有数据表需满足三大范式18实体关系图

管理员仓库管理工号姓名投影机笔记本打印机部门部门办公地址部门主管部门电话归属岗位名称岗位级别岗位职责岗位受雇19关系数据库基本概念关系模式(RelationSchema)关系模式描述的是数据库二维表的表结构,即表结构中包含那些属性,属性的类型、长度等。20关系数据库基本概念关系(Relation)关系指是数据库中的二维表的数据。数据库表中的内容是对应关系模式在某个时刻的值,称为一个关系。21关系数据库基本概念元组(Tuple)元组是关系数据库中的基本概念,数据库表中的一行就是一个元组,元组也称为记录。22关系数据库基本概念码(Key)码是关系数据库系统中的基本概念所谓码就是能唯一标识实体的属性集,是整个属性集,而不是单个属性在关系数据库中,码包括多种类型,如:超码,候选码,主码23关系数据库基本概念超码(SuperKey)超码是一个或多个属性的集合,这些属性可以让我们在一个实体集中唯一地标识一个实体。如果K是超码,那么所有包含K的集合也是超码。24关系数据库基本概念候选码(CandidateKey)候选码是可以唯一标识一个元组的最少的属性集合。候选码是从超码中选出的,因而候选码也是一个或多个属性的集合如:学生表有属性:学号、身份证、姓名、年级、专业其中:学号、身份证均是候选码25关系数据库基本概念主码(PrimaryKey)主码是从多个候选码中任意选出一个做为主键,这个被选中的候选码就称为主码。如果候选码只有一个,那么候选码就是主码。26关系模式定义关系模式:关系模式是数据库二维表的表结关系是数据库二维表中的数据记录关系是动态的,关系模式是静态的一个关系模式通常表示为:R(A1,A2,A3,...,An)R为关系模式名称A1,A2,A3,...,An均为属性如学生关系模式:Student(SNO,SNAME,AGE,SEX,SDEPT);27关系模式分解有损分解关系模式分解后原关系中的相关信息发生丢失的情况分解时不丢失,重新自然联接后丢失无损分解关系模式分解后仍能完整、完全的保持原关系中的相关数据,信息不失真的分解方式分解时不丢失,重新自然联接后也不丢失28单元作业设计一个图书管理系统借书模块读者人数众多、可借图书众多需维护读者的姓名、年龄、职业、学历、地址、电话、是否可借阅等信息需维护图书的书名、出版社、ISBN号、出版日期、书本价格等信息需维护出借的日期、到期时间、是否续期等信息需满足数据库三大范式开发出对应的SQL脚本29高等职业技术院校教材《数据库高级应用技术》

主编:温立辉wenlihui2004@163.com30事务管理本单元教学目标认识、了解事务的概念、作用理解事务控制的原理、规则深入理解解事务的ACID特性深入认识关系数据库事务级别分类掌握关系数据库事务控制方法31事务管理问题的引入

事务并发控制32问题的引入多用户同时操作数据库系统在执行用户的请求时出现故障例如:帐户A转帐给帐户B10000元。UPDATEYHZHSET余额=余额-10000WHERE帐号=‘A’UPDATEYHZHSET余额=余额+10000WHERE帐号=‘B’33事务的基本概念事务(Transaction)用户定义的一个对数据库读写操作序列一个不可分割的工作单位在关系数据库中,事务可以是一条、一组SQL语句,或整个程序。事务和程序的区别?程序包含多个事务34事务的性质原子性(Atomicity)事务中的操作要么都做,要么都不做(AllorNone)一致性(Consistency)在事务完成或回滚时,必须使所有的数据都保持一致状态,即所有数据必须是完整性的,所有数据都必须是正确的。隔离性(Isolation)并发执行的各事务不能相互干扰持续性/永久性(Durability)事务一旦提交,它对数据库的更新不再受后继操作或故障的影响★

DBMS中事务处理必须保证其ACID特性,这样才能保证数据库中数据的安全和正确。35银行转帐:从帐号A中取出一万元,存入帐号B。定义一个事务,该事务包括两个操作这两个操作要么全做,要么全不做全做或者全不做,数据库都处于一致性状态。如果只做一个操作,数据库就处于不一致性态。

B=B+1

A=A-1BA

事务的性质36T1的修改被T2覆盖了!

读A=16

A←A-3写回A=13①读A=16

②

③A←A-1写回A=15

④T2T1

事务的性质37

事务的基本概念事务的开始和结束可以由用户显式控制。SQL定义事务的语句Starttransaction(事务开始)Commit(事务提交,正常结束,将更新结果写入磁盘)Rollback(事务回滚,撤销事务中所有已完成的更新)38事务的基本概念显式定义方式STARTTRANSACTIONSTARTTRANSACTIONSQL语句1SQL语句1SQL语句2SQL语句2。。。。。。。。。。COMMITROLLBACK39COMMIT事务正常结束提交事务的所有操作(读+更新)事务中所有对数据库的更新永久生效ROLLBACK事务异常终止事务运行的过程中发生了故障,不能继续执行,回滚事务的所有更新操作事务回滚到开始时的状态事务的基本概念40事务的基本概念隐含事务与自动提交

ALTERINSERTCREATEDELETEDROPSELECTUPDATETRUNCATETABEL41例:删除仓库“WH1”表,并将职工T表当中所有在“WH1”仓库中职工记录删除。事务案例BEGINTRANSACTIONMYDELDELETEFROM仓库TWHERE仓库号='WH1'DELETEFROM职工TWHERE仓库号='WH1'IF@@ERROR<>0ROLLBACKTRANSACTIONMYDELELSECOMMITTRANSACTIONMYDEL42干扰问题

解决干扰——封锁

封锁不当——死锁

封锁与隔离级别

并发控制43干扰问题丢失更新问题读“脏”数据问题不一致分析问题幻象读问题44更新丢失问题

用SQL术语描述丢失更新问题45脏读问题(DirtyRead)

读“脏”(DirtyRead)数据问题:查询一个已经被其他事务更新、但尚未提交的数据,将会引起脏读问题。

46不一致分析问题

不一致分析问题也称为不可重复读问题,很多应用可能需要校验功能,这时往往需要连续两次或多次读数据进行校验和分析,结果由于其他事务的干扰,使得前后结果不一致,从而产生校验错误(即不一致的分析)。如,对某个数据(a=10)作两次校验:1、检查其值是否为2的倍数,2、检查其减去20后是否大于0(a-20>0)如果在第1与第2步之间,这个数据被其它进程更改为25(a=25)则这个不合法的数据将变为合法数据。47幻读问题

幻象读问题与不一致分析问题有关,当事务A读数据时,事务B在对同一个关系(事务A的结果集)进行插入或删除操作,这时事务A再遍历结果集时(读同一条件的数据),会发现神秘地多出了一些元组或丢失了一些元组,把这种现象称作幻象读。

48可串行性可串行化的调度:多个事务的并发执行是正确的,当且仅当其结果与按某一顺序串行地执行它们时的结果相同可串行性是并发事务操作是否正确的判别准则49可串行性例子:两个事务:初值:A=10,B=10。T1:SELECTAUPDATEA=A-5SELECTBUPDATEB=B+5T2:SELECTBUPDATEB=B-550可串行性T1:SELECTAUPDATEA=A-5SELECTBUPDATEB=B+5T2:

SELECTBUPDATEB=B-551可串行性T1:SELECTAUPDATEA=A-5SELECTBUPDATEB=B+5T2:SELECTBUPDATEB=B-552可串行性T1:SELECTAUPDATEA=A-5SELECTBUPDATEB=B+5T2:SELECTBUPDATEB=B-553可串行性T1:SELECTAUPDATEA=A-5SELECTBUPDATEB=B+5T2:SELECTBUPDATEB=B-5不可串行化54封锁

封锁的基本技术

封锁机制

MySQL中与封锁有关的命令

封锁粒度

意向锁

55封锁的基本技术当需要查询或更新数据时,先对数据进行封锁,以避免来自其他事务的干扰。针对不同的干扰问题可以有不同的封锁机制。

以丢失更新问题为例,实施封锁的基本思想是:当一个用户对一个表或记录进行更新时,封锁该表或记录,使其他用户不能在同一时刻更新相同的表或记录,迫使其他用户在更新后的基础上(而不是在更新前的基础上)再实施另外的更新操作。

56封锁的基本技术实施封锁以后的事件进程57封锁机制

共享封锁

独占封锁

更新封锁

有些封锁在执行完相应操作后就自动释放封锁,有些封锁则保持到事务结束(提交或撤消)时才释放(无论如何,所有的封锁都会在事务结束时自动释放)。

58共享封锁

共享封锁是为读操作设置的一种封锁,所以也称作读锁,或简称S锁目的是想读到一组不变的数据,也就是在读数据的过程中,不允许其他用户对该数据进行任何修改操作。这种封锁可以保证最大的读并发性,任何数量的用户都可以同时对同样的数据施加这种共享锁。已经实施共享锁的表拒绝来自其他事务的独占封锁和更新封锁。

59独占封锁

独占封锁也叫排他封锁,它是为写(修改、增加、删除)操作设置的一种封锁,也称为写封锁,或简称为X锁,这是最严格的一类封锁。当需要对表实施插入、删除或修改操作时,应该使用独占封锁。已经实施独占封锁的表,拒绝来自其他用户的任何封锁。60更新封锁

当需要对一个记录或一组记录进行更新时(只是修改,不包括插入和删除)使用更新封锁,该封锁的目的是防止其他用户在同一时刻修改同一记录。已经实施更新封锁的记录,拒绝来自其他用户的任何封锁。61封锁粒度

封锁的对象可以是表、也可以是元组等,我们把封锁对象的大小称为封锁粒度(Granularity)。封锁的对象可以是逻辑单元(如表和元组等),也可以是物理单元(如数据页和数据块等)。数据库管理系统一般都具有多粒度锁定功能,允许一个事务锁定不同类型的资源。

62封锁粒度锁定在较小的粒度(例如行)可以增加并发操作的性能,但系统开销也较大。这是因为如果封锁的粒度小,则意味着需要的锁多,从而需要系统控制更多的锁。

锁定在较大的粒度(例如表)会降低操作的并发性,这是因为锁定整个表限制了其他事务对表中任意部分进行访问。封锁粒度大,则不需要太多的封锁,由于需要维护的锁较少,所以系统开销较低。

63死锁

产生死锁的原因

避免死锁

发现死锁解决死锁

64产生死锁的原因右图示意了两个并发事务所发生事件的序列,两个程序都为了等待对方释放数据资源而产生死锁。

65避免死锁

相同顺序法

所有的用户程序约定都按相同的顺序来封锁表一次封锁法

为了完成一个事务,一次性封锁所需要的全部表66避免死锁的封锁

67发现死锁超时法

即一个事务在等待的时间超过了规定的时限后就认为发生了死锁。这种方法非常不可靠,如果设置的等待时限长,则不能及时发现死锁;如果设置的等待时限短,则可能会将没有发生死锁的事务误判为死锁。

68解决死锁发现死锁后解决死锁的一般策略是:自动使“年轻”的事务(即完成工作量少的事务)先退回去,然后让“年老”的事务(即完成工作量多的事务)先执行,等“年老”的事务完成并释放封锁后,“年轻”的事务再重新执行。

69隔离级别在避免干扰的情况下,适当的降低隔离级别,从而提高并发的操作效率。隔离级别越低,并发操作效率越高,但是产生干扰的可能性也越大。隔离级别越高,并发操作效率越低,同时产生干扰的可能性也越小。在设计应用时,可以在所能容忍的干扰程度范围内,尽可能降低隔离级别,提高应用的执行效率。70隔离级别未提交读(READUNCOMMITTED):最低级别,仅可保证不读取物理损坏的数据,隔离级别最低。提交读(READCOMMITTED):可以保证不读取“脏”数据,大多数的数据库默认级别。可重复读(REPEATABLEREAD):可以保证读一致性,避免不一致分析问题,mysql默认级别。可串行化(SERIALIZABLE):事务隔离的最高级别,事务之间完全隔离,强制事务串行执行,不可并发执行。SQL支持4种隔离级别:71隔离级别查询可以用下列语句查询事务隔离级别:全局事务隔离级别(整个数据库)SELECT@@global.tx_isolation;

72隔离级别设置mysql引擎为:innodb的事务级别设置方法是:set+作用域+transactionisolationlevel+隔离级别如:用下列语句设置全局事务隔离级别:设置隔离级别为:未提交读setglobaltransactionisolationlevelREADUNCOMMITTED;

设置隔离级别为:提交读setglobaltransactionisolationlevelREADCOMMITTED;设置隔离级别为:可重复读setglobaltransactionisolationlevelREPEATABLEREAD;设置隔离级别为:可串行化setglobaltransactionisolationlevelSERIALIZABLE;73命令行-事务操作开启一个事务方式一starttransaction方式二begin提交一个事务方式一commit回滚一个事务方式一rollback74演示-建表CREATEDATABASEIFNOTEXISTSmydb;USEmydb;DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_id`int(10)unsignedNOTNULLauto_increment,`user_name`varchar(45)NOTNULL,`pass_word`varchar(45)NOTNULL,`email`varchar(45)NOTNULL,`phone`varchar(45)NOTNULL,`sex`char(1)NOTNULL,`score`int(10)unsignedNOTNULL,PRIMARYKEY(`user_id`))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8;INSERTINTO`user`(`user_id`,`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES(1,'LiMing','LiMing','LiMing@','83278904','0',60),(2,'ZhuangPing','ZhuangPing','ZhuangPing@','83278678','0',70),(3,'LuMei','LuMei','LuMei@','83278904','1',10),(4,'QiaoBing','QiaoBing','QiaoBing@','83278452','1',70),(5,'Kerry','Kerry','Kerry@','83278678','1',50),(6,'Jetty','Jetty','Jetty@','83278904','0',90),(7,'Lucy','Lucy','Lucy@','83278904','0',40),(8,'Honey','Honey','Honey@','83278904','1',80),(9,'Wendy','Wendy','Wendy@','83278452','0',50),(10,'Rose','Rose','Rose@','83278904','0',30);75事务隔离性:演示-1打开第一个mysql客户端,手动开户事务,进行一个update操作(未提交或回滚事务)76事务隔离性:演示-2打开第二个mysql客户端检索上面被更新的记录,可以看到数据还是之前未做update操作前的数据77事务隔离性:演示-3打开第三个mysql客户端,手动开户事务,对第一个浏览器update的那条记录,进行delete操作,可以看到,操作无法进行,事务处于等待中78事务隔离性:演示-4第一个mysql客户端,进行事务提交,则第二个mysql客户端,马上可以检索到update的数据,则第三个mysql客户端则可进行delete操作第一个客户端提交事务第二个客户端检索到新数据79脏读:演示-1设置mysql全局事务隔离级别为:未提交读setglobaltransactionisolationlevelREADUNCOMMITTED;80脏读:演示-2打开第一个mysql客户端,检索user_id=2的记录,可以看到email字段值为:ZhuangPing@81脏读:演示-3打开第二个mysql客户端,手动开户事务,进行一个update操作(未提交或回滚事务)82脏读:演示-4打开第三个mysql客户端,检索刚刚进行更新操作的记录,可以看到刚刚更新但还末提交事务的数据,已经被检索到。83脏读:演示-5回到第二个mysql客户端,回滚事务,则刚刚的update操作无效。84脏读:演示-6重新回到第三个mysql客户端,检索user_id=2的记录,可以看到数据已经变成末做update操作前的状态,第4步操作中读到的数据为脏数据。85不一致分析:演示-1要求:对积分字段(score=10)作两次校验:1、检查其值是否为2的倍数,2、检查其减去20后是否大于0(a-20>0)第一步:设置mysql全局事务隔离级别为:未提交读setglobaltransactionisolationlevelREADUNCOMMITTED;86不一致分析:演示-2打开第一个mysql客户端,开启一个事务,查询user_id=3的记录,事务未提交检索到score值为10,符合第1个条件87不一致分析:演示-3打开第二个mysql客户端,开启一个事务,把user_id=3的score值更新为25,并提交事务88不一致分析:演示-4重新回到第一个mysql客户端,第二次查询user_id=3的记录检索到score值为25,符合第2个条件,但实际数据不能同时满足以上两个条件虽然在同一个查询事务以内,前后两次检索到的值不却不一样,导致不一致分析89避免不一致分析:演示-5避免出现不一致分析的方法是把事务隔离级别提升到:可重复读setglobaltransactionisolationlevelREPEATABLEREAD;90避免不一致分析:演示-6回到第一个mysql客户端,重新开启一个事务,查询user_id=3的记录,事务未提交检索到score值为10,符合第1个条件91避免不一致分析:演示-7回到第二个mysql客户端,开启一个事务,把user_id=3的score值更新为25,并提交事务92不一致分析:演示-8再次回到第一个mysql客户端,第二次查询user_id=3的记录检索到score值还是为10,不符合第2个条件因此在同一个查询事务以内,前后两次检索到的值是一样的(不管其它事务做了什么操作),不会导致不一致分析93练习建立学生表、课程表、学生选课表学生表:学号、姓名、年级、专业、入学年份、已修学分等课程表:课程号、课程名、学分、课程性质(选修、必修)、课程归属专业等学生选课表:学号、课程号、成绩往以上数据表插入若干条记录在数据库命令行带事务(成功/失败)进行以下操作增加一个学生数据(同时往三张表写入对应数据)删除一个学生数据(同时删除三张表对应的此学生数据)修改某门课程的性质,同时修改此门课程对应的学生的成绩94高等职业技术院校教材《数据库高级应用技术》

主编:温立辉wenlihui2004@163.com95存储过程本单元教学目标了解存储过程的种类认识、了解存储过程的作用掌握存储过程的开发语法熟练使用数据库IDE开发、调试存储过程96

存储过程概念(一)SQL语句:SQL语句在执行的时候需要先编译,然后执行存储过程:存储过程(StoredProcedure)是一组为了完成特定功能的SQL语句集经编译后存储在数据库中用户通过指定存储过程的名字与参数(如果该存储过程带有参数)来调用执行它。97

存储过程概念(二)一个存储过程是一个可编程的函数它在数据库中创建并保存它由SQL语句和一些特殊的控制结构组成存储过程适用于以下场合在不同的平台(应用程序)上执行相同的函数封装特定功能是数据库编程中面向对象方法的模拟98存储过程优点(一)(1).存储过程增强了SQL语言的功能和灵活性存储过程可以用流控制语句编写,有很强的灵活性可以完成复杂的判断和较复杂的运算(2).存储过程是允许编程的标准组件被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响(3).存储过程能实现较快的执行速度如果某一操作包含大量的SQL代码或分别被多次执行,存储过程比批处理执行速度快很多,因存储过程是预编译的批处理的SQL语句在每次运行时要进行编译,速度相对慢99存储过程优点(二)(4).存储过程能过减少网络流量。当同一操作所涉及的众多SQL语句被编码到存储过程中在客户机上调用该存储过程时,网络中只需传送该调用语句从而大大减少了网络流量并降低了网络负载(5).存储过程被作为一种安全机制来充分利用系统管理员通过执行某一存储过程的权限进行限制能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,从而保证了数据的安全100存储过程的创建MySQL格式:CREATEPROCEDURE

存储过程名([过程参数[,...]])BEGIN…过程控制语句(Transaction-SQL)…END101存储过程案例-创建User表CREATEDATABASEIFNOTEXISTSmydb;USEmydb;DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_id`int(10)unsignedNOTNULLauto_increment,`user_name`varchar(45)NOTNULL,`pass_word`varchar(45)NOTNULL,`email`varchar(45)NOTNULL,`phone`varchar(45)NOTNULL,`sex`char(1)NOTNULL,`score`int(10)unsignedNOTNULL,PRIMARYKEY(`user_id`))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8;INSERTINTO`user`(`user_id`,`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES(1,'LiMing','LiMing','LiMing@','83278904','0',60),(2,'ZhuangPing','ZhuangPing','ZhuangPing@','83278678','0',70),(3,'LuMei','LuMei','LuMei@','83278904','1',80),(4,'QiaoBing','QiaoBing','QiaoBing@','83278452','1',70),(5,'Kerry','Kerry','Kerry@','83278678','1',50),(6,'Jetty','Jetty','Jetty@','83278904','0',90),(7,'Lucy','Lucy','Lucy@','83278904','0',40),(8,'Honey','Honey','Honey@','83278904','1',80),(9,'Wendy','Wendy','Wendy@','83278452','0',50),(10,'Rose','Rose','Rose@','83278904','0',30);102存储过程案例-开发编码DELIMITER//CREATEPROCEDUREproc1(insint)BEGINupdateusersetscore=swhereuser_idin(2,4,6,8,10);END//DELIMITER;103案例语句解释DELIMITER//表示把MySQL分隔符修改为:"//"MySQL默认以“;”为分隔符如果没有声明分割符,编译器会把存储过程中的”;”当成SQL语句结束分隔符进行处理,则存储过程的编译过程会报错;所以要事先用DELIMITER关键字声明当前段分隔符,这样MySQL才会将“;”当做存储过程中的代码,而不是语句MySQL中语句的结束分隔符DELIMITER;用完了之后要把分隔符还原为:";"存储过程根据需要可能会有输入、输出、输入输出参数本案例中有一个输入参数s,类型是int型如果有多个参数用","分割开过程体的开始与结束使用BEGIN与END进行标识。104存储过程调用调用格式如下:call+存储过程名+参数callproc1(100);调用后看到user表中user_id为:2、4、6、8、10的记录的score字段值变为了100删除存储过程格式:drop+PROCEDURE+存储过程名dropPROCEDUREproc1;105存储过程参数存储过程共有三种参数类型,IN,OUT,INOUT,形式如:CREATEPROCEDURE([[IN|OUT|INOUT]参数名数据类形...])IN输入参数:表示该参数值必须在调用存储过程时指定,在存储过程中修改该参数值将不被返回,为默认值OUT输出参数:该值可在存储过程内部被改变,并可返回INOUT输入输出参数:调用时指定,并且可被改变和返回106IN参数例子DELIMITER//CREATEPROCEDUREdemo_in_parameter(INp_idint,INp_score_addint)BEGINupdateusersetscore=(score+p_score_add)whereuser_id=p_id;END;//DELIMITER;107存储过程调用(IN参数)直接调用:CALLdemo_in_parameter(10,5000);通过预定义参数变量调用:先定义两个参数变量set@p_id=10;set@p_score_add=5000;再把变量作为参数调用CALLdemo_in_parameter(@p_id,@p_score_add);

调用后可看到user_id为10的score字段的值增加了5000108OUT参数例子DELIMITER//CREATEPROCEDUREdemo_out_parameter(OUTp_outint)BEGINSELECTCOUNT(*)INTOp_outFROMuser;END//DELIMITER;109存储过程调用(OUT参数)通过预定义输出参数变量调用:先定义输出参数变量set@p_out=0;再把变量作为参数调用CALLdemo_out_parameter(@p_out);

可以看到:调用前p_out的变量值为0调用后p_out的变量值为10110INOUT参数例子DELIMITER//CREATEPROCEDUREdemo_inout_parameter(INOUTp_inout_numint,INOUTp_inout_strvarchar(50))BEGINDECLAREidintdefault0;SETid=p_inout_num;UPDATEuserSETemail=p_inout_strWHEREuser_id=id;SELECTscoreINTOp_inout_numFROMuserWHEREuser_id=id;SELECTphoneINTOp_inout_strFROMuserWHEREuser_id=id;END//DELIMITER;111INOUT参数例子通过预定义输出参数变量调用:先定义输出参数变量set@p_inout_num=5;set@p_inout_str='Test@163.com';再把变量作为参数调用calldemo_inout_parameter(@p_inout_num,@p_inout_str);112INOUT参数例子(结果分析)调用前:p_inout_num的变量值为5

p_inout_str的变量值为‘Test@163.com’调用后:p_inout_num的变量值为50对应score字段值

p_inout_str的变量值为‘83278678’对应phone字段值113课堂练习1写一个存储过程,实现以下功能:把上面USRE表中积分(score)大于60的用户同步到一个VIP_USER表中insertintonew_table

(column1,column2)

selectcolumn1,column2fromold_table给积分60以下的女性会员(sex字段值为0),每人加5分返回以上操作以后男会员的平均分与女会员的平均分114变量mysql存储过程中,定义变量有两种方式:会话变量:也叫用户变量,使用set直接赋值,变量名以@开头例如:set@num=1;可以在一个客户端会话的任何地方声明,作用域是整个会话。存储过程变量:以DECLARE关键字声明的变量,只能在存储过程中使用,例如:DECLAREmynumINTDEFAULT0;

主要用在存储过程中,或者是给存储传参数中。两者的区别是:以DECLARE声明的变量都会被初始化为NULL而会话变量(即@开头的变量)则不会被再初始化在一个会话内,只须初始化一次会话断开后,会话变量也就消失115存储过程变量变量定义DECLARE+变量名+数据类型+[DEFAULTvalue];数据类型为MySQL的数据类型如:int,float,date,varchar(length)默认值:DEFAULTvalue,可有可无例如:DECLAREmy_intintdefault4000000;DECLAREmy_numericnumber(8,2)DEFAULT9.95;DECLAREmy_datedateDEFAULT'1999-12-31';DECLAREmy_datetimedatetimeDEFAULT'1999-12-3123:59:59';DECLAREmy_varcharvarchar(255)DEFAULT'Thiswillnotbepadded';

116存储过程变量变量赋值SET变量名=表达式值

例如:SETmy_int=100SETmy_numeric=11.02SETmy_date='2009-11-21'SETmy_datetime=‘2009-11-2120:50:50'SETmy_varchar='Hello'变量取值直接调用变量名即可取得变量值例如:INSERTINTOtable1VALUES(my_int

)上面的INSERT语句直接用前面定义的my_int变量名,即可取得变量值100117变量案例DELIMITER//CREATEPROCEDUREproc_declare_demo(INp_inINTEGER)BEGINDECLAREmystrCHAR(10);

IFp_in=17THENSETmystr='--birds--';ELSESETmystr='--beasts--';ENDIF;INSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES(mystr,mystr,'----@----','81234567','0',111);END//DELIMITER;118变量作用域变量的作用范围:往上回溯,从最靠近变量的第一个begin开始往下延伸,到最靠近变量的第一个end结束内部的变量比外部变量在其作用域范围内享有更高的优先权119变量作用域案例DELIMITER//CREATEPROCEDUREproc3()begindeclarex1varchar(5)default'outer';

begindeclarex1varchar(5)default'inner';

selectx1;

end;selectx1;

end;//DELIMITER;120课程练习2写一个存储过程,实现以下功能:给上面用户表(User表),增加一个备注字段(mark)ALTERTABLE`user`ADDCOLUMN`mark`VARCHAR(45)AFTER`score`用DECLARE定义几个存储过程变量变量1值为:“不活跃会员”变量2值为:“普通会员”变量3值为:“高级会员”根据上面定义的变量填充mark字段如果积分在60分以下,填充变量1的值如果积分在60-79分,填充变量2的值如果积分在80分以上,填充变量3的值返回以上操作以后每个等级会员的个数121注释MySQL存储过程可使用两种风格的注释双模杠:'--'+

空格该风格一般用于单行注释编程风格:/*…*/一般用于多行注释122注释样例123条件语句(if…then)if…then…elseif…then…else语句if后面跟判断条件elseif后面跟判断条件then后面跟条件分支语句块可以有多个elseif…then语句块,也可以没有else表示以上条件均不满足时会执行的语句块整个条件语句块的最后面以endif表示结束124条件语句案例(if…then)DELIMITER//CREATEPROCEDUREproc_if_else(INif_parameterint)begindeclarecondition_paraint;setcondition_para=if_parameter;if

condition_para=1

then

INSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES('if_user','if_user','if_user@','88888888','0',100);elseifcondition_para=2then

update`user`setscore=200whereuser_id<=5;elseifcondition_para=3then

update`user`setscore=400whereuser_id>5;elseupdate`user`setscore=1000;endif;end;//DELIMITER;125课程练习3写一个存储过程:如果输入参数值为1把user表数据同步到user_info表如果输入参数值为2删除user_info表数据如果输入参数值为3把user表数据同步到user_bak表如果输入参数值为4删除user_bak表数据如果输入参数值为5同时删除user_info、user_bak表数据user、user_info、user_bak三个表结构相同用if…then…elseif…then…else语句实现126SELECT中的IF条件语句IF表达式:IF(expr1,expr2,expr3)如果expr1是TRUE则IF()的返回值为expr2否则返回值则为expr3IF()的返回值为数字值或字符串值,具体情况视其所在语境而定selectif(sex=0,'女','男')fromuser;127条件语句(case…when)case…when…then…else语句case后面跟条件变量when后面跟条件值then后面跟条件分支语句块可以有多个when…then语句块,也可以只有有一个else表示以上条件均不满足时会执行的语句块整个条件语句块最后面以endcase表示结束128条件语句案例(case…when)DELIMITER//CREATEPROCEDUREproc_case_when(INcase_parameterint)begindeclarecondition_paraint;setcondition_para=case_parameter;case

condition_parawhen

1

then

INSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES('case_user','case_user','case_user@','7777777','0',200);when

2

then

update`user`setscore=300whereuser_id<=5;when

3

then

update`user`setscore=600whereuser_id>5;else

update`user`setscore=2000;endcase;end;//DELIMITER;129课程练习4用case…when…then…else语句改写课程练习3130SELECT中的CASE…WHEN语句格式:selectcasewhenA条件then字段1whenB条件then字段2else字段0endfromtbSELECTCASEWHENSEX=0THEN'女'WHENSEX=1THEN'男'ELSE'ERROR'ENDFROMUSER;131循环语句(while…do)while…do语句while后面跟条件表达式do后面跟条件分支语句块整个条件语句块最后面以endwhile表示结束132循环语句案例(while…do)DELIMITER//CREATEPROCEDUREproc_while_do()begindeclareaint;seta=0;while

a<100

do

INSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES('case_user','case_user','case_user@','7777777','0',a);seta=a+1;endwhile;end;//DELIMITER;

133课程练习5用when…do循环语句往User_Info表、User_Bak表各插入500条记录User_Info、User_Bak表与User表结构相同134循环语句(repeat…until)repeat…until语句until后面跟条件表达式repeat后面跟条件分支语句块整个条件语句块最后面以endrepeat表示结束先执行repeat操作后检查until条件语句,而while则是执行前进行检查。135循环语句案例(repeat…until)DELIMITER//CREATEPROCEDUREproc_repeat_until()begindeclarebint;setb=0;repeatINSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES('repeat_user','repeat_user','repeat_user@','11111111','0',b);setb=b+1;until

b>=200

endrepeat;end;//DELIMITER;

136课程练习6用repeat…until语句改写课程练习5137循环语句(loop…leave)loop…leave语句loop循环不需要初始条件loop循环格式:循环标识+:+loop如:LOOP_LABLE:loop

不需要结束条件,leave语句即表示跳出循环leave的格式是:leave+循环标识如:leaveLOOP_LABLE整个条件语句块最后面以endloop表示结束138循环语句案例(loop…leave)DELIMITER//CREATEPROCEDUREproc_loop()begindeclarecint;setc=0;LOOP_LABLE:loop

INSERTINTO`user`(`user_name`,`pass_word`,`email`,`phone`,`sex`,`score`)VALUES('loop_user','loop_user','loop_user@','22222222','0',c);setc=c+1;ifc>=400thenleaveLOOP_LABLE;endif;endloop;

end;//DELIMITER;139课程练习7用loop…leave语句改写课程练习5140游标(cursor)游标的作用及属性游标的作用是:对查询数据库所返回的记录进行遍历游标如下属性:游标是只读的,也就是不能更新它;游标是不能滚动的,也就是只能在一个方向上进行遍历,不能在记录之间随意进退,不能跳过某些记录;避免在已经打开游标的表上更新数据141如何使用游标首先用DECLARE语句声明一个游标格式:DECLARE+游标名+CURSORFOR+SELECT语句例如,声明一个叫mycoursor的游标:DECLAREmycoursorCURSORFORselectuser_name,passwordfromuser142如何使用游标其次需要使用OPEN语句来打开定义的游标格式:OPEN+游标名例如,打开一个叫mycoursor的游标:OPENmycoursor143如何使用游标接下来可以用FETCH语句来获得下一行数据游标将移动到对应的记录上类似java里面的iterator格式:FETCH+游标名+INTO+变量列表例如:把mycoursor游标的一行记录装进my_name、my_pwd变量FETCHmycoursorINTOmy_name,my_pwdmy_name、my_pwd两个变量需提前定义好144如何使用游标最后操作都结束后要把游标释放掉格式:CLOSE+游标名例如:关闭mycoursor游标CLOSEmycoursor145如何使用游标定义一个NOTFOUND条件处理函数游标到达记录的末尾时避免出现“nodatatofetch”这样的错误格式:DECLARECONTINUEHANDLERFORNOTFOUND+需要执行的语句例如:DECLARECONTINUEHANDLERFORNOTFOUND

SETno_more_products=1146游标使用案例-建表CREATEDATABASEIFNOTEXISTSmydb;USEmydb;DROPTABLEIFEXISTS`products`;CREATETABLE`products`(`id`int(10)unsignedNOTNULLauto_increment,`name`varchar(45)NOTNULL,`category`varchar(45)NOTNULL,`price`floatNOTNULL,`code`varchar(45)NOTNULL,`quantity`int(10)unsignedNOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8;INSERTINTO`products`(`id`,`name`,`category`,`price`,`code`,`quantity`)VALUES(1,'cake','dessert',10,'112011',323),(2,'cheese','dairy',12.5,'112010',55),(3,'cookie','dessert',50,'112012',323),(4,'carpet','furniture',100,'113010',323),(5,'couch','furniture',1250,'113011',15);147游标使用案例/*游标使用演示获取库存量小于100的产品的代码code*/DELIMITER$$DROPPROCEDUREIFEXISTS`CursorProc`$$CREATEPROCEDURE`CursorProc`()BEGINDECLAREno_more_products,quantity_in_stockINTDEFAULT0;DECLAREprd_codeVARCHAR(255);DECLAREcur_productCURSORFORSELECTcodeFROMproducts;/*First:Delcareacursor,首先这里对游标进行定义*/DECLARECONTINUEHANDLERFORNOTFOUNDSETno_more_products=1;/*when"notfound"occur,justcontinue,这个是个条件处理,针对NOTFOUND的条件*//*forloggginginformation创建个临时表格来保持*/CREATETEMPORARYTABLEinfologs(Idint(11)NOTNULLAUTO_INCREMENT,Msgvarchar(255)NOTNULL,PRIMARYKEY(Id));OPENcur_product;/*Second:Openthecursor接着使用OPEN打开游标*/FETCHcur_productINTOprd_code;/*Third:nowyoucanFetchtherow把第一行数据写入变量中,游标也随之指向了记录的第一行*/REPEATSELECTquantityINTOquantity_in_stockFROMproductsWHEREcode=prd_code;

IFquantity_in_stock<100THENINSERTINTOinfologs(msg)VALUES(prd_code);ENDIF;FETCHcur_productINTOprd_code;UNTILno_more_products=1ENDREPEAT;CLOSEcur_product;/*Finally:cursorneedbeclosed用完后记得用CLOSE把资源释放掉*/SELECT*FROMinfologs;DROPTABLEinfologs;END$$DELIMITER;148课程练习7改用游标功能实现课程练习1149基本函数-字符串类CHARSET(str)//返回字串字符集CONCAT(string2,string3,...)//连接字串INSTR(string,substring)//返回substring首次在string中出现的位置,不存在返回0LCASE(string2)//转换成小写UCASE(string2)//转换成大写LEFT(string2,length)//从string2中的左边起取length个字符RIGHT(string2,length)//从string2中的右边起取length个字符LENGTH(string)//string长度LTRIM(string2)//去除前端空格RTRIM(string2)//去除后端空格TRIM(string2)//去除前后端空格SPACE(count)//生成count个空格REPLACE(str,search_str,replace_str)//在str中用replace_str替换search_strSUBSTRING(str,position,length)//从str的position开始,取length个字符,position从1开始150基本函数-数学类ABS(number2)//绝对值BIN(decimal_number)//十进制转二进制CEILING(number2)//向上取整FLOOR(number2)//向下取整FORMAT(number,decimal_places)//保留小数位数LEAST(number,number2,…)//求最小值MOD(numerator,denominator)//求余SQRT(number2)//开平方RAND()//随机数ROUND(number)//四舍五入,保留到整数ROUND(number,decimals)//四舍五入,decimals为小数位数151基本函数-日期类CURRENT_DATE()//当前日期CURRENT_TIME()//当前时间CURRENT_TIMESTAMP()//当前时间戳NOW()//当前时间SEC_TO_TIME(seconds)//秒数转成时间(时、分、秒)MAKETIME(hour,minute,second)//生成时间串YEAR(datetime)//年份MONTH(datetime)//月DATE(datetime)//返回datetime的日期部分HOUR(datetime)//小时152存储过程函数演示字符串类数学类日期类153数学操作符154单元作业1用存储过程对商品销售明细表(order_detail)作如下统计操作:按月统计出7、8月份的销售金额,把数据汇总到月销售汇总表(order_month)按商品类型统计出各种类型商品销售金额,把数据汇总到类型销售汇总表(order_total_type)按月份及商品类型统计出7、8月份各种类型商品销售金额,把数据汇总到月类型销售汇总表(order_total_type_month)参考语句:insertintonew_table

(column1,column2)

selectcolumn1,column2fromold_table155建表脚本CREATEDATABASEIFNOTEXISTSmydb;USEmydb;DROPTABLEIFEXISTS`order_detail`;CREATETABLE`order_detail`(`order_id`int(10)unsignedNOTNULLauto_increment,`commodity`varchar(45)NOTNULL,`commodity_num`int(10)unsignedNOTNULL,`order_money`floatNOTNULL,`order_time`datetimeNOTNULL,`commodity_type`varchar(45)defaultNULL,PRIMARYKEY(`order_id`))ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=utf8;INSERTINTO`order_detail`(`order_id`,`commodity`,`commodity_num`,`order_money`,`order_time`,`commodity_type`)VALUES(1,'衬衣',9,900,'2015-07-0212:13:20','服装'),(2,'帽子',30,600,'2015-08-0712:13:40','服装'),(3,'裤子',8,640,'2015-08-1711:13:20','服装'),(4,'大米',100,350,'2015-07-2012:13:20','食品'),(5,'零食',50,200,'2015-08-1112:13:40','食品'),(6,'蔬菜',30,90,'2015-07-0912:12:30','食品'),(7,'面包',20,80,'2015-08-2512:13:40','食品'),(8,'风车',10,70,'2015-07-2112:13:

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论