MySQL数据库原理及应用课后实训题.docx

《MySQL数据库原理及应用课后实训题.docx》由会员分享，可在线阅读，更多相关《MySQL数据库原理及应用课后实训题.docx（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、MySQL数据库原理及应用课后实训题项目实训1 安装配置MySQL1 实训目的（1）掌握在Windows 环境下安装MySQL的方法。（2）掌握MySQL图形化管理工具的安装。（3）学会使用命令方式和图形化管理工具来连接和断开服务器的操作方法。 2 实训内容（1） 登录官方网站下载合适的版本，安装MySQL服务器。官方网站提供了图形化界面安装（任务1.2已介绍）和免安装两种安装包。这两种安装版的安装方式和配置方式均不同，图形化界面安装包有完整的安装向导。 （2） 配置并测试所安装的MySQL服务器。（3） 登录官方网站下载Navicat for MySQL软件。（4） 安装SQLyog软件。（

2、5） 安装AppServ软件包，了解AppServ软件的目录结构，分别测试Apache服务器、MySQL数据库、phpMyAdmin数据库管理工具安装是否正确。3 实训小结通过实训了解了在Windows操作系统上安装和配置MySQL数据库的方法，掌握了MySQL数据库、使用图形化方式安装MySQL数据库、配置MySQL数据库、启动MySQL服务和登录MySQL数据库等内容。 项目实训2 创建数据模型1. 实训目的(1)能设计网上书城的局部E-R图，并集成全局E-R图。(2)能运用关系数据模型的基本知识将概念模型转换为关系模型。(3)能应用关系规范化方法对关系模型进行规范化和优化。2. 实训内容

3、（1）图书借阅系统中，根据系统需求分析，得到以下实体：读者实体，属性有读者编号、姓名、读者类型和已借数量等。图书类型，属性有类型编号，类型名。图书实体，属性有图书编号、书名、作者、出版社、出版日期和定价等。图书存放信息实体，属性有条形码、图书编号、入库日期、图书状态等。读者和图书实体之间通过借阅建立联系，并派生出借期和还期属性。假定一位读者可以借阅多本图书，一本图书可以经多位读者借阅，一种图书分类可以有多本图书。(1) 根据以上需求分析设计其E-R图。(2) 根据项目需求标示出实体的主码。(3) 将E-R图转换为关系模式，并标示出实体的主键和外键。（2）有一个图书关系模式，分析图书关系中存在的

4、问题，该关系满足第几范式，以及该如何解决。表2-9 图书关系图书编号图书名分类名分类编号作者出版社TP39/1712JAVA设计计算机1张志成电子工业出版社TP55/85数据结构计算机1李明浩清华大学出版社S39/44机械制图机械3陈永红机械工业出版社TP39/21C+计算机1陈非凡邮电出版社W2/48鼠小说文学4葛永红清华大学出版社ZY1/41中医的故事医学6刘小龙互动出版社3. 实训小结能运用该章节的知识，绘制数据库的概念模型E-R图，并能运用关系数据模型的基本知识将E-R转换为关系模型，对关系模型能应用关系规范化方法进行规范化和优化，并根据项目需求进行主码设置和外码设置。希望读者通过练习

5、加深对E-R图和关系模型的理解，提高运用能力。项目实训3数据库和表的管理4 实训目的（1） 掌握创建数据库方法；（2） 掌握数据表创建、修改和删除方法。（3） 掌握对数据的增加、修改和删除方法。5 实训内容（1）创建数据库library。（2）在数据库library分别创建图书类别表（booktype）、图书信息表（book）、图书存储信息表（bookstorage）、读者类别表（readertype）、读者信息表（reader）和图书借阅表（bookborrow），其结构参照如下表3-14表3-19所示。（3）分别使用SQL语句修改表结构。1) 将读者表reader中的“读者编号（reade

6、rid）”列长度从10个字符改为12个字符。2) 在图书借阅表bookborrow表中最后加一列“罚金（fine）”，其数据类型为double类型。（4）向上述表中插入数据，数据如下表3-20表3-25所示。（5）修改记录。 1) 修改一条记录。在图书类别表booktype中修改编号为“9”的记录，把类别名称修改为“室内装修设计”。2) 修改多条记录。在图书信息表book中，把类别为“3”图书的价钱全部增加10元。（6）删除记录1) 删除一条记录。在图书借阅表bookborrow中删除读者编号为“0016”的记录。2) 删除多条记录。在图书信息表book中删除出版社“机械工业出版社”的记录。表

