数据库原理及应用教程MySQL版-实验素材

1、数据库原理及应用教程（MySQL版）-实验素材实验任务一创立、查看、修改、删除MySQL数据库实验任务书1.实验目的掌握在MySQL中使用MySQL Workbench或者SQL语句创立数据库的方法。掌握在MySQL中使用MySQL Workbench或者SQL语句查看、修改、删除数据库的方法。 2.实验内容在MySQL中使用MySQL Workbench创立数据库，数据库的名称自定。（1）使用MySQL Workbench创立数据库，请给出重要步骤的截图。（2）使用MySQL Workbench查看数据库，请给出重要步骤的截图。（3）根据需要，使用MySQL Workbench修改数据库，请

2、给出重要步骤的截图。（4）使用MySQL Workbench删除数据库，请给出重要步骤的截图。在MySQL中使用SQL语句创立数据库，数据库的名称自定。（1）使用SQL语句创立数据库，请给出SQL代码。（2）使用SQL语句查看数据库，请给出SQL代码。（3）根据需要，使用SQL语句修改数据库，请给出SQL代码。实验任务二创立数据表实验任务书.实验目的掌握在MySQL中使用MySQL Workbench或者SQL语句创立数据表的方法（以SQL命令为 重点）。.实验内容给定如表1、表2和表3所示的信息。表1学生表学号姓名性别出生日期专业院系联系 0433张艳女2000-9-13计算机信息学院049

3、6李越女2001-1-23信息信息学院1381290 xxxx0529赵欣男2002-2-27信息信息学院1350222xxxx0531张志国女2002-10-10自动化工学院1331256xxxx0538于兰兰男2002-2-1数学理学院1331200XXXX0591王丽丽女2003-3-21计算机信息学院1332080XXXX0592王海强女2003-9-1数学理学院表2课程表(1)根据已经绘制的全局E-R图，通过E-R图到关系模式的转换方法，将全局E-R图课程号课程名学分数学时数任课教师K001计算机图形学2.540胡晶晶K002计算机应用基础348任泉转换为关系模式，并注明每个模式的主

4、码和外码。(2)使用数据规范化理论，分析转换后的模式属于第几范式。(3)运用模式合并方法，优化关系模式。(4)分析各关系模式预期数据增长情况，对潜在增长速度较快的关系模式提供模式分 解方案。实验任务十一 MySQL编程基础与函数实验任务书.实验目的熟练掌握使用流程控制语句完成简单程序的编写。掌握常用的系统函数的使用。掌握在MySQL中使用SQL语句完成自定义函数的创立、调用及管理工作。.实验内容根据实验2.1中表1所设计的数据库(teachingab)和其中的3个数据表(学生表s_lab 课程表cab和学生成绩表scab),完成如下实验内容，给出实验涉及的SQL语句和执行 结果截图。(1)编写

5、程序代码完成如下功能，如果学生表中有1988年出生的同学，那么把其学号、 姓名、性别、出生日期及所属的星期几查询出来，否那么输出“没有1988年出生的同学”。(2)通过MySQL编程，完成用户自定义函数创立、调用、查看和删除等管理工作。创立一个函数show_sign,函数的功能是，当向函数传递一个出生日期参数时，函数 返回其所属的星座名称；调用函数show_sign,显示每位同学的学号、姓名、出生日期和所属的星座；分别写出查看函数show_sign的代码定义和状态特征的语句；写出删除函数show_sign的语句。(3)通过MySQL编程，完成用户自定义函数的创立、调用。创立一个函数count_

6、number,任意向函数传递一个学号值，如果存在该同学，那么能计 算并返回该同学的选课门数，否那么，返回0;编写代码，调用函数counjnumber,显示每位同学的学号、姓名和选课门数。(4)通过MySQL编程，完成用户自定义函数的创立、调用。创立一个函数show_score,任意向函数传递一个学号值，如果存在该同学，那么能计算 并返回该同学已经取得的学分总数(课程的考试成绩=60分表示取得该课程的学分)，否那么, 返回0；编写代码，调用函数show_score,显示每位同学的学号、姓名和取得的学分数。 实验任务十二存储过程与游标实验任务书.实验目的掌握在MySQL中使用SQL语句定义和使用游

