數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告

1、數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告2010211122班 李濟(jì)漢 10210737 20號(hào)實(shí)驗(yàn)一：用SQL Server實(shí)現(xiàn)數(shù)據(jù)庫設(shè)計(jì)建立數(shù)據(jù)庫：CREATE DATABASE teachingON PRIMARY(NAME='teaching_data',FILENAME='e:李濟(jì)漢teaching.mdf',SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=10%);LOG ON(NAME='teaching_log',FILENAME='e:李濟(jì)漢teaching.ldf',SIZE=1MB,MAXSIZE=2MB,FILEGR

2、OWTH=10%);建立數(shù)據(jù)類型：CREATE TYPE student_noFROM CHAR(10) NOT NULL;CREATE TYPE member_noFROM INTEGER;CREATE TYPE shortstringFROM VARCHAR(15);建立表：班級(jí)表CREATE TABLE class(CLname CHAR(10) PRIMARY KEY,CLmajor CHAR(20),CLgrade CHAR(4),CLnumber INTEGER,CLteacher CHAR(10);學(xué)生表CREATE TABLE student(Sno CHAR(8) PRIMA

3、RY KEY,Sname CHAR(20) UNIQUE,Sclass CHAR(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&#

4、39;課程表CREATE TABLE lesson(Lnum CHAR(10) PRIMARY KEY,Lname CHAR(20) UNIQUE,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) R

5、EFERENCES class(CLname),FOREIGN KEY (LAnum) REFERENCES lesson(Lnum);選課表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);數(shù)據(jù)庫各表關(guān)系圖：執(zhí)行插入操作：INSERT INTO class(CLname,CLmajor,CLgrade

6、,CLnumber,CLteacher)VALUES('2010211127','通信工程','大二',32,'趙一超');INSERT INTO class(CLname,CLmajor,CLgrade,CLnumber,CLteacher)VALUES('2010211122','通信工程','大二',28,'韓宇宏');INSERT INTO student(Sno,Sname, Sclass,Sgender,Sage,Sface,Splace,Stime,Sp

7、hone,Sexp)VALUES('10210737','李濟(jì)漢','2010211127','男',1992-11-28,'共青團(tuán)員','湖南',2010-9-1,'#39;,'我是一個(gè)有夢(mèng)想的學(xué)生。');執(zhí)行更新操作：UPDATE student SET Sclass='2010211122' WHERE Sno='10210737'執(zhí)行刪除操作：DELETE FROM student WHERE Sno='

8、10210737'執(zhí)行查詢操作：SELECT Sno,Sname,Sgender,Sage INTO stu FROM student;刪除表數(shù)據(jù)：TRUNCATE TABLE創(chuàng)建索引：CREATE CLUSTERED INDEX SLcluster1 ON sele (SLnum,SLno);CREATE INDEX SLcluster2 ON sele(SLno);CREATE INDEX SLcluster3 ON sele(SLnum);刪除索引：DROP INDEX SLcluster3;實(shí)現(xiàn)各種查詢：檢索選修了課程號(hào)為C1或C2課程，且成績(jī)高于或等于70分的學(xué)生的姓名，課程

9、名和成績(jī)。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);檢索姓“王”的所有學(xué)生的姓名和年齡。SELECT Sname AS name,Sage AS ageFROM studentWHERE Sname LIKE '

10、王%'檢索沒有考試成績(jī)的學(xué)生姓名和課程名。SELECT student.Sname,lesson.LnameFROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum AND sele.SLscore= NULL;檢索年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡。SELECT Sname, Sage AS ageFROM studentWHERE Sgender='男' AND Sage>(SELECT AVG(Sage) FROM student WHERE Sgend

11、er='女');創(chuàng)建視圖：創(chuàng)建視圖，包含所有通信工程專業(yè)的學(xué)生的信息；CREATE VIEW Tel_studentAS SELECT student.*FROM student,classWHERE student.Sclass=class.CLname AND class.CLmajor='通信工程'WITH CHECK OPTION;創(chuàng)建視圖，包含所有學(xué)生的學(xué)號(hào)，姓名，選課的課程名和成績(jī)；CREATE VIEW Is_studentAS SELECT student.Sno,student.Sname,lesson.Lname,sele.SLscoreFR

12、OM student LEFT OUT JOIN sele ON (student.Sno=sele.SLno),lesson LEFT OUT JOIN sele ON (lesson.Lnum=sele.SLnum)WITH ENCRYPTION;創(chuàng)建視圖，包含所有課程的課程號(hào)，名，班級(jí)名稱及每班選課的人數(shù)；CREATE VIEW Is_lessonAS SELECT lesson.Lnum,lesson.Lname,class.CLname,COUNT(sele.SLno)AS NumberFROM student,lesson,class,seleWHERE student.Sno=s

13、ele.SLno AND student.Sclass=class.CLname AND lesson.Lnum=sele.SLnumGROUP BY lesson.Lnum,lesson.Lname,class.CLname;實(shí)驗(yàn)二：SQL Server數(shù)據(jù)庫設(shè)計(jì)高級(jí)內(nèi)容統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)。SELECT COUNT (DISTINCT SLnum) AS coursenumber FROM sele;求選課在四門以上的學(xué)生所選課程的平均成績(jī)(不統(tǒng)計(jì)不及格的課程)。最后按降序列出平均成績(jī)名次名單來。SELECT SLno,AVG(SLscore) AS avggradeFROM sele

