《数据库原理及应用实验》.doc

1、数据库原理与应用实验实验报告册学年 第 学期学 院：专 业：年 级：姓 名：学 号：任课教师：学院 班级 学号 姓名 2MySQL+Navicat 安装步骤与下载地址 百度地址：https:/ 配置与简单使用 百度地址：https:/ 学院 班级 学号 姓名 3实验一 创建和维护数据库一、实验目的（1）掌握在 Windows 平台下安装与配置 MySQL 5.5 的方法。（2）掌握启动服务并登录 MySQL 5.5 数据库的方法和步骤。（3）掌握 MySQL 数据库的相关概念。（4）掌握使用 Navicat 工具和 SQL 语句创建数据库的方法。（5）掌握使用 Navicat 工具和 SQL

2、语句删除数据库的方法。二、实验要求（1）学生提前准备好实验报告，预习并熟悉实验步骤；（2）遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤（1）在 Windows 平台下安装与配置 MySQL 5.5.36 版。（2）在服务对话框中，手动启动或者关闭 MySQL 服务。（3）使用 Net 命令启动或关闭 MySQL 服务。（4）分别用 Navicat 工具和命令行方式登录 MySQL。（5）在 my.ini 文件中将数据库的存储位置改为 D:MYSQLDATA。（6）创建数据库。 使用 Navicat 创建学生信息管理数据库 gradem。 使用 SQL 语句创建数据库 MyD

3、B。（7）删除数据库。 使用 Navicat 图形工具删除 gradem 数据库。 使用 SQL 语句删除 MyDB 数据库。四、思考题学院 班级 学号 姓名 4常见的数据库产品有哪些？五、实验总结1、收获2、存在的问题学院 班级 学号 姓名 5实验二 管理表一、实验目的(1) 掌握表的基础知识。(2) 掌握使用 Navicat 管理工具和 SQL 语句创建表的方法。(3) 掌握表的修改、查看、删除等基本操作方法。二、实验要求（1）学生提前准备好实验报告，预习并熟悉实验步骤；（2）遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤(1)在 gradem 数据库中创建表 2.1表

4、2.5 所示结构的表。表 2.1 student 表的表结构字段名称 数据类型 长度 小数位数 是否允许 NULL 值 说明sno char 10 否 主码sname varchar 8 是ssex char 2 是sbirthday datetime 是saddress varchar 50 是sdept char 16 是speciality varchar 20 是表 2.2 course 表( 课程名称表) 的表结构字段名称 数据类型 长度 小数位数 是否允许 NULL 值 说明cno char 5 否 主码cname varchar 20 否表 2.3 sc 表( 成绩表)的表结构字

5、段名称 数据类型 长度 小数位数 是否允许 NULL 值 说明sno char 10 否组合主码、外码cno char 5 否组合主码、外码degree decimal 4 1 是 1100表 2.4 teacher 表(教师表)的表结构字段名称 数据类型 长度 小数位数 是否允许 NULL 值 说明tno char 3 否 主码学院 班级 学号 姓名 6tname varchar 8 是tsex char 2 是tbirthday date 是tdept char 16 是表 2.5 teaching 表(授课表)的表结构字段名称 数据类型 长度 小数位数 是否允许 NULL 值 说明cno

6、 char 5 否 组合主码、外码tno char 3 否 组合主码、外码cterm tinyint 1 0 是 110(2) 向表 2.1 至表 2.5 输入数据记录，见表 2.6表 2.10。表 2.6 学生关系表 studentsno sname ssex sbirthday saddress sdept speciality20050101 李勇 男 1987-01-12 山东济南 计算机工程系 计算机应用20050201 刘晨 女 1988-06-04 山东青岛 信息工程系 电子商务20050301 王敏 女 1989-12-23 江苏苏州 数学系 数学20050202 张立 男 1

7、988-08-25 河北唐山 信息工程系 电子商务表 2.7 课程关系表 coursecno cname cno cnameC01 数据库 C03 信息系统C02 数学 C04 操作系统表 2.8 成绩表 scsno cno degree20050101 C01 9220050101 C02 8520050101 C03 8820050201 C02 9020050201 C03 80表 2.9 教师表 teachertno tname tsex tbirthday tdept101 李新 男 1977-01-12 计算机工程系102 钱军 女 1968-06-04 计算机工程系201 王小花

