实验四数据库操作语言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存储过程可以按多种组合调用执行。
forbidden翻译成中文①CALL proc_FuzzySearchBook('%') --查询所有图书（如图4.36）
②CALL proc_FuzzySearchBook('%程序设计%') --查书名中含“程序设计”的图书（如图4.37）
③CALL proc_FuzzySearchBook('VB程序设计') --查询“VB程序设计”的图书（如图4.38）
图4.36 查询所有图书图4.37 查询书名中含“程序设计”的图书
mysql查看所有存储过程图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)<=yyearmicrosoft数据库
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关键字。
nodejs运行环境调用有参数的存储过程时，
注意括号里面要带参数。
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 触发器名
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论