数据库上课 第九讲 数据库开发

1、机械自动化学院机械自动化学院20152015主讲：主讲： 顾顾 曦曦 电话：电话：1569718107915697181079EmailEmail：主要内容基于数据库的开发1.视图2.存储过程和函数3.游标4.触发器22:472数据库的开发与应用 应用程序应用程序数据库访问数据库访问技术技术视图事件函数存储过程（函数）触发器触发器游标数据库数据库事务注意注意：不同的数据库，语法不尽相同标准标准SQL（非过程化的查询语言）22:473MySQL 参考手册http:/ 视图视图是虚表，是从一个或几个基本表(或视图)中导出的表。仅在系统的数据字典中存放视图的定义，不存放视图对应的数据。当基本表中的数

2、据发生变化时，从视图中查询出的数据也随之改变。视图实现了数据库管理系统三级模式中的外模式。基于视图的操作包括：查询、删除、受限更新和定义基于该视图的新视图。22:476视图的主要作用简化用户的操作；使用户能以多种角度看待同一数据；对重构数据库提供了一定程度的逻辑独立性；能够对机密数据提供安全保护；适当的利用视图可以更清晰的表达查询。主要方法使用CREATE VIEW或ALTER VIEW创建或更改视图。使用DROP VIEW销毁视图。使用SHOW CREATE VIEW 显示视图元数据。22:477使用视图的优点为用户集中数据为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在

3、多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。屏蔽数据库的复杂性屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。简化用户权限的管理简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。便于数据共享便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。可以重新组织数据以便输出到其他应用程序中。22:4781.2 定义视图CREATE VIEW ,.AS WITH CHECK OPTION22:479视图的名称，该名称在数据库中唯一列

4、名，可以省略不写，自动取查询出来的列名但是下列情况必须写：1.某个目标列是集函数或表达式；2.多表连接中有相同的列名；3.在视图中为某列取新的名称更合适。增删改操作增删改操作必须满足视图定义的谓词条件（子查询中的条件表达式）。子查询，不允许含有ORDER BY子句和DISTINCT短语例3.86创建仅包含1991年出生的学生视图StudentView1991。CREATE VIEW StudentView1991ASSELECT*FROMStudentWHEREyear(birthday)=1991l注意：省略了视图的列名，自动取查询出来的列名。l没有使用WITH CHECK OPTION选项

5、，下面的语句可以执行：INSERT INTO StudentView1991VALUES(0700008,李相东,男,1992-10-2100:00,云南,撒呢族,CS0701)例3.87创建仅包含1991年出生的学生视图StudentView1991Chk，并要求进行修改和插入操作时仍需保证该视图只有91年出生的学生。CREATE VIEW StudentView1991ChkASSELECT *FROMStudentWHEREyear(birthday)=1991WITH CHECK OPTION22:4711lWITH CHECK OPTION l其更新操作必须满足:修改操作：自动加上y

6、ear(birthday)=1991的条件；删除操作：自动加上year(birthday)=1991的条件；插入操作：自动检查birthday属性值是否满足为1991年出生，如果不是，则拒绝该插入操作。22:4712l例：下面的插入语句可以执行： INSERT INTO StudentView1991ChkVALUES(0700011,李相东,男,1991-10-2100:00,云南,撒呢族,CS0701)l下面的插入语句不可以执行： INSERT INTO StudentView1991ChkVALUES(0700009,李相东,男,1992-10-2100:00,云南,撒呢族,CS0701

7、)l插入的出生日期违反了出生日期必须为1991年。22:4713l当视图是基于一张表，且保留了主码属性，这样的视图称为行列子集视图。l视图可以建立在一张表上，也可以建立在多张表上。22:4714例3.88创建一个包含学生学号、姓名、课程名、获得的学分和相应成绩的视图ScoreView。l由于成绩必须大于等于60分才获得学分，该视图必须含有该条件。CREATE VIEW ScoreViewAS SELECT a.studentNo, studentName, courseName, creditHour, score FROM Student a, Course b, Score c WHERE

8、 a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND score=6022:4715结果（select * from scoreview）视图也可以建立在视图上22:4716在设计表结构时，为减少数据的冗余存放，往往仅存放基本数据凡是可以由基本数据导出的数据，在基本表中一般不存储；如在学生Student表中没有存放年龄，但可建立一个包含年龄属性的视图，这样的视图称为带表达式的视图。例3.91创建一个包含学生学号、姓名和年龄的视图CREATE VIEW StudentAgeView AS SELECT studentNo, studentN

