实验四数据库操作语言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, ISBNFROM BookWHERE 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 totalfeeFROM `orders`WHERE CustomerCode=ccode AND year(OrderTime)<=yyearGroup 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．创建触发器和检验触发器执行效果。
解析：触发器定义在表上，是一个数据库对象。
当在表上发生某种影响表中数据的操作时将触发触发器的执行。