14、WHERE SLscore>=60GROUP BY SLno HAVING COUNT(SLnum)>=4ORDER BY avggrade DESC;統(tǒng)計(jì)每門指選課程的學(xué)生選修人數(shù)(超過10人的課程才統(tǒng)計(jì)),要求輸出課程號(hào),課程名和選修人數(shù),查詢結(jié)果按人數(shù)降序排列,若人數(shù)相同,按課程號(hào)升序排列。SELECT SLnum,Lname,COUNT(SLno) SLnumberFROM sele,lessonWHERE lesson.Lnum=sele.SlnumGROUP BY SLnum,Lname HAVING COUNT(SLno)>=4 -由于數(shù)據(jù)較少，將改為選課人數(shù)大

15、于等于人ORDER BY COUNT(SLno)DESC,SLnum ASC;檢索所學(xué)課程包含了學(xué)生s3所選的所有課程的學(xué)生學(xué)號(hào)。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 sel

16、e ADD CONSTRAINT checksele CHECK(SLscore>=0 AND SLscore<=100);ALTER TABLE sele DROP CONSTRAINT checksele;定義規(guī)則：CREATE RULE coursetype as coursetype in('必修','選修','必選');sp_bindrule 'coursetype','lesson.Lkind'sp_unbindrule 'lesson.Lkind'DROP RULE cou

17、rsetype;定義存儲(chǔ)過程：定義存儲(chǔ)過程，實(shí)現(xiàn)學(xué)生學(xué)號(hào)，姓名，課程名和成績(jī)的查詢；CREATE PROC student_query1 ASSELECT student.Sno,Sname,Lnum,SLscoreFROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnum;EXEC student_query1;定義存儲(chǔ)過程，實(shí)現(xiàn)按某人指定課程的成績(jī)；CREATE PROC student_query2 sname varchar(20),cname varchar(30)ASSELECT st

18、udent.Sno,Sname,Lnum,SLscoreFROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnumAND Sname=sname AND Lnum=cname;EXEC student_query2 '李濟(jì)漢','C2'定義存儲(chǔ)過程，在查詢某人所選修的課程和成績(jī)，指定姓名時(shí)，可以只給出姓；CREATE PROC student_query3 sname varchar(20)='%'ASSELECT student.Sno,Sname