8、 女 1979-12-23 信息工程系202 张小青 男 1968-08-25 信息工程系学院 班级 学号 姓名 7表 2.10 授课表 teachingcno tno ctermC01 101 2C02 102 1C03 201 3C04 202 4(3)在 navicat 下修改表结构。 向 student 表中增加“入学时间”列，其数据类型为日期时间型。 将 student 表中的 sdept 字段长度改为 20。 将 student 表中的 speciality 字段删除。 删除 student 表。(4) 利用 SQL 命令（ create table、alter table、dro

9、p table）完成对表的操作利用 create talbe 命令完成 student 表和 course 表的定义。利用 alter table、drop table 命令实现（3）中的所有任务。学院 班级 学号 姓名 8四、思考题(1) 在定义基本表语句时，NOT NULL 参数的作用是什么？(2) 主码可以建立在“值可以为 NULL”的列上吗？五、实验总结：1、收获2、存在的问题学院 班级 学号 姓名 9实验三 简单查询单表无条件和有条件查询一、实验目的(1) 掌握 SELECT 语句的基本用法。(2) 使用 WHERE 子句进行有条件的查询。(3) 掌握使用 IN 和 NOT IN，

10、BETWEENAND 和 NOT BETWEENAND 来缩小查询范围的方法。(4) 利用 LIKE 子句实现字符串匹配查询。二、实验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤在上次实验建立的 Gradem 或 Gradem1 数据库中完成下面查询：(1) 查询所有学生的基本信息、所有课程的基本信息和所有学生的成绩信息（用三条 SQL 语句） 。SELECT *FROM student;SELECT *FROM course;SELECT *FROM sc;(2) 查询所有学生的学号、姓名、性别和出生日期。SELEC

11、T sno,sname,ssex,sbirthdayFROM student;(3) 查询所有课程的课程名称。SELECT cnameFROM course(4) 查询前 10 门课程的课号及课程名称。SELECT cno,cnameFROM courseWHERE cno18(7) 查询所有男生的信息。SELECT *FROM student,teacherWHERE ssex=男and tsex=男(8) 查询所有任课教师的姓名（Tname ）和所在系别（Tdept ） 。SELECT tname,tdeptFROM teacher(9) 查询“电子商务”专业的学生姓名、性别和出生日期。S

12、ELECT sname,ssex,sbirthdayFROM studentWHERE sdept=电子商务(10) 查询 Student 表中的所有系名。SELECT sdeptFROM student(11) 查询“C01”课程的开课学期。SELECT ctermFROM teachingWHERE cno=c01(12) 查询成绩在 8090 分之间的学生学号及课号。SELECT sno,cnoFROM scWHERE degree BETWEEN 80 and 90学院 班级 学号 姓名 11(13) 查询在 1970 年 1 月 1 日之前出生的男教师信息。SELECT *FROM

13、teacherWHERE tbirthday3(8) 查询成绩不及格的学生学号及课号，并按成绩降序排列。SELECT sno,cnoFROM scWHERE degree1(10) 统计输出各系学生的人数SELECT sdept ,COUNT(*)FROM studentGROUP BY sdept学院 班级 学号 姓名 15(11) 统计各系的男、女生人数。 （两条命令）Select sdept,ssex,count(*)From studentGroup by sdept ,ssex(12) 统计籍贯的男、女生人数。 （两条命令）Select saddress,ssex,count(*)F

