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

学专 年 姓 学数据库原理与应用实验实验报告册学年第_学期院业级名 号：任课教师：MySQL+Navicat 安装步骤与下载地址 百度地址： 配置与简单使用 百度地址：本文档所书写的代码，为本人纯手工敲打，并且通过软件测试成功，欢迎大家 进行学习，如有错误，可联系本人实验一 创建和维护数据库一、实验目的(1) 掌握在Windows平台下安装与配置MySQL的方法。(2) 掌握启动服务并登录 MySQL 数据库的方法和步骤。(3) 掌握MySQL数据库的相关概念。(4) 掌握使用Navicat工具和SQL语句创建数据库的方法。(5) 掌握使用 Navicat 工具和 SQL 语句删除数据库的方法二、实验要求(1) 学生提前准备好实验报告，预习并熟悉实验步骤；(2) 遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤(1) 在Windows平台下安装与配置MySQL版。(2) 在服务对话框中，手动启动或者关闭 MySQL 服务。(3) 使用 Net 命令启动或关闭 MySQL 服务。4)分别用 Navicat 工具和命令行方式登录 MySQL。(5)在 文件中将数据库的存储位置改为D:MYSQLDATA。6)创建数据库。 使用 Navicat 创建学生信息管理数据库 gradem 使用 SQL 语句创建数据库 MyDB。7)删除数据库。 使用 Navicat 图形工具删除 gradem 数据库 使用 SQL 语句删除 MyDB 数据库。四、思考题常见的数据库产品有哪些五、实验总结1、收获2、存在的问题实验二 管理表一、实验目的（1）掌握表的基础知识。（2）掌握使用Navicat管理工具和SQL语句创建表的方法。（3）掌握表的修改、查看、删除等基本操作方法。二、实验要求（1）学生提前准备好实验报告，预习并熟悉实验步骤；（2）遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤在gradem数据库中创建表表所示结构的表。表student表的表结构字段名称数据类型长度小数位数是否允许NULL值说明snochar10否主码sn amevarchar8是ssexchar2是sbirthdaydatetime是saddressvarchar50是sdep tchar16是specialityvarchar20是表course表（课程名称表）的表结构字段名称数据类型长度小数位数是否允许NULL值说明enochar5否主码cn amevarchar20否表sc表（成绩表）的表结构字段名称数据类型长度小数位数是否允许NULL值说明snochar10否组合主码、外码enochar5否组合主码、外码degreedecimal41是1100表 teacher表（教师表）的表结构字段名称数据类型长度小数位数是否允许NULL值说明tnochar3否主码tn amevarchar8是t sexchar2是表teaching表(授课表)的表结构字段名称数据类型长度小数位数是否允许NULL值说明enochar5否组合主码、外码tnochar3否组合主码、外码ct ermtinyint10是110t dep tchar是16(2)向表至表输入数据记录，见表表。表学生关系表studentsnosn amessexsbirthdaysaddresssdep tspeciality李勇男1987-01-12山东济南计算机工程系计算机应用刘晨女1988-06-04山东青岛信息工程系电子商务王敏女1989-12-23江苏苏州数学系数学张立男1988-08-25河北唐山信息工程系电子商务表课程关系表courseenocn ameenocn ameC01数据库C03信息系统C02数学C04操作系统表 成绩表scsnoenodegreeC0192C0285C0388C0290C0380表教师表teachertnotn amet sextbirthdayt dep t101李新男1977-01-12计算机工程系102钱军女1968-06-04计算机工程系201王小花女1979-12-23信息工程系202张小青男1968-08-25信息工程系表 授课表teachingenotnocterm(3) 在navicat下修改表结构。 向student表中增加“入学时间”歹U,其数据类型为日期时间型。 将student表中的sdept字段长度改为20。 将student表中的speciality字段删除。 删除student表。(4) 利用 SQL 命令(create table、alter table、drop table)完成对表的操作 利用create talbe命令完成student表和course表的定义。 利用alter table、drop table命令实现(3)中的所有任务。四、思考题(1) 在定义基本表语句时，NOT NULL参数的作用是什么(2) 主码可以建立在“值可以为NULL”的列上吗五、实验总结：1、收获2、存在的问题实验三 简单查询单表无条件和有条件查询一、实验目的(1) 掌握SELECT语句的基本用法。(2) 使用WHERE子句进行有条件的查询。(3) 掌握使用IN和NOT IN, BETWEENAND和NOT BETWEENAND来缩小查询范围的方法。(4) 利用LIKE子句实现字符串匹配查询。二、实验要求1、学生提前准备好实验报告,预习并熟悉实验步骤；2、遵守实验室纪律,在规定的时间内完成要求的内容；三、实验内容及步骤在上次实验建立的Gradem或Gradem1数据库中完成下面查询：(1) 查询所有学生的基本信息、所有课程的基本信息和所有学生的成绩信息(用三条SQL语句)。SELECT *FROM student;SELECT *FROM course;SELECT *FROM sc;(2) 查询所有学生的学号、姓名、性别和出生日期。SELECT 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) 查询“电子商务”专业的学生姓名、性别和出生日期。SELECT sname,ssex,sbirthdayFROM studentWHERE sdep t二电子商务(10) 查询Student表中的所有系名。SELECT sdeptFROM student(11) 查询“C01 ”课程的开课学期。SELECT ctermFROM teachingWHERE cno=c01(12) 查询成绩在8090分之间的学生学号及课号。SELECT sno,cnoFROM scWHERE degree BETWEEN 80 and 90(13) 查询在1970年1月1日之前出生的男教师信息。SELECT *FROM teacherWHERE tbirthday3(8) 查询成绩不及格的学生学号及课号，并按成绩降序排列SELECT sno,cnoFROM scWHERE degree1(10) 统计输出各系学生的人数SELECT sdept ,COUNT(*)FROM studentGROUP BY sdept(11) 统计各系的男、女生人数。(两条命令)Select sdept,ssex,count(*)From studentGroup by sdept ,ssex(12) 统计籍贯的男、女生人数。(两条命令)Select saddress,ssex,count(*)From 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) 查询软件系的男生信息，查询结果按出生日期升序排序，出生日期相同的按地址降序排序。SELECT *FROM studentWHERE ssex二男and sdept二信息工程系ORDER BY sbirthday,saddress DESC四、思考题(1) 聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中(2) WHERE子句与HAVING子句有何不同五、实验总结：1、收获2、存在的问题实验五 多表查询一、实验目的(1) 掌握SELECT语句在多表查询中的应用。(2) 掌握多表连接的几种连接方式及应用。二、实验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤：在Gradem或Gradem1数据库中完成下面查询：(1) 查询计算机工程系女学生的学生学号、姓名及考试成绩SELECT ,sname,FROM student,scWHERE = and =计算机工程系 and =女;(2) 查询“李勇”同学所选课程的成绩。(不考虑重名)SELECTFROM student,scWHERE = and =李勇(3) 查询“李新”老师所授课程的课程名称。SELECTFROM teacher,teaching,courseWHERE = and = and =李新(4) 查询女教师所授课程的课程号及课程名称。SELECT ,FROM teacher,teaching,courseWHERE = and = and =女(5) 查询至少选修一门课程的女学生姓名。SELECTFROM student,scWHERE = AND =女GROUP BYHAVING COUNT1(6) 查询姓“王”的学生所学的课程名称。SELECTFROM student,sc,courseWHERE 二 AND 二 AND sname二王(7) 查询选修“数据库”课程且成绩在8090分之间的学生学号及成绩。SELECT ,FROM student,sc,courseWHERE 二 AND 二 AND cname二数据库AND degree BETWEEN 80 AND 90(8) 查询课程成绩及格的男同学的学生信息及课程号与成绩。SELECT student.*,FROM student,sc,courseWHERE 二 AND 二AND degree60 AND ssex=男(9) 查询选修“c04”课程的学生的平均年龄。SELECT AVG(year(NOW()-YEAR(sbirthday)FROM student,sc,courseWHERE 二 AND 二 AND =c04(10) 查询学习课程名为“数学”的学生学号和姓名。SELECT ,snameFROM student,sc,courseWHERE 二 AND 二 AND cname二数学(11) 查询“钱军”教师任课的课程号，选修其课程的学生的学号、姓名和成绩。SELECT ,sname,FROM teacher,teaching,course,student,scWHERE = and = and =and = and =钱军(12) 查询在第3学期所开课程的课程名称及成绩。SELECT ,FROM course,sc,teachingWHERE = and =and cterm=3(13) 查询“c02”号课程不及格的学生信息。SELECT student.*FROM student,scWHERE = and cno=c02 and degree90(15) 查询同时选修了 “cO4”和“c02”课程的学生姓名和成绩。SELECT ,FROM student,sc,courseWHERE = and = and =c04 AND =c02四、思考题(1) 指定一个较短的别名有什么好处更容易辨识方便查看(2) 内连接与外连接有什么区别内连接: 只有两个表相匹配的行才能在结果集中出现 外连接: 包括左外连接(左边的表不加限制) 右外连接(右边的表不加限制) 全外连接(左右两表都不加限制)(3) “二”与IN在什么情况下作用相同选值只有一个五、实验总结1、收获经过近一个小时的调试，编写，最终得出结论，并进行书写。使我加深了对mysql进一步学习和认识，更加 熟练了对软件的使用，收获颇丰。2、存在的问题对于语句的使用略显生疏，需要进一步的练习，加深认识。实验六 嵌套查询一、实验目的(1) 掌握嵌套查询的使用方法。(2) 掌握相关子查询与嵌套子查询的区别。(3) 掌握带IN谓词的子查询的使用方法。(4) 掌握带比较运算符的子查询的使用方法二、实验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤在Grademanager数据库中完成下面查询：(1) 查询计算机系(CS)学生的学生学号、姓名及考试成绩。SELECT ,FROM student,scWHERE 二 AND sdep t二计算机系；(2) 查询“李勇”同学所选课程的成绩。SELECTFROM sc,studentWHERE 二 AND sname二李勇；(3) 查询“李新”老师所授课程的课程名称。SELECTFROM course,teacher,teachingWHERE 二 AND 二 AND t name二李新；(4) 查询女教师所授课程的课程号及课程名称。SELECT ,FROM teaching,teacher,courseWHERE 二 AND 二 AND tsex二女；(5) 查询姓“王”的学生所学的课程名称。SELECTFROM course,student,scWHERE = AND = AND sname LIKE 王%;(6) 查询选修“数据库”课程且成绩在8090分之间的学生学号及成绩。SELECT ,FROM course,scWHERE = AND =数据库 AND degree BETWEEN 80 AND 90;(7) 查询选修“C04”课程的学生的平均年龄。SELECT AVG(YEAR(CURDATE()-YEAR) AS 选修 C04 课程的学生平均年龄FROM student,scWHERE = AND =c04(8) 查询学习课程名为“数学”的学生学号和姓名。SELECT ,FROM student,course,scWHERE = AND = AND =数学(9) 查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。SELECT cno,sno,degreeFROM scWHERE eno in(SELECT eno FROM teaching, teacher WHERE 二 AND tname二钱军)(10) 查询在第3学期所开课程的课程号及成绩。SELECT cno,degreeFROM scWHERE cno in(SELECT cno FROM teaching WHERE cterm=3)(11) 查询与“李勇”同一个系的同学姓名。SELECT snamefrom studentWHERE sdep t in( SELECT sdep t FROM st uden t WHERE sname二李勇)(12) 查询学号比“刘晨”同学大，而出生日期比他小的学生姓名。SELECT snamefrom studentWHERE snoall (SELECT year(sbir thday) from st uden t WHERE sname二刘晨)(13) 查询出生日期大于所有女同学出生日期的男同学的姓名及系别。select sname,sdeptfrom studentwhere year(sbir thday)all(selec t year(sbir thday)from stu den t where ssex二女) and ssex二男(14) 查询成绩比该课程平均成绩高的学生的学号及成绩。select sno,degreefrom scwhere degree =(select avg(degree) from student,sc where 二(15) 查询不讲授“C01 ”课的教师姓名。select tnamefrom teacher ,teachingwhere = and cno !=c01(16) 查询没有选修“C02”课程的学生学号及姓名。 select ,snamefrom student,scwhere = and cno!=C02(17) 查询选修了“数据库”课程的学生学号、姓名及系别。select sname,sdeptfrom student,sc,coursewhere 二 and 二 and cname二数据库(18) 查询“C02”号课程不及格的学生信息。select *from student,scwhere = and cno=c02 and degree60四、思考题(1) 子查询一般分为几种4种(2) 相关子查询的执行过程是什么依赖于父查询的查询五、实验总结1、收获进一步的练习了嵌套查询和夺标连接，学到更多的知识，受益良多2、存在的问题还是不够熟悉，需要进一步的学习和复习实验七 数据更新一、实验目的：(1) 掌握利用INSERT命令实现对表数据的插入操作。(2) 掌握利用UPDATE命令实现对表数据的修改操作。(3) 掌握利用DELETE命令实现对表数据的删除操作。二、实验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤在Gradem或Gradem1数据库中完成下面操作：注意：利用SELECT INTO命令备份Student、SC、Course这3个表，备份表名自定。(1) 向Student表中插入记录(，张静，1981-3-21，女，CS，电子商务)。INSERTINTO student(sno,sname,sbirthday,ssex,sdept,speciality)VALUESC,张静，1982-3-21,女，CS,电子商务)；(2) 插入学号为“”、姓名为“李四”的学生信息。INSERTINTO student(sno,sname)VALUESC,李四)；(3) 把计算机系的学生记录保存到表TS中(TS表已存在，表结构与Student表相同)。INSERTINTO tsSELECT *FROM studentWHERE sdep t二计算机将学号为“”的学生姓名改为“张华”，系别改为“CS”，专业改为“多媒体技术”。UPDATE studentSET sname二张华，sdept二cs,speciali ty二多媒体技术WHERE sno=;(5) 将“李勇”同学的专业改为“计算机信息管理”。UPDATE studentSET speciali ty二计算机管理WHERE sname二李勇；(6) 把选修了 “数据库”课程而成绩不及格的学生的成绩全改为空值(NULL)。UPDATE SCSET degree二NULLWHERE cno in(SELECT cnofrom courseWHERE cname二数据库)AND degree60(7) 删除学号为“”的学生记录。DELETEFROM studentWHERE sno二(8) 删除“计算机系”所有学生的选课记录。DELETEFROM scWHERE sno in(SELECT snofrom studentWHERE sdep t二计算机系)(9) 删除SC表中尚无成绩的选课记录。DELETEFROM scWHERE degree二NULL(10) 把“张晨”同学的成绩记录全部删除。DELETEFROM scWHERE sno in(SELECT snoFROM studentWHERE sname二张晨)四、思考题(1) DROP命令和DELETE命令的本质区别是什么DROP删除整个表，表结构和数据DELETE删除表的某一行，不删除表结构(2) 利用INSERT、UPDATE和DELETE命令可以同时对多个表进行操作吗 不能，只能单个表五、实验总结：1、收获进一步的熟悉了 mysq丨的运用，记住了很多插入，修改，删除的语句。2、存在的问题不够熟练，需要背语句结构。实验八 视图一、实验目的：(1) 理解视图的概念。(2) 掌握创建、更改、删除视图的方法。(3) 掌握使用视图来访问数据的方法。二、实验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤1)使用SQL语句创建、管理视图(1) 创建视图。 创建一个名为sc_view1的视图，从数据库gradem的sc表中查询出成绩大于90分的所有学生选修成绩的信 创建一个名为sc_view2的视图，从数据库gradem的sc表中查询出成绩小于80分的所有学生的学号、课程 号、成绩等信息。 创建一个名为sc_view3的视图，由数据库gradem的student、course、sc表创建一个显示“”班学生选修 课程(包括学生姓名、课程名称、成绩等信息)的视图。 创建一个从视图sc_view1中查询出课程号“c01 ”的所有学生的视图。(2) 修改视图的定义。修改视图sc_view1，使其从数据库gradem的sc表中查询出成绩大于90分且第3学期的所有学生选修成绩的信 息。(3) 视图的删除。 将视图sc_view1删除。(4) 管理视图中的数据。 从视图sc_view2中查询出学号为“25”、课程号为“aO1 ”的学生选修成绩的信息。 将视图sc_view2中学号为“22”、课程号为“c02”的成绩改为87。 从视图sc_view2中将学号为“23”、课程号为“a01 ”的学生信息删除。四、思考题向视图中插入的数据能进入到基本表中去吗修改基本表的数据会自动反映到相应的视图中去吗五、实验总结1、收获2、存在的问题实验九 数据库的安全性一、实验目的：(1) 理解My SQL的权限系统的工作原理。(2) 理解My SQL账户及权限的概念。(3) 掌握管理My SQL账户和权限的方法。(4) 学会创建和删除普通用户的方法和密码管理的方法。(5) 学会如何进行权限管理。二、实验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤1) 利用Navicat图形工具实现下列操作： 使用roo t用户创建aric用户，初始密码设置为abcdef。让该用户对gradem数据库拥有SELECT、UPDATE、 DROP权限。 用aric用户登录，利用aric用户来验证自己是否有GRANT权限和CREATE权限。 用root用户登录，收回aric用户的删除权限。2) 利用命令实现下列操作：进行本实验的准备工作是：以root的身份完成了表student、course、sc的创建。(一) 授权1以root的身份登陆mysql，创建用户u 1+学号后四位，u2+学号后四位，u3+学号后四位，u4+学号后四位。2.用户root把查询Student表权限授给用户u1+学号后四位，验证u1能够执行相应的查询。1) 查询所有姓刘的学生的姓名、学号和性别。2) 查询名字中第二字为“勇”字的学生的姓名和学号。3. 用户root授予用户u4+学号后四位对student表的insert权限，并允许此权限传播。4. 用户u4+学号后四位将对表student的insert权限授予u3+学号后四位，并允许将权限转授给其他用户（例如：u3+学号后四位），描述过程及操作。（二）回收权限1. 收回所有用户对表sc的查询权限2. 收回用户u4对student表的insert权限3 在回收权限之后验证用户是否真正丧失了该权限（查询表，插入记录），描述过程及提示信息。四、实验总结1、收获2、 存在的问题实验十 数据库的完整性一、实验目的：(1) 理解My SQL的实体完整性的定义。(2) 理解My SQL的参照完整性的定义。(3) 理解My SQL的自定义完整性的定义。二、实验要求1、学生提前准备好实验报告，预习并熟悉实验步骤；2、遵守实验室纪律，在规定的时间内完成要求的内容；三、实验内容及步骤(一) 实体完整性1. 建立部门表DEPT(deptno,deptname),部门编号Deptno列为主码。写出两种方式：一种在列级定义主码,一种是在表级定义主码。2. 建立成绩表sc1(sno,cno,grade)，将sno、eno属性组定义为码。(二) 参照完整性1. 建立成绩表sc2(sno,cno,grade), (sno、eno)是主码，sno、eno分别参照引用student表上的sno和course表的eno主码。2. insert操作验证外键约束(1)在sc2中插入数据(1001, 01 ,95)，假设eno二01的数据存在，而sno= 1001 的数据不存在,执行结果是什么(2)在student表中添加sno= 1001 的数据，然后继续执行(1)中的插入数据操作，执行结果是什么3、delete操作验证外键约束(1) MySQL在外键的删除上有RESTRICT、NO ACTION(类似RESTRICT)、CASCADE和SET NULL四种行为，默认是 那种行为在默认情况下，对student表中的sno= 1001 的记录，执行删除操作时，执行结果是什么若执意要 删除该条记录，需要如何操作(2) SET NULL指当删除主表中被引用列的数据时，将子表中相应引用列的值设置为NULL值。SET NULL有个前 提就是外键引用列必须可以设置为NULL。把sc表的sno外键约束删除，将外键约束的删除行为改为SET NULL，并验证该删除策略。（3）CASCADE指当删除主表中被引用列的数据时，级联删除子表中相应的数据行。把sc表的sno外键约束删除，将外键约束的删除行为改为CASCADE。，并验证该删除策略。（三）自定义完整性1.修改部门表DEPT，要求添加约束cons train tel ：部门名称Dname列取值唯一。2.建立学生登记表Student2,要求学号在9000至9999之间（约束名默认），年龄29 （约束名为a2），性别只能是男或女（约束名默认），姓名非空。3. 修改表Student的结构，由年龄小于29改为小于40。（先删除原有的约束，再添加新约束）四、实验总结1、收获2、存在的问题