19、,Lnum,SLscoreFROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnumAND Sname LIKE sname;EXEC student_query3 '周%'定義存儲(chǔ)過程，計(jì)算并查看指定學(xué)生的總學(xué)分。ALTER TABLE lesson ALTER COLUMN Lscore FLOAT;CREATE PROC student_query4 sname varchar(20),total integer OUTPUTASSELECT total=SUM(Lscore

20、)FROM student,lesson,seleWHERE student.Sno=sele.SLno AND lesson.Lnum=sele.SLnumAND Sname=sname;DECLARE total FLOATEXEC student_query4 '朱建華',total OUTPUT;SELECT total AS Total;定義觸發(fā)器：使用CREATE TRIGGER命令對(duì)學(xué)生選課信息表創(chuàng)建插入觸發(fā)器，實(shí)現(xiàn)的功能是：當(dāng)向?qū)W生選課信息表中插入一記錄時(shí)，檢查該記錄的學(xué)號(hào)在學(xué)生表中是否存在，檢查該記錄的課程編號(hào)是否在課程表中存在，若有一項(xiàng)為否，則提示“違背數(shù)據(jù)

21、的一致性”錯(cuò)誤信息，并且不允許插入。CREATE TRIGGER checkin ON dbo.seleFOR INSERTASIF NOT EXISTS(SELECT * FROM student,lesson,insertedWHERE student.Sno=inserted.SLno AND lesson.Lnum=inserted.SLnum)BEGIN RAISERROR('違背數(shù)據(jù)的一致性',16,1)ROLLBACKENDELSESELECT * FROM seleINSERT INTO sele VALUES('10310435','C

22、5',94)使用CREATE TRIGGER 命令對(duì)學(xué)生信息表創(chuàng)建刪除觸發(fā)器，實(shí)現(xiàn)的功能是：當(dāng)在學(xué)生信息表中刪除一條記錄時(shí)，同時(shí)刪除學(xué)生選課信息表中相應(yīng)的記錄。CREATE TRIGGER del_trigger ON dbo.studentFOR DELETEAS DELETE FROM seleWHERE sele.SLno IN (SELECT Sno FROM deleted)實(shí)驗(yàn)三：在PB中實(shí)現(xiàn)第一個(gè)應(yīng)用程序d_student學(xué)生信息表：計(jì)算并增加年齡（當(dāng)今年份-出生年份）項(xiàng)：d_score學(xué)生成績(jī)：學(xué)生信息按年齡降序排列，并可查詢其各科成績(jī)：面板：dw_1.SetTrans

23、Object(sqlca)dw_1.retrieve()信息顯示區(qū)：string s1int rowrow=GetRow(dw_1)if row<1 then returnSelectRow (dw_1,0,False)SelectRow (dw_1,row,True)s1=dw_1.GetItemString(row,1)dw_2.settransobject(sqlca)dw_2.retrieve(s1)按年齡排序：if cbx_1.checked=TRUE thenif rb_1.checked=TRUE thendw_1.SetSort("sage A")el

24、sedw_1.SetSort("sage D")end ifdw_1.Sort()end if升序：if cbx_1.checked=TRUE thendw_1.SetSort("sage A")dw_1.Sort()end if降序：if cbx_1.checked=TRUE thendw_1.SetSort("sage D")dw_1.Sort()end if退出：CLOSE(Parent)學(xué)生信息按年齡升序排列，并可查詢其各科成績(jī)：教學(xué)管理系統(tǒng)登錄界面（teachingsystem）：設(shè)計(jì)一個(gè)登錄窗口，使用用戶在窗口的輸入信息，

25、作為登錄指定的數(shù)據(jù)庫的帳號(hào)和密碼。創(chuàng)建數(shù)據(jù)庫用戶名密碼表：登陸界面：用戶名密碼匹配錯(cuò)誤：（功能亮點(diǎn)）設(shè)置用戶名全局變量usernameGlobal Variables: string username與數(shù)據(jù)庫進(jìn)行匹配用戶名與密碼：string suser,spasswd,str="0"suser=trim(sle_1.text)spasswd=trim(sle_2.text)select Suser,Spasswd,Suserinto :suser,:spasswd,:strfrom adminwhere Suser=:suser and Spasswd=:spasswd;

26、if str="0" thenmessagebox("警告","用戶名或密碼輸入錯(cuò)誤！")returnelse username=suseropen(blog)close(parent)end if新功能：修改密碼驗(yàn)證密碼窗口（code）：錯(cuò)誤窗口（code4）：string str,suser="0"str=sle_1.textselect Suserinto :suserfrom adminwhere Spasswd=:str;if suser=username thenopen(code2)close(par

27、ent)elseopen(code4)end if設(shè)置新密碼窗口（code2）：錯(cuò)誤窗口（code3）:string s1,s2s1=sle_1.texts2=sle_2.textif s1=s2 thenupdate admin set Spasswd=:s2 where Suser=:username;close(parent)else open(code3)end if實(shí)驗(yàn)四：在PB應(yīng)用中實(shí)現(xiàn)數(shù)據(jù)處理目錄：d_course_free課程信息自由顯示:d_student_free學(xué)生信息自由顯示:d_course_grid課程信息列表顯示:d_student_grid學(xué)生信息列表顯示:教學(xué)

28、管理系統(tǒng)查詢窗口blog：信息輸入：open(w_input)學(xué)生查詢：open(firstpb)學(xué)生信息輸入窗口w_input：插入：int nn=dw_1.rowcount()dw_1.insertrow(0)dw_1.scrolltorow(n+1)更新：if dw_1.update()=1 thencommit;messagebox("成功","更新數(shù)據(jù)庫成功")elserollback;messagebox("失敗","更新數(shù)據(jù)庫失敗")end if刪除：dw_1.deleterow(0)關(guān)閉：close

29、(parent)第一個(gè)：dw_1.scrolltorow(1)上一個(gè)：int mm=dw_1.getrow()if m>1 thenm = m - 1dw_1.scrolltorow(m)elsemessagebox("提示","這是第一個(gè)記錄")end if下一個(gè)：int m,nn=dw_1.rowcount()m=dw_1.getrow()if m<n thenm = m + 1dw_1.scrolltorow(m)elsemessagebox("提示","這是最后一個(gè)記錄")end if最后一個(gè)：

30、int nn=dw_1.rowcount()dw_1.scrolltorow(n)自由顯示風(fēng)格：if rb_3.checked=true thendw_1.dataobject="d_student_free"elsedw_1.dataobject="d_course_free"end ifdw_1.settrans(sqlca)dw_1.retrieve()表格顯示風(fēng)格：if rb_3.checked=true thendw_1.dataobject="d_student_grid"elsedw_1.dataobject="

31、;d_course_grid"end ifdw_1.settrans(sqlca)dw_1.retrieve()數(shù)據(jù)插入更新：學(xué)生表：if rb_1.checked=true thendw_1.dataobject="d_student_free"elsedw_1.dataobject="d_student_grid"end ifdw_1.settrans(sqlca)dw_1.retrieve()課程表：if rb_1.checked=true thendw_1.dataobject="d_course_free"else

32、dw_1.dataobject="d_course_grid"end ifdw_1.settrans(sqlca)dw_1.retrieve()學(xué)生信息查詢窗口firstpb：由學(xué)號(hào)查詢由姓名查詢由列表查詢查詢不存在的人插入數(shù)據(jù)成功性別輸入錯(cuò)誤面板：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.sqlcode&

33、lt;>0 then exitddlb_1.additem(str)loop學(xué)生下拉列表: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='

34、;0'int syear,agesno=sle_1.textsname=sle_1.textselect 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.tex

35、t =snamesle_5.text=snosle_3.text =sexsle_4.text=string(year(today()-syear)end ifs2=sle_5.textco.settransobject(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 =:sno

36、USING SQLCA;if SQLCA.SQLCode=0 thenmessagebox("警告","學(xué)號(hào)輸入重復(fù)")returnend ifif sgender <> "男" and sgender <> "女" thenmessagebox("警告","性別輸入錯(cuò)誤")returnend ifINSERT INTO student(Sno,Sname,Sgender,Sbirthday)VALUES (:sno,:sname,:sgender,:sbirthday)USING SQLCA;IF SQLCA.SQLCode<>0 THENMessagebox("失敗","數(shù)據(jù)庫插入失敗"+SQLCA.SQLERRTEXT)ROLLBACK USING SQLCA;ELSEMessagebox("成功&quo