軟件工程專業(yè)實(shí)驗(yàn)報(bào)告

題目 數(shù)據(jù)定義——基本表 《數(shù)據(jù)庫(kù)原理》實(shí)驗(yàn)報(bào)告 姓名 班級(jí) 日期 實(shí)驗(yàn)環(huán)境： 操作系統(tǒng) Microsoft Windows 7 (位/Service Pack 1) CPU (英特爾)Intel(R) Core(TM) i7-4710MQ CPU @ 2.50GHz(2501 MHz) 內(nèi)存 8.00 GB ( 1600 MHz) 數(shù)據(jù)庫(kù) Microsoft SQL Server 2012 實(shí)驗(yàn)內(nèi)容與完成情況： 一、實(shí)驗(yàn)?zāi)康?本次實(shí)驗(yàn)了解DDL語(yǔ)言的CREATE、DROP、ALTER對(duì)表進(jìn)行操作，學(xué)會(huì)在SQL Server 2012中使用DDL語(yǔ)言進(jìn)行對(duì)表的創(chuàng)建、刪除和改動(dòng)。 二、實(shí)驗(yàn)內(nèi)容 1.在文本編譯窗口編寫SQL語(yǔ)句，將下列數(shù)據(jù)分別插入Student，Course，SC和Teacher表中，操作方法同上。 CREATE TABLE Student( Sno VARCHAR(9) PRIMARY KEY, Sname VARCHAR(20) UNIQUE, Ssex VARCHAR(2) CHECK (Ssex='男'or Ssex='女'), Sage SMALLINT , Sdept VARCHAR(20), ) 1

CREATE TABLE Course( Cno VARCHAR(4) PRIMARY KEY, Cname VARCHAR(40), Cpno VARCHAR(4) , Ccredit SMALLINT , FOREIGN KEY (Cno) REFERENCES Course(Cno) ) CREATE TABLE SC( Sno VARCHAR(9) , Cno VARCHAR(4), Grade SMALLINT CHECK (Grade>=0 AND Grade<=100) , PRIMARY KEY (SnO,Cno), FOREIGN KEY (Sno,Cno) REFERENCES SC(Sno,Cno)

2

) CREATE TABLE Teacher( Tno Varchar(6) PRIMARY KEY, Tname Varchar(20), Tsex VARCHAR(2) CHECK (Tsex='男'or Tsex='女'), Tage INT, Tdept VARCHAR(20), Ttitles VARCHAR(20), Twage INT, Tdno VARCHAR(6), FOREIGN KEY (Tdno) REFERENCES Teacher(Tno), ) 3

2.在數(shù)據(jù)里面插入數(shù)據(jù)； INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215121','李勇','男',20,'CS') INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215122','劉晨','女',19,'CS') INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215123','王敏','女',18,'MA') INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215125','張立','男',19,'IS') INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215126','歐陽(yáng)麗','女',21,'FL') INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('1','數(shù)據(jù)庫(kù)',NULL,4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('2','數(shù)學(xué)',NULL,2) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('3','信息系統(tǒng)',NULL,4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('4','操作系統(tǒng)',NULL,3) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('5','數(shù)據(jù)結(jié)構(gòu)',NULL,4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('6','數(shù)據(jù)處理',NULL,2) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('7','PASCAL',NULL,4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('8','DB_Design',NULL,2) UPDATE Course SET Cpno='5' WHERE Cno='1'

4

