《数据库与信息系统》实验4指导解析_9-10

实验四数据库操作语言SQL

一．实验目的

?了解使用SQL中DDL语句创建数据库和表的方法

?熟练掌握使用SQL中DML语句对数据库进行查询、插入、修改和删除等操作的方法?掌握可编程对象视图和存储过程的创建与使用方法，了解触发器的创建方法

二．实验环境及素材

?MySQL和Navicat for MySQL

?bookstore数据库的脚本文件bookstore.sql

三．实验内容

首先创建一个bookstore数据库，执行bookstore.sql脚本文件实现表的创建及数据记录的添加。然后在bookstore数据库中完成以下题目（bookstore数据库设计说明参见10.1节）。

①在Navicat for MySQL中，创建数据库bookstore。

②右击数据库bookstore，选择“运行SQL文件…”，在对话框中浏览选择“bookstore.sql”文件，点击“开始”即可完成数据表定义及添加数据记录。

③在左栏连接窗口展开数据库“bookstore”/表，右击“表”选“刷新”菜单，即可看到恢复的各数据表。

9．创建和使用存储过程。

解析：存储过程是经过编译的SQL语句的集合。用户首先创建存储过程，然后在程序中调用该存储过程执行。创建存储过程可以在Navicat for MySQL中使用向导建立，也可以使用SQL的CREATE PROCDURE语句。存储过程可以接受参数、也可将查询信息通过输出参数返回调用者。

常用存储过程的语法格式：

CREATE PROCDURE 存储过程名( [形式参数列表])

SQL语句段

“形式参数列表”中多个参数之间用逗号分隔，如果没有参数，则()中为空。每个参数由输入输出类型、参数名和参数类型三部分组成，定义规则如下：

