1、数据库实验报告2010211122班 李济汉 10210737 20号实验一：用SQL Server实现数据库设计建立数据库：CREATE DATABASE teachingON PRIMARY(NAME=teaching_data,FILENAME=e:李济汉teaching.mdf,SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=10%);LOG ON(NAME=teaching_log,FILENAME=e:李济汉teaching.ldf,SIZE=1MB,MAXSIZE=2MB,FILEGROWTH=10%);建立数据类型：CREATE TYPE student_noF
2、ROM CHAR(10) NOT NULL;CREATE TYPE member_noFROM INTEGER;CREATE TYPE shortstringFROM VARCHAR(15);建立表：班级表CREATE TABLE class(CLname CHAR(10) PRIMARY KEY,CLmajor CHAR(20),CLgrade CHAR(4),CLnumber INTEGER,CLteacher CHAR(10);学生表CREATE TABLE student(Sno CHAR(8) PRIMARY KEY,Sname CHAR(20) UNIQUE,Sclass CHAR
3、(10) NOT NULL,Sgender CHAR(2) DEFAULT 男,Sbirthday DATETIME,Sface CHAR(10),Splace CHAR(6),Stime DATETIME,Sphone CHAR(15) DEFAULT unknown,Sexp VARCHAR(200),FOREIGN KEY (Sclass) REFERENCES class(CLname); CREATE DEFAULT Sphone AS unknown课程表CREATE TABLE lesson(Lnum CHAR(10) PRIMARY KEY,Lname CHAR(20) UNI
4、QUE,Lkind CHAR(10),Lmajor CHAR(10),Lgrade CHAR(4),Ltime DATETIME,Lscore CHAR(2),Lperiod CHAR(3);课程安排表CREATE TABLE lessonarrange(LAnum CHAR(10) PRIMARY KEY,LAtime DATETIME,LAclass CHAR(10),LAteacher CHAR(10),FOREIGN KEY (LAclass) REFERENCES class(CLname),FOREIGN KEY (LAnum) REFERENCES lesson(Lnum);选课
5、表CREATE TABLE sele(SLnum CHAR(10),SLno CHAR(8),SLscore INTEGER,FOREIGN KEY (SLnum) REFERENCES lesson(Lnum),FOREIGN KEY (SLno) REFERENCES student(Sno),PRIMARY KEY(SLnum,SLno);数据库各表关系图：执行插入操作：INSERT INTO class(CLname,CLmajor,CLgrade,CLnumber,CLteacher)VALUES(2010211127,通信工程,大二,32,赵一超);INSERT INTO clas
6、s(CLname,CLmajor,CLgrade,CLnumber,CLteacher)VALUES(2010211122,通信工程,大二,28,韩宇宏);INSERT INTO student(Sno,Sname, Sclass,Sgender,Sage,Sface,Splace,Stime,Sphone,Sexp)VALUES(10210737,李济汉,2010211127,男,1992-11-28,共青团员,湖南,2010-9-1,13311035011,我是一个有梦想的学生。);执行更新操作：UPDATE student SET Sclass=2010211122 WHERE Sno=
7、10210737;执行删除操作：DELETE FROM student WHERE Sno=10210737;执行查询操作：SELECT Sno,Sname,Sgender,Sage INTO stu FROM student;删除表数据：TRUNCATE TABLE创建索引：CREATE CLUSTERED INDEX SLcluster1 ON sele (SLnum,SLno);CREATE INDEX SLcluster2 ON sele(SLno);CREATE INDEX SLcluster3 ON sele(SLnum);删除索引：DROP INDEX SLcluster3;实现
8、各种查询：检索选修了课程号为C1或C2课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。SELECT student.Sname,lesson.Lname,sele.SLscoreFROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum AND (lesson.Lnum=C1 OR lesson.Lnum=C2) AND (sele. SLscore=70);检索姓“王”的所有学生的姓名和年龄。SELECT Sname AS name,Sage AS ageFROM studentWHER
9、E Sname LIKE 王%;检索没有考试成绩的学生姓名和课程名。SELECT student.Sname,lesson.LnameFROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum AND sele.SLscore= NULL;检索年龄大于女同学平均年龄的男学生姓名和年龄。SELECT Sname, Sage AS ageFROM studentWHERE Sgender=男 AND Sage(SELECT AVG(Sage) FROM student WHERE Sgender=女)
10、;创建视图：创建视图，包含所有通信工程专业的学生的信息；CREATE VIEW Tel_studentAS SELECT student.*FROM student,classWHERE student.Sclass=class.CLname AND class.CLmajor=通信工程WITH CHECK OPTION;创建视图，包含所有学生的学号，姓名，选课的课程名和成绩；CREATE VIEW Is_studentAS SELECT student.Sno,student.Sname,lesson.Lname,sele.SLscoreFROM student LEFT OUT JOIN
11、sele ON (student.Sno=sele.SLno),lesson LEFT OUT JOIN sele ON (lesson.Lnum=sele.SLnum)WITH ENCRYPTION;创建视图，包含所有课程的课程号，名，班级名称及每班选课的人数；CREATE VIEW Is_lessonAS SELECT lesson.Lnum,lesson.Lname,class.CLname,COUNT(sele.SLno)AS NumberFROM student,lesson,class,seleWHERE student.Sno=sele.SLno AND student.Scla
12、ss=class.CLname AND lesson.Lnum=sele.SLnumGROUP BY lesson.Lnum,lesson.Lname,class.CLname;实验二：SQL Server数据库设计高级内容统计有学生选修的课程门数。SELECT COUNT (DISTINCT SLnum) AS coursenumber FROM sele;求选课在四门以上的学生所选课程的平均成绩(不统计不及格的课程)。最后按降序列出平均成绩名次名单来。SELECT SLno,AVG(SLscore) AS avggradeFROM seleWHERE SLscore=60GROUP BY
13、SLno HAVING COUNT(SLnum)=4ORDER BY avggrade DESC;统计每门指选课程的学生选修人数(超过10人的课程才统计),要求输出课程号,课程名和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。SELECT SLnum,Lname,COUNT(SLno) SLnumberFROM sele,lessonWHERE lesson.Lnum=sele.SlnumGROUP BY SLnum,Lname HAVING COUNT(SLno)=4 -由于数据较少，将改为选课人数大于等于人ORDER BY COUNT(SLno)DESC,SLnum AS
14、C;检索所学课程包含了学生s3所选的所有课程的学生学号。SELECT DISTINCT SLno FROM sele sele_1 WHERENOT EXISTS(SELECT * FROM sele sele_2 WHERE SLno=10210737 ANDNOT EXISTS(SELECT * FROM sele sele_3WHERE sele_3.SLno=sele_1.SLno AND sele_3.SLnum=sele_2.SLnum);定义check约束：ALTER TABLE sele ADD CONSTRAINT checksele CHECK(SLscore=0 AND
15、SLscore=100);ALTER TABLE sele DROP CONSTRAINT checksele;定义规则：CREATE RULE coursetype as coursetype in(必修,选修,必选);sp_bindrule coursetype,lesson.Lkind;sp_unbindrule lesson.Lkind;DROP RULE coursetype;定义存储过程：定义存储过程，实现学生学号，姓名，课程名和成绩的查询；CREATE PROC student_query1 ASSELECT student.Sno,Sname,Lnum,SLscoreFROM
16、student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum;EXEC student_query1;定义存储过程，实现按某人指定课程的成绩；CREATE PROC student_query2 sname varchar(20),cname varchar(30)ASSELECT student.Sno,Sname,Lnum,SLscoreFROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnumAND Sna
17、me=sname AND Lnum=cname;EXEC student_query2 李济汉,C2;定义存储过程，在查询某人所选修的课程和成绩，指定姓名时，可以只给出姓；CREATE PROC student_query3 sname varchar(20)=%ASSELECT student.Sno,Sname,Lnum,SLscoreFROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnumAND Sname LIKE sname;EXEC student_query3 周%;定义存储过程，
18、计算并查看指定学生的总学分。ALTER TABLE lesson ALTER COLUMN Lscore FLOAT;CREATE PROC student_query4 sname varchar(20),total integer OUTPUTASSELECT total=SUM(Lscore)FROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnumAND Sname=sname;DECLARE total FLOATEXEC student_query4 朱建华,total OUTPUT;
19、SELECT total AS Total;定义触发器：使用CREATE TRIGGER命令对学生选课信息表创建插入触发器，实现的功能是：当向学生选课信息表中插入一记录时，检查该记录的学号在学生表中是否存在，检查该记录的课程编号是否在课程表中存在，若有一项为否，则提示“违背数据的一致性”错误信息，并且不允许插入。CREATE TRIGGER checkin ON dbo.seleFOR INSERTASIF NOT EXISTS(SELECT * FROM student,lesson,insertedWHERE student.Sno=inserted.SLno AND lesson.Lnu
20、m=inserted.SLnum)BEGIN RAISERROR(违背数据的一致性,16,1)ROLLBACKENDELSESELECT * FROM seleINSERT INTO sele VALUES(10310435,C5,94)使用CREATE TRIGGER 命令对学生信息表创建删除触发器，实现的功能是：当在学生信息表中删除一条记录时，同时删除学生选课信息表中相应的记录。CREATE TRIGGER del_trigger ON dbo.studentFOR DELETEAS DELETE FROM seleWHERE sele.SLno IN (SELECT Sno FROM d
21、eleted)实验三：在PB中实现第一个应用程序d_student学生信息表：计算并增加年龄（当今年份-出生年份）项：d_score学生成绩：学生信息按年龄降序排列，并可查询其各科成绩：面板：dw_1.SetTransObject(sqlca)dw_1.retrieve()信息显示区：string s1int rowrow=GetRow(dw_1)if row1 thenm = m - 1dw_1.scrolltorow(m)elsemessagebox(提示,这是第一个记录)end if下一个：int m,nn=dw_1.rowcount()m=dw_1.getrow()if mn then
22、m = m + 1dw_1.scrolltorow(m)elsemessagebox(提示,这是最后一个记录)end if最后一个：int nn=dw_1.rowcount()dw_1.scrolltorow(n)自由显示风格：if rb_3.checked=true thendw_1.dataobject=d_student_freeelsedw_1.dataobject=d_course_freeend ifdw_1.settrans(sqlca)dw_1.retrieve()表格显示风格：if rb_3.checked=true thendw_1.dataobject=d_student
23、_gridelsedw_1.dataobject=d_course_gridend ifdw_1.settrans(sqlca)dw_1.retrieve()数据插入更新：学生表：if rb_1.checked=true thendw_1.dataobject=d_student_freeelsedw_1.dataobject=d_student_gridend ifdw_1.settrans(sqlca)dw_1.retrieve()课程表：if rb_1.checked=true thendw_1.dataobject=d_course_freeelsedw_1.dataobject=d_
24、course_gridend ifdw_1.settrans(sqlca)dw_1.retrieve()学生信息查询窗口firstpb：由学号查询由姓名查询由列表查询查询不存在的人插入数据成功性别输入错误面板：string strddlb_1.clear()declare cur_sno Cursor forselect Sno from student order by Sno;open cur_sno;do while sqlca.sqlcode=0fetch cur_sno into :str;if sqlca.sqlcode0 then exitddlb_1.additem(str)l
25、oop学生下拉列表:string sno,sname,sexint syear,agesno=ddlb_1.textselect Sname,Sgender,year(Sbirthday)into :sname,:sex,:syearfrom studentwhere sno=:sno or sname=:sname;sle_2.text =snamesle_3.text =sexsle_4.text=string(year(today()-syear)查询：string sno,sname,sex,s2,m=0int syear,agesno=sle_1.textsname=sle_1.te
26、xtselect Sno,Sname,Sgender,year(Sbirthday),Snointo :sno,:sname,:sex,:syear,:mfrom studentwhere Sno=:sno or Sname=:sname;if m=0 thensle_2.text =无sle_5.text=无sle_3.text =无sle_4.text=无elsesle_2.text =snamesle_5.text=snosle_3.text =sexsle_4.text=string(year(today()-syear)end ifs2=sle_5.textco.settransob
27、ject(sqlca)co.retrieve(s2)退出：close(parent)插入：string sno,sname,sgender,sbirthdaysno=sno_in.textsname=sle_9.textsgender=sle_8.textsbirthday=sle_7.textselect Snointo :snofrom studentwhere sno =:snoUSING SQLCA;if SQLCA.SQLCode=0 thenmessagebox(警告,学号输入重复)returnend ifif sgender 男 and sgender 女 thenmessage
28、box(警告,性别输入错误)returnend ifINSERT INTO student(Sno,Sname,Sgender,Sbirthday)VALUES (:sno,:sname,:sgender,:sbirthday)USING SQLCA;IF SQLCA.SQLCode0 THENMessagebox(失败,数据库插入失败+SQLCA.SQLERRTEXT)ROLLBACK USING SQLCA;ELSEMessagebox(成功,数据库插入完成)COMMIT USING SQLCA;END IF新功能：模糊查询string s1s1=sle_1.textdw_1.setfil
29、ter(Sname like %+s1+%) dw_1.retrieve()实验总结：经过这次实验，我对于数据库有了更深入的理解，基本掌握了数据库的各种操作（如创建数据类型、表、索引、视图、约束、存储过程、触发器等、进行数据插入、查询、删除等），这些对于今后我做一个大型系统，尤其是网络系统有极大帮助，因为网络上的海量数据都是存储于数据库中，掌握数据库管理方法是十分必要的。另一方面，我对PB这一软件也有了基本认识，学会了运用它创建系统登录查询等窗口，以及如何利用它与数据库进行关联，还创造性地实现了修改密码、模糊查询等功能。在实验中我也遇到了很多问题：1、在创建并执行触发器时，插入一个不合法数据，
30、程序并没有在触发器的“检验一致性”处终止，而是在主键约束处终止了，于是我将主键约束删除，插入数据时虽然出现了“不符合数据一致性要求”的警告，但数据还是插进了数据库中，于是我和同学讨论，最终发现原来在创建触发器代码中需要加入一句ROLLBACK，即若触发器执行失败，就要回滚到数据库执行操作之前，这样就不会进行插入，而在触发器处真正终止。2、在PB实验中，进行人员插入时，一开始总是报错，但我并没有发现代码上的错误，后来我仔细查阅历史操作，发现原来问题出在数据库本身，我插入的数据溢出了某项的最大串长的限制，因此插入提示错误“更新数据库失败”，经修改后，成功插入了数据。在另一个插入窗口中，我遇到了同样
31、的问题，这次我很容易就发现了问题出在“生日”的数据类型上，将它由datetime改为string类型，便可成功插入。3、实现模糊查询时一开始我运用Sname like sle_1.text+“%”的方式来实现，但并不成功，于是我在网上搜集资料，发现可以利用一个函数Setfilter来进行实现，即把所有数据按所给条件进行过滤，从而筛选出符合要求的数据，这让我获益颇多。4、在实现修改密码时，我曾一度为如何记忆当前用户名而苦恼，因为一旦登录了系统，用户的用户名便没有记录在PB内，这样就无法验证修改密码时的旧密码一致性了，于是我想到了全局变量，即利用全局变量获得当前用户名，我在网上查找PB中定义全局变量的方法，终于发现在Global Variables这一标签下可以定义全局变量，这样便成功实现了用户密码的校验。经过这些问题的考验，我自身编程与运用数据库的能力有了很大提高，并且对数据库编程的兴趣也更加浓厚，将来我希望实现一个小型搜索引擎，因此在获得海量数据之后需要用数据库对数据进行高效处理，因此我要继续钻研数据库编程的技术，学会使用MySQL等通用数据库，相信我一定会在这方面有所成果！
