数据库原理实验指导书(ek绝密版).doc

数据库原理实验指导书(ek绝密版) 数据库原理实验指导书实验项目列表序号实验项目名称指导教师1实验一数据库的定义实验（验证性)2实验二数据库的建立和维护实验（验证性)3实验三数据库的查询实验（验证性)陈4实验四数据库的视图操作实验（验证性）陈5实验五触发器、存储过程操作实验(综合性）陈实验一数据库的定义实验 一、实验目的 1、理解MySQL Server6.0服务器的安装过程和方法； 2、要求学生熟练掌握和使用SQL、T-SQL、SQL ServerEnterpriser ManagerServer创建数据库、表、索引和修改表结构，并学会使用SQL ServerQuery Analyzer,接收TSQL语句和进行结果分析。 二、实验环境硬件PC机软件Windows操作系统、MySQL Server6.0和Navicat forMySQL9.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 forMySQL，然后进行数据库连接，进入到GUI界面； 2、利用图形界面建立基础表student表的信息course表的信息字段名类型长度约束条件Sno varchar9非空、主键Sname varchar20Ssex varchar2Sage smallint6Sdept varchar20sc表的信息 （1）、连接数据库，在localhost中点击鼠标右键（如图1所示），点击“新建数据库”，在弹出的窗口中输入数据库名称（如图2所示），然后单击“确定”，就完成了数据库的建立。 字段名类型长度约束条件Cno varchar4非空、主键Cname varchar40Cpno varchar4与course表中Cno关联Ccredit smallint6字段名类型长度约束条件Sno varchar9非空、主键、与student表中Sno外键关联,级联删除Cno varchar4非空、主键、与course表中Cno外键关联Grade varchar6图1新建数据库图2 （2）、进入新建的数据库，在表的位置单击鼠标右击（如图3所示），点击“新建表”，分别在“栏位”中输入上表所示的字段名、类型及长度中的数据（如图 4、 5、6所示），在“外键”中输入对应表的约束条件（如图）。 图3新建表图4student表图5course表图6sc表图7course的约束条件图8sc的约束条件 3、利用命令方式建表 （1）、单击“查询”，然后点击“新建查询”，在弹出的新建查询窗口中输入“CREATE DATABASEchk”命令，建立一个名为zhz的数据库； （2）、通过“use chk”命令进入到zhz数据库中； （3）、在查询器窗口中分别输入命令建立student表CREATE TABLE student(Sno varchar (9)PRIMARY KEY,Sname varchar (20)UNIQUE,Ssex varchar (2),Sage SMALLINT,Sdept varchar (20)建立course表CREATE TABLEcourse(Cno varchar (4)PRIMARY KEY,Cname varchar (40),Cpno varchar (4),Ccredit SMALLINT,FOREIGN KEY(Cpno)REFERENCES course(Cno)建立sc表CREATE TABLEsc(Sno varchar (9),Cno varchar (4),Grade SMALLINT,PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno)REFERENCES student(Sno)ON DELETECASCADE ON UPDATE CASCADE,FOREIGN KEY(Cno)REFERENCES course(Cno)ON DELETENO ACTIONONUPDATECASCADE) （4）、向student表中添加“Sentrance”列ALTER TABLEstudent ADDSentrance DATE （5）、将student表中“Sentrance”的类型改为varchar ALTER TABLEstudentMODIFY COLUMNSentrance varchar (4); （6）、删除student表中的“Sentrance”列ALTERTABLEstudent DROPSentrance; 五、实验结果 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和sc3个表中各输入若干条记录。 要求记录不仅满足数据约束要求，还要有表间关联的记录。 （2）通过MySQL的GUI工具Navicat实现对学生-课程数据库的student、course和sc3个表中数据的插入、删除和修改操作。 2、提高操作实验通过查询器用SQL命令实现对学生-课程库的数据增加、数据删除和数据修改操作。 三、实验环境硬件PC机软件Windows操作系统、MySQL Server6.0和Navicat forMySQL9.0 四、算法描述及实验步骤 1、基本操作实验 （1）运行“Navicat”，双击“student”表，将学生的信息输入对应位置（如图1所示）；双击“course”表，将课程信息输入对应位置（如图2所示）；双击“sc”表，将选课信息输入对应位置（如图3所示）； （2）需要数据插入时，就在最后一条记录后输入一条记录。 当鼠标点击其他行时，输入的记录会自动保存在表中。 （3）需要修改记录时，直接对表中已有记录的数据进行改动，用新值替换原有的值。 （4）需要删除记录时，先用鼠标单击要删除行的左边灰色方块，使该记录成为当前行，然后按键。 为了防止误操作，MySQL会弹出一个警告框，要求用户确认删除操作，单击“确认”按钮即可删除记录。 也可通过先选中一行或多行记录，然后再按键的方法一次删除多条记录。 图1student表的数据图2course表的数据图3sc表的数据图4警告图标 2、提高操作实验在查询器中输入以下代码，实现相应的功能。 （1）将（学号:xx006；姓名:李四；性别;男；所在系:IS；年龄:20）的学生信息插入到student表中，实现的代码如下INSERT INTO student(Sno,Sname,Ssex,Sdept,Sage)VALUES(xx006,李四,男,IS,20); （2）将student表中学号为201xx的学生的所在系改为MA，实现代码如下UPDATE studentSET Sdept=MAWHERE Sno=201xx （3）删除姓名为“张立”的学生记录，实现代码如下DELETE FROM student WHERE Sname=张立 五、调试过程提示操作失败，应将SET Sdept=MA和WHERE Sno=201xx改为SET Sdept=MA和WHERE Sno=201xx，然后再运行，提示修改成功，如下图 六、实验结果 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 Server6.0和Navicat forMySQL9.0 四、算法描述及实验步骤 1、select语句的基本使用 （1）查询student表中每个学生的所有数据SELECT*FROM student （2）查询年龄在1719岁之间的学生的姓名及年龄SELECT Sname,Sage FROM student WHERESage BETWEEN17AND19 （3）统计学生总人数SELECT COUNT(*)FROM student （4）查询信息系（MA）学生的姓名和性别SELECT Sname,Ssex FROM student WHERE Sdept IN(MA) （5）查询所有姓“王”的学生的信息。 SELECT*FROM student WHERE SnameLIKE王% 2、子查询的使用 （1）查询与“李勇”在同一个系的学生的姓名和所在系SELECT Sname,Sdept FROM student WHERESdept IN(SELECT Sdept FROM student WHERESname=李勇) （2）查询其他系中比IS系所有学生年龄都小的学生的姓名和年龄。 SELECT Sname,Sage FROM student WHERESage 3、连接查询的使用 （1）查询选修了3号课程且成绩在85分以上的学生的学号、姓名SELECT Sno,Sname FROMstudentWHERE Sno IN(SELECT SnoFROM sc WHERE Cno=3) （2）查询有选课的学生的基本情况。 SELECT student.Sno,student.Sname,course.Cno,course.Cname FROMstudent,sc,course WHEREstudent.Sno=sc.Sno ANDsc.Cno=course.Cno 4、GROUP BY、ORDER BY和LIMIT子句的使用 （1）查找student中男生和女生的人数SELECT Ssex,COUNT(Ssex)FROMstudentGROUP BYSsex （2）查找选修了2号课程的学生的学号及其成绩，查询结果按成绩降序排列SELECT Sno,Grade FROM sc WHERECno=2ORDER BYGrade DESC （3）返回student表中的前3为同学的信息。 SELECT*FROMstudentLIMIT3 五、实验结果 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中插入一行数据xx005，5，87； （2）将视图student_view中学号=xx002的学生的系改为MA； （3）删除视图student_view中学号=xx007的学生的信息。 4、删除视图 （1）删除视图sc_view； 三、实验环境硬件PC机软件Windows操作系统、MySQL Server6.0和Navicat forMySQL9.0 四、算法描述及实验步骤 1、创建视图 （1）创建zhz数据库上的视图student_view，视图包含学号、姓名、系CREATE VIEWstudent_view ASSELECT Sno,Sname,Sdept FROMstudent （2）创建zhz数据库上的视图sc_view，视图包含sc表的全部列CREATE VIEWsc_view ASSELECT*FROM sc 2、查询视图 （1）从视图sc_view中查看选修了3号课程的学生的学号SELECT SnoFROMsc_view WHERECno=3 （2）从视图student_view中查询姓名为“王敏”的学生所在的系SELECT Sname,SdeptFROMstudent_view WHERESname=王敏 3、更新视图 （1）向视图sc_view中插入一行数据xx005，5，87；INSERT INTOsc_view VALUES(xx005,5,87) （2）将视图student_view中学号=xx002的学生的系改为MA；UPDATE student_view SETSdept=MAWHERE Sno=xx002 （3）删除视图student_view中学号=xx006的学生的信息；DELETE FROMstudent_view WHERE Sno=xx006 4、删除视图 （1）删除视图sc_view；DROP VIEWsc_view 5、在界面工具中操作视图 （1）创建视图选择chk数据库，单击“视图”图标，单击“新建视图”，单击“视图创建工具”，双击“student”表，选择“Sno，Sname，Sdept”，然后单击“保存”，输入视图名“student_view”，单击“确定”按钮。 （2）查询视图双击视图名，可直接查看视图中的数据 （3）删除视图在视图窗体中，选择要删除的视图，点击鼠标右键，选择“删除视图”，然后单击“删除”. 五、实验结果 1、创建视图 (1) (2) 2、查询视图 (1). (2). 3、更新视图 (1). (2). (3). 4、在界面工具中操作视图 六、总结通过这次实验，掌握了视图的创建、查询、修改以及删除等基本操作；了解了视图与基本表的区别，视图是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中；巩固了视图的基本概念及其作用。 实验五触发器、存储过程操作实验 一、实验目的和要求 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_Ine。 三、实验环境硬件PC机软件Windows操作系统、MySQL Server6.0和Navicat forMySQL9.0 四、算法描述及实验步骤 1、存储过程 （1）创建存储过程，使用student表中的学生人数来初始化一个局部变量，并调用这个存储过程delimiter$CREATE PROCEDURETEST(OUT number1INTEGER)BEGIN DECLAREnumber2INTEGER;SET number2=(SELECT COUNT(*)FROMstudent);SET number1=number2;END$delimiter调用该存储过程CALL TEST(number)查看结果SELECTnumber （2）创建存储过程，比较两学生的年龄，若前者比后者大就输出0，否则输出1delimiter$CREATE PROCEDUREPA(IN Sno1varchar (9),IN Sno2varchar (9),OUT BJINTEGER)BEGIN DECLARESR1,SR2FLOAT (10);SELECT SageINTO SR1FROMstudentWHERESno=Sno1;SELECT SageINTO SR2FROMstudentWHERESno=Sno2;IF Sno1Sno2THEN SETBJ=0;ELSE SETBJ=1;END IF;END$delimiter调用该存储过程CALL PA(xx002,xx003,BJ);查看结果SELECTBJ 2、触发器 （1）创建触发器，在student表中删除学生信息的同时将sc表中该学生的选课信息删除，以确保数据的完整性CREATE TRIGGERDELETE_SM AFTERDELETE ONstudent FOREACH ROWDELETE FROMscWHERESno=OLD.Sno删除student表中的一行数据，然后查看sc表的变化DELETE FROMstudentWHERESno=xx003观察sc表的变化； （2）假设student1表和student表的结构和内容都相同，在student上创建一个触发器，如果添加一个学生的信息，该信息也会被添加到student1表中delimiter$CREATE TRIGGERstuden