7、标。掌握在MySQL中使用SQL语句创立和执行用户自定义存储过程（以SQL命令为重点）。 掌握在MySQL中使用SQL语句完成存储过程的查看、修改、删除等管理任务。.实验内容根据实验2.1中表1所设计的数据库（teachingab）和其中的3个数据表（学生表s_lab 课程表cab和学生成绩表scab）,成如下实验内容，给出实验涉及的SQL语句和执行结 果截图。（1）通过MySQL编程，完成用户存储过程的创立、调用、查看和删除等管理工作。创立存储过程showstudents,显示出生年月在2002年7月前出生的性别为“男”的 学生所有信息、；编写程序代码，调用存储过程showstudents,

8、显示执行结果；写出相应语句，分别查看存储过程showstudents的代码定义和状态特征；修改存储过程showstudents,将其功能改为：显示出生年月在2002年7月前出生的 性别为“女”的学生信息。（2）通过MySQL编程，利用存储过程向数据表中添加记录。创立存储过程insertsc,实现向选课表中添加一条选课记录，记录内容由参数传递完 成，当提供的学号和课程号合法（即学号和课程号存在）且不存在该条选课记录时，那么向选 课表中插入该记录，插入完成后，显示选课表的内容，否那么，输出“学号或课程号不存在和 重复”的错误提示信息；编写程序代码，调用存储过程insertsc,显示执行结果；写出相

9、应语句，删除存储过程insertsc。（3）通过MySQL编程，完成游标的创立与使用。创立一个名称为showcursor的存储过程，在该存储过程中，创立一个名称为 shownum.cursor的游标，对应的结果集为课程号、课程名和选课人数，然后利用游标逐一 从结果集中取出每一条记录，并显示各字段的值。实验任务十三触发器和事件管理实验任务书.实验目的掌握在MySQL中使用MySQL Workbench或者SQL语句创立触发器完成复杂数据操作功能。掌握在MySQL中使用MySQL Workbench或者SQL语句创立事件完成复杂数据操作功能。.实验内容根据实验2.1中创立的学生成绩管理数据库以及其

10、表广表3所示的学生表、课程表和 学生成绩表，在MySQL中使用MySQL Workbench和SQL语句创立触发器完成复杂的数据操作 功能。.为了防止有人随意修改学生成绩，学校规定只能教务处（用户user2）才可以修 改学生成绩表中的数据，普通教师用户（userl）不能随意修改学生成绩，否那么输出“用 户没有权限:创立触发器完成上述成绩修改权限问题，具体步骤如下所示：（1）在数据库中创立两个用户“userl”和“user2”。（2）查看数据库中所有的用户，确认两名用户创立成功。（3）创立触发器（名字自拟，符合命名规那么即可）完成成绩修改权限限制功能。（4）分别使用用户“userl”和“user

11、2”修改学生成绩表中任意学生成绩信息，查看结 果。.学校规定，所有转入计算机专业的学生都需要重修课程“计算机应用基础（K002）”。 在MySQL中使用MySQL Workbench和SQL语句创立触发器完成如下操作：（1）创立触发器（名字自拟，符合命名规那么即可）完成以下功能：当往学生表中插入转 入学生信息时，需要向学生成绩表中插入该学生的“计算机应用基础（K002）”课程的成绩 信息，平时成绩和期末成绩的初始值均为NULL,平时成绩比重为0.3。（2）往学生表中插入以下学生信息：0593王志军男2003-9-2计算机信息学院1588524xxxx（3）查询学生成绩表中学号二“0593”的学

12、生的信息来验证触发器的功能。.当某个学生退学，需要把学生表中该学生的信息删除，为了减少数据库中的垃圾数 据，需要从学生成绩表中删除该同学相应的成绩信息。（1）创立触发器（名字自拟，符合命名规那么即可）实现以下功能：在删除学生信息之前， 把学生成绩表中该同学所有相关数据删除。（2）删除学生表中学号为“0593”的学生信息。（3）查询学生成绩表中学号为“0593”的学生的信息来验证触发器功能。.为了更好的了解学生的成绩状况，首先，创立如表4的统计成绩表，用来存放学生 的平均成绩和总成绩，其中，每门课成绩=平时成绩*平时成绩比重+期末成绩* （1-平时成绩比重）；总成绩二所有课程成绩的总和；平均成绩