14、rom studentGroup by saddress ,ssex(13) 统计各系的老师人数，并按人数升序排序。SELECT tdept ,COUNT(*)FROM teacherGROUP BY tdeptORDER BY COUNT(*) DESC(14) 统计不及格人数超过 10 人的课程号。SELECT cnoFROM scGROUP BY cnoHAVING COUNT(degree10(15)统计选修人数超过 10 人的课程号。SELECT cnoFROM scGROUP BY cnoHAVING COUNT(*)10(16) 查询软件系的男生信息，查询结果按出生日期升序排序，

15、出生日期相同的按地址降序排序。SELECT *学院 班级 学号 姓名 16FROM studentWHERE ssex=男 and sdept= 信息工程系ORDER BY sbirthday,saddress DESC四、思考题(1) 聚集函数能否直接使用在 SELECT 子句、HAVING 子句、WHERE 子句、GROUP BY 子句中？(2) WHERE 子句与 HAVING 子句有何不同？五、实验总结：1、收获2、存在的问题学院 班级 学号 姓名 17实验五 多表查询一、实验目的(1) 掌握 SELECT 语句在多表查询中的应用。(2) 掌握多表连接的几种连接方式及应用。二、实验要求

16、1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤：在 Gradem 或 Gradem1 数据库中完成下面查询：(1) 查询计算机工程系女学生的学生学号、姓名及考试成绩。SELECT student.sno,sname,sc.degreeFROM student,scWHERE student.sno=sc.sno and student.sdept=计算机工程系 and student.ssex= 女;(2) 查询“李勇”同学所选课程的成绩。( 不考虑重名)SELECT sc.degreeFROM student,scWHER

17、E student.sno=sc.sno and student.sname=李勇(3) 查询“李新”老师所授课程的课程名称。SELECT ameFROM teacher,teaching,courseWHERE teacher.tno=teaching.tno and o=o and teacher.tname=李新(4) 查询女教师所授课程的课程号及课程名称。SELECT ame,oFROM teacher,teaching,courseWHERE teacher.tno=teaching.tno and o=o and teacher.tsex=女(5) 查询至少选修一门课程的女学生姓名

18、。SELECT student.snameFROM student,scWHERE student.sno=sc.sno AND student.ssex=女GROUP BY student.snoHAVING COUNT(o)1学院 班级 学号 姓名 18(6) 查询姓“王”的学生所学的课程名称。SELECT ameFROM student,sc,courseWHERE student.sno=sc.sno AND o=o AND sname=王%(7) 查询选修“数据库”课程且成绩在 8090 分之间的学生学号及成绩。SELECT sc.degree,student.snoFROM stu

19、dent,sc,courseWHERE student.sno=sc.sno AND o=o AND cname=数据库 AND degree BETWEEN 80 AND 90(8) 查询课程成绩及格的男同学的学生信息及课程号与成绩。SELECT student.*,o,sc.degreeFROM student,sc,courseWHERE student.sno=sc.sno AND o=o AND degree60 AND ssex=男(9) 查询选修“c04”课程的学生的平均年龄。SELECT AVG(year(NOW()-YEAR(sbirthday)FROM student,sc

20、,courseWHERE student.sno=sc.sno AND o=o AND o=c04(10)查询学习课程名为“数学”的学生学号和姓名。SELECT student.sno,snameFROM student,sc,courseWHERE student.sno=sc.sno AND o=o AND cname=数学学院 班级 学号 姓名 19(11)查询“钱军”教师任课的课程号，选修其课程的学生的学号、姓名和成绩。SELECT o,student.sno,sname,sc.degreeFROM teacher,teaching,course,student,scWHERE tea

21、cher.tno=teaching.tno and o=o and o=oand sc.sno=student.sno and teacher.tname=钱军(12)查询在第 3 学期所开课程的课程名称及成绩。SELECT ame,sc.degreeFROM course,sc,teachingWHERE o=o and o=oand cterm=3(13)查询“c02”号课程不及格的学生信息。SELECT student.*FROM student,scWHERE student.sno=sc.sno and cno=c02 and degree90学院 班级 学号 姓名 20(15)查询

22、同时选修了“c04”和“c02”课程的学生姓名和成绩。SELECT student.sname,sc.degreeFROM student,sc,courseWHERE student.sno=sc.sno and o=o and o =c04 AND o =c02四、思考题(1) 指定一个较短的别名有什么好处？更容易辨识方便查看(2) 内连接与外连接有什么区别？内连接: 只有两个表相匹配的行才能在结果集中出现 外连接: 包括 左外连接(左边的表不加限制 ) 右外连接(右边的表不加限制 ) 全外连接(左右两表都不加限制 ) (3) “=”与 IN 在什么情况下作用相同？选值只有一个五、实验总结

23、1、收获经过近一个小时的调试，编写，最终得出结论，并进行书写。使我加深了对 mysql 进一步学习和认识，更加熟练了对软件的使用，收获颇丰。2、存在的问题对于语句的使用略显生疏，需要进一步的练习，加深认识。学院 班级 学号 姓名 21实验六 嵌套查询一、实验目的(1) 掌握嵌套查询的使用方法。(2) 掌握相关子查询与嵌套子查询的区别。(3) 掌握带 IN 谓词的子查询的使用方法。(4) 掌握带比较运算符的子查询的使用方法。二、实验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤在 Grademanager 数据库中完成下面

24、查询：(1) 查询计算机系(CS) 学生的学生学号、姓名及考试成绩。SELECT student.sno,student.sname,SC.degreeFROM student,scWHERE student.sno=sc.sno AND sdept=计算机系 ;(2) 查询“李勇”同学所选课程的成绩。SELECT sc.degreeFROM sc,studentWHERE student.sno=sc.sno AND sname=李勇;(3) 查询“李新”老师所授课程的课程名称。SELECT ameFROM course,teacher,teachingWHERE teacher.tno=t

25、eaching.tno AND o=o AND tname=李新;(4) 查询女教师所授课程的课程号及课程名称。SELECT o,ameFROM teaching,teacher,courseWHERE teacher.tno=teaching.tno AND o=o AND tsex=女;学院 班级 学号 姓名 22(5) 查询姓“王”的学生所学的课程名称。SELECT ameFROM course,student,scWHERE student.sno=sc.sno AND o=o AND sname LIKE 王%;(6) 查询选修“数据库”课程且成绩在 8090 分之间的学生学号及成绩

26、。SELECT sc.sno,sc.degreeFROM course,scWHERE o=o AND ame=数据库 AND degree BETWEEN 80 AND 90;(7) 查询选修“C04 ”课程的学生的平均年龄。SELECT AVG(YEAR(CURDATE()-YEAR(student.sbirthday) AS 选修 C04 课程的学生平均年龄FROM student,scWHERE student.sno=sc.sno AND o=c04(8) 查询学习课程名为“数学”的学生学号和姓名。SELECT student.sno,student.snameFROM studen

27、t,course,scWHERE student.sno=sc.sno AND o=o AND ame=数学(9) 查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。SELECT cno,sno,degreeFROM scWHERE cno in(SELECT cno FROM teaching,teacher WHERE teacher.tno=teaching.tno AND tname=钱军)学院 班级 学号 姓名 23(10) 查询在第 3 学期所开课程的课程号及成绩。SELECT cno,degree FROM scWHERE cno in(SELECT cno FROM

28、teaching WHERE cterm=3)(11) 查询与“李勇”同一个系的同学姓名。SELECT snamefrom studentWHERE sdept in( SELECT sdept FROM student WHERE sname=李勇)(12) 查询学号比“刘晨”同学大，而出生日期比他小的学生姓名。SELECT snamefrom studentWHERE snoall (SELECT year(sbirthday) from student WHERE sname=刘晨)(13) 查询出生日期大于所有女同学出生日期的男同学的姓名及系别。select sname,sdeptfr

29、om studentwhere year(sbirthday)all(select year(sbirthday)from student where ssex=女 ) and ssex=男(14) 查询成绩比该课程平均成绩高的学生的学号及成绩。select sno,degreefrom scwhere degree =(select avg(degree) from student,sc where student.sno=sc.sno)(15) 查询不讲授“C01 ”课的教师姓名。学院 班级 学号 姓名 24select tnamefrom teacher ,teachingwhere t

30、eacher.tno=teaching.tno and cno !=c01(16) 查询没有选修“C02 ”课程的学生学号及姓名。select student.sno,snamefrom student,scwhere student.sno=sc.sno and cno!=C02(17) 查询选修了“数据库”课程的学生学号、姓名及系别。select sname,student.sno,sdeptfrom student,sc,coursewhere student.sno=sc.sno and o=o and cname=数据库(18) 查询“C02”号课程不及格的学生信息。select *

31、from student,scwhere student.sno=sc.sno and cno=c02 and degree60四、思考题(1) 子查询一般分为几种？4 种(2) 相关子查询的执行过程是什么？依赖于父查询的查询五、实验总结学院 班级 学号 姓名 251、收获进一步的练习了嵌套查询和夺标连接，学到更多的知识，受益良多。2、存在的问题还是不够熟悉，需要进一步的学习和复习实验七 数据更新一、实验目的：(1) 掌握利用 INSERT 命令实现对表数据的插入操作。(2) 掌握利用 UPDATE 命令实现对表数据的修改操作。(3) 掌握利用 DELETE 命令实现对表数据的删除操作。二、实

32、验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤在 Gradem 或 Gradem1 数据库中完成下面操作：注意：利用 SELECT INTO命令备份 Student、SC、Course 这 3 个表，备份表名自定。(1) 向 Student 表中插入记录(“20050203“，“ 张静“，“1981-3-21“，“女“，“CS“ ，“电子商务“)。INSERTINTO student(sno,sname,sbirthday,ssex,sdept,speciality)VALUES(20050203,张静,1982-3-

33、21,女,CS,电子商务);(2) 插入学号为“20050302”、姓名为“李四”的学生信息。INSERTINTO student(sno,sname)VALUES(20050202,李四);(3) 把计算机系的学生记录保存到表 TS 中(TS 表已存在，表结构与 Student 表相同)。INSERTINTO tsSELECT *学院 班级 学号 姓名 26FROM studentWHERE sdept=计算机(4) 将学号为“20050202”的学生姓名改为“张华”，系别改为 “CS”，专业改为“多媒体技术”。UPDATE studentSET sname=张华,sdept=cs,spec

34、iality=多媒体技术WHERE sno=20050202;(5) 将“李勇”同学的专业改为“计算机信息管理”。UPDATE studentSET speciality=计算机管理WHERE sname=李勇;(6) 把选修了“数据库”课程而成绩不及格的学生的成绩全改为空值 (NULL)。UPDATE SCSET degree=NULLWHERE cno in(SELECT cnofrom courseWHERE cname=数据库)AND degree60(7) 删除学号为“20050302”的学生记录。DELETEFROM studentWHERE sno=20050302(8) 删除“

35、计算机系”所有学生的选课记录。DELETEFROM scWHERE sno in(SELECT snofrom studentWHERE sdept=计算机系)(9) 删除 SC 表中尚无成绩的选课记录。DELETEFROM sc学院 班级 学号 姓名 27WHERE degree=NULL(10) 把“张晨”同学的成绩记录全部删除。DELETEFROM scWHERE sno in(SELECT snoFROM studentWHERE sname=张晨)四、思考题(1) DROP 命令和 DELETE 命令的本质区别是什么？DROP 删除整个表，表结构和数据DELETE 删除表的某一行，不

36、删除表结构(2) 利用 INSERT、UPDATE 和 DELETE 命令可以同时对多个表进行操作吗？不能，只能单个表五、实验总结：1、收获进一步的熟悉了 mysql 的运用，记住了很多插入，修改，删除的语句。2、存在的问题不够熟练，需要背语句结构。实验八 视图一、实验目的：(1) 理解视图的概念。(2) 掌握创建、更改、删除视图的方法。(3) 掌握使用视图来访问数据的方法。二、实验要求学院 班级 学号 姓名 281、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤1) 使用 SQL 语句创建、管理视图(1) 创建视图。 创建一个

37、名为 sc_view1 的视图，从数据库 gradem 的 sc 表中查询出成绩大于 90 分的所有学生选修成绩的信息。 创建一个名为 sc_view2 的视图，从数据库 gradem 的 sc 表中查询出成绩小于 80 分的所有学生的学号、课程号、成绩等信息。 创建一个名为 sc_view3 的视图，由数据库 gradem 的 student、course、sc 表创建一个显示“20070303”班学生选修课程(包括学生姓名、课程名称、成绩等信息 )的视图。 创建一个从视图 sc_view1 中查询出课程号“c01”的所有学生的视图。(2) 修改视图的定义。修改视图 sc_view1，使其从

38、数据库 gradem 的 sc 表中查询出成绩大于 90 分且第 3 学期的所有学生选修成绩的信息。学院 班级 学号 姓名 29(3) 视图的删除。 将视图 sc_view1 删除。(4) 管理视图中的数据。 从视图 sc_view2 中查询出学号为“2007030125”、课程号为 “a01”的学生选修成绩的信息。 将视图 sc_view2 中学号为“2007030122”、课程号为 “c02”的成绩改为 87。 从视图 sc_view2 中将学号为“2007030123”、课程号为 “a01”的学生信息删除。四、思考题向视图中插入的数据能进入到基本表中去吗？修改基本表的数据会自动反映到相应

39、的视图中去吗？五、实验总结1、收获2、存在的问题学院 班级 学号 姓名 30实验九 数据库的安全性一、实验目的：(1) 理解 My SQL 的权限系统的工作原理。(2) 理解 My SQL 账户及权限的概念。(3) 掌握管理 My SQL 账户和权限的方法。(4) 学会创建和删除普通用户的方法和密码管理的方法。(5) 学会如何进行权限管理。二、实验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤1)利用 Navicat 图形工具实现下列操作： 使用 root 用户创建 aric 用户，初始密码设置为 abcdef。让该用户

40、对 gradem 数据库拥有SELECT、UPDATE、DROP 权限。用 aric 用户登录，利用 aric 用户来验证自己是否有 GRANT 权限和 CREATE 权限。用 root 用户登录，收回 aric 用户的删除权限。2) 利用命令实现下列操作：进行本实验的准备工作是：以 root 的身份完成了表 student、course、sc 的创建。（一） 授权1.以 root 的身份登陆 mysql，创建用户 u1+学号后四位，u2+ 学号后四位，u3+ 学号后四位，u4+学号后四位。2.用户 root 把查询 Student 表权限授给用户 u1+学号后四位，验证 u1 能够执行相应的查询。1） 查询所有姓刘的学生的姓名、学号和性别。2） 查询名字中第二字为“勇”字的学生的姓名和学号。