數(shù)據(jù)庫實驗前幾個解答

1、上機實驗一 數(shù)據(jù)庫的管理- 創(chuàng)建數(shù)據(jù)庫CREATE DATABASE teachingDataAON PRIMARY(name='teachingDataA', filename='e:teachingDataA.mdf') LOG ON(name='teachingDataA_log', filename='e:teachingDataA_log.ldf')- 刪除數(shù)據(jù)庫-DROP DATABASE teachingDataA實驗二 數(shù)據(jù)表的管理 ( 一)USE teachingDataA- 自定義數(shù)據(jù)類型 'myID&

2、#39;CREATE TYPE myID FROM char(6) NOT NULL- 自定義數(shù)據(jù)類型 'myName1' CREATE TYPE myName1 FROM nvarchar(10) NOT NULL- 自定義數(shù)據(jù)類型 'myName2' CREATE TYPE myName2 FROM char(20) NOT NULL- 自定義數(shù)據(jù)類型 'myDept'CREATE TYPE myDept FROM char(50)- 創(chuàng)建數(shù)據(jù)表 TInfoCREATE TABLE TInfo(TID myID PRIMARY KEY,TNa

3、me myName1,Sex char(2) DEFAULT '男' CHECK(sex=' 男' or sex=' 女 '),Dept myDept,Birthday smalldatetime,Title varchar(10),homeTel nchar(8),MPhone nchar(11) - 或/*CREATE TABLE TInfo(TID myID PRIMARY KEY, TName myName1, Sex char(2) DEFAULT '男',Dept myDept, Birthday smalldatet

4、ime, Title varchar(10), homeTel nchar(8), MPhone nchar(11), CONSTRAINT CK_SEX CHECK(sex=' 男 or女')sex=')*/- 創(chuàng)建學生表 SInfoCREATE TABLE SInfo(SID myID PRIMARY KEY, SName myName1,Sex char(2) DEFAULT '男' CHECK(sex=' 男' or sex=' 女 '),Birthday smalldatetime,Dept myDept, ma

5、jor varchar(30), Class nchar(18), IDCardNo char(18)- 創(chuàng)建課程表 CInfo CREATE TABLE CInfo(CID myID PRIMARY KEY, CName myName2, CCredit tinyint, CProperty varchar(10)- 創(chuàng)建選修表 ScoreInfo CREATE TABLE ScoreInfo(CID myID FOREIGN KEY REFERENCES CInfo(CID), SID myID FOREIGN KEY REFERENCES SInfo(SID), TID myID, Sc

6、ore numeric(3,1), Schyear char(9), term char(1),FOREIGN KEY(TID) REFERENCES TInfo(TID), PRIMARY KEY(CID,SID)- 修改表 SInfo 表的表結(jié)構，設置 IDCardNo 值為唯一的。 ALTER TABLE SInfoADD CONSTRAINT UQ_Card UNIQUE(IDCardNo)- 修改表 ScoreInfo 的表結(jié)構，設置 term 值只能為 1 或 2，默認值為 1。ALTER TABLE ScoreInfoADD CONSTRAINT CK_term CHECK(te

7、rm='1' OR term='2')ALTER TABLE ScoreInfoADD CONSTRAINT DF_term DEFAULT('1') FOR term- 在表 SInfo 中添加一字段 EntrData( 即入學日期 ) ，要求該輸入范圍在 1900-1-1至 2079-12-31 。之間ALTER TABLE SInfoADD EntrDate SmallDateTime- 修改表 TInfo 的表結(jié)構，要求刪除其中的 Birthday 字段。ALTER TABLE TInfoDROP COLUMN Birthday- 修改表

8、 TInfo 表的表結(jié)構，其中的字段 homeTel 只允許為數(shù)字， 且第 1 位數(shù)不 能為' 0'。ALTER TABLE TInfoADD CONSTRAINT CK_ThomeTelCHECK(homeTel like'1-90-90-90-90-90-90-90-9')- 修改表 TInfo 的表結(jié)構，增加 Birthday 字段。ALTER TABLE TInfoADD Birthday datetime- 向教師表插入數(shù)據(jù)INSERT INTO TInfo(TID, TName, sex, Dept, Birthday, Title, homeTel

9、)VALUES('101001',' 施華 ',' 男','','1962-5-1','教授 ','43562634')- 利用 T-SQL 命令語句在表 TInfo 中輸入數(shù)據(jù)，當 TID 大于' 101000 '且小于' 101999 '時，其所在部門 Dept 為'信息管理'，當 TID 大于' 102000 '且小于' 102999 '時，其所在部門 Dept 為電子商務。UPDATE TInfo

10、SET Dept=' 信息管理 ' where TID>='101001' andTID<='101999' UPDATE TInfoSET Dept=' 電子商務 ' where TID>='102001' and TID<='102999'實驗三 數(shù)據(jù)表的管理（二）- 利用 T-SQL 命令語句修改數(shù)據(jù)表 SInfo, 取消對性別 sex 默認字段值的設置。 ALTER TABLE SInfoDROP CONSTRAINT DF_SInfo_Sex_0519C6AF- 利

11、用 T-SQL 命令語句移除表 ScoreInfo 的 TID 外鍵引用，移除表 TInfo 的主鍵 設置。ALTER TABLE ScoreInfoDROP CONSTRAINT FK_ScoreInfo_TID_0CBAE877- 外 鍵 ALTER TABLE TInfoDROP CONSTRAINT PK_TInfo_00551192- 主鍵- 利用 T-SQL 命令語句更改表 SInfo 中的字段 SID 名為 SNO 。EXEC sp_rename 'SInfo.SID','SNO','COLUMN'- 利用 T-SQL 命令為表 T

12、Info 按教師工號( TID )升序建立一個聚集索引 clu_tidCREATE CLUSTERED INDEX clu_tid ON TInfo(TID ASC )- 刪除索引 clu_tidDROP INDEX TInfo.clu_tid- 利用 T-SQL 命令為表 TInfo 中的 Dept 和 TName 兩個字段創(chuàng)建一個簡單的復合 索引 ix_dptnm 。CREATE NONCLUSTERED INDEX ix_dptnm ON TInfo(Dept,TName)- 利用 T-SQL 命令為表 TInfo 中的 HomeTel 字段創(chuàng)建一個唯一索引 unq_hometelCRE

13、ATE UNIQUE INDEX unq_hometel ON TInfo(HomeTel)- 利用 T-SQL 命令為表 TInfo 中的 TName 字段創(chuàng)建降序的非聚集索引。CREATE NONCLUSTERED INDEX ix_Tname ON TInfo(TName DESC)- 禁用表 TInfo 的索引 clu_tid 。ALTER INDEX clu_tidON TInfoDISABLE- 啟用索引表 TInfo 的索引 clu_tid 。ALTER INDEX clu_tidON TInfoREBUILD- 刪除已經(jīng)建立的復合索引 ix_dptnm 。DROP INDEX

14、ix_dptnmON TInfo實驗四 數(shù)據(jù)的查詢- （一）基本查詢-1 、查詢 TchInfo 表中教師的工號 TID 、姓名 TName 和職稱 Title ，要求查詢結(jié) 果列將 TID 、TName 和 Title 字段分別顯示為工號、姓名和職稱。USE teachingDataSELECT TID 工號 ,TName 姓名 ,Title 職稱FROM TchInfo-2 、查詢 TchInfo 表中教師的全部信息。SELECT *FROM TchInfo-3 、查詢 TchInfo 表前 6 行數(shù)據(jù)。SELECT TOP 6 *FROM TchInfo-4 、查詢 TchInfo 表中

15、前 50% 的數(shù)據(jù)。SELECT TOP 50 PERCENT *FROM TchInfo- （二）條件查詢-1 、在表 StuInfo 中查詢 2009 級、物電學院的學生姓名、性別和所在系。SELECT Sname,Sex,DeptFROM StuInfoWHERE Grade='2009' AND Dept=' 物電學院 '-2 、查詢 StuInfo 中出生年月介于 1989 年 9 月 1 日到 1990 年 8 月 31 日的學生 信息。SELECT *FROM StuInfoWHERE Birthday BETWEEN '1989-9-1&

16、#39; AND '1990-8-31'-3 、查詢職稱為副教授和教授的教師的信息。SELECT *FROM TchInfoWHERE Title = ' 教授 ' OR Title='副教授 '- 或SELECT *FROM TchInfoWHERE Title IN （' 教授 ',' 副教授 '）-4 、在表 StuInfo 中查詢所有學號為“ 2010 ”開頭學生的姓名 SName 和專業(yè) Major 。SELECT SName, MajorFROM StuInfoWHERE LEFT(SID,4)=

17、9;2010'-5 、在表 StuInfo 中查詢所有學號的第 5， 6 位為“ 10 ”的學生的姓名 SName 和專業(yè) Major 。SELECT SName, MajorFROM StuInfoWHERE SUBSTRING(SID,5,2)='10'- (三)排序與分組查詢-1 、查詢表 ScoreInfo 中選修了“ 1101 ”課程的學生的學號和學生成績，并按 成績進行降序排列。SELECT SID, ScoreFROM ScoreInfoWHERE CID='1101'ORDER BY Score DESC-2 、通過查詢表 TchInfo

18、 查詢各院系的講師數(shù)。SELECT Dept, COUNT(*) 講師人數(shù)FROM TchInfoWHERE Title=' 講師 'GROUP BY Dept-3 、通過表 scoreinfo 查詢各門課程的平均成績，要求顯示列名為：課程代碼和 平均成績。SELECT CID as 課程代碼 , AVG(Score) as 平均成績FROM ScoreInfoGROUP BY CID- ( 四) 聯(lián)結(jié)查詢- 查詢選修了 " 數(shù)據(jù)庫原理與應用 " 課程且成績在 70 分以上的所有學生的姓名、 成績。SELECT Sname, scoreFROM StuInf

19、o,CourseInfo,ScoreInfowhere StuInfo.SID=ScoreInfo.SID ANDScoreInfo.CID=CourseInfo.CID ANDCName = ' 數(shù)據(jù)庫原理與應用 ' ANDScore>=70- 或SELECT Sname, scoreFROM StuInfo,ScoreInfowhere StuInfo.SID=ScoreInfo.SID ANDScore>=70 ANDCID =(SELECT CID FROM CourseInfoWHERE CName = 數(shù)'據(jù)庫原理與應用 ')-( 五 )

20、 嵌套查詢- 查詢與“周志成”同學在同一個班同一個年級的學生姓名。SELECT Sname FROMStuInfo whereclass=(SELECT class FROM StuInfo WHERE Sname = 周'志成 ')AND grade=(SELECT grade FROM StuInfo WHERE Sname = 周'志成 ')實驗五 視圖-PPT 中例題-1 、建立物電學院選修了 1101 號課程的學生視圖 S_1 。CREATE VIEW S_1(SID, Sname, Score)ASSELECT StuInfo.SID, StuInf

21、o.Sname,ScoreInfo.score FROM StuInfo,ScoreInfoWHERE StuInfo.Dept= '物電學院 ' ANDStuInfo.SID = ScoreInfo.SID ANDScoreInfo.CID= '1101'-2 、創(chuàng)建學生成績 stu_scoreA 視圖，要求其中包括字段 Sid, Sname, Cname, score.CREATE VIEW S_2(SID, Sname, CName, Score)ASSELECT StuInfo.SID, StuInfo.Sname,CourseInfo.CName,Sc

22、oreInfo.scoreFROM StuInfo,ScoreInfo,CourseInfoWHERE ScoreInfo.CID = CourseInfo.CID ANDStuInfo.SID = ScoreInfo.SID;-3 、建立計算機學院學生的學號、姓名和專業(yè)的視圖student_1 。CREATE VIEW Student_1( 學號 , 姓名 , 專業(yè) )ASSELECT SID, Sname, MajorFROM StuInfoWHERE Dept= ' 計算機學院 '-4 、建立計物電學院的學號、姓名和專業(yè)的視圖 Student_2 ，并要求通過該視圖 進行

23、的更新操作只涉及物電學院學生。CREATE VIEW Student_2ASSELECT SID,Sname, Major FROM StuInfoWHERE Dept= ' 物電學院 'WITH CHECK OPTION;-5 、定義一個反映學生年齡的視圖StuAge 。CREATE VIEW StuAge(SID, Sname, Sage)ASSELECT SID, Sname, year(getdate()-year(birthday)FROM StuInfo-6 、將學生的學號及他的平均成績定義為一個視圖ScoreAvg 。CREATE VIEW ScoreAvg( 學

24、號 , 平均成績 )ASSELECT SID, AVG(Score)FROM ScoreInfoGROUP BY SID;-7 、建立物電學院選修了 1101 號課程的學生成績視圖 StuCourse1 。 CREATE VIEW StuCourse1( 學號 , 姓名 , 成績 )ASSELECT StuInfo.SID, Sname, ScoreFROM StuInfo,ScoreInfoWHERE Dept= ' 物電學院 ' AND StuInfo.SID=ScoreInfo.SID AND ScoreInfo.CID= '1101'-8 、建立物電學院

25、選修了 1101 號課程且成績在 60 分以上的學生的視圖。 CREATE VIEW Stu_Pass1ASSELECT 學號 , 姓名 , 成績FROM StuCourse1 WHERE 成績 >=60-9 、使用 T-SQL 語句來查看 teachindData 數(shù)據(jù)庫的 student_1 視圖的定義。 USE teachingData;EXEC sp_helptext student_1;-10 、從視圖 Student_1 查詢計算機學院學生的學號、姓名和專業(yè)的信息。 Use teachingData;SELECT * FROM Student_1;-11 、查詢視圖 stu_

26、scoreA, 要求按課程名稱進行排序 select *from stu_scoreB order by Cname -12 、修改視圖“ Student_1 ”為查看計算機學院學生的學號、姓名、性別和專 業(yè)。ALTER VIEW Student_1ASSELECT SID, sname, sex, MajorFROM StuInfoWHERE Dept= ' 計算機學院 '-13 、將視圖 student_1 更名為“ student_computer Use teachingDataGoEXEC sp_rename 'student_1', 'stu

27、dent_computer' -14 、利用視圖 StuCourse1 為物電學院中每位學生的數(shù)據(jù)庫原理與應用成績減 少 5 分。Update StuCourse1Set 成績 =成績 -5-15 、從 teachingData 數(shù)據(jù)庫中刪除視圖 student_2 和 sc_1 。USE teachingDataGoDrop View student_2, sc_1- 其他-1 、在 teachingData 數(shù)據(jù)庫中建立視圖 score_top ，要求在該視圖中顯示各門學 科的 CID 和 score_max (即學科的最高分)。CREATE VIEW score_topASSELECT CID, MAX(Score)最高分FROM ScoreInfoGROUP BY CID-2 、利用 teachingData 數(shù)據(jù)庫中的相關的數(shù)據(jù)表和視圖 score_top 新建一視圖 top_stu ，要求在該視圖中顯示各門學科的學科名 cname ，取得最