7、3-14 图书类别表（booktype）序号属性名称含义数据类型为空性约束1typeid类别编号intnot null主键2typename类别名称varchar(20)null表3-15 图书信息表（book）序号属性名称含义数据类型为空性约束1bookid图书编号char(10)not null主键2bookname图书名称varchar(20)not null3typeid类别编号intnull外键4bookauthor图书作者varchar(20)null5bookpublisher出版社varchar(50)null6bookprice图书价格doublenull7borrowsum

8、借阅次数intnull表3-16 图书存储信息表（bookstorage）序号属性名称含义数据类型为空性约束1bookbarcode图书条码char(20)not null主键2bookid图书编号char(10)not null外键3bookintime图书入馆时间datetimenull4bookstatus图书状态varchar(4)null表3-17 读者类别表（readertype）序号属性名称含义数据类型为空性约束1retypeid类别编号intnot null主键2typename类别名称varchar(20)not null3borrowquantity可借数量intnot n

9、ull4borrowday可借天数intnull表3-18 读者信息表（reader）序号属性名称含义数据类型为空性约束1readerid读者编号char(10)not null主键2readername读者姓名varchar(20)not null3readerpass读者密码varchar(20)not null4retypeid类别编号intnull外键5readerdate发证日期datetimenull6readerstatus借书证状态varchar(4)null表3-19 图书借阅表（bookborrow）序号属性名称含义数据类型为空性约束1borrowid借阅号char(10)

10、not null主键2bookbarcode图书条码char(20)not null外键3readerid读者编号char(10)not null外键4borrowtime借书日期datetimenull5returntime还书日期datetimenull6borrowstatus借阅状态varchar(4)null表3-20 图书类别表（booktype）数据typeidtypename1自然科学2数学3计算机4建筑水利5旅游地理6励志/自我实现7工业技术8基础医学9室内设计0人文景观表3-21 图书信息表（book）数据bookidbooknametypeidbookauthorbook

11、publisherbookpriceborrowsumTP39/1712JAVA程序设计3陈永红机械工业出版社35.530013452离散数学2张小新机械工业出版社45.510TP/3452JSP程序设计案例3刘城清电子工业出版社42.88TH/2345机械设计手册7黄明凡人民邮电出版社4010R/345677中医的故事8李奇德国防工业出版社20.05表3-22 图书存储信息表（bookstorage）数据bookbarcodebookidbookintimebookstatus132782TP39/17122009-8-10在馆132789TP39/17122009-8-10借出145234

12、0134522008-12-6借出145321TP/34522007-11-4借出156833TH/23452009-12-4借出345214R/3456772008-11-3在馆表3-23 读者类别表（readertype）数据retypeidtypenameborrowquantityborrowday1学生10302教师20603管理员15304职工1520表3-24 读者信息表（reader）数据readeridreadernamereaderpassretypeidreaderdatereaderstatus0016苏小东12345611999-8-9有效0017张明12345612

13、010-9-10有效0018梁君红12345612010-9-10有效0021赵清远12345622010-7-1有效0034李瑞清12345632009-8-3有效0042张明月12345641997-4-23有效表3-25 图书借阅表（bookborrow）数据borrowidbookbarcodereaderidborrowtimereturntimeborrowstatus00143213278200162011-3-42011-4-5已还00132813278900172011-1-242011-2-28已还00135614523400182011-2-122011-2-27已还00

14、143514532100212011-8-92011-9-2已还00157815683300342011-10-12011-11-1未还00167934521400422011-2-212011-3-5未还6 实训小结本项目主要介绍了MySQL中数据库中的各种操作，如创建表、添加各类约束、查看表结构，以及修改删除表和添加数据。读者应该掌握这些的操作，为以后的学习打下坚实的基础。项目实训4 数据的查询一、实训目的1掌握SELECT语句的基本用法。2掌握条件查询的基本用法。3掌握查询排序、分组基本用法。5掌握查询中聚合函数的使用。6掌握多表连接、子查询的基本用法。二、实训内容对library数据库

