数据库实验报告(7个实验完整附截图).doc

福建农林大学计算机与信息学院实验报告课程名称：数据库原理及应用姓 名：系：计算机科学与技术 专 业：计算机科学与技术 年 级：2012 级学 号：指导教师：陈长江 2014 年 5月 18 日实验项目列表序号实验项目名称成绩指导教师1实验一 数据库的定义实验（验证性） 2实验二 数据库的建立和维护实验（验证性） 3实验三 数据库的查询实验（验证性） 4实验四 数据库的视图操作实验（验证性） 5实验五 触发器、存储过程操作实验（综合性） 实验一：数据库的定义实验 一、实验目的： 1、理解 MySQL Server 6.0 服务器的安装过程和方法； 2、要求学生熟练掌握和使用 SQL、T-SQL、SQL Server Enterpriser Manager Server 创建数据库、表、索引和修改表结构，并学会使用 SQL Server Query Analyzer,接收TSQL 语句和进行结果分析。 二、实验环境： 硬件：PC机 软件：Windows操作系统、 MySQL Server 6.0 和Navicat for MySQL 9.0 三、实验内容和原理： 1、安装 MySQL以及相应的 GUI工具 2、用 SQL命令，建立学生-课程数据库基本表 ： 学生 Student（学号 Sno，姓名 Sname，年龄 Sage，性别 Ssex，所在系Sdept）； 课程Course（课程号 Cno，课程名Cname，先行课Cpno，学分 Ccredit）； 选课SC（学号 Sno，课程号Cno，成绩 Grade）； 要求: 1) 用SQL命令建库、建表和建立表间联系。 2) 选择合适的数据类型。 3) 定义必要的索引、列级约束和表级约束 四、实验步骤： 1、运行 Navicat for MySQL，然后进行数据库连接，进入到 GUI界面； 2、利用图形界面建立基础表： student 表的信息： 字段名类型长度约束条件Snovarchar9非空、主键Snamevarchar20Ssexvarchar2Sagesmallint6Sdeptvarchar20course表的信息： 字段名类型长度约束条件Cnovarchar4非空、主键Cnamevarchar40Cpnovarchar4与 course 表中 Cno 关联Ccreditsmallint6sc表的信息： 字段名类型长度约束条件Snovarchar9非空、主键、与 student 表中 Sno外键关联,级联删除Cnovarchar4Gradesmallint6非空、主键、与 course 表中 Cno外键关联（1）连接数据库，在 localhost 中点击鼠标右键（如图1所示），点击“新建数据库”，在弹出的窗口中输入数据库名称（如图2所示），然后单击“确定”，就完成了数据库的建立。（2）进入新建的数据库，在表的位置单击鼠标右击（如图3），点击“新建表”，分别在“栏位”中输入上表所示的字段名、类型及长度中的数据（如图 4、5、6所示），在“外键”中输入对应表的约束条件（如图7、8）。3、利用命令方式建表：（1）、单击“查询”，然后点击“新建查询”，在弹出的新建查询窗口中输入“CREATE DATABASE zhou”命令，建立一个名为 zhou 的数据库； （2） 、通过“use zhou”命令进入到 zhou 数据库中； （3）、在查询编辑器窗口中分别输入命令： 建立student 表： CREATE TABLE student (Sno varchar(10) PRIMARY KEY, Sname varchar(20) UNIQUE, Ssex varchar(2), Sage SMALLINT, Sdept varchar(20) ) 建立course 表： CREATE TABLE course (Cno varchar(4) PRIMARY KEY, Cname varchar(40), Cpno varchar(4), Ccredit SMALLINT, FOREIGN KEY (Cpno) REFERENCES course(Cno) ) 建立sc表： CREATE TABLE sc (Sno varchar(10), Cno varchar(4), Grade SMALLINT, PRIMARY KEY (Sno,Cno), FOREIGN KEY (Sno) REFERENCES student(Sno) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (Cno) REFERENCES course(Cno) ON DELETE NO ACTION ON UPDATE CASCADE)（4）、向student 表中添加“Sentrance”列： ALTER TABLE student ADD Sentrance DATE （5）、将student 表中“Sentrance”的类型改为 varchar ALTER TABLE student MODIFY COLUMN Sentrance varchar(4); （6）、删除student 表中的“Sentrance”列： ALTER TABLE student DROP Sentrance; 五、实验结果 1、student 表： 2、course表： 3、sc表： 4、向student表中添加 Sentrance列： 5、student表的基本信息： 6、将Sentrance的数据类型改为 varchar： 六、总结： 通过这次实验，要求掌握了数据库的定义以及基本表的建立，熟悉 MySQL图形界面和SQL命令去创建、修改、删除基本表及设定表级完整性约束，巩固了SQL的一些常用的命令语句，为接下来的实验奠定基础。 实验二：数据库的建立和维护实验 一、实验目的和要求 熟练掌握使用 SQL、Transact-SQL和SQL Server 企业管理器向数据库输入数据、修改数据和删除数据的操作。 二、实验内容和原理 1、基本操作实验 （1） 通过MySQL的GUI工具Navicat，在学生-课程数据库的student、 course和sc 3个表中各输入若干条记录。要求记录不仅满足数据约束要求，还要有表间关联的记录。 （2）通过 MySQL 的 GUI 工具 Navicat 实现对学生-课程数据库的 student、course和sc 3个表中数据的插入、删除和修改操作。 2、提高操作实验 通过查询编辑器用 SQL命令实现对学生-课程库的数据增加、数据删除和数据修改操作。 三、实验环境 硬件：PC机 软件：Windows操作系统、 MySQL Server 6.0 和Navicat for MySQL 9.0 四、算法描述及实验步骤 1、基本操作实验 （1）运行“Navicat”，双击“student”表，将学生的信息输入对应位置（如图1所示）；双击“course”表，将课程信息输入对应位置（如图 2 所示）；双击“sc”表，将选课信息输入对应位置（如图3所示）； （2）需要数据插入时，就在最后一条记录后输入一条记录。当鼠标点击其他行时，输入的记录会自动保存在表中。 （3）需要修改记录时，直接对表中已有记录的数据进行改动，用新值替换原有的值。 （4）需要删除记录时，先用鼠标单击要删除行的左边灰色方块，使该记录cj9 成为当前行，然后按<Delete>键。为了防止误操作，MySQL会弹出一个警告框，要求用户确认删除操作，单击“确认”按钮即可删除记录。也可通过先选中一行或多行记录，然后再按 <Delete>键的方法一次删除多条记录。 2、 提高操作实验 在查询编辑器中输入以下代码，实现相应的功能。 （1）将（学号:3126010050；姓名:周孙彬；性别;男；所在系:CS；年龄:20）的学生信息插入到student 表中，实现的代码如下： INSERT INTO student(Sno,Sname,Ssex,Sdept,Sage) VALUES('3126010050','周孙彬','男','CS',20); （2）将student 表中学号为 3126010050的学生的所在系改为 MA，实现代码如下： UPDATE student SET Sdept='MA' WHERE Sno='3126010050' （3）删除姓名为“周孙彬”的学生记录，实现代码如下： DELETE FROM student WHERE Sname='周孙彬' 五、调试过程 提示操作失败，应将 SET Sdept=MA和WHERE Sno=2008001 改为： SET Sdept='MA' 和WHERE Sno='2008001'，然后再运行，提示修改成功，如下图： 六、 实验结果 1、向 student 表插入数据： 2、修改数据： 3、 删除数据： 七、总结 通过这次实验，掌握了数据库的建立和维护的基本知识以及约束条件的作用，掌握使用MySQL 图形界面和SQL命令对建立的基本表进行添加数据、修改数据、删除数据的操作，同时也巩固了 SQL命令中的添加、修改、删除语句，为以后做数据库的维护奠定基础。 实验三：数据库的查询实验 一、实验目的和要求 1、掌握 select语句的基本语法； 2、掌握子查询、连接查询的表示； 3、掌握 select语句的 GROUP BY、ORDER BY、LIMIT的作用和使用方法。 二、实验内容和原理 1、select语句的基本使用： （1）查询student 表中每个学生的所有数据； （2）查询course 和sc表的所有记录； （3）查询年龄在 1719岁之间的学生的姓名及年龄； （4）统计学生总人数； （5）查询信息系（IS）学生的姓名和性别； （6）查询所有姓“王”的学生的信息。 2、子查询的使用： （1）查询与“周孙彬”在同一个系的学生 （2）查询其他系中比 CS系所有学生年龄都小的学生的姓名和年龄。 3、连接查询的使用： （1）查询选修了 3号课程且成绩在85分以上的学生的学号、姓名。 （2）查询所有学生的选课情况。 4、GROUP BY、ORDER BY 和LIMIT子句的使用： （1）查找student 中男生和女生的人数； （2）查找选修了 2 号课程的学生的学号及其成绩，查询结果按成绩降序排列； （3）返回student 表中的前3为同学的信息。 三、 实验环境 硬件：PC机 软件：Windows操作系统、 MySQL Server 6.0 和Navicat for MySQL 9.0 cj13 四、算法描述及实验步骤 1、select语句的基本使用： （1）查询student 表中每个学生的所有数据： SELECT * FROM student .（2）查询年龄在 1719岁之间的学生的姓名及年龄： SELECT Sname,Sage FROM student WHERE Sage BETWEEN 17 AND 19 （3）统计学生总人数： SELECT COUNT(*) FROM student （4）查询信息系（MA）学生的姓名和性别： SELECT Sname,Ssex FROM student WHERE Sdept IN('MA') （5）查询所有姓“王”的学生的信息。 SELECT * FROM student WHERE Sname LIKE '王%' 2、子查询的使用： （1）查询与“周孙彬”在同一个系的学生的姓名和所在系： SELECT Sname,Sdept FROM student WHERE Sdept IN (SELECT Sdept FROM student WHERE Sname='周孙彬') （2）查询其他系中比 IS系所有学生年龄都小的学生的姓名和年龄。 SELECT Sname,Sage FROM student WHERE Sage<ALL (SELECT Sage FROM student WHERE Sdept='IS') 3、连接查询的使用： （1）查询选修了 3号课程且成绩在85分以上的学生的学号、姓名： SELECT Sno,Sname FROM student WHERE Sno IN (SELECT Sno FROM sc WHERE Cno='3') （2）查询有选课的学生的基本情况。 SELECT student.Sno,student.Sname,course.Cno,course.Cname FROM student,sc,course WHERE student.Sno=sc.Sno AND sc.Cno=course.Cno 4、GROUP BY、ORDER BY 和LIMIT子句的使用： （1）查找student 中男生和女生的人数： SELECT Ssex,COUNT(Ssex) FROM student GROUP BY Ssex （2）查找选修了 2 号课程的学生的学号及其成绩，查询结果按成绩降序排列： SELECT Sno,Grade FROM sc WHERE Cno='2' ORDER BY Grade DESC （3）返回student 表中的前3为同学的信息。 SELECT * FROM student LIMIT 3 五、 实验结果 1、select语句的基本使用： （1） . （2） （3） （4）（5）2、子查询的使用： （1） （2） 3、连接查询的使用： （1） （2） 4、GROUP BY、ORDER BY 和LIMIT子句的使用： （1） （2） （3） 六、总结 通过这次实验，掌握了 select 语句的基本使用方法，对数据库中的数据进行查询方法有了进一步的了解，巩固了子查询和连接查询的使用以及 GROUP BY、ORDER BY和LIMIT子句的使用，为以后做数据库数据的查询奠定基础。 说明：可以一次插入多条记录 实验四：数据库的视图操作实验 一、 实验目的和要求 1、熟悉视图的概念和作用； 2、掌握视图的创建方法； 3、掌握如何查询和修改视图。 二、实验内容和原理 1、创建视图： （1）创建zhz数据库上的视图 student_view，视图包含学号、姓名、系； （2）创建zhz数据库上的视图 sc_view，视图包含 sc表的全部列。 2、查询视图： （1）从视图sc_view 中查看选修了3号课程的学生的学号； （2）从视图student_view 中查询姓名为“王敏”的学生所在的系。 3、更新视图： （1）向视图sc_view 中插入一行数据：2008005，5，87； （2）将视图student_view 中学号=2008002 的学生的系改为MA； （3）删除视图student_view 中学号=2008007 的学生的信息。 4、删除视图： （1）删除视图sc_view； 5、在界面工具中操作视图 三、实验环境 硬件：PC机 软件：Windows操作系统、 MySQL Server 6.0 和Navicat for MySQL 9.0 四、算法描述及实验步骤 1、创建视图： （1）创建zhz数据库上的视图 student_view，视图包含学号、姓名、系： CREATE VIEW student_view AS SELECT Sno,Sname,Sdept FROM student （2）创建zhz数据库上的视图 sc_view，视图包含 sc表的全部列： CREATE VIEW sc_view AS SELECT * FROM sc 2、查询视图： （1）从视图sc_view 中查看选修了3号课程的学生的学号： SELECT Sno FROM sc_view WHERE Cno='3' （2）从视图student_view 中查询姓名为“周孙彬”的学生所在的系： SELECT Sname,Sdept FROM student_view WHERE Sname='周孙彬' 3、更新视图： （1）向视图sc_view 中插入一行数据：2008005，5，87； INSERT INTO sc_view VALUES('2008005','5','87') （2）将视图student_view 中学号=2008002 的学生的系改为MA； UPDATE student_view SET Sdept='MA' WHERE Sno='2008002' （3）删除视图student_view 中学号=2008007 的学生的信息； DELETE FROM student_view WHERE Sno='2008007' 4、删除视图： （1）删除视图sc_view； DROP VIEW sc_view 5、在界面工具中操作视图 （1）创建视图：选择 zhz数据库，单击“视图”图标，单击“新建视图”，单击“视图创建工具”，双击“student”表，选择“Sno，Sname，Sdept”，然后单击“保存”，输入视图名“student_view”，单击“确定”按钮。 （2） 查询视图：双击视图名，可直接查看视图中的数据 （3）删除视图：在视图窗体中，选择要删除的视图，点击鼠标右键，选择“删除视图”，然后单击“删除”： 五、实验结果 1、创建视图： （1） （2） 2、 查询视图： （1） （2） 3、更新视图： （1）插入 （2） 修改 （3）删除 5、在界面工具中操作视图 六、总结 通过这次实验，掌握了视图的创建、查询、修改以及删除等基本操作；了解了视图与基本表的区别，视图是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中；巩固了视图的基本概念及其作用。 实验五：触发器、存储过程操作实验 一、实验目的和要求 1、掌握存储过程创建和调用的方法； 2、掌握 MySQL中程序片段的组成； 3、掌握触发器的使用方法。 二、实验内容和原理 1、存储过程： （1）创建存储过程，使用 student表中的学生人数来初始化一个局部变量，并调用这个存储过程； （2）创建存储过程，比较两学生的年龄，若前者比后者大就输出 0，否则输出1。 2、触发器： （1）创建触发器，在 student表中删除学生信息的同时将 sc表中该学生的选课信息删除，以确保数据的完整性； （2）假设 course1 表和 course 表的结构和内容都相同，在 course 上创建一个触发器，如果添加一门新的选修课程，该课程也会被添加到 course1 表中。 （3）定义一个 BEFORE 行级触发器，为 teacher 表定义完整性规则，“向teacher插入教授的信息时，工资不得低于 4000 元，如果低于4000元，则自动将其更改为4000元”。 （4）删除teacher 表上的触发器teacher_Income 。 三、实验环境 硬件：PC机 软件：Windows操作系统、 MySQL Server 6.0 和Navicat for MySQL 9.0 四、算法描述及实验步骤 1、存储过程： （1）创建存储过程，使用 student表中的学生人数来初始化一个局部变量， 并调用这个存储过程： delimiter $ CREATE PROCEDURE TEST(OUT number1 INTEGER) BEGIN DECLARE number2 INTEGER; SET number2=(SELECT COUNT(*) FROM student); SET number1=number2; END $ Delimiter ; 调用该存储过程：CALL TEST(number) 查看结果：SELECT number （2）创建存储过程，比较两学生的年龄，若前者比后者大就输出 0，否则输出1： delimiter $ CREATE PROCEDURE COMPA(IN Sno1 varchar(9),IN Sno2 varchar(9),OUT BJ INTEGER) BEGIN DECLARE SR1,SR2 FLOAT(10,2); SELECT Sage INTO SR1 FROM student WHERE Sno=Sno1; SELECT Sage INTO SR2 FROM student WHERE Sno=Sno2; IF Sno1>Sno2 THEN SET BJ=0; ELSE SET BJ=1; END IF; END $ Delimiter ; 调用该存储过程：CALL COMPA('2008002','2008003',BJ); 查看结果：SELECT BJ 2、触发器： （1）创建触发器，在 student表中删除学生信息的同时将 sc表中该学生的选课信息删除，以确保数据的完整性： CREATE TRIGGER deleteSm before DELETE ON student FOR EACH ROW DELETE FROM sc WHERE Sno=OLD.Sno; 删除student 表中的一行数据，然后查看 sc表的变化： DELETE FROM student WHERE Sno='2008007' 观察sc表的变化； （2）假设student1 表和student表的结构和内容都相同，在 student 上创建一个触发器，如果添加一个学生的信息，该信息也会被添加到 student1 表中： delimiter $ CREATE TRIGGER student_Ins AFTER INSERT ON student FOR EACH ROW BEGIN INSERT INTO student1 VALUES(new.Sno,new.Sname,new.Ssex,new.Sage,new.Sdept); END $ Delimiter ; 向student 表中添加一条新信息('2008006','杨过','男',19,'IS') ： INSERT INTO student VALUES('2008006','杨过','男',19,'IS'); 观察student1表的变化； （3）定义一个BEFORE行级触发器，为teacher 表定义完整性规则“插入教授的信息时，工资不得低于 4000元，如果低于 4000元，则自动改为4000 元”： Create table teacher ( Tno varchar(20) primary key, Tname varchar(20), Tsex varchar(2), Job varchar(10), Income varchar(10) ); CREATE TRIGGER teacher_Income BEFORE INSERT ON teacher FOR EACH ROW BEGIN IF(new.Job='教授')AND(new.salary<4000) THEN Set new.salary=4000; END IF; END; 向teacher表中添加两条新信息（'10006','林涛'，'男','教授',3500）和('10007','冯巩','男','讲师',3500)，以对比效果:INSERT INTO teacherVALUES('10006','林涛','男','教授',3500);INSERT INTO teacherVALUES('10007','冯巩','男','讲师',3500);SELECT * FROM teacher;观察teacher表的结果； （4）删除teacher 表上的触发器teacher_Income 。 DROP TRIGGER teacher_Income; 五、调试过程 1、 提示在第四行出现语法错误，将DECLARE SR1,SR2,FLOAT(10);改为DECLARE SR1,SR2 FLOAT(10);2、 调用CALL COMPA0('2012001','2012002',BJ);后查询完的结果不正确，将条件 IF Sno1>Sno2 THEN 改为 IF SR1>SR2 THEN 后查询信息正确。3 提示出现语法错误，应在“DROP TRIGGER teacher_Income ON teacher”中的“ON teacher” 删除，然后再运行： 六、实验结果 实验前： Student:Student1:Teacher:SC:Course: 实验截图 1、 存储过程： 2、触发器： （1） student表： （2） student表： Student1表：（3） 七、总结 通过这次实验，掌握了存储过程的创建和调用方法以及触发器的创建、删除操作；了解了触发器在数据库中所起到的作用，它是数据库完整性的一个重要方法；巩固了存储过程和触发器的基本概念。 Experiment 07 数据库存储和优化使用show status了解SQL的执行效率；定位执行效率较低的SQL语句：打开my.ini，找到mysqld标签，添加3句：log-slow-queries = “f:/slow.log”Long_query_time = 2Log-queries-not-using-indexes保存、关闭，重启MySQL使用explain语句检查SQL语句：Explain tblName;或Explain select select_option;实例例7-1：查找计算机系学生的姓名以及选修的课程名和修课成绩。（多表连接查询分析和改进）explain select student.sname, ame, grade from student, course, scwhere student.sno =sc.snoand o = oand sdept = 'cs' create index sdeptindex on student(sdept);示例 对约有8万条记录的表进行单记录插入和所有记录排序查询（分别对两个字段进行排序）执行耗时比较，测试使用索引和不使用索引、使用聚集索引与非聚集索引、对唯一值与非唯一值字段建立索引并排序等情况的执行情况，从而了解使用索引的作用和意义。在教学管理系统（jxgl）中创建表test，并插入8万条记录。创建表：Create table test( id int unique auto_increment,rq datetime null,srq varchar(20) null,hh smallint null,mm smallint null,ss smallint null,num numeric(12,3),primary key (id)auto_increment = 1, engine = myisam;创建存储过程，生成表中的数据：Delimiter /Create procedure p1()BeginSet i = 1;While i <= 80000 doInsert into test ( rq,srq,hh,mm,ss,num)Values( now(), now(), hour(now(), minute(now(),second(now(),rand(i) *100);Set i = i + 1;End while;End/Delimiter ;调用存储过程：Call p1();运行测试代码:Case 1: 未建立索引时按以下步骤操作：单记录插入Select i:=max(id) from test;Insert into test( rq,srq,hh,mm,ss,num)Values( now(), now(), hour(now(), minute(now(), second(now(), rand(i)*100);查询所有记录，按id排序Select * from test order by id;查询所有记录，按mm排序Select * from test order by mm;单记录查询Select id from test where id = 51;Case 2: 对test表的id字段建立非聚集索引 create index indexname1 on test(id);重做以上4步后删除索引Drop index indexname1 on test;Case 3：对test表的mm字段建立非聚集索引 create index indexmm on test(mm);重做以上4步后删除索引 drop index indexmm on test;Case 4 ：对test 表的id字段建立唯一索引Create unique index indexid on test(id);重做以上4步后删除索引Drop index indexid on test;