13、二总成绩/课程数。表16.1统计成绩表在MySQL中使用MySQL Workbench或者SQL语句实现下述操作: （1）在数据库中创立统计成绩表。学号平均成绩总成绩统计时间char(lO)decimal(4,2)decimal(5,2)date（2）创立事件（名字自拟，符合命名规那么即可）完成以下功能：每个月统计一次学生 的平均分和总分并把结果插入统计成绩表中。（3）为了防止统计成绩表中的数据过多而影响操作效率，创立事件于今年年底删除统 计成绩表中所有的数据。.教务处想保存统计成绩表中的成绩数据，但是从现在到年底的数据还没有生成，因 此，需要阶段性的保存成绩信息。在MySQL中使用MySQL

14、 Workbench或者SQL语句实现下述 操作：(1)首先根据的统计成绩表创立一个表结构一样的副表。(2)创立事件实现以下功能：从现在开始到年底，把每个月的成绩数据都保存到副表中。实验任务十四 视图管理实验任务书.实验目的掌握在Python中连接MySQL数据库的方法。掌握使用Python操纵MySQL数据库数据的方法，包括插入、删除，修改数据。掌握使用Python查询MySQL数据库数据的方法。.实验内容根据实验2.1中创立的学生成绩管理数据库以及学生表、课程表和学生成绩表，在 Python中使用Python代码和SQL语句完成数据操作功能。按要求完成实验，并给出代码和 重要步骤截图：(1

15、)在Python代码中连接数据库teaching labo(2)使用Python往学生成绩表中插入以下两条数据。M00105312019-2-26 13:15:12770.4K00205912018-2-26 13:15:1282.50.486(3)把平时成绩和期末成绩为空的学生的成绩修改成0。(4)查询出所有学生的每门课程的总成绩(总成绩二平时成绩*平时成绩比重+考试成绩* (1-平时成绩比重)并输出。(5)查询期末成绩最高的同学的成绩信息并输出。(6)统计学生期末成绩低于所有学生总平均分的人数。(7)删除平时成绩和期末成绩都为0的数据，并输出删除的记录数。(8)查询胡老师所有教课程中考试成

16、绩最高的学生的信息。K006数据结构464马跃先M001政治经济学464孔繁新S001高等数学348赵晓尘表3学生成绩表(1)对表1、表2和表3,分别以下表的方式给出各字段的属性定义和说明。课程号学号选课时间平时成绩平时成绩 比重考试成绩K00104332017-8-2316:14:1190.50.493.5K00105292017-8-24 8:15:11850.390K00105312017-8-2510:18:34570.475K00105912017-8-2415:20:2481.50.471.5K00204962018-2-2515:20:240.3K00205292018-2-24

17、10:15:21700.483K00205312018-2-2513:20:19750.281.5K00205382018-2-2414:20:2470.50.473K00205922018-2-15 9:18:12850.4K00605312018-8-2519:17:25930.386K00605912018-8-2413:19:45850.482M00104962019-2-23 10:8:11830.391M00105912019-2-2512:14:1292.50.489S00105312019-8-2613:15:1282.50.477S00105382019-8-2715:10:

18、1275.20.3(2)使用SQL命令在学生作业管理数据库中建立学生表、课程表和学生成绩表，在实 验报告中给出SQL代码。字段名数据类型长度或者精度默认值完整性约束(3)在各个表中输入表1、表2和表3中的相应内容。实验任务三数据操纵实验任务书.实验目的掌握数据操纵的使用方法。.实验内容使用数据操纵完成以下任务(每一个任务都要给出SQL语句，并且列出查询结果)。(1)在学生表中添加一条学生记录，其中，学号为0593,姓名为张乐，性别为男，出生日 期为2000-06-10,专业为自动化，院系为信息学院。(2)将所有课程的学分数提高到平均学分的L 5倍。(3)删除张乐的信息。实验任务四数据查询实验任

19、务书.实验目的掌握无条件查询的使用方法；掌握条件查询的使用方法；掌握聚合函数查询的使用方法；掌握分组查询的使用方法；掌握查询的排序方法；掌握连接查询的使用方法；掌握子查询的使用方法；.实验内容根据实验任务一和实验任务二的实验中创立的学生成绩管理数据库以及其中的学生表、 课程表和学生成绩表，进行以下的查询操作(每一个查询都要给出SQL语句，列出查询结果)。(1)查询各位学生的学号、专业和姓名。(2)查询课程的全部信息。(3)查询数据库中有哪些专业。(4)查询学时数大于60的课程信息。(5)查询在2003年出生的学生的学号、姓名和出生日期。(6)查询姓张的学生的学号、姓名和专业。(7)查询没有考试

20、成绩的学号和课程号。(8)查询学号为“0538”的学生的平时成绩的总分。(9)查询选修了 K001课程的学生人数。(10)查询数据库中共有多少个专业。(11)查询选修三门以上(含三门)课程的学生的学号和考试平均分。(12)查询“于兰兰”的选课信息，列出学号、姓名、课程名。(13)查询与“张艳”同一班级的学生的学号和姓名。(14)查询比“计算机应用基础”学时多的课程的课程号、课程名和课时。(15)查询选修课程号为K002的学生的学号、姓名。实验任务五视图管理实验任务书.实验目的掌握在MySQL中使用MySQL Workbench或者SQL语句创立和查询视图的方法（以SQL 命令为重点）。掌握在M

21、ySQL中使用MySQL Workbench或者SQL语句查看、修改和删除视图的方法（以 SQL命令为重点）。.实验内容根据实验2.1中学生表、课程表和学生成绩表完成如下实验内容，给出实验涉及的SQL 语句和执行结果截图。在MySQL中使用MySQL Workbench或者SQL语句创立和查询视图（1 ）在MySQL Workbench或命令行环境下，创立计算机专业的学生视图 s_computer_view,视图包含学生表全部字段。（2）在MySQL Workbench或命令行环境下，查询s computer view中内容。（3）在MySQL Workbench或命令行环境下，创立2002年

22、（含）后出生的女学生视图 s_female_view,视图显示学生的学号（s_no）、姓名（s_name）、学生性别（s_sex）、出生日期 （s_birthday）以及学生的联系方式（s_contact）。（4）在MySQL Workbench或命令行环境下,通过视图s_female_view,查询张姓学生 的信息。（5）在MySQL Workbench或命令行环境下，创立学生选课信息视图sc_view,显示选 修3学分（含）学生学号（s_no）、姓名（s_name）、课程号（c_no）、课程名（c_name）。（6）在MySQL Workbench或命令行环境下，创立学生选课成绩视图sco

23、re_view,显示 选修3学分（含）学生学号（s_no）、姓名（s_name）、课程号（c_no）、课程名（c_name）、总成 绩（total_score）。其中，总成绩按照一一平时成绩*平时成绩比重+考试成绩*（1-平时成绩 比重）公式计算。（7）在MySQL Workbench或命令行环境下，创立学生成绩汇总视图score_group_view, 提供每位学生考试成绩最高的选课信息，要求显示的视图字段包括学生学号（s_no）、姓名 （s name） 课程号（c_no）、课程名（c name）、考试成绩（total_score）。其中，总成绩按照 平时成绩*平时成绩比重+考试成绩*（1-

24、平时成绩比重）公式计算。（8）在MySQL Workbench或命令行环境下，通过视图score_group view,按考试成绩 降序排列学生成绩汇总情况。MySQL中使用MySQL Workbench或者SQL语句修改和删除视图的方法（1）使用SQL语句，查看s_computer_view视图的结构信息和状态信息。（2）使用SQL语句，查看s_female_view视图的创立信息和元信息。（3）在MySQL Workbench或命令行环境下，替换视图s_female_view,替换后的视图 提供2003年（含）后出生的女学生信息，具体显示的字段包括学生的学号（s_no）、姓名(s_name

25、) 学生性别(s_sex)、出生日期(s_birthday)。(4)在MySQL Workbench或命令行环境下，修改视图sc_view,显示男同学选课情况， 具体显示的字段包括学生学号(s_no)、姓名(s_name),课程号(c_no)、课程名(c_name)、总 成绩(total_score)。其中，总成绩按照一一平时成绩*平时成绩比重+考试成绩*(1-平时成 绩比重)公式计算。(5)在MySQL Workbench或命令行环境下，删除视图s_female_view。实验任务六索引管理实验任务书.实验目的掌握在MySQL中使用MySQL Workbench或者SQL语句创立和使用索引的

26、方法(以SQL 命令为重点)。掌握在MySQL中使用MySQL Workbench或者SQL语句查看和删除索引的方法(以SQL 命令为重点)。.实验内容根据实验2.1中学生表、课程表和学生成绩表完成如下实验内容，给出实验涉及的SQL 语句和执行结果截图。在MySQL中使用MySQL Workbench或者SQL语句创立和使用索引的方法(1)在MySQL Workbench或命令行环境下，使用CREATE INDEX为课程表上课程名称添 加普通索引，索引名称自拟。(2)在MySQL Workbench或命令行环境下，使用EXPLAIN语句分析查询计算机图形学 课程信息时，索引的使用情况。(3)在

27、MySQL Workbench或命令行环境下，使用ALTER TABLE为学生表上学生姓名和 出生日期附加复合唯一索引，索引名称自拟。(4)在MySQL Workbench或命令行环境下，使用EXPLAIN语句，分析查询学生表时， 使用学生姓名为查询条件和使用出生日期为查询条件，在索引使用时的差异性。(5)在MySQL Workbench或命令行环境下，修改学生表，添加学生基本信息字段info, 为学生基本信息字段添加全文索引，索引名称自拟。MySQL中使用MySQL Workbench或者SQL语句查看和删除索引的方法(1)在MySQL Workbench或命令行环境下，查看学生表上索引情况

28、。(2)在MySQL Workbench或命令行环境下，删除学生表上姓名和出生日期的复合索引。 实验任务七用户平安性管理实验任务书.实验目的掌握在MySQL中使用MySQL Workbench或者SQL语句用户管理的方法(以SQL命令为重 点)。掌握在MySQL中使用MySQL Workbench或者SQL语句权限授予和回收方法(以SQL命令 为重点)。掌握在MySQL中使用SQL语句角色创立、分配和激活方法(以SQL命令为重点)。.实验内容根据实验2.1中数据库，给出实验涉及的SQL语句和执行结果截图。MySQL中使用MySQL Workbench或者SQL语句管理用户(1)在MySQL W

29、orkbench或命令行环境下，创立一个允许在任意主机登录的用户，用户 名为admin,密码为adminl23,使用sha策略加密。(2)使用SQL语句，创立一个允许在本地登录的用户，用户名为teacher,密码为 teacherl23,使用sha策略加密。使用SQL语句,同时创立一个允许在202204.111.111 IP位置登录的2个用户, 用户名1为student,密码为studentl23,用户2位stud6nt2,密码为studentl233,两个 用户均使用sha策略加密。(4)使用SQL语句，将用户名为student的用户密码修改为123studento(5)使用SQL语句，用查

30、询语句，查询系统表，查看已经创立的用户情况。(6)使用SQL语句，删除student2用户，并通过查询系统表，查看已经删除用户的情况。MySQL中使用MySQL Workbench或者SQL语句授予和回收权限(1)使用SQL语句，为admin用户授予全局全部权限，并允许权限转移。(2)使用MySQL Workbench,为teacher语句授予teaching_lab数据库中课程表和成绩 表的查找、修改数据权限，不允许权限转移。(3)使用SQL语句，为student用户授予teaching_lab中学生表s中出生日期和联系方 式的查询和修改权限。(4)使用SQL语句，查询系统数据表，查看为te

31、acher用户和student用户授权情况。(5)使用SQL语句，回收teacher用户的有关teaching_lab数据中课程表的修改数据权 限。MySQL中使用SQL语句创立、分配和激活角色方法(1)使用SQL语句，创立teach角色。(2)使用SQL语句，为teach角色授予teaching_lab数据库中课程表的平时分数和考试 成绩查询数据、修改数据的权限。(3)使用SQL语句,将teach角色分配给teacher用户。(4)使用SQL语句,激活teacher用户的teach角色。(5)使用SQL语句，查询teacher用户当前活跃的角色情况。(6)使用SQL语句,删除teach角色。

32、实验任务八MySQL备份和还原实验任务书.实验目的掌握使用命令进行MySQL数据库备份和还原的方法。掌握使用工具进行MySQL数据库备份和还原的方法。.实验内容在D盘新建一个用于存放备份文件的文件夹bako分别选用命令和至少一种工具将实验2.1中所创立的数据库完整备份到文件夹bak中， 给出命令和重要步骤的截图。使用命令将数据库中的学生成绩表备份到文件夹bak中，给出命令。删除实验2.1所创立的数据库。使用命令和至少一种工具还原数据库，给出命令和重要步骤的截图。实验任务九数据库概念结构设计.实验目的结合需求分析得到的数据字典，能够根据实际业务需求抽象出实体、实体的属性和实体 的联系及联系的属性

33、。根据E-R图的绘制规范，结合抽象的实体、属性和联系及联系属性，熟练运用E-R图绘 制描述局部业务数据关系的局部E-R图。根据全局E-R图合并方法，在局部E-R图基础上，能够合并、优化并形成描述整个系统 的全局（初步）E-R图。.实验内容某高校计划开发一套在线教学资源（视频、图片、PDF等）评价及提供系统，为学生在 第一课堂学习的基础上，提供优质的辅助教学资源，方便为学生浏览高评分优质课程，同时， 还可根据学生资源评分情况，提供其潜在感兴趣的资源。根据工程市场评估要求，需优先开发一套满足基本业务需求的教学资源评价、提供系统， 作为数据库设计人员，通过走访与跟班作业的方式，了解到目前该系统的主要

34、角色包括：（1）非注册用户可以浏览和搜索目前系统中评分较高的各门课课程资源，但并不能评价资源和收藏资源。 非注册用户经注册后，可转换为注册用户。（2）普通注册用户可以登录系统和对个人的基本信息（昵称、缩略图、邮箱、微信、手机、出生日期、性 别、个人简介、偏好专业门类）进行维护，也可在忘记密码时，通过手机重置密码。可以浏览、搜索、共享系统中各专业学科下的课程资源评分，并为感兴趣的资源提供学 习感受、评分或直接收藏。（3）系统管理员用户可以管理系统中专业分类情况。可以管理系统中各类资源及资源的评价信息。可以管理普通用户的基本信息并对用户状态进行调整（激活状态、密码重置状态、冻结状态、删除状态）。（

35、4）系统超级管理员用户具有系统管理员的全部功能。可手动生成管理员的登录信息和基本信息。对上述用户需要的业务按功能进行分类，梳理和整合后的系统功能如下：（1）用户管理功能用户注册：获取并存储用户输入的昵称、手机、密码、确认密码、性别、等必要登录信 息和缩略图、邮箱、微信、出生日期、个人简介、2个偏好专业门类等非必要个人信息。根 据注册信息，获取用户注册时间并在系统中构建普通注册用户。用户登录：获取并验证用户输入的用户名、密码、验证码等登录信息。当登录信息与系 统中存储和激活状态用户信息匹配时，记录用户登录时间和IP地址，允许用户登录并以注 册用户身份使用系统，否那么提示“用户名或密码”出错，重新

36、获取用户输入信息。普通用户密码重置：获取用户输入的注册手机号码，假设手机号码匹配，将重置密码状态 和时间存储在系统中，发送给用户手机，并在用户登录时,根据系统中存储的重置密码情况， 提示用户立即修改密码。假设手机号码不匹配，提示“手机号码不匹配”。普通用户登录密码修改：获取用户录入的原密码、新密码和确认新密码，当原密码与用 户现有密码匹配且新密码与确认密码一致时，修改用户密码，并记录用户密码修改时间。普通用户登录手机修改：获取用户提供的原手机号码和原手机号码接收的手机修改验证 码，假设验证码匹配，获取并存储修改后用户登录手机号码。用户个人信息维护：获取用户个人基本信息（除手机外）修改结果，将修改后的结果存 储在系统中。管理员用户生成：获取和存储超级管理员录入的系统管理员登录手机和密码信息。生成 系统管理员用户。普通注册用户列表：为管理员用户提供系统中普通注册用户列表。系统管理员用户列表：为系统超级管理员提供系统管理员用户列