9、ame, year(now()-year(birthday) age FROM Student22:47171.3 查询视图 从用户的角度来看，查询视图与查询基本表的方式是完全一样的。从系统的角度来看，查询视图的过程是：进行有效性检查，检查查询中涉及的表和视图是否存在；从数据字典中取出视图的定义，将视图定义的子查询与用户的查询结合起来，转换成等价的对基本表的查询；执行修正后的查询。22:4718例3.92在StudentView1991中查询CS0701班同学的信息。SELECT*FROMStudentView1991WHEREclassNo=CS070122:47191.系统首先进行有效性检

10、查，判断视图StudentView1991是否存在，如果存在，则从系统的数据字典中取出该视图的定义；2.将定义中的子查询与用户的查询结合起来，转换为基于表的查询：SELECT*FROM StudentWHEREyear(birthday)=1991ANDclassNo=CS0701然后系统执行修正后的查询。22:47201.4 更新视图更新视图指通过视图来插入、删除和修改基本表中的数据。视图不实际存放数据，对视图的更新，最终要转换为对基本表的更新。对视图进行更新操作，其限制条件比较多建立视图的作用不是利用视图来更新数据库中的数据，而是简化用户的查询简化用户的查询；达到一定程度的安全性保护；22

11、:4721如果视图包含下述结构中的任何一种，那么它就是不可更新的：（1）聚合函数；（2）DISTINCT关键字；（3）GROUPBY子句；（4）ORDERBY子句；（5）HAVING子句；（6）UNION运算符；（7）位于选择列表中的子查询；（8）FROM子句中包含多个表；（9）SELECT语句中引用了不可更新视图；（10）WHERE子句中的子查询，引用FROM子句中的表；（11）ALGORITHM选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）。22:4722尽量不要对视图执行更新操作。尽量不要对视图执行更新操作。例3.95在StudentView1991中，将学号为080

12、0004同学的名字修改为张小立。UPDATEStudentView1991SET studentName=张小立WHEREstudentNo=0800004对于该操作：系统首先进行有效性检查，判断视图StudentView1991是否存在，如果存在，则从系统的数据字典中取出该视图的定义；将定义中的子查询与用户的查询结合起来，转换为基于基本表的修改：22:47231.5 删除数据使用DELETE语句可以通过视图删除基本表的数据。语法：DELETE FROM 视图 WHERE 条件注意：对依赖于多个基本表的视图，不能使用DELETE语句。22:47241.6 删除视图 删除视图：DROP VIEW

13、CASCADEl其中，CASCADE为可选项，选择表示级联删除。l该语句从数据字典中删除指定的视图定义如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除；删除基本表时，由该基本表导出的所有视图定义都必须显式地使用DROPview语句删除。22:4725例3.100删除视图及级联视图。删除视图StudentView1991：DROP VIEW StudentView1991级联删除视图SourceView：DROP VIEW SourceVIEWCASCADE22:472622:47272.1 存储过程概述存储过程（StoredProcedure）

14、是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。由SQL语句语句和一些特殊的控制结构控制结构组成。用户通过指定存储过程的名字并给出参数指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。用于封装计算；存储过程存放在数据库中，且在服务器端服务器端运行；可以定义多个输入输出参数输入输出参数。22:4728存储过程的优点1.实现一定程度的安全性保护对存储过程只需授予执行权限，不需授予表或视图的操作权限。对于不允许用户直接操作的表或视图，可通过调用存储过程来间接地访问这些表或视图，达到一定程度的安全性；用户对存储过程只有执行权限，没有查看权限。拥有存储过程的执行权限，自动获取

15、了存储过程中对相应表或视图的操作权限；这些操作权限仅能通过执行存储过程来实现，一旦脱离存储过程，也就失去了相应操作权限。22:47292.特别适合统计和查询操作一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若在客户端实现，数据流量和网络通信量较大；很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储过程来实现，免去客户端的大量编程。3.减少网络通信量存储过程仅在服务器端执行，客户端只接收结果；由于存储过程与数据一般在一个服务器中，可减少大量的网络通信量。22:4730示例：helloWorld定义：定义：delimiter /Drop PROCEDURE if exists p

16、ro_helloworld/CREATE PROCEDURE pro_helloworld(in str varchar(40)begin SELECT str; end/delimiter ;执行：执行： Call pro_helloworld(helloWorld);22:4731改变命令结束符存储过程的语法创建存储过程执行存储过程修改和删除存储过程参考：http:/ 创建存储过程基本语法：CREATE PROCEDURE name (参数参数,.)特性特性 . routine_body22:4733 IN | OUT | INOUT 数据类型数据类型默认是IN。集合值输出参数不在存储过程

17、的参数中定义。函数体函数体流程控制流程控制IF语句CASE语句LOOP语句LEAVE语句ITERATE语句REPEAT语句WHILE语句BEGIN . END复合语句复合语句使用BEGIN . END复合语句来包含多个语句。变量变量声明声明DECLARE可以是局部变量，条件和 处理程序、游标变量变量赋值赋值SET var_name = expr , var_name = expr .SELECT . INTO语句语句把选定的列直接存储到变量。22:4734用户变量用户变量在MySQL客户端使用的变量: variable示例：示例：set y=Goodbye Cruel World;Call p

18、ro_helloworld(y);22:4735内部变量声明：DECLARE variable_name ,variable_name. datatype DEFAULT value;delimiter /Drop PROCEDURE if exists pro_helloworld/CREATE PROCEDURE pro_helloworld(in str varchar(40)begin DECLARE str2 varchar(40) DEFAULT Ill be back! ;select CONCAT(str, ,str2) as voice;end /delimiter ;22:

19、4736CONCAT()函数函数: 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。 定义内部变量str2，作用域为最近的begin和end之间调用存储过程set y=Goodbye Cruel World;Call pro_helloworld(y);22:4737例9.23mySQL版 输入某个同学的学号，统计该同学的平均分。delimiter /CREATE PROCEDURE proStudentByNo1(insnochar(7),outstnochar(7),out snamevarchar(20),out savgdecimal(5,1) SE

20、LECT a.studentNo,studentName,avg(score)intostno,sname,savgFROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDa.studentNo=sNoGROUP BY a.studentNo;/delimiter ;22:4738把取出的值存入out变量执行存储过程mysql call proStudentByNo1(0700001,no,name,avg);mysql select no,name,avg;+-+-+-+|no|name|avg|+-+-+-+|0700001|李小勇|78.7|+

21、-+-+-+22:4739一般在应用程序中调用定义out变量该变量在存储过程外部可调用2.4 修改存储过程（自学） 修改存储过程ALTER PROCEDURE | FUNCTIONsp_namecharacteristic.characteristic: CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY DEFINER | INVOKER | COMMENT string22:47402.5 删除存储过程（自学） 语法：DROP PROCEDURE 例9.31删除存储过程DROP PROCEDUR

22、E proStudentByNo122:47412.6 函数函数与存储过程类似。函数参数均为“in”，有一个返回值有一个返回值。存储过程无返回值，但可以定义多个out参数返回处理后的值。基本语法：CREATE FUNCTION name (参数,.)RETURNS type characteristic . routine_body函数的使用：funcName (x)22:4742例SET GLOBAL log_bin_trust_function_creators = 1;delimiter /CREATE FUNCTION hello (s CHAR(20) RETURNS CHAR(50

23、)/*do something*/RETURN CONCAT(Hello, ,s,!); /delimiter ;调用函数：调用函数：SELECT hello(world);22:4743允许创建函数| hello(world) | Hello, world! |22:47443.1 游标游标（cursor）用于指向SQL语句的执行结果语句的执行结果。游标可前后移动。用户可以对SELECT语句返回的结果值进行逐行处理，逐一从游标处获取记录，并赋给主变量，交由主语言进一步处理。可对游标的当前位置进行更新、查询和删除注意： 在mySQL中，游标仅在存储过程和函数内被支持。游标仅在存储过程和函数内被

24、支持。22:47453.2 使用游标的五个步骤声明游标：DECLARE打开游标：OPEN逐行提取游标集中的行：FETCH关闭游标：CLOSE释放游标：DEALLOCATE22:47461）声明）声明游标标DECLARE cursor_name CURSOR FOR select_statement：声明一个游标。也可以在子程序中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。注意：SELECT语句不能有INTO子句。2）OPEN cursor_name：打开先前声明的游标。3）FETCH cursor_name INTO var_name , var_name .用指定的打开游标读取下

25、一行（如果有下一行的话），并且前进游标指针。4）CLOSE cursor_name：关闭先前打开的游标。如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。3.3 获取当前游标值 定义变量：DECLARE变量类型FETCH INTO 执行一次该语句，系统将当前游标所指向的元组属性值放到变量中，然后游标自动下移一个元组。当前游标所指向元组的每个属性值必须分别用一个变量来接收，即变量个数、数据类型必须与定义游标中的SELECT子句所定义的属性(或表达式)个数、数据类型相一致一致。当游标移至尾部，不可再读取游标，必须关闭游标然后重新打开游标。 模板 CREATE PROCEDURE curde

26、mo(参数,.) /创建存储过程 BEGIN DECLARE a CHAR(16); . /声明变量DECLARE cur1 CURSOR FOR SELECT . FROM .; /声明游标OPEN cur1; /打开游标REPEAT /流程控制与循环FETCH cur1 INTO a; /获取当前游标值到变量aDOSomeThingEND REPEAT; CLOSE cur1; /关闭游标END 22:47504.1 触发器(TRIGGER)概述触发器是用户定义在关系表上的一类由事件驱动的存储过程，由服务器自动激活。触发器可进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力。触发器

27、是一种特殊的存储过程，不管什么原因造成的数据变化都能自动响应，对于每条SQL语句，触发器仅执行一次，事务可用于触发器中。触发程序是与表有关的，当表上出现特定事件时，将激活该对象。表必须引用永久性表。不能将触发程序与临时表或视图关联起来。22:4751触发器触发器仅在当前数据库中生成触发器有三种类型，即插入、删除和更新；插入、删除和更新可作为一种类型的触发器；查询操作不会产生触发动作，没有查询触发器类型。创建触发器的语法mySQL语法语法CREATE TRIGGER trigger_time trigger_event ON FOR EACH ROW SQLServer语法语法CREATE TRIGGER ON FOR