數(shù)據(jù)庫實驗報告

1、數(shù)據(jù)庫實驗報告2010211122班 李濟漢 10210737 20號實驗一：用SQL Server實現(xiàn)數(shù)據(jù)庫設計建立數(shù)據(jù)庫：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,FILEGR

2、OWTH=10%);建立數(shù)據(jù)類型：CREATE TYPE student_noFROM 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) 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ù)庫各表關系圖：執(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','李濟漢','2010211127','男',1992-11-28,'共青團員','湖南',2010-9-1,'#39;,'我是一個有夢想的學生。');執(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;實現(xiàn)各種查詢：檢索選修了課程號為C1或C2課程，且成績高于或等于70分的學生的姓名，課程

9、名和成績。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 studentWHERE Sname LIKE '

10、王%'檢索沒有考試成績的學生姓名和課程名。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 Sgend

11、er='女');創(chuàng)建視圖：創(chuàng)建視圖，包含所有通信工程專業(yè)的學生的信息；CREATE VIEW Tel_studentAS SELECT student.*FROM student,classWHERE student.Sclass=class.CLname AND class.CLmajor='通信工程'WITH CHECK OPTION;創(chuàng)建視圖，包含所有學生的學號，姓名，選課的課程名和成績；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)建視圖，包含所有課程的課程號，名，班級名稱及每班選課的人數(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;實驗二：SQL Server數(shù)據(jù)庫設計高級內容統(tǒng)計有學生選修的課程門數(shù)。SELECT COUNT (DISTINCT SLnum) AS coursenumber FROM sele;求選課在四門以上的學生所選課程的平均成績(不統(tǒng)計不及格的課程)。最后按降序列出平均成績名次名單來。SELECT SLno,AVG(SLscore) AS avggradeFROM sele

14、WHERE SLscore>=60GROUP BY SLno HAVING COUNT(SLnum)>=4ORDER BY avggrade DESC;統(tǒng)計每門指選課程的學生選修人數(shù)(超過10人的課程才統(tǒng)計),要求輸出課程號,課程名和選修人數(shù),查詢結果按人數(shù)降序排列,若人數(shù)相同,按課程號升序排列。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;檢索所學課程包含了學生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 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;定義存儲過程：定義存儲過程，實現(xiàn)學生學號，姓名，課程名和成績的查詢；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;定義存儲過程，實現(xiàn)按某人指定課程的成績；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 '李濟漢','C2'定義存儲過程，在查詢某人所選修的課程和成績，指定姓名時，可以只給出姓；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 '周%'定義存儲過程，計算并查看指定學生的總學分。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命令對學生選課信息表創(chuàng)建插入觸發(fā)器，實現(xiàn)的功能是：當向學生選課信息表中插入一記錄時，檢查該記錄的學號在學生表中是否存在，檢查該記錄的課程編號是否在課程表中存在，若有一項為否，則提示“違背數(shù)據(jù)

21、的一致性”錯誤信息，并且不允許插入。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 命令對學生信息表創(chuàng)建刪除觸發(fā)器，實現(xiàn)的功能是：當在學生信息表中刪除一條記錄時，同時刪除學生選課信息表中相應的記錄。CREATE TRIGGER del_trigger ON dbo.studentFOR DELETEAS DELETE FROM seleWHERE sele.SLno IN (SELECT Sno FROM deleted)實驗三：在PB中實現(xiàn)第一個應用程序d_student學生信息表：計算并增加年齡（當今年份-出生年份）項：d_score學生成績：學生信息按年齡降序排列，并可查詢其各科成績：面板：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)學生信息按年齡升序排列，并可查詢其各科成績：教學管理系統(tǒng)登錄界面（teachingsystem）：設計一個登錄窗口，使用用戶在窗口的輸入信息，

25、作為登錄指定的數(shù)據(jù)庫的帳號和密碼。創(chuàng)建數(shù)據(jù)庫用戶名密碼表：登陸界面：用戶名密碼匹配錯誤：（功能亮點）設置用戶名全局變量usernameGlobal Variables: string username與數(shù)據(jù)庫進行匹配用戶名與密碼：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("警告","用戶名或密碼輸入錯誤！")returnelse username=suseropen(blog)close(parent)end if新功能：修改密碼驗證密碼窗口（code）：錯誤窗口（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設置新密碼窗口（code2）：錯誤窗口（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實驗四：在PB應用中實現(xiàn)數(shù)據(jù)處理目錄：d_course_free課程信息自由顯示:d_student_free學生信息自由顯示:d_course_grid課程信息列表顯示:d_student_grid學生信息列表顯示:教學

28、管理系統(tǒng)查詢窗口blog：信息輸入：open(w_input)學生查詢：open(firstpb)學生信息輸入窗口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)關閉：close

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

30、int nn=dw_1.rowcount()dw_1.scrolltorow(n)自由顯示風格：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()表格顯示風格：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ù)插入更新：學生表：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()學生信息查詢窗口firstpb：由學號查詢由姓名查詢由列表查詢查詢不存在的人插入數(shù)據(jù)成功性別輸入錯誤面板：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學生下拉列表: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("警告","學號輸入重復")returnend ifif sgender <> "男" and sgender <> "女" thenmessagebox("警告","性別輸入錯誤")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