[IN|OUT|INOUT 参数名类型

输入输出类型中：IN是输入参数，即把数据传递给存储过程；OUT是输出参数，即从存储过程返回值；INOUT表示输入输出，即传入也能返回值。默认为IN类型；参数名必须符合标识符规则；参数类型可以是MySQL支持的任意数据类型。

存储过程创建后，可以通过CALL语句调用执行存储过程。语法格式如下：

CALL 存储过程名(

实参值|@变量)

其中：“实参值”是输入参数的值；“@

变量”表示用来保存参数或者返回参数的变量。多个参数可依次按以上参数定义规则列出，用逗号分隔。

（1）创建存储过程proc_SearchBook ，查询指定图书名称的图书信息，并调用查看结果。 解析：本题以图书名称作为存储过程的输入参数。

参考步骤（Navicat for MySQL 中使用向导创建存储过程）：

① 打开Navicat for MySQL ，展开数据库bookstore ，右击“函数”，选择“+新建函数”，出现创建f()函数向导对话框，点击类型“过程”。

② 输入存储过程参数。依次设置参数模式“IN ”、参数名“bkname ”和类型“varchar(50)”，如图4.34（a ）所示，点击“完成”,进入存储过程

SQL 代码输入窗口,如图4.34（b ）所示,在BEGIN 和END 之间输入SQL 语句：

SELECT * FROM book WHERE bookname=bkname; -- 按bkname 查询图书信息

③ 点击工具栏中的“”或“ ”按钮，在“过程名”对话框输入存储过程名称 “proc_SearchBook ”，点击“确定”按钮。在“bookstore/f()函数”下可看到该存储过程。 调用查看结果（在SQL 查询编辑窗口中调用）：

运行proc_SearchBook ，查询图书“西游记”的信息。点击“ ”按钮，在“参数”对话框中输入参数'西游记'（字符串需加单引号），点击“确定”按钮，执行结果如图4.35。或在查询

编辑窗口执行调用存储过程proc_SearchBook 的语句获取查询结果。 CALL proc_SearchBook ('西游记') 或者：SET @nbook='西游记';

CALL proc_SearchBook (@nbook);

（a ）存储过程参数设置 （b ）SQL 语句输入

图4.34 使用向导创建存储过程

调用有参数的

存储过程时，

注意括号里面

要带参数。

图4.35 proc_SearchBook的执行结果

该存储过程可直接运行下面SQL语句创建：

CREATE PROCEDURE proc_SearchBook (IN bkname varchar(50)) -- bkname 是IN类型参数SELECT * FROM book WHERE bookname=bkname; -- 按bkname查询图书信息

（2）创建存储过程proc_FuzzySearchBook，按不完整图书名称模糊查询图书信息，调用查看结果。

在SQL查询编辑窗口直接输入代码执行：

CREATE PROCEDURE proc_FuzzySearchBook(IN btname varchar(50))

SELECT BookName, Author, BookSort, ISBN

FROM Book

WHERE BookName LIKE btname;

调用查看结果（在SQL查询编辑窗口中调用）：

proc_FuzzySearchBook存储过程可以按多种组合调用执行。

①CALL proc_FuzzySearchBook('%') --查询所有图书（如图4.36）

②CALL proc_FuzzySearchBook('%程序设计%') --查书名中含“程序设计”的图书（如图4.37）

③CALL proc_FuzzySearchBook('VB程序设计') --查询“VB程序设计”的图书（如图4.38）

图4.36 查询所有图书图4.37 查询书名中含“程序设计”的图书

图4.38 查询“VB程序设计”的图书

（3）创建存储过程proc_SearchCustomerMoney，查询指定客户在某年之前的购书总金额（已知客户号和年份，输出总金额），并调用查看结果。

解析：本实验的存储过程不但有输入参数，还需要有输出参数来存放“总金额”值。

参考步骤（Navicat for MySQL中使用向导创建存储过程）：

①打开Navicat for MySQL，展开数据库bookstore，右击“函数”，选择“+新建函数”，出现创建f()函数向导对话框，点击类型“过程”。

②输入存储过程参数。输入参数ccode：选择参数模式IN、参数名”ccode”和类型int；点击“+”添加参数：yyear：选择参数模式IN、参数名”yyear”和类型int；再次点击“+”添加参数totalfee：选择参数模式OUT、参数名”totalfee”和类型decimal(8,2)；如图4.39（a）所示，点击“完成”,进入存储过程SQL代码输入窗口，如图4.39（b）所示，在BEGIN和END之间输入SQL语句：

SELECT sum(TotalPrice) INTO totalfee

FROM `orders`

WHERE CustomerCode=ccode AND year(OrderTime)<=yyear

Group by CustomerCode;

③点击工具栏中的“”或“”按钮，在“过程名”对话框输入存储过程名称

“proc_SearchCustomerMoney”，点击“确定”按钮。在“bookstore/f()函数”下可看到该存储

过程。

（a）存储过程参数设置（b）SQL语句输入

图4.39使用向导创建存储过程

调用查看结果（在SQL查询编辑窗口中调用）：

调用存储过程，结果如图4.40。

CALL proc_SearchCustomerMoney('2401', 2018, @total );

SELECT @total As 总金额;

图4.40查询“2401”客户2018年前的购书总金额

（4）创建存储过程proc_UpdateVIPClass，修改指定客户（CustomerCode）的VIPClass和

EvaluateDate，EvaluateDate取当前时间，调用查看结果。

解析：本存储过程的功能用于修改数据表CustomerEvaluation中的记录。

参考答案（使用CREATE PROCEDURE语句在查询命令窗口创建存储过程）：

注意out参数的

使用方法，使用

INTO关键字。

调用有参数的存储过程时，

注意括号里面要带参数。

CREATE PROCEDURE proc_UpdateVIPClass(IN ccode char(4),IN cclass char(1))

UPDATE CustomerEvaluation SET VIPClass=cclass, EvaluateDate=CURDATE()

WHERE CustomerCode=ccode;

调用查看结果：调用存储过程，可在调用的前后分别打开表或使用Select语句检查客户等级。如图4.41所示。

CALL proc_UpdateVIPClass('2401', 'A');

Select * From CustomerEvaluation Where CustomerCode='2401';

图4.41 修改“2401”客户的等级和评价时间

（5）创建存储过程proc_InsertOrderDetail，向orderdetail表中添加一条记录，调用查看结果。参考答案（使用CREATE PROCEDURE语句在查询命令窗口创建存储过程）：

CREATE PROCEDURE proc_InsertOrderDetail(IN ordcode char(8), IN bookcode char(4), IN count int)

INSERT INTO OrderDetail VALUES(ordcode,bookcode,count);

验证步骤（在SQL查询编辑窗口中验证）：

调用存储过程，可在调用的前后分别打开表或使用Select语句检查客户等级。如图4.42所示。

CALL proc_InsertOrderDetail('10120701', '0202', 5);

Select * From OrderDetail Where BookCode='0202';

图4.42 调用存储过程插入一条记录

10．创建触发器和检验触发器执行效果。

解析：触发器定义在表上，是一个数据库对象。当在表上发生某种影响表中数据的操作时将触发触发器的执行。在表上可以定义的触发器分三类：更新触发器、插入触发器和删除触发器。当表上发生更新、插入和删除操作时，相应的触发器将会执行。触发器不能被直接调用，也不能传递参数。创建触发器可以在Navicat for MySQL中进行，也可以使用SQL的CREATE TRIGGER语句。

创建触发器的SQL语句的语法格式如下：

CREATE TRIGGER 触发器名

{BEFORE|AFTER} {INSERT|UPDATE|DELETE}

ON 表名FOR EACH ROW

SQL语句块

其中：

①BEFORE|AFTER：说明何时触发。在触发事件操作之前还是之后执行触发器。

②INSERT|UPDATE|DELETE：说明被谁触发，即激活触发器操作类型。

③FOR EACH ROW：说明触发范围是行级触发，即任一记录上的操作相关操作都能触发。

④SQL语句块：说明触发后后执行的语句。

SQL语句块中经常需要用到数据表更新前或更新后的数据，触发器用两个特殊的对象new 和old（也可看作是两个临时表）来存放记录数据，某列的值用new.列名或old.列名表示。

①INSERT触发：new代表新插入的记录，old 不可用。

②DELETE触发：old代表被删除的记录，new不可用。

③UPDATE触发：old代表更新前的记录。new代表更新后的记录。

注意：触发器只能建立在表上，视图和临时表都不支持触发器。每个触发器只能和一个触发事件相关，每个事件只支持一个触发器。

（1）创建触发器tri_OrderDetailInsertUpdate，当在orderdetail表中插入或修改订单时，计算orders 表中相关订单的TotalPrice。

解析：本题在OrderDetail表上创建触发器tri_OrderDetailInsertUpdate，当在OrderDetail表中修改记录时，将会触发该触发器的执行，修改Orders表中的数据。

参考步骤（在Navicat for MySQL中创建触发器）：

①在Navicat for MySQL窗口，右击OrderDetail表，选择“设计表”命令。

②在表设计窗口（如图4.43所示），选“触发器”选项卡。在列“名”处输入触发器名“tri_OrderDetailInsertUpdate”，在“触发”下拉列表中选“After”，选中“更新”复选框。在“定义”窗格输入SQL语句，点击工具栏中的“”按钮，即完成触发器的创建。

图4.43 触发器设计窗口

验证步骤（在SQL查询编辑窗口中验证）：

当向Orderdetail中修改其中的一条记录时，自动计算Orderdetail中与修改记录订单号相同的记录的所有订书总价，并修改Orders表中该订单的总价。

①使用查询语句查看数据。如图4.44（a）所示。

Select * From `orders` Where OrderCode='08110801';

②执行修改操作

Update Orderdetail set Amount=6 where OrderCode='08110801' and Bookcode='0202';

③使用查询语句查看数据。如图4.44（b）所示。

Select * From `orders` Where OrderCode='08110801'

图4.44 (a)修改Orderdetail中订单号'08110801'记录前orders表中该记录信息

图4.44 (b)修改Orderdetail中订单号'08110801'记录后orders表中该记录信息

（2）创建触发器tri_CustomerEvaluationUpdate，当customerevaluation表发生更新操作时，以当前时间修改被更新记录的EvaluateDate。

参考答案（在SQL查询编辑窗口使用CREATE TRIGGER语句创建触发器）：

CREATE TRIGGER tri_CustomerEvaluationInsert

BEFORE UPDATE ON CustomerEvaluation

验证步骤（在SQL 查询编辑窗口中验证）：

① 执行UPDATE 操作

UPDATE customerevaluation SET VIPClass='B' WHERE CustomerCode='4002';

③ 使用查询语句查看修改后的数据，注意观察EvaluateDate 的值。

Select * From CustomerEvaluation WHERE CustomerCode='4002'

注意：如果将创建触发器代码描述如下：

'customerevaluation' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.”，原因是造成了循环的调用，所以应该使用SET 操作，而不是在触发器里使用UPDATE 操作。

（3）创建触发器tri_BookOrderDel ，当从orders 表中删除记录时，检查订单状态：如果不是“待处理”状态，则订单不能被删除，否则先删除orderdetail 表中的相关数据。

解析：按照触发器被触发和数据更新的时间先后，可把DML 触发器分为AFTER 触发器（在触发事件之后执行触发语句）和BEFOR 触发器（在触发事件之前执行触发语句）。本题目需要更新前检查，因此可使用BEFOR 触发器。

参考答案一（使用INSTEAD OF 触发器）：

CREATE TRIGGER tri_BookOrderDel

BEFORE DELETE

ON `orders` FOR EACH ROW

IF old.OrderStatus='待处理' THEN

Delete From OrderDetail Where OrderCode=old.ordercode;

END IF 验证步骤(在SQL 查询编辑窗口中验证)：

① 当删除“待处理”的订单时，触发器被触发，将在删除OrderDetail 表中的相关数据后，删除Order 表中的记录。

典型错误old表是一个临时表，存储被删掉的记录（针对DELETE动作的触发器）。

使用Select语句查看订单号为“10120701”的相关数据记录在Order表和OrderDetail表中，该订单的的状态为“待处理”:

Select * From `orders` Where OrderCode='10120701';

Select * From orderdetail Where OrderCode='10120701';

在查询编辑窗口输入命令：

DELETE FROM `orders` WHERE OrderCode='10120701';

再次使用Select语句可查看到相关数据记录已从Order表和OrderDetail表中删除：

Select * From `orders` Where OrderCode='10120701';

Select * From orderdetail Where OrderCode='10120701';

②当删除“结单”或“已发货”的订单时，触发器被触发，但Order表和OrderDetail表中的相关记录不被删除。