信息數(shù)據(jù)應用實驗報告

1、信息數(shù)據(jù)應用實驗報告實驗5數(shù)據(jù)庫查詢（I）實驗日期2011-10-26學生姓名11學號2010201245數(shù)據(jù)準備：use master;gocreate database MyDB;goUSE MyDB;gocreate table class(ID char(3) primary key,name nvarchar(10) not null);goinsert into class values('A01','08信管');insert into class values('A02','09信管');insert into c

2、lass values('A03','10信管');gocreate table student( ID int identity(1,1) not null, sex nchar(1) not null, classID char(3) not null, score int, birthday datetime, constraint zhujian primary key(ID), constraint scorecheck check(score>=0 and score<=100); goinsert into student values

3、('男','A01',65,'4/1/1982') insert into student values('女','A01',80,'9/12/1982') insert into student values('女','A02',91,'6/7/1981') insert into student values('女','A03',72,'10/15/1984') insert into student

4、 values('男','A03',83,'7/2/1982') insert into student values('男','A02',75,'1/9/1983') insert into student values('女','A02',96,'6/7/1981') insert into student values('女','A03',79,'10/15/1984') insert into s

5、tudent values('男','A03',63,'7/2/1982') insert into student values('男','A02',85,'1/9/1983')goselect * from student;go查詢命令示例：（1） 修改查詢結果的列標題名稱select ID as '學號',score as '分數(shù)' from student;select ID as '學號', DATENAME(year,birthday)+&

6、#39;年'+DATENAME(month,birthday)+'月'+DATENAME(day,birthday)+'日' as '生日' from student;go（2） 多表查詢中創(chuàng)建各表的別名select A.ID as '學號',A.score as '分數(shù)',B.name as '班級名稱' from student as A,class as Bwhere A.classID=B.ID;（3） 將查詢結果存入臨時表，并查詢臨時表select ID as '學號

7、9;,score as '分數(shù)'into #tmpTable1 from student;goselect * from #tmpTable1;go（4） 設置查詢條件1)連續(xù)值select ID as '學號',score as '分數(shù)' from student where score between 70 and 90;goselect ID as '學號',score as '分數(shù)' from student where score>=70 and score<=90;go2)離散值select

8、 ID as '班號',name as '班級名稱' from class where ID IN ('A01','A02');go2)空值select ID as '學號',score as '分數(shù)' from student where ID is not null;4)字符與日期：通配符查詢select ID as '班號',name as '班級名稱' from class where ID=A02; goselect ID as '班號',n

9、ame as '班級名稱' from class where ID LIKE %2 and ID LIKE A%;goselect ID as '班號',name as '班級名稱' from class where ID LIKE 'A_2'goselect ID as '班號',name as '班級名稱' from class where ID LIKE 'A0-92'go（5） 多表聯(lián)合查詢select A.ID as '學號',A.score as '

10、;分數(shù)',B.name as '班級名稱'from student as A,class as Bwhere A.classID=B.ID and A.classID LIKE '%3'go（6） TOPselect TOP 2 ID as '學號',score as '分數(shù)' from student;goselect TOP 30 percent * from student;go（7） 查詢結果排序select ID as '學號',score as '分數(shù)' from student

11、 where ID is not nullorder by score ASC;goselect ID as '學號',score as '分數(shù)' from student where ID is not nullorder by score DESC;go（8） 分組查詢，聚合函數(shù)(group,compute,compute by)select classID as '班號',avg(score) as '平均分' from student group by classID;goselect classID as '班號

12、',avg(score) as '平均分' from student group by classIDhaving classID LIKE '%2'goselect classID as '班號',sex as '性別',avg(score) as '平均分' from student group by classID,sexhaving classID in ('A01','A02')order by classID;goselect classID as '班號

13、',score as '分數(shù)',avg(score) as '平均分' from student group by classID,scorehaving classID in ('A01','A02')order by classIDcompute sum(score),max(score),min(score),count(score);goselect classID as '班號',score as '分數(shù)',avg(score) as '平均分' from stud

14、ent group by classID,scorehaving classID in ('A01','A02')order by classIDcompute sum(score),max(score),min(score),count(score) by classID;go實驗要求：（1） 測試驗證“實驗參考命令”中（1）（8）的相關命令，并寫出實驗結果。實驗成果：use mastergocreate database learngouse learngocreate table class(classID char(3) primary key,clas

15、sName nvarchar(10) not null,);goinsert into class values('a01','08信管');insert into class values('a02','09信管');insert into class values('a03','10信管');insert into class values('a04','11信管');gocreate table student(stuID int identity(1,1) n

16、ot null,stuName nvarchar(5) not null, stuSex nchar(1) not null,classNumber char(3) not null,score int,birthday datetime,constraint zhujian primary key(stuID),constraint scorecheck check(score>=0 and score<=100);goinsert into student values('王小屁','男','a04','90',&

17、#39;1992/02/29');insert into student values('王大屁','男','a02','21','1991/05/20');insert into student values('王臭屁','男','a01','59','1990/01/01');insert into student values('唐小堯','男','a03','87&

18、#39;,'1989/02/28');insert into student values('王屁屁','女','a03','100','1987/12/31');goselect* from student;結果-（1） 修改查詢結果的列標題名稱select stuID as '學號',stuName as '姓名',score as '成績'from student;結果：select stuID as '學號',stuName a

19、s '姓名',score as '成績',datename(year,birthday)+'年'+datename(month,birthday)+'月'+datename(day,birthday)+'日'as '生日'from student;go結果-（2） 多表查詢中創(chuàng)建各表的別名 select a.classID as '班號',a.className as '班名',b.stuName as '姓名',b.score as '成績&

20、#39;from student as b,class as awhere a.classID=b.classNumber;結果：（3）將查詢結果存入臨時表，并查詢臨時表Selecta.classID as '班號',a.className as '班名',b.stuName as '姓名',b.score as '成績'into #tmptablefrom student as b,class as awhere a.classID=b.classNumber;goselect*from #tmptable;連續(xù)值-select

21、班號,班名,姓名,成績from #tmptablewhere 成績between 30 and 100;或者select班號,班名,姓名,成績from #tmptablewhere 成績>=30 and 成績<=100;離散值-select班號,班名,姓名,成績from #tmptablewhere 班號 in ('a02','a03')；空值-select班號,班名,姓名,成績from #tmptableWhere 成績is not null;字符與日期：通配符查詢-select班號,班名,姓名,成績from #tmptablewhere 班號=&

22、#39;a03'-select班號,班名,姓名,成績from #tmptablewhere 班號like 'a%'-select班號,班名,姓名,成績from #tmptablewhere 班號like 'a00-2'或者 where 班號 like 'a_1' and 班號 like 'a_2'或者 where 班號 not like 'a_3' and 班號 not like 'a_4'-(4) 多表聯(lián)合查詢select a.classID as '班號',a.class

23、Name as '班名',b.stuName as '姓名',b.score as '成績'from student as b,class as awhere a.classID=b.classNumber and a.classID not like '%1' and b.score between 60 and 100;-(5) TOPSelect top 2 a.score as '成績',a.stuID as '學號',a.stuName as '姓名',b.classNam

24、e as '班名',b.classID as '班號'From student as a,class as bWhere a.classNumber=b.classID-(6) 查詢結果排序Select top 4 a.score as '成績',a.stuID as '學號',a.stuName as '姓名',b.className as '班名',b.classID as '班號'From student as a,class as bWhere a.classNumber=b.classID order by score asc;/asc由低到高 desc由高到低-(7) 分組查詢，聚合函數(shù)(group,compute,compute by)select classNumber,avg(score) as &#