15、进行以下查询：（1）SELECT语句的基本使用。 查询book表的书号、书名和借出数量。 用别名查询book表的书号、书名和借出数量。（2）条件查询。 查询类型是“学生”的所有读者的信息。 查询借出时间在2011年3月1日和2011年10月1日之间的图书。查询借出时间在2011年3月1日之后并且还书时间在2011年10月1日之前的图书。用IN关键字查询类型是“老师”或“学生”的读者信息。查询书名包含“程序”的图书信息。（3）查询排序。 查询借出数量排名前3的图书。 按图书借出数量从高到低排序查询，如果借出数量相同，再按价格高低排序。 查找图书表中从第2条记录开始的5条记录的名称和价格。（4）分

16、组查询。 按读者类型分组查询借出图书的数量。 查询读者类型是“管理员”的读者借出图书的平均价格。（5）多表连接查询。 查询读者编号是“0021”的读者借书的信息，包括读者名、图书号、借出时间和归还时间。 查询所有读者的借书信息，包括读者名、图书名、借出时间和归还时间。（6）子查询。 查询借出数量大于书籍编号为“TP/3452”的借出数量的图书信息。 查询已借了图书的读者信息。三、实训小结数据库的查询是本课程最核心的内容，涉及到的知识点较多，查询的方法也很灵活。读者要多加练习，才能熟练掌握单表查询和不同方式的表的连接查询，为后续的数据库信息系统开发打下良好的基础。项目实训5 运算符与函数的操作1

17、. 实训目的（4） 掌握MySQL运算符的功能及使用方法；（5） 了解运算符的优先级的使用方法；（6） 掌握系统内置函数的功能及使用方法。（7） 了解常用的字符串函数的运用。2. 实训内容对library数据库进行以下查询：（1） 使用算术运算符“-”查询最高借阅量与最高借阅量的差值。 （2） 使用比较运算符“”查询book表中借阅量大于10的书籍信息。（3） 使用RIGHT()函数返回从字符串“loveMySQL”右边开始的5个字符。（4） 查询数据表bookborrow中2011年的借阅信息。（5） 使用CONCAT()函数连接两个字符串。3. 实训小结本章介绍了MySQL的运算符和系统内

18、置函数， 在MySQL中包括了4 类运算符，分别是算术运算符、比较运算符、逻辑运算符、位运算符。前3种运算符在实际操作中使用比较频繁，也是本项目的重点。系统内置函数通常与SELECT语句一起使用，用来方便用户的查询，同时INSERT、UPDATE、DELETE语句和条件表达式也可以使用这些函数。 项目实训6 创建存储过程一、实训目的1掌握存储过程的功能和作用。2掌握用户自定义存储过程的创建方法，包括不带参数的和带返回值参数的。3掌握存储过程的几种调用方法。二、实训内容对library数据库进行以下操作：1.变量的使用。（1）创建一个名为publisher的出版社变量，并在SELECT语句中使用

19、该局部变量查找Book表中的所有该出版社的图书。2.流程控制语句。（1）判断读者表Reader中是否存在编号为“0018”的读者，如果存在则显示该读者的信息，否则显示“没有此读者！”。（2）使用 CASE 表达式更改读者类型的显示：类型1、2、3、4的读者分别显示为学生、教师、管理员和职工。（3）现将图书馆的图书打折处理：循环将图书价格减 2，直到图书的最低价格小于或等于 5元，显示打折后的图书信息。3.在library数据库中创建一个带有参数的存储过程bookinfo，根据不同的图书编号查询该图书在图书馆共有几本图书上架，并执行该存储过程。4.在library数据库中创建一个带有参数的存储过

20、程readborrowed，根据读者编号查询该读者的借阅情况，并执行该存储过程。5.在library数据库中创建一个带有输出参数的存储过程sumbybooktype，根据图书类型编号查询该类型的图书共有多少本，并执行该存储过程。6. 修改图片表book的书籍类型，如果存在此图书类型则修改成功，提交事务，否则回滚事务。三、实训小结存储过程可以封装SQL代码，通过本章学习，能够创建存储过程实现一些复杂的业务逻辑，并能够正确调用存储过程，并且可以通过存储过程的输出参数输出结果。希望读者通过练习加深对存储过程的理解，提高运用能力。项目实训7 创建触发器1. 实训目的(1) 掌握触发器的功能和作用(2)