UPDATE Course SET Cpno='1' WHERE Cno='3' UPDATE Course SET Cpno='6' WHERE Cno='4' UPDATE Course SET Cpno='7' WHERE Cno='5' UPDATE Course SET Cpno='6' WHERE Cno='7' UPDATE Course SET Cpno='1' WHERE Cno='8' INSERT SC(Sno,Cno,Grade) VALUES('200215121','1',92) INSERT SC(Sno,Cno,Grade) VALUES('200215121','2',85) INSERT SC(Sno,Cno,Grade) VALUES('200215121','3',88) INSERT SC(Sno,Cno,Grade) VALUES('200215122','2',90) INSERT SC(Sno,Cno,Grade) VALUES('200215122','3',80) INSERT SC(Sno,Cno,Grade) VALUES('200215122','1',NULL) INSERT SC(Sno,Cno,Grade) VALUES('200215123','2',50) INSERT SC(Sno,Cno,Grade) VALUES('200215123','3',70) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('110001','鐘靈','女',27,'CS','講師',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('110002','楊毅','男',42,'CS','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('110003','周倩','女',25,'CS','講師',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('110005','陳文茂','男',48,'CS','教授',4000,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('120001','江南','男',30,'IS','副教授',3500,NULL)

5

INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('120002','劉洋','男',28,'IS','講師',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('120003','汪明','男',44,'IS','教授',4000,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('120004','張蕾','女',35,'IS','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('130001','鄒佳羽','女',25,'MA','講師',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('130002','王力','男',30,'MA','講師',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('130003','王小峰','男',35,'MA','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('130004','魏昭','男',40,'MA','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140001','王力','男',32,'FL','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140002','張小梅','女',27,'FL','講師',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140003','吳婭','女',27,'FL','講師',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140004','陳姝','女',35,'FL','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140005','周斌','男',44,'FL','教授',3500,NULL) UPDATE Teacher SET Ttitles='教授' WHERE Tno='140005' UPDATE Teacher SET Tdno='110005' WHERE Tno='110001' UPDATE Teacher SET Tdno='110005' WHERE Tno='110002' UPDATE Teacher SET Tdno='110005' WHERE Tno='110003'

6

UPDATE Teacher SET Tdno='110005' WHERE Tno='110005' UPDATE Teacher SET Tdno='120003' WHERE Tno='120001' UPDATE Teacher SET Tdno='120003' WHERE Tno='120002' UPDATE Teacher SET Tdno='120003' WHERE Tno='120003' UPDATE Teacher SET Tdno='120003' WHERE Tno='120004' UPDATE Teacher SET Tdno='130003' WHERE Tno='130001' UPDATE Teacher SET Tdno='130003' WHERE Tno='130002' UPDATE Teacher SET Tdno='130003' WHERE Tno='130003' UPDATE Teacher SET Tdno='130003' WHERE Tno='130004' UPDATE Teacher SET Tdno='140005' WHERE Tno='140001' UPDATE Teacher SET Tdno='140005' WHERE Tno='140002' UPDATE Teacher SET Tdno='140005' WHERE Tno='140003' UPDATE Teacher SET Tdno='140005' WHERE Tno='140004' UPDATE Teacher SET Tdno='140005' WHERE Tno='140005' 3.在文本編譯窗口編寫SQL語(yǔ)句，完成： （1）求全體學(xué)生的學(xué)號(hào)和姓名； SELECT Sno,SnameFROM Student （2）求數(shù)學(xué)系學(xué)生的學(xué)號(hào)和姓名； SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept LIKE'MA'

7

（3）求選修了課程的學(xué)生學(xué)號(hào)； SELECT DISTINCT SnoFROM SC （4）求開設(shè)的課程號(hào)和課程名； SELECT Cno,CnameFROM Course （5）求計(jì)算機(jī)系教師的姓名和性別。 SELECT Tname,TsexFROM Teacher 8

4.編寫SQL語(yǔ)句實(shí)現(xiàn)刪除Teacher表。 DROP TABLE Teacher 三、出現(xiàn)的問(wèn)題及其解決方案(列出遇到的問(wèn)題和解決辦法，列出沒(méi)有解決的問(wèn)題) 問(wèn)題1：遇到外鍵時(shí)先輸入外鍵時(shí)數(shù)據(jù)錄入會(huì)失敗 分析原因：外鍵后錄入的話參考建就會(huì)出問(wèn)題 解決辦法：先錄入外鍵在錄入主鍵

9