《数据库与信息系统》实验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()函数向导对话框，点击类型“过程”。