21、 掌握触发器的创建和管理方法。(3) 掌握触发器的工作原理和一些业务应用。2. 实训内容对library数据库进行以下操作：（1）在library数据库中，创建基于表bookstorage的触发器，当在bookstorage 中执行DELETE操作时，如果该书被借出则不允许删除此条记录。（2）在library数据库中，为表bookborrow中添加借阅信息记录时，得到该书的应还日期。（3）在library数据库中，当读者续借时，实际上要修改表bookborrow中相应记录还期列的值，请计算出是否过期，如果过期则不允许续借。3. 实训小结本章介绍了触发器的创建和管理方法，在使用触发器时需要注意，

22、对于相同的表，相同的事件只能创建一个触发器。触发器定义之后，每次执行触发事件，都会激活触发器并执行触发器中的语句。如果需求发生变化，而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句，从而会影响新数据的完整性。因此，要将不再使用的触发器及时删除。项目实训8 索引与视图的管理 一、 实训目的1、掌握创建索引方法。2、掌握创建视图方法。二、实训内容对library数据库进行以下操作：1、在library数据库中bookstorage表上创建索引IX_bstorage，按照图书条形码bookbarcode的降序、入库时间bookintime升序排列。 2、在library数据库，使用CR

23、EATE TABLE语句创建reader表，创建表的时候同时创建两个索引，在readerid字段上创建名为index_rid的唯一索引，并且以降序的形式排列；在readername和readerpass字段上创建名为index_user的多列索引。3.视图的使用（1）创建“自然科学”类书籍的视图v_bt_book。（2）向（1）中的视图插入一条“自然科学”类的书籍，并使用查询语句验证插入情况。（3）使用Check Option选项修改（1）中的视图。（4）向（3）中的视图插入另一条“自然科学”类的书籍，并观察插入结果。（5）将该视图进行改名为v_bt_book_1。（6）删除v_bt_book

24、_1视图。 三、实训小结本章重点讲述了索引和视图的创建、使用、修改和删除操作；索引可以提高数据查询的效率，同时维护索引需要大量的空间和时间消耗。在创建视图和修改视图后，一定要查看视图的结构，以确保创建和修改的操作是否正确。项目实训9 日常维护与管理7 实训目的（1）了解数据库备份与还原的原理。（2）掌握数据库备份与还原的方法。（3）了解数据导入和导出的目的。（4）掌握数据导入和导出的方法。8 实训内容对library数据库进行以下操作：（1） 使用mysqldump命令备份libarary数据库，生成的lib_bak.sql文件存储在D:backup。（2） 使用mysqldump命令备份li

25、barary数据库中的book表和reader表，生成的br_bak.sql文件存储在D:backup。（3） 将libarary删除，分别使用mysql命令和source命令将libarary数据库备份文件lib_bak.sql恢复到数据库中。（4） 将libarary数据库中的book表和reader表删除，分别使用mysql命令和source命令将备份文件br_bak.sql恢复到libarary数据库中。（5） 将libarary数据库中不同的数据表中的数据，导出到XML文件或者HMTL文件，并查看文件内容。9 实训小结为了保证数据的安全，需要定期对数据进行备份。备份的方式有很多种，效

26、果也不一样。mysqldump命令可以将数据库中的数据备份成一个文本文件。MySQL有一种简单的备份方法，就是将MySQL中的数据库文件直接复制出来。MySQL数据库中的表可以导出成文本文件、XML文件和HTML文件。项目实训10 用户和数据安全一、 实训目的1、掌握数据库用户账号的建立和删除方法；2、掌握数据库用户权限的授予方法。二、 实训内容 本节将创建一个名为Penny、Jenny两个用户，初始密码都设置为abcdef。用户对library数据库下的某些表拥有部分权限。本实例的执行步骤如下：1 创建Penny、Jenny用户2 将用户Jenny的名称修改为Jenny2。3 将用户Jenn

27、y2的密码修改为686868。4 删除Jenny2用户。5 授予用户Penny对library数据库的reader表进行插入、修改、删除操作权限。6 使用root用户回收Penny的user表上的DELETE权限。 7 使用root用户回收Penny的所有权限。三、 实训小结本项目介绍了添加用户的几种方法：GRANT语句、CREATE USER语句和直接操作user表。一般情况下，最好使用GRANT或者CREATE USER语句，而不要直接将用户信息插入user表，因为user表中存储了全局级别的权限以及其他的账户信息，如果意外破坏了user表中的记录，则可能会对MySQL服务器造成很大影响。请勿